Diagnosis Oracle Data Guard Primary Site Configuration

-- NAME: DG_Primary_Database_Diagnosis.sql
-- ------------------------------------------------------------------------
-- AUTHOR: Mohammad Abdul Momin Arju
-- September 2010.
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is used to troubleshoot Oracle Data Guard issues for
-- Data Guard Primary database.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only.
-- Test it before you run in your production database.
-- ------------------------------------------------------------------------
-- Content of the Script

set echo off 
set feedback off 
column timecol new_value timestamp 
column spool_extension new_value suffix 
select to_char(sysdate,'Mondd_hhmi') timecol, 
'.out' spool_extension from sys.dual; 
column output new_value dbname 
select value || '_' output 
from v$parameter where name = 'db_name'; 
spool dg_prim_diag_&&dbname&×tamp&&suffix 
set linesize 79
set pagesize 35 
set trim on 
set trims on 
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS'; 
set feedback on 
select to_char(sysdate) time from dual; 
 
set echo on 

-- Select from v$database will give us the generic information about how this standby is
-- setup. The database_role can be SNAPSHOT STANDBY/LOGICAL STANDBY/PHYSICAL STANDBY/PRIMARY
-- It should be standby for standby database. PROTECTION_MODE can be,
-- MAXIMUM PROTECTION - Database is running in maximized protection mode
-- MAXIMUM AVAILABILITY - Database is running in maximized availability mode
-- RESYNCHRONIZATION - Database is running in resynchronization mode
-- MAXIMUM PERFORMANCE - Database is running in maximized protection mode
-- UNPROTECTED - Database is unprotected (this normally occurs when the primary database is mounted and not open)

-- 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.

column role format a7 tru 
column name format a10 wrap

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

-- ARCHIVER can be STOPPED/STARTED/FAILED -
-- Archiver FAILED means archiver failed to archive a log last time but will try
-- again within 5 minutes
-- LOG_SWITCH_WAIT can be ARCHIVE LOG/CLEAR LOG/CHECKPOINT/NULL -
-- NULL means ALTER SYSTEM SWITCH LOGFILE is hung but there is room in the current online redo log

column host_name format a20 tru 
column version format a9 tru 

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

-- Force logging is not mandatory but is recommended. Supplemental
-- logging must be enabled if the standby associated with this primary is
-- a logical standby. During normal operations it is acceptable for
-- SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.

column force_logging format a13 tru 
column remote_archive format a14 tru 
column dataguard_broker format a16 tru 

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

-- 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.
-- Status can be, VALID - Initialized and available
-- INACTIVE - No destination information
-- DEFERRED - Manually disabled by the user
-- ERROR - Error during open or copy
-- DISABLED - Disabled after error
-- BAD PARAM - Parameter has errors
-- ALTERNATE - Destination is in an alternate state
-- FULL - Exceeded quota size for the destination.
-- For a physical standby we should have at least one remote destination that points the primary set.

COLUMN destination FORMAT A35 WRAP 
column process format a10 
column archiver format a10 
column ID format 99 

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

-- This select will give further detail on the destinations as to what
-- options have been set. Register indicates whether or not the archived
-- redo log is registered in the remote destination control file.

set numwidth 8
column ID format 99 

select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,
       net_timeout net_time,delay_mins delay,reopen_secs reopen,
       register,binding 
from v$archive_dest order by dest_id;

-- 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.

column error format a80 tru 
select dest_id,status,error from v$archive_dest; 

-- Determine if any error conditions have been reached by querying the v$dataguard_status

column message format a100 
select message, timestamp 
from v$dataguard_status 
where severity in ('Error','Fatal') 
order by timestamp; 

-- The following query will determine the current sequence number
-- and the last sequence archived. If you are remotely archiving
-- using the LGWR process then the archived sequence should be one
-- higher than the current sequence. If remotely archiving using the
-- ARCH process then the archived sequence should be equal to the
-- current sequence. The applied sequence information is updated at
-- log switch time.

select ads.dest_id,max(sequence#) "Current Sequence",
       max(log_sequence) "Last Archived"
from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads 
where ad.dest_id=al.dest_id 
and al.dest_id=ads.dest_id 
group by ads.dest_id; 

-- The following select will attempt to gather as much information as
-- possible from the standby.

set numwidth 8
column ID format 99 
column "SRLs" format 99 
column Active format 99 

select dest_id id,database_mode db_mode,recovery_mode, 
       protection_mode,standby_logfile_count "SRLs",
       standby_logfile_active ACTIVE, 
       archived_seq# 
from v$archive_dest_status; 

-- Query v$managed_standby to see the status of processes involved in
-- the shipping redo on this system. Does not include processes needed to
-- apply redo.

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

-- The following query is run on the primary to see if SRL's have been
-- created in preparation for switchover.

select group#,sequence#,bytes from v$standby_log; 

-- 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; 

-- Verify all the Non-default init parameters.

set numwidth 5 
column name format a30 tru 
column value format a48 wra 
select name, value 
from v$parameter 
where isdefault = 'FALSE';
 
spool off