Description
The ALTER SYSTEM SET LOG_RESTORE_SOURCE statement is used to set the log restore source. The log source of an OceanBase AI Database can be dynamically modified. You can use this statement to set a log restore source for a standby tenant whose source is not specified, or to switch the log source of a standby tenant from one type to another.
Physical standby databases are classified into log-archived physical standby databases and network-based physical standby databases based on the deployment scheme. In a log-archived physical standby database, the logs for the standby tenant are sourced from the log archives of the primary tenant or other standby tenants. In a network-based physical standby database, the standby tenant directly reads logs from the primary tenant or other standby tenants over the network.
If you create a standby tenant by using the BACKUP DATABASE PLUS ARCHIVELOG feature, you must use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to set a restore source for the standby tenant after creation. Otherwise, the standby tenant cannot enable continuous log synchronization.
For network-based physical standby database scenarios, if the primary tenant or the source standby tenant has archive mode enabled, you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to change the log restore source for the standby tenant's logs to the archive logs of the primary tenant or the source standby tenant.
For physical standby database scenarios based on log archiving, if the network connection between the current standby tenant and the primary tenant or the source standby tenant is available, you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to change the log restore source for the standby tenant to directly read logs over the network.
Limitations and considerations
In a cascading standby scenario, you can set the log restore source for the current standby tenant to the primary tenant or another standby tenant.
For example, assume that the current environment already has a primary tenant A and a standby tenant B. After creating a new standby tenant C, you can set the log restore source for standby tenant C to either primary tenant A or standby tenant B.
If you need to change the log restore source for a standby tenant from archived logs to direct log reading over a network connection, you must create a dedicated user on the primary tenant to access the view for the current standby tenant before setting the log restore source.
When changing the log source of a standby tenant from one log restore source to another, you must ensure that the log restore source remains consistent with the source tenant specified during the creation of the standby tenant. That is, either use the source tenant's log archive or point directly to the source tenant.
In a single-node deployment, the log stream location for the primary tenant system is the address of the standalone OBServer node.
Privilege requirements
This statement must be executed by the root user of the sys tenant (root@sys) or the administrator user of a user tenant (root user).
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 setting sub-parameters such as SERVICE=$host_ip_list, USER=$user_name@$tenant_name, and PASSWORD=$password, separate them with spaces. The system also recognizes these spaces during parsing. Therefore, do not include spaces before or after the equal sign (=) that assigns a value to each sub-parameter.
Parameters
Parameters |
Description |
|---|---|
| host_ip_list | When the log restore source is online logs connected through the network, this parameter specifies the location information of the primary tenant or source standby tenant's system log stream, that is, the IP address and SQL port number (default: 2881) of the OBServer node where the replica of the primary tenant or source standby tenant resides. Multiple IP addresses are separated by semicolons (;). In a network-based physical standby database, the location information of the primary tenant or source standby tenant's system log stream serves as the entry point for connecting to the standby tenant. You can query the view under the sys tenant to obtain this information. CDB_OB_ACCESS_POINT or query views in a user tenant. DBA_OB_ACCESS_POINT to obtain the location information of the primary tenant or source standby tenant's system log stream. After the source setting is successfully restored, the standby tenant detects changes in the source tenant's system log stream location within seconds and updates it automatically. Furthermore, manual updates are not required when load balancing or disaster recovery operations are performed on the primary tenant. LOG_RESTORE_SOURCE. |
| user_name | The name of the user created in the primary tenant to access views. This user must have read-only privileges on the following views within the system tenant:
|
| tenant_name | The tenant name of the primary tenant to which the standby tenant is connected through the network. |
| password | The password of the dedicated user for accessing the view. |
| archive_path | When the log restore source is archived logs, this parameter specifies the archive path of the restore source. You can query the view under the sys tenant to obtain the information. CDB_OB_ARCHIVE_DEST or query views in a user tenant. DBA_OB_ARCHIVE_DEST To obtain the archive path information of the primary tenant or the source standby tenant. |
| standby_tenant_name | Specifies the name of the standby tenant for which you want to set a log restore source. When setting the log restore source for a standby tenant using the system tenant of its cluster, you must specify TENANT = standby_tenant_name. |
Examples
In a network-based physical standby database scenario, the following example shows how to restore the source log of the standby tenant to the archived logs of the primary tenant:
Obtain the primary tenant's archive path.
The
systenant of the cluster where the primary tenant resides retrieves the primary tenant's archive path.The statement is as follows:
obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_ARCHIVE_DEST WHERE tenant_id= 1002;The primary tenant obtains its own tenant's archive path.
The statement is as follows:
obclient [(none)]> SELECT * FROM oceanbase.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 setIn the example, the value of
pathin theNAMEcolumn is the log archive path for the current tenant.Modify the log restore source.
The
systenant of the cluster where standby tenantstandby_tenant1resides modifies the log restore source for the standby tenant.obclient [(none)]> ALTER SYSTEM SET LOG_RESTORE_SOURCE='LOCATION=file:///data/1/example_archive' TENANT = standby_tenant1;Standby tenant
standby_tenant1modifies the recovery source of the current tenant.obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE='LOCATION=file:///data/1/example_archive';
In a physical standby database scenario based on log archiving, the following example shows how to modify the log source for restoring the standby tenant standby_tenant2 to directly obtain logs over a network connection:
Obtain the location information of the primary tenant
primary_tenantsystem log stream.The
systenant in the cluster where the primary tenant resides retrieves the location information of the primary tenant's system log stream.The statement is as follows:
obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_ACCESS_POINT WHERE tenant_name='primary_tenant';The primary tenant obtains the location information of its own system log stream.
The statement is as follows:
obclient [(none)]> SELECT * FROM oceanbase.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 returned result depends on the number of replicas of the primary tenant. In this example, the queried primary tenant has three replicas. If the primary tenant or the standby tenant at the source is a single-replica tenant, only one row of results is returned.
Modify the log restore source.
The
systenant of the cluster where the standby tenant resides modifies the log restore source for 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;The standby tenant modifies the log restore source for itself.
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=******';