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 as needed. 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 log restore source remains the same as when the standby tenant was created, namely, that it is either the log archive of the source tenant or the source tenant itself. If the log restore source changes, the data of the standby tenant will be incorrect and irrecoverable. In this case, you need to create a new standby tenant.

Physical standby databases created from log archives

A physical standby database created by using the physical backup restore (with logs) feature has a default restore source, namely, the log archive destination of the primary tenant or the source standby tenant, when you restore the tenant by using the RESTORE command. Therefore, you usually do not need to set the restore source. 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 databases created from network backups section.

If you create a standby tenant by using a method other than the physical backup restore (with logs) feature, and you want to change the log restore source to the archive destination of the primary tenant, you must manually configure the log restore source by following these steps.

1. Log in to the sys tenant of the cluster where the standby tenant is located 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 when the sys tenant of the standby tenant's cluster is online.

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

   • Configure the restore source of the standby tenant when the standby tenant is online.

     ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=archive_path';
     

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

   Here is an example:

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

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

Physical standby database scenario based on the network

A physical standby database 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 perform the steps outlined in Physical standby database scenario based on log archives.

For a physical standby database 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), if you want to change the log restore source to point to the primary tenant or a source tenant, you need to manually set the log restore source 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 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 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 information about all units in the primary tenant, including the replica status, zone, and region. This information is used to filter, connect to, and manage 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 cluster_id and tenant_id for services 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 obtain access entry information. If the access entry changes during tenant 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 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 and grant privileges, follow the steps below.

MySQL mode

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

2. Execute the following statement to create a new user.

   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.

   GRANT SELECT ON oceanbase.* TO rep_user;
   

Oracle mode

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

2. Execute the following statement to create a new user.

   CREATE USER rep_user IDENTIFIED BY '******';
   

3. Grant the STANDBY_REPLICATION role to the rep_user user.

   Starting from OceanBase Database V4.2.2, the system default STANDBY_REPLICATION role is supported. This role includes 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

   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 statement to obtain the access entry information.

   • MySQL mode

     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

     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 architecture, after the primary tenant undergoes scaling down or a transfer operation, 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 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 to which the primary tenant belongs, or the primary tenant, as the administrator.

  2. Check whether the ARCHIVELOG 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

       In MySQL mode, execute the following command to check whether the ARCHIVELOG mode is enabled:

       obclient> SELECT LOG_MODE FROM oceanbase.DBA_OB_TENANTS;
       

       In Oracle mode, execute the following command to check whether the ARCHIVELOG mode is enabled:

       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 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 for deleting log streams. The default value is 0s, which indicates that the latency-based deletion of log streams is disabled. If 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 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, 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 resides, or the sys tenant of the tenant where the standby tenant resides, as the administrator.

2. Execute the following statement 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 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. Multiple OBServer nodes can be specified by separating their IP addresses and port numbers with commas.
   • $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:

   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;
   

   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 to verify whether the log restore source is modified. For more information, see View the log restore source.

References

View logs to restore the source information