Overall status of replication


set linesize 1024
set colsize 20
SELECT a.replication_name rep_name
     , d.host_ip || decode(d.host_ip, b.peer_ip, ' (*)', NULL) peer_ip
     , nvl(to_char(e.rep_gap), '-') as rep_gap
     , a.xsn restart_xsn
     , decode(b.peer_port, NULL, 'OFF', 'ON') as sender
     , decode(c.peer_port, NULL, 'OFF', 'ON') as receiver 
  FROM 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
 ORDER BY rep_name;
REP_NAME              PEER_IP               REP_GAP               RESTART_XSN          SENDER  RECEIVER  
----------------------------------------------------------------------------------------------------------------
REP                   192.168.1.149 (*)     0                     9668                 ON   ON

Column description

Column nameDescription
restart_xsnSN reflected by the remote server that is the target of replication, and the starting point for retransmission when the replication is restarted
senderWhether the sender is running or not
receiverWhether the receiver is running or not

The name of the replication, IP, sender status, and receiver status can also be checked.

Replication sender information


set linesize 1024
set colsize 20
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, 'OK', 'Error') as NET_ERROR_FLAG
     , decode(STATUS, 0, 'Stop', 1, 'Run', 2, 'Retry') as STATUS
     , peer_ip
     , peer_port
     , XSN
  FROM V$REPSENDER;
REP_NAME              START_FLAG  NET_ERROR_FLAG  STATUS  PEER_IP               PEER_PORT   XSN                  
-------------------------------------------------------------------------------------------------------------------------
REP                   Normal      OK              Run     192.168.1.149         30300       9675

Column description

Column nameDescription
rep_nameName of replication object
peer_ipIP address of the replication target remote server
peer_portPort number of the replication target remote server
StatusIt is normal when the current state of sender is 1. / STOP(0), RUN(1), RETRY(2)
repl_modesender's current replication mode / lazy, eager
NET_ERROR_FLAGnetwork 에러 여부로 0이어야 정상이다. / OK(0), ERROR(1)
XSNsender가 마지막으로 송신한 SN(Serial Number/리두로그일련번호)으로 v$repgap의 REP_SN과 동일

The IP, port, network error, and status of the replication sender's remote server can also be checked.

 

Replication receiver information


set linesize 1024
set colsize 20
SELECT trim(REP_NAME)
     , trim(MY_IP)
     , trim(PEER_IP)
     , MY_PORT
     , PEER_PORT
     , apply_xsn
  FROM X$REPRECEIVER;
TRIM(REP_NAME)        TRIM(MY_IP)           TRIM(PEER_IP)         MY_PORT     PEER_PORT   APPLY_XSN            
----------------------------------------------------------------------------------------------------------------------
REP                   192.168.1.145         192.168.1.149         30300       26722       13461585

Column description

Column nameDescription
peer_ipIP address of the remote server as the subject of replication
peer_portPort number of the remote server that is the subject of replication
apply_xsnSN of the remote server currently being reflected by the receiver

The IP and port of the remote server of the replication receiver can be checked.

 

Replication gap


set linesize 1024
set colsize 20
select rep_name
     , rep_gap
  from v$repgap;
REP_NAME              REP_GAP              
----------------------------------------------
REP                   0 

Column description

Column nameDescription
rep_nameName of replication
rep_gapThe degree of unsynchronization is indicated by the interval between rep_last_sn and rep_sn. (I.e. rep_last_sn-rep_sn)

If the replication gap is increased by a lot, there are things to check.

  1. Check the network status (operation, failure, if IP or port is blocked by the firewall, etc.)
  2. Check the remote requirement status (hardware failure, remote DB shutdown, etc.)
  3. Check the BULK DML operation

In the above case, the replication gap may increase, so it is necessary to check the above cases.