SET LOG_RESTORE_SOURCE

Purpose

The ALTER SYSTEM SET LOG_RESTORE_SOURCE statement is used to set the log restore source. OceanBase Database supports dynamic modification of log sources. You can use this statement to set the log restore source for a standby tenant that has not been configured with one, or to switch the log source of a standby tenant from one log restore source to another.

Physical standby databases are divided into two types based on the deployment plan: log-based physical standby databases and network-based physical standby databases. In log-based physical standby databases, the log source of a standby tenant is the log archive of the primary tenant or another standby tenant. In network-based physical standby databases, the standby tenant directly reads logs from the primary tenant or another standby tenant over the network.

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

In network-based physical standby databases, if the primary tenant or the source standby tenant has enabled the archive mode, you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to modify the log restore source of the standby tenant to the archive logs of the primary tenant or the source standby tenant.

In log-based physical standby databases, if the network is connected between the current standby tenant and the primary tenant or the source standby tenant, you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to modify the log restore source of the standby tenant to directly read logs over the network.

Limitations and considerations

• In a cascading standby database scenario, you can set the log restore source of the current standby tenant to the primary tenant or another standby tenant.

  For example, suppose you have a primary tenant A and a standby tenant B in your environment. After you create a new standby tenant C, you can set the log restore source of tenant C to primary tenant A or standby tenant B.

• If you need to modify the log restore source of a standby tenant from archive logs to directly reading logs over the network, you must create a dedicated user for accessing views in the primary tenant before setting the log restore source. For detailed steps on creating a dedicated user for accessing views, see Step 1: Create a dedicated user for accessing views in Create an empty standby tenant.

• When modifying the log restore source of a standby tenant from one log restore source to another, ensure that the log restore source remains consistent with the source tenant specified when the standby tenant was created. In other words, either use the log archive of the source tenant or directly point to the source tenant.

• In a single-server scenario, the location information of the primary tenant's system log stream is the address of the single OBServer node.

Privilege requirements

The sys tenant's root user (root@sys) or the administrator user of each tenant must execute this statement. Specifically:

• In MySQL mode, the default administrator user is root.
• In Oracle mode, the default administrator user 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

In a physical standby database scenario based on network-based log transmission, the following example shows how to modify the log restore source of a standby tenant to the archive logs of the primary tenant:

1. Obtain the archive path of the primary tenant.

   • Obtain the archive path of the primary tenant from the sys tenant of the cluster where the primary tenant is located.

     Statement:

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

   • Obtain the archive path of the primary tenant from the primary tenant.

     MySQL mode

     Oracle mode

     Statement:

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

     Statement:

     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. Modify the log restore source.

   • Modify the log restore source of the standby tenant standby_tenant1 from the sys tenant of the cluster where the standby tenant is located.

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

   • Modify the log restore source of the standby tenant standby_tenant1.

     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 restore source of the standby tenant standby_tenant2 to directly obtain logs through a network connection:

1. Obtain the location information of the system log stream of the primary tenant primary_tenant.

   • Obtain the location information of the system log stream of the primary tenant from the sys tenant of the cluster where the primary tenant is located.

     Statement:

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

   • Obtain the location information of the system log stream of the primary tenant from the primary tenant.

     MySQL mode

     Oracle mode

     Statement:

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

     Statement:

     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 query 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 the source standby tenant is a single-replica tenant, only one row of result is returned.

2. Modify the log restore source.

   • Modify the log restore source of the standby tenant from the sys tenant of the cluster where the standby tenant is located.

     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;
     

   • Modify the log restore source of the standby 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 of log transmission service

• Set the log restore source