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 Scenario where a physical standby tenant is created by using a network in this topic and configure the log restore source 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 as an administrator to the standby tenant, or to the sys tenant of the cluster where the standby tenant resides.

2. Execute the following command to configure the standby tenant's restore source to point to the primary tenant's archiving destination.

   • From the sys tenant of the cluster where the standby tenant resides, configure the standby tenant's restore source

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

   • From the standby tenant, configure this tenant's own restore source

     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 OSS, the following examples show how to set the log restore source from the sys tenant of the cluster where the standby tenant resides, or from 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 NFS, the following examples show how to set the log restore source from the sys tenant of the cluster where the standby tenant resides, or from 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 standby tenant method, the default log restore source is set when you execute the CREATE STANDBY TENANT statement: it points directly to the primary tenant. Therefore, you usually do not need to set it again. If you still need to change the log restore source to use the primary tenant's log archiving, refer to Scenario where a physical standby tenant is created based on log archiving in this topic and configure the log restore source again.

For a standby tenant created by using other methods (the backup and restore (with logs) feature and the BACKUP DATABASE PLUS ARCHIVELOG feature), if you need to set the log restore source to point to the primary tenant or source standby tenant, follow the steps below to set it manually with the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement.

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: obtains the primary tenant's server list, the LSN range of log streams on replica services, and role information (such as whether a replica is a leader).

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

• GV$OB_UNITS: queries all unit information of the primary tenant to obtain replica status, zone, and region information for filtering, retrieving, and maintaining tenant server connections.

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

• GV$OB_PARAMETERS: queries 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: obtains access point information. If the access point changes during primary-tenant migration, replication, or disaster recovery, the standby tenant detects the change automatically and you do not need to change it manually.

  For more information about the DBA_OB_ACCESS_POINT view, see DBA_OB_ACCESS_POINT.

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

  For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

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

  For more information about the DBA_OB_LS view, see DBA_OB_LS.

• DBA_OB_LOG_RESTORE_SOURCE: used when switching a standby tenant to the primary role to query upstream information about the former primary tenant, so you can determine whether Switchover can run without changing the protection mode.

  For more information about the DBA_OB_LOG_RESTORE_SOURCE view, see DBA_OB_LOG_RESTORE_SOURCE.

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. Execute the following command to create a new 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, DBA_OB_LS, and DBA_OB_LOG_RESTORE_SOURCE 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 new user.

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

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

   STANDBY_REPLICATION is a system default role that includes the CREATE SESSION system privilege and 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
   • DBA_OB_LOG_RESTORE_SOURCE

   The statement is as follows:

   obclient [SYS]> GRANT STANDBY_REPLICATION TO rep_user;
   

Step 2: Obtain access point information for the primary tenant or source standby tenant

For a network-based physical standby deployment, when you set the log restore source you also need the access point information of the primary tenant or source standby tenant; that is, the IP addresses and SQL port numbers of the OBServer nodes where the tenant replicas reside.

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

2. Execute the following command to obtain access point information.

   • MySQL-compatible mode

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

     Note

     If you are logged in to the sys tenant, query the CDB_OB_ACCESS_POINT view to obtain access point information.

   • 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, Transfer, or other operations, its log streams might be deleted and 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 delayed deletion time for 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: Set the 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. Execute the following command to set 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. Use the information obtained in Step 2. You do not need to specify all OBServer nodes if multiple nodes exist.
   • $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.

   An example is as follows:

   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=******';
   

   After successful configuration, 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 verify whether the log restore source has been updated. For more information, see View log restore source information.

References

View log restore source information