RESTORING THE ORACLE DB FROM THE LOSS/CORRUPTION OF AN ACTIVE REDO LOG FILE – AN EXTREME RECOVERY SCENARIO

                                                                                             

 

INTRODUCTION:

 

We have encountered a very uncommon recovery scenario recently, where an “active” redo log file got corrupted and as a result, the crashed instance could not be brought up. Recovering from the loss of an “inactive” redo log file would be straight forward as per Oracle docs, however nothing has been covered on the topic of recovering due to loss of an “active” redo member, except a brief mention to call up the oracle tech support.

 

PROBLEM:

 

In the event of loss or corruption of redo log file, the instance detects a mismatch between Redo records and Rollback (Undo)   records, and causes the crash with a PMON error of 472 as shown below.

 

OPIRIP: Uncaught error 1089. Error stack:

ORA-01089: immediate shutdown in progress – no operations are permitted

ORA-00600: internal error code, arguments: [4194], [52], [46], [], [], [], [], []

PMON: terminating instance due to error 472

Instance terminated by PMON, pid = 7623

 

IMBROGLIO a.k.a CATCH 22:

 

1. The database will not open with the loss / corruption of redo log.

2. Unless the database is opened, the redo log related commands such as switch, clear, drop and create log file   member or log file group will not work.

3. Reset logs will not work as well as the header information between redo and undo differs and the instance will still be looking for a complete recovery.

 

ANALYSIS:

Based on the mismatch of records between redo and undo, we need to recreate both redo and undo segments in order for the database to be  functional.

 

RESOLUTION:

1.  In the mount state

 

SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

 

    GROUP#    MEMBERS STATUS           ARC

———- ———- —————- —

         1          1 INACTIVE         NO

         2          1 CURRENT          NO

 

SQL> !ls -ltr /opt/oracle/oradata/test/redo02.log

ls: /opt/oracle/oradata/test/redo02.log: No such file or directory

 

 

2. SQL>ALTER DATABASE ADD LOGFILE GROUP 3 (‘/opt/oracle/oradata/test/redo04.log’, ‘/opt/oracle/oradata/test/redo05.log’) SIZE 500K;

 

3. Shutdown the database.

 

4. Incorporate the below three hidden parameters and start up in the mount state.

 

_ALLOW_RESETLOGS_CORRUPTION = true

_CORRUPTED_ROLLBACK_SEGMENTS = true

_ALLOW_READ_ONLY_CORRUPTION = tue

 

5. Note down that the status column “active” state has been transferred to a different member from 2 to 1.    Now, we need to bring the header, scn info into sync for all the redo members and for undo segment as well.

 

SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

 

    GROUP#    MEMBERS STATUS           ARC

———- ———- —————- —

         1          1 CURRENT          NO

         2          1 INACTIVE         NO

         3          2 UNUSED           YES

 

 

6. SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

 

 

7. SQL> ALTER DATABASE ADD LOGFILE GROUP 2;

     SQL>  ALTER DATABASE ADD LOGFILE MEMBER ‘/opt/oracle/oradata/test/redo02.log’ REUSE TO GROUP 2;

 

 

8.  SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE  ’/opt/oracle/oradata/test/undotbs02.dbf’ SIZE 1024M reuse  AUTOEXTEND ON;

 

9. SQL> ALTER SYSTEM SET undo_tablespace = UNDOTBS2;

 

10. SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

 

11. Finally, clean -up the parameter file from the three hidden underscore parameters, then set the correct undo tablespace followed by bringing up the database.

 

In summary, this particular procedure outlines a technique on, how to recover the database from the loss/corruption of an active redo log. 

Hope fellow oracle-ites find the information useful.

 

Disclaimer:  If you ever get into similar situation in production environment, please contact  oracle tech support, as each oracle set-up will be different.