Roll-forwarding a physical standby database is a procedure, technique and method typically used to resolve the huge archive gap between the primary and standby database that is caused by various reasons. It’s a faster and recommended approach to resolve the huge gap and make the standby database in line with the primary database in a quick fashion.

Most of the existing material in this context, explains only one approach, i.e. taking a fresh RMAN incremental back up on the primary database and then applying it on a standby database to fill the archive gap. In this article, I will be sharing an interesting scenario that I have confronted while building a standby database of 2TB sized database and how I resolved the issue taking a slight different approach to the problem.

The following is the typical roll-forward method is used to resolve the gap between the primary and standby databases. For example, when a huge gap, let’s say over 500 archives logs, is found on a physical standby database, you will follow the steps mentioned below to resolve the gap:

  1. Cancel the automatic media recovery process (MRP) on the standby database, if in progress:
     
    SQL> alter database recover managed standby database cancel;
     
  2. Take a note of current System Change Number (SCN) on the standby database using the following SQL statement:
     
    SQL> SELECT current_scn FROM V$DATABASE;
     
  3. Perform an incremental RMAN back up and create a new standby control file on the primary database:
      
    $ export ORACLE_SID=<primary database SID>
    $ rman target /
     
    RMAN> BACKUP INCREMENTAL FROM SCN <value from the above query>
          DATABASE FORMAT ‘/u00/tmp/incr_db_%U’;
     
    --- create a new standby control-file
    RMAN> backup current controlfile for standby format 
                ‘/u00/tmp/standby.ctl’;
     
  4. Copy the files generated under the /u00/tmp directory to the same location on the standby database site. You can use any copy/FTP option to transfer the backup files from primary to the standby database site. 
     
  5. Once files are moved/copied on the standby site, execute the following sequence of commands on the standby database:
     
    $ rman target /
    RMAN> shutdown database;
    RMAN> startup nomount;
    RMAN> restore standby controlfile from ‘/u00/tmp/standby.ctl’
    RMAN> alter database mount;
    RMAN> catalog start with ‘+DG_PRIM/STDBY/DATAFILE’;
    --- Respond with YES when the following is prompted:
    Do you really want to catalog the above files (enter YES or NO)? YES
     
    Note: the above step is necessary as the location of the primary and standby data files are likely to be a different one.
     
    RMAN> switch database to copy;
    RMAN> recover database delete archivelog;
     
  6. Once the recovery is completed on the standby database, re-create the standby redo logs and restart the MRP on the standby database using the following SQL statement:
     
    For 10g database:
    SQL> alter database recover managed standby database disconnect from session;
     
    For 11g database:
    SQL> alter database recover managed standby database using current logfile disconnect from session;

The approach that I used for my issue

Now coming back to my situation, as part of the Disaster Recovery (DR) implementation project at our premises, a physical standby database of 1.5TB sized primary database was being configured. Due to slow network between the primary and disaster recovery (DR) sites, the standby database creation procedure triggered from RMAN (DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK ) took about 2 days of time to complete. When the standby database creation procedure was completed, the standby database was almost 2 days behind from the primary database with a larger number of archive log gap. What made the situation complex was there were couple of new data files added to the primary database during the course of standby database creation.

If I wanted to take the typical roll-forward approach in case, i.e. taking and applying fresh incremental backup, it is going to take a considerable time and there will be a gap again. The amount of archive logs produced per hour for the database is over 200, and imagine when we should be able to complete the synchronization in this context.

The below is a step-by-step procedure that followed to address the issue in my case:

  1. Generated a latest standby control file from the primary database:
     
    $ export ORACLE_SID=<primary database SID>
    $ sqlplus / as sysdba
    SQL> alter database create standby controlfile as ‘/home/oracle/newstandby.ctl’
     
  2. Copied the standby control file copy to the standby site.
     
  3. Shutdown the standby database.
     
  4. From RMAN, started the standby instance in mount state and restored the standby control-file from the location where the file was copied.
     
    SQL> shutdown immediate;
    SQL> EXIT
     
    $ rman target /
      
    RMAN> startup nomount
    RMAN> restore controlfile from ‘/home/oracle/newstandby.ctl’;
    RMAN> alter database mount;
     
  5. Restored those two new data files that were created during the standby database creation time, using the following RMAN scripts:
     
    RMAN> RUN
    {
    ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
    SEND 'NB_ORA_CLIENT=hostname,NB_ORA_POLICY=BACKUP_POLICY_NAME,NB_ORA_SERV=nbmaster';
    restore datafile 58,59;
    RELEASE CHANNEL ch00;
    }
     
    Note : The example uses Symantec NetBackup script to restore the data files from the daily incremental backups stored in the TAPEs. The files were backed-up as part of the daily incremental backup schedule. Hence, I don’t need to take a new backup of those data files in order to restore them on the standby database.
     
  6. Since the primary and standby data files locations are different, had to catalog the standby data file location and switched to the database copy subsequently.
     
    RMAN> CATALOG START WITH ‘+DG_DISKGROUP/STANDBY/DATAFILE’;
    RMAN> SWITCH DATABASE TO COPY;
     
  7. After switching the database data files to STANDBY data file location, I have initiated the standby database recovery using the following RMAN script:
     
    RMAN> RUN
    {
    ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
    SEND 'NB_ORA_CLIENT=hostname,NB_ORA_POLICY=BACKUP_POLICY_NAME,NB_ORA_SERV=nbmaster';
    recover database delete archivelog;
    RELEASE CHANNEL ch00;
    }
     
    Note : The above script restores required incremental/archive logs from the previous backups and applies on the standby database to cover the gap. Oracle is smart enough to sense the current and starts applying the changes from the current SCN onwards.
     
  8. Once the recovery completed, the following error was reported, which is expected one:
     
    archived log thread=1 sequence=1352165
    released channel: ch00
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 05/20/2013 10:23:32
    RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1352165 and starting SCN of 18843614727
     
  9. At this point in time, the gap is resolved and the standby database is almost in-line with the primary database. I just continued completing rest of the standby database configuration settings and was able to ensure the standby database is totally in sync with the primary database.

Conclusion

The method and approach demonstrated in this paper can be used in any standby database environment to cover the huge gap between the primary and standby. Additionally, if you come across situation like my case, also, it would be very useful to address the gap or missing data files issues. This approach also explained how to make use of the backups stored in TAPES.


Contributed by Syed Jaffar Hussain