oracle9204(9i)_dg(data guard)_archive gap_query_apply_transmit

To identify the logs in the archive gap

Query the V$ARCHIVED_LOG and V$LOG views on the standby database. For example, the following query shows that there is a difference in the RECD and SENT sequence numbers for the destination specified by DEST_ID=2, indicating that there is a gap:

SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM

  2> V$ARCHIVED_LOG R, V$LOG L WHERE

  3> R.DEST_ID=2 AND L.ARCHIVED='YES';


LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
            7            10

Use the following query to determine the names of the archived redo logs on the local system that must be copied to the standby system that has the gap:

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND

  2> SEQUENCE# BETWEEN 7 AND 10;


NAME
--------------------------------------------------------------------------------
/primary/thread1_dest/arcr_1_7.arc

/primary/thread1_dest/arcr_1_8.arc

/primary/thread1_dest/arcr_1_9.arc

/primary/thread1_dest/arcr_1_10.arc

B.3.3 Manually Transmitting the Logs in the Archive Gap to the Standby Site

After you have obtained the log sequence numbers of the logs in the archive gap, you can obtain their filenames by querying the V$ARCHIVED_LOG view on the primary site. The archived log filenames on the standby site are generated by the STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT parameters in the standby initialization parameter file.

If the standby database is on the same site as the primary database, or the standby database is on a remote site with a different directory structure than the primary database, the filenames for the logs on the standby site cannot be the same as the filenames of the logs archived by the primary database. Before transmitting the archived logs to the standby site, determine the correct filenames for the logs at the standby site.

To copy logs in an archive gap to the standby site

1. Review the list of archive gap logs that you obtained earlier. For example, assume you have the following archive gap:

   THREAD#    LOW_SEQUENCE#   HIGH_SEQUENCE#
   ---------- -------------   --------------
          1             460              463
          2             202              204
          3             100              100
   

   If a thread appears in the view, then it contains an archive gap. You need to copy logs from threads 1, 2, and 3.

2. Determine the filenames of the logs in the archive gap that were archived by the primary database. After connecting to the primary database, issue a SQL query to obtain the name of a log in each thread. For example, use the following SQL statement to obtain filenames of logs for thread 1:

   SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1
     2> AND SEQUENCE# > 459 AND SEQUENCE# < 464;
   
   NAME
   ---------------------------------------------------------------------
   /primary/thread1_dest/arcr_1_460.arc
   /primary/thread1_dest/arcr_1_461.arc
   /primary/thread1_dest/arcr_1_462.arc
   /primary/thread1_dest/arcr_1_463.arc
   4 rows selected
   
   

3. On the standby site, review the settings for STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT in the standby initialization parameter file. For example, you discover the following:

   STANDBY_ARCHIVE_DEST = /standby/arc_dest/
   LOG_ARCHIVE_FORMAT = log_%t_%s.arc
   

   These parameter settings determine the filenames of the archived redo logs at the standby site.

4. On the primary site, copy the archive gap logs from the primary site to the standby site, renaming them according to values for STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. For example, enter the following copy commands to copy the archive gap logs required by thread 1:

   % cp /primary/thread1_dest/arcr_1_460.arc /standby/arc_dest/log_1_460.arc
   % cp /primary/thread1_dest/arcr_1_461.arc /standby/arc_dest/log_1_461.arc
   % cp /primary/thread1_dest/arcr_1_462.arc /standby/arc_dest/log_1_462.arc
   % cp /primary/thread1_dest/arcr_1_463.arc /standby/arc_dest/log_1_463.arc
   
   

   Perform. similar copy commands to copy archive gap logs for threads 2 and 3.

5. On the standby site, if the LOG_ARCHIVE_DEST and STANDBY_ARCHIVE_DEST parameter values are not the same, then copy the archive gap logs from the STANDBY_ARCHIVE_DEST directory to the LOG_ARCHIVE_DEST directory. If these parameter values are the same, then you do not need to perform. this step.

   For example, assume the following standby initialization parameter settings:

   STANDBY_ARCHIVE_DEST = /standby/arc_dest/
   LOG_ARCHIVE_DEST = /log_dest/
   

   Because the parameter values are different, copy the archived logs to the LOG_ARCHIVE_DEST location:

   % cp /standby/arc_dest/* /log_dest/
   

   When you initiate manual recovery, the Oracle database server looks at the LOG_ARCHIVE_DEST value to determine the location of the logs.

Now that all required logs are in the STANDBY_ARCHIVE_DEST directory, you can proceed to to apply the archive gap logs to the standby database.