RESTORE|V4.3.3| docs|Distributed Database

RESTORE

Last Updated：2025-11-27 02:38:06 Updated

Purpose

You can use the ALTER SYSTEM RESTORE statement to restore the data of a standby tenant.

Limitations and considerations

OceanBase Database supports a physical restore within a cluster or across clusters.
Before you use the ALTER SYSTEM RESTORE statement for a physical restore, you must confirm the version of the OceanBase database that hosts the backup data to be restored. For more information, see Preparations.
Before you use the ALTER SYSTEM RESTORE statement for a physical restore, you must make sure that resources required for the tenant to be restored have been created. For more information, see Preparations.
By default, a tenant restored by using the ALTER SYSTEM RESTORE statement is a standby tenant. To switch the standby tenant to the PRIMARY role to provide services, execute the ALTER SYSTEM ACTIVATE STANDBY statement. For more information about the ALTER SYSTEM ACTIVATE STANDBY statement, see ACTIVATE STANDBY.

Required privileges

You can execute the ALTER SYSTEM RESTORE statement only as the root user of the sys tenant (namely root@sys).

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&method=method_name

Parameters

Parameter	Description
dest_tenant_name	The name of the tenant to be restored.
uri	The data backup path and log archive path, which are respectively specified by `backup_data_dest` and `log_archive_dest` during backup. The value must contain at least one data backup path and one log archive path that are separated with a comma (,). OceanBase Database allows you to use Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), Amazon Simple Storage Service (S3), and other object storage services that are compatible with the S3 protocol, such as Huawei Object Storage Service (OBS) and Google Cloud Storage (GCS), as the media of archive destinations. For more information about how to specify `uri`, see SET LOG_ARCHIVE_DEST or SET DATA_BACKUP_DEST.
TIME='timestamp' \| SCN=scn	The inclusive timestamp or SCN up to which the table is restored. To restore to a specified `TIME` or `SCN` value, join the parameter name and value with an equal sign (`=`). By default, if you do not specify the `UNTIL` clause, the tenant is restored to the latest timestamp or SCN. The value of `timestamp` must be in the `YYYY-MM-DD HH24:MI:SS.FF` format, accurate to nanoseconds. For more information about how to specify a timestamp or SCN for restore, see Parameters related to physical restore.
restore_option	The restore options, including `pool_list`, `locality`, `primary_zone`, `concurrency`, and `method`. Separate the options with ampersands (`&`). `pool_list` is required and specifies the resource pool of the tenant. Multiple resource pools must be separated with commas (,). `locality` is optional and specifies the locality of replicas of the restored tenant, which must match the zone information of `pool_list` of the cluster where the tenant is located. This option is optional. By default, if this parameter is not specified, a full-featured replica is set for each zone in `zone_list` of the resource pools specified by `resource_pool`. Example: `locality='F@z1,F@z2,F@z3'`. `primary_zone` is optional and specifies the preferred position of the leader of the new tenant, which must match the settings of `pool_list` and `locality`. In other words, the value of this parameter must match the zone information and meet the constraint that the primary region has at least two Paxos members. If this parameter is not specified, the system randomly distributes leaders in the zones specified by `zone_list` in `locality`. Example: `primary_zone='z1'`. `concurrency` is optional and specifies the concurrency for data restore. If `concurrency` is not specified, the `MAX_CPU` value assigned to the tenant is used as the default value. Example: `concurrency=50`. `method` is optional and specifies the data restore method. The value is case-insensitive. Valid values: `full`: indicates a full restore. It is the default value. `quick`: indicates a quick restore. Example: `method=quick`.
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	The key backup information of the tenant. You need to specify the key backup information for a tenant restore only when the tenant is configured with transparent encryption. The parameters are described as follows: `backup_key_path`: the backup path of the key. `password`: the encryption password specified during key backup.
description	Optional. The description.

Examples

Restore the mysql tenant from a data backup path and log archive path in NFS to the timestamp 2022-06-01 00:00:00. Set the resource pool to restore_pool, replica locality to F@z1,F@z2,F@z3, and primary_zone to 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 from a data backup path and log archive path in NFS to the latest archive checkpoint. Set the resource pool to restore_pool and concurrency to 50.

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 from a data backup path and log archive path in NFS to the specified SCN. Set the resource pool to 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 from a data backup path and log archive path in OSS to the timestamp 2022-06-01 00:00:00. Set the resource pool to 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 from a data backup path and log archive path in COS to the timestamp 2022-06-01 00:00:00. Set the resource pool to 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';

Here, appid indicates the AppID of your Tencent Cloud account.

Execute the RESTORE statement on the target cluster to create and restore a standby tenant from backups and archives of the tenant.

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

Restore the mysql tenant from a data backup path and log archive path in S3 to the timestamp 2024-01-15 00:00:00. Set the resource pool to restore_pool.

obclient [oceanbase]> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com&access_id=xxx&access_key=xxx&s3_region=xxx, s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=xxx&access_key=xxx&s3_region=xxx' UNTIL TIME='2024-01-15 00:00:00' WITH 'pool_list=restore_pool';

Here, s3_region indicates the region where the S3 bucket is deployed.