ORA-00392: log 1 of thread 1 is being cleared, operation not allowedORA-00312: online log 1 thread 1: ‘/u01/ORCLDGP/onlinelog/o1_mf_1_%u_.log’

Cause:

After cloning a database from source to destination, i was issuing resetlogs for an incomplete recovery of new database.But ended up with ora-00392 error.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/ORCLDGP/onlinelog/o1_mf_1_%u_.log'

When i check status of online logs , they were in clear state

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          0   52428800        512          1 NO
CLEARING_CURRENT      14764677 20-JUN-21   2.8147E+14                    0

         3          1          0   52428800        512          1 YES
CLEARING              14760171 20-JUN-21     14764677 20-JUN-21          0

         2          1          0   52428800        512          1 YES
CLEARING              14755753 19-JUN-21     14760171 20-JUN-21          0

Workaround:

Manually clear the logfiles to proceed with resetlog

SQL> alter database clear logfile group 1;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          0   52428800        512          1 NO
CURRENT               14764677 20-JUN-21   2.8147E+14                    0

         3          1          0   52428800        512          1 YES
CLEARING              14760171 20-JUN-21     14764677 20-JUN-21          0

         2          1          0   52428800        512          1 YES
CLEARING              14755753 19-JUN-21     14760171 20-JUN-21          0


SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL>

Check the fresh log files

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          1   52428800        512          1 NO
CURRENT               14768907 21-JUN-21   2.8147E+14                    0

         2          1          0   52428800        512          1 YES
UNUSED                       0                      0                    0

         3          1          0   52428800        512          1 YES
UNUSED                       0                      0                    0

Post the logfile header clear, resetlog worked

SQL>    alter database open resetlogs;

Database altered.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s