Oracle 10G dataguard 主库诊断脚本

翻译 2017年08月22日 11:49:49
分享一个Oracle官方的10g Dataguard主库诊断SQL脚本,此脚本可以诊断出DG的相关配置,日志应用相关信息
-- NAME: new_dg_prim_diag.sql  (Run from sqlplus on PRIMARY with a LOGICAL or PHYSICAL STANDBY as SYS)
-- ------------------------------------------------------------------------  
-- Copyright 2002, Oracle Corporation       
-- LAST UPDATED: 15-Apr-2013
--
-- Usage: @new_dg_prim_diag
-- ------------------------------------------------------------------------  
-- PURPOSE:  
--    This script is to be used to assist in the collection of information to help
--    troubleshoot Data Guard issues with a Primary Database
------------------------------------------------------------------------  
-- DISCLAIMER:  
--    This script is provided for educational purposes only. It is NOT   
--    supported by Oracle World Wide Technical Support.  
--    The script has been tested and appears to work as intended.  
--    You should always run new scripts on a test instance initially.  
-- ------------------------------------------------------------------------  
-- Script output is as follows: 

set echo off 
set feedback off 
column timecol new_value timestamp 
column spool_extension new_value suffix 
SELECT TO_CHAR(sysdate,'Mondd_hhmi') timecol, '.html' spool_extension FROM dual; 
column output new_value dbname 
SELECT value || '_' output FROM v$parameter WHERE name = 'db_unique_name'; 
spool new_dg_prim_diag_&&dbname&×tamp&&suffix
set linesize 2000
set pagesize 50000
set numformat 999999999999999
set trim on 
set trims on 
set markup html on
set markup html entmap off

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; 
SELECT TO_CHAR(sysdate) time FROM dual; 

SELECT 'In the following output the DATABASE_ROLE should be PRIMARY as that is what this script is intended to be run on.<br>PLATFORM_ID should match the PLATFORM_ID of the standby(s) or conform to the supported options in<br>Note: 413484.1 Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration<br>Note: 1085687.1 Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration<br>OPEN_MODE should be READ WRITE.<br>LOG_MODE should be ARCHIVELOG.<br>FLASHBACK can be YES (recommended) or NO.<br>If PROTECTION_LEVEL is different from PROTECTION_MODE then for some reason the mode listed in PROTECTION_MODE experienced a need to downgrade.<br>Once the error condition has been corrected the PROTECTION_LEVEL should match the PROTECTION_MODE after the next log switch.' "Database 1" FROM dual;

SELECT database_role role, name, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;

SELECT 'FORCE_LOGGING is not mandatory but is recommended.<br>REMOTE_ARCHIVE should be ENABLE.<br>SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI must be enabled if the standby associated with this primary is a logical standby.<br>During normal operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.<br>DG_BROKER can be ENABLED (recommended) or DISABLED.' "Database 2" FROM dual;

column force_logging format a13 tru
column remote_archive format a14 tru 
column supplemental_log_data_pk format a24 tru
column supplemental_log_data_ui format a24 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;  

SELECT 'The following query gives us information about catpatch. From this we can tell if the catalog version doesn''t match the image version it was started with.' "Database 3" FROM dual;

column version format a10 tru 

SELECT version, modified, status FROM dba_registry WHERE comp_id = 'CATPROC';

SELECT 'Check how many threads are enabled and started for this database. If the number of instances below does not match then not all instances are up.' "Threads" FROM dual;

SELECT thread#, instance, status FROM v$thread;

SELECT 'The number of instances returned below is the number currently running.  If it does not match the number returned in Threads above then not all instances are up.<br>VERSION should match the version from CATPROC above.<br>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.<br>LOG_SWITCH_WAIT the ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for.<br>Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then the value is NULL.' "Instances" FROM dual;

column host_name format a32 wrap

SELECT thread#, instance_name, host_name, version, archiver, log_switch_wait FROM gv$instance ORDER BY thread#;

SELECT 'Check how often logs are switching. Log switches should not regularly be occuring in < 20 mins.<br>Excessive log switching is a performance overhead. Whilst rapid log switching is not in itself a Data Guard issue it can affect Data guard. It may also indicate a problem with log shipping.<br>Use redo log size = 4GB or redo log size >= peak redo rate x 20 minutes.' "Log Switches" FROM dual;

SELECT fs.log_switches_under_20_mins, ss.log_switches_over_20_mins FROM (SELECT  SUM(COUNT (ROUND((b.first_time - a.first_time) * 1440) )) "LOG_SWITCHES_UNDER_20_MINS"  FROM v$archived_log a, v$archived_log b WHERE a.sequence# + 1 = b.sequence# AND a.dest_id = 1 AND a.thread# = b.thread#  AND a.dest_id = b.dest_id AND a.dest_id = (SELECT MIN(dest_id) FROM gv$archive_dest WHERE target='PRIMARY' AND destination IS NOT NULL) AND ROUND((b.first_time - a.first_time) * 1440)  < 20 GROUP BY ROUND((b.first_time - a.first_time) * 1440))  fs, (SELECT  SUM(COUNT (ROUND((b.first_time - a.first_time) * 1440) )) "LOG_SWITCHES_OVER_20_MINS"  FROM v$archived_log a, v$archived_log b WHERE a.sequence# + 1 = b.sequence# AND a.dest_id = 1 AND a.thread# = b.thread#  AND a.dest_id = b.dest_id AND a.dest_id = (SELECT MIN(dest_id) FROM gv$archive_dest WHERE target='PRIMARY' AND destination IS NOT NULL) AND ROUND((b.first_time - a.first_time) * 1440)  > 19 GROUP BY ROUND((b.first_time - a.first_time) * 1440)) ss;

column  minutes  format a12

SELECT (CASE WHEN bucket = 1 THEN '<= ' || TO_CHAR(bucket* 5) WHEN (bucket >1 AND bucket < 9) THEN TO_CHAR(bucket * 5 - 4) || ' TO ' || TO_CHAR(bucket * 5) WHEN bucket > 8 THEN '>= ' || TO_CHAR(bucket * 5 - 4) END) "MINUTES", switches "LOG_SWITCHES" FROM (SELECT bucket , COUNT(b.bucket) SWITCHES FROM (SELECT WIDTH_BUCKET(ROUND((b.first_time - a.first_time) * 1440), 0, 40, 8) bucket FROM v$archived_log a, v$archived_log b WHERE a.sequence# + 1 = b.sequence# AND a.dest_id = b.dest_id  AND a.thread# = b.thread#  AND a.dest_id = (SELECT MIN(dest_id) FROM gv$archive_dest WHERE target = 'PRIMARY' AND destination IS NOT NULL)) b GROUP BY bucket ORDER BY bucket);


SELECT 'Check the number and size of online redo logs on each thread.' "Online Redo Logs" FROM dual;

set feedback on

SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#; 

set feedback off

SELECT 'The following query is run to see if standby redo logs have been created in preparation for switchover.<br>The standby redo logs should be the same size as the online redo logs.<br>There should be (( # of online logs per thread + 1) * # of threads) standby redo logs.<br>A value of 0 for the thread# means the log has never been allocated.' "Standby Redo Logs" FROM dual;

set feedback on

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#; 

set feedback off

SELECT 'This query produces a list of defined 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.' "Archive Destinations" FROM dual;

column destination format a35 wrap 
column process format a7 
column ID format 99 
column mid format 99
 
SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id; 

SELECT 'This select will give further detail on the destinations as to what options have been set.<br>Register indicates whether or not the archived redo log is registered in the remote destination control file.' "Archive Destination Options" FROM dual;

set numwidth 8
column archiver format a8 
column ID format 99 
column error format a55 wrap

SELECT thread#, dest_id, gvad.archiver, transmit_mode, affirm, async_blocks, net_timeout, delay_mins, reopen_secs reopen, register, binding FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id; 

SELECT 'The following select will show any errors that occured the last time an attempt to archive to the destination was attempted.<br>If ERROR is blank and status is VALID then the archive completed correctly.' "Archive Destination Errors" FROM dual;

SELECT thread#, dest_id, gvad.status, error FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id; 

SELECT '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).' "Data Guard Status" FROM dual;

column message format a80 

set feedback on

SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;

set feedback off

SELECT 'Query v$managed_standby to see the status of processes involved in the shipping redo on this system.<br>Does not include processes needed to apply redo.' "Managed Standby Status" FROM dual;

SELECT inst_id, thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, pid;

SELECT 'The following query will determine the current sequence number and the last sequence archived.<br>If you are remotely archiving using the LGWR process then the archived sequence should be one higher than the current sequence.<br>If remotely archiving using the ARCH process then the archived sequence should be equal to the current sequence.<br>The applied sequence information is updated at log switch time.' "Archived Sequences" FROM dual;

SELECT la.thread#, la.dest_id, currentsequence "Current Sequence", lastarchived  "Last Archived" FROM (SELECT gval.thread#, gvad.dest_id, MAX(gvad.log_sequence) lastarchived FROM gv$archive_dest gvad, gv$archived_log gval where gvad.inst_id = gval.inst_id AND gvad.dest_id = gval.dest_id AND thread# = gval.inst_id GROUP BY gval.thread#, gvad.dest_id) la, (SELECT thread#, dest_id, MAX(sequence#) currentsequence FROM  gv$archived_log WHERE resetlogs_change# = (SELECT MAX(resetlogs_change#) FROM v$archived_log) AND thread# = inst_id GROUP BY  thread#, dest_id) cs WHERE cs.thread# = la.thread# and cs.dest_id = la.dest_id ORDER BY thread#, dest_id;

SELECT 'The following select will attempt to gather as much information as possible from the standby.<br>Standby redo logs are not supported with Logical Standby until Version 10.1.<br>The ARCHIVED_SEQUENCE# from a logical standby is the sequence# created by the apply, not the sequence# sent from the primary.' "Archive Destination Status" FROM dual;

set numwidth 8
column dest_id format 99 
column Active format 99

SELECT dest_id, database_mode, recovery_mode, protection_mode, standby_logfile_count, standby_logfile_active, archived_seq# FROM v$archive_dest_status WHERE destination IS NOT NULL; 
 
SELECT 'Non-default init parameters.<br>For a RAC DB Thread# = * means the value is the same for all threads (SID=*)<br>Threads with different values are shown with their individual thread# and values.' "Non Default init Parameters" FROM dual;

column num noprint

SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE isdefault = 'FALSE'
MINUS
SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND  gvpa.value <> gvpb.value AND gvpa.isdefault = 'FALSE') AND gvi.inst_id = gvp.inst_id  AND gvp.isdefault = 'FALSE')
UNION
SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND  gvpa.value <> gvpb.value AND gvpa.isdefault = 'FALSE') AND gvi.inst_id = gvp.inst_id  AND gvp.isdefault = 'FALSE' ORDER BY 1, 2;

spool off
set markup html off entmap on
set feedback on
set echo on

oracle dataguard 主库归档日志缺失,导致主备库不一致故障处理

背景描述: 主库上由于归档日志未及时清理,导致根目录满了,虚拟化环境,系统ping通,但连接不上,只好重启。 由于主库上堆积了大量归档日志未应用到备库,所以备库上一直在追加日志,然后在我低头沉思卖...
  • killvoon
  • killvoon
  • 2017年07月07日 10:31
  • 767

ORACLE DataGuard 主备库角色切换

主备库切换原因: 1. switch:用户主动切换; 2. failover:主库出现故障,强行切换;   switch切换过程:先主库再备库   主库 1....
  • techsupporter
  • techsupporter
  • 2016年08月12日 19:25
  • 878

Oracle-11G-DataGuard 一主库多备库详细配置

1  说明 基于网上的文档做了补充说明改动形成的文档。 2 判断DataGuard是否安装 select * from v$option where parameter = 'Oracle Da...
  • alangmei
  • alangmei
  • 2014年07月31日 09:24
  • 3946

Oracle11g_DataGuard_架构

1、为什么要使用Data Guard 使用Data Guard可以实现以下需求: 高可用性——通过快速故障转移,在生产库发生问题时,立刻将备库切换为主库。 数据保护——通过备库实现了...
  • wang_san_shi
  • wang_san_shi
  • 2015年01月22日 15:25
  • 527

Oracle DG之--构建Physical Standby(一主库对应多备库)

Oracle DG之--构建Physical Standby(一主库对应多备库)系统环境:操作系统: RedHat EL6Oracle:    Oracle 10.2.0.1.0650) this.w...
  • lqx0405
  • lqx0405
  • 2015年03月31日 12:10
  • 4556

Oracle dataguard之备库切换(failover)

oracle 11g中有一个新特性叫flush redo,就是说如果主库挂了,只要能mount,就可以将没传到备库的online redo log信息刷到备库去,这样就不会有数据丢失。 1.在主库f...
  • cscscscsc
  • cscscscsc
  • 2016年04月03日 18:19
  • 721

Oracle 10G dataguard 备库诊断脚本

分享一个Oracle官方的10g Dataguard备库诊断SQL脚本,此脚本可以诊断出DG的相关配置,日志应用相关信息  -- NAME: new_dg_psby_diag.sql (Run fro...
  • Evils798
  • Evils798
  • 2017年08月22日 11:56
  • 273

Oracle OMF特性导致的DataGuard Duplicate构建备库的Datafiles命名问题

今天凌晨数据库系统做架构变更,构建DataGuard Max Availability主备模式。由于之前已经在自己新构建的测试环境下进行了多次的演练,本以为会在半个小时结束搭建Dg的战斗,支援其它战线...
  • ltc1183705927
  • ltc1183705927
  • 2017年01月22日 21:17
  • 236

DataGuard主备切换及故障切换

点击打开链接 DataGuard主备切换及故障切换 JUNE 12, 2014 DataGuard是从Oracle7.3开始引进的,在8i之前叫standby database,...
  • qq_36276335
  • qq_36276335
  • 2017年02月16日 16:39
  • 695

dataguard :主库的一个归档日志没传送到备库,导致备库无法同步

FAL[client]: Failed to request gap sequence  GAP - thread 2 sequence 1837-1837  DBID 265008880 branc...
  • gudagudaga
  • gudagudaga
  • 2015年02月11日 13:50
  • 1294
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 10G dataguard 主库诊断脚本
举报原因:
原因补充:

(最多只允许输入30个字)