Perform physical restore

This topic describes how to perform tenant-level physical restore by using SQL statements. OceanBase Database supports tenant-level full restore based on points in time.

OceanBase Database supports restore within a cluster or across clusters.

After you execute the related statements, the system restores the required macroblock data from the corresponding full and incremental backups at the backup destination to the destination tenant and then pulls and replays the transaction logs.

Prerequisites

The preparations before restore are completed. For more information, see Preparations.

Procedure

1. Log in to the sys tenant of the cluster as the root user.

2. (Optional) Execute the following statement to set a restore password for the backup.

   You need to set a restore password for the backup only if a password is added during data backup.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   In the statement, password must be replaced with the password added during data backup. If the password for full backup is different from that for incremental backup, you must enter the password for full backup before that for incremental backup and separate them with a comma.

   If the password for full backup is the same as that for incremental backup, use the following syntax:

   SET DECRYPTION IDENTIFIED BY '******';
   

   If the password for full backup is different from that for incremental backup, use the following syntax:

   SET DECRYPTION IDENTIFIED BY '******','******';
   

3. Execute the following statement to set the encryption configuration:

   Note

   If the data to be restored is not encrypted, or Key Management Service (KMS) of the original version is available, skip this step.

   obclient [(none)]> SET @kms_encrypt_info = '<encryption string>';
   

   Here, <encryption string> is the value of external_kms_info, which is a tenant-level parameter.

   Note

   external_kms_info is used to store specific key management information.

4. Start a restore job.

   Here are some examples:

   • Restore to a specified timestamp

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

   • Restore to a specified SCN

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL SCN=scn WITH 'restore_option' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   • Restore to the latest timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri WITH 'restore_option' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   where:

   • dest_tenant_name specifies the name of the tenant to be restored.

   • uri specifies the data backup path and log archive path, and UNTIL [TIME|SCN] specifies the end checkpoint for restore.

   • To restore to a specified TIME or SCN value, join the parameter name and value with an equal sign (=).

   • restore_option specifies the restore options, including pool_list, locality, primary_zone, and concurrency. 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.

     • pool_list specifies the name of the resource pool created for the tenant to be restored. Multiple resource pools must be separated with commas (,).

     • Locality specifies the locality of replicas of the tenant to be restored, which must match the zone information of pool_list of the cluster where the tenant is located. To ensure homogeneity with the source tenant, we recommend that you set the number of full-featured replicas of the tenant to the same as that of the source tenant.

     • primary_zone specifies the preferred position of the leader of the tenant to be restored, which must match the settings of pool_list and locality. To ensure homogeneity with the source tenant, we recommend that you set the number of primary zones with the top priority of the tenant to the same as that of the source tenant.

     • concurrency specifies the concurrency for data restore. If concurrency is not specified, the MAX_CPU value assigned to the tenant is used as the default value. In this example, the MAX_CPU value is 16.

     For more information about the parameters, 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 data encryption (TDE).

     • backup_key_path specifies the backup path of the key.

     • password specifies the encryption password for key backup.

     For more information about how to back up keys, see Back up a key in Preparations.

   For more information about TIME, SCN, and other parameters, see Parameters related to physical restore.

   Here are some examples:

   OSS

   NFS

   COS

   S3

   Object storage services compatible with the S3 protocol

   • Restore the mysql tenant from an Alibaba Cloud Object Storage Service (OSS) data backup path and an OSS log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool and restore the key of the source tenant.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool' WITH KEY FROM 'oss://oceanbase-test-bucket/data_backup_dest/key?host=***.aliyun-inc.com&access_id=***&access_key=***' ENCRYPTED BY '******';
     

   • Restore the mysql tenant from a data backup path and a log archive path to the SCN 1658285759724047000. Set the resource pool to restore_pool.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' UNTIL SCN=1658285759724047000 WITH 'pool_list=restore_pool';
     

   • Restore the mysql tenant from a data backup path and a log archive path to the latest archive timestamp. Set the resource pool to restore_pool and the restore concurrency to 50.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' WITH 'pool_list=restore_pool&concurrency=50';
     

   • Restore the mysql tenant from a data backup path and a log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool and the replica locality to F@z1,F@z2,F@z3.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool&locality=F@z1,F@z2,F@z3';
     

   • Restore the mysql tenant from a data backup path and a log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool, replica locality to F@z1,F@z2,F@z3, and primary_zone to z1.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool&locality=F@z1,F@z2,F@z3&primary_zone=z1';
     

   Except for the url parameter, the settings of other parameters are consistent with those used for OSS.

   • Restore the mysql tenant from a data backup path and a log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool and restore the key information of the source tenant.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool' WITH KEY FROM 'file:///data_backup_dest/key' ENCRYPTED BY '******';
     

   • Restore the mysql tenant from a data backup path and a log archive path to the latest archive timestamp. Set the resource pool to restore_pool and the restore concurrency to 50.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' WITH 'pool_list=restore_pool&concurrency=50';
     

   Except for the url parameter, the settings of other parameters are consistent with those used for OSS.

   Restore the mysql tenant from a Tencent Cloud Object Storage (COS) data backup path and a COS log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool.

   obclient> ALTER SYSTEM RESTORE mysql FROM 'cos://oceanbase-test-appid/backup?host=cos.ap-***x.myqcloud.com&access_id=***&access_key=***&appid=***, cos://oceanbase-test-appid/backup/archive?host=cos.ap-***x.myqcloud.com&access_id=***&access_key=***&appid=***' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool';
   

   Except for the url parameter, the settings of other parameters are consistent with those used for OSS.

   Restore the mysql tenant from an Amazon Simple Storage Service (S3) data backup path and an S3 log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool.

   obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test-bucket/backup/data/?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***,s3://oceanbase-test-bucket/backup/archive/?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***,' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';
   

   Except for the url parameter, the settings of other parameters are consistent with those used for OSS.

   • Restore the mysql tenant from a Huawei Object Storage Service (OBS) data backup path and an OBS log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool.

     obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test-bucket/backup/data/?host=obs.***.myhuaweicloud.com&access_id=***&access_key=***,s3://oceanbase-test-bucket/backup/archive/?host=obs.***.myhuaweicloud.com&access_id=***&access_key=***' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';
     

   • Restore the mysql tenant from a Google Cloud Storage (GCS) data backup path and a GCS log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool.

     obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test-bucket/backup/data/?host=https://storage.googleapis.com&access_id=***&access_key=***,s3://oceanbase-test-bucket/backup/archive/?host=https://storage.googleapis.com&access_id=***&access_key=***' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';
     

   • Restore the mysql tenant from a Tencent Cloud Object Storage (COS) data backup path and a COS log archive path to the timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool.

     obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***,s3://oceanbase-test/backup/archive?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';
     

5. (Optional) After the meta tenant corresponding to the tenant to be restored is created, set the ha_high_thread_score parameter to specify the number of worker threads for the restore job to speed up the restore.

   1. Query the oceanbase.DBA_OB_TENANTS view to check whether the meta tenant is created.

      The syntax is as follows:

      SELECT * FROM oceanbase.DBA_OB_TENANTS;
      

      In the query results, tenant names starting with META$ in the TENANT_NAME column indicate meta tenants. Meta tenants in the TENANT_STATUS_NORMAL state are successfully created.

      For more information about the columns in the DBA_OB_TENANTS view, see DBA_OB_TENANTS.

   2. Specify the ha_high_thread_score parameter.

      The tenant-level ha_high_thread_score parameter specifies the current number of high-availability worker threads with a high priority. The default value is 0, and the value range is [0, 100]. We recommend that you set this parameter to 10.

      Here is an example:

      ALTER SYSTEM SET ha_high_thread_score =10 TENANT = mysql;
      

What to do next

• After you initiate a restore job, you can query the corresponding views to check the restore progress and results. For more information, see View the physical restore progress.

• If you restore a tenant from backup files of an earlier OceanBase Database version to a cluster of a later version, you must upgrade the restored tenant after the restore job is completed. For more information, see Upgrade a tenant after restore.

• Physical restore is associated with the Physical Standby Database solution. A physically restored tenant serves as a standby tenant. You can switch it to the PRIMARY role based on your business needs. For information about how to proceed to log replay from the source tenant, see Replay logs to a standby tenant. For information about how to switch a standby tenant to the PRIMARY role, see Convert a standby tenant into primary tenant.

References

For more information about restore, see Restore process.