db10g | Oracle Technology

Oracle, Working noteAdd comments

When we did a cloning/recover the database with noarchivelog mode, we got the problem that some datafile need to be recover. It will be difficulty since no archivelog that can help us to recover it. Otherwise we can copy all datafiles from offline backup of the source database. But it will takes time to copy/ftp/restore especially if the database size are hundreds GB or even TB. But there is a solution to recover the database with noarchivelog mode, please check this out :

When we did a cloning, startup nomount :

$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Apr 13 13:54:43 2010

Connected to an idle instance.

SQL> startup nomount pfile=initMYDB.ora

ORACLE instance started.

Total System Global Area 5251268608 bytes

Fixed Size 2091368 bytes

Variable Size 1040189080 bytes

Database Buffers 4194304000 bytes

Redo Buffers 14684160 bytes

Create New control File:

SQL> @createctl.sql

Control file created.

Since the cloning come from offline backup and the SID in target db as same as source db so

we donâ€™t need to resetlogs, but the one of datafile need to recover :

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘/u01/system01.dbf’

Try To recover, but we donâ€™t have the archivelog file that needed :

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 5991183372639 generated at 04/13/2010 13:51:42 needed for

thread 1

ORA-00289: suggestion :

/u02/db/10.2.0/dbs/arch1_1125_714320021.dbf

ORA-00280: change 5991183372639 for thread 1 is in sequence #1125

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: ‘/u01/system01.dbf’

ORA-01112: media recovery not started

Try to open resetlogs, we still got the same error

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: ‘/u01/system01.dbf’

To fix this issue :

1. Shutdown immediate

SQL> Shutdown immediate

2. Remark the parameter in initMYDB.ora:

– UNDO_MANAGEMENT=AUTO

– UNDO_TABLESPACE=OLD_UNDOTS

3. Add the parameter in initMYDB.ora :

UNDO_MANAGEMENT=MANUAL

_ALLOW_RESETLOGS_CORRUPTION = TRUE

_ALLOW_ERROR_SIMULATION = TRUE

4. Startup database with new init.ora :

$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Apr 13 16:06:56 2010

Connected to an idle instance.

SQL> startup mount pfile=initMYDB.ora

ORACLE instance started.

Total System Global Area 5251268608 bytes

Fixed Size 2091368 bytes

Variable Size 1040189080 bytes

Database Buffers 4194304000 bytes

Redo Buffers 14684160 bytes

Database mounted.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 5991183372639 generated at 04/13/2010 13:51:42 needed for

thread 1

ORA-00289: suggestion :

/u02/db/10.2.0/dbs/arch1_1125_714320021.dbf

ORA-00280: change 5991183372639 for thread 1 is in sequence #1125

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: ‘/u01/system01.dbf’

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

Database altered.

5. Now the database already startup with Manual undo management.

6. Create new UNDO Tablespace

SQL> Create UNDO tablespace NEW_UNDOTS datafile ‘/u02/undo01.dbf’ size 2048M;

7. Take offline the OLD Undo Tablespace :

SQL> alter tablespace OLD_UNDOTS offline;

8. Take online the NEW Undo Tablespace :

SQL> alter tablespace NEW_UNDOTS ;

9. Shutdown the database :

SQL> shutdown immediate;

10. Edit the initMYDB.ora :

+ Remark the parameter :

– UNDO_MANAGEMENT=MANUAL

– _ALLOW_RESETLOGS_CORRUPTION = TRUE

– _ALLOW_ERROR_SIMULATION = TRUE

+ Add and edit the parameter :

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=NEW_UNDOTS

11. Startup the database :

SQL> startup

12. The database will startup with the NEW Undo tablespace, change the default undo tablespace :

SQL> alter system set undo_tablespace=NEW_UNDOTS;

13. Then we can drop the OLD Undo tablespace :

SQL> drop tablespace OLD_UNDOTS including contents and datafiles;

———-Startup standby database———-

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY ;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION ;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE ;

———-Managed Standby Oracle Database——————-

select open_mode,protection_mode from v$database;

select * from v$archive_gap;

select process, client_process, sequence#, status from v$managed_standby ;

select sequence#, first_time, next_time, applied from v$archived_log where thread#=&thread;

select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

select thread#, max (sequence#) from v$log_history group by thread#;

select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

————- Change to Maximize Protection ———————

SELECT PROTECTION_MODE FROM V$DATABASE;

log_archive_dest_2=’SERVICE=C67DB SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=C67DB’;

log_archive_dest_2 string SERVICE=C67DB LGWR ASYNC VALI

D_FOR=(ONLINE_LOGFILES,PRIMARY

_ROLE) DB_UNIQUE_NAME=C67DB

alter system set log_archive_dest_2=’SERVICE=C67O SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=C67O’ scope=spfile;

alter system set log_archive_dest_3=’SERVICE=STDB SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDB’ scope=spfile;

Oracle Technology

Build oracle service success.

Category Archives: db10g

Recover the Oracle Database without Archive Log

Manage Data Guard