All about recovering REDO logs

Online redo logs store a record of transactions that have occurred in your database. Online redo logs exist solely to provide a mechanism for you to recover your database in the event of a failure


Various states of REDO logs groups:

Types of Failures and Action Taken:

Case I:  INACTIVE state damage

SQL> connect / as sysdba

SQL> startup mount;

Next, run the following query to verify that the damaged log group is INACTIVE and determine whether it has been archived:

SQL> select group#, status, archived, thread#, sequence# from v$log;

GROUP# STATUS ARC THREAD# SEQUENCE#

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

1 INACTIVE YES 1 44

3 INACTIVE YES 1 45

2 CURRENT NO 1 46

SQL> alter database clear logfile group 1;

If the log group has not been archived, then you will need to use the clear unarchived logfile command as follows:

SQL> alter database clear unarchived logfile group 1;

Case II:  ACTIVE state damage

If the status is ACTIVE, then attempt to issue an alter system checkpoint command, as shown here:

SQL> alter system checkpoint;

If the checkpoint completes successfully, then the active log group should be marked as INACTIVE. A successful checkpoint ensures that all modified database buffers have been written to disk

SQL> select group#, status, archived, thread#, sequence# from v$log;

GROUP# STATUS ARC THREAD# SEQUENCE#

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

1 INACTIVE YES 1 47

2 INACTIVE YES 1 46

3 CURRENT NO 1 48

SQL> alter database clear logfile group <group#>;

SQL> alter database clear unarchived logfile group <group#>;

Case III:  CURRENT state damage

Unfortunately, your alternatives are limited when you lose all members of a current online redo log group.

Perform an incomplete recovery up to the last good SCN

SQL> shutdown immediate;

SQL> startup mount;

SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log;

GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#

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

1 INACTIVE YES 1 50 1800550

2 INACTIVE YES 1 49 1800468

3 CURRENT NO 1 51 1800573

RMAN> restore database until scn 1800573;

RMAN> recover database until scn 1800573;

RMAN> alter database open resetlogs;

Enjoy:-)