3 - 4 minutes readStandby Diagnosis Query for Standby Node

Query 1: ARCHIVER can be (STOPPED | STARTED | FAILED) FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes. LOG_SWITCH_WAIT The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then value is NULL

select instance_name,host_name,version,archiver,log_switch_wait from v$instance;

INSTANCE_NAME                HOST_NAME             VERSION                 ARCHIVE                 LOG_SWITCH_

TEST                                  flex-sprod                 9.2.0.5.0                 STARTED

1 row selected.

Query 2: The following select will give us the generic information about how this standby is setup. The database_role should be standby as that is what this script is intended to be ran on. If protection_level is different than protection_mode then for some reason the mode listed in protection_mode experienced a need to downgrade. Once the error condition has been corrected the protection_level should match the protection_mode after the next log switch.

select name,database_role,log_mode,controlfile_type,protection_mode,protection_level from v$database;

Query 3: Force logging is not mandatory but is recommended. Supplemental logging should be enabled on the standby if a logical standby is in the configuration. During normal operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or NOT ALLOWED.

select force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui, switchover_status,dataguard_broker from v$database;

FORCE_LOGGING          REMOTE_ARCHIVE     SUP         SUP          SWITCHOVER_STATUS             DATAGUARD_BROKER

NO                                ENABLED                   NO           NO            SESSIONS ACTIVE                   DISABLED

1 row selected.

Query 4: This query produces a list of all archive destinations and shows if they are enabled, what process is servicing that destination, if the destination is local or remote, and if remote what the current mount ID is. For a physical standby we should have at least one remote destination that points the primary set but it should be deferred.

select dest_id "ID",destination,status,target, archiver,schedule,process,mountid from v$archive_dest;

Query 5: If the protection mode of the standby is set to anything higher than max performance then we need to make sure the remote destination that points to the primary is set with the correct options else we will have issues during switchover. 

select dest_id,process,transmit_mode,async_blocks,net_timeout,delay_mins,reopen_secs,register,binding from v$archive_dest;

Query 6: The following select will show any errors that occured the last time an attempt to archive to the destination was attempted. If ERROR is blank and status is VALID then the archive completed correctly.

select dest_id,status,error from v$archive_dest;

Query 7: Determine if any error conditions have been reached by querying thev$dataguard_status view (view only available in 9.2.0 and above):

select message, timestamp from v$dataguard_status where severity in (‘Error’,’Fatal’) order by timestamp;

Query 8: The following query is ran to get the status of the SRL’s on the standby. If the primary is archiving with the LGWR process and SRL’s are present (in the correct number and size) then we should see a group# active.

select group#,sequence#,bytes,used,archived,status from v$standby_log;

Query 9: The above SRL’s should match in number and in size with the ORL’s returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

Query 10: Query v$managed_standby to see the status of processes involved in the configuration.

select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

Query 11: Verify that the last sequence# received and the last sequence# applied to standby database.

select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;

Query 12: The V$ARCHIVE_GAP fixed view on a physical standby database only returns the next gap that is currently blocking redo apply from continuing. After resolving the identified gap and starting redo apply, query the V$ARCHIVE_GAP fixed view again on the physical standby database to determine the next gap sequence, if there is one.

select * from v$archive_gap;