There would be scenarios where the standby database lags far behind from the primary database leading to Archive Gap. It could be due to one of the following reasons

1.  Might be due to the network outage between the primary and the standby database leading to the archive gaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as soon as the connection is re-established.

2.It could also be due to archive logs getting missed out on the primary database or the archives getting corrupted and there would be no valid backups.

In such cases where the standby lags far behind from the primary database, incremental backups can be used as one of the methods to roll forward the physical standby database to have it in sync with the primary database.

Oracle Database version : My Oracle Database is using ASM.

Primary database : sspm              Standby database : sssb

Primary Host : dev                          Standby Host : uat

The maximum archivelog sequence generated on the Primary Database is 1005.

On the standby database, the maximum archivelog sequence that is applied is sequence 865.

The standby database is lagging behind the primary database by around 140 archives (1005 – 865).

When I investigated the alert log file of the Primary database  to find out the reason for the logs not getting applied on the standby database, I got to see the below error message.

So the problem was here. The archivelog sequence 866 was missing and was unavailable at the FRA site. There were few more archives missing on the FRA and nor did I had the backup to restore them on the standby database. My option was to go with Roll Forwarding the Standby Database using Incremental Backups. Below are the steps on how to roll forward the physical standby database.

Step 1: Take a note of the Current SCN of the Physical Standby Database.

Standby Database:

Note down the CURRENT_SCN value of the standby database (991247) to proceed further.

Step 2 : Cancel the Managed Recovery Process on the Standby database.

Standby Database:

Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (991247)

Connect to the primary database and take the incremental SCN backup.
Primary Database:

Step 4: Take the standby controlfile backup of the Primary database controlfile.

Connect to the Primary database and create the standby controlfile backup.

Primary Database :

Step 5: Transfer the backups from the Primary Server to the Standby Server.

Primary Database :

Step 6: On the standby server, connect the Standby Database through RMAN and catalog the copied incremental backups so that the Controlfile of the Standby Database would be aware of these incremental backups.

I had the incremental backuppieces copied to the location ‘/u02/bkp‘ on the standby server.

Standby Database:

Step 7: Recover the standby database with the cataloged incremental backup pieces.

Step 8 : Shutdown the physical standby database, start it in nomount stage and restore the standby controlfile backup that we had taken from the primary database.

Standby Database:

Step 9: Shutdown the standby database and mount the standby database, so that the standby database would be mounted with the new controlfile that was restored in the previous step.

Standby Database:

Step 10: If the datafile location of the primary and standby databases are different, then you need to follow this step. If not, then proceed with Step 11.

The datafiles of my primary database are residing on the Diskgroup +DATA_NEW on the primary server and the datafiles on the standby database are residing on the Diskgroup +DATA on the standby server, the datafiles location are different.

Since, I have restored the standby controlfile backuppiece of my primary database on the standby database (Step 7) and mounted the standby database, the standby database controlfile would now have the locations of the datafiles recorded as available in the Primary database. So, we need to make the standby controlfile understand that the datafiles location of the standby database are different from that of the Primary database. For this, you need to catalog the datafile location of the standby database to its controlfile as shown below.

Connect the standby database through RMAN and catalog the location of its datafiles and later switch them.

Standby Database:

Step 11: If the datafile locations of the primary and the standby databases are same, then there is no necessity to perform the catalogging operation as done in the previous step.

On the standby database, start the Managed Recovery Process.

Standby Database:

Step 12: On the Primary database, check the Maximum Archivelog Sequence generated.

Primary Database:

Step 13: Check the maximum archivelog sequence that is applied on the Physical standby database.

So, here we can see from Steps 12 and 13 that the maximum archivelog sequence generated on the Primary database is sequence# 1009 and that applied on the Physical Standby Database is also 1009 which means that the Standby database is in sync with the Primary Database. You can check it out by generating an archive sequence on the Primary database and check if its shipped and applied on the standby database.

Primary Database:

Standby Database:

Now standby database is in sync with the Primary Database.


Leave a Reply