Configure standby database delay

The standby database delay feature in OceanBase Database allows a standby database to intentionally delay synchronizing logs from the upstream for a period, maintaining a data replica at a historical point in time.

This feature is commonly used in the following scenarios:

• Prevent human error: If a table is accidentally deleted or data is mistakenly updated on the primary database, you can quickly restore from the unaffected historical data replica on the delayed standby database.

• Data protection within a time window: During the configured delay period, if an issue is detected on the primary database, you can export the correct data from the delayed standby database.

Limitations and considerations

When configuring standby database delay, note the following considerations.

Configuration recommendations

• Standby database delay: We recommend setting it to 30 minutes to 2 hours to balance the time needed to detect misoperations and RPO.

• Archive retention time: Ensure that the log archive retention time on the primary database is longer than the standby database delay to prevent logs from being purged.

• Multi-standby scenario: We recommend deploying one real-time standby database and one delayed standby database to balance HA (High Availability) and data protection.

Set the delay time for a standby database

For a standby tenant in MAXIMUM PERFORMANCE mode, you can set the standby database delay based on your business needs. The procedure is as follows:

1. Log in to the standby tenant or the system tenant (sys tenant) of the cluster where the standby tenant resides as the administrator user.

   Note

   The administrator user in MySQL-compatible mode is the root user, and in Oracle-compatible mode, it is the SYS user.

   The connection example below is for reference only; please use the actual environment parameters when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Execute the following command to view the protection mode of the current standby tenant. Only standby tenants in MAXIMUM PERFORMANCE mode can have delay configured.

   • View the protection mode of the standby tenant from the system tenant (sys tenant) of its cluster.

     obclient(root@sys)[oceanbase]> SELECT TENANT_NAME, PROTECTION_MODE, PROTECTION_LEVEL FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'standby_tenant';
     

   • View the protection mode of the standby tenant itself.

     MySQL-compatible mode

     Oracle-compatible mode

     In MySQL-compatible mode, the query statement is as follows:

     obclient(root@standby_tenant)[oceanbase]> SELECT TENANT_NAME, PROTECTION_MODE, PROTECTION_LEVEL FROM oceanbase.DBA_OB_TENANTS;
     

     In Oracle-compatible mode, the query statement is as follows:

     obclient(sys@standby_tenant)[SYS]> SELECT TENANT_NAME, PROTECTION_MODE, PROTECTION_LEVEL FROM SYS.DBA_OB_TENANTS;
     

   An example of the query result is as follows:

   +----------------+---------------------+---------------------+
   | TENANT_NAME    | PROTECTION_MODE     | PROTECTION_LEVEL    |
   +----------------+---------------------+---------------------+
   | standby_tenant | MAXIMUM PERFORMANCE | MAXIMUM PERFORMANCE |
   +----------------+---------------------+---------------------+
   1 row in set
   

   Based on the query result, confirm that the standby tenant's protection mode is MAXIMUM PERFORMANCE.

3. Execute the following command to obtain the log restore source configuration for the current standby tenant.

   • View the log restore source information for the standby tenant from the system tenant (sys tenant) of its cluster.

     obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_LOG_RESTORE_SOURCE\G
     

   • View the log restore source information for the standby tenant itself.

     MySQL-compatible mode

     Oracle-compatible mode

     In MySQL-compatible mode, the query statement is as follows:

     obclient(root@standby_tenant)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_LOG_RESTORE_SOURCE\G
     

     In Oracle-compatible mode, the query statement is as follows:

     obclient(sys@standby_tenant)[SYS]> SELECT * FROM SYS.DBA_OB_LOG_RESTORE_SOURCE\G
     

   An example of the query result is as follows:

   *************************** 1. row ***************************
              TENANT_ID: 1002
                     ID: 1
                   TYPE: SERVICE
                  VALUE: IP_LIST=6.xx.xxx.91:2881+2882,USER=rep_user@mysql001,PASSWORD=4E9*************************************,TENANT_ID=1002,CLUSTER_ID=10001,COMPATIBILITY_MODE=MYSQL,IS_ENCRYPTED=true
     RECOVERY_UNTIL_SCN: 4611686018427387903
         RECOVERY_DELAY: 0
   1 row in set
   

4. Update the log restore source configuration to specify the delay time for the standby tenant (standby database).

   The statement is as follows:

   ALTER SYSTEM SET LOG_RESTORE_SOURCE ='SERVICE=$ip_list USER=$user_name@$tenant_name PASSWORD=$password DELAY=$delay_time' [TENANT = standby_tenant_name];
   

   Some parameters are described as follows:

   • $ip_list, $user_name, $tenant_name, $password: log restore source configuration. Enter the information obtained in Step 3.

   • $delay_time: specifies the delay before the standby tenant (standby database) pulls logs. If a value is specified without a unit, the default unit is seconds. You can also specify a value with a unit: us (microseconds), ms (milliseconds), m (minutes), h (hour), or d (days). If this parameter is not specified, the delay defaults to 0 seconds, which means the standby database does not delay.

   Based on the log restore source configuration obtained in the previous step, set the standby database's delay to 30 minutes, which is 1800 seconds. Example:

   • The system tenant (sys tenant) of the cluster where the standby tenant resides sets the delay for that standby tenant.

     obclient(root@sys)[oceanbase]> ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=6.xx.xx.91:2881 USER=rep_user@mysql001 PASSWORD=****** DELAY=1800' TENANT = standby_tenant;
     

   • The standby tenant sets its own delay.

     obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=6.xx.xx.91:2881 USER=rep_user@mysql001 PASSWORD=****** DELAY=1800';
     

   After you set the delay for the standby database (standby tenant), compared with the state before the change:

   • If you increase the delay time, the standby database stops log synchronization at the current position and resumes synchronization after the elapsed time reaches the configured delay.

   • If you decrease the delay time, the standby database immediately starts catching up logs.

   • If you clear the delay time, the standby database catches up logs at full speed from the current position as quickly as possible.

5. After the configuration succeeds, view the delay configuration of the standby database. The result is displayed in microseconds.

   • The system tenant (sys tenant) of the cluster where the standby tenant resides checks the delay setting for that standby tenant.

     obclient(root@sys)[oceanbase]> SELECT TENANT_ID, recovery_delay FROM oceanbase.CDB_OB_LOG_RESTORE_SOURCE;
     

     An example of the query result is as follows:

     +-----------+----------------+
     | TENANT_ID | recovery_delay |
     +-----------+----------------+
     |      1002 |     1800000000 |
     +-----------+----------------+
     1 row in set
     

   • The standby tenant checks its own delay setting.

     MySQL-compatible mode

     Oracle-compatible mode

     In MySQL-compatible mode, the query statement is as follows:

     obclient(root@standby_tenant)[oceanbase]> SELECT recovery_delay FROM oceanbase.DBA_OB_LOG_RESTORE_SOURCE;
     

     In Oracle-compatible mode, the query statement is as follows:

     obclient(sys@standby_tenant)[SYS]> SELECT recovery_delay FROM SYS.DBA_OB_LOG_RESTORE_SOURCE;
     

     The following is an example of the query result:

     +----------------+
     | recovery_delay |
     +----------------+
     |     1800000000 |
     +----------------+
     1 row in set
     

View standby database delay status

After the standby database delay is configured, you can view the delay status through views.

• View the delay status of the standby tenant from the system tenant (sys tenant) of the cluster where the standby tenant resides

  obclient(root@sys)[oceanbase]> SELECT tenant_name,
        sync_scn,
        replayable_scn,
        readable_scn,
        TIMESTAMPDIFF(SECOND, SCN_TO_TIMESTAMP(sync_scn), NOW()) AS actual_delay_seconds
  FROM oceanbase.DBA_OB_TENANTS
  WHERE tenant_role = 'STANDBY';
  

  In the command, actual_delay_seconds is the difference between sync_scn and the current time, which is the actual delay in seconds.

• View the delay status of the standby tenant itself

  MySQL-compatible mode

  Oracle-compatible mode

  In MySQL-compatible mode, the query statement is as follows:

  obclient(root@standby_tenant)[oceanbase]> SELECT tenant_name,
        sync_scn,
        replayable_scn,
        readable_scn,
        TIMESTAMPDIFF(SECOND, SCN_TO_TIMESTAMP(sync_scn), NOW()) AS actual_delay_seconds
  FROM oceanbase.DBA_OB_TENANTS;
  

  In the command, actual_delay_seconds is the difference between sync_scn and the current time, which is the actual delay in seconds.

  In Oracle-compatible mode, the query statement is as follows:

  obclient(sys@standby_tenant)[SYS]> SELECT tenant_name,
        sync_scn,
        replayable_scn,
        readable_scn,
        TIMESTAMPDIFF(SECOND, SCN_TO_TIMESTAMP(sync_scn), NOW()) AS actual_delay_seconds
  FROM SYS.DBA_OB_TENANTS;
  

  In the command, actual_delay_seconds is the difference between sync_scn and the current time, which is the actual delay in seconds.

  The following is an example of the query result:

  +----------------+---------------------+---------------------+---------------------+----------------------+
  | tenant_name    | sync_scn            | replayable_scn      | readable_scn        | actual_delay_seconds |
  +----------------+---------------------+---------------------+---------------------+----------------------+
  | standby_tenant | 1780553786243403000 | 1780553786243403000 | 1780553786243403000 |                  205 |
  +----------------+---------------------+---------------------+---------------------+----------------------+
  1 row in set
  

References

For delayed standby application examples, see Delayed standby application cases.