SET LOG_RESTORE_SOURCE|V4.3.3|OceanBase Database| docs|Distributed Database

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 ];

Note

When you specify the SERVICE=$host_ip_list, USER=$user_name@$tenant_name, and PASSWORD=$password parameters, separate them with spaces. The system distinguishes the parameters based on spaces during parsing. Therefore, there cannot be spaces before or after the equal sign (=) of each parameter value.

Parameters

Parameter	Description
host_ip_list	The system log stream location of the primary tenant or source standby tenant. This parameter is required when the log restore source is online logs read through network connection. It can be the IP address and SQL port number (2881 by default) of OBServer nodes where the replicas of the primary tenant or source standby tenant reside. Multiple IP addresses must be separated with semicolons (;). For the network-based Physical Standby Database solution, the system log stream location of the primary tenant or source standby tenant is the access point for the standby tenant. You can query this location from the `CDB_OB_ACCESS_POINT` view in the `sys` tenant or the `DBA_OB_ACCESS_POINT` view in a user tenant. After you set the log restore source, the standby tenant detects changes in the system log stream location of the source tenant within seconds and automatically updates the location. You do not need to manually update `LOG_RESTORE_SOURCE` during load balancing or disaster recovery in the primary tenant.
user_name	The name of the dedicated user for accessing views that is created in the primary tenant. The user must have read-only privileges on the following views in the `sys` tenant: `GV$OB_LOG_STAT`: records information such as the list of OBServer nodes in a tenant, log sequence number (LSN) range of the log stream for the replica service, and role (leader/follower). `GV$OB_UNITS`: records information about all units of a tenant, including the replica status, zone, region, and other information for filtering, obtaining, and maintaining OBServer node connections of a tenant. `GV$OB_PARAMETERS`: records the metadata required by services, for example, `cluster_id`. `DBA_OB_ACCESS_POINT`: records the access point information. In scenarios such as migration, replication, and disaster recovery of the primary tenant, if the access point changes, the standby tenant can automatically detect the change. `DBA_OB_TENANTS`: records the compatibility mode of the primary tenant. `DBA_OB_LS`: records the log stream list and log stream status of the primary tenant. `DBA_OB_LS_HISTORY`: records the log stream creation and deletion history of the primary tenant. If you want to use the `CREATE STANDBY TENANT` statement to create a standby tenant, make sure that no log streams have been deleted.
tenant_name	The name of the primary tenant to which the standby tenant connects over the network.
password	The password of the dedicated user for accessing views.
archive_path	The log archive path of the log restore source. This parameter is required when the log restore source is archive logs. You can query the log archive path of the primary tenant or source standby tenant from the `CDB_OB_ARCHIVE_DEST` view in the `sys` tenant or from the `DBA_OB_ARCHIVE_DEST` view in a user tenant.
standby_tenant_name	The name of the standby tenant for which you want to set the log restore source. When you set the log restore source for a standby tenant in the `sys` tenant of the cluster to which the standby tenant belongs, you must specify `TENANT = standby_tenant_name`.

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.

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.

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.

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.

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

OceanBase

Customer Stories

Documentation

SET LOG_RESTORE_SOURCE