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 log archiving 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 log archiving for the primary tenant or source standby tenant and perform a full data backup.

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

   1. Specify the archive destination.

      Assuming that the archive medium is Network File System (NFS), execute the following statement in a user tenant to set the archive 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 log archiving for the primary tenant or source standby tenant.

      Notice

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

      Execute the following statement in a user tenant to enable log archiving:

      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 CDB_OB_ARCHIVELOG view for the archiving status. 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 log archiving, 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 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 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 query the DBA_OB_BACKUP_JOBS view from a user tenant or the CDB_OB_BACKUP_JOBS view from the sys tenant for the data backup progress. 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 query the DBA_OB_BACKUP_JOB_HISTORY view from a user tenant or the CDB_OB_BACKUP_JOB_HISTORY view from the sys tenant for the data backup history. 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 example 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 example 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 tenant.

   Here is an example:

   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 specifies the name of the standby tenant to be restored.

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

   • UNTIL TIME= timestamp specifies 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 the timestamp, see Parameters related to physical restore.

   • restore_option specifies the restore options, including pool_list, locality, and primary_zone. Separate the options with ampersands (&). We recommend that you set locality and primary_zone to the same values as those 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' specifies 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 specifies the backup path of the key.

     • password specifies the encryption password specified during key backup.

   The following example 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 query the DBA_OB_TENANTS view for the restore progress.

   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 View 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 SYNC_SCN field in the DBA_OB_TENANTS view for the synchronization timestamp for the new standby tenant.

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

   • 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