FAQ
이중화 모니터링 쿼리
분류 모니터링 등록일 2013-07-08 조회수 2545
+이중화 모니터링 쿼리

[rep_mon.sql]
select
local_user_name||'.'||local_table_name tname,
a.replication_name rep_name,
d.host_ip || decode(d.host_ip, b.peer_ip, ' (*)', NULL) peer_ip,
nvl(to_char(e.rep_gap), 'n/a') as rep_gap,
a.xsn,
decode(b.peer_port, NULL, 'OFF', 'ON') as sender_stat,
decode(c.peer_port, NULL, 'OFF', 'ON') as receiver_stat
from
system_.sys_repl_items_ f,
system_.sys_repl_hosts_ d ,
system_.sys_replications_ a
left outer join v$repsender b
on a.replication_name = b.rep_name
left outer join v$repreceiver c
on a.replication_name = c.rep_name
left outer join
(
select rep_name, max(rep_gap) rep_gap from v$repgap
group by rep_name
) e
on a.replication_name = e.rep_name
where
a.replication_name = d.replication_name
AND a.replication_name = f.replication_name
order by rep_name, tname;


SELECT
trim(REP_NAME),
decode(START_FLAG, 0, 'Normal',
1, 'Quick',
2, 'Sync',
3, 'Sync Only'),
decode(NET_ERROR_FLAG, 0, 'Connected',1, 'Disconnected'),
decode(STATUS, 0, 'Stop', 1, 'Run', 2, 'Retry'),
trim(SENDER_IP),
trim(PEER_IP),
SENDER_PORT,
PEER_PORT
FROM V$REPSENDER;



+ 이중화 sender 상태
SELECT
trim(REP_NAME) as REP_NAME,
decode(START_FLAG, 0, 'Normal',
1, 'Quick',
2, 'Sync',
3, 'Sync Only') as START_FLAG,
decode(NET_ERROR_FLAG, 0, 'Connected',1, 'Disconnected') as NET_ERROR_FLAG,
decode(STATUS, 0, 'Stop', 1, 'Run', 2, 'Retry') as STATUS,
trim(SENDER_IP) as SENDER_IP,
trim(PEER_IP) as SENDER_IP,
SENDER_PORT,
PEER_PORT
FROM V$REPSENDER;



+ 이중화 receiver 상태
SELECT
trim(REP_NAME),
trim(MY_IP),
trim(PEER_IP),
MY_PORT,
PEER_PORT
FROM X$REPRECEIVER;



+ 이중화 gap 상태
select rep_name, rep_gap from v$repgap;
목록