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

This method is applicable to users who use OceanBase Database Community Edition or have deployed OceanBase Database in standalone mode on a single server. It generates a database snapshot for all data and archived logs of the primary tenant on the local server if the primary tenant is in standalone mode or on the shared storage in OceanBase Database Community Edition when the primary tenant belongs to a cluster. Then you can upload the snapshot to a medium accessible to the cluster where you want to create a standby tenant, and finally restore the standby tenant from the database snapshot.

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

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

2. Enable archivelog for the primary tenant.

   1. Specify the archiving destination.

      If the primary tenant is in standalone mode, you can use an accessible path on the server where the tenant replica and observer process reside as the archiving destination. Otherwise, you must use shared storage such as Network File System (NFS) or Alibaba Cloud Object Storage Service (OSS) as the archiving destination.

      Assuming that the archiving medium is NFS, the following sample statements set the archiving destination path to /data/1/sh_archive/:

      • Execute the following statement in the sys tenant of the cluster where the primary tenant resides to specify the archiving destination for the primary tenant:

        ALTER SYSTEM SET LOG_ARCHIVE_DEST = "LOCATION=file:///data/1/sh_archive" TENANT = mysql;
        

        Here, mysql is the name of the primary tenant.

      • Execute the following statement in the primary tenant to specify the archiving destination for the primary tenant:

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

      For more information, see Preparations.

   2. Enable archivelog.

      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 statement in the sys tenant of the cluster where the primary tenant resides to enable archivelog for the primary tenant:

        ALTER SYSTEM ARCHIVELOG TENANT = mysql;
        

        Here, mysql is the name of the primary tenant.

      • Execute the following statement in the primary tenant to enable archivelog for the primary tenant:

        ALTER SYSTEM ARCHIVELOG;
        

      For more information, see Enable ARCHIVELOG.

   3. Verify that the archiving status is DOING.

      You can check the archiving status by querying the DBA_OB_ARCHIVELOG view in the primary tenant or the CDB_OB_ARCHIVELOG view in the sys tenant. Sample SQL statements:

      • 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;
        

      The query result is as follows:

      +---------+----------+---------+--------+---------------------+----------------------------+--------------------------------+
      | DEST_ID | ROUND_ID | DEST_NO | STATUS | CHECKPOINT_SCN      | CHECKPOINT_SCN_DISPLAY     | PATH                           |
      +---------+----------+---------+--------+---------------------+----------------------------+--------------------------------+
      |    1001 |        1 |       0 | DOING  | 1680867152120380679 | 2023-04-07 19:32:32.120380 | file:///data/1/xianlin_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.

3. Perform a data backup for the primary tenant by using the BACKUP DATABASE PLUS ARCHIVELOG feature.

   After you enable archivelog, you must perform a data backup for the primary tenant by using the BACKUP DATABASE PLUS ARCHIVELOG feature to generate a database snapshot.

   1. Specify the backup destination.

      If the primary tenant is in standalone mode, you can use an accessible path on the server where the tenant replica and observer process reside as the backup destination. The backup destination path must be different from the archiving destination path. Otherwise, you must use shared storage such as NFS or Alibaba Cloud OSS as the backup destination.

      Assuming that the backup medium is NFS, the following sample statements set the backup destination path to /data/1/sh_databackup:

      • Execute the following statement in the sys tenant of the cluster where the primary tenant resides to specify the backup destination for the primary tenant:

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

        Here, mysql is the name of the primary tenant.

      • Execute the following statement in the primary tenant to specify the backup destination for the primary tenant:

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

      For more information, see Preparations.

   2. Perform a data backup.

      • Execute the following statement in the sys tenant of the cluster where the primary tenant resides to perform the data backup:

        ALTER SYSTEM BACKUP TENANT = mysql PLUS ARCHIVELOG;
        

        Here, mysql is the name of the primary tenant.

      • Execute the following statement in the primary tenant to perform the data backup:

        ALTER SYSTEM BACKUP DATABASE PLUS ARCHIVELOG;
        

   3. Check the data backup status.

      You can check the data backup progress by querying the DBA_OB_BACKUP_JOBS view in the primary tenant or the CDB_OB_BACKUP_JOBS view in the sys tenant. Sample SQL statements:

      • 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:

      • 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               | ON              | FULL        | USER_TENANT | NONE            |        | 2023-04-07 19:52:35.361398 | 2023-04-07 19:58:57.223196 | 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. The value of the PLUS_ARCHIVELOG field is ON, which indicates that the data backup is performed by using the BACKUP DATABASE PLUS ARCHIVELOG feature. In this case, the backup directory contains the archived logs required for the operation of the database. The data backup and archived logs constitute a database snapshot.

Step 2: Disable archivelog for the primary tenant

After the data backup, you do not need to continuously archive logs. You can disable archivelog for the primary tenant.

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

2. Disable archivelog for the primary tenant.

   • Execute the following statement in the sys tenant of the cluster where the primary tenant resides to disable archivelog for the primary tenant:

     ALTER SYSTEM NOARCHIVELOG TENANT = mysql;
     

     Here, mysql is the name of the primary tenant.

   • Execute the following statement in the primary tenant to disable archivelog for the primary tenant:

     ALTER SYSTEM NOARCHIVELOG;
     

Step 3: Obtain the data backup and store it at a location accessible to the standby tenant

1. Obtain the data backup for the primary tenant from the specified backup destination.

   For example, the data backup is stored at /data/1/sh_databackup.

2. Store the data backup at a location accessible to the standby tenant to be created.

   For example, if the primary tenant is in standalone mode, package the data backup and copy it to an accessible path on the server where the replica of the standby tenant and the observer process reside. Otherwise, you can store the data backup in shared storage such as NFS or Alibaba Cloud OSS that is accessible to the standby tenant.

Step 4: Restore the standby tenant by using the database snapshot

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

2. Create a unit with required specifications for the standby tenant.

   The following sample statement creates a unit named unit1 that contains 1 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.

   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. Restore the standby tenant from the database snapshot of the primary tenant.

   Assuming that the data backup is stored at file:///data/1/sh_databackup, which is accessible to the standby tenant, execute the following SQL statement:

   ALTER SYSTEM RESTORE standby_tenant FROM 'file:///data/1/sh_databackup' WITH 'pool_list=pool_for_standby';
   

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-07 20:43:39.883628 | NORMAL | STANDBY     | 2023-04-07 20:05:16.617492 |
   +----------------+-------------+----------------------------+--------+-------------+----------------------------+
   1 row in set
   

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

What to do next

After you create a standby tenant by using this method, you must perform the following operations:

1. Specify a log restore source

2. Specify a log restore end point

References

• Preparations

• Create an empty standby tenant

• Create a standby tenant by using the backup and recovery feature

• Specify a log restore source

• Specify a log restore end point