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. You can dynamically change the log restore source at any time. You can also switch from one log restore source to another. This topic explains how to set the log restore source in the two deployment scenarios.

Considerations

When you change the log restore source from one tenant 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 archive of the source tenant or the source tenant itself. If the log restore sources are different before and after the change, the data of the standby tenant will be incorrect and irrecoverable. In this case, you need to create a new standby tenant.

Physical standby database scenario based on log archives

If you create a physical standby database by using log shipping, the default restore source, namely, the log archive destination of the primary tenant or the source standby tenant, is set for the standby tenant when you restore the tenant by using the RESTORE command. Therefore, no additional configuration is generally required. If you want to change the log restore source to directly point to the primary tenant or the source standby tenant, you can follow the procedure outlined in the Physical standby database scenario based on network connections section.

If you create a standby tenant by using a method other than the physical backup and restore (with logs) feature or the BACKUP DATABASE PLUS ARCHIVELOG feature, and you want to set the restore source to the log archive destination of the primary tenant, you can perform the following manual steps to configure the restore source.

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

2. Execute the following command 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 if you want the sys tenant of the cluster to which the standby tenant belongs to perform the configuration.

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

   • Configure the restore source of the standby tenant if you want the tenant itself to perform the configuration.

     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 connection

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 usually do not need to set the log restore source. If you want to change the log restore source to use the logs of the primary tenant, you need to follow the steps outlined in Physical standby tenants created through log archive.

For physical standby tenants created by using methods other than the empty tenant method (such as the backup restore (with logs) method and the BACKUP DATABASE PLUS ARCHIVELOG method), you can use the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement to manually set the log restore source to the primary tenant or a source physical standby tenant.

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 from 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 is used to obtain the machine list of the primary tenant, the log stream LSN range, and the role (whether it is a leader) of each 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 information about all units in the primary tenant, including the replica status, zone, and region. This information is used to filter, connect, and maintain 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.

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

• DBA_OB_ACCESS_POINT: This view is used to obtain information about access points. In scenarios such as migration, replication, and disaster recovery of the primary tenant, if the access points change, the standby tenant can automatically sense the changes without requiring manual modifications from users.

  For more information about the DBA_OB_ACCESS_POINT view, see DBA_OB_ACCESS_POINT.

• DBA_OB_TENANTS: This view is used to obtain the compatible 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 obtain 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 to the user for all tables in the oceanbase database. You can also grant the SELECT privilege to the user only for 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 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 destination for a network-based physical standby database, you need to 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 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 architecture, after the primary tenant performs operations such as scaling down and transfer, its log streams may be deleted and its logs may be recycled, which can cause the log synchronization for the standby tenant to be stuck. To resolve this issue, you need to enable archiving mode on the primary tenant or set the tenant-level parameter ls_gc_delay_time.

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

• Check whether archiving mode is enabled on the primary tenant

  1. Log in to the sys tenant of the cluster to which the primary tenant belongs, or log in to the primary tenant as the administrator.

  2. Check whether archiving mode is enabled.

     • The sys tenant of the cluster to which the primary tenant belongs

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

     • A user tenant

       MySQL mode

       Oracle mode

       Query whether archiving mode is enabled in MySQL mode:

       obclient> SELECT LOG_MODE FROM oceanbase.DBA_OB_TENANTS;
       

       Query whether archiving mode is enabled in Oracle mode:

       obclient> SELECT LOG_MODE FROM SYS.DBA_OB_TENANTS;
       

     Here is an example of the query result:

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

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

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

  The ls_gc_delay_time parameter specifies the latency 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 archiving mode is disabled, a log stream that meets the deletion conditions will be retained for a period of time before it is recycled.

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

  2. Check the value of the ls_gc_delay_time parameter.

     obclient> SHOW PARAMETERS LIKE '%ls_gc_delay_time%';
     

     If the value is 0s, archiving mode is disabled. You can set this parameter 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 resides, or the sys tenant of the standby tenant.

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

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

     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 standby 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 that you want to connect to.
   • $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 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