Script to Collect Data Guard Diagnostic Information-白红宇

Script to Collect Data Guard Diagnostic Information

阅读量：6275 次

发布时间：2019-06-22

本文共 18788 字，大约阅读时间需要 62 分钟。

Overview -------- This script is intended to provide an easy method to provide information necessary to troubleshoot Data Guard issues. Script Notes ------------- This script is intended to be run via sqlplus as the SYS or Internal user. Script -------

- - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - --- NAME: dg_prim_diag.sql  (Run on PRIMARY with a LOGICAL or PHYSICAL STANDBY)set echo offset feedback offcolumn timecol new_value timestampcolumn spool_extension new_value suffixselect to_char(sysdate,'Mondd_hhmi') timecol,'.out' spool_extension from sys.dual;column output new_value dbnameselect value || '_' outputfrom v$parameter where name = 'db_name';spool dg_prim_diag_&&dbname&&timestamp&&suffixset linesize 79set pagesize 35set trim onset trims onalter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';set feedback onselect to_char(sysdate) time from dual;set echo on-- In the following the database_role should be primary as that is what-- this script is intended to be run 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.column role format a7 trucolumn name format a10 wrapselect name,database_role role,log_mode,protection_mode,protection_levelfrom v$database;-- 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-- NULLcolumn host_name format a20 trucolumn version format a9 truselect instance_name,host_name,version,archiver,log_switch_waitfrom v$instance;-- The following query give us information about catpatch.-- This way we can tell if the procedure doesn't match the image.select version, modified, status from dba_registrywhere comp_id = 'CATPROC';-- 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 trucolumn remote_archive format a14 trucolumn dataguard_broker format a16 truselect force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui,switchover_status,dataguard_brokerfrom v$database;-- This query produces a list of all archive destinations.  It 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.column destination format a35 wrapcolumn process format a7column archiver format a8column ID format 99column mid format 99select dest_id "ID",destination,status,target,schedule,process,mountid  midfrom v$archive_dest order by dest_id;-- 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 8column ID format 99select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,net_timeout net_time,delay_mins delay,reopen_secs reopen,register,bindingfrom 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 a55 wrapselect dest_id,status,error from v$archive_dest;-- The query below will determine if any error conditions have been-- reached by querying the v$dataguard_status view (view only available in-- 9.2.0 and above):column message format a80select message, timestampfrom v$dataguard_statuswhere 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 adswhere ad.dest_id=al.dest_idand al.dest_id=ads.dest_idgroup by ads.dest_id;-- The following select will attempt to gather as much information as-- possible from the standby.  SRLs are not supported with Logical Standby-- until Version 10.1.set numwidth 8column ID format 99column "SRLs" format 99column Active format 99select 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;-- Non-default init parameters.set numwidth 5column name format a30 trucolumn value format a48 wraselect name, valuefrom v$parameterwhere isdefault = 'FALSE';spool off- - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -

- - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - --- NAME: DG_phy_stby_diag.sqlset echo offset feedback offcolumn timecol new_value timestampcolumn spool_extension new_value suffixselect to_char(sysdate,'Mondd_hhmi') timecol,'.out' spool_extension from sys.dual;column output new_value dbnameselect value || '_' outputfrom v$parameter where name = 'db_name';spool dgdiag_phystby_&&dbname&&timestamp&&suffixset lines 200set pagesize 35set trim onset trims onalter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';set feedback onselect to_char(sysdate) time from dual;set echo on---- 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 NULLcolumn host_name format a20 trucolumn version format a9 truselect instance_name,host_name,version,archiver,log_switch_wait from v$instance;-- 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.column ROLE format a7 truselect name,database_role,log_mode,controlfile_type,protection_mode,protection_levelfrom v$database;-- 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.column force_logging format a13 trucolumn remote_archive format a14 trucolumn dataguard_broker format a16 truselect 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. For a physical standby we should have at-- least one remote destination that points the primary set but it should be deferred.COLUMN destination FORMAT A35 WRAPcolumn process format a7column archiver format a8column ID format 99select dest_id "ID",destination,status,target,archiver,schedule,process,mountidfrom v$archive_dest;-- 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,bindingfrom v$archive_dest;-- 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 a55 truselect dest_id,status,error from v$archive_dest;-- Determine if any error conditions have been reached by querying thev$dataguard_status-- view (view only available in 9.2.0 and above):column message format a80select message, timestampfrom v$dataguard_statuswhere severity in ('Error','Fatal')order by timestamp;-- 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;-- 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 v$managed_standby to see the status of processes involved in the-- configuration.select process,status,client_process,sequence#,block#,active_agents,known_agentsfrom v$managed_standby;-- Verify that the last sequence# received and the last sequence# applied to standby-- database.select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"from (select thread# thrd, max(sequence#) almaxfrom v$archived_logwhere resetlogs_change#=(select resetlogs_change# from v$database)group by thread#) al,(select thread# thrd, max(sequence#) lhmaxfrom v$log_historywhere first_time=(select max(first_time) from v$log_history)group by thread#) lhwhere al.thrd = lh.thrd;-- 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;-- Non-default init parameters.set numwidth 5column name format a30 trucolumn value format a50 wraselect name, valuefrom v$parameterwhere isdefault = 'FALSE';spool off- - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -

- - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - --- NAME: dg_lsby_diag.sql  (Run on LOGICAL STANDBY)set echo offset feedback offcolumn timecol new_value timestampcolumn spool_extension new_value suffixselect to_char(sysdate,'Mondd_hhmi') timecol,'.out' spool_extension from sys.dual;column output new_value dbnameselect value || '_' outputfrom v$parameter where name = 'db_name';spool dg_lsby_diag_&&dbname&&timestamp&&suffixset linesize 79set pagesize 180set long 1000set trim onset trims onalter session set nls_date_format = 'MM/DD HH24:MI:SS';set feedback onselect to_char(sysdate) time from dual;set echo on-- The following select will give us the generic information about how-- this standby is setup.  The database_role should be logical standby as-- that is what this script is intended to be ran on.column ROLE format a7 trucolumn NAME format a8 wrapselect name,database_role,log_mode,protection_modefrom v$database;-- 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-- NULLcolumn host_name format a20 trucolumn version format a9 truselect instance_name,host_name,version,archiver,log_switch_waitfrom v$instance;-- The following query give us information about catpatch.-- This way we can tell if the procedure doesn't match the image.select version, modified, status from dba_registrywhere comp_id = 'CATPROC';-- Force logging and supplemental logging are not mandatory but are-- recommended if you plan to switchover.  During normal operations it is-- acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or NOT ALLOWED.column force_logging format a13 trucolumn remote_archive format a14 trucolumn dataguard_broker format a16 truselect force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui,switchover_status,dataguard_brokerfrom v$database;-- This query produces a list of all archive destinations.  It 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.column destination format a35 wrapcolumn process format a7column archiver format a8column ID format 99column mid format 99select dest_id "ID",destination,status,target,schedule,process,mountid  midfrom v$archive_dest order by dest_id;-- 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 8column ID format 99select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,net_timeout net_time,delay_mins delay,reopen_secs reopen,register,bindingfrom v$archive_dest order by dest_id;-- Determine if any error conditions have been reached by querying the-- v$dataguard_status view (view only available in 9.2.0 and above):column message format a80select message, timestampfrom v$dataguard_statuswhere severity in ('Error','Fatal')order by timestamp;-- 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;-- Verify that log apply services on the standby are currently-- running. If the query against V$LOGSTDBY returns no rows then logical-- apply is not running.column status format a50 wrapcolumn type format a11set numwidth 15SELECT TYPE, STATUS, HIGH_SCNFROM V$LOGSTDBY;-- The DBA_LOGSTDBY_PROGRESS view describes the progress of SQL apply-- operations on the logical standby databases.  The APPLIED_SCN indicates-- that committed transactions at or below that SCN have been applied. The-- NEWEST_SCN is the maximum SCN to which data could be applied if no more-- logs were received. This is usually the MAX(NEXT_CHANGE#)-1 from-- DBA_LOGSTDBY_LOG.  When the value of NEWEST_SCN and APPLIED_SCN are the-- equal then all available changes have been applied.  If your-- APPLIED_SCN is below NEWEST_SCN and is increasing then SQL apply is-- currently processing changes.set numwidth 15select(casewhen newest_scn = applied_scn then 'Done'when newest_scn <= applied_scn + 9 then 'Done?'when newest_scn > (select max(next_change#) from dba_logstdby_log)then 'Near done'when (select count(*) from dba_logstdby_logwhere (next_change#, thread#) not in(select first_change#, thread# from dba_logstdby_log)) > 1then 'Gap'when newest_scn > applied_scn then 'Not Done'else '---' end) "Fin?",newest_scn, applied_scn, read_scn from dba_logstdby_progress;select newest_time, applied_time, read_time from dba_logstdby_progress;-- Determine if apply is lagging behind and by how much.  Missing-- sequence#'s in a range indicate that a gap exists.set numwidth 15column trd format 99select thread# trd, sequence#,first_change#, next_change#,dict_begin beg, dict_end end,to_char(timestamp, 'hh:mi:ss') timestamp,(case when l.next_change# < p.read_scn then 'YES'when l.first_change# < p.applied_scn then 'CURRENT'else 'NO' end) appliedfrom dba_logstdby_log l, dba_logstdby_progress porder by thread#, first_change#;-- Get a history on logical standby apply activity.set numwidth 15select to_char(event_time, 'MM/DD HH24:MI:SS') time,commit_scn, current_scn, event, statusfrom dba_logstdby_eventsorder by event_time, commit_scn, current_scn;-- Dump logical standby statscolumn name format a40column value format a20select * from v$logstdby_stats;-- Dump logical standby parameterscolumn name format a33 wrapcolumn value format a33 wrapcolumn type format 99select name, value, type from system.logstdby$parametersorder by type, name;-- Gather log miner session and dictionary information.set numwidth 15select * from system.logmnr_session$;select * from system.logmnr_dictionary$;select * from system.logmnr_dictstate$;select * from v$logmnr_session;-- Query the log miner dictionary for key tables necessary to process-- changes for logical standby Label security will move AUD$ from SYS to-- SYSTEM.  A synonym will remain in SYS but Logical Standby does not-- support this.set numwidth 5column name format a9 wrapcolumn owner format a6 wrapselect o.logmnr_uid, o.obj#, o.objv#, u.name owner, o.namefrom system.logmnr_obj$ o, system.logmnr_user$ uwhereo.logmnr_uid = u.logmnr_uid ando.owner# = u.user# ando.name in ('JOB$','JOBSEQ','SEQ$','AUD$','FGA_LOG$','IND$','COL$','LOGSTDBY$PARAMETER')order by u.name;-- Non-default init parameters.column name format a30 trucolumn value format a48 wraselect name, valuefrom v$parameterwhere isdefault = 'FALSE';spool off- - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -

转载地址：http://dprpa.baihongyu.com/

你可能感兴趣的文章