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