SET LOG_RESTORE_SOURCE

Purpose

You can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to set a log restore source. OceanBase Database allows you to dynamically modify the log restore source. You can use this statement to set a log restore source for a standby tenant without one, or switch the log restore source of a standby tenant to another one.

The Physical Standby Database solution can be based on log archiving or network, depending on the deployment mode. In a log archiving-based Physical Standby Database scenario, a standby tenant obtains logs from the archive logs of the primary tenant or another standby tenant. In a network-based Physical Standby Database scenario, a standby tenant directly reads logs from the primary tenant or another standby tenant over the network.

After 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 log restore source for the standby tenant. Otherwise, you cannot enable continuous log synchronization for the standby tenant.

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

In a log archiving-based Physical Standby Database scenario, if the network connection between the current standby tenant and the primary tenant or source standby tenant is normal, you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to change the log restore source of the current standby tenant so that it directly reads logs over the network.

Limitations and considerations

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

  Assume that primary tenant A and standby tenant B exist in the current environment. After you create standby tenant C, you can set the log restore source of standby tenant C to primary tenant A or standby tenant B.

• Before you change the log restore source of a standby tenant so that it directly reads logs over the network, you must create a dedicated user for accessing views for the current standby tenant in the primary tenant. For more information about how to create a dedicated user for accessing views, see the Step 1: Create a dedicated user for accessing views section in the Create an empty standby tenant topic.

• When you switch the log restore source of a standby tenant to another, you must make sure that the source tenant is consistent with the that specified when you created the standby tenant. In other words, the standby tenant can obtain logs from the archive logs of the source tenant or directly reads logs from the source tenant.

• In a standalone OceanBase database, the location of the system log stream of the primary tenant is the IP address and port number of the OBServer node.

Required privileges

You must execute this statement as the root user of the sys tenant (namely root@sys) or as the administrator of a user tenant.

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

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 network-based Physical Standby Database scenario, perform the following steps to change the log restore source of a standby tenant to the archive logs of the primary tenant:

1. Query the archive path of the primary tenant.

   • Query the archive path of the primary tenant from the sys tenant of the cluster where the primary tenant resides

     Here is a sample statement:

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

   • Query the archive path of the primary tenant from the current tenant

     MySQL mode

     Oracle mode

     Here is a sample statement:

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

     Here is a sample 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
   

   The value of path in the NAME column is the log archive path of the current tenant.

2. Change the log restore source.

   • Change the log restore source of the standby tenant standby_tenant1 from the sys tenant of the cluster where the standby tenant resides

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

   • Change the log restore source of the standby tenant standby_tenant1 from the current tenant

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

In a log archiving-based Physical Standby Database scenario, perform the following steps to change the log restore source of the standby tenant standby_tenant2 so that the standby tenant directly reads logs over the network:

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

   • Query the location of the system log stream of the primary tenant from the sys tenant of the cluster where the primary tenant resides

     Here is a sample statement:

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

   • Query the location of the system log stream of the primary tenant from the current tenant

     MySQL mode

     Oracle mode

     Here is a sample statement:

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

     Here is a sample 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 number of rows in the return result varies with the number of replicas of the tenant. In this example, the primary tenant has three replicas. If the primary tenant or source standby tenant has only one replica, only one row is returned.

2. Change the log restore source.

   • Change the log restore source of a standby tenant from the sys tenant of the cluster where the standby tenant resides

     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;
     

   • Change the log restore source of a standby tenant from 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