A2Z of Oracle Database Recovery
Posted by Sumit Singh Chadha on November 3, 2008
Let us look at the following recovery scenario’s :-
1. Loss of INACTIVE ONLINE Redo Log Group – COMPLETE RECOVERY.
a. CASE I – During Database Startup we notice that REDO LOG is missing and Error Came.
b. CASE II – If the INACTIVE REDO log is lost during Database Open.
2. Loss of Current Online Redo Log Group – INCOMPLETE RECOVERY
3. Loss of CONTROL FILE
4. Loss of UNDO Datafile/SYSTEM Datafile.
5. Loss of Datafile ( NON SYSTEM and NON UNDO Datafile )
——————————————————————————————————
1. Loss of INACTIVE ONLINE Redo Log Group – COMPLETE RECOVERY.
· CASE I – During Database Startup we notice that REDO LOG is missing and Error Came.
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: ‘C:ORACLEORADATATEST1REDO03.LOG’
Database in Mounted Stage.
Now check the status of above mentioned redo log. It must be INACTIVE .. according to our scenario.
SQL> select l.status,lf.member from v$log l,v$logfile lf where l.group#=lf.group#;
STATUS MEMBER
————————————-
CURRENT C:ORACLEORADATATEST1REDO01.LOG
INACTIVE C:ORACLEORADATATEST1REDO02.LOG
INACTIVE C:ORACLEORADATATEST1REDO03.LOG
NOTE: Assumption is that there is least 3 redo log group in order to do the following.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
SQL> select group#, thread#,bytes, members from v$log;
GROUP# THREAD# BYTES MEMBERS
———- ———- ———- ———-
1 1 10485760 1
2 1 10485760 1
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
C:ORACLEORADATATEST1REDO01.LOG
C:ORACLEORADATATEST1REDO02.LOG
SQL> alter database add logfile group 3 (‘C:ORACLEoradataTEST1REDO03.log’) size 10M;
Database altered.
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
C:ORACLEORADATATEST1REDO01.LOG
C:ORACLEORADATATEST1REDO02.LOG
C:ORACLEORADATATEST1REDO03.LOG
SQL>
· CASE II – If the INACTIVE REDO log is lost during Database Open.
Database will automatically SHUTDOWN the very moment when the log switch happens onto that datafile which is lost. Then During STARTUP you will face the same error as seen in above CASE 1 of Scenario 1 … hence follow the same steps.
2. Loss of Current Online Redo Log Group – INCOMPLETE RECOVERY
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘C:ORACLEORADATATEST1REDO01.LOG’
Database is in Mounted Stage.
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for thread 1 – cannot drop
ORA-00312: online log 1 thread 1: ‘C:ORACLEORADATATEST1REDO01.LOG’
SQL> select group#, status, sequence# from v$log;
GROUP# STATUS SEQUENCE#
———- —————- ———-
1 CURRENT 47
2 INACTIVE 45
3 INACTIVE 46
SQL> recover automatic database until cancel; ————– Incomplete Recovery and Apply Archives If Needed.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select l.status,lf.member from v$log l,v$logfile lf where l.group#=lf.group#;
STATUS MEMBER
——————————————————————————–
CURRENT C:ORACLEORADATATEST1REDO01.LOG ———————-automatically created.
INACTIVE C:ORACLEORADATATEST1REDO02.LOG
INACTIVE C:ORACLEORADATATEST1REDO03.LOG
SQL>
