Tag Archives: coding

Check health for the Dataguard environment in Oracle

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;