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

To create a standby tenant by using the BACKUP DATABASE PLUS ARCHIVELOG feature, generate a complete dataset that includes all data and archive logs of the primary tenant on local or shared storage. Then upload the dataset to a medium accessible to the cluster where the standby tenant will be created. Finally, restore the standby tenant from the dataset.

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

1. Log in 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.

      For a primary tenant in standalone mode, you can use an accessible path on the server where the tenant replica and observer process reside as the archiving destination. For other scenarios, you must use shared storage (such as NFS or Alibaba Cloud 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/:

      • The sys tenant of the cluster where the primary tenant resides sets 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 specified primary tenant name.

      • The primary tenant sets the archiving destination for itself:

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

      For more information about how to set the archiving destination, see Preparations before log archive.

   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.

      • The sys tenant of the cluster where the primary tenant resides enables archivelog for the primary tenant:

        ALTER SYSTEM ARCHIVELOG TENANT = mysql;
        

        Here, mysql is the specified primary tenant name.

      • The primary tenant enables archivelog for itself:

        ALTER SYSTEM ARCHIVELOG;
        

      For more information about how to enable archivelog, see Enable archivelog.

   3. Verify that the archiving status is DOING.

      The primary tenant can verify the archiving status by querying the DBA_OB_ARCHIVELOG view (the sys tenant queries 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-compatible tenant

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

      • Oracle-compatible 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  | 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 archiving is in normal operation.

      For more information about how to view the archiving status, 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, the primary tenant must perform a data backup by using the BACKUP DATABASE PLUS ARCHIVELOG feature to generate a complete dataset that contains archive logs.

   1. Specify the backup destination.

      For a primary tenant 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. For other scenarios, 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:

      • The sys tenant of the cluster where the primary tenant resides sets the backup destination for the primary tenant:

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

        Here, mysql is the specified primary tenant name.

      • The primary tenant sets the backup destination for itself:

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

      For more information about how to set the backup destination, see Preparations before data backup.

   2. Perform data backup.

      • The sys tenant of the cluster where the primary tenant resides performs data backup for the primary tenant:

        ALTER SYSTEM BACKUP TENANT = mysql PLUS ARCHIVELOG;
        

        Here, mysql is the specified primary tenant name.

      • The primary tenant performs 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 are as follows:

      • sys tenant

        SELECT * FROM oceanbase.CDB_OB_BACKUP_JOBS;
        

      • MySQL-compatible tenant

        SELECT * FROM oceanbase.DBA_OB_BACKUP_JOBS;
        

      • Oracle-compatible 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-compatible tenant

        SELECT * FROM oceanbase.DBA_OB_BACKUP_JOB_HISTORY;
        

      • Oracle-compatible 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, when the value of STATUS is COMPLETED, the data backup is completed. When the PLUS_ARCHIVELOG column shows ON, the data backup was performed by using the BACKUP DATABASE PLUS ARCHIVELOG feature. In this case, the backup directory contains the archive logs required for database operation. Together, they constitute a complete dataset with archive logs.

(Optional) Step 2: Disable archivelog for the primary tenant

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

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

2. Execute the following command to disable archivelog for the primary tenant.

   • The sys tenant of the cluster where the primary tenant resides disables archivelog for the primary tenant:

     ALTER SYSTEM NOARCHIVELOG TENANT = mysql;
     

     Here, mysql is the specified primary tenant name.

   • The primary tenant disables archivelog for itself:

     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 of the primary tenant from the specified backup destination.

   For example, in this topic, the data backup of the primary tenant is stored at /data/1/sh_databackup.

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

   For example, for a primary tenant in standalone mode, package the data backup and copy it to an accessible path on the server where the standby tenant replica and observer process reside. For other scenarios, you can place it on 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 complete dataset that contains archive logs

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

2. Execute the following command to create the Unit specification required for the standby tenant.

   The Unit specification for the new standby tenant is not required to be the same as that of the source tenant. You can use a smaller Unit specification. For example, create a Unit specification named unit1 with 1 CPU core and 5 GB memory:

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

   For more information about how to create a Unit specification, see Create a tenant.

3. Create the resource pool required for the standby tenant.

   For example, create 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 about how to create a resource pool, see Create a tenant.

4. Restore the standby tenant from the complete dataset that contains archive logs.

   Assuming that the obtained data backup is placed at file:///data/1/sh_databackup, which is accessible to the standby tenant, the SQL statement is as follows:

   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, when the value of TENANT_ROLE is STANDBY, the standby tenant is restored.

What to do next

After you create a standby tenant by using this method, you must also 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 restore (with complete logs) feature

• Specify a log restore source

• Specify a log restore end point