OPEN Resetlogs, reported ORA-00392 ORA-00312 in oracle12c

Phenomenon

sql> ALTER DATABASE open resetlogs;

ALTER DATABASE open Resetlogs

*

ERROR at line 1:

Ora-00392:log 4 of Thread 1 is being cleared, operation not allowed

Ora-00312:online Log 4 Thread 1: '/mnt/data/pbf/db/proddata/redo04a.log '

Ora-00312:online Log 4 Thread 1: '/mnt/data/pbf/db/proddata/redo04b.log '

"Official explanation"

Clearing_current-current log is being cleared of a closed thread. The log can stay in this status if there are some failure in the switch such as an I/O error writing the new log header.

Clearing-log is being re-created as a empty Log after an ALTER DATABASE CLEAR

"Centralized state of redo log Files"

1.CURRENT-The group (also active state) currently being used by LGWR, which records ongoing changes in the database and deletes will result in data loss at recovery.

2.ACTIVE-Active log file status, log completion switch or wait for checkpoint event may be out of this state, not the current log but the instance recovery is required, at this time the log may have completed the archive, is about to become inactive state, Or the log file waits for the checkpoint event to not complete the archive.

3.INACTIVE-Inactive log state, at which time the log instance is no longer required for recovery and may have been archived.

4.UNUSED-A blank log group that has not recorded the change, typically appears in Add logfile, resetlogs open the database, or use the clear logfile command.

5.CLEARING-Indicates that the group redo log file is being rebuilt in the alter DATABASE CLEAR logfile statement (the post-rebuild state becomes unused).

6.clearing_current-This state occurs when the group redo log file is rebuilt with an I/O failure on the log file.

"Workaround"

Sql> Select Group#,bytes/1024/1024| | ' M ', status from V$log;

group# bytes/1024/1024| | ' M

---------- -----------------------------------------

STATUS

------------------------------------------------

1 1000M

Clearing

2 1000M

Clearing

5 1000M

Clearing

group# bytes/1024/1024| | ' M

---------- -----------------------------------------

STATUS

------------------------------------------------

4 1000M

Clearing_current

3 1000M

Clearing

Sql>

Sql>

Sql>

Sql> ALTER DATABASE clear logfile Group 1;

ALTER DATABASE clear logfile Group 2;

ALTER DATABASE clear logfile Group 3;

ALTER DATABASE clear logfile Group 4;

ALTER DATABASE clear logfile Group 5;

Database altered.

Sql>

Database altered.

Sql>

Database altered.

Sql>

Database altered.

Sql>

Database altered.

Sql> Col group# form 99

sql> Col bytes Form 99999999999

sql> Col Status Form A20

Sql> Select Group#,bytes/1024/1024| | ' M ', status from V$log;

group# bytes/1024/1024| | ' M ' STATUS

------ ----------------------------------------- --------------------

1 1000M UNUSED

2 1000M UNUSED

5 1000M UNUSED

4 1000M Current

3 1000M UNUSED

ALTER DATABASE open resetlogs;

Ok!

OPEN Resetlogs, reported ORA-00392 ORA-00312 in oracle12c