Skip to content

Replication monitoring query#

Overall status of replication#

You can check the name, IP, sender status, and receiver status of the 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 name Description
restart_xsn SN reflected by the remote server that is the target of replication, and the starting point for retransmission when the replication is restarted
sender Whether the sender is running or not
receiver Whether the receiver is running or not

Replication sender information#

You can check the replication sender's remote server IP, port, network error status, state, and more.

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 name Description
rep_name Name of the replication object
peer_ip IP address of the replication target remote server
peer_port Port number of the replication target remote server
status Normal when the current sender status is 1. / 0: stop, 1: run, 2: retry
repl_mode Current replication mode of the sender / lazy, eager
net_error_flag Whether a network error has occurred; normal when 0. / 0: ok, 1: error
xsn The last SN (Serial Number/redo log serial number) sent by the sender; same as rep_sn in v$repgap

Replication receiver information#

You can check the replication receiver's remote server IP, port, and more.

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 name Description
peer_ip IP address of the remote server as the subject of replication
peer_port Port number of the remote server that is the subject of replication
apply_xsn SN of the remote server currently being reflected by the receiver

Replication gap#

Altibase 6 or earlier#

The REP_GAP column shows the gap in log serial numbers between REP_LAST_SN and REP_SN.

In other words, it represents the gap between the most recently logged log record from the local server's transactions and the log record currently being sent by the replication sender thread.

set linesize 1024
set colsize 20
select rep_name
     , rep_gap
  from v$repgap;
REP_NAME              REP_GAP              
----------------------------------------------
REP                   0
  • Column description
Column name Description
rep_name Name of the replication
rep_gap The degree of unsynchronization is indicated by the interval between rep_last_sn and rep_sn. (i.e., rep_last_sn - rep_sn)

Altibase 7 or later#

Starting from Altibase 7, the v$repgap view includes a new column called REP_GAP_SIZE, which allows you to check the size of the replication gap in bytes.

Unlike before, the meaning of the REP_GAP column now refers to the size between the most recently logged position by the local server transaction and the current log position being transmitted by the replication sending thread.

The default unit is megabytes (MB), and the value in REP_GAP_SIZE is divided by the REPLICATION_GAP_UNIT property.

set linesize 1024
set colsize 20
select rep_name
     , rep_gap
     , rep_gap_size
  from v$repgap;
REP_NAME        REP_GAP              REP_GAP_SIZE         
--------------------------------------------------------------------
REP             0                    0
  • Column description
Column name Description
rep_name Name of the replication
rep_gap The log file size of the replication gap is displayed in the unit set by the REPLICATION_GAP_UNIT property. (Unit: REPLICATION_GAP_UNIT, default value 1 MB)
* REP_GAP = CEIL(REP_GAP_SIZE / REPLICATION_GAP_UNIT)
rep_gap_size The log file size of the replication gap, displayed in bytes.
  • If the replication gap increases significantly, there are some things to check.

    1. Check the network status (e.g., maintenance, failures, or if the IP or port is blocked by a firewall)
    2. Check the remote server status (e.g., hardware failure, disk full, or the remote DB being shut down)
    3. Check whether bulk DML operations are being performed

    In the above cases, the replication gap may continue to increase, so it is important to check and take prompt action.