INACTIVE - Redo log file removed in Oracle

 

Deleted an "Inactive" Redo Log File
"Inactive" redo logs are no longer needed for recovery because their contents are already archived.
You can resolve the issue by clearing the log group:

ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;

SQL> create table emp (ename varchar2(20));

Table created.

SQL> insert into emp values ( 'AMIT');

1 row created.

SQL> commit;


SQL> insert into emp values ('PRIYA');

1 row created.

SQL> insert into emp values ('SAMIK');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

ENAME
--------------------
AMIT
PRIYA
SAMIK

SQL> set time on
12:31:25 SQL>
12:31:25 SQL>
12:31:25 SQL> @redo.sql

                                                                            Size
Group                    Member                     Archived     Status     (MB)
----- --------------------------------------------- ---------- ---------- ------
    1 /data2/OEMDB/redo01.log                       NO         CURRENT       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                       YES        INACTIVE      200

12:31:28 SQL>
12:31:29 SQL>
12:31:30 SQL> !rm -rf /data2/OEMDB/redo05.log


12:32:23 SQL> @ts_size

TABLESPACE_NAME                USED_SPACE_IN_GB FREE_SPACE_IN_GB TOTAL_SIZE_IN_GB % Free Space
------------------------------ ---------------- ---------------- ---------------- -------------
SYSAUX                                      .79            31.21               32         97.55
SYSTEM                                     1.86            30.14               32         94.19
TEMP                                          0               32               32           100
UNDOTBS1                                     .2             31.8               32         99.38
USERS                                         0               32               32         99.99



[oracle@rac201 OEMDB]$
[oracle@rac201 OEMDB]$ ls -rlt redo05.log
ls: cannot access 'redo05.log': No such file or directory


12:34:13 SQL>
12:34:13 SQL> insert into emp select * from emp;

3 rows created.

12:34:22 SQL> /

6 rows created.

12:34:23 SQL> /

12 rows created.

12:34:23 SQL> /

24 rows created.


12582912 rows created.

12:34:43 SQL> commit;

25165824 rows created.


Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc0_3708.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-29T12:47:46.053057+05:30
ARC0 (PID:3708): Master archival failure: error=313
2024-11-29T12:47:46.054471+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc1_3712.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-29T12:47:46.054553+05:30
ARC1 (PID:3712): Master archival failure: error=313
2024-11-29T12:47:46.055668+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc2_3714.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



RMAN> advise failure;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of advise command at 11/29/2024 12:41:44
RMAN-07211: failure option not specified

Note:- You need to run first - List Failure and then need to run advise failure.

RMAN> LIST FAILURE;

Database Role: PRIMARY

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

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

RMAN>

RMAN>

RMAN> ADVISE FAILURE;

Database Role: PRIMARY

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

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

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
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_3882578597.hm


12:47:43 SQL>
12:47:43 SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;

Database altered.

12:47:47 SQL> exit

2024-11-29T12:47:47.395622+05:30
Thread 1 advanced to log sequence 71 (LGWR switch),  current SCN: 2280088
  Current log# 5 seq# 71 mem# 0: /data2/OEMDB/redo05.log
2024-11-29T12:47:47.415229+05:30
Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5
2024-11-29T12:47:48.129214+05:30



Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
[oracle@rac201 OEMDB]$
[oracle@rac201 OEMDB]$ ls -lrt redo05.log
-rw-r-----. 1 oracle oinstall 209715712 Nov 29 12:48 redo05.log
[oracle@rac201 OEMDB]$

                                                                            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