Check Active Dataguard Replication Status


In order to check if ADG is up-to-date, one has to tail the alert log on both the Primary and Physical Standby databases, followed by a manual check to look for the log status of (in-transit) against each of the threads. While this method is perfect it can be bit tedious to perform. You can now do the same using one of the attached SQL from the Primary database.

 Deployment and usage steps
- Open a vi editor on the Primary database.
- Copy of the below SQL into the editor
- Complete the  database link creation command based on the database 
  you are checking.
- Save the text file as check_adg_status.sql

 - execute the sql file as sys user.
e.g. sqlplus -s  / as sysdba @check_adg_status.sql 

 Note: These commands cannot be run from the Physical standby as the query relies on using a temporary database link that cannot be created on a read-only database.

 Troubleshooting
1. Some database clusters may report the following error when invoked
     ORA-02085: database link [db_link_name] connects to [database_name]
     This is caused by a oracle initialisation parameter GLOBAL_NAMES set to  
     TRUE. In such a scenario, you must run the following SQL prior to invoking  
      the script.
      To Disable : ALTER SESSION SET GLOBAL_NAMES=FALSE;
      To Enable  : ALTER SESSION SET GLOBAL_NAMES=TRUE;
   

 SQL
set lines 250 pages 200 feedback off
--
-- Comment/uncomment the appropriate database link creation --
-- command based on the database you are checking.
--
Prompt
Prompt Creating db link to standby database...
--create database link adg_check connect to [user] identified by [passwd] using '[db_name]';

 Prompt
Prompt Log Shipping Status...
select local_instance.thread#, local_seq Primary_sequence#, remote_seq Standby_sequence#, local_seq-remote_seq diff
  from (select thread#, max(Sequence#) local_seq from v$log where thread#=1 group by thread#) local_instance,
       (SELECT THREAD#, MAX(SEQUENCE#) remote_seq FROM V$LOG_HISTORY@adg_check where thread#=1 GROUP BY THREAD#) remote_instance
union all
select local_instance.thread#, local_seq Primary_sequence#, remote_seq Standby_sequence#, local_seq-remote_seq diff
  from (select thread#, max(Sequence#) local_seq from v$log where thread#=2 group by thread#) local_instance,
       (SELECT THREAD#, MAX(SEQUENCE#) remote_seq FROM V$LOG_HISTORY@adg_check where thread#=2 GROUP BY THREAD#) remote_instance;

 Prompt  
Prompt MRP Status...
select decode(inst_id,1,'NODE-01A','NODE-01B') node, process mrp_process, thread#, sequence#  
  from gv$managed_standby@adg_check
where process like '%MRP%'
union all
select '...MRP Not Running',null,null,null  
  from dual   
where not exists    
       (select process
          from gv$managed_standby@adg_check 
          where process like '%MRP%');

 Prompt 
Prompt Dropping db link to standby database...
drop database link adg_check;

 Prompt
exit;

Sample Output 1
Creating db link to standby database...
Log Shipping Status...
   THREAD# PRIMARY_SEQUENCE# STANDBY_SEQUENCE#       DIFF
---------- ----------------- ----------------- ----------
         1             36251             36251          0
         2             38389             38389          0
MRP Status...
NODE               MRP_PROCE    THREAD#  SEQUENCE#
------------------ --------- ---------- ----------
NODE-01A           MRP0               1      60439
Dropping db link to standby database...

Sample Output 2
Creating db link to standby database...
Log Shipping Status Verification...
   THREAD# PRIMARY_SEQUENCE# STANDBY_SEQUENCE#       DIFF
---------- ----------------- ----------------- ----------
         1             36251             36251          0
         2             38389             38389          0
MRP Status...
NODE               MRP_PROCE    THREAD#  SEQUENCE#
------------------ --------- ---------- ----------
...MRP Not Running
Dropping db link to standby database...


Column Definition
Column Name          Comments
Thread : This column indicates the 2 node RAC. Ensure there are always 2 rows returned for of the cluster node.
Primary_Sequence# : This is current sequence number for the respective thread on the Primary database.
Standby_Sequence# : This is the sequence number on the Physical Standby that has been successfully mined so far.
Diff : Arithmetic difference between the two corresponding sequence numbers. 
        Valid values are
        - Value of "0" indicates both databases are in sync
        - Positive integer indicates Physical Standby is lagging behind by this number of files.
        - Negative integer indicates the Primary has fallen behind.
          Congratulations you have discovered time travel where the replicated    
          database is ahead of its source database. Contact CERN immediately.
Node : The RAC node where the MRP process is active. Valid values are NODE-01A and NODE-01B ( for a 2 node cluster)
MRP_PROCESS : MRP process name
Thread# :  The current thread that MRP is mining
Sequence# : The current sequence number associated with the thread that is being mined.

Popular posts from this blog

Recover Standby database from ORA-00332: archived log is too small

When a standby database crashes and upon instance recovery gets stuck with error ORA-00332: archived log is too small - may be incompletely archived, then following set of actions must be carried out to restore the service. 1. Check that the instance is in MOUNTED. (select status from v$instance) 2. If not mounted, then start the database in mounted mode SQL> startup mount; 3. Search the alert log to identify the archive log that is corrupt,  Sample lines from the alert log Errors in file /u01/app/oracle/diag/rdbms/fxfgsby/FXFGSBY1/trace/FXFGSBY1_ora_19463.trc: ORA-00332: archived log is too small - may be incompletely archived ORA-00334: archived log: '+FLASH/fxfgsby/archivelog/2013_08_19/thread_1_seq_173831.5998.823855747' Shutting down recovery slaves due to error 332 4. In the above example, the corrupt log file belongs to Thread#=1 and Sequence#=173831 5. Log on to the Primary database cluster and take a copy of the file from ASM onto the local file system. Not...
Read more

Top CPU consuming oracle sessions

Use the shell script from  here  to obtain the details of the Oracle sessions using high CPU.  Usage: Log in as oracle user and navigate to the folder where the script has been copied into. Do not forget to grant execute permissions to the script prior to invoking it. $ ./top_oracle_sessions.sh [cpu_threshold]  e.g.1  $ ./top_oracle_sessions.sh 90       This will gather information if overall CPU usage is above 90% e.g.1  $ ./top_oracle_sessions.sh        This will gather information if overall CPU usage is above 50%. This is the default setting. Notes: Use the attached script to obtain the details of the Oracle sessions using high CPU. The script works by follows Takes 2 snapshots using Linux "top" command with a interval of 2 seconds between snaps Uses the data from the second snapshot to determine if the overall CPU usage is above a set threshold. The threshold is a conf...
Read more