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.
- Check the network status (e.g., maintenance, failures, or if the IP or port is blocked by a firewall)
- Check the remote server status (e.g., hardware failure, disk full, or the remote DB being shut down)
- 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.