View the log synchronization progress

After a standby tenant enters the continuous synchronization mode, you can query the log synchronization progress of the standby tenant in real time to make timely adjustment.

A primary tenant has at least one system log stream and one common log stream. A standby tenant has the same number of log streams as a primary tenant. The log synchronization progress of the standby tenant is the overall synchronization progress of multiple log streams.

You can compare the synchronization time of different log streams in the same tenant. The overall synchronization progress of the standby tenant is the synchronization progress of the slowest log stream among all log streams.

Query the synchronization progress of standby tenants from the sys tenant

You can log on to the sys tenant to view the synchronization progress of all tenants or a specified tenant.

1. Log on as the administrator to the sys tenant of the cluster where the standby tenant resides.

2. Execute the following statement to view the synchronization progress of the specified standby tenant.

   SELECT TENANT_NAME, TENANT_ID, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) 
   FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'standby_tenant';
   

   The query result is as follows:

   +----------------+-----------+-------------+----------------------------+
   | TENANT_NAME    | TENANT_ID | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
   +----------------+-----------+-------------+----------------------------+
   | standby_tenant |      1004 | STANDBY     | 2023-04-14 16:38:53.938774 |
   +----------------+-----------+-------------+----------------------------+
   1 row in set
   

   The SCN_TO_TIMESTAMP(SYNC_SCN) field in the query result displays the synchronization progress of the specified standby tenant. You can compare the displayed time with the current time. A large difference indicates that the synchronization progress of the standby tenant lags behind that of the primary tenant.

   For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

3. (Optional) If the synchronization progress of the standby tenant is slow, query the slowest log stream.

   SELECT LS_ID, SCN_TO_TIMESTAMP(END_SCN) FROM oceanbase.GV$OB_LOG_STAT WHERE TENANT_ID = 1004 AND ROLE = 'LEADER';
   

   The query result is as follows:

   +-------+----------------------------+
   | LS_ID | SCN_TO_TIMESTAMP(END_SCN)  |
   +-------+----------------------------+
   |     1 | 2023-04-14 16:42:34.249988 |
   |  1001 | 2023-04-14 16:42:34.295123 |
   +-------+----------------------------+
   

   The query result shows that the synchronization progress of log stream 1 is slow.

Query the synchronization progress of a standby tenant from the standby tenant

You can log on to a standby tenant and view its synchronization progress.

1. Log on to the standby tenant as the administrator.

2. Execute the following statement to view the synchronization progress of the standby tenant.

   • MySQL mode

     SELECT TENANT_NAME, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) FROM oceanbase.DBA_OB_TENANTS;
     

     The query result is as follows:

     +----------------+-------------+----------------------------+
     | TENANT_NAME    | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
     +----------------+-------------+----------------------------+
     | standby_tenant | STANDBY     | 2023-04-14 16:38:53.938774 |
     +----------------+-------------+----------------------------+
     

   • Oracle mode

     SELECT TENANT_NAME, TENANT_ROLE, SCN_TO_TIMESTAMP(SYNC_SCN) FROM SYS.DBA_OB_TENANTS;
     

     The query result is as follows:

     +----------------+-------------+------------------------------+
     | TENANT_NAME    | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN)   |
     +----------------+-------------+------------------------------+
     | standby_tenant | STANDBY     | 14-APR-23 16:38:53.938774 PM |
     +----------------+-------------+------------------------------+
     

   The SCN_TO_TIMESTAMP(SYNC_SCN) field in the query result displays the synchronization progress of the specified standby tenant. You can compare the displayed time with the current time. A large difference indicates that the synchronization progress of the standby tenant lags behind that of the primary tenant.

   For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS (MySQL mode) and DBA_OB_TENANTS (Oracle mode).

3. (Optional) If the synchronization progress of the standby tenant is slow, query the slowest log stream.

   • MySQL mode

     SELECT LS_ID, SCN_TO_TIMESTAMP(END_SCN) FROM oceanbase.GV$OB_LOG_STAT WHERE ROLE = 'LEADER';
     

     The query result is as follows:

     +-------+----------------------------+
     | LS_ID | SCN_TO_TIMESTAMP(END_SCN)  |
     +-------+----------------------------+
     |     1 | 2023-04-14 16:42:34.249988 |
     |  1001 | 2023-04-14 16:42:34.295123 |
     +-------+----------------------------+
     

   • Oracle mode

     SELECT LS_ID, SCN_TO_TIMESTAMP(END_SCN) FROM SYS.GV$OB_LOG_STAT WHERE ROLE = 'LEADER';
     

     The query result is as follows:

     +-------+-----------------------------+
     | LS_ID | SCN_TO_TIMESTAMP(END_SCN)   |
     +-------+-----------------------------+
     |     1 | 14-APR-23 16:42:34.249988 PM|
     |  1001 | 14-APR-23 16:42:34.295123 PM|
     +-------+-----------------------------+
     

   The query result shows that the synchronization progress of log stream 1 is slow.

References

Optimize the log synchronization performance