Set the log restore source

Whether you use a physical standby database deployed based on log archives or one deployed based on a network connection, you must set the log restore source for the standby tenant. The log restore source can be changed dynamically at any time in both deployment scenarios. In addition, you can switch from one log restore source to another. This topic explains how to set the log restore source in the two scenarios.

Considerations

When you change the log restore source from one type to another, make sure that the new log restore source is the same as the source tenant specified when the standby tenant was created, namely, the log archives of the source tenant or the source tenant itself. If the log restore sources before and after the change are different, the data of the standby tenant will be incorrect and cannot be restored. In this case, you need to create a new standby tenant.

Physical standby databases created based on log archives

A physical standby database created using the physical backup restore (with logs) feature has a default restore source, which is the log archive destination of the primary tenant or the source standby tenant, when you restore the tenant using the RESTORE command. Therefore, the default restore source is usually set and you typically do not need to set it again. If you want to change the log restore source to directly point to the primary tenant or the source standby tenant, follow the procedure for setting the log restore source in the Physical standby databases created based on network connections section.

If you create a standby tenant using methods other than the physical backup restore (with logs) feature, and want to change the log restore source to the archive destination of the primary tenant, you must manually set the log restore source following the procedure below.

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

2. Execute the following statement to configure the restore source of the standby tenant to point to the archive destination of the primary tenant.

   • Configure the restore source of the standby tenant when it resides in the same cluster as the primary tenant.

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

   • Configure the restore source of the standby tenant when it resides in a different cluster from the primary tenant.

     ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=archive_path';
     

   The LOCATION attribute specifies the archive destination of the primary tenant.

   Here is an example:

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

Physical standby tenants created through network synchronization

A physical standby tenant created by using the empty tenant method has a default log restore source, namely, the primary tenant, which is specified by using the LOG_RESTORE_SOURCE parameter in the CREATE STANDBY TENANT statement. Therefore, you typically do not need to manually set the log restore source. If you want to change the log restore source to use the log archives of the primary tenant, you need to perform the same operations as those in the Physical standby tenants created through log archive synchronization section.

A physical standby tenant created by using a method other than the empty tenant method (such as the backup restore (with logs) method and the BACKUP DATABASE PLUS ARCHIVELOG method) can be manually configured to point to the primary tenant or the source standby tenant by using the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement.

Step 1: Create a dedicated user for the access view

When a standby tenant connects to the primary tenant or a source standby tenant, it needs to access some system views in the primary tenant or the source standby tenant. Therefore, a dedicated user with query privileges on these system views must be created. You can use an existing user in the primary tenant or the source standby tenant that has the corresponding privileges, or create a new user with the required privileges in the primary tenant or the source standby tenant.

The following system views are accessed by the standby tenant:

• GV$OB_LOG_STAT: This view provides the machine list of the primary tenant, the log stream LSN range of the replica service, and the role (whether it is a leader) of the replica.

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

• GV$OB_UNITS: This view is used to query the information of all units in the primary tenant, such as the replica status, zone, and region, for filtering, selecting, and managing tenant machines.

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

• GV$OB_PARAMETERS: This view is used to query essential metadata, such as the cluster_id and tenant_id, required for the service in the primary tenant.

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

• DBA_OB_ACCESS_POINT: This view is used to query access entry information. If the access entry in the primary tenant changes during migration, disaster recovery, or other scenarios, the standby tenant can automatically sense the change without requiring manual modification by the user.

  For more information about the DBA_OB_ACCESS_POINT view, see DBA_OB_ACCESS_POINT.

• DBA_OB_TENANTS: This view is used to query the compatibility mode of the primary tenant.

  For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

• DBA_OB_LS: This view is used to query the log stream list and status of the primary tenant.

  For more information about the DBA_OB_LS view, see DBA_OB_LS.

To create a dedicated user for the access view and grant privileges to the user, perform the following steps.

MySQL mode

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

2. Run the following command to create a new user.

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

3. Grant privileges to the user.

   In the following example, the SELECT privilege is granted on all tables in the oceanbase database to the user. You can also grant the SELECT privilege only 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 to the user.

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

Oracle mode

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

2. Run the following command to create a new user.

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

3. Grant the STANDBY_REPLICATION role to the rep_user user.

   The STANDBY_REPLICATION role is a default system role that contains the CREATE SESSION 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

   The statement is as follows:

   obclient [SYS]> GRANT STANDBY_REPLICATION TO rep_user;
   

Step 2: Obtain the access entry information of the primary tenant or source standby tenant

When you set up a log restore source for a network-based physical standby database, you must obtain the access entry information of the primary tenant or source standby tenant, which is the IP address and port number of the OBServer node where the tenant replica resides.

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

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

   • MySQL mode

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

     Note

     If you have logged in to the sys tenant, execute the SELECT * FROM CDB_OB_ACCESS_POINT statement to obtain the access entry information.

   • Oracle 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 is a single-replica tenant, the query result contains only one row.

Step 3: Check the settings of the primary tenant

In the current primary/standby tenant architecture, when the primary tenant undergoes scaling down or transfer, the logs of the primary tenant may be deleted or recycled, which can cause log synchronization to be stuck in the standby tenant. To resolve this issue, you need to enable the ARCHIVELOG mode for the primary tenant or set the tenant-level parameter ls_gc_delay_time.

To check whether the ARCHIVELOG mode is enabled or the ls_gc_delay_time parameter is set for the primary tenant, follow these steps:

• Check whether the ARCHIVELOG mode is enabled for the primary tenant

  1. Log in to the sys tenant of the cluster as the administrator or a tenant user.

  2. Query whether the ARCHIVELOG mode is enabled.

     • The sys tenant of the cluster

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

     • A user tenant

       MySQL mode

       Oracle mode

       Query whether the ARCHIVELOG mode is enabled in MySQL mode:

       obclient> SELECT LOG_MODE FROM oceanbase.DBA_OB_TENANTS;
       

       Query whether the ARCHIVELOG mode is enabled in Oracle mode:

       obclient> SELECT LOG_MODE FROM SYS.DBA_OB_TENANTS;
       

     A sample query result is as follows:

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

     Based on the query result, ARCHIVELOG indicates that the ARCHIVELOG mode is enabled for the primary tenant; NOARCHIVELOG indicates that the ARCHIVELOG mode is disabled. For more information about how to enable the ARCHIVELOG mode, see Open the log archive mode.

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

  The ls_gc_delay_time parameter specifies the latency period for deleting log streams. The default value is 0s, which indicates that the latency deletion feature is disabled. When the ls_gc_delay_time parameter is set and the ARCHIVELOG mode is disabled, a log stream that meets the deletion conditions will be retained for the latency period before it is recycled.

  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%';
     

     If the value is 0s, it indicates that the parameter is not set. In this case, you can set it as needed. For more information, see ls_gc_delay_time.

Step 4: Set the log restore source

1. Log in to the sys tenant of the cluster where the standby tenant is located, or log in to the standby tenant as the administrator.

2. Execute the following statement to set the log restore source.

   • Set the log restore source for the standby tenant in the sys tenant of the cluster.

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

   • Set the log restore source for the current tenant.

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

   Here is a breakdown of the parameters:

   • $ip_list: the IP address and the SQL port number of the OBServer node where the primary tenant or the source standby tenant resides. Fill in the information obtained in Step 2. If multiple OBServer nodes are provided, you do not need to specify all of them.
   • $user_name: the dedicated user created in Step 1.
   • $tenant_name: the name of the primary tenant or the source standby tenant.
   • $password: the password of the dedicated user.

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

   After the setting is successful, the system tenant (sys tenant) or a user tenant can query the CDB_OB_LOG_RESTORE_SOURCE view or the DBA_OB_LOG_RESTORE_SOURCE view respectively to verify whether the log restore source is modified. For more information, see View the log restore source.

References

View the source information in logs after restore