CURRENT redo log file delated at OS level - only one member in the group - Scenario Recap

 

Scenario Recap

• You have 5 redo log groups.
• Each group has only one member (no multiplexing).
• The OS team accidentally deleted the "CURRENT" redo log file.

The redo log file labeled "CURRENT" is actively being written to by the database. It contains ongoing and uncommitted transactions. If it's deleted, the database will encounter issues during the next log switch or checkpoint.

---

1. What recovery do I need to perform?

You must recover the database because the "CURRENT" redo log file contains crucial data that hasn't been written to the data files. Since it is deleted, Oracle cannot proceed with normal operations.

You may need to:

• Shut down the database.
• Restore the missing redo log file or reset the redo logs.
• Perform recovery to ensure no data corruption.

What recovery do I need to do?

When the "CURRENT" redo log file is deleted, Oracle will encounter a critical error when it tries to write to the missing file. The database will likely crash or hang. To recover:

1. You need to stop the database (SHUTDOWN IMMEDIATE).
2. Bring it to MOUNT mode (STARTUP MOUNT).
3. Either clear the missing redo log (CLEAR UNARCHIVED LOGFILE) or restore it from a backup.

---

2. How will I lose transactions?

Let’s use an example:

Practical Setup:

1. Create a table and insert some data:

   SQL> CREATE TABLE emp (id NUMBER, name VARCHAR2(50));
   SQL> INSERT INTO emp VALUES (1, 'John');
   SQL> INSERT INTO emp VALUES (2, 'Jane');
   SQL> COMMIT; -- These transactions are committed.
   

2. Insert new data but don’t commit it:

   SQL> INSERT INTO emp VALUES (3, 'Alice'); -- Uncommitted transaction
   SQL> INSERT INTO emp VALUES (4, 'Bob');   -- Uncommitted transaction
   

3. At this point, the "CURRENT" redo log contains:

   • Committed transactions (John and Jane).
   • Uncommitted transactions (Alice and Bob).

Impact:

• If the "CURRENT" redo log is deleted:
• Committed transactions are safe. They are already written to the data files.
• Uncommitted transactions are lost. They were in the redo log and not yet written to the data files.

2. How will I lose my transactions?

You will lose:

• Uncommitted transactions in the "CURRENT" redo log, as they were only in memory and the deleted log file.
• Committed transactions in the "CURRENT" log if the log was not archived yet.

Practical Example:

1. Create a table and commit a transaction:

   CREATE TABLE test_recovery (id NUMBER, name VARCHAR2(50));
   INSERT INTO test_recovery VALUES (1, 'Committed');
   COMMIT;
   

   This transaction is safe, as it’s committed and likely written to previous redo logs.

2. Insert another record without committing:

   INSERT INTO test_recovery VALUES (2, 'Uncommitted');

   If the redo log holding this change is deleted, this transaction is lost forever.

3. Insert a committed transaction after the uncommitted one:

   INSERT INTO test_recovery VALUES (3, 'Committed After Loss');
   COMMIT;
   

   If the redo log containing this transaction was not archived, it will also be lost.

---

3. Do I really need to perform point-in-time recovery?

No, you generally don’t need to perform a point-in-time recovery unless:

• There is corruption or loss of critical data.
• The redo logs are unrecoverable.

You can usually reset the logs or perform standard recovery.

Do I need to perform point-in-time recovery?

Point-in-time recovery (PITR) is only required if:

• You cannot clear the missing log (e.g., it contains unarchived transactions crucial to the database).

If the missing log is cleared:

• PITR is unnecessary, but uncommitted and unarchived changes are lost.

---

4. Do I need to stop the database and start in mount stage?

Yes. You need to:

1. Shut down the database:

   SQL> SHUTDOWN IMMEDIATE;

2. Start it in mount mode:

   SQL> STARTUP MOUNT;

This is necessary because redo log management happens at the mount stage.

Do I need to stop the database and start in MOUNT stage?

• Yes, for a "CURRENT" log, the database must be in MOUNT mode to clear or reset the redo log. This is because the log is actively used, and clearing it in an open database would disrupt transactions.

---

5. What does "CLEAR UNARCHIVED LOGFILE" mean?

This command clears the damaged redo log files, making them usable again, but data in the log may be lost.

Example:

• If you clear an unarchived redo log file, any uncommitted transactions in that file will be lost.

What does "CLEAR UNARCHIVED LOGFILE" mean?

This command removes references to the deleted log, allowing Oracle to reuse the log group. However:

• Unarchived transactions in the log are lost forever.

---

6. Do I need to "CLEAR UNARCHIVED LOGFILE" when the database is running or stopped?

You must clear the log file when the database is mounted but not open.

 Do I need to "CLEAR UNARCHIVED LOGFILE" while DB is running or after shutdown?

• You must shutdown the database and bring it to MOUNT mode to execute this command.

---

7. What is the impact of "CLEAR UNARCHIVED LOGFILE"?

Example Impact:

1. If you clear an unarchived redo log file:

   
   SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
   

2. Impact:
   • Committed transactions in other log files are safe.
   • Uncommitted transactions in the cleared redo log are lost.

Layman Explanation:

Think of the redo log as a diary. If you accidentally tear out a page before archiving it, you lose everything written on that page.

Impact of "CLEAR UNARCHIVED LOGFILE"

• Impact: Any unarchived transactions in the missing log are lost.
• Example:
1. Missing redo log contains:
   • Committed transaction: INSERT INTO test_recovery VALUES (3, 'Committed After Loss'); COMMIT;
   • Uncommitted transaction: INSERT INTO test_recovery VALUES (2, 'Uncommitted');
2. After clearing the log, both transactions are gone.

---

8. Do I need to restore the 10TB database?

No, you do not need to restore the entire 10TB database unless:

• The database has corruption.
• You lose all redo log files.

Strategy:

• Fix the redo logs (e.g., recreate or clear them).
• Perform recovery to ensure database consistency.

Do I need to restore the entire 10TB database?

• No, you don’t need to restore the entire database unless the redo log loss has caused unrecoverable corruption.

Strategy:

• If you have a backup of the redo logs or multiplexed copies, restore only the missing log.
• If not, clear the log and accept the loss of unarchived changes.

---

9. Layman Explanation (Step-by-Step)

Imagine your redo log files are like flight recorders for a plane. If the recorder for a current flight (CURRENT redo log) is destroyed:

1. You lose information about what happened on that flight (uncommitted transactions).
2. Other flights (committed transactions) are safe because their information was already saved elsewhere (data files).

When a redo log is deleted:

• Uncommitted transactions in that log are lost.
• You must fix or reset the logs to let the database operate again.

---

10. Simplified Recovery Steps

1. Check which redo log file is missing:

   SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

2. Shut down the database:

   SQL> SHUTDOWN IMMEDIATE;

3. Start in mount mode:

   SQL> STARTUP MOUNT;

4. Clear the missing redo log file:

   SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

5. Open the database:

   SQL> ALTER DATABASE OPEN;

6. If needed, recreate the redo log group:

   SQL> ALTER DATABASE ADD LOGFILE GROUP 1 '/path/to/new_redo01.log' SIZE 200M;

---

Conclusion

The key to avoiding such issues is:

1. Always multiplex redo logs (at least 2 members per group).
2. Ensure regular backups with archiving enabled.
3. Understand the difference between committed and uncommitted transactions.

 Summary of Steps

If "CURRENT" Log is Deleted:

1. Shutdown Database:

   SHUTDOWN IMMEDIATE;

2. Start in MOUNT Mode:

   STARTUP MOUNT;

3. Clear Missing Log:

   ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>;

4. Open Database:

   ALTER DATABASE OPEN;

5. Monitor Data Loss:
• Check for missing transactions in application logs or DBA_DATA_FILES.

---

Eternal Lesson

1. Multiplex redo logs to prevent data loss.
2. Backup regularly to restore lost files without clearing.
3. Use CLEAR UNARCHIVED LOGFILE cautiously, as it guarantees some data loss.