SET LOG_RESTORE_SOURCE

Purpose

You can use this 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 or switch from one log restore source to another for a standby tenant.

OceanBase Database supports Physical Standby Database solutions based on log archiving and the network, which differ in the deployment solution. For the log archiving-based Physical Standby Database solution, logs of standby tenants are sourced from archive logs of the primary tenant or other standby tenants. For the network-based Physical Standby Database solution, standby tenants directly connect to the primary tenant or other standby tenants over the network to read logs.

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

For the network-based Physical Standby Database solution, if the archive mode is enabled for the primary tenant or source standby tenant, you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to switch the log restore source for a standby tenant to archive logs of the primary tenant or source standby tenant.

For the log archiving-based Physical Standby Database solution, if a 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 switch the log restore source for the standby tenant to logs read through network connection.

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 sys tenant (root@sys) or the administrator user of a user tenant can execute this statement.

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

Syntax

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

Parameters

Examples

The following example changes the log restore source of a standby tenant to archive logs of the primary tenant in the network-based Physical Standby Database solution.

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

   • In the sys tenant of the cluster to which the primary tenant belongs, 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 mode

     Oracle 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
   

   Here, the value of path in the NAME column indicates the log archive path of the tenant.

2. Change the log restore source.

   • In the sys tenant of the cluster to which the standby tenant standby_tenant1 belongs, change the log restore source of the standby tenant.

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

   • In the standby tenant standby_tenant1, change the log restore source of the current tenant.

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

The following example changes the log restore source of the standby tenant standby_tenant2 to logs read from the primary tenant through network connection in the log archiving-based Physical Standby Database solution.

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

   • In the sys tenant of the cluster to which the primary tenant belongs, 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 mode

     Oracle 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 sys tenant of the cluster to which a standby tenant belongs, change the log restore source of the 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=******' TENANT = standby_tenant2;
     

   • In a standby tenant, change the log restore source of the current tenant.

     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

• Overview

• Specify a log restore source