Create a standby tenant by using the BACKUP DATABASE PLUS ARCHIVELOG feature|V4.3.1| docs|Distributed Database

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

Last Updated：2024-06-28 05:30:30 Updated

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

Log on as the administrator to the primary tenant or the sys tenant of the cluster where the primary tenant resides.
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 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  | 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.
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 complete dataset that contains archive logs.
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 data backup.
  - Perform data backup for the primary tenant from the sys tenant of the cluster where the primary tenant resides.
```
ALTER SYSTEM BACKUP TENANT = mysql PLUS ARCHIVELOG;
```
    Here, mysql is the name of the primary tenant.
  - Perform data backup in the primary tenant.
```
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 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               | 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 archive logs required for the running of the database. The data backup and archive logs constitute a complete dataset.

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.

Log on as the administrator to the primary tenant or the sys tenant of the cluster where the primary tenant resides.
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

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.
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 complete dataset that contains archive logs

Log on as the root user to the sys tenant of the cluster where you want to create the standby tenant.
Create a unit config for the standby tenant.

The specifications of the unit for the new standby tenant do not need to be the same as those of the unit for the source tenant. You can use smaller specifications. 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.
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.
Restore the standby tenant from the complete dataset that contains archive logs.

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

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

Notice

Unlike the physical backup and restore (with complete logs) feature, the BACKUP DATABASE PLUS ARCHIVELOG feature restores a standby tenant without specifying a log restore source. Therefore, you cannot directly enable continuous log synchronization for a standby tenant restored by using this method. You must specify a log restore source to enable continuous log synchronization for the restored standby tenant.

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