Disclaimer

Thursday, 28 November 2024

Current Redo log file dropped in Oracle - ORA-00313-ORA-00312-ORA-27041

 

STATUS in V$LOG:

  • CURRENT: The log group is currently being used.
  • ACTIVE: The log group is required for instance recovery.
  • INACTIVE: The log group is no longer needed for recovery and can be cleared

Initial State of Redo Log Files

The database has five redo log groups, each with a single member. Among them:

  • Groups 1 to 4 are archived and in the INACTIVE state.
  • Group 5 is the current log group (CURRENT) and is actively being written to by the database.

SQL> @redo

                                                                            Size
Group                    Member                     Archived     Status     (MB)
----- --------------------------------------------- ---------- ---------- ------
    1 /data2/OEMDB/redo01.log                       YES        INACTIVE      200
    2 /data2/OEMDB/redo02.log                       YES        INACTIVE      200
    3 /data2/OEMDB/redo03.log                       YES        INACTIVE      200
    4 /data2/OEMDB/redo04.log                       YES        INACTIVE      200
    5 /data2/OEMDB/redo05.log                       NO         CURRENT       200

SQL>


Deletion of the Current Redo Log File

The command !rm -rf /data2/OEMDB/redo05.log removes the physical redo log file for Group 5 from the filesystem


SQL> !rm -rf  /data2/OEMDB/redo05.log

SQL> @redo

                                                                            Size
Group                    Member                     Archived     Status     (MB)
----- --------------------------------------------- ---------- ---------- ------
    1 /data2/OEMDB/redo01.log                       YES        INACTIVE      200
    2 /data2/OEMDB/redo02.log                       YES        INACTIVE      200
    3 /data2/OEMDB/redo03.log                       YES        INACTIVE      200
    4 /data2/OEMDB/redo04.log                       YES        INACTIVE      200
    5 /data2/OEMDB/redo05.log                       NO         CURRENT       200







SQL>
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.


I was getting below alerts in alert log file continuously :)
Additional information: 3
2024-11-28T19:38:52.667390+05:30
ARC0 (PID:4983): Master archival failure: error=313
2024-11-28T19:38:52.668593+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc1_4987.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/data2/OEMDB/redo05.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2024-11-28T19:38:52.668672+05:30
ARC1 (PID:4987): Master archival failure: error=313
2024-11-28T19:38:52.669838+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc2_4989.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/data2/OEMDB/redo05.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2024-11-28T19:38:52.669959+05:30
ARC2 (PID:4989): Master archival failure: error=313
2024-11-28T19:38:52.671234+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc3_4991.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/data2/OEMDB/redo05.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2024-11-28T19:38:52.671313+05:30
ARC3 (PID:4991): Master archival failure: error=313
2024-11-28T19:38:52.672825+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc0_4983.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/data2/OEMDB/redo05.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2024-11-28T19:38:52.673019+05:30
ARC0 (PID:4983): Master archival failure: error=313
2024-11-28T19:38:52.674613+05:30


I checked the "LIST FAILURE"

RMAN> LIST FAILURE;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
662        CRITICAL OPEN      28-NOV-24     Online log group 5 is unavailable
665        HIGH     OPEN      28-NOV-24     Online log member /data2/OEMDB/redo05.log is missing

Executed - ADVISE FAILURE:-
RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
662        CRITICAL OPEN      28-NOV-24     Online log group 5 is unavailable
665        HIGH     OPEN      28-NOV-24     Online log member /data2/OEMDB/redo05.log is missing

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /data2/OEMDB/redo05.log was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Clear unarchived redo log group 5
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/hm/reco_1257920248.hm

RMAN>

RMAN>

RMAN> exit


Recovery Manager complete.
[oracle@rac201 OEMDB]$
[oracle@rac201 OEMDB]$ cat /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/hm/reco_1257920248.hm
   # clear unarchived log group
   sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/hm/reco_466279785.hm'' ); end;";
[oracle@rac201 OEMDB]$


rman advisor suggested to Clear unarchived redo log group 5 :-

Clearing the Unarchived Redo Log Group

Since the missing redo log cannot be restored, the ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5 command is issued. This command removes the references to the missing redo log file from the control file and allows the database to reuse the log group.

Key Note: Clearing an unarchived log file may result in data loss if the log contained transactions that were not yet written to the datafiles. This step should be taken only if you are certain that the missing log file is irrecoverable.


SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;

Database altered
ARC1 (PID:4987): Archived Log entry 15 added for T-1.S-24 ID 0x32dc164a LAD:1
2024-11-28T19:38:52.985544+05:30
ARC2 (PID:4989): Archived Log entry 16 added for T-1.S-25 ID 0x32dc164a LAD:1
2024-11-28T19:38:53.006242+05:30
ARC3 (PID:4991): Archived Log entry 17 added for T-1.S-26 ID 0x32dc164a LAD:1
Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5



  SELECT GROUP#, STATUS, THREAD#, SEQUENCE#, ARCHIVED FROM V$LOG
  2  /

Group   Status      THREAD#  SEQUENCE# Archived
----- ---------- ---------- ---------- ----------
    1 INACTIVE            1         24 YES
    2 INACTIVE            1         25 YES
    3 INACTIVE            1         26 YES
    4 INACTIVE            1         27 YES
    5 CURRENT             1         28 NO


SQL> SELECT GROUP#, STATUS, ARCHIVED, THREAD#
FROM V$LOG
WHERE GROUP# = 5;  2    3

Group   Status   Archived      THREAD#
----- ---------- ---------- ----------
    5 CURRENT    NO                  1

SQL> SELECT GROUP#, STATUS, MEMBER
FROM V$LOGFILE
WHERE GROUP# = 5;  2    3

Group   Status                      Member
----- ---------- ---------------------------------------------
    5            /data2/OEMDB/redo05.log
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

How to recovery PDB when PDB database is dropped in Oracle

  How to recovery PDB when PDB database is dropped :) [oracle@rac01 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 21.0.0.0.0 - Product...