Lost an archive log file? Use RMAN incremental backups to resync your standby database

Using RMAN Incremental Backups To Resync Your Standby Database

Anyone who has done time on the "tech frontline" knows that there are occasions in production systems when the unexpected transpires. Whether such events are of human origin, for example, accidentally deleting a file or setting an overly aggressive retention policy, or otherwise (corruptions and disk failures), these threaten to compromise the integrity of the system(s) you are responsible for.

One such example which can strike fear into the heart of an Oracle Database Administrator charged with maintaining a physical standby database is the 'disappearance' of an archive log file from the primary server. When it comes to Oracle physical standby databases the bottom line is that the DR site must have sequential access to all archive log files generated, in order to apply these changes so that the standby database can be kept in sync with the primary. No archive log file can be skipped; that is not an option.

So when this situation occurs, beyond understanding why an archive log(s) went missing and addressing the root cause of this, there are basically 3 options available to rectify the issue:

1. Restore the missing archive log from a backup onto the primary server. If you are using Standby™️ then it will be able to pick up this log and transfer it to the DR site to apply to the standby database.

2. Rebuild the standby database. With Standby™️ the standby database can be rebuilt using the CSD (create standby database), either through the web-based interface or via the command line (dbvisit_setup).

3. Resync the standby database using an incremental RMAN backup from the primary database. This does not require a full rebuild of the standby database and is, therefore, considerably more efficient.

Occasionally this very situation is brought to the Dbvisit Support team by customers and prospects, who need advice in terms of how to resolve this issue. For those who are using Standby™️, a missing archive log file typically generates the Return Codes 6001 or 460 on the primary server - and 409 on the standby side (thrown after a number of unsuccessful attempts to find the archive log file in question). Our general approach to bringing resolution to this, before the decision to rebuild the standby database is made, is to suggest restoring the archive log(s) from backup/copy, if this is a simple process, or otherwise opting for option #3 (above) to resync the standby database using incremental backups.

The steps to resync the standby database using an incremental RMAN backup from the primary database are as follows:

1. Obtain the current SCN from the standby database (here via SQL*Plus):

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

example:

CURRENT_SCN
-----------
30215876

2. Connect to the primary database as the RMAN target:

> RMAN
> connect target /

Then create an incremental backup from the current SCN of the standby database (as determined in step 1) and save to a selected location:

RMAN> BACKUP INCREMENTAL FROM SCN 30215876 DATABASE FORMAT 'C:\Temp\ForStandby_%U';

3. Transfer all of these backup pieces created by RMAN on the primary to the same location on the standby server.

4. Log into the primary database via SQL*Plus and generate a standby controlfile:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\Temp\standbycontrolfile.ctl' REUSE;

Then copy this new Standby controlfile to a destination directory readable by the Standby Database server.

5. Recreate the standby controlfile on the standby with the following:

SQL> shutdown abort
SQL> startup nomount

Exit out of SQL*Plus and log into RMAN again and run the following:

RMAN> RESTORE STANDBY CONTROLFILE FROM 'C:\Temp\standbycontrolfile.ctl';

Then mount the standby database:

SQL> alter database mount standby database;

NOTE: If you are using Standby™️ you can use the "recreate standby controlfile" command on the primary server to do all of this standby controlfile recreation work for you, and so skip steps #4 and #5. The command to perform this work is to be executed on the primary as follows:

> dbv_functions -Q (DDC_name)

6. Rename standby data/temp files and redo logs if different from the primary using ALTER DATABASE RENAME FILE

7. Catalog the backup pieces on the standby server with RMAN, according to the location you have placed these files:

> RMAN

> connect target /

> catalog start with 'C:\Temp\';

8. Apply incremental backup to the standby database on the DR server, using RMAN:

RMAN> RECOVER DATABASE NOREDO;

9. At this point the standby database will have been updated with changes from the primary, including those which would have been contained in the missing archive log file(s). For those running Standby™️ the next step is to run the "Resync Archives" command to refresh the systems meta-information, and this can be done via the GUI or from the command line on the primary server, with the following:

> dbvisit -R (DDC_name)

In the near future, we are looking to integrate this functionality directly into Standby™️, and have already started work on this. In the meantime, we are interested to hear if you have used this process before to save a standby. We would love to hear your stories, and any 'gotchas' learned through experience.

Good luck!

PS: A big hello to all our new blog subscribers who have just signed up - really nice to have you along! For those of you who haven't yet then please feel free to subscribe to be kept up to date on this and other developments.

Download a free Standby™️ 10 Day Trial today.