SET LOG_RESTORE_SOURCE

Purpose

You can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to set the log restore source. OceanBase Database allows you to dynamically modify the log restore source. You can use this statement to set the log restore source for a standby tenant that does not yet have one, or switch a standby tenant from one log restore source to another.

Depending on the deployment model, physical standby databases are classified as log archiving-based physical standby databases and network-based physical standby databases. In a log archiving-based physical standby database, a standby tenant obtains logs from log archiving of the primary tenant or another standby tenant. In a network-based physical standby database, a standby tenant reads logs by connecting to the primary tenant or another standby tenant over the network.

If you created a standby tenant by using the BACKUP DATABASE PLUS ARCHIVELOG feature, you must use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to set the log restore source for the standby tenant after creation. Otherwise, the standby tenant cannot enable continuous log synchronization.

For a network-based physical standby database, if the primary tenant or source standby tenant has archiving mode enabled, you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to change the standby tenant's log restore source to the archive logs of the primary tenant or source standby tenant.

For a log archiving-based physical standby database, if the standby tenant can connect to the primary tenant or source standby tenant over the network, you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to change the standby tenant's log restore source to logs read directly over the network.

Limitations and considerations

• For cascaded standby databases, you can set the log restore source for the current standby tenant to the primary tenant or another standby tenant.

  For example, assume that a cluster contains the primary tenant A and a standby tenant B. After you create a standby tenant C, you can set the log restore source for the standby tenant C to the primary tenant A or the standby tenant B.

• If you want to switch the log restore source for a standby tenant from archive logs to logs read through network connection, you must first create a dedicated user for accessing views in the primary tenant for the standby tenant. For more information, see Step 1: Create a dedicated user for accessing views in Create an empty standby tenant.

• When you switch from one log restore source to another for a standby tenant, make sure that the original and new log restore sources are consistent with the source tenant specified when you created the standby tenant. In other words, the log restore source must be archive logs of the source tenant or directly point to the source tenant.

• If only one OBServer node is deployed, the system log stream location of the primary tenant is the address of the OBServer node.

Required privileges

Only the root user of the system tenant (sys tenant) (root@sys) or the administrator user of a user tenant can execute this statement.

• The default administrator user in MySQL-compatible mode is root.
• The default administrator user in Oracle-compatible mode is SYS.

Syntax

ALTER SYSTEM SET LOG_RESTORE_SOURCE = {'SERVICE=$host_ip_list USER=$user_name@$tenant_name PASSWORD=$password[ DELAY=$delay_time]' | 'LOCATION=archive_path[ DELAY=$delay_time]'} [ TENANT = standby_tenant_name ];

Parameters

Examples

Set the log restore source

The following example changes the log restore source of a standby tenant to the primary tenant's archive logs in a network-based physical standby database scenario.

1. Query the log archive path of the primary tenant.

   • In the system tenant (sys tenant) of the cluster where the primary tenant resides, query the log archive path of the primary tenant.

     The statement is as follows:

     obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_ARCHIVE_DEST WHERE tenant_id= 1002;
     

   • In the primary tenant, query the log archive path of the current tenant.

     MySQL-compatible mode

     Oracle-compatible mode

     The statement is as follows:

     obclient [(none)]> SELECT * FROM oceanbase.DBA_OB_ARCHIVE_DEST;
     

     The statement is as follows:

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_ARCHIVE_DEST;
     

   The query result is as follows:

   +---------+-----------------------+---------------------------------+
   | DEST_NO | NAME                  | VALUE                           |
   +---------+-----------------------+---------------------------------+
   |       0 | binding               | OPTIONAL                        |
   |       0 | dest_id               | 1002                            |
   |       0 | path                  | file:///data/1/example_archive  |
   |       0 | piece_switch_interval | 1d                              |
   |       0 | state                 | ENBALE                          |
   +---------+-----------------------+---------------------------------+
   5 rows in set
   

   In this example, the value corresponding to path in the NAME column is the log archive path of the current tenant.

2. Change the log restore source.

   • In the system tenant (sys tenant) of the cluster where standby tenant standby_tenant1 resides, change the log restore source for that standby tenant.

     obclient [(none)]> ALTER SYSTEM SET LOG_RESTORE_SOURCE='LOCATION=file:///data/1/example_archive' TENANT = standby_tenant1;
     

   • Standby tenant standby_tenant1 changes its own log restore source.

     obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE='LOCATION=file:///data/1/example_archive';
     

The following example changes the log restore source of standby tenant standby_tenant2 to logs obtained directly over the network in a log archiving-based physical standby database scenario.

1. Query the system log stream location of the primary tenant primary_tenant.

   • In the system tenant (sys tenant) of the cluster where the primary tenant resides, query the system log stream location of the primary tenant.

     The statement is as follows:

     obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_ACCESS_POINT WHERE tenant_name='primary_tenant';
     

   • In the primary tenant, query the system log stream location of the current tenant.

     MySQL-compatible mode

     Oracle-compatible mode

     The statement is as follows:

     obclient [(none)]> SELECT * FROM oceanbase.DBA_OB_ACCESS_POINT;
     

     The statement is as follows:

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_ACCESS_POINT;
     

   The query result is as follows:

   +-----------+----------------+-------------+----------+
   | TENANT_ID | TENANT_NAME    | SVR_IP      | SQL_PORT |
   +-----------+----------------+-------------+----------+
   |      1004 | primary_tenant | 11.xx.xx.22 |    17855 |
   |      1004 | primary_tenant | 11.xx.xx.23 |    17857 |
   |      1004 | primary_tenant | 11.xx.xx.24 |    17859 |
   +-----------+----------------+-------------+----------+
   3 rows in set
   

   The return result depends on the number of replicas of the primary tenant. In this example, the primary tenant has three replicas. If the primary tenant or source standby tenant is a single-replica tenant, only one row is returned.

2. Change the log restore source.

   • In the system tenant (sys tenant) of the cluster where the standby tenant resides, change the log restore source for the standby tenant and set the delay to 3600 seconds.

     obclient [(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@primary_tenant PASSWORD=****** DELAY=3600' TENANT = standby_tenant2;
     

   • The standby tenant changes its own log restore source and sets the 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@primary_tenant PASSWORD=****** DELAY=3600';
     

Set standby database delay

This section covers modifying and canceling standby database delay.

• Modifying the delay means increasing or decreasing the standby database delay. The following example sets the standby database delay to 1800 seconds:

  • In the system tenant (sys tenant) of the cluster where the standby tenant (standby database) resides, change the delay for that standby tenant.

    obclient [(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@primary_tenant PASSWORD=****** DELAY=1800' TENANT = standby_tenant2;
    

  • The standby tenant (standby database) changes its own delay.

    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@primary_tenant PASSWORD=****** DELAY=1800';
    

• Canceling standby database delay means setting the delay to 0 seconds. The following example shows how:

  • In the system tenant (sys tenant) of the cluster where the standby tenant (standby database) resides, cancel the delay for that standby tenant.

    obclient [(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@primary_tenant PASSWORD=****** DELAY=0' TENANT = standby_tenant2;
    

    Alternatively, you can also cancel the standby tenant's delay by not specifying the DELAY parameter. The statement is as follows.

    obclient [(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@primary_tenant PASSWORD=******' TENANT = standby_tenant2;
    

  • The standby tenant (standby database) cancels its own delay.

    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@primary_tenant PASSWORD=****** DELAY=0';
    

    Alternatively, you can also cancel the standby tenant's delay by not specifying the DELAY parameter. The statement is as follows.

    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@primary_tenant PASSWORD=******';
    

References

• Log transmission service overview

• Set a log restore source