Database Recovery from Corrupted, Missing Redo, Archive Log Files Using _ALLOW_RESETLOGS_CORRUPTION, _CORRUPTED_ROLLBACK_SEGMENTS, _minimum_giga_scn and _allow_error_simulation

advertisements

_____________________________________________________________________________________________________________________

Recovery Scenario:

This recovery method will help you to recover the database in following situations.

1. If you lose all redo log files for the database. It can be deleted by mistake or disk corruption.
2. If you database recovery needed by redo log group corruption
3. If you don’t have archive log backups to finish the incomplete recovery of the database.
4. If your ‘open resetlogs command’ fails with error ORA-01194: file 1 needs more recovery to be consistent

In above cases, you have to go ahead with incomplete recovery and before do the open resetlogs the datafiles headers should be in sync with the scn number which will not be the case in these situations.

You can try this method to complete the recovery. In most of the case this will help you to bring up the database and as soon as you bring up the db take an export backup of required schemas.

Oracle Hidden/Undocumented Parameters:

Hidden parameters need to be tested in test database before implementing in production. Also, better to get support from oracle before going to production. Hidden/Undocumented parameters are for internal purpose of the oracle. You have to be very careful before implementing as they are very dangerous. It starts with ‘_’.

The Hidden parameters mentioned for Database restoration are.

_ALLOW_RESETLOGS_CORRUPTION=TRUE

This parameter helps to open the database even the datafile headers are not sync with the SCN details. It will allow you to open the database with resetlogs option even the datafiles needed recovery. As soon as the database opens with resetlogs the next check point SCN will get updated to the datafile headers. This leaves the database to an unknown state as it avoids the rolling forward process.

_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU10_1912826470$,..)

This parameter is used to corrupt the active rollback segments and which will not be used in the open database. Using this parameter you can drop the rollback segments as well as the undo tablespaces. Refer the following link for more details.

Find Out Active Rollback Segment

_allow_error_simulation=TRUE

This parameter allows the error simulation for testing purpose. Normally it used for Oracle internal purpose.

_minimum_giga_scn=<number>

This parameter is similar to the ADJUST_SCN event to advance the database SCN to higher value (ahead value). The database should be rebuilt after using this parameter and you should get support from Oracle to decide the value. This parameter is useful in some recovery situations where the current SCN needs to be incremented by a large value to ensure it is ahead of the highest SCN in the database. Typically it needed for ORA-600 [2662] errors to be recoverd.

Suppose if you set _minimum_giga_scn=6774, the scn would be incremented to 6774*1024*1024*1024=7273527115776 and you can see corresponding log in the alert log file as like following.

Setting recovery target incarnation to 3

Advancing SCN to 7273527115776 according to _minimum_giga_scn

Recovery Steps

1. Open resetlogs failed with following error.

SQL> startup nomount  ;

ORACLE instance started.

Total System Global Area 1720328192 bytes

Fixed Size                  2227192 bytes

Variable Size            1308623880 bytes

Database Buffers          402653184 bytes

Redo Buffers                6823936 bytes

SQL> @cr_ctrl.sql

Control file created.

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/app/oracle/oradata/testdb/system01.dbf'

2. Shutdown the database.

SQL> Shutdown immediate

3. Set the following parameters in the init.ora file.

Make the UNDO_MANAGEMENT=MANUAL

_MANAGEMENT=MANUAL

_ALLOW_RESETLOGS_CORRUPTION=TRUE

_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU10_1912826470$,_SYSSMU10_84651485$,
_SYSSMU11_2656753586$,_SYSSMU12_2886358909$,
_SYSSMU1_2986795754$,_SYSSMU13_718924942$,
_SYSSMU1_4289064447$,_SYSSMU14_3114789169$,
_SYSSMU15_996261396$,_SYSSMU16_1415326701$,
_SYSSMU17_2055763971$,_SYSSMU18_3698054671$,
_SYSSMU19_3843065260$,_SYSSMU20_4176760040$,
_SYSSMU2_346913448$,_SYSSMU2_3741593945$,
_SYSSMU3_219912871$,_SYSSMU3_2414309367$,
_SYSSMU4_1372950691$,_SYSSMU4_2796668278$,
_SYSSMU5_1189263717$,_SYSSMU5_3414239920$,
_SYSSMU6_2463593703$,_SYSSMU6_3524683480$,
_SYSSMU7_1565166063$,_SYSSMU7_2494227896$,
_SYSSMU8_2615489449$,_SYSSMU8_625026625$,
_SYSSMU9_3163463872$,_SYSSMU9_4094185854)

_allow_error_simulation=true

4. Startup the database and open resetlogs

SQL>  startup;

ORACLE instance started.

Total System Global Area 1720328192 bytes

Fixed Size                  2227192 bytes

Variable Size            1308623880 bytes

Database Buffers          402653184 bytes

Redo Buffers                6823936 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number  with name "" too small

Process ID: 5701

Session ID: 125 Serial number: 5

5. You must succeed with above step. But if you are again getting the error as above then you have to set _minimum_giga_scn=6774 parameter and try the same open resetlogs.

6. Now you have to make the undo_management to again back to AUTO for that you have to follow below steps.

a.   Add a new undo tablespace and make online.

SQL> Create UNDO tablespace UNDO02  datafile '/u01/app/oracle/oradata/testdb /undo02.dbf' size 2g;

Tablespace created.

SQL> alter tablespace UNDO02;

Tablespace altered.

    

b.   Offline the previous undo tablespace.

SQL> alter tablespace UNDO01 offline;

Tablespace altered.

c.    Shutdown the database

d.   Make the changes to the UNDO_MANAGEMENT and UNDO_TABLESPACE

UNDO_MANAGEMENT = AUTO

UNDO_TABLESPACE = UNDO02

e.   Comment out parameters _allow_error_simulation, _ALLOW_RESETLOGS_CORRUPTION, _CORRUPTED_ROLLBACK_SEGMENTS, _minimum_giga_scn

f.     Startup the database and set new undo tablespace as default one.

SQL> Startup;

SQL> alter system set undo_tablespace UNDO02;

System altered.

g.   Drop the previous undo tablespace.

SQL> drop tablespace UNDO01 including contents and datafiles;

h.   If you are facing the following error refer below mentioned link.

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU1_2986795754$' found, terminate

dropping tablespace

Find Out Active Rollback Segment

_____________________________________________________________________________________________________________________

Website Stats