Script to collect Diagnostic Information in a Dataguard Environment

I’ve come across many posts in the OTN forums wherein the OP faces sync issues between the Physical Standby and the Primary, but there wouldn’t be much details provided to identify the loopholes. This post provides a couple of scripts which would pull out the required dataguard configuration parameters and the diagnostic information needed to identify where the issue lies and to identify on what’s happening.
The following script needs to be run on the Primary Database. Do not worry, the script doesn’t has a shutdown command 🙂

On the Primary:

set linesize 300
set pages 70
col name for a30
col value for a60
col message for a90
col destination for a35
col dest_name for a40
select name,value from gv$parameter where name in ('db_name','db_unique_name','db_domain','db_file_name_convert','log_file_name_convert','fal_server','fal_client','remote_login_passwordfile','standby_file_management','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2');
select status,instance_name,database_role,open_mode,protection_mode,switchover_status from gv$instance,gv$database;
select name,(space_limit/1024/1024/1024) "Limit in GB",(space_used/1024/1024/1024) "Used in GB" from v$recovery_file_dest;
select thread#,max(sequence#) from gv$archived_log group by thread#;
select inst_id,dest_id, dest_name, status, target, archiver , destination from GV$ARCHIVE_DEST where destination IS NOT NULL;
select * from (select severity,error_code,message,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from v$dataguard_status where dest_id=2 order by rownum DESC) where rownum <= 7;
select group#,thread#,status,members,(bytes/1024/1024)"Each ORL File Size in MB" from gv$log;
select group#,thread#,status,(bytes/1024/1024)"Each SRL File Size in MB" from gv$standby_log;

Run the following script on the Physical Standby:

On the Standby:

set linesize 300
set pages 70
col name for a30
col value for a60
col message for a90
col destination for a35
col dest_name for a40
select name,value from gv$parameter where name in ('db_name','db_unique_name','db_domain','db_file_name_convert','log_file_name_convert','fal_server','fal_client','remote_login_passwordfile','standby_file_management','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2');
select status,instance_name,database_role,open_mode,protection_mode,switchover_status from gv$instance,gv$database;
select name,(space_limit/1024/1024/1024) "Limit in GB",(space_used/1024/1024/1024) "Used in GB" from v$recovery_file_dest;
select thread#,max(sequence#) from gv$archived_log group by thread#;
select thread#,max(sequence#) from gv$archived_log where applied='YES' group by thread#;
select dest_id, dest_name, status, target, archiver , destination from GV$ARCHIVE_DEST where destination IS NOT NULL;
select inst_id,process,status,sequence#,thread#,client_process from gv$managed_standby;
select group#,thread#,status,members,(bytes/1024/1024)"Each ORL File Size in MB" from gv$log;
select group#,thread#,status,(bytes/1024/1024)"Each SRL File Size in MB" from gv$standby_log;

Script to gather Standby Sync Details:

In addition to the above scripts, this script helps in monitoring the standby. It basically lists out the last archive that has been received on the standby and also the last archive sequence that has been applied on the standby.

select rec.thread#, rec.last_rec "Last Sequence Received", app.last_app "Last Sequence Applied"
from
(select thread#, max(sequence#) last_rec
from v$archived_log
where resetlogs_id = (select max(resetlogs_id) from v$archived_log)
group by thread#) rec,
(select thread#, max(sequence#) last_app
from v$archived_log
where resetlogs_id = (select max(resetlogs_id) from v$archived_log)
and applied='YES' and registrar='RFS'
group by thread#) app
where rec.thread#=app.thread#
and rec.thread# != 0
order by rec.thread#
/

Script to monitor Standby Recovery:

The following script helps you to monitor the recovey progress on the physical standby.

select to_char(START_TIME,'DD-MON-YYYY HH24:MI:SS') "Recovery Start Time",to_char(item)||' = '||to_char(sofar)||' '||to_char(units) "Progress"
from v$recovery_progress where start_time=(select max(start_time) from v$recovery_progress);

This post would be updated accordingly based on the script modifications.

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.