RESTORE|V4.2.2| docs|Distributed Database

RESTORE

Last Updated：2025-12-02 02:50:56 Updated

Description

The ALTER SYSTEM RESTORE statement restores the data backed up for a tenant.

Limitations and considerations

Physical restore in OceanBase Database supports restore within the same cluster or between different clusters.
Before you use the ALTER SYSTEM RESTORE statement for physical restore, make sure that you have confirmed the version of the backup data to be restored. OceanBase Database allows you to restore low-version backup data to the same version or a later version, but does not support reverse restore between minor versions of the same version.
Before you use the ALTER SYSTEM RESTORE statement for physical restore, make sure that you have created the resources required for the tenant to be restored. For more information, see Prepare resources before restore.
A tenant restored by using the ALTER SYSTEM RESTORE statement is a standby tenant by default. To use it as a primary tenant, you need to execute the ALTER SYSTEM ACTIVATE STANDBY statement. For more information about the ALTER SYSTEM ACTIVATE STANDBY statement, see ACTIVATE STANDBY.

Privilege requirements

Only the root user of the sys tenant (root@sys) can 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}]

Parameters

Parameter	Description
dest_tenant_name	The name of the tenant to restore.
uri	The data backup path `backup_data_dest` and log archive path `log_archive_dest` specified when data is backed up. The value of this parameter contains at least one data backup path and one log archive path, with English commas (,) separating the paths. At present, OceanBase Database supports the following media as the destination for archiving data: Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), and Tencent Cloud Object Storage (COS).
TIME='timestamp' \| SCN=scn	The timestamp to which data is to be restored. Data at this timestamp is included in the restored data. When you specify the restore timestamp or SCN using the `TIME` or `SCN` keyword, you must use the `=` sign to connect the keyword and the value. If you do not specify the `UNTIL` clause, data is restored to the latest timestamp by default. The timestamp value must be in the `YYYY-MM-DD HH24:MI:SS.FF` format, accurate to nanoseconds. For more information about how to select the `timestamp` and `scn` values, see Physical restore parameters.
restore_option	Supports parameters such as `pool_list`, `locality`, `primary_zone`, `concurrency`, and `kms_encrypt`. These parameters are separated with `&`. When specifying `locality` and `primary_zone`, it is recommended that you maintain the same structure as the source tenant. If the structures are different, activating the restored tenant as the primary tenant may result in load balancing operations, affecting performance. `pool_list`: Required. The resource pools created for the new tenant to be restored. Multiple resource pools are separated with English commas (,). `locality`: Optional. The locality information of replica distribution for the new tenant. The information must match the zone information in the `pool_list` of the cluster where the new tenant is located. To maintain the same structure as the source tenant, it is recommended that the new tenant have the same number of F replicas as the source tenant. If you do not explicitly specify this parameter, an F replica is created for each zone in `zone_list` of `resource_pool` by default. Example: `locality='F@z1,F@z2,F@z3'`. `primary_zone`: Optional. The preferred position of the leader replica of the new tenant. The zone must match `pool_list` and `locality`, that is, it must meet requirements such as matching zone information and having at least two Paxos members in `primary_region`. To maintain the same structure as the source tenant, it is recommended that the new tenant have the same number of primary zones with the top priority as the source tenant. If you do not explicitly specify this parameter, the system randomly distributes the leader in the zones specified in `zone_list` of `locality`. Example: `primary_zone='z1'` `concurrency`: Optional. The concurrency for data restore. If you do not explicitly specify this parameter, the default value equals the `MAX_CPU` value assigned to the tenant. For example, if the system tenant assigns `MAX_CPU` of 16 to the tenant to be restored, the default value of `concurrency` is 16 when this parameter is not explicitly specified. Example: `concurrency=50` `kms_encrypt`: Optional. When set to `true`, it indicates that `kms_encrypt_info` specified before restore needs to be used during restore.
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	The key information for restoring an encrypted tenant. If transparent encryption is enabled for the source tenant, you must specify the key information for restoring the tenant. In this parameter: `backup_key_path`: the path where the key is backed up. `password`: the encryption password used when the key is backed up.
description	User-defined description. This parameter is optional.

Examples

Restore the mysql tenant to the specified timestamp 2022-06-01 00:00:00 from the NFS data backup path and NFS log archive path. Specify the resource pool as restore_pool, the replica Locality as F@z1,F@z2,F@z3, and the primary_zone as z1.
```
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 NFS log archive path. Specify the resource pool as restore_pool. Also, specify the concurrency concurrency of 50 for data restore.
```
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 NFS log archive path. Specify the resource pool as restore_pool.

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

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

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

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

obclient [oceanbase]> ALTER SYSTEM RESTORE mysql FROM 'cos://oceanbase-test-bucket/backup/data/?host=cos.ap-xxx.myqcloud.com&access_id=xxx&access_key=xxx&appid=xxx,cos://oceanbase-test-bucket/backup/archive/?host=cos.ap-xxx.myqcloud.com&access_id=xxx&access_key=xxx&appid=xxx' UNTIL TIME='2022-06-01 00:00:00' WITH 'pool_list=restore_pool';

Execute the RESTORE command on the target cluster to create and restore a standby tenant using the tenant's backup and archive data.

obclient [oceanbase]> ALTER SYSTEM RESTORE restore_oracle_tenant FROM 'file:///data/nfs/backup//ob_backup_oracle_tenant/archive,file:///data/nfs/backup//ob_backup_oracle_tenant/data' UNTIL TIME='2022-11-11 15:04:23.825558' with 'pool_list=small_pool_2&primary_zone=z1';