Wednesday, April 23, 2014

ORA-01624 log needed for crash recovery of instance

It is quite common to see the error ORA-01624 while dropping a redo log group. The full error text is similar to “log <log_group#> needed for crash recovery of instance <instance_name (thread #)”.

Reason
As we know that Log Writer (LGWR) has the responsibility to write

the changes made to the data into the CURRENT redo log group (in all the members of this redo log group). Writing into redo log group guarantees that all committed transactions are recoverable in case of any failure (instance/media). At this point, there is no guarantee that changed blocks (buffers) in database buffer cache have also been written to the datafiles (as these buffers would be written to the datafiles when CHECKPOINT would occur) and if before data buffers are written to the disk, an instance failure occurs, Oracle would need this current redo log group (or may be other redo log groups as well) to perform the instance recovery - and hence Oracle would not let you drop any redo log group which has ACTIVE status in V$LOG view.
Solution
Issue a checkpoint statement which would cause all changed data blocks in the buffer cache to be written to the disk (in the datafiles) and this (also any other group with ACTIVE status) logfile group would no longer be required for instance recovery.

TestCase

--Add a logfile group.
SQL> alter database add logfile group 4 '+DATA' size 50m;

-- Switch logfile until this new group 4 becomes CURRENT.
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
         4 UNUSED

SQL> alter system switch logfile;

System altered.

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

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 INACTIVE
         4 CURRENT

-- Switch logfile once again so that it is no longer current.
SQL> alter system switch logfile;

System altered.

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

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT
         4 ACTIVE

Now the status of group 4 is ACTIVE and it means that either it is to be archived yet or needed for instance recovery - and during this status, it can't be dropped or cleared. 

If it has to be archived yet, we would need to wait for archiving to finish, otherwise, we can issue a checkpoint statement to change it's status to INACTIVE and then drop it.

-- Try to drop group 4
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance cdb (thread 1)
ORA-00312: online log 4 thread 1: '+DATA/CDB/ONLINELOG/group_4.264.845652729'

--Issue a checkpoint. append GLOBAL at the end if this is a RAC environment to checkpoint globally.
alter system checkpoint;

System altered.

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

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE

-- Now again try to drop the group
SQL> alter database drop logfile group 4;

Database altered.


5 comments:

Kumar K Shiva said...

I faced a case where the corrupted log is CURRENT and we don't have any backup. database went down and unable to OPEN
In such case 'switch logfile' fails, too.

ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 96592341 time 04/09/2015 15:50:43
ORA-00312: online log 4 thread 1: '/database/TEST/redo/REDO1_4a_00.rdo'

Salman Ahmed Qureshi said...

Hi Kumar,
You are right, there could also be several other scenarios where we can see this error.

Shaggy Dad said...

how did you fix it?

Shaggy Dad said...

how did you fix it?

Salman Ahmed Qureshi said...

Fix what? Please read this article carefully, it includes scenario and the solution.

Popular Posts - All Times