Check health for the Dataguard environment in Oracle

Leave a reply

Run the following commands on Primary database Server

Check the database role ,force logging, protection mode for dataguard in Oracle

col db_unique_name format a15
col flashb_on format a10

select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
       GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;

Current SCN with Standby SCN match for dataguard in primary database

--- PRimary database SCN
select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;
-- Standby database SCN
select DEST_ID, APPLIED_SCN FROM v$archive_dest WHERE TARGET='STANDBY';

Check the incarnation of database in primary database

select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;

Check archive log configuration and destination in primary database

column host_name format a30 tru
column version format a10 tru
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;

column destination format a35 wrap
column process format a7
column archiver format a8
column dest_id format 99999999
select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE  
from v$archive_dest where DESTINATION IS NOT NULL;

column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';

column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest where DESTINATION IS NOT NULL;

column message format a80
select MESSAGE, TIMESTAMP from v$dataguard_status
where SEVERITY in ('Error','Fatal') order by TIMESTAMP;

Check Redo Log configuration

select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;

select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;

Check the Dataguard parameter on primary database

column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;

Check the Redo Shipping Progress in Primary database

column client_pid format a10
select PROCESS, STATUS, CLIENT_PROCESS, CLIENT_PID, THREAD#, SEQUENCE#, BLOCK#, ACTIVE_AGENTS, KNOWN_AGENTS from v$managed_standby  order by CLIENT_PROCESS, THREAD#, SEQUENCE#;

--- Check dataguard in error in last day
select TIMESTAMP,SEVERITY,ERROR_CODE,MESSAGE from v$dataguard_status where timestamp > systimestamp-1;

Run the following commands on Standby Database Server

Check the database detail

-- Check the database detail on standby database
col db_unique_name format a15
col flashb_on format a10
select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
       GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;

Check the Current SCN on standby 

select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;

Check the incarnation of database

select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;

Check archive destination configuration on standby database

column host_name format a30
column version format a10
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;

column process format a7
column archiver format a8
column dest_id format 99999999 
select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE  
from v$archive_dest
where DESTINATION IS NOT NULL;

column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';

Check Archive Destination Errors in Standby database

column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest where DESTINATION IS NOT NULL;

column message format a80
select MESSAGE, TIMESTAMP from v$dataguard_status
where SEVERITY in ('Error','Fatal')
order by TIMESTAMP;

Check the Data Guard Redo Log Configuration in Standby 

select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;

select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;

Check Data Guard Related Parameters in Standby database

column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;

Check the Managed Recovery State in standby database

column client_pid format a10
select  PROCESS, STATUS, CLIENT_PROCESS, CLIENT_PID, THREAD#, SEQUENCE#, BLOCK#, ACTIVE_AGENTS, KNOWN_AGENTS from v$managed_standby order by CLIENT_PROCESS, THREAD#, SEQUENCE#;

Check the Data Guard Apply Lag on Standby Database

column name format a12
column lag_time format a20
column datum_time format a20
column time_computed format a20
SELECT NAME, VALUE LAG_TIME, DATUM_TIME, TIME_COMPUTED from V$DATAGUARD_STATS where name like 'apply lag';

select * from v$archive_gap;

select TIMESTAMP, SEVERITY, ERROR_CODE, MESSAGE from v$dataguard_status where timestamp > systimestamp-1;

Leave a Reply