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

Some days before when I was cloning one of the production database into UAT box, step to open database with resetlogs option failed due to insufficient space in one of the diskgroup.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log '+RADACTL1'
ORA-17502: ksfdcre:4 Failed to create file +RADACTL1
ORA-15041: diskgroup "RADACTL1" space exhausted

So I checked for the diskgroup with sufficient free space for creating redo logs and changed parameter accordingly.

SQL> alter system set db_create_online_log_dest_1='+RADA';
System altered.
SQL> alter system set db_create_online_log_dest_2='+RADA';
System altered.
SQL> sho parameter online
NAME TYPE VALUE
----------------------------------- ----------- ------------------------------
db_create_online_log_dest_1 string +RADA
db_create_online_log_dest_2 string +RADA
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

Now once again I tried opening database with resetlogs option. This attempt once again failed but giving different error.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 12 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 12 thread 1: '+RADACTL1'
ORA-00312: online log 12 thread 1: '+RADACTL2'

I checked for status of redo log group 12:

 SQL> select status from v$log where GROUP#=12;
STATUS 
----------------
CLEARING_CURRENT

I decided to clear this group manually:

SQL> alter database clear unarchived logfile group 12;
Database altered.

Now once again I attempted opening database with resetlogs option & it worked without issues. 🙂

SQL> alter database open resetlogs;
Database altered.

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Advertisements

26 thoughts on “ORA-00392: log X of thread X is being cleared, operation not allowed

  1. Hello Adi,

    This blog post solved my problem with clearing the LOG files out from a CLEARING_CURRENT state and was able to ALTER DATABASE OPEN RESETLOGS after.
    SQL> select status from v$log where GROUP#=’39’;

    STATUS
    —————-
    CLEARING_CURRENT

    Elapsed: 00:00:00.01
    SQL> alter database clear unarchived logfile group 39;

    Database altered.

    My question is, if you know, how did my DB get into this state, CLEARING_CURRENT? This happened when I was restoring the DB from an image.

    Thanks!

    • Thanks for visiting and appreciate your feedback.:-)

      Your redo log will be in status CLEARING_CURRENT when current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

      In my case, I tried to open resetlogs database just after recovering & my diskgroup for FRA was 100% full, due to which it was not able to update log headers.

      Regards,
      Adi

  2. Thank you for every other informative blog. The place else may just I get that kind of info written in such an ideal way? I have a mission that I am simply now operating on, and I’ve been at the glance out for such information.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s