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.
