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

Published on

July 20, 2014

7,323 hits

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

38 responses to “ORA-00392: log X of thread X is being cleared, operation not allowed”

Mayur Deshmukh

July 31, 2014

Really helpful article!

Thanks Adityanath 🙂

Reply
1. Adityanath Dewoolkar
  
  August 5, 2014
  
  Welcm Mayur!!
  
  Reply
Raj Mohan

January 21, 2015

Really a very helful article.. I got stuck in same problem.. Your solution fixed it.. THanks for sharing..

Reply
1. Adityanath Dewoolkar
  
  January 21, 2015
  
  Hey Raj Mohan,
  
  Thanks for visiting and appreciate your feedback. 🙂
  
  Reply
Amol

February 18, 2015

I had this same issue and your blog entry guided me to fix it. Thanks.

Reply
1. Adityanath Dewoolkar
  
  February 18, 2015
  
  Thanks for visiting and appreciate your feedback. 🙂
  
  Reply
Naseer Arif

March 16, 2015

Thanks, it works for me 🙂

Reply
1. Adityanath Dewoolkar
  
  March 16, 2015
  
  Thanks for visiting and appreciate your feedback. 🙂
  
  Reply
Kenneth Igiri

March 18, 2015

Wow… Adit… this is one of the blogs that solved my problem straight away. Thanks and keep n=blogging.

Reply
1. Adityanath Dewoolkar
  
  March 18, 2015
  
  Thanks for visiting and appreciate your feedback. 🙂
  
  Reply
Ajay

May 11, 2015

thanks a lot….

Reply
1. Adityanath Dewoolkar
  
  May 11, 2015
  
  Thanks for visiting and appreciate your feedback. 🙂
  
  Reply
Dhanasekaran

October 21, 2015

Thank you.. Was very helpful

Reply
1. Adityanath Dewoolkar
  
  October 21, 2015
  
  Thanks for visiting and appreciate your feedback. 🙂
  
  Reply
Khalid

January 1, 2016

Thanks a lot and happy new year

Reply
1. Adityanath Dewoolkar
  
  January 1, 2016
  
  Thanks for visiting and appreciate your feedback. 🙂 & happy new year 🙂
  
  Reply
Dave

March 16, 2016

Thanks! You saved me some time!

Reply
1. Adityanath Dewoolkar
  
  March 17, 2016
  
  Thanks for visiting and appreciate your feedback. 🙂
  
  Reply
CSGO

June 6, 2016

Wow because this is great work! Congrats and keep it up

Reply
1. Adityanath Dewoolkar
  
  June 7, 2016
  
  Thanks for visiting and appreciate your feedback.:-)
  
  Regards,
  Adi
  
  Reply
willsnotes

June 13, 2016

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!

Reply
1. Adityanath Dewoolkar
  
  June 14, 2016
  
  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
  
  Reply
Webmaster

June 29, 2016

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.

Reply
1. Adityanath Dewoolkar
  
  June 29, 2016
  
  Thanks for visiting and appreciate your feedback.:-)
  
  Regards,
  Adi
  
  Reply
naga

February 4, 2017

thanks you.
Regards,
Naga

Reply
1. Adityanath Dewoolkar
  
  February 9, 2017
  
  Hello There,
  
  Thanks for visiting and appreciate your feedback.:-)
  
  Regards,
  Adi
  
  Reply
Johanna

February 12, 2018

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!

Reply
Linh

February 16, 2018

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!

Reply
1. Adityanath Dewoolkar
  
  April 2, 2018
  
  Hello There,
  
  Thanks for visiting and appreciate your feedback.:-)
  
  Regards,
  Adi
  
  Reply
Blogs

April 1, 2018

Way to go!

Reply
Long Car

April 3, 2018

Don’t ever change a thing about this site. I enjoy it so much.

Reply
1. Adityanath Dewoolkar
  
  August 21, 2018
  
  Hello There,
  
  Thanks for visiting and appreciate your feedback.:-)
  
  Regards,
  Adi
  
  Reply
Tasty Waffles

April 4, 2018

Keep up the fantastic work. I can wait to read the next article.

Reply
1. Adityanath Dewoolkar
  
  August 21, 2018
  
  Hello There,
  
  Thanks for visiting and appreciate your feedback.:-)
  
  Regards,
  Adi
  
  Reply
ปั้มไลค์

July 1, 2020

Like!! I blog frequently and I really thank you for your content. The article has truly peaked my interest.

Reply
Florian

March 17, 2021

Generally I don’t read article on blogs, but I would like
to say that this write-up very pressured me to try and do so!
Your writing style has been amazed me. Thank you, very great article.

Reply
Valentina

August 19, 2021

Very good write-up. I absolutely appreciate this website.

Thanks!

Reply
Brooke

August 24, 2021

Excellent, what a website it is! This website gives
valuable data to us, keep it up.

Reply