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

Perform a physical restore

Last Updated：2025-12-03 09:13:04 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 tenant data from full backup and incremental backup.

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

The restore process first recovers the required macroblocks from full backup and incremental backup to the target tenant based on the restore 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 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 back up data. If the passwords for full backup and incremental backup are different, enter both passwords separated by commas (with the password for full backup placed in front of the password for incremental backup).

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 encryption parameters.

Note

If you can access the original key management service during restore or the data is not encrypted, 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 the 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 is as follows:
- 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 archive point
```
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; UNTIL [TIME|SCN], which indicates the restore endpoint. The restore is performed until the specified time or SCN, and the restore includes the specified time or SCN.
- When you restore to TIME or SCN, separate the parameter value with =.
- The RESTORE_OPTION parameter allows you to specify the pool_list, locality, primary_zone, concurrency, and kms_encrypt parameters. Parameters are separated with &. We recommend that you configure the new tenant to be homogeneous with the source tenant in terms of the locality and primary_zone parameters. If the new tenant is heterogeneous with the source tenant, activating the new tenant as the primary tenant after the restore may cause load balancing operations, which affects the performance of the system.
  - pool_list: the resource pool created for the new tenant. Resource pools are separated with commas (,).
  - Locality: the locality information of the replicas of the new tenant. It must match the zone information of the pool_list parameter of the target cluster. If you want to configure the new tenant to be homogeneous with the source tenant, we recommend that you set the number of full-featured replicas (F) 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 pool_list and locality parameters. If you want to configure the new tenant to be homogeneous with the source tenant, we recommend that you set the number of primary zones (z) of the first priority 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 (MAX_CPU), takes effect. For example, in this topic, the system tenant allocates MAX_CPU equal to 16 to the restored tenant.
  - kms_encrypt: if set to true, the system will use the encryption information specified in the kms_encrypt_info parameter during restore.
  For more information about these parameters, see Parameters for physical restore.
- WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': the secret key backup information of the tenant to be restored. You need to specify this parameter only if transparent encryption is configured for the source tenant.
  - 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 the secret key in Prepare for data backup.
You can select TIME or SCN in the statement and the parameters based on the preceding descriptions. For more information, see Parameters for physical restore.

Here is an example of the restore statement:
Alibaba Cloud OSS

NFS

Tencent Cloud COS

AWS S3

Compatible object storage services that support the S3 protocol
- Restore the mysql tenant to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as restore_pool, and restore the secret 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 to the specified scn 1658285759724047000, specify the resource pool as 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 to the latest archive point. Specify the resource pool as restore_pool and set the degree of concurrency 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 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.
  
  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 to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as restore_pool, set the replica locality to F@z1,F@z2,F@z3, and specify the 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';
Apart from the url parameter, the usage of this statement is the same as that for Alibaba Cloud OSS.
- Restore the mysql tenant to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as restore_pool, and restore the secret key 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 to the latest archive point. Specify the resource pool as restore_pool and set the degree of concurrency 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';
Apart from the url parameter, the usage of this statement is the same as that for NFS.

Restore the mysql tenant to the specified timestamp 2020-06-01 00:00:00 and specify the resource pool as restore_pool.
ALTER SYSTEM RESTORE mysql FROM 'cos://oceanbase-test-appid/backup?host=cos.ap-****.myqcloud.com&access_id=***&access_key=***&appid=***, cos://oceanbase-test-appid/backup/archive?host=cos.ap-****.myqcloud.com&access_id=***&access_key=***&appid=***' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool';
Apart from the url parameter, the usage of this statement is the same as that for NFS.

Restore the mysql tenant to the specified timestamp 2024-01-15 00:00:00 and specify the resource pool as 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';
Apart from the url parameter, the usage of this statement is the same as that for Alibaba Cloud OSS.
- Restore the mysql tenant to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as 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 to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as 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';
(Optional) If you want to speed up the restore after the new tenant is created, you can set the ha_high_thread_score parameter to the maximum number of concurrent threads for the restore task.
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 DBA_OB_TENANTS view, see DBA_OB_TENANTS.
2. Set the ha_high_thread_score parameter.
  
  The ha_high_thread_score tenant-level parameter specifies the current number of working threads for high-priority threads in 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, see ha_high_thread_score.
  
  Here is a sample statement:
```
ALTER SYSTEM SET ha_high_thread_score =10 TENANT = mysql;
```

Next steps

After a restore task is initiated, you can view the restore progress and result through the console. For more information, see View the restore progress.
After a restore task is completed, if you restore data from a lower version to a higher version, you must upgrade the restored tenant. For more information, see Upgrade a tenant.
The physical restore process is the same as that in a physical standby database. After a physical restore, the tenant database becomes a standby tenant database. The standby tenant database can provide standby services or can be activated as a primary tenant database. For more information about how to segmentally archive logs from the source tenant and play them back to a standby tenant, see Playback logs to a standby tenant. For more information about how to activate a standby tenant as a primary tenant, see Activate a standby tenant.

References

For more information about restores, see Restore architecture.