Sunday, 6 November 2022

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

 

Problem Description

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

We restored NON-PROD Database from RMAN full backup(Production) but while opening database it is throwing below error

 

SQL> ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

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

ORA-00312: online log 3 thread 1: '/db1/oraredo/EASYDB/EASYDB_redo_03a.log'

ORA-00312: online log 3 thread 1: '/db1/oraredo/EASYDB/EASYDB_redo_03b.log'

 

Database is in mount stag, and we are not able to drop online redo logfile As it is current file.

 

SQL> SELECT group#,sequence#,status,next_change# FROM V$LOG;

 

GROUP# SEQUENCE# STATUS NEXT_CHANGE#

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

1 0 UNUSED 6.0919E+12

2 0 UNUSED 6.0919E+12

10 0 UNUSED 6.0919E+12

9 0 UNUSED 6.0919E+12

3 0 CLEARING_CURRENT 1.8447E+19

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE DROP LOGFILE GROUP 3

*

ERROR at line 1:

ORA-01623: log 3 is current log for instance EASYDB (thread 1) - cannot drop

ORA-00312: online log 3 thread 1: '/db1/oraredo/EASYDB/EASYDB_redo_03aa.log'

ORA-00312: online log 3 thread 1: '/db1/oraredo/EASYDB/EASYDB_redo_03bb.log'

 

To fix this issue, we renamed group3 where space is available and created dummy files

 

a) rename problematic online redo file

alter database rename file '/db1/oraredo/EASYDB/EASYDB_redo_03aa.log' to '/db1/orafra/EASYDB/EASYDB_redo_03aa.log';

alter database rename file '/db1/oraredo/EASYDB/EASYDB_redo_03bb.log' to '/db1/orafra/EASYDB/EASYDB_redo_03bb.log';

 

b) create dummy file using os touch command 

{orauat@EASYSERVER:db1/orafra/EASYDB}bdf .

{orauat@EASYSERVER:db1/orafra/EASYDB}touch EASYDB_redo_03bb.log

{orauat@EASYSERVER:db1/orafra/EASYDB} touch EASYDB_redo_03aa.log

{orauat@EASYSERVER:/db1/oraredo/EASYDB}sqlplus

 

SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 6 23:55:11 2022

 

SQL> show parameter control

 

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     21

control_files                        string      /db1/oraredo/EASYDB/control/cont

                                                 rol01.ctl, /db1/orafra/EASYDB/co

                                                 ntrol/control02.ctl

control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> !ls -lr /db1/oraredo/EASYDB/control/control01.ctl

-rw-r-----   1 orauat     oinstall   88719360 Nov  7 00:05 /db1/oraredo/EASYDB/control/control01.ctl

 

SQL> !ls -ltr /db1/orafra/EASYDB/control/control02.ctl

-rw-r-----   1 orauat     oinstall   88719360 Nov  7 00:05 /db1/orafra/EASYDB/control/control02.ctl


c) clear problematic online redo log file 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 

SQL>  alter database clear unarchived logfile group 3;

 

Database altered.

 d) check status , it is changed now

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log order by first_change# ;

 

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#

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

         9          1          0          2 YES UNUSED              6.0919E+12

        10          1          0          2 YES UNUSED              6.0919E+12

         1          1          0          2 YES UNUSED              6.0919E+12

         2          1          0          2 YES UNUSED              6.0919E+12

         3          1          0          2 NO  CURRENT             6.0919E+12

 

e) started database with resetlogs option and stop and start gracefully

SQL> alter database open resetlogs;

Database altered.

SQL>

SQL> select name,open_mode from v$database;

 

NAME      OPEN_MODE

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

EASYDB      READ WRITE

 

SQL>

 

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

EASYDB      READ WRITE

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size                 19289376 bytes

Variable Size            5502929632 bytes

Database Buffers         3053453312 bytes

Redo Buffers               14262272 bytes

Database mounted.

Database opened.

SQL>


select name,open_mode from v$database;


No comments:

Post a Comment