Set a log restore source

Whether you are using a log-archiving-based physical standby or a network-based physical standby, you need to configure a log restore source for the standby tenant. Both deployment methods support dynamic, on-demand modification of the log restore source at any time, and you can also switch between different types of log restore sources as needed. This topic will explain how to configure the log restore source for both log-archiving-based and network-based physical standby scenarios.

Considerations

When switching log restore sources, you must ensure that both the old and new sources match the original source tenant specified at the time the standby tenant was created. This means the log restore source must either use the source tenant’s log archiving or point directly to the source tenant. If the log restore sources before and after the change do not match, it will cause data errors for the standby tenant and make recovery impossible. In this case, you will need to create a new standby tenant.

Scenario where a physical standby tenant is created based on log archiving

When you use the physical backup restore (with logs) feature to create a standby tenant, the default restore source is set for the standby tenant when you execute the RESTORE command to restore the standby tenant. The default restore source is the log archiving destination of the primary tenant or the source standby tenant. Therefore, you usually do not need to set a restore source. If you want to set the log restore source to directly point to the primary tenant or the source standby tenant, refer to the content under Scenario where a physical standby tenant is created based on a network and perform the settings again.

For a standby tenant created by using other methods (create an empty standby tenant and use the BACKUP DATABASE PLUS ARCHIVELOG feature to create a standby tenant), if you want to set the log restore source to use the log archiving of the primary tenant, you need to manually set the log restore source. The following steps describe how to do this.

1. Log in to the sys tenant of the standby tenant or the cluster where the standby tenant resides as an administrator.

2. Execute the following commands to configure the restore source of the standby tenant to point to the archiving destination of the primary tenant.

   • Configure the restore source of the sys tenant of the cluster where the standby tenant resides

     ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=archive_path' TENANT = tenant_name;
     

   • Configure the restore source of the standby tenant

     ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=archive_path';
     

   Here, the LOCATION attribute specifies the archiving destination of the primary tenant.

   The following examples describe how to configure the log restore source when the archiving medium is an OSS or NFS server.

   Alibaba Cloud OSS

   NFS

   When the archiving medium of the primary tenant is an OSS server, the following example shows how to configure the log restore source for the sys tenant of the cluster where the standby tenant resides or for the standby tenant:

   obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE ='location=oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com&access_id=****&access_key=****' TENANT = standby_tenant;
   

   obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE ='location=oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com&access_id=****&access_key=****';
   

   When the archiving medium of the primary tenant is an NFS server, the following example shows how to configure the log restore source for the sys tenant of the cluster where the standby tenant resides or for the standby tenant:

   obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE ='location=file:///data/1/sh_archive' TENANT = standby_tenant;
   

   obclient> ALTER SYSTEM SET LOG_RESTORE_SOURCE ='location=file:///data/1/sh_archive';
   

Scenario where a physical standby tenant is created by using a network

When you create a standby tenant by using the empty tenant method, the LOG_RESTORE_SOURCE parameter is set to a default value when you execute the CREATE STANDBY TENANT statement. The default value is to directly point to the primary tenant. Therefore, you usually do not need to set this parameter. If you still want to set the log restore source to the archive log of the primary tenant, you need to refer to Scenario of a physical standby tenant created by using archive logs in this topic to set the log restore source again.

When you create a standby tenant by using the backup and restore (with complete logs) feature or the BACKUP DATABASE PLUS ARCHIVELOG feature, you can execute the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to change the log restore source to the primary tenant or source standby tenant.

Step 1: Create a dedicated user for accessing views

When a standby tenant connects to a primary tenant or source standby tenant, the standby tenant accesses some system views of the primary tenant or source standby tenant. Therefore, a dedicated user with the privilege to query the system views is required. You can use a user with the privilege in the primary tenant or source standby tenant, or create a dedicated user with the privilege in the primary tenant or source standby tenant for the standby tenant.

The standby tenant needs to access the following system views:

• GV$OB_LOG_STAT: contains the servers in the primary tenant, the log sequence number (LSN) range of log streams for replicas, and the role information indicating whether a replica is a leader.

  For more information about the GV$OB_LOG_STAT view, see GV$OB_LOG_STAT.

• GV$OB_UNITS: contains the replica status, zones, and regions of all units in the primary tenant. You can use this view to filter, query, and maintain server connections for tenants.

  For more information about the GV$OB_UNITS view, see GV$OB_UNITS.

• GV$OB_PARAMETERS: contains the metadata required for services, such as the cluster_id and tenant_id of the primary tenant.

  For more information about the GV$OB_PARAMETERS view, see GV$OB_PARAMETERS.

• DBA_OB_ACCESS_POINT: contains information about the access portal. If the access portal changes during migration, replication, or disaster recovery of the primary tenant, the standby tenant automatically synchronizes the change without manual modification.

  For more information about the DBA_OB_ACCESS_POINT view, see DBA_OB_ACCESS_POINT.

• DBA_OB_TENANTS: contains the compatibility mode of the primary tenant.

  For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

• DBA_OB_LS: contains the log streams and log stream status of the primary tenant.

  For more information about the DBA_OB_LS view, see DBA_OB_LS.

To create and authorize a dedicated user, perform the following operations:

MySQL-compatible mode

1. Log in to the primary tenant as the administrator.

2. Create a user.

   obclient [oceanbase]> CREATE USER rep_user IDENTIFIED BY '******';
   

3. Grant privileges to the user.

   The following sample statement grants the user the SELECT privilege on all tables in the oceanbase database. You can also grant the user the SELECT privilege on the GV$OB_LOG_STAT, GV$OB_UNITS, GV$OB_PARAMETERS, DBA_OB_ACCESS_POINT, DBA_OB_TENANTS, and DBA_OB_LS views in the oceanbase database.

   obclient [oceanbase]> GRANT SELECT ON oceanbase.* TO rep_user;
   

Oracle-compatible mode

1. Log in to the primary tenant as the administrator.

2. Create a user.

   obclient [SYS]> CREATE USER rep_user IDENTIFIED BY '******';
   

3. Grant the STANDBY_REPLICATION role to the created user rep_user.

   STANDBY_REPLICATION is a default system role that has the CREATE SESSION privilege and the query privileges on the following views by default:

   • GV$OB_LOG_STAT
   • GV$OB_UNITS
   • GV$OB_PARAMETERS
   • DBA_OB_ACCESS_POINT
   • DBA_OB_TENANTS
   • DBA_OB_LS
   • DBA_OB_LS_HISTORY

   Here is a sample statement:

   obclient [SYS]> GRANT STANDBY_REPLICATION TO rep_user;
   

Step 2: Query the access portal for the primary tenant or source standby tenant

If you specify a log restore source for the network-based Physical Standby Database solution, you must obtain the IP address and port number of the OBServer node where the tenant replica resides for the primary tenant or source standby tenant.

1. Log in to the primary tenant or source standby tenant as the administrator.

2. Execute the following statement to obtain the information about the access portal:

   • MySQL-compatible mode

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_ACCESS_POINT;
     

     Note

     If you have logged on to the sys tenant, you need to obtain the information about the access portal by querying the CDB_OB_ACCESS_POINT view.

   • Oracle-compatible mode

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_ACCESS_POINT;
     

   The query result is as follows:

   +-----------+-------------+-------------+----------+
   | TENANT_ID | TENANT_NAME | SVR_IP      | SQL_PORT |
   +-----------+-------------+-------------+----------+
   |      1004 | mysql       | xx.xx.xx.22 |    17855 |
   |      1004 | mysql       | xx.xx.xx.23 |    17857 |
   |      1004 | mysql       | xx.xx.xx.24 |    17859 |
   +-----------+-------------+-------------+----------+
   3 rows in set
   

   In this example, the queried tenant has three replicas. If the primary tenant or source standby tenant has a single replica, only one row is returned.

Step 3: Check the primary tenant settings

In the current primary/standby architecture, when the primary tenant undergoes scaling in or a transfer operation, its log streams might be deleted, and the logs might be recycled, potentially causing the standby tenant's log synchronization to become stuck. To resolve this issue, you need to enable archiving mode on the primary tenant or configure the tenant-level parameter ls_gc_delay_time.

To check whether archiving mode is enabled on the primary tenant or whether the value of the ls_gc_delay_time parameter is greater than 0s, perform the following steps:

• Check whether archiving mode is enabled on the primary tenant

  1. Log in to the primary tenant or the sys tenant of the cluster where the primary tenant resides using an administrator account.

  2. Query whether archiving mode is enabled.

     • For the sys tenant of the cluster where the primary tenant resides:

       obclient> SELECT LOG_MODE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME='mysql';
       

     • For user tenants:

       MySQL-compatible mode

       Oracle-compatible mode

       Query whether archiving mode is enabled in MySQL-compatible mode:

       obclient> SELECT LOG_MODE FROM oceanbase.DBA_OB_TENANTS;
       

       Query whether archiving mode is enabled in Oracle-compatible mode:

       obclient> SELECT LOG_MODE FROM SYS.DBA_OB_TENANTS;
       

     A sample query result is as follows:

     +--------------+
     | LOG_MODE     |
     +--------------+
     | NOARCHIVELOG |
     +--------------+
     1 row in set
     

     As shown in the query result, ARCHIVELOG indicates that archiving mode is enabled on the primary tenant, while NOARCHIVELOG indicates that archiving mode is disabled. For more information about how to enable archiving mode, see Enable archiving mode.

• Check whether the value of the ls_gc_delay_time parameter is greater than 0s

  The tenant-level parameter ls_gc_delay_time configures the delay time for deleting log streams. The default value is 0s, which means the delayed deletion feature is not enabled. When the ls_gc_delay_time parameter is set and archiving is not enabled, a log stream will wait for a specified period before being recycled after meeting the deletion conditions.

  1. Log in to the primary tenant as the administrator.

  2. Query the value of the ls_gc_delay_time parameter.

     obclient> SHOW PARAMETERS LIKE '%ls_gc_delay_time%';
     

     In the above query result, a value of 0s indicates that the parameter is not configured. If needed, you can adjust this value. For more information about the ls_gc_delay_time parameter, see ls_gc_delay_time.

Step 4: Specify a log restore source

1. Log in as the administrator to the standby tenant or the sys tenant of the cluster where the standby tenant resides.

2. Specify the log restore source.

   • Execute the following statement in the sys tenant of the cluster where the standby tenant resides to specify the log restore source of the standby tenant:

     ALTER SYSTEM SET LOG_RESTORE_SOURCE ='SERVICE=$ip_list USER=$user_name@$tenant_name PASSWORD=$password' TENANT = standby_tenant_name;
     

   • Execute the following statement in the standby tenant to specify the log restore source of the standby tenant:

     ALTER SYSTEM SET LOG_RESTORE_SOURCE ='SERVICE=$ip_list USER=$user_name@$tenant_name PASSWORD=$password';
     

   The parameters are described as follows:

   • $ip_list: the IP addresses and SQL port number of the OBServer nodes where replicas of the primary tenant or source standby tenant reside. You need to use the value obtained in Step 2.
   • $user_name: the dedicated user for accessing views, which is created in Step 1.
   • $tenant_name: the name of the primary tenant or source standby tenant to connect to.
   • $password: the password of the dedicated user for accessing views.

   Here is an example:

   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@mysql PASSWORD=******' TENANT = 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@mysql PASSWORD=******';
   

   Then, you can query the CDB_OB_LOG_RESTORE_SOURCE view in the sys tenant or the DBA_OB_LOG_RESTORE_SOURCE view in a user tenant to check whether the log restore source has changed. For more information, see View the log restore source.

References

View the log restore source