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
---------- ---------- --------------- ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
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
'Oracle Database' 카테고리의 다른 글
| Oracle 내 table에 걸쳐있는 constraint 찾고, disable 하기 (0) | 2010.04.06 |
|---|---|
| Oracle DECODE() Function (0) | 2010.04.06 |
| Oracle 전문가가 되기위한 추천 책,자료 (0) | 2010.03.30 |
| Oracle FLASHBACK TABLE - sample (2) | 2010.03.30 |
| oracle10g rac new feature - DRM (Dynamic Resource Mastering) (0) | 2010.03.25 |