Issue while dropping redo logs
SQL> 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 db12 (thread 2)
ORA-00312: online log 2 thread 2: '+DB1_REDO1/db/onlinelog/group_2.259.994893411'
ORA-00312: online log 2 thread 2: '+DB1_REDO2/db/onlinelog/group_2.259.994893411'
Check the status of the Redo Group if its ACTIVE or INACTIVE
If its CURRENT, please do some redo switches
SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;
THREAD# GROUP# Mbytes STATUS---------- ---------- ---------- ----------------
2 2 100 CURRENT
1 11 6000 INACTIVE
1 12 6000 ACTIVE
1 13 6000 ACTIVE
1 14 6000 ACTIVE
1 15 6000 CURRENT
1 16 6000 UNUSED
1 17 6000 UNUSED
1 18 6000 UNUSED
2 21 6000 UNUSED
2 22 6000 UNUSED
2 23 6000 UNUSED
2 24 6000 UNUSED
2 26 6000 UNUSED
2 27 6000 UNUSED
2 28 6000 UNUSED
SQL> alter system switch logfile;
After performing some redo switch, the status of the REDO GROUP changed to ACTIVE, still preventing me from dropping the REDO GROUP
SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;
THREAD# GROUP# Mbytes STATUS
---------- ---------- ---------- ----------------
2 2 100 ACTIVE
1 11 6000 CURRENT
1 12 6000 INACTIVE
1 13 6000 INACTIVE
1 14 6000 INACTIVE
1 15 6000 INACTIVE
1 16 6000 INACTIVE
1 17 6000 INACTIVE
1 18 6000 INACTIVE
2 21 6000 ACTIVE
2 22 6000 ACTIVE
2 23 6000 ACTIVE
2 24 6000 CURRENT
2 26 6000 UNUSED
2 27 6000 UNUSED
2 28 6000 UNUSED
Solution to do this is perform checkpoint
SQL> alter system checkpoint;
System altered.
SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;
THREAD# GROUP# Mbytes STATUS
---------- ---------- ---------- ----------------
2 2 100 INACTIVE
1 11 6000 CURRENT
1 12 6000 INACTIVE
1 13 6000 INACTIVE
1 14 6000 INACTIVE
1 15 6000 INACTIVE
1 16 6000 INACTIVE
1 17 6000 INACTIVE
1 18 6000 INACTIVE
2 21 6000 INACTIVE
2 22 6000 INACTIVE
2 23 6000 INACTIVE
2 24 6000 CURRENT
2 26 6000 UNUSED
2 27 6000 UNUSED
2 28 6000 UNUSED
16 rows selected.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
