OLD - Recover Standby Database Using Incremental Backup - Resolve Log Gap - Oracle

 

Recovering a standby database using an incremental backup is a practical way to resolve log gaps when redo or archive logs are missing in an Oracle Data Guard environment. 

This method avoids a full rebuild and minimizes downtime. 

Below is a comprehensive step-by-step guide with commands and scenarios.

---

Scenario:

A standby database might lag behind the primary for various reasons like:

Problem:

• Unavailability of or insufficient network bandwidth between primary and standby database
• Unavailability of Standby database
• Corruption / Accidental deletion of Archive Redo Data on primary

•  The standby database is out of sync due to missing archive logs, and redo shipping cannot bring it back in sync.

• Solution: Use incremental backups from the primary database to recover the standby.

---

Prerequisites:

• Primary and standby databases must be accessible.

• Standby must be in a mounted state.

• Archive logs should not be recoverable, making an incremental backup necessary.

---

Step-by-Step Recovery

Step 1: Identify the Gap on the Standby

Connect to the Standby Database and check the current applied log sequence.

SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Identify the last applied log sequence. For example:

• Last applied: 1000

• Current log sequence on primary: 1050

---

Step 2: Place Standby in Recovery Mode

Ensure the standby database is in the MOUNT state:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

---

Step 3: Create an Incremental Backup on the Primary

On the primary database, create an incremental backup starting from the SCN of the standby.

1. Check the standby's current SCN:

   SQL> SELECT CURRENT_SCN FROM V$DATABASE;

   Example result: 400000

2. On the primary, take an incremental backup starting from this SCN:

   
   rman target /
   RMAN> BACKUP INCREMENTAL FROM SCN 400000 DATABASE FORMAT '/backup/incr_standby_%U';
   

   This creates incremental backup files that can be used to recover the standby.

---

Step 4: Transfer Backup Files to Standby

Transfer the incremental backup files to the standby server using scp or a shared location:

$] scp /backup/incr_standby_* oracle@standby:/backup/

---

Step 5: Catalog Backup Files on Standby

On the standby, inform RMAN about the incremental backup files:

rman target /
RMAN> CATALOG START WITH '/backup/incr_standby_';

---

Step 6: Apply Incremental Backup

Apply the incremental backup to the standby database:

RMAN> RECOVER DATABASE NOREDO;

This command applies the changes captured in the incremental backup to bring the standby database closer to the primary database’s SCN.

---

Step 7: Synchronize Datafiles with Controlfile

The standby controlfile may still have outdated information about the datafiles. To resolve this:

1. On the primary database, create a fresh standby controlfile:

   
   $] rman target /
   RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/standby_control.ctl';
   

2. Transfer the controlfile to the standby server:

   $ scp /backup/standby_control.ctl standby:/backup/;

3. .Replace the current controlfile on the standby

4. SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP NOMOUNT;
   SQL> RESTORE STANDBY CONTROLFILE FROM '/backup/standby_control.ctl';
   SQL> ALTER DATABASE MOUNT;
   

---

Step 8: Restart Managed Recovery

Start the managed recovery process on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

---

Step 9: Verify Synchronization

On the primary and standby databases, check the archive logs and log sequence:

• On the primary:

  SQL> ARCHIVE LOG LIST;

• On the standby:

  
  SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
  

Ensure the standby has caught up with the primary.

---

Alternate Scenarios

1. Missing Files During Transfer: If files are missing during transfer, re-transfer only the missing files and re-catalog them on the standby.

2. Corrupt Backup Files: If incremental backup files are corrupt, take a fresh incremental backup starting from the same SCN.

3. Redo Log Shipping Resumes: If redo log shipping resumes after applying the incremental backup, the standby database will automatically continue to apply logs.

---

Preventive Measures

• Configure multiple archive destinations to avoid log gaps:

  SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/archive2/';

• 
  
• Regularly monitor Data Guard:

  SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST_STATUS;

• 
  

---

This process efficiently resolves log gaps and brings the standby back into sync with the primary database. Let me know if you need further clarification!