ACTIVE - redo log file removed in Oracle

 

10:51:24 SQL> CREATE TABLE test_table (id NUMBER, name VARCHAR2(50));

Table created.

10:51:26 SQL> INSERT INTO test_table VALUES (1, 'Test Data');

1 row created.

10:51:32 SQL>
10:51:33 SQL> commit;

Commit complete.

10:51:36 SQL>
10:51:37 SQL> select * from test_table;

        ID NAME
---------- --------------------------------------------------
         1 Test Data

10:51:44 SQL>
10:51:44 SQL> @redo

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

10:51:49 SQL>
10:51:50 SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

Group   Status                      Member
----- ---------- ---------------------------------------------
    3            /data2/OEMDB/redo03.log
    2            /data2/OEMDB/redo02.log
    1            /data2/OEMDB/redo01.log
    4            /data2/OEMDB/redo04.log
    5            /data2/OEMDB/redo05.log

10:52:02 SQL>
10:53:29 SQL> @redo.sql

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

[oracle@rac201 OEMDB]$ ls -lrt redo05.log
-rw-r-----. 1 oracle oinstall 209715712 Nov 29 10:50 redo05.log


10:53:32 SQL>
10:53:33 SQL> !rm -rf /data2/OEMDB/redo05.log

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


10:55:44 SQL>
10:55:49 SQL> INSERT INTO test_table VALUES (1, 'Test Data');

1 row created.

10:56:01 SQL> commit;

Commit complete.

10:56:05 SQL>
10:56:09 SQL> INSERT INTO test_table select * from test_table;

3 rows created.

10:56:25 SQL> /

6 rows created.

10:56:27 SQL> /

12 rows created.

10:56:28 SQL> /

24 rows created.


10:57:35 SQL> @redo

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

10:57:39 SQL> select count(*) from test_table;

  COUNT(*)
----------
  12582912

10:58:05 SQL>
10:58:13 SQL> INSERT INTO test_table select * from test_table;

12582912 rows created.

10:58:42 SQL> INSERT INTO test_table select * from test_table;
^C
^C

INSERT INTO test_table select * from test_table
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



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=81 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      Checkpoint database and 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_328586723.hm
  
  
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-29T11:08:24.411933+05:30
ARC1 (PID:2634): Master archival failure: error=313
2024-11-29T11:08:24.413201+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc2_2636.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-29T11:08:24.413279+05:30
ARC2 (PID:2636): Master archival failure: error=313
2024-11-29T11:08:24.414441+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc3_2638.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-29T11:08:24.414519+05:30
ARC3 (PID:2638): Master archival failure: error=313
2024-11-29T11:08:24.415710+05:30
Errors in file /u01/app/oracle/oracle_base/diag/rdbms/oemdb/OEMDB/trace/OEMDB_arc0_2630.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



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

11:00:01 SQL>
11:00:03 SQL>
11:04:12 SQL> create table emp (ename varchar2(20));

Table created.

11:04:30 SQL> insert into emp values ( 'AMIT');

1 row created.

11:05:03 SQL> commit;

Commit complete.

11:05:06 SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data2/OEMDB/arch
Oldest online log sequence     51
Next log sequence to archive   53
Current log sequence           55
11:06:01 SQL>


11:10:28 SQL> @redo

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

11:10:33 SQL>
11:10:34 SQL> select count(*) from test_table;

  COUNT(*)
----------
  25165824

11:12:19 SQL>
11:12:44 SQL> select * from emp;

ENAME
--------------------
AMIT


11:10:28 SQL> @redo

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





11:07:48 SQL>
11:07:49 SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;

Database altered.
Note:- When you execute the above command Oracle will create redo05.log file automatically.

11:08:25 SQL>


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 2432695120 bytes
Fixed Size                  8928080 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7847936 bytes
Database mounted.
Database opened.
SQL>
SQL> select count(*) from test_table;

  COUNT(*)
----------
  25165824

The scenario you're describing involves the deletion of the redo05.log file, followed by the execution of the command ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;. 

To understand the potential loss of data and the impact of the steps taken, let’s break it down carefully:

What Happened:

1. Creation of Redo Log Files: The Oracle redo log files (redo01.log, redo02.log, etc.) are crucial for recording changes made to the database. These log files track every change (such as inserts, updates, deletes) made to the data in the database.

2. Deletion of redo05.log: The redo05.log file was deleted while it was marked as ACTIVE in the redo log group, meaning it was still in use for recording database changes. Deleting this file could cause issues with recovery, as Oracle expects this file to be available.

3. Insertions into test_table: You inserted multiple rows into test_table, which would generate redo entries in the active redo log files. These redo entries are necessary for recovering the committed changes if a failure occurs.

4. ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5: This command clears an unarchived redo log group, which means Oracle will "mark" the group as available for reuse. It does not remove committed transactions or roll back changes; it only clears the logs to free up space for new transactions.

Technically Speaking:

• No Data Loss (if committed): Since you executed a COMMIT statement after every insert (including in test_table), the changes were written to the datafiles and are not subject to being lost by clearing the redo log. Committed data (like the rows in test_table) is safe because it has already been written to the datafiles, and Oracle guarantees durability with the redo logs. The role of the redo log is to ensure that changes can be recovered if the database crashes, and once changes are committed, they are persistent.

• Active Log File Deletion Impact: The deletion of redo05.log would cause an issue if the database were trying to write to that log and it couldn't be accessed. However, in your case, once you executed ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;, Oracle cleared the log group, allowing the log to be reused. This action doesn't affect the data that was already written and committed; it only affects unarchived (not yet backed up) redo logs that are required for recovery.

• What was at Risk:

  • Unarchived Redo: If you had any unarchived (not yet backed up) redo data in redo05.log that hadn't been written to the datafiles (but was pending archival), there was a risk of losing that data if the database had crashed. In your case, since the logs were cleared without a crash, no uncommitted changes would be lost.
  • Uncommitted Transactions: If any transaction was still in progress (not committed), it would not be safe. Since you had committed all changes (based on your log), there should be no risk of losing uncommitted data.

Current Database Status:

• Your database is running fine because the necessary actions for recovery (such as clearing the log) were handled properly. Oracle can continue processing without issue as long as the redo logs have been cleared and the database is operating in ARCHIVELOG mode.
• There was no data loss as long as all transactions were committed before the log group was cleared. From the output of the commands, it looks like all your operations on test_table (and other tables) were committed, so no changes were lost.

Data Loss Evaluation:

• Committed Data: Any committed data, such as your entries in test_table, is safe. There has been no loss here.
• Unarchived Redo Log Data: The only data that could have been lost (if there were uncommitted transactions) would be the data that was in-flight in the redo log that was deleted. Since you've been committing frequently, it's highly unlikely that any data was lost.
• Current System Integrity: The system is fine because the database was able to clear the redo log without affecting the committed transactions, and Oracle is handling archiving and log switching properly.

Conclusion:

• Since the command ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5 was executed, and you've committed all the data changes, there is no data loss from the perspective of committed transactions.
• The main concern would have been uncommitted transactions at the time of log deletion, but based on your description, there was no uncommitted data left in redo05.log that could cause a loss.
• Your database is safe, and all committed data (including test_table entries) should be intact.