RESTORE|V4.3.3|OceanBase Database| docs|Distributed Database

RESTORE

Last Updated：2026-07-25 06:58:08 Updated

Description

The ALTER SYSTEM RESTORE statement restores data from a tenant backup.

Limitations and considerations

Physical restore is supported within the same cluster or between clusters.
Before you use the ALTER SYSTEM RESTORE statement to restore a backup, make sure that the version of the backup data is compatible with the version of OceanBase Database. For more information, see Prepare for the restore.
Before you use the ALTER SYSTEM RESTORE statement to restore a tenant, make sure that the resources required for the tenant have been created. For more information, see Prepare for the restore.
By default, the tenant restored using the ALTER SYSTEM RESTORE statement is a standby tenant. To switch it to the primary tenant, you need to execute the ALTER SYSTEM ACTIVATE STANDBY statement. For more information, see ACTIVATE STANDBY.

Privilege requirement

Only the root user (root@sys) of the sys tenant has the privilege to execute the ALTER SYSTEM RESTORE statement.

Syntax

ALTER SYSTEM
   RESTORE dest_tenant_name
   FROM uri
   [ UNTIL {TIME='timestamp' | SCN=scn} ] 
   WITH 'restore_option' 
   [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] 
   [DESCRIPTION [=] 'description'];

restore_option:
 pool_list=pool_name[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}][&method={full | quick}]

Parameter explanation

Parameter	Description
dest_tenant_name	The name of the tenant to restore to.
uri	The data backup destination `backup_data_dest` and log archive destination `log_archive_dest` specified during backup. The value of this parameter contains at least one data backup destination and one log archive destination, separated by commas (,). At present, OceanBase Database supports archiving data to NFS, Alibaba Cloud OSS, AWS S3, and object storage services compatible with the S3 protocol, such as Huawei Cloud OBS, Google Cloud GCS, and Tencent Cloud COS. For more information about the `uri` format, see SET LOG_ARCHIVE_DEST or SET DATA_BACKUP_DEST.
TIME='timestamp' \| SCN=scn	The end point of the restore. The restore is performed to this point and includes the data at this point. If you specify the `UNTIL` clause, you must connect the specified value with `='. If you do not specify the`UNTIL`clause, the restore is performed to the latest point by default. timestamp is supported only in the`YYYY-MM-DD HH24:MI:SS.FF`format and supports nanosecond precision. For more information about how to select the`timestamp`and`scn` values, see Physical restore parameters.
restore_option	Supports the `pool_list`, `locality`, `primary_zone`, `concurrency`, `kms_encrypt`, and `method` parameters. The parameters are separated with `&`. We recommend that you configure the new tenant with the same or similar hardware configuration as the source tenant when you specify `locality` and `primary_zone`. Otherwise, load balancing operations may be triggered to balance the performance of the primary tenant after it is activated. `pool_list`: the resource pools created for the new tenant. Multiple resource pools are separated with commas (,). This parameter is mandatory. `locality`: the locality information where the replicas of the new tenant are distributed. It must match the zone information of the pool_list of the cluster where the new tenant resides. We recommend that you retain the same number of full-featured replicas as the source tenant when you specify `locality`. If you do not specify this parameter, the system will set one full-featured replica for each zone based on the `zone_list` of the `resource_pool`. Example: `locality='F@z1,F@z2,F@z3'`. `primary_zone`: the preferred zone where the leader replica of the new tenant is located. It must match the zone information of `pool_list` and `locality`, namely, the zone information must be consistent and at least two Paxos members must be located in the `primary_region`. We recommend that you retain the same number of primary zones as the source tenant when you specify `primary_zone`. If you do not specify this parameter, the system will randomly distribute the leader replicas across the zones specified in `locality`. Example: `primary_zone='z1'` `concurrency`: the concurrency degree of data restore. If you do not specify this parameter, it will be set to the value of `MAX_CPU` allocated to the tenant. For example, if the `MAX_CPU` allocated to the tenant is 16, the value of `concurrency` will be 16. Example: `concurrency=50` `kms_encrypt`: specifies whether to use the key material specified before the restore. If set to `true`, the `kms_encrypt_info` specified earlier is required. `method`: the data restore method, which is case-insensitive. It has the following values: `full`: specifies full restore. If you do not specify this parameter, the default value `full` takes effect. `quick`: specifies quick restore. Example: `method=quick`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	The secret key backup information of the tenant to be restored. If transparent encryption is configured for the source tenant, you must specify the tenant's secret key backup information during restore. In this parameter: `backup_key_path`: the backup path of the secret key. `password`: the encryption password set when backing up the secret key.
description	The user-specified description. It is an optional parameter.

Examples

Restore the mysql tenant to the specified timestamp 2022-06-01 00:00:00 from the NFS data backup path and the NFS log archive path. Specify the resource pool as restore_pool, the locality of the replica as F@z1,F@z2,F@z3, and the primary_zone as z1. This restores all data.
```
obclient [oceanbase]> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' UNTIL TIME='2022-06-01 00:00:00' WITH 'pool_list=restore_pool&locality=F@z1,F@z2,F@z3&primary_zone=z1';
```
Restore the mysql tenant to the latest archive point, from the NFS data backup path and the NFS log archive path. Specify the resource pool as restore_pool and the concurrency of data restoration as 50. This restores all data.
```
obclient [oceanbase]> 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 from the NFS data backup path and the NFS log archive path. Specify the resource pool as restore_pool. This restores data in quick restore mode.

obclient [oceanbase]> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' UNTIL SCN=1658285759724047000 WITH 'pool_list=restore_pool&method=quick';

Restore the mysql tenant to the specified timestamp 2022-06-01 00:00:00 from the OSS backup path and the OSS log archive path. Specify the resource pool as restore_pool. This restores all data.

obclient [oceanbase]> 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='2022-06-01 00:00:00' WITH 'pool_list=restore_pool';

Restore the mysql tenant to the specified timestamp 2024-01-15 00:00:00 from the S3 backup path. Specify the resource pool as restore_pool.

obclient [oceanbase]> 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';

The s3_region parameter indicates the region where the S3 bucket is located.

Restore the mysql tenant to the specified timestamp 2023-06-01 00:00:00 from the OBS backup path and the log archive path. Specify the resource pool as restore_pool, and restore all data.

 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 2023-06-01 00:00:00 from the GCS backup path and the log archive path. 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';

Restore the mysql tenant to the specified timestamp 2023-06-01 00:00:00 from the COS backup path and the log archive path. Specify the resource pool as restore_pool.

obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test/backup/data?host=cos.ap-***x.myqcloud.com&access_id=***&access_key=***,s3://oceanbase-test/backup/archive?host=cos.ap-***.myqcloud.com&access_id=***&access_key=***' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';

OceanBase

Customer Stories

Documentation