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 restore statement, 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 for restore are completed. For more information, see Preparations.

Procedure

1. Log on 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 the password for incremental backup, you must enter the password for full backup before the password 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 start a restore job:

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

     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, you must join the parameter name and value with an equal sign (=).

   • The restore_option parameter provides the following options: pool_list, locality, primary_zone, and concurrency, which are separated 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: the name of the resource pool created for the tenant to be restored. Multiple resource pools must be separated with commas (,).

     • Locality: 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: 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: the degree of parallelism (DOP) 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 key information for encrypting the tenant. This is only required when the source tenant is configured with transparent encryption and the key backup information needs to be specified during restore.

     • backup_key_path: the backup path for the key.

     • password: the encryption password specified during 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:

   • NFS

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

       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 the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the latest archive checkpoint, use the resource pool restore_pool, and set the restore concurrency (concurrency) to 50.

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

     • Restore the mysql tenant from the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the specified SCN 1658285759724047000, and use the resource pool restore_pool.

       ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' UNTIL SCN=1658285759724047000 WITH 'pool_list=restore_pool';
       

     • Restore the mysql tenant from the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the specified timestamp 2020-06-01 00:00:00. Use the resource pool restore_pool and specify the locality of replicas as F@z1,F@z2,F@z3.

       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&locality=F@z1,F@z2,F@z3';
       

     • Restore the mysql tenant from the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the specified timestamp 2020-06-01 00:00:00. Use the resource pool restore_pool and specify the locality of replicas as F@z1,F@z2,F@z3 and primary_zone as z1.

       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&locality=F@z1,F@z2,F@z3&primary_zone=z1';
       

   • OSS

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

     • Restore the mysql tenant from the OSS backup path oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx and OSS log archive path oss://oceanbase-test-bucket/backup/archive/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx to the specified timestamp 2020-06-01 00:00:00, and use the resource pool restore_pool.

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

   • COS

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

     Restore the mysql tenant from the COS backup path cos://oceanbase-test-appid/backup?host=cos.ap-xxxx.myqcloud.com&access_id=xxx&access_key=xxx&appid=xxx and COS log archive path cos://oceanbase-test-appid/backup/archive?host=cos.ap-xxxx.myqcloud.com&access_id=xxx&access_key=xxx&appid=xxx to the specified timestamp 2020-06-01 00:00:00, and use the resource pool restore_pool.

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

4. (Optional) After you create the meta tenant corresponding to the tenant to be restored, set the ha_high_thread_score parameter to specify the backend scheduling weight 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:

      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 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. For more information about the ha_high_thread_score parameter, see ha_high_thread_score.

      Here is a sample statement:

      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 Query 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, after the restore job is completed, you must upgrade the restored tenant. For more information, see Upgrade a tenant after restore.

• Physical restore is associated with the Physical Standby Database feature. A physically restored tenant serves as a standby tenant. You can convert it role to a primary tenant 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 convert a standby tenant into primary tenant, see Convert a standby tenant into primary tenant.

References

For more information about restore, see Restore architecture.