Oracle 11g Recovery from loss of current online Redo log file

Recovery from loss of current online Redo log file
--------------------------------------------------

1.
SQL> Select member
From v$logfile;

2.
To simulate loss of online Redo log file rename the fileslisted in step 1 above.

Result -> This may crash the DB -> This simulates loss of online Redo log files

If DB did not crash try
SQL> alter database checkpoint;
SQL> alter database clear logfile;
SQL> shutdown abort;

3.
sqlplus / as sysdba;
SQL> startup

O/P -> ORA-00313
followed by ORA-00312
followed by ORA-27037...

4.
Restore the DB files, control files from the latest backup

5.
sqlplus / as sysdba
SQL> startup mount;

6.
Determine the last log sequence archived

SQL> Select max(sequence#)
From v$archived_log
Where resetlogs_changed# = (Select max(resetlogs_change#)
From v$archived_log);

7.
SQL> recover database using backup controlfile until cancel;
SQL> recover database until cancel using backup controlfile;

O/P -> ORA-00279
followed by ORA-00289
...
followed by ORA-00280
followed by ORA-00278
Specify log: {=suggested filename Auto Cancel}

8.
Type
cancel

O/p -> Media recovery cancelled.

9.
SQL> alter database open resetlogs;

Recovery is done.

----------------------------------------------------------

- Suresh