Perform physical restore

This topic describes how to perform tenant-level physical restore by using SQL statements. OceanBase Database supports tenant-level full restore and quick restore based on points in time. For more information about the differences between full restore and quick restore as well as the limitations of quick restore, see Restore process.

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 before restore are complete. For more information, see Preparations.

Considerations

• In the current version, tenant-level parameters are backed up along with the data. However, these backed-up tenant-level parameters are provided only for user reference and will not be directly restored to the tenant during physical recovery. For more information about backup parameters, see Backup parameters.

• When executing a recovery command using the fast recovery method, please note the following after the fast recovery is complete:

  • The recovered tenant does not support manual merge operations, data backup, or Switchover/Failover to become the primary cluster. It can only exist as a standby cluster.

  • During use, all OBServer nodes hosting the Units of the recovered tenant must be able to access the data in the source tenant’s backup. If the backup medium used for the source tenant is NFS, it must not be in the UMOUNT state.

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

4. Execute the following statement to start a restore job:

   Full restore

   Quick restore

   Here are some examples:

   • Restore to a specified timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL TIME='timestamp' WITH 'pool_list=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}][&method=full]' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   • Restore to a specified system change number (SCN)

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL SCN=scn WITH 'pool_list=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}][&method=full]' [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 'pool_list=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}][&method=full]' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   Here are some examples:

   • Restore to a specified timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL TIME='timestamp' WITH 'pool_list=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}]&method=quick' [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 'pool_list=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}]&method=quick' [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 'pool_list=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}]&method=quick' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   Some of the parameters in the statement are explained as follows:

   • TIME='timestamp', SCN=scn: Specifies the recovery endpoint. The system will restore up to and including the specified point. For more information on how to choose between TIME and SCN in the statement, see Physical restore parameters.

   • uri: Specifies the data backup path backup_data_dest and log archive path log_archive_dest set during backup. This value must include at least one data backup path and one log archive path, separated by commas (,).

   • pool_list: Specifies the user’s resource pools. Multiple resource pools are separated by commas (,).

   • locality, primary_zone, concurrency, kms_encrypt: Optional. For detailed explanations of these parameters, see ALTER SYSTEM - RESTORE.

     Notice

     Physical restore does not support columnstore replicas. When specifying the locality for a new tenant, C replicas are not allowed. For more information about C replicas and their usage, see Columnstore replicas.

   • method: Specifies the data recovery method (case-insensitive). The options are:

   • full: Full restore. If not explicitly specified, the default is full.

   • quick: Quick restore.

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': Specifies the backup information for the encrypted tenant's key. This only needs to be specified during restore if the source tenant is configured with Transparent Data Encryption (TDE).

     • backup_key_path: The backup path of the key.

     • password: The encryption password set when backing up the key.

     For operations related to key backup, see Backup key in Preparation before data backup.

   For detailed explanations of each parameter in physical restore, see ALTER SYSTEM - RESTORE.

   Here are some examples:

   OSS

   NFS

   Amazon 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&method=full';
     

   • 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 NFS.

   Restore the mysql tenant from an S3 data backup path and an S3 log archive path to the timestamp 2024-01-15 00:00:00. Set the resource pool to restore_pool.

   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='2024-01-15 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 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 the specified data backup path and log archive path in COS to the timestamp 2020-06-01 00:00:00, and specify the resource pool as 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, you can increase the number of concurrent threads of the restore task by setting the ha_high_thread_score parameter.

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

      Here is an example:

      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 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, 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 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.