This topic introduces how the standby database delay feature is used in daily business operations through a typical case.
Scenario description
An enterprise has deployed a network-based physical standby database (one primary and multiple standbys), and configured a delay of 1 hour (3600 seconds) for one of the standbys. During routine database maintenance, a DBA accidentally executed the DELETE FROM important_table statement on the primary database, clearing data in an important table. The data must now be recovered from the delayed standby database.
Procedure
Notice
When performing the following operations, you must first set the log restore endpoint for the standby database (Step 2) and then cancel the standby database delay (Step 3). Otherwise, after you cancel the delay, the standby database will catch up at full speed and may pass the accidental operation point. When restoring the configuration, you must first restore the standby database delay configuration (Step 6) and then set the log restore endpoint back to continuous synchronization (Step 7) to prevent logs from instantly catching up.
Determine the point in time of the accidental operation.
For example, this document uses
2026-06-01 10:01:00as the accidental operation time.Set the log restore endpoint of the delayed standby database to a point in time before the accidental operation. This prevents the standby database from catching up past the accidental operation point after the delay is canceled.
Based on the accidental operation time
2026-06-01 10:01:00, you can set the log restore endpoint of the delayed standby database to2026-06-01 10:00:00.The system tenant (
systenant) of the cluster where the standby tenant (standby database) resides sets the log restore endpoint for that standby tenant.obclient(root@sys)[(none)]> ALTER SYSTEM RECOVER STANDBY TENANT = standby_tenant UNTIL TIME = '2026-06-01 10:00:00';The standby tenant (standby database) sets the log restore endpoint for itself.
obclient> ALTER SYSTEM RECOVER STANDBY UNTIL TIME = '2026-06-01 10:00:00';
Cancel the standby database delay configuration so the standby database immediately starts catching up logs to the specified log restore endpoint.
This document uses the current network standby database's log restore source information as
SERVICE=11.xx.xx.22:17855;11.xx.xx.23:17857;11.xx.xx.24:17859 USER=rep_user@mysql PASSWORD=******as an example. The log restore source information can be obtained by querying theCDB_OB_LOG_RESTORE_SOURCEview (system tenant) /DBA_OB_LOG_RESTORE_SOURCEview (user tenant).The system tenant (
systenant) of the cluster where the standby tenant (standby database) resides sets the delay for that standby tenant to 0 seconds.obclient(root@sys)[(none)]> ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=11.xx.xx.22:17855;11.xx.xx.23:17857;11.xx.xx.24:17859 USER=rep_user@mysql PASSWORD=****** DELAY=0' TENANT = standby_tenant;The standby tenant (standby database) sets its own delay to 0 seconds.
obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=11.xx.xx.22:17855;11.xx.xx.23:17857;11.xx.xx.24:17859 USER=rep_user@mysql PASSWORD=****** DELAY=0';
Wait for the standby database to synchronize and replay logs to the target point in time.
During the wait, you can use the following statements to check the synchronization status until the value of
SCN_TO_TIMESTAMP(readable_scn)shows the specified log restore endpoint2026-06-01 10:00:00.The system tenant (
systenant) of the cluster where the standby tenant (standby database) resides checks the synchronization status of that standby tenant.obclient(root@sys)[(none)]> SELECT tenant_name, readable_scn, SCN_TO_TIMESTAMP(readable_scn) FROM oceanbase.DBA_OB_TENANTS WHERE tenant_name = 'standby_tenant';The standby tenant (standby database) checks its own synchronization status.
obclient> SELECT tenant_name, readable_scn, SCN_TO_TIMESTAMP(readable_scn) FROM oceanbase.DBA_OB_TENANTS;obclient> SELECT tenant_name, readable_scn, SCN_TO_TIMESTAMP(readable_scn) FROM SYS.DBA_OB_TENANTS;
Export data from the standby database and then import it into the primary database for repair.
Restore the standby database delay configuration.
Set the standby database delay back to 1 hour (3600 seconds).
The system tenant (
systenant) of the cluster where the standby tenant (standby database) resides sets the delay for that standby tenant to 3600 seconds.obclient(root@sys)[(none)]> ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=11.xx.xx.22:17855;11.xx.xx.23:17857;11.xx.xx.24:17859 USER=rep_user@mysql PASSWORD=****** DELAY=3600' TENANT = standby_tenant;The standby tenant (standby database) sets its own delay to 3600 seconds.
obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE = 'SERVICE=11.xx.xx.22:17855;11.xx.xx.23:17857;11.xx.xx.24:17859 USER=rep_user@mysql PASSWORD=****** DELAY=3600';
Restore continuous synchronization for the standby database.
Set the log restore endpoint of the standby database back to continuous synchronization.
The system tenant (
systenant) of the cluster where the standby tenant (standby database) resides sets the log restore endpoint for that standby tenant to continuous synchronization.obclient(root@sys)[(none)]> ALTER SYSTEM RECOVER STANDBY TENANT = standby_tenant UNTIL UNLIMITED;The standby tenant (standby database) sets its own log restore endpoint to continuous synchronization.
obclient> ALTER SYSTEM RECOVER STANDBY UNTIL UNLIMITED;