Perform a physical restore|V4.2.0| docs|Distributed Database

Perform a physical restore

Last Updated：2025-12-03 11:18:03 Updated

This topic describes how to perform a physical restore at the tenant level by using commands. Currently, OceanBase Database allows full point-in-time restore of tenants from full backups and incremental backups.

OceanBase Database allows restore within the same cluster or to a different cluster.

The restore process first recovers the required macroblocks from the full backup and incremental backup to the target tenant based on the command entered by the user. Then, it synchronously pulls and replays transaction logs.

Prerequisites

Before you perform a physical restore, make sure that you have completed the pre-recovery preparations. For more information, see Prepare for a restore.

Procedure

Log in to the sys tenant of the cluster as the root user.
(Optional) Execute the following statements to set the restore password for the backup.

If you add a password when you back up the data, you must set a restore password.
```
SET DECRYPTION IDENTIFIED BY 'password';
```
In this statement, you need to replace password with the password you added when you backed up the data. If the passwords for full backup and incremental backup are different, enter both passwords separated by commas. The password for full backup should be entered first.

Here is an example where the passwords for full backup and incremental backup are the same:
```
SET DECRYPTION IDENTIFIED BY '******';
```
Here is an example where the passwords for full backup and incremental backup are different:
```
SET DECRYPTION IDENTIFIED BY '******','******';
```
Execute the following statement to set the encryption information.

Note

If the original key management service is accessible without encryption or when restoring, skip this step.
```
obclient [(none)]> SET @kms_encrypt_info = '<encrypted string>';
```
In this statement, <encrypted string> is the value of the EXTERNAL_KMS_INFO tenant-level parameter, which is a string that contains some encrypted key management information.

Note

The external_kms_info parameter is used to store some key management information. For more information, see external_kms_info.
Execute the following statement to start the restore task.

The statement can be one of the following:
- 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];
```
In these statements:
- dest_tenant_name: the name of the new tenant to restore to.
- You need to specify the uri parameter, which consists of the data backup path and the log archive path, separately.
- UNTIL [TIME|SCN] indicates the restore end point, which is the checkpoint where the restore is to end. The restore is performed up to and including the specified checkpoint. When you restore to a TIME or SCN, you must use = to connect the specified value. For more information about how to select a TIME or SCN, see Physical restore parameters.
- restore_option supports pool_list, locality, primary_zone, concurrency, and kms_encrypt. These parameters are separated by &. When you specify locality and primary_zone, we recommend that you configure the new tenant to be homogeneous with the source tenant. If the new tenant is heterogeneous from the source tenant, activating the new tenant as the primary tenant after the restore may cause load balancing operations, which affects the performance.
  - pool_list: the resource pool created for the new tenant. Multiple resource pools are separated by commas (,).
  - Locality: the locality information of the replicas of the new tenant. It must match the zone information of the pool_list parameter in the new tenant. When you configure the new tenant to be homogeneous with the source tenant, we recommend that you set the number of full-featured replicas (F-replicas) of the new tenant to be the same as that of the source tenant.
  - primary_zone: the preferred zone of the leader replica of the new tenant. It must match the zone information of the pool_list and locality parameters. When you configure the new tenant to be homogeneous with the source tenant, we recommend that you set the number of primary zones of the new tenant to be the same as that of the source tenant.
  - concurrency: the degree of concurrency of data restore. If you do not specify this parameter, the default value, which is equal to the maximum number of CPU cores allocated to the tenant, is used. For example, in this topic, the system tenant has allocated 16 maximum CPU cores to the tenant to be restored.
  - kms_encrypt: specifies whether to use the encryption information specified earlier (kms_encrypt_info). If you specify this parameter, you must specify the kms_encrypt_info parameter. It is an optional parameter, and its value is true or false.
  For more information about the parameters, see Physical restore parameters.
- WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': the secret key backup information of the tenant to be restored. You must specify this parameter if transparent encryption is configured for the source tenant. Otherwise, the data of the tenant cannot be restored.
  - backup_key_path: the backup path of the secret key.
  - password: the encryption password set when you back up the secret key.
  For more information about how to back up the secret key, see Backup secret key in Prepare for data backup.
Here are some restore examples:
- NFS
  - Restore the mysql tenant to the specified timestamp 2020-06-01 00:00:00 in the specified resource pool restore_pool from the data backup path file:///data/nfs/backup/data and the log archive path file:///data/nfs/backup/archive, and restore the secret key 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 to the latest checkpoint in the specified resource pool restore_pool from the data backup path file:///data/nfs/backup/data and the log archive path file:///data/nfs/backup/archive, and set the degree of 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 to the specified SCN 1658285759724047000 in the specified resource pool restore_pool from the data backup path file:///data/nfs/backup/data and the log archive path file:///data/nfs/backup/archive.
```
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 to the specified timestamp 2020-06-01 00:00:00 in the specified resource pool restore_pool from the data backup path file:///data/nfs/backup/data and the log archive path file:///data/nfs/backup/archive, and set the replica locality to 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 to the specified timestamp 2020-06-01 00:00:00 in the specified resource pool restore_pool, set the replica locality to F@z1,F@z2,F@z3, and set the primary zone to 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
  
  Apart from the url parameter, the parameters are the same as those in the NFS example.
  - Restore the mysql tenant to the specified timestamp 2020-06-01 00:00:00 in the specified resource pool restore_pool from the data backup path oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx and the log archive path oss://oceanbase-test-bucket/backup/archive/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx.
```
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';
```
(Optional) After the Meta tenant corresponding to the tenant to be restored is created, you can increase the number of threads for the restore task by setting the ha_high_thread_score parameter.
1. Query whether the Meta tenant corresponding to the tenant to be restored has been created by using the oceanbase.DBA_OB_TENANTS view.
  
  The sample statement is as follows:
```
SELECT * FROM oceanbase.DBA_OB_TENANTS;
```
  In the query result, a tenant whose name starts with META$ in the TENANT_NAME column is the Meta tenant. If the status of the Meta tenant is TENANT_STATUS_NORMAL, the Meta tenant has been created.
  
  For more information about the columns of the DBA_OB_TENANTS view, see DBA_OB_TENANTS.
2. Set the ha_high_thread_score parameter.
  
  The tenant-level parameter ha_high_thread_score specifies the number of high-priority threads for high availability. The default value is 0, and the value range is [0, 100]. We recommend that you set the value 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;
```

Next steps

After you initiate a restore task, you can check the progress and result of the restore by using the view. For more information, see Check the restore progress.
If you restore data from a backup of a lower-version cluster to a higher-version cluster, upgrade the tenant to the corresponding version after the restore is completed. For more information, see Upgrade a tenant.
After the restore is successful, the physical restore process is the same as that in a physical standby database. The physically restored tenant is a standby tenant. You can use the tenant as a standby tenant to provide related services or convert it into a primary tenant to provide services. For more information, see Segmented restore for a standby tenant and Convert a standby tenant into a primary tenant.

References

For more information about restores, see Restore architecture.