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

32 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.

  3. You actually make it seem so easy with your presentation but I find this matter to be actually something that I think I would never understand. It seems too complicated and very broad for me. I’m looking forward for your next post, I will try to get the hang of it!

  4. Wow, superb blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your web site is excellent, as well as the content!

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 )

w

Connecting to %s