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.

For a primary tenant, there are at least two log streams: one system log stream and one common log stream. A standby tenant has the same number of log streams as the primary tenant, also at least two. The log synchronization progress of the standby tenant needs to consider 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.

View the synchronization progress of standby tenants from the sys tenant

The sys tenant can view the synchronization progress of all tenants or a specified tenant.

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

2. Execute the following command 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) column in the query result displays the current synchronization progress of the standby tenant. You can compare this time with the actual time. If the difference is large, the standby tenant's synchronization progress lags behind that of the primary tenant.

   For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

3. (Optional) After confirming that the standby tenant's synchronization progress is slow, you can execute the following command to further 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 |
   +-------+----------------------------+
   

   Based on the query result, log stream 1 has the slowest synchronization progress.

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

A standby tenant can also view its own current synchronization progress.

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

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

   • MySQL-compatible 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-compatible 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) column in the query result displays the current synchronization progress of the standby tenant. You can compare this time with the actual time. If the difference is large, the standby tenant's synchronization progress lags behind that of the primary tenant.

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

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

   • MySQL-compatible 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-compatible 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|
     +-------+-----------------------------+
     

   Based on the query result, log stream 1 has the slowest synchronization progress.

References

Optimize the log synchronization performance