ORA-01624: log 2 needed for crash recovery of instance

<<Back to DB Administration Main Page

ORA-01624: log 2 needed for crash recovery of instance

ORCL1> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance ORCL1 (thread 1)
ORA-00312: online log 7 thread 1: '+DATA/ORCL/ONLINELOG/group_7.1287.984827207'
ORA-00312: online log 7 thread 1: '+RECO/ORCL/ONLINELOG/group_7.12707.984827207'
ORCL1>  select group#,thread#,status, bytes/1024/1024 "size in MB" from v$log;
    GROUP#    THREAD# STATUS               size in MB
---------- ---------- -------------------- ----------
         1          1 CURRENT                    2048
         2          1 ACTIVE                    2048
         6          3 CURRENT                      50
         7          1 ACTIVE                     2048

ERROR ORA-01624 is triggered if you are trying to drop a redo log group whose status is still ACTIVE. Although its not current , but it is still holding ACTIVE redo which is required for instance recovery and therefore you needed to wait for a while until the status turns to INACTIVE (means until this redo log is archived).

Solution
Retry dropping the redo logs only after STATUS becomes INACTIVE

ORA-01623: log 1 is current log for instance ORCL1 (thread 1) - cannot drop

ORCL1>  alter database drop logfile group 1;
 alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance ORCL1 (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '+DATA/ORCL/ONLINELOG/group_1.1225.984827621'
ORA-00312: online log 1 thread 1: '+RECO/ORCL/ONLINELOG/group_1.40899.984827623'


ERROR ORA-01623 is triggered if you are trying to drop a redo log group whose status is still CURRENT.

Solution
Perform log switch
SQL> alter system switch logfile;
System altered.
Check Log group Status
SQL>select group#,thread#,status, bytes/1024/1024 "size in MB" from v$log;

Proceed to drop the log group only once the status is INACTIVE