Recover Gaps in Standby Database from primary Using incremental backups

Recovering a Standby database from a missing archives log

Recover Gaps in Standby Database from primary Using incremental backups

A Physical Standby database synchs with Primary by continuous apply of archivelogs from a Primary Database. In case of an archivelog gone missing or corrupt you have to rebuild the standby database from scratch but in 10g, an incremental backup created with BACKUP INCREMENTAL… FROM SCN can be used to refresh the standby database with changes at the primary database since the last SCN at Standby and then managed recovery can resume i.e. Compensate for the missing archivelogs
Step 1: On the standby database find out the current SCN.

standby > select current_scn from v$database;


Step 2: On the primary database, create an incremental backup from the above SCN

#rman target /

BACKUP as compressed backupset device type disk INCREMENTAL fROM SCN 2757142742 DATABASE FORMAT ‘/oracle/backups/priv1/rmanbackup/manual/forstandby_%U’ tag ‘FOR STANDBY’;

Step 3: Cancel managed recovery at the standby database

STDBY>alter database recover managed standby database cancel;
Media recovery complete.

scp the backup files to standby server to /oracle/backups/priv2/rmanbackup/manual/ folder.

Step 4: Catalog the Incremental Backup Files at the Standby Database

#rman target /

RMAN> CATALOG START WITH ‘/oracle/backups/priv1/rmanbackup/manual/’;

searching for all files that match the pattern /oracle/backups/priv1/rmanbackup/manual/

List of Files Unknown to the Database

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files…
cataloging done

Step 5: Apply the Incremental Backup to the Standby Database

RMAN> recover database noredo;

Step 6: Put the standby database back to managed recovery mode.

STDBY>> recover managed standby database disconnect;
Media recovery complete.

From the alert.log you will notice that the standby database is still looking for the old log files

FAL[client]: Failed to request gap sequence
GAP – thread 1 sequence ….

DBID 23623369570 branch 704397469

FAL[client]: All defined FAL servers have been attempted.


This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated

Step 7:  At Primary .. Create latest control file for standby database

RMAN> backup device type disk current controlfile for standby format ‘/oracle/backups/test1/rmanbackup/manual/forstandbyctrl.bck’;

Step 8: At Standby … Create a backup (text copy) of control file (to find out the actual location of data files)

SQL> alter database backup controlfile to trace;

Step 9: At Standby …

SQL> shutdown immediate;

SQL> startup nomount;

RMAN> restore standby controlfile from ‘/oracle/backups/test1/rmanbackup/manual/forstandbyctrl.bck’;

Step 10 : At Standby …

Create standby Redo log file

For example ( check out for location , group number and size )

alter database add standby logfile thread  1 group x ‘redo log location’ size mb;

alter database add standby logfile thread  1 group 5 ‘/oracle/oradata01/test1/redo05.log’ size 52428800;

alter database add standby logfile thread  1 group 6 ‘/oracle/oradata01/test1/redo06.log’ size 52428800;

Step 11: At Standby …

If there is mismatch in location of data files between primary and standby, need to update control file, follow these steps

Sql>Alter system set standby_file_management=manual;

Sql> alter database recover managed standby database cancel;

Sql>Shutdown immediate;

Sql>Startup mount

Sql>Alter database rename datafile  ‘primary data file location’ to ‘standby datafile location’;

For example:

SQL>alter database rename file ‘/oracle/oradata02/test2/data/app_data05.dbf’ to ‘/oracle/oradata/test1/data/lob/app_data05.dbf’;

Sql>alter system set standby_file_management=auto;

Sql>Shutdown immediate

Sql>Startup mount

Sql>recover managed standby database disconnect;

Step 12:

Verify the standby alert log file, after couple of switch logfile at primary:

SQL> alter system switch logfile;


About deepakguptadba

10 years of experience in Database Administrator for Oracle Database 10g//9i/8i/7.x with total 15 years’ comprehensive experience in IT , Pharma / Biotech , Manufacturing, Construction , Auto & Electronics.
This entry was posted in Data Guard and tagged . Bookmark the permalink.

One Response to Recover Gaps in Standby Database from primary Using incremental backups

  1. guest says:

    Nice posting.
    But what about the different locations for online redo log files in Standby from primary?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s