PROBLEM:
While dropping a redolog group , got below error.
SQL> alter database drop logfile group 1; alter database drop logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance SBIP18DB (thread 1) ORA-00312: online log 1 thread 1: '/SIBIP16/SBIP18DB/SBIP18DB/redo01.log'
SOLUTION:
First get the status of redolog group:
select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize from v$log l, v$logfile f where f.group# = l.group# order by 1,2 GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB) ------ ------- ---------------------------------------------------------------------- ---------- ---------- --------- 1 1 /SIBIP16/SBIP18DB/SBIP18DB/redo01.log YES ACTIVE 50 ------ >>>>>>> 2 1 /SIBIP16/SBIP18DB/SBIP18DB/redo02.log YES ACTIVE 50 3 1 /SIBIP16/SBIP18DB/SBIP18DB/redo03.log NO CURRENT 50
We can see the though the redolog group 1 has been archived, its status is ACTIVE. I.e this redolog is required for crash recovery or block recovery.
So to change the status from ACTIVE to INACTIVE, switch logfile multiple times, till the status is INACTIVE.
SQL> alter system switch logfile; SQL> @log_member GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB) ------ ------- ---------------------------------------------------------------------- ---------- ---------- --------- 1 1 /SIBIP16/SBIP18DB/SBIP18DB/redo01.log NO CURRENT 50 2 1 /SIBIP16/SBIP18DB/SBIP18DB/redo02.log YES ACTIVE 50 3 1 /SIBIP16/SBIP18DB/SBIP18DB/redo03.log YES ACTIVE 50 alter system switch logfile; SQL> @log_member GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB) ------ ------- ---------------------------------------------------------------------- ---------- ---------- --------- 1 1 /SIBIP16/SBIP18DB/SBIP18DB/redo01.log YES INACTIVE 50 -------------->>>>>>>>>> 2 1 /SIBIP16/SBIP18DB/SBIP18DB/redo02.log YES INACTIVE 50 3 1 /SIBIP16/SBIP18DB/SBIP18DB/redo03.log NO CURRENT 50
Once the redo log group status is INACTIVE, drop it.
SQL> alter database drop logfile group 1; Database altered.