isitdevops.com/databases

V$ARCHIVED_LOG

V$ARCHIVE_DEST_STATUS

V$LOG

To determine if there is an archive gap, query the V$ARCHIVED_LOG and V$LOG views. If an archive gap exists, the output of the query specifies the thread number and log sequence number of all log files in the archive gap. If there is no archive gap for a given thread, the query returns no rows.

Identify the log files in the archive gap

Query the V$ARCHIVED_LOG and V$LOG views on the standby database. For example, the following query shows 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 log files 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

12.11.3 Manually Transmitting Log Files in the Archive Gap to the Standby Site

After you have obtained the sequence numbers of the log files in the archive gap, you can obtain their filenames by querying the V$ARCHIVED_LOG view on the primary site. The archived redo log path names 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 path names for the log files on the standby site cannot be the same as the path names of the log files archived by the primary database. Before transmitting the redo data to the standby site, determine the correct path names for the archived redo log files at the standby site.

To copy log files in an archive gap to the standby site

1. Review the list of archive gap log files 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 log files from threads 1, 2, and 3.

2. Determine the path names of the log files in the archive gap that were transmitted by the primary database. After connecting to the primary database, issue a SQL query to obtain the name of a log file in each thread. For example, use the following SQL statement to obtain filenames of log files 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 

Perform similar queries for threads 2 and 3.

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_%r.arc 

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

4. On the primary site, copy the log files in the archive gap 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 log files 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 commands to copy archive gap log files 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 log files 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 redo log files to the LOG_ARCHIVE_DEST location:

% cp /standby/arc_dest/* /log_dest/ 

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

Now that all required log files are in the STANDBY_ARCHIVE_DEST directory, you can proceed to Section 12.11.4 to apply the archive gap log files to the standby database. See also Section 8.5.4.4 and the V$ARCHIVED_LOG view in Chapter 16.

12.11.4 Manually Applying Log Files in the Archive Gap to the Standby Database

After you have copied the log files in the archive gap to the standby site, you can apply them using the RECOVER AUTOMATIC statement.

To apply the archived redo log files in the archive gap

1. Start up and mount the standby database (if it is not already mounted). For example, enter:

SQL> STARTUP MOUNT PFILE=/oracle/admin/pfile/initSTBY.ora 

2. Recover the database using the AUTOMATIC option:

SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE; 

The AUTOMATIC option automatically generates the name of the next archived redo log file needed to continue the recovery operation.

After recovering the available log files, the Oracle database prompts for the name of a log file that does not exist. For example, you might see:

ORA-00308: cannot open archived log '/oracle/standby/standby_logs/arcr_1_540.arc' 

ORA-27037: unable to obtain file status SVR4 Error: 

2: No such file or directory Additional information: 

3 Specify log: {=suggested | filename | AUTO | CANCEL} 

3. Cancel recovery after the Oracle database applies the available log files by typing CTRL/C:

SQL>  Media recovery cancelled. 

The following error messages are acceptable after recovery cancellation and do not indicate a problem:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below 

ORA-01194: file 1 needs more recovery to be consistent 

ORA-01110: data file 1: 'some_filename' 

ORA-01112: media recovery not started 

4. After you finish manually applying the missing log file, you can restart log apply services on the standby database, as follows:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;