oracle dg监控

1. dg监控

2. 数据库状态

col name for a10
SELECT name,
       log_mode,
       open_mode,
       protection_mode,
       protection_level,
       database_role,
       dataguard_broker,
       switchover_status,
       force_logging
  FROM v$database;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xfD5yR6v-1617853276120)(_v_images/20190919152741167_3163.png =1000x)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gRSDazUl-1617853276126)(_v_images/20190919152801846_15228.png =1000x)]

3. 监控备库和主库时间差距

col name for a24
col value for a16
col datum_time for a24
col time_computed for a24
SELECT name, value, datum_time, time_computed  FROM V$DATAGUARD_STATS;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7U0wdtyA-1617853276138)(_v_images/20190920105448482_30897.png =1000x)]

4. 监控redo传输

4.1. 查询参数文件归档目的地配置

col dest_name for a20
col dest_value for a80
SELECT name dest_name, VALUE dest_value
  FROM v$parameter
 WHERE name LIKE 'log_archive_dest_%'
   AND name IN (SELECT lower(dest_name)
                  FROM v$archive_dest
                 WHERE status NOT IN ('DEFERRED', 'INACTIVE'));

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uJrafFeI-1617853276143)(_v_images/20190919163255238_26870.png =1000x)]

4.2. 查询redo传输状态

4.2.1. 查询归档目的地状态

col dest_name for a20
SELECT dest_name,
       status,
       TYPE,
       destination,
       database_mode,
       protection_mode,
       archived_thread#,
       archived_seq#,
       applied_thread#,
       applied_seq#,
       db_unique_name,
       gap_status
  FROM v$archive_dest_status
 WHERE STATUS NOT IN ('DEFERRED', 'INACTIVE');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9u0uXhOl-1617853276149)(_v_images/20190919162159345_4326.png =1000x)]

4.2.2. 查询主库最新归档序列

SELECT THREAD#, MAX(SEQUENCE#) max_sequence#
  FROM V$ARCHIVED_LOG
 WHERE RESETLOGS_CHANGE# =
       (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)
 GROUP BY THREAD#;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xFGzd3DX-1617853276154)(_v_images/20190919154005225_29053.png)]

4.2.3. 查询所有归档目的地最新归档序列

col destination for a40
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
  FROM V$ARCHIVE_DEST_STATUS
 WHERE STATUS NOT IN ('DEFERRED', 'INACTIVE');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-69nVcelj-1617853276160)(_v_images/20190919153940791_17327.png)]

4.2.4. 查询未传输到备库的归档日志

假设1为本地，2为备库

SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#
  FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID = 1) LOCAL
 WHERE LOCAL.SEQUENCE# NOT IN
       (SELECT SEQUENCE#
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2
           AND THREAD# = LOCAL.THREAD#);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1JNoXE9p-1617853276166)(_v_images/20190919154708511_16204.png)]

4.2.5. 验证备库是有有GAP

假设dest_id为2

SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wSbq6xQm-1617853276169)(_v_images/20190920101714046_27462.png)]

4.3. 查询redo传输响应时间

SELECT DEST_ID, FREQUENCY, DURATION
  FROM V$REDO_DEST_RESP_HISTOGRAM
 WHERE FREQUENCY > 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ej8wa942-1617853276173)(_v_images/20190919170443402_16481.png)]

4.4. 注册归档日志

物理备库：

ALTER DATABASE REGISTER LOGFILE '/u02/app/oracle/arch/stdtest/1_19_1019298037.arc';

逻辑备库：

ALTER DATABASE REGISTER LOGICAL LOGFILE '/u02/app/oracle/arch/stdtest/1_19_1019298037.arc';

4.5. 查询物理备库进程状态

SELECT process,
       client_process,
       status,
       thread#,
       sequence#,
       block#,
       blocks,
       delay_mins
  FROM v$managed_standby;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DoS3KrNH-1617853276177)(_v_images/20190920142454976_11605.png =1000x)]
block#,
blocks,
delay_mins
FROM v$managed_standby;

[外链图片转存中...(img-DoS3KrNH-1617853276177)]