Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 20 May 2012

Gap Detection on Standby Database

Causes: A gap sequence can occur whenever the primary database archives a log but the log is not transferred to the standby site. Because the standby database requires the sequential application of redo logs, a missing log prevents managed recovery from applying subsequent logs.
Gap sequences can occur in the following situations:
- Creation of standby database
- Shutdown the standby database when primary database is open
- Network Failure preventing the transfer of log to the standby site.
Script: To Identify the Gap sequence on standby Database 
SQL> CONNECT sys/sys_pwd@standby1 AS SYSDBA
SQL> STARTUP NOMOUNT pfile=/oracle/admin/pfile/init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Copy the following SELECT statement into a script and then run the script on the standby database:
SELECT high.thread#, "LowGap#", "HighGap#"
FROM ( SELECT thread#, MIN(sequence#)-1 "HighGap#"
     FROM ( SELECT a.thread#, a.sequence#
         FROM ( SELECT * FROM v$archived_log ) a,
         ( SELECT thread#, MAX(next_change#)gap1 FROM v$log_history GROUP BY thread# ) b
         WHERE a.thread# = b.thread# AND a.next_change# > gap1
     )
     GROUP BY thread# ) high,
  (  SELECT thread#, MIN(sequence#) "LowGap#"
     FROM (SELECT thread#, sequence#
         FROM v$log_history, v$datafile
         WHERE checkpoint_change# <= next_change#
         AND checkpoint_change# >= first_change#
     )
     GROUP BY thread# ) low
 WHERE low.thread# = high.thread#;
Examine the output of the query to determine the gap sequence. Generally Not every thread has a gap sequence. If the LowSeq# and HighSeq# are identical, that means no gap sequence exists for this thread. You must apply the logs in the gap sequence for each thread to the standby database to prepare it for managed recovery.

To identify the logs in the gap sequence:
If the primary and standby databases are configured with "FAL_SERVER and FAL_CLIENT" parameters then generally gaps should not occur. Incase if the gaps occur do the following.
On the standby database:
SQL> select * from v$archive_gap;
THREAD#   LOW_SEQUENCE#   HIGH_SEQUENCE#
-------   --------------  ---------------
      1               20               30
The above query shows that their is a gap from log sequence number 20 to 30.
Now On Primary Database: Identify the logs required for gap resolution.
SQL> select name from v$archived_log
where thread# = 1 and dest_id = 1 and sequence# between 20 and 30;
NAME
--------------------------------
E:\oracle\Archive\dest1_a20.dbf
E:\oracle\Archive\dest1_a21.dbf
E:\oracle\Archive\dest1_a22.dbf
E:\oracle\Archive\dest1_a23.dbf
E:\oracle\Archive\dest1_a24.dbf
E:\oracle\Archive\dest1_a25.dbf
E:\oracle\Archive\dest1_a26.dbf
E:\oracle\Archive\dest1_a27.dbf
E:\oracle\Archive\dest1_a28.dbf
E:\oracle\Archive\dest1_a29.dbf
E:\oracle\Archive\dest1_a30.dbf

Copy the above files to standby database and register them using the following commands (If the LOG_ARCHIVE_DEST and STANDBY_ARCHIVE_DEST parameter values are not the same)
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a20.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a21.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a22.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a23.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a24.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a25.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a26.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a27.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a28.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a29.dbf';
SQL>alter database register logfile 'E:\oracle\Archive\dest1_a30.dbf';

Again query the v$archived_gap for any gaps. If there are still gaps repeat the process until there are no gaps. Put the physical standby database into managed recovery mode.
SQL> alter database recover managed standby database disconnect from session;

Finally Test whether managed recovery is applying the transferred archived redo logs:
Connect to the primary database and make sure it is open.
SQL> SELECT status FROM v$instance;
STATUS
-------
OPEN

ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> SELECT max(sequence#) FROM v$log_history;
MAX(SEQUENCE#)
--------------
           126
Create a new session on the standby instance and query the V$LOG_HISTORY view:
SQL> SELECT max(sequence#) FROM v$log_history;
MAX(SEQUENCE#)
--------------
           126
The sequence number should be the same as the number on the primary site. If it is not, wait a short time for Oracle to finish receiving and applying the log and try again.

You can cancel managed recovery operation at any time by issuing either of the following SQL*Plus statements:
RECOVER MANAGED STANDBY DATABASE CANCEL;
RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE;
The first statement waits for the managed recovery operation to finish with the current redo log before terminating recovery while second statement oracle stops managed recovery operation either before reading another block from the redo log or before opening the next redo log file.

0 comments:

Post a Comment