Tuesday, November 8, 2022

Physical Standby Recovery or Sync Using Recover from Service from 18c

The RECOVER FROM SERVICE is used to recover data guard database which is out of sync or lagging standby. Before 12c the user needs to determine the current SCN, take a backup and apply, start managed recovery of standby and but in 12c they automated these steps but user still needs to perform few manual steps such as control file update. 

This feature has been improved in Oracle 18c and covers all the steps that DBA used to perform manually

Primary : DBPRI
Standby: DBSTB

Steps if the database is Oracle 18c or above

1. Check the Data Guard Broker configuration.

DGMGRL> show configuration
Configuration – dg_config
Protection Mode: MaxPerformance

Members:
dbpri - Primary database
Error: ORA-16724: cannot resolve gap for one or more members

  dbstb - Physical standby database
  Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover: Disabled

Configuration Status:
ERROR (status updated 5 seconds ago)

We can see that both Primary and Physical standby are out of sync

2. Stop Recovery Managed process on the standby

DGMGRL> edit database DBSTB set state=APPLY-OFF;

Or

RMAN> alter database recover managed standby database cancel;

3. Run roll forward command

$rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 11 12:12:48 202
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: DBSTB (DBID=xxxxxxxx)
RMAN> recover standby database from service DBPRI
Starting recover at 11-MAR-22
using target database control file instead of recovery catalog
Oracle instance started…….

media recovery complete, elapsed time: 00:00:00
Finished recover at 05-MAR-22
Executing: alter system set standby_file_management=auto
Finished recover at 05-MAR-22

4. Enable the Recovery Managed process on the standby

DGMGRL> edit database DBSTB set state=APPLY-ON;

Or

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


5. Verify the Data Guard configuration status

DGMGRL> show configuration

Configuration - dg_config
Protection Mode: MaxPerformance

Members:
dbpri - Primary database
  dbstb - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 56 seconds ago)

Now standby Database has been recovered and in sync with the primary.

Thanks & Regards