End-to-End Data Guard Recovery: Standby Controlfile Restore, Catalog, Switch Database to Copy, and MRP Restart

 

To perform this operation safely on a Production environment, you must execute these steps in a precise, logical sequence. Since you are modifying the standby controlfile and re-aligning it with existing physical data files on disk, the order of execution is critical to avoid data loss or accidental file overwrites.

Forget your old metadata, learn the current primary database structure, match your existing files, and then apply missing changes

Step-by-Step Production Sequence

Step 1: Stop Redo Apply (MRP)

You must halt the Managed Recovery Process before changing the state of the database or its controlfiles.

• Run on Standby (SQL*Plus):
  sql

  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 2: Shutdown and Restart Standby in NOMOUNT State

To overwrite or restore a new standby controlfile, the database must not have the old controlfile locked or mounted.

• Run on Standby (SQL*Plus):
  sql

  SHUTDOWN IMMEDIATE;
  STARTUP NOMOUNT;

Step 3: Restore the Standby Controlfile from the Primary Service

Connect to RMAN on the standby and pull the fresh metadata directly from the active primary database instance (ORCL).

• Run on Standby (RMAN):
  program

  rman target /
  RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE 'ORCL';
  
  What is a Controlfile?
  The controlfile contains the database metadata:
  Datafile names and locations
  Redo log file names
  Standby redo log information
  PDB information
  Checkpoint SCNs
  Archive log history
  RMAN repository information

Why restore it?

Suppose on Primary:

DROP PLUGGABLE DATABASE PDBORCL01 INCLUDING DATAFILES;

Primary controlfile now knows:

PDBORCL01 does not exist

But standby controlfile still knows:

PDBORCL01 exists

Datafile 14 exists

Now MRP applies redo and sees:

Primary says file removed

Standby says file exists

Result:

ORA-65138

ORA-10458

Therefore we restore the latest standby controlfile from Primary.

After restore:

Primary Metadata = Standby Metadata

Step 4: Mount the Standby Database

Now that the fresh controlfile is in place, mount the database so Oracle can load the primary's structural metadata into memory.

• Run on Standby (RMAN or SQL*Plus):
  sql

  ALTER DATABASE MOUNT;

Step 5: Catalog the Existing Standby Data Files

Because the primary controlfile points to the primary's storage path (e.g., +DATAC1), you must tell RMAN where the actual physical files live on the standby storage (e.g., +DATAC2).

• Run on Standby (RMAN):
  program

  RMAN> CATALOG START WITH '+DATAC2/D2/' NOPROMPT;
  
  (Note: Ensure the trailing slash is included in the path so RMAN scans the directory correctly).

Step 6: Switch the Controlfile Pointers to the Local Files

This step instantly rewrites the newly restored controlfile pointers to use the disk paths discovered during the catalog step.

• Run on Standby (RMAN):
  program

  RMAN> SWITCH DATABASE TO COPY;

Step 7: Roll Forward the Standby via Primary Service (Incremental Sync)

Instead of processing days of archived logs, this command performs an online network-based incremental roll-forward. It synchronizes the SCN gaps by blocks.

• Run on Standby (RMAN):
  program

  RMAN> RECOVER STANDBY DATABASE FROM SERVICE 'ORCL';
  
  Exit RMAN once completed successfully.

Step 8: Clear Standby Redo Logs (Optional but Recommended for Production)

Before starting MRP, it is a production best practice to clear the standby redo logs to prevent archival or synchronization hangs due to mismatched log metadata.

• Run on Standby (SQL*Plus):
  Review your log groups via SELECT GROUP# FROM V$LOGFILE; and clear them:
  sql

  ALTER DATABASE CLEAR LOGFILE GROUP 1;
  ALTER DATABASE CLEAR LOGFILE GROUP 2;
  -- Repeat for all standby redo log groups

Step 9: Restart Managed Recovery Process (MRP)

Start the redo apply engine to keep the standby in continuous real-time synchronization with the primary.

• Run on Standby (SQL*Plus):
  sql

  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
  -- Or if using Real-Time Apply:
  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
  

  
  

🔍 Verification Steps

Once the sequence is complete, run these queries in SQL*Plus on the Standby to confirm health status:

1. Check Data Guard Role and Open Mode:
   sql

   SELECT database_role, open_mode, protection_mode FROM v$database;

Check MRP Status:

sql

SELECT process, status, sequence# FROM v$managed_standby WHERE process LIKE 'MRP%';

 

Oracle Data Guard Switchover and Failover in OCI

Oracle Data Guard is a high availability and disaster recovery solution that maintains one or more synchronized standby databases for a production (primary) database. In Oracle Cloud Infrastructure (OCI), Data Guard operations such as Switchover, Failover, and Reinstate can be performed using the dbaascli utility, making database role transitions simple and automated.

Environment Details

Role	DB Unique Name
Primary Database	ORCL
Standby Database	ORCLDG

Understanding Oracle Data Guard

Oracle Data Guard protects databases against:

• Hardware failures
• Storage failures
• Site outages
• Planned maintenance activities
• Human errors

A Data Guard configuration consists of:

Primary Database

The production database that receives all read-write transactions.

Physical Standby Database

An exact block-by-block copy of the primary database that continuously receives and applies redo from the primary.

Redo Transport Services

Transfers redo generated on the primary to the standby.

Redo Apply Services

Applies redo on the standby database to keep it synchronized.

Switchover

What is a Switchover?

A Switchover is a planned role reversal between the primary and standby databases with zero or minimal downtime and no data loss.

Common Use Cases

• Operating system patching
• Database upgrades
• Exadata maintenance
• Data center migration
• Planned DR testing

Role Transition

Before Switchover:

Primary  : ORCL

Standby  : ORCLDG

After Switchover:

Primary  : ORCLDG

Standby  : ORCL

OCI Data Guard Switchover: ORCL (Primary) to ORCLDG (Primary)

Step 1: Verify Data Guard Association

List the Data Guard associations:

dbscli list-dataguard-associations

Note the Data Guard Association ID.

Step 2: Check Data Guard Details

dbscli get-dataguard-association --dataguard-association-id <dg_id>

Validate:

• Primary Database = ORCL
• Standby Database = ORCLDG
• Protection Mode
• Transport Status
• Apply Status
• Overall Health = SUCCESS

Step 3: Connect to the Current Primary Host and Execute Switchover

dbaascli dataguard switchover \

--dbname ORCL \

--targetStandbyDBUniqueName ORCLDG \

--waitForCompletion true

The command performs:

1. Synchronizes remaining redo.
2. Stops Primary services on ORCL.
3. Converts ORCLDG to Primary.
4. Converts ORCL to Physical Standby.
5. Starts Data Guard services automatically.

Step 5: Monitor Progress

Check operation status:

dbaascli job list

View detailed job information:

dbaascli job getDetails --jobID <job_id>

Step 6: Validate New Roles

SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

DATABASE_ROLE : PRIMARY

OPEN_MODE     : READ WRITE

Step 7: Verify Data Guard Synchronization

Check Data Guard statistics:

SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

===============================================================

FAILOVER and REINSTATE in OCI Data Guard

Objective

Perform a Failover when the Primary database becomes unavailable and then Reinstate the failed Primary database to restore the Data Guard configuration.

Initial Configuration

Before Failover

Primary  : ORCL

Standby  : ORCLDG

Step 1: Perform Failover

When to Use Failover

Failover is performed when:

• Primary database is unavailable.
• Server crash.
• Storage failure.
• Site failure.
• Database cannot be recovered within acceptable business timelines.

Unlike Switchover, Failover is an emergency operation.

Execute Failover

Run the following command on the standby environment:

dbaascli dataguard failover \

--dbname ORCL \

--useImmediateFailover \

--waitForCompletion true

Result of Failover

Before:

ORCL    = Primary

ORCLDG  = Standby

After:

ORCLDG  = Primary

ORCL    = Failed Primary

Validation After Failover

Connect to ORCLDG and verify:

SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

Step 2: Reinstate the Failed Primary

What is Reinstate?

After a Failover, the original Primary database cannot automatically rejoin the configuration.

Reinstate converts the failed Primary database into a Physical Standby and restores Data Guard protection.

Reinstate Works Only If

✅ Flashback Database is enabled

Without Flashback Database, a full standby rebuild may be required.

Current State After Failover

ORCLDG = PRIMARY

ORCL   = FAILED PRIMARY

Goal:

ORCLDG = PRIMARY

ORCL   = PHYSICAL STANDBY

Method A: Reinstate Using DGMGRL

Connect to the database:

dgmgrl /

Check current configuration:

DGMGRL> show configuration;

Example:

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance

  Members:

  ORCLDG - Primary database

  ORCL   - Disabled

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS

Reinstate ORCL:

DGMGRL> reinstate database 'ORCL';

Expected Output:

Reinstating database "ORCL", please wait...

Operation succeeded.

Verify:

DGMGRL> show configuration;

Expected:

ORCLDG - Primary database

ORCL   - Physical standby database

Method B: Reinstate Using dbaascli

Connect to the ORCL server.

Set Oracle environment:

export ORACLE_SID=ORCL

Execute Reinstate:

dbaascli dataguard reinstate \

--dbname ORCL \

--primaryDBUniqueName ORCLDG \

--waitForCompletion true

Note: After failover, ORCLDG is the new Primary. Therefore --primaryDBUniqueName should point to ORCLDG.

Final State After Reinstate

Primary  : ORCLDG

Standby  : ORCL