Specify a log restore source

You must specify a log restore source for the standby tenant regardless of whether the Physical Standby Database solution is based on log archiving or network. Both of the two deployment modes allow you to dynamically modify the log restore source as needed, and switch from one log restore source to another. This topic describes how to specify a log restore source in the two deployment modes.

Considerations

When you switch from one log restore source to another, the original and new log restore sources must be the same as the source tenant specified when you created the standby tenant. In other words, the log restore source must be the archived logs of the source tenant or point to the source tenant. Otherwise, non-recoverable data errors occur in the standby tenant, and you must create another standby tenant.

Specify the log restore source for the Physical Standby Database solution based on log archiving

If you execute the RESTORE statement to restore a standby tenant by using the physical backup and recovery feature, a log restore source is specified for the standby tenant by default. The default log restore source is the log archiving destination of the primary tenant or source standby tenant. Therefore, you do not need to specify a log restore source again. However, you can change the log restore source to the primary tenant or source standby tenant. For more information, see Specify the log restore source for the network-based Physical Standby Database solution.

If you create an empty standby tenant or create a standby tenant by using the BACKUP DATABASE PLUS ARCHIVELOG feature, and you want to set the log restore source to archived logs of the primary tenant, perform the following steps:

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

2. Set the restore source of the standby tenant to the archiving destination of the primary tenant.

   • Execute the following statement in the sys tenant of the cluster where the standby tenant resides to specify the restore source of the standby tenant:

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

   • Execute the following statement in the standby tenant to specify the restore source of the standby tenant:

     ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=archive_path';
     

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

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

Specify the log restore source for the network-based Physical Standby Database solution

When you execute the CREATE STANDBY TENANT statement to create an empty standby tenant, a default log restore source is specified by the LOG_RESTORE_SOURCE parameter. The default log restore source is the primary tenant. Therefore, you do not need to specify a log restore source again. However, you can change the log restore source to archived logs of the primary tenant. For more information, see Specify the log restore source for the Physical Standby Database solution based on log archiving.

If you create a standby tenant by using the backup and recovery feature or BACKUP DATABASE PLUS ARCHIVELOG feature, and you want to set the log restore source to the primary tenant or source standby tenant, you must execute the ALTER SYSTEM SET LOG_RESTORE_SOURCE statement. Perform the following steps:

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.

System views to be accessed by the standby tenant:

• GV$OB_LOG_STAT: contains the servers in the primary tenant, the log sequence number (LSN) range of log streams for replicas, and the role information indicating whether a replica is a leader.

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

• GV$OB_UNITS: contains the replica status, zones, and regions of all units in the primary tenant. You can use this view to filter, query, and maintain server connections for tenants.

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

• GV$OB_PARAMETERS: contains 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: contains information about the access portal. If the access portal changes during migration, replication, or disaster recovery of the primary tenant, the standby tenant automatically synchronizes the change without manual modification.

  For more information about the DBA_OB_ACCESS_POINT view, see DBA_OB_ACCESS_POINT.

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

  For more information about the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

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

  For more information about the DBA_OB_LS view, see DBA_OB_LS.

To create and authorize a dedicated user, perform the following operations:

MySQL mode

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

2. Create a user.

   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, and DBA_OB_LS views in the oceanbase database.

   GRANT SELECT ON oceanbase.* TO rep_user;
   

Oracle mode

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

2. Create a user.

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

3. Grant privileges to the user.

   • Grant the privilege to connect to the database.

     GRANT CONNECT TO rep_user;
     

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

     GRANT SELECT on SYS.GV$OB_LOG_STAT to rep_user;
     

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

     GRANT SELECT on SYS.GV$OB_UNITS to rep_user;
     

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

     GRANT SELECT on SYS.GV$OB_PARAMETERS to rep_user;
     

   • Grant the privilege to access the DBA_OB_ACCESS_POINT view.

     GRANT SELECT on SYS.DBA_OB_ACCESS_POINT to rep_user;
     

   • Grant the privilege to access the DBA_OB_TENANTS view.

     GRANT SELECT on SYS.DBA_OB_TENANTS to rep_user;
     

   • Grant the privilege to access the DBA_OB_LS view.

     GRANT SELECT on SYS.DBA_OB_LS to rep_user;
     

Step 2: Query the access portal for the primary tenant or source standby tenant

If you specify a log restore source for the network-based Physical Standby Database solution, you must obtain the IP address and port number of the OBServer node where the tenant replica resides for the primary tenant or source standby tenant.

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

2. Execute the following statement to obtain the information about the access portal:

   • MySQL mode

     SELECT * FROM oceanbase.DBA_OB_ACCESS_POINT;
     

     Note

     If you have logged on to the sys tenant, you need to obtain the information about the access portal by querying the CDB_OB_ACCESS_POINT view.

   • 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 has a single replica, only one row is returned.

Step 3: Specify a log restore source

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

2. Specify 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 = 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 RPC port number of the OBServer nodes where replicas of the primary tenant or source standby tenant reside. You need to use the value obtained in Step 2.
   • $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.

   For 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 = mysql;
   

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

   Then, 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 check whether the log restore source has changed. For more information, see View the log restore source.

References

View the log restore source