ORA-01624
ORA-01624 means that the redo log group that you want to operate with is needed for recovery once the database startup after a crash. This is because some changes in the redo log group have not written into data blocks.
Let's see a case that can produce ORA-01624. Here we query V$LOG, a dynamic view of Oracle database, to look up the status of every redo log group.
SQL> select thread#, group#, status from v$log order by 1,2;
THREAD# GROUP# STATUS
---------- ---------- ----------------
1 1 CURRENT
1 3 UNUSED
1 5 ACTIVE
2 2 ACTIVE
2 4 CURRENT
2 6 UNUSED
6 rows selected.
Please focus on group 5 which status is ACTIVE and not CURRENT right now.
Now, we want to clear logfile group 5 for replacing a redo log member with a new one in a different location at a later time.
SQL> alter database clear logfile group 5;
alter database clear logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance TESTCDB1 (thread 1)
ORA-00312: online log 5 thread 1: '+DATA/ORCLCDB/redo05.log'
ORA-00312: online log 5 thread 1: '+DATA/TESTCDB/redo05.log'
Solution
1. When Database is Open
If the database is online. The solution to ORA-01624 is to do an ensuring checkpoint (CKPT), it will trigger the database writer (DBWn) process to write all changes in redo logs write into data files.
SQL> alter system checkpoint;
System altered.
Let's see the status of all redo log groups.
SQL> select thread#, group#, status from v$log order by 1,2;
THREAD# GROUP# STATUS
---------- ---------- ----------------
1 1 CURRENT
1 3 UNUSED
1 5 INACTIVE
2 2 INACTIVE
2 4 CURRENT
2 6 UNUSED
6 rows selected.
As we can see, all ACTIVE redo groups, group 5 and 2 become INACTIVE. We can safely clear logfile now.
SQL> alter database clear logfile group 5;
Database altered.
2. When Database Cannot Open
If the database can only reach MOUNT state and OPEN state is not possible, which means that the redo log is CURRENT or ACTIVE, even worse, it's corrupted, there's no way to open normally. The only way that you can do is to perform a database point-in-time recovery (DBPITR) and then ALTER DATABASE OPEN RESETLOGS in order to save the data as much as possible.