Perform physical restore

This topic describes how to perform physical restore at the tenant level by using commands. OceanBase Database supports full restore and fast restore at the tenant level. For more information about the differences between full restore and fast restore, see Overview of physical backup and restore.

OceanBase Database allows you to restore a tenant in the same cluster or in a different cluster.

The restore process first retrieves the required macroblocks from the full backup and incremental backup based on the commands you enter and restores them to the target tenant. It then synchronously pulls and replays the transaction logs.

Prerequisites

Before you perform physical recovery, make sure that the preparation for data recovery is completed. For more information, see Preparation for data recovery.

Considerations

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

• If you use quick recovery when you execute the recovery command, after the quick recovery is completed, note the following:

  • The recovered tenant does not support manual major compactions, data backups, or switchover/failover to become the primary tenant. It can only exist as a standby tenant.

  • During the use of the recovered tenant, ensure that all OBServer nodes hosting the tenant's units can access the data from the source tenant's data backup. If the source tenant's data backup uses NFS as the backup medium, it must not be in the UMOUNT state.

Procedure

Procedure

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

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

   You need to set the restore password for the backup only if you added a password when creating the backup.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   In the statement, replace password with the password added when creating the backup. If the passwords are set for the full backup and incremental backup, you need to input multiple passwords, and separate the passwords with commas (,). The password of the full backup comes before the password of the incremental backup.

   Example 1: The passwords set for the full backup and incremental backup are the same.

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

   Example 2: The passwords set for the full backup and incremental backup are different.

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

3. Execute the following statement to set the encryption information.

   Note

   If the data is not encrypted or the original KMS service is accessible for restore, you can skip this step.

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

   In the statement, <encrypted string> indicates the value of EXTERNAL_KMS_INFO, which is a tenant-level parameter.

   Note

   The external_kms_info parameter stores key management information. For more information, see external_kms_info.

4. Execute the following statement to start the restore task.

   Full restore

   Quick restore

   Syntax:

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

     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];
     

   Syntax:

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

     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];
     

   The description of some parameters in the statements is as follows:

   • TIME='timestamp' or SCN=scn: the endpoint for restore. The data is restored to the specified timestamp or SCN, and the timestamp or SCN is included. For more information about how to specify the timestamp or SCN, see Physical restore parameters.

   • uri: the backup data path backup_data_dest and the log archive path log_archive_dest specified when creating the backup. At least one data backup path and one log archive path must be specified, and they are separated by commas (,).

   • pool_list: the resource pool specified for the user. Multiple resource pools are separated by commas (,).

   • locality and primary_zone: the locality information and preferred leader replica location for the new tenant replica. These parameters are optional. For more information, see ALTER SYSTEM - RESTORE.

     Notice

     Columnstore replicas cannot be restored. When specifying the locality for the new tenant, you cannot specify columnstore replicas. For more information about columnstore replicas, see Columnstore replicas.

   • concurrency: the concurrency for data restore. This parameter is optional. The default value is the MAX_CPU value of the units allocated to the tenant.

   • kms_encrypt: indicates whether to use the kms_encrypt_info parameter specified before restore. This parameter is optional.

   • method: the restore method. The parameter is not case-sensitive. Valid values:

     • full: full restore. If this parameter is not specified, the default value is full.
     • quick: quick restore.

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': the encrypted key information for restore. You need to specify this option only when the source tenant is encrypted.

     • backup_key_path: the backup path of the key.

     • password: the password used to encrypt the key when backing up.

     For more information about key backup, see Backup the key in Prepare for backup.

   For more information about the parameters of physical restore, see ALTER SYSTEM - RESTORE.

   Here are some restore examples:

   Alibaba Cloud OSS

   NFS

   AWS S3

   Object Storage Service (OSS) compatible with the S3 protocol

   • Restore the mysql tenant from the backup path and log archive path in Alibaba Cloud OSS to the specified timestamp 2020-06-01 00:00:00. Specify the resource pool as restore_pool, perform a full restore, and restore the key information 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 the backup data path and log archive path to the specified SCN 1658285759724047000. Specify the resource pool as restore_pool, and perform a full restore.

     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 to the latest log archive path from the backup data path and log archive path. Specify the resource pool as restore_pool, and set the concurrency to 50. Perform a full restore.

     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 the data backup path and log archive path to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as restore_pool, and set the replica locality to F@z1,F@z2,F@z3 for a full restore.

     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 the data backup path and log archive path to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as restore_pool, and set the replica locality to F@z1,F@z2,F@z3 for a full restore. Additionally, specify primary_zone as 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 usage of other parameters is the same as in OSS.

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

     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 the data backup path and log archive path to the latest archive point, specify the resource pool as restore_pool, and set the concurrency concurrency to 50 for a full restore.

     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 usage of other parameters is the same as in NFS.

   Restore the mysql tenant from the S3 backup path and S3 log archive path to the specified timestamp 2024-01-15 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

   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 usage of other parameters is the same as in OSS.

   • Restore the mysql tenant from the OBS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

     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 the GCS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

     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 COS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

     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 configure the ha_high_thread_score parameter to set the number of concurrent threads for the restore task to accelerate the restore.

   1. Query the oceanbase.DBA_OB_TENANTS view to check whether the Meta tenant corresponding to the tenant to be restored is created.

      The statement is as follows:

      obclient> SELECT * FROM oceanbase.DBA_OB_TENANTS;
      

      In the query result, the tenant whose TENANT_NAME column starts with META$ is the Meta tenant. If the status of the Meta tenant is NORMAL, the Meta tenant is created.

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

   2. Set the ha_high_thread_score parameter.

      The tenant-level ha_high_thread_score parameter specifies the current number of threads for high availability high-priority threads. 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.

      The statement is as follows:

      obclient> ALTER SYSTEM SET ha_high_thread_score =10 TENANT = mysql;
      

What to do next

• After you initiate a restore task, you can view the restore progress and results in the view. For more information, see View the restore progress.

• After the restore task is completed, if you restored data from a lower version to a higher version cluster, you need to upgrade the restored tenant. For more information, see Upgrade a tenant after the restore task is completed.

• The physical restore process is the same as the physical standby database. After a physical restore, the tenant becomes a standby tenant. This standby tenant can provide services as a standby tenant or be converted to a primary tenant. For more information about how to replay logs from the source tenant to the standby tenant, see Replay logs from the source tenant to the standby tenant. For more information about how to convert the standby tenant to a primary tenant, see Convert the standby tenant to a primary tenant.

References

For more information about the restore process, see Restore process.