Diagnose physical standby database log synchronization and replay exceptions

In a physical standby scenario, log synchronization or replay between the primary and standby tenants (or databases) may encounter exceptions due to various factors such as network, resource, system load, and environment.

This topic provides a diagnostic process and key system views to help you quickly locate and resolve such exceptions.

Diagnostic flow

In OceanBase Database, log flow between primary and standby tenants is primarily divided into three stages: log transmission, log reception, and log replay. The system provides the following views to monitor the synchronization status of each stage between primary and standby tenants.

When an exception occurs in log synchronization between primary and standby tenants, you can diagnose it using the following flow:

1. In strong sync mode, on the primary tenant, query the GV$OB_LS_LOG_TRANSPORT_STAT or V$OB_LS_LOG_TRANSPORT_STAT view to check whether logs are being sent normally and whether the standby tenant has acknowledged them.

   Notice

   The log transmission service operates only in strong sync mode. Therefore, you can view the log transmission status of the primary tenant only when the primary tenant is in strong sync mode. In asynchronous sync mode, the standby tenant pulls logs from the primary tenant.

2. On the standby tenant, query the V$OB_LS_LOG_RESTORE_STATUS view to check whether log pulling or reception is normal.

3. On the standby tenant, query the GV$OB_LS_LOG_REPLAY_STAT or V$OB_LS_LOG_REPLAY_STAT view to check whether log replay is normal and whether replay is stuck or slow.

Diagnostic process

Log transmission phase

In strong sync mode, during the log transmission phase, you can confirm the log transmission status by checking the following fields in the GV$OB_LS_LOG_TRANSPORT_STAT or V$OB_LS_LOG_TRANSPORT_STAT view:

• end_lsn: The actual position on Palf. The primary database reads logs from Palf and sends them to the standby database.

• last_sent_lsn: The maximum position last read by the primary database.

• standby_end_lsn: The position persisted on the standby database. After receiving logs from the primary database, the standby database persists them to Palf on the standby side.

Procedure:

1. Check the log stream transmission status for all nodes of the primary tenant.

   • In the system tenant (sys tenant) of the cluster where the primary tenant resides, execute the following statement.

     obclient(root@sys)[(none)]> SELECT tenant_id, svr_ip, svr_port, ls_id, role, enabled,
                   end_lsn, last_sent_lsn, standby_end_lsn,
                   standby_addr
               FROM oceanbase.GV$OB_LS_LOG_TRANSPORT_STAT
               WHERE tenant_id= xxx AND role = 'LEADER';
     

   • In the primary tenant, execute the following statement.

     MySQL-compatible mode

     Oracle-compatible mode

     The statement is as follows:

     obclient(root@mysql001)[(none)]> SELECT svr_ip, svr_port, ls_id, role, enabled,
                   end_lsn, last_sent_lsn, standby_end_lsn,
                   standby_addr
               FROM oceanbase.GV$OB_LS_LOG_TRANSPORT_STAT
               WHERE role = 'LEADER';
     

     The statement is as follows:

     obclient(sys@oracle001)[(none)]> SELECT svr_ip, svr_port, ls_id, role, enabled,
                   end_lsn, last_sent_lsn, standby_end_lsn,
                   standby_addr
               FROM SYS.GV$OB_LS_LOG_TRANSPORT_STAT
               WHERE role = 'LEADER';
     

2. Check transmission lag.

   • In the system tenant (sys tenant) of the cluster where the primary tenant resides, execute the following statement.

     obclient(root@sys)[(none)]> SELECT tenant_id, ls_id,
                end_lsn - last_sent_lsn AS read_lag,
                last_sent_lsn - standby_end_lsn AS network_lag
            FROM oceanbase.GV$OB_LS_LOG_TRANSPORT_STAT
            WHERE tenant_id = xxx AND role = 'LEADER' AND enabled = 1;
     

   • In the primary tenant, execute the following statement.

     MySQL-compatible mode

     Oracle-compatible mode

     The statement is as follows:

     obclient(root@mysql001)[(none)]> SELECT ls_id,
                end_lsn - last_sent_lsn AS read_lag,
                last_sent_lsn - standby_end_lsn AS network_lag
            FROM oceanbase.GV$OB_LS_LOG_TRANSPORT_STAT
            WHERE role = 'LEADER' AND enabled = 1;
     

     The statement is as follows:

     obclient(sys@oracle001)[SYS]> SELECT ls_id,
                end_lsn - last_sent_lsn AS read_lag,
                last_sent_lsn - standby_end_lsn AS network_lag
            FROM SYS.GV$OB_LS_LOG_TRANSPORT_STAT
            WHERE role = 'LEADER' AND enabled = 1;
     

     Where:

     • read_lag: the value of end_lsn - last_sent_lsn, representing the lag in log transmission from the primary tenant to the standby tenant.

     • network_lag: the value of last_sent_lsn - standby_end_lsn, representing the lag in log reception by the standby tenant.

Based on the results of the two queries, diagnose the issue by referring to the following table:

Log reception phase

During the log reception phase, regardless of whether the tenant is in strong sync mode or not, you can use the V$OB_LS_LOG_RESTORE_STATUS view to confirm the log reception status of the log stream.

The procedure is as follows:

• In the system tenant (sys tenant) of the cluster where the standby tenant resides, execute the following statement.

  obclient(root@sys)[(none)]> SELECT tenant_id, ls_id, sync_lsn, sync_scn, sync_status, err_code
                 FROM oceanbase.V$OB_LS_LOG_RESTORE_STATUS WHERE tenant_id = xxx;
  

• In the standby tenant, execute the following statement.

  MySQL-compatible mode

  Oracle-compatible mode

  The statement is as follows:

  obclient(root@mysql001)[(none)]> SELECT ls_id, sync_lsn, sync_scn, sync_status, err_code
                 FROM oceanbase.V$OB_LS_LOG_RESTORE_STATUS;
  

  The statement is as follows:

  obclient(sys@oracle001)[SYS]> SELECT ls_id, sync_lsn, sync_scn, sync_status, err_code
                 FROM SYS.V$OB_LS_LOG_RESTORE_STATUS;
  

Where:

• sync_scn: The synchronization SCN of the log stream.

• sync_status: The synchronization status of the log stream. NORMAL indicates that log synchronization is normal. Any other value indicates an exception. The log synchronization statuses and their meanings are described in the following table.

  Log synchronization status	Diagnostic result
  NORMAL	Synchronizing normally
  SOURCE HAS A GAP	A gap exists between the standby tenant and the source logs. Logs on the primary tenant were reclaimed before being synchronized to the standby tenant. Rebuild the standby tenant to restore synchronization.
  STANDBY LOG NOT MATCH	The restored logs conflict with the standby tenant. This may be caused by dual primary tenants or an incorrect log restore source configuration. Reconfigure the log restore source for the standby tenant.
  CHECK USER OR PASSWORD	The replication account username or password is incorrect, so the original primary tenant cannot be accessed. Reconfigure the log restore source for the standby tenant.
  CHECK NETWORK	The primary tenant is unreachable. Check whether the network is abnormal.
  RESTORE SUSPEND	The standby tenant has been restored to the specified log restore endpoint.
  STANDBY NEED UPGRADE	The binary version of the standby tenant is outdated and must be upgraded.
  PRIMARY TENANT DROPPED	The primary tenant has been dropped.
  FETCH LOG TIMEOUT	Log synchronization timed out. Check network health or increase the tenant-level parameter standby_db_fetch_log_rpc_timeout for the standby tenant log synchronization timeout. For more information about standby_db_fetch_log_rpc_timeout, see standby_db_fetch_log_rpc_timeout
  STANDBY IN THROTTLING	The standby tenant is being write-throttled.
  STANDBY LOG DISK IS FULL	Insufficient disk space on the standby tenant.
  WAIT LOG STREAM CREATED	Waiting for the standby tenant to finish creating the log stream.
  NOT AVAILABLE	Log synchronization is unavailable due to other exceptions.

Log replay phase

During the log replay phase, you can use the following fields in the GV$OB_LS_LOG_REPLAY_STAT/V$OB_LS_LOG_REPLAY_STAT view to determine the log replay progress and replay lag:

• end_lsn: The position persisted on Palf.

• unsubmitted_lsn: The position of the next log to be submitted to the replay queue.

• min_unreplayed_lsn: The position of the earliest unreplayed log in the replay queue.

• pending_cnt: The number of tasks waiting to be replayed.

Procedure:

1. Check the replay progress on all nodes of the tenant.

   • In the system tenant (sys tenant) of the cluster where the standby tenant resides, execute the following statement.

     obclient(root@sys)[(none)]> SELECT tenant_id, svr_ip, svr_port, ls_id, role, enabled,
       end_lsn, unsubmitted_lsn, min_unreplayed_lsn,
       pending_cnt
     FROM oceanbase.GV$OB_LS_LOG_REPLAY_STAT WHERE tenant_id = xxx;
     

   • In the standby tenant, execute the following statement.

     MySQL-compatible mode

     Oracle-compatible mode

     The statement is as follows:

     obclient(root@mysql001)[(none)]> SELECT svr_ip, svr_port, ls_id, role, enabled,
       end_lsn, unsubmitted_lsn, min_unreplayed_lsn,
       pending_cnt
     FROM oceanbase.GV$OB_LS_LOG_REPLAY_STAT;
     

     The statement is as follows:

     obclient(sys@oracle001)[SYS]> SELECT svr_ip, svr_port, ls_id, role, enabled,
       end_lsn, unsubmitted_lsn, min_unreplayed_lsn,
       pending_cnt
     FROM SYS.GV$OB_LS_LOG_REPLAY_STAT;
     

2. Check replay lag.

   • In the system tenant (sys tenant) of the cluster where the standby tenant resides, execute the following statement.

     obclient(root@sys)[(none)]> SELECT tenant_id, ls_id,
       end_lsn - unsubmitted_lsn AS submit_lag,
       unsubmitted_lsn - min_unreplayed_lsn AS replay_lag,
       pending_cnt
      FROM oceanbase.GV$OB_LS_LOG_REPLAY_STAT
      WHERE tenant_id = xxx AND role = 'FOLLOWER';
     

   • In the standby tenant, execute the following statement.

     MySQL-compatible mode

     Oracle-compatible mode

     The statement is as follows:

     obclient(root@mysql001)[(none)]> SELECT ls_id,
       end_lsn - unsubmitted_lsn AS submit_lag,
       unsubmitted_lsn - min_unreplayed_lsn AS replay_lag,
       pending_cnt
      FROM oceanbase.GV$OB_LS_LOG_REPLAY_STAT
      WHERE role = 'FOLLOWER';
     

     The statement is as follows:

     obclient(sys@oracle001)[SYS]> SELECT ls_id,
       end_lsn - unsubmitted_lsn AS submit_lag,
       unsubmitted_lsn - min_unreplayed_lsn AS replay_lag,
       pending_cnt
      FROM SYS.GV$OB_LS_LOG_REPLAY_STAT
      WHERE role = 'FOLLOWER';
     

Based on the results of the two queries, refer to the following table for diagnosis: