ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

This post helps you out on overcoming the error “ORA-00392: log %s of thread %s is being cleared, operation not allowed” while trying to open the database in RESETLOGS mode after an incomplete recovery.

Here is what I came across while creating a database clone using the traditional RMAN backup restore and recovery process. Well, everything went fine with the restoration and the recovery operations, but error was thrown out while opening the database with RESETLOGS. Reason for this to occur was that the “alter database open resetlogs” was abnormally aborted.

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/30/2015 20:47:22
ORA-00344: unable to re-create online log '/u02/oradata/testdb/testdb/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

Recovery Manager complete.

[oracle@ora1-2 testdb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 30 20:47:44 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u02/oradata/testdb/testdb/redo01.log'

Let’s check the status of each of the groups and try clearing the problematic group manually. From the above error, the error is being thrown for the GROUP 1. So let’s try clearing it manually.

SQL> select group#,thread#,status from v$log;

GROUP#   THREAD#   STATUS
-------  --------- ----------------
1        1         CLEARING_CURRENT
3        1         CLEARING
2        1         CLEARING

SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/u02/oradata/testdb/testdb/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/u02/oradata/testdb/testdb/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

Oops, it doesn’t allow me to clear as these files do not exist on the axuiliary server and that’s right. Redo logs would be created once you open the database with Reestlogs.

The control file has the records of the Redo Log file locations as that of the source server. Agreed.

SQL> select group#,status,member from v$logfile;

GROUP#   STATUS      MEMBER
-------  ----------- ----------------------------------------------------------
3                    /u02/oradata/testdb/testdb/redo03.log

2                    /u02/oradata/testdb/testdb/redo02.log

1                    /u02/oradata/testdb/testdb/redo01.log

What next ?

I tried to update the controlfile with the new location of redo logs by renaming the log files at the database level.

SQL> alter database rename file '/u02/oradata/testdb/testdb/redo01.log' to '/u03/oradata/testdb/redo01.log';

Database altered.

SQL> alter database rename file '/u02/oradata/testdb/testdb/redo02.log' to '/u03/oradata/testdb/redo02.log';

Database altered.

SQL> alter database rename file '/u02/oradata/testdb/testdb/redo03.log' to '/u03/oradata/testdb/redo03.log';

Database altered.

SQL> alter database clear logfile group 1;

Database altered.

Now, I tried opening the database with resetlogs, yes of of-course without any abruption 🙂

SQL> alter database open resetlogs;

Database altered

So basically, if there is an error “ORA-00392: log %s of thread%s is being cleared, operation not allowed”, then you need to clear the problematic Logfile Group. If the file doesn’t exist on the auxiliary server and the controlfile is still pointing to the Redo Log file location of the source database, then you can try renaming the files at the database level and then try clearing the ORLs.

Here we go !!

HTH

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.