Create a standby tenant by using the backup and restore (with complete logs) feature

This is a general method for creating a standby tenant. It restores a standby tenant from data backups and archive logs of the primary tenant or an existing standby tenant.

Step 1: Enable archivelog for the primary tenant or source standby tenant and back up data

New standby tenants are restored from data backups and archive logs. Therefore, before you create a standby tenant, you must enable archivelog for the primary tenant or source standby tenant and perform a full data backup.

1. Enable archivelog for the primary tenant or source standby tenant and ensure that the archiving status is DOING.

   1. Specify the archiving destination.

      Assuming that the archiving medium is Network File System (NFS), execute the following sample SQL statement in a user tenant to set the archiving destination path to /data/1/sh_archive/:

      ALTER SYSTEM SET LOG_ARCHIVE_DEST = 'LOCATION=file:///data/1/sh_archive';
      

      For more information, see Preparations.

   2. Enable archivelog for the primary tenant or source standby tenant.

      Notice

      To ensure archiving of all logs generated during data backup, you must enable archivelog for the tenant before you perform data backup.

      Execute the following SQL statement in a user tenant to enable archivelog:

      ALTER SYSTEM ARCHIVELOG;
      

      For more information, see Enable ARCHIVELOG.

   3. Verify that the archiving status is DOING.

      In the sys tenant, you must query the archiving status from the CDB_OB_ARCHIVELOG view. Sample SQL statements are as follows:

      • sys tenant

        SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM oceanbase.CDB_OB_ARCHIVELOG;
        

      • MySQL tenant

        SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM oceanbase.DBA_OB_ARCHIVELOG;
        

      • Oracle tenant

        SELECT DEST_ID, ROUND_ID, DEST_NO, STATUS, CHECKPOINT_SCN, CHECKPOINT_SCN_DISPLAY, PATH FROM SYS.DBA_OB_ARCHIVELOG;
        

      A sample query result is as follows:

      +---------+----------+---------+--------+---------------------+----------------------------+--------------------------------+
      | DEST_ID | ROUND_ID | DEST_NO | STATUS | CHECKPOINT_SCN      | CHECKPOINT_SCN_DISPLAY     | PATH                           |
      +---------+----------+---------+--------+---------------------+----------------------------+--------------------------------+
      |    1001 |        1 |       0 | DOING  | 1680265982125159110 | 2023-03-31 20:33:02.125159 | file:///data/1/sh_archive      |
      +---------+----------+---------+--------+---------------------+----------------------------+--------------------------------+
      1 row in set
      

      In the query result, the value of STATUS is DOING, which indicates that the system is archiving logs.

      For more information, see View the archiving progress.

2. Enable data backup for the primary tenant or source standby tenant and perform a full backup.

   After you enable archivelog, you must perform a data backup for the primary tenant or source standby tenant.

   1. Specify the backup destination.

      Assuming that the backup medium is NFS, execute the following SQL statement in a user tenant to set the backup destination path to /data/1/sh_databackup:

      ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/1/sh_databackup';
      

      For more information, see Preparations.

   2. Perform data backup.

      Execute the following SQL statement in a user tenant:

      ALTER SYSTEM BACKUP DATABASE;
      

      For more information about how to initiate a full data backup for a tenant, see Initiate a data backup job.

   3. Check the data backup status.

      You can check the data backup progress by querying the DBA_OB_BACKUP_JOBS view in a user tenant or the CDB_OB_BACKUP_JOBS view in the sys tenant. Sample SQL statements are as follows:

      • sys tenant

        SELECT * FROM oceanbase.CDB_OB_BACKUP_JOBS;
        

      • MySQL tenant

        SELECT * FROM oceanbase.DBA_OB_BACKUP_JOBS;
        

      • Oracle tenant

        SELECT * FROM SYS.DBA_OB_BACKUP_JOBS;
        

      If the backup has started, the progress of the backup job is displayed in the views. For more information, see View the data backup progress.

      If the backup is completed, you can view the data backup history by querying the DBA_OB_BACKUP_JOB_HISTORY view in a user tenant or the CDB_OB_BACKUP_JOB_HISTORY view in the sys tenant. Sample SQL statements are as follows:

      • sys tenant

        SELECT * FROM oceanbase.CDB_OB_BACKUP_JOB_HISTORY;
        

      • MySQL tenant

        SELECT * FROM oceanbase.DBA_OB_BACKUP_JOB_HISTORY;
        

      • Oracle tenant

        SELECT * FROM SYS.DBA_OB_BACKUP_JOB_HISTORY;
        

      The query result is as follows:

      +--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+-------------------------------+
      | JOB_ID | INCARNATION | BACKUP_SET_ID | INITIATOR_TENANT_ID | INITIATOR_JOB_ID | EXECUTOR_TENANT_ID | PLUS_ARCHIVELOG | BACKUP_TYPE | JOB_LEVEL   | ENCRYPTION_MODE | PASSWD | START_TIMESTAMP            | END_TIMESTAMP              | STATUS    | RESULT | COMMENT | DESCRIPTION | PATH                          |
      +--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+-------------------------------+
      |      1 |           1 |             1 |                1004 |                0 | 1004               | OFF             | FULL        | USER_TENANT | NONE            |        | 2023-03-31 20:48:32.342315 | 2023-03-31 20:49:39.908559 | COMPLETED |      0 |         |             | file:///data/1/sh_databackup  |
      +--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+-------------------------------+
      1 row in set
      

      In the query result, the value of STATUS is COMPLETED, which indicates that the data backup is completed.

Step 2: Restore a standby tenant

1. Log in as the root user to the sys tenant of the cluster where you want to create the standby tenant.

2. Create a unit config for the standby tenant.

   The following sample statement creates a unit config named unit1 that contains one CPU core and 5 GB of memory:

   CREATE RESOURCE UNIT unit1 MAX_CPU 1, MEMORY_SIZE = '5G';
   

   For more information, see Create a tenant.

3. Create a resource pool for the standby tenant.

   We recommend that you create a resource pool that is homogeneous with the resource pool of the source tenant, namely has the same number of resource units as the resource pool of the source tenant.

   The following sample statement creates a resource pool named pool_for_standby.

   CREATE RESOURCE POOL pool_for_standby UNIT = 'unit1', UNIT_NUM = 1, ZONE_LIST = ('zone1','zone2','zone3');
   

   For more information, see Create a tenant.

4. Execute the RESTORE statement to restore the standby tenant by using the data backups and archive logs of the sys tenant.

   Here is a sample statement:

   ALTER SYSTEM RESTORE standby_tenant_name FROM uri UNTIL TIME ='timestamp' WITH 'restore_option' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
   

   where

   • standby_tenant_name indicates the name of the standby tenant to be restored.

   • uri indicates the data backup path and archive path for the primary or source standby tenant.

   • UNTIL TIME= timestamp indicates the restore end point, which is the timestamp to which the standby tenant is restored. Join the parameter name and the value of timestamp with an equal sign (=). The value of timestamp must be in the YYYY-MM-DD HH24:MI:SS.FF format, accurate to nanoseconds.

     For more information about how to specify timestamp, see Parameters related to physical restore.

   • restore_option provides the following options: pool_list, locality, and primary_zone, where pool_list is required and the others are optional. Different parameters are separated by ampersands (&). When you specify locality or primary_zone, we recommend that you specify the same value as that of the source tenant. Otherwise, load balancing may be performed after the tenant is restored and activated as the primary tenant, which compromises the performance.

     For more information about options in restore_option, see Parameters related to physical restore.

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password' indicates the backup information of the key for encrypting the tenant. You need to specify the key backup information for a tenant restore only when the tenant is configured with transparent encryption.

     • backup_key_path: the backup path for the key.

     • password: the encryption password specified during key backup.

   The following sample statement restores a standby tenant named standby_tenant from the log archive path file:///data/1/sh_archive and data backup path file:///data/1/sh_databackup.

   ALTER SYSTEM RESTORE standby_tenant
   FROM 'file:///data/1/sh_databackup,file:///data/1/sh_archive'
   UNTIL TIME='2023-05-26 15:04:23.825558' WITH 'pool_list=pool_for_standby';
   

   For more information about the RESTORE statement, see Parameters related to physical restore.

5. Verify that the restore is completed.

   You can check the restore progress by querying the DBA_OB_TENANTS view.

   SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN)  FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'standby_tenant';
   

   The query result is as follows:

   +----------------+-------------+----------------------------+--------+-------------+----------------------------+
   | TENANT_NAME    | TENANT_TYPE | CREATE_TIME                | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
   +----------------+-------------+----------------------------+--------+-------------+----------------------------+
   | standby_tenant | USER        | 2023-04-02 20:42:38.800518 | NORMAL | STANDBY     | 2023-04-02 20:42:30.736135 |
   +----------------+-------------+----------------------------+--------+-------------+----------------------------+
   1 row in set
   

   In the query result, the value of TENANT_ROLE is STANDBY, which indicates that the standby tenant is restored.

   For more information, see Query the restore progress.

Step 3: Enable continuous log synchronization

If you create a standby tenant by using the RESTORE statement, the new standby tenant does not continuously synchronize the archive logs of the primary tenant or source standby tenant by default. You must manually execute the RECOVER statement to specify a restore end point for the new standby tenant, to enable continuous log synchronization.

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

2. Execute the RECOVER statement to modify the restore end point for the new standby tenant as needed.

   • Execute the following statement in the sys tenant of the cluster where the new standby tenant resides to enable continuous log synchronization for the new standby tenant:

     ALTER SYSTEM RECOVER STANDBY TENANT = tenant_name UNTIL UNLIMITED;
     

     UNLIMITED indicates that the end point is infinite.

     Here is an example:

     ALTER SYSTEM RECOVER STANDBY TENANT = standby_tenant UNTIL UNLIMITED;
     

   • Execute the following statement in the new standby tenant to enable continuous log synchronization for the new standby tenant:

     ALTER SYSTEM RECOVER STANDBY UNTIL UNLIMITED;
     

3. Query the synchronization timestamp for the new standby tenant from the SYNC_SCN field in the DBA_OB_TENANTS view.

   The synchronization progress of the new standby tenant is displayed in the SCN_TO_TIMESTAMP(SYNC_SCN) field.

   • Query the view from the sys tenant of the cluster where the new standby tenant resides

     SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN)  FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'standby_tenant';
     

   • Query the view from the new standby tenant

     • MySQL tenant

       SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN)  FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'standby_tenant';
       

       The query result is as follows:

       +----------------+-------------+----------------------------+--------+-------------+----------------------------+
       | TENANT_NAME    | TENANT_TYPE | CREATE_TIME                | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) |
       +----------------+-------------+----------------------------+--------+-------------+----------------------------+
       | standby_tenant | USER        | 2023-04-02 20:42:38.800518 | NORMAL | STANDBY     | 2023-04-02 21:06:50.810688 |
       +----------------+-------------+----------------------------+--------+-------------+----------------------------+
       1 row in set
       

     • Oracle tenant

       SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN)  FROM SYS.DBA_OB_TENANTS WHERE TENANT_NAME = 'standby_tenant';
       

       The query result is as follows:

       +------------------+-------------+------------------------------+--------+-------------+---------------------------------+
       | TENANT_NAME      | TENANT_TYPE | CREATE_TIME                  | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN)      |
       +------------------+-------------+------------------------------+--------+-------------+---------------------------------+
       | standby_tenant   | USER        | 23-MAY-23 02.37.58.647507 PM | NORMAL | STANDBY     | 26-MAY-23 01.34.46.896905660 PM |
       +------------------+-------------+------------------------------+--------+-------------+---------------------------------+
       1 row in set
       

References

• Preparations

• Create an empty standby tenant

• Create a standby tenant by using the BACKUP DATABASE PLUS ARCHIVELOG feature

• Specify a log restore source