Rolling Forward a Physical Standby Database Using the RECOVER Command

A standby database is a transactionally-consistent copy of the production database. It enables production Oracle database to survive disasters and data corruption. If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch a standby database to the production role, minimizing the downtime associated with the outage. Moreover, performance of production database can be improved by offloading resource-intensive backup and reporting operations to standby systems. As you can see, it’s always desirable to have standby database synchronized with the primary database.

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

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

If standby database lags behind the primary database:

• Switchover will take more time.
• Failover will result in data loss.
• An attempt to issue Real Time Query against the standby database will result in error
  ORA-03172 : STANDBY_MAX_DATA_DELAY of n seconds exceeded.

Synchronizing the standby and primary databases can be done by copying and applying the archived logs from the primary database but this process is quite time consuming as it will first apply both the COMMITED and the NON COMMITED transactions followed by rolling back uncommitted transactions. Employing incremental backups of the primary database containing changes since the standby database was last refreshed is a faster alternative which will recover the standby database much faster as it will apply only the COMMITED transactions on the standby database. Moreover, incremental backups are also useful in cases when there are missing archived logs on Primary which have not been applied to the standby database

Prior to 12c, in order to roll forward the standby database using incremental backups you would need to:

• Create a control file for the standby database on the primary database.
• Take an incremental backup on the primary starting from the SCN# of the standby database.
• Copy the incremental backup to the standby host and catalog it with RMAN.
• Mount the standby database with newly created standby control file.
• Cancel managed recovery of the standby database and apply incremental backup to the standby database.
• Start managed recovery of standby database.

In 12c, this procedure has been dramatically simplified. Now you can use the RECOVER … FROM SERVICE command to synchronize the physical standby database with the primary database.  This command does the following:

• Creates an incremental backup containing the changes to the primary database. All changes to data files on the primary database, beginning with the SCN in the standby data file header, are included in the incremental backup.
• Transfers the incremental backup over the network to the physical standby database.
• Applies the incremental backup to the physical standby database.

This results in rolling forward the standby data files to the same point-in-time as the primary. However, the standby control file still contains old SCN values which are lower than the SCN values in the standby data files. Therefore, to complete the synchronization of the physical standby database, the standby control file needs to be refreshed to update the SCN#.

Now I will demonstrate the steps to refresh the physical standby database with changes made to the primary database using the RECOVER…FROM SERVICE command.

Overview:

• View current configuration and verify that primary database, far sync for primary and standby database are in sync
• Simulate loss of archived logs on the primary database
• Refresh the physical standby using the RECOVER…FROM SERVICE command

Setting up the example

View Current configuration

It can be verified that primary database (boston), far sync for primary (bostonFS) and physical standby (london) are in sync.

Simulate loss of archived logs on primary database (boston)

Let’s stop redo transport from primary (boston) and switch logs on primary so that far sync (bostonFS ) and physical standby (london) lag behind primary (boston).

We can verify that logs are not being transported to far sync bostonFS and standby london. Although sequence# of the latest archived log is 194 on primary, archived logs up to sequence# 191 only have reached far sync and physical standby.

Next we’ll find out the names of archived logs generated on primary which have not been transported to standby:

In a real-time environment, archived logs on primary could be lost due to:

• their deletion if archivelog deletion policy has not configured  properly
• their corruption

In our experimental setup, I’ll  simulate loss of archived logs on primary by renaming them:

Now even if we restart redo transport from primary, gap in redo logs on far_sync / standby cannot be resolved as some logs are missing on primary.

It can be verified that SCN# (3717618) of standby (london) is lagging behind that (3718999) of the primary (boston)

Refresh the physical standby using the RECOVER…FROM SERVICE command

First of all let us identify the datafiles on standby database which are out of sync with respect to primary.

On  checking  checkpoint_change# in datafile headers on primary (boston) and standby (london), we note that whereas checkpoint_change# of datafiles 5,7,8,9,10,11 match on primary and standby, for rest of the  datafiles (1,3,4,6) standby is lagging behind  primary.

In order to synchronize the standby we will stop the managed recovery processes on the physical standby database and place the physical standby database in MOUNT mode.

Start RMAN and connect as target to the physical standby database. Refresh the data files on the physical standby database by using an incremental backup of the data files on the primary database.

The following command creates a compressed multi-section incremental backup on the primary database that is then used to refresh the standby data files. boston is the net service name of the primary database that is used to refresh the standby database. The NOREDO clause specifies that the archived redo log files must not be applied during recovery.

— Catalogues all the existing backups and archivelogs available on standby —

— Backups —

— Archived logs —

— Skips all the datafiles (5, 7, 8, 9, 10, 11) whose checkpoint_change# on standby match that of primary are skipped for restore operation —

— Restores multi-section compressed backupsets    with section size of 100m of all the data files (1, 3, 4, 6) whose checkpoint_change# on standby is behind  primary  over network. —

As soon as standby is recovered, redo transport to far sync (bostonFS) and standby (london) is resumed.

The RECOVER … FROM SERVICE command refreshes the standby data files and rolls them forward to the same point-in-time as the primary. However, the standby control file still contains old SCN values which are lower than the SCN values in the standby data files. Therefore, to complete the synchronization of the physical standby database, we must refresh the standby control file to update the SCN#.

Hence place the physical standby database in NOMOUNT mode and restore controlfile to standby by using the control file on the primary database.

Mount the standby database.

Now the standby database has been rolled forward successfully as can be verified using DGMGRL.

Note: If network resources are a constraint, then you can still use the Pre-12c BACKUP INCREMENTAL command to create incremental backups on the primary database, and then use the incremental backups to roll forward the physical standby database.

Conclusion:

In case standby database lags behind the primary, it can be rolled forward using incremental backups from primary database. It is a faster alternative to copying archived logs from primary and applying them on standby database. When an archive log gap is unrecoverable  this procedure is a much faster alternative to  rebuilding the standby database. This  used to be a long manual process till 11g. In 12c, datafiles from primary database can be restored over the network as backupset and they can be optionally compressed and encrypted as well.

References:

https://docs.oracle.com/database/121/BRADV/rcmadvre.htm#BRADV685