Lesson 4	Clearing Corrupt Online Redo Log Files
Objective	Describe the Application of Syntax to Clear Redo Log Files

Clearing Corrupt Online Redo Log Files in Oracle

The ALTER DATABASE CLEAR LOGFILE statement in Oracle 19c is used to clear corrupt or inactive online redo log files that are no longer needed for instance recovery. This command reinitializes the redo log file, allowing it to be reused without requiring a full database shutdown. It is critical when redo log files are marked as "STALE" or unusable due to corruption. Below is a detailed guide on its usage, including steps, warnings, and modern considerations.
Purpose of ALTER DATABASE CLEAR LOGFILE

Clear corrupted online redo log files that are not needed for instance recovery.
Reinitialize the redo log file for reuse without manual intervention.
Handle scenarios where redo log files are unusable due to corruption.

Syntax

ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;

For unarchived logs:

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>;

For logs needed for unrecoverable datafile recovery:

ALTER DATABASE CLEAR LOGFILE GROUP <group_number> UNRECOVERABLE DATAFILE;

Use Cases in Oracle 19c

Corrupted Inactive Redo Log: Clear a corrupt redo log file that is not part of the current redo log group.
Unarchived Logs: Clear unarchived redo logs, noting that this may compromise recoverability.
Unrecoverable Datafile: Clear a redo log needed for a datafile that cannot be brought online or restored, marking the datafile as unrecoverable (e.g., when a datafile is offline and its recovery depends on a corrupt, unarchived redo log).

Steps for Clearing a Corrupt Redo Log File

Check the Status of Redo Log Groups: Use the following query to identify the corrupt redo log group:
```
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
SELECT GROUP#, STATUS FROM V$LOG;
    
```
- INACTIVE: Safe to clear.
- CURRENT: Cannot be cleared as it is actively being written to.
- ACTIVE: Must be archived or switched out before clearing.

Clear the Corrupt Log:

ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;

Special Case - Unarchived Redo Logs: If the log group has not been archived:
```
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>;
    
```
Special Case - Unrecoverable Datafile: If the redo log is needed for a datafile that cannot be recovered:
```
ALTER DATABASE CLEAR LOGFILE GROUP <group_number> UNRECOVERABLE DATAFILE;
    
```

Warning: Clearing unarchived redo logs or using UNRECOVERABLE DATAFILE may result in data loss. Always perform a full database backup, including the control file, after issuing these commands.
Important Considerations in Oracle 19c

ARCHIVELOG Mode: In ARCHIVELOG mode, ensure the corrupt log is archived before clearing to avoid data loss. Force archiving if necessary.
Loss of Recoverability: Clearing unarchived redo logs prevents recovery of transactions in that log. Use this option only when no alternative recovery strategies exist.
Redo Log Multiplexing: If redo logs are multiplexed, verify that other members of the group are intact before clearing the corrupt log.
Automatic Diagnostic Repository (ADR): Oracle 19c’s ADR provides diagnostic information for corrupt redo logs, aiding in determining the best recovery approach.

Example

-- Identify the redo log groups and their status
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
SELECT GROUP#, STATUS FROM V$LOG;

-- Clear the corrupt log group (e.g., Group 2)
ALTER DATABASE CLEAR LOGFILE GROUP 2;

-- If unarchived, use this:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

-- If needed for an unrecoverable datafile:
ALTER DATABASE CLEAR LOGFILE GROUP 2 UNRECOVERABLE DATAFILE;

Modern Alternatives in Oracle 19c If clearing the redo log is not feasible (e.g., for a current log), consider:

Drop and Recreate the Log Group:

ALTER DATABASE DROP LOGFILE GROUP <group_number>;
ALTER DATABASE ADD LOGFILE GROUP <group_number> ('<path>') SIZE <size>;

Restore and Recover: Use backups and archived redo logs for media recovery, or perform incomplete recovery (RECOVER DATABASE UNTIL CANCEL) if unarchived logs are lost.

Conclusion: The ALTER DATABASE CLEAR LOGFILE command is a powerful tool in Oracle 19c for managing corrupt redo log files. Use it cautiously, especially with UNARCHIVED or UNRECOVERABLE DATAFILE options, to avoid compromising database recoverability. Features like multiplexing and ADR enhance recovery options in modern Oracle environments.

Media Failure Damages the Online Redo Log

If a media failure damages online redo log files, consider:

Is the media failure temporary or permanent?
Which online redo log is damaged (current, active, or inactive)?
Has it been archived?

In most cases, corrupt redo logs can be cleared using:

ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;

For unarchived logs or unrecoverable datafiles, use UNARCHIVED or UNRECOVERABLE DATAFILE options, but be cautious of data loss. View the slideshow below for steps to clear damaged redo logs.

Steps to Clear Damaged Online Redo Log Files

1) If a media failure happens, check if the media failure is permanent or temporary.

2) If the media failure is temporary, simply have the hardware problem fixed.

3) If the media failure is permanent, check if the database is open. If not, mount the database.

4) Check if the damaged online redo log is archived — 4) The flowchart provides a structured decision-making process for handling damaged online redo log files in an Oracle database:

**Identifying Media Failure**

Check if the **media failure is permanent**.

**If No**: The issue is temporary, and the Log Writer (LGWR) will automatically reuse the previously unavailable log file when required.

**If Yes**: Proceed to the next step.

**Checking Database State**

Check if the **database is open**.

**If No**: Start the database in mount mode.

**If Yes**: Move to the next step.

**Checking If Online Redo Logs are Archived**

Check whether the **online redo logs are archived**.

**If No**: Use `ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>;` to clear the unarchived redo log, noting potential data loss.

**If Yes**: Use `ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;` to clear the archived redo log safely.

**Key Takeaways:**

For temporary hardware issues, Oracle’s LGWR automatically handles redo log files.

If the database is not open, start it in mount mode before recovery actions.

Use `ALTER DATABASE CLEAR UNARCHIVED LOGFILE` for unarchived logs or `ALTER DATABASE CLEAR LOGFILE` for archived logs, based on the archiving status.

This flowchart aids Oracle DBAs in managing corrupt redo log files effectively.

5) If the damaged online redo log is archived, use the ALTER DATABASE CLEAR LOGFILE <group_number>; command — 5) If the damaged online redo log is archived, use:
ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;

Corrupted Online Redo Logs for Offline Datafiles

If an offline datafile requires a damaged, unarchived online redo log for recovery, use:

ALTER DATABASE CLEAR LOGFILE GROUP <group_number> UNRECOVERABLE DATAFILE;

This renders the offline datafile unusable, requiring you to drop the datafile and its tablespace. To avoid this:

Bring the offline datafile online.
Restore all datafiles.
Perform an incomplete recovery prior to the corrupted redo log.

Always back up the database after clearing redo logs to ensure future recoverability. The next lesson explains how to recover a database with inactive redo logs.