Oracle Active DataGuard Monitoring script

Active DataGuard 모니터링하는 SQL script를 만들어 보았습니다.
수행환경은 oracle 11g release 1 입니다.

1. 현재 dataguard 관련 process 모니터링
2. 마지막 전송받은 archived log file
3. Primary와 Standby 간의 GAP, 지연 정보 등

column process format a10

column status format a15

column client_pid format a10

column group# format a10

column name format a60

set linesize 200

alter session set nls_date_format = 'MM/DD HH24:MI:SS';

select PROCESS,PID,STATUS,CLIENT_PROCESS,CLIENT_PID,GROUP#,THREAD#,SEQUENCE#,DELAY_MINS,BLOCK#,BLOCKS from V$MANAGED_STANDBY

where status not in ('IDLE','CLOSING')

order by status

/

select b.NAME,b.DEST_ID, b.THREAD#,b.SEQUENCE#,b.STANDBY_DEST,b.ARCHIVED,b.APPLIED,b.STATUS,b.COMPLETION_TIME

from 

(select thread#,max(sequence#) sequence from V$ARCHIVED_LOG group by thread#) a, v$archived_log b

where a.thread# = b.thread# and a.sequence = b.sequence# 

/

column name format a30

column value format a50

column TIME_COMPUTED format a30

column unit format a30

select * from V$DATAGUARD_STATS

/

PROCESS           PID STATUS          CLIENT_PROCESS           CLIENT_PID GROUP#        THREAD#  SEQUENCE# DELAY_MINS     BLOCK#     BLOCKS
---------- ---------- --------------- ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
MRP0            11615 APPLYING_LOG    N/A                      N/A        N/A                 1      47741          0     214659    1024000
ARCH             9762 CONNECTED       ARCH                     9762       N/A                 0          0          0          0          0


NAME                                                            DEST_ID    THREAD#  SEQUENCE# STANDBY_D ARCHIVED  APPLIED                     STATUS          COMPLETION_TIM
------------------------------------------------------------ ---------- ---------- ---------- --------- --------- --------------------------- -------------
/ARC2/2_21989_697581426.arc                             1          2      21989 NO        YES       YES                         A               03/23 14:39:46
/ARC3/3_21296_697581426.arc                             1          3      21296 NO        YES       YES                         A               03/23 15:06:36
/ARC1/1_47740_697581426.arc                             1          1      47740 NO        YES       IN-MEMORY               A               03/23 16:05:58


NAME                           VALUE                                              UNIT                           TIME_COMPUTED
------------------------------ -------------------------------------------------- ------------------------------ ------------------------------
apply finish time              +00 00:00:03.5                                     day(2) to second(1) interval   23-MAR-2010 16:24:19
apply lag                      +00 00:00:00                                       day(2) to second(0) interval   23-MAR-2010 16:24:19
estimated startup time         32                                                 second                         23-MAR-2010 16:24:19
standby has been open          N                                                                                 23-MAR-2010 16:24:19
transport lag                  +00 00:00:00                                       day(2) to second(0) interval   23-MAR-2010 16:24:19