Check the log synchronization and replay status of primary and standby tenants

This topic describes how to check the log synchronization and replay status of primary and standby tenants during routine maintenance.

Step 1: Check the protection mode and tenant-level positions (SCNs)

For OceanBase Database V4.4.2, starting from V4.4.2 BP1, OceanBase Database supports the maximum protection mode and the maximum availability mode. For primary and standby tenants, each tenant's protection mode and level are independent. Only a standby tenant in strong sync mode has the same protection mode and level as the primary tenant; for other standby tenants in asynchronous sync mode, the protection mode and level remain at MAXIMUM PERFORMANCE.

You can query the DBA_OB_TENANTS view for a tenant's protection mode. Additionally, the tenant-level SCNs (fields such as SYNC_SCN, REPLAYABLE_SCN, and READABLE_SCN) in the DBA_OB_TENANTS view provide key metrics for tenant data synchronization and availability status, serving as an important basis for operations personnel to monitor primary-standby synchronization progress and diagnose synchronization issues.

You can check the protection mode and tenant-level positions (SCNs) on 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, tenant_name, tenant_role, protection_mode, protection_level,
        sync_scn, replayable_scn, readable_scn
        FROM oceanbase.DBA_OB_TENANTS
        WHERE tenant_type = 'USER';
  

• On the primary tenant, execute the following statement:

  MySQL-compatible mode:

  obclient(root@mysql001)[(none)]> SELECT tenant_role, protection_mode, protection_level,
        sync_scn, replayable_scn, readable_scn
        FROM oceanbase.DBA_OB_TENANTS;
  

  Oracle-compatible mode:

  obclient(sys@oracle001)[SYS]> SELECT tenant_role, protection_mode, protection_level,
        sync_scn, replayable_scn, readable_scn
        FROM SYS.DBA_OB_TENANTS;
  

Where:

• protection_mode: The protection mode of the current tenant. Valid values include MAXIMUM PERFORMANCE (maximum performance), MAXIMUM PROTECTION (maximum protection), and MAXIMUM AVAILABILITY (maximum availability).

• protection_level: The protection level of the current tenant. Valid values include MAXIMUM PERFORMANCE (maximum performance), MAXIMUM PROTECTION (maximum protection), MAXIMUM AVAILABILITY (maximum availability), and RESYNCHRONIZATION (log catching up state).

• sync_scn: The global synchronization SCN. It is the minimum sync_scn among all log streams under the tenant, which is also the minimum end_scn (the timestamp of the last log that achieved a majority in the log stream) among all leader replicas. All logs before this SCN have been synchronized to the standby tenant.

• replayable_scn: The global replayable SCN. In a physical standby database scenario, all logs before this SCN can be replayed on the standby tenant.

• readable_scn: The global readable SCN. It is the minimum readable_scn among all log streams under the tenant, which is also the minimum replayable SCN among the majority of log streams. All data before this SCN can be read on the standby tenant.

Step 2: Check the synchronization mode of each log stream

The synchronization mode of a log stream determines how logs are synchronized between the primary and standby tenants. In strong sync mode, the primary tenant pushes logs to the standby tenant. In asynchronous sync mode, the standby tenant pulls logs from the primary tenant.

On the primary tenant, you can check the synchronization mode of each log stream using the GV$OB_LOG_STAT view.

• 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, role, sync_mode
         FROM oceanbase.GV$OB_LOG_STAT
         WHERE tenant_id = xxx AND role = 'LEADER';
  

• In the primary tenant, execute the following statements.

  MySQL-compatible mode:

  obclient(root@mysql001)[(none)]> SELECT ls_id, role, sync_mode
         FROM oceanbase.GV$OB_LOG_STAT
         WHERE role = 'LEADER';
  

  Oracle-compatible mode:

  obclient(sys@oracle001)[SYS]> SELECT ls_id, role, sync_mode
         FROM SYS.GV$OB_LOG_STAT
         WHERE role = 'LEADER';
  

The values of sync_mode are as follows:

• SYNC: strong sync mode.
• ASYNC: async sync mode.
• PRE_ASYNC: transitioning from strong sync mode to async sync mode.

Step 3: Check the log stream transmission status (only applicable in strong sync mode)

When the primary tenant is in strong sync mode (i.e., sync_mode = SYNC in the GV$OB_LOG_STAT view), check the transmission status of each log stream from the primary tenant to the standby tenants using the GV$OB_LS_LOG_TRANSPORT_STAT view on 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, ls_id,
       end_scn, last_sent_scn, standby_end_scn,
       standby_addr
  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 statements.

  MySQL-compatible mode:

  obclient(root@mysql001)[(none)]> SELECT ls_id,
       end_scn, last_sent_scn, standby_end_scn,
       standby_addr
  FROM oceanbase.GV$OB_LS_LOG_TRANSPORT_STAT
  WHERE role = 'LEADER' AND enabled = 1;
  

  Oracle-compatible mode:

  obclient(sys@oracle001)[SYS]> SELECT ls_id,
       end_scn, last_sent_scn, standby_end_scn,
       standby_addr
  FROM SYS.GV$OB_LS_LOG_TRANSPORT_STAT
  WHERE role = 'LEADER' AND enabled = 1;
  

Where:

• end_scn: the current maximum SCN of the primary tenant's log stream.

• last_sent_scn: the SCN of the last log sent by the primary tenant to the standby tenant.

• standby_end_scn: the maximum SCN of logs that have been received and acknowledged by the standby tenant.

• standby_addr: the address information of the log stream leader on the standby tenant.

Step 4: Check the log reception status

During primary-standby synchronization, you can query the GV$OB_LS_LOG_RESTORE_STATUS view on the standby tenant to check whether log reception is normal.

• 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;
  

• On the standby tenant, execute the following statement.

  MySQL-compatible mode:

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

  Oracle-compatible mode:

  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_lsn: The maximum continuous majority LSN or the maximum consumable LSN of the standby database. It indicates the physical location of the log that has been continuously received and confirmed in this log stream on the standby tenant.

• sync_scn: The maximum continuous majority SCN or the maximum consumable SCN of the standby database. Corresponding to sync_lsn, it indicates the logical time point of the log that has been continuously received and confirmed in this log stream on the standby tenant.

• sync_status: The recovery status of the log stream.

• err_code: Error code information.

Step 5: Check the log replay status

During primary-standby synchronization, you can query the GV$OB_LS_LOG_REPLAY_STAT view on the standby tenant to check whether log replay is normal.

• 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,
       pending_cnt
  FROM oceanbase.GV$OB_LS_LOG_REPLAY_STAT
  WHERE tenant_id = xxx AND role = 'FOLLOWER';
  

• On the standby tenant, execute the following statement.

  MySQL-compatible mode:

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

  Oracle-compatible mode:

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

Where:

• end_lsn: The LSN persisted by Palf. All logs before this LSN have completed replay processing.

• unsubmitted_lsn: The LSN of the next log to be submitted to the replay queue, which marks the starting position of the pending replay queue.

• submit_lag: The value of end_lsn - unsubmitted_lsn, representing the length of the log segment pending submission to the replay queue. If this difference remains unchanged for a long time, it may indicate that replay is stuck.

• pending_cnt: The number of tasks waiting for replay, reflecting the backlog of replay processing.