RESTORE|V4.3.5| docs|Distributed Database

RESTORE

Last Updated：2025-11-14 07:33:32 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][&kms_encrypt={true | false}][&method={full | quick}]

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 include at least one data backup path and one log archive path, separated by commas (,). Currently, OceanBase Database supports the following archive destination media: NFS, Alibaba Cloud OSS, Azure Blob Storage (For OceanBase Database V4.3.5, supported starting from V4.3.5 BP3), AWS S3, and S3-compatible object storage such as Huawei OBS, Google GCS, and Tencent Cloud COS. For examples of uri paths, 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	Supports parameters such as pool_list, locality, primary_zone, concurrency, kms_encrypt, and method, separated by &. When specifying locality and primary_zone, it is recommended to keep the configuration as consistent as possible with the source tenant. If they differ, activating the tenant as the primary tenant after recovery may trigger load balancing operations, which could impact performance. pool_list: Required. Enter the resource pools created for the new tenant to be restored. Separate multiple resource pools with commas (,). locality: Optional. Specifies the locality information for the distribution of replicas for the new tenant; this must match the zone information of the pool_list in the new tenant’s cluster. To maintain consistency with the source tenant, it is recommended that the number of F replicas for the new tenant matches that of the source tenant. If not explicitly specified, the default is to assign one F replica to each zone in the resource pool’s zone_list. Example: `locality='F@z1,F@z2,F@z3'` primary_zone: Optional. Specifies the preferred location for the leader replica of the new tenant, which must match both the `pool_list` and `locality`. This means the zone information must be consistent, and there must be at least two Paxos members in the `primary_region`. To maintain consistency with the source tenant, it is recommended that the number of first-priority primary zones for the new tenant matches that of the source tenant. If not explicitly specified, the system will randomly distribute the leader among the zones in the locality’s zone_list. Example: `primary_zone='z1'` concurrency: Optional. Specifies the degree of concurrency for data recovery. If not explicitly specified, the default is the MAX_CPU allocated to the tenant. For example, if the system tenant allocates a MAX_CPU of 16 to the tenant being restored, then the value of `concurrency` is 16. Example: `concurrency=50` kms_encrypt: Optional. If set to `true`, it means that the `kms_encrypt_info` specified before recovery will be used during the recovery process. method: Optional. Specifies the recovery method (case-insensitive) with the following options: full: Full recovery. If not explicitly specified, the default is `full`. quick: Quick recovery. Example: `method=quick`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	Specifies the key backup information for an encrypted tenant. This is only required during recovery if the source tenant has transparent encryption configured. Details: `backup_key_path`: The backup path of the key. `password`: The encryption password set when backing up the key.
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, using the full restore method.
```
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, using the full restore method.
```
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, using the full restore method.

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 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, using the full restore method.

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 tenant mysql from the S3 backup path and S3 log archive path to the specified timestamp 2024-01-15 00:00:00, specifying 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';

s3_region indicates the region where the S3 bucket is located.

Restore the tenant mysql from the OBS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specifying the resource pool as restore_pool, using the full recovery method.

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 tenant mysql from the GCS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specifying the resource pool as restore_pool, using the full recovery method.

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 tenant mysql from the COS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specifying the resource pool as restore_pool, using the full recovery method.

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-***x.myqcloud.com&access_id=***&access_key=***' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';