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:
|
| 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
systenant 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 modeOracle modeThe 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 setHere, the value of
pathin theNAMEcolumn indicates the log archive path of the tenant.Change the log restore source.
In the
systenant of the cluster to which the standby tenantstandby_tenant1belongs, 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
systenant 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 modeOracle modeThe 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 setThe 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
systenant 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=******';