Resolve Physical Standby gaps

Scenario #1 - Version 10g – ASM is used as storage and primary’s backups not available on standby

Assumptions:

1. The primary’s backup location can not be mounted onto standby server i.e it the NFS mount where primary’s backup are taken is not available to standby server
2. ASM is the storage on primary and standby

To resolve the gap, we need to transfer the standby from primary to standby.

There are 2 cases:

1. The missing log is available in the primary database and has not been deleted
2. The missing log is not available in the primary database and is available in the archive log backup of primary.


First check if the log is available or not, run the following query on primary database:

select status, deleted from v$archived_log where sequence# = ;

In both the cases, the steps are almost similar. Wherever the steps are exclusive to a case, a note is provided to specify the same.

Step#1 – Restore the missing log from primary’s archived log backup.

This step is only required when the log has been deleted from the primary database and is available only in the archived log backups.

RMAN> connect catalog username/password@catalog

RMAN> connect target /

RMAN> restore archivelog sequence 157682;

==> If there is a gap of more than 1 log, we need to use the statement like:

RMAN> restore archivelog from sequence until sequence ;

Note that this will restore the archive log to the default archival destination of the database specified by the parameter log_archive_dest_1

==> You can use the following command to know the backup piece which contains the backup of that missing log

RMAN> list backup of archivelog sequence 157682;

Step#2 – Copy the archive log from ASM diskgroup to the normal OCFS file system

If the missing archive log is available in the primary database, start from this step.

Use the following RMAN command to achieve this:

RMAN> copy archivelog '+DATA/IPWP_RWC1/ARCHIVELOG/2009_11_13/thread_1_seq_157682.1341.702785107' to '/tmp/thread_1_seq_157682.1341.702785107';

Step#3 – “scp” the log from primary server to standby server’s file system

scp /tmp/thread_1_seq_157682.1341.702785107 oracle@ipw-db-sac1: /tmp/thread_1_seq_157682.1341.702785107

Step#4 – Manually recover the standby database using the just shipped archived log

To do this first cancel the managed recovery:

Now, we need to perform the manual recovery to apply the archive log which is missing and has been transferred from primary:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> recover automatic standby database; <== This is for manual recovery ORA-00279: change 34121233951 generated at 11/12/2009 07:54:10 needed for
thread 1
ORA-00289: suggestion : +DATA
ORA-00280: change 34121233951 for thread 1 is in sequence #157682
ORA-00278: log file '+DATA' no longer needed for this recovery
ORA-00308: cannot open archived log '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form


Specify log: {=suggested filename AUTO CANCEL}
/tmp/thread_1_seq_157682.1341.702785107 <== At this prompt, provide the name of the log which has been copied over from primary

ORA-00279: change 34121306612 generated at 11/12/2009 07:58:19 needed for
thread 1
ORA-00289: suggestion : +DATA
ORA-00280: change 34121306612 for thread 1 is in sequence #157683
ORA-00278: log file '/tmp/thread_1_seq_157682.1341.702785107' no longer needed
for this recovery


Specify log: {=suggested filename AUTO CANCEL}
CANCEL <== At this prompt now, enter CANCEL to tell oracle to stop recovery because we have applied the one missing log
Media recovery cancelled.

Now, restart managed recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Now by querying the view v$archived_log, we can see that the missing log has been bypassed and MRP process has proceeded the log application process.

Scenario # 2 - Version 10g – Primary’s backups are available on the standby

Assumptions:

1. The primary’s backup location can be mounted onto standby server i.e it the NFS mount where primary’s backup are taken is available to standby server
2. OCFS is being used as storage

If the archive logs have been deleted from the database, we need to restore them from the archive log backups as demonstrated. This scenario is in fact independent of ASM or OCFS because backup pieces are available on the standby.

STEP#1: First, know the primary’s backup piece(s) which contain the missing archive log(s), for example:

RMAN> list backup of archivelog from sequence 154472 until sequence 154474;
This will give the name of all backup pieces which contain the required logs.

STEP#2: Since the backup pieces are available on the standby server, catalog all the backup pieces on the standby. For example,

RMAN > catalog backuppiece '/location/piece_name';
You do not need to connect to recovery catalog, but connect to standby as the target. Note that “catalog” is only available in version 10g

STEP#3: Once the pieces are cataloged, stay connected to the standby database as target and restore the archive logs as follows:

RMAN> restore archivelog from sequence 154472 until sequence 154474;

RMAN will restore all the archivelogs to the correct diskgroup (archival location) on the standby

Since we have now restored the missing log(s) onto the standby, there is no need of recovery. The restored logs will now be available in standby and shold be applied by MRP automatically which has been so far waiting for the gaps to be filled.

Scenario # 3 - Version 9i – Primary’s backups are not available on standby server

Assumptions:

1. The archive logs have been deleted from the primary database
2. Primary’s backups are not available on standby server.

In this scenario, the steps are almost similar to the first scenario.

Note that, if ASM is not the storage, then we just need to restore the archive logs and move them to the standby server. Which means step#2 can be skipped when compared to the first scenario.

Important notes on resolving physical standby gaps

==> If the archive log has not been deleted from the database and ASM is not the storage, then simply copy the archive log from primary to standby’s archival location. MRP will automatically pick the archive logs and start applying them. This is true for 10g as well as 9i.

==> In all the scenarios, there are 2 fundamental ways to fill the gap on standby – Either by restoring the missing logs at standby’s archival location where MRP can pick it up automatically
OR
If the missing log(s) can not be restored at the default location, then perform an incomplete recovery of standby using the restored archived log which has been restored at the non-default location. This is basically applicable in cases where ASM is being used