Saturday, August 20, 2022

How to clear unarchived redo log file? (fix redo log member missing issue)

Symptom:

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata/db/ORCL/onlinelog/o1_mf_1_k4fz1yon_.log
/oradata/db/ORCL/onlinelog/o1_mf_2_k4fz1y42_.log
/oradata/db/ORCL/onlinelog/o1_mf_3_k4fz1xll_.log
/oradata/db/ORCL/onlinelog/o1_mf_4_k4fz1zn7_.log
SQL> select group#,status from v$Log;

    GROUP# STATUS
---------- -------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
         4 INACTIVE
SQL> ! ls -l /oradata/db/ORCL/onlinelog/
total 262152
-rw-r----- 1 oracle oinstall 134218240 Aug 19 22:00 o1_mf_2_k4fz1y42_.log
-rw-r----- 1 oracle oinstall 134218240 Aug 19 03:22 o1_mf_4_k4fz1zn7_.log

Steps to fix:

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance ORCL (thread 1) needs to be archived
ORA-00312: online log 1 thread 1:
'/oradata/db/ORCL/onlinelog/o1_mf_1_k4fz1yon_.log'
SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.
SQL> ! ls -l /oradata/db/ORCL/onlinelog/
total 524308
-rw-rw---- 1 oracle oinstall 134218240 Aug 19 23:40 o1_mf_1_kj07to4t_.log
-rw-r----- 1 oracle oinstall 134218240 Aug 19 23:40 o1_mf_2_k4fz1y42_.log
-rw-rw---- 1 oracle oinstall 134218240 Aug 19 23:40 o1_mf_3_kj07v76f_.log
-rw-r----- 1 oracle oinstall 134218240 Aug 19 03:22 o1_mf_4_k4fz1zn7_.log

No comments:

Post a Comment