Configure 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 configure a 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 describes how to configure the log restore source in these 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 cannot be restored. In this case, you need to create a new standby tenant.

Physical standby databases created using log archive

If you create a physical standby database by using log archive, when you restore the physical standby database by using the RESTORE command, the system automatically sets the primary tenant or the log archive destination of the source physical standby database as the default restore source for the restored physical standby database. Therefore, you usually do not need to manually set the restore source. If you want to change the restore source to directly point to the primary tenant or the source physical standby database, you can follow the procedure outlined in the Physical standby databases created using the network method section.

If you create a physical standby database by using a method other than log archive (such as creating an empty physical standby database or using the BACKUP DATABASE PLUS ARCHIVELOG feature), and you want to set the restore source to the log archive destination of the primary tenant, you must manually configure the restore source by following the procedure below.

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

2. Run the following command to configure the restore source of the physical standby database to point to the log archive destination of the primary tenant.

   • Configure the restore source of the physical standby database from the sys tenant of its cluster.

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

   • Configure the restore source of the physical standby database from the tenant itself.

     ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=archive_path';
     

   The LOCATION attribute specifies the log 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 databases created through network

A physical standby database created by using an empty tenant 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 databases created through log archive shipping to reconfigure the log restore source.

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 the 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 LSN range of log streams in 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, such as the replica status, zone, and region, for filtering, selecting, and maintaining 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 obtain information about access points. If the access points change during the migration, replication, or disaster recovery of the primary tenant, the standby tenant can automatically sense the changes without requiring manual modifications from 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. Run the following command to create a new user.

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

3. Grant privileges to the user.

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

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

3. Grant privileges to the user.

   • Grant the user the privilege to connect to the database.

     GRANT CONNECT TO rep_user;
     

   • Grant the user access to the GV$OB_LOG_STAT view.

     GRANT SELECT on SYS.GV$OB_LOG_STAT to rep_user;
     

   • Grant the user access to the GV$OB_UNITS view.

     GRANT SELECT on SYS.GV$OB_UNITS to rep_user;
     

   • Grant the user access to the GV$OB_PARAMETERS view.

     GRANT SELECT on SYS.GV$OB_PARAMETERS to rep_user;
     

   • Grant the user access to the DBA_OB_ACCESS_POINT view.

     GRANT SELECT on SYS.DBA_OB_ACCESS_POINT to rep_user;
     

   • Grant the user access to the DBA_OB_TENANTS view.

     GRANT SELECT on SYS.DBA_OB_TENANTS to rep_user;
     

   • Grant the user access to the DBA_OB_LS view.

     GRANT SELECT on SYS.DBA_OB_LS 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

     SELECT * FROM oceanbase.DBA_OB_ACCESS_POINT;
     

     Note

     If you are logged in to the sys tenant, you must query the CDB_OB_ACCESS_POINT view for 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 blocked. To resolve this issue, you need to enable archivelog for the primary tenant or set the tenant-level parameter ls_gc_delay_time.

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

• Check whether archivelog is enabled for the primary tenant

  1. Log in to the sys tenant of the cluster as the administrator or a tenant user with the privileges to perform the operation.

  2. Check whether archivelog 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 archivelog is enabled in MySQL mode:

       obclient> SELECT LOG_MODE FROM oceanbase.DBA_OB_TENANTS;
       

       Query whether archivelog 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 archivelog is enabled for the primary tenant; NOARCHIVELOG indicates that archivelog is disabled. For more information about how to enable archivelog, see Open the log archive mode.

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

  The ls_gc_delay_time parameter specifies the latency for deleting log streams and its default value is 0s, which indicates that the log stream latency deletion feature is disabled. When the ls_gc_delay_time parameter is set and archivelog is disabled, a log stream that meets the deletion conditions will be retained for the specified 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, archivelog is disabled. In this case, you can set the value 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';
     

   The parameters are described as follows:

   • $ip_list: the IP address and SQL port number of the OBServer node where the primary tenant or the source standby tenant is located. Fill in the information obtained in Step 2. Multiple OBServer nodes can be specified, but 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:

   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