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

Purpose

The ALTER SYSTEM RESTORE statement is used to restore data from a tenant backup.

Limitations and considerations

Physical restore in OceanBase Database supports restoring within the same cluster and across different clusters.
Before using the ALTER SYSTEM RESTORE statement for physical restore, please confirm the version of the backup data to be restored. For information about the limitations of restoring to a specific version, see Preparations before restore.
Before using the ALTER SYSTEM RESTORE statement for physical restore, please ensure that the resources required for the tenant to be restored have been created. For information about how to create the required resources for the tenant to be restored, see Preparations before restore.
After using the ALTER SYSTEM RESTORE statement to restore a tenant, the tenant is initially a standby tenant. If you want to activate it as the primary tenant to provide services, 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}][&method={full | quick}]

Parameters

Parameter	Description
dest_tenant_name	The name of the tenant to be restored.
uri	The data backup path `backup_data_dest` and log archive path `log_archive_dest` set during backup. This value must contain 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, AWS S3, and object storage compatible with the S3 protocol, 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 endpoint of the restore. The restore will include this endpoint. When specifying `TIME` or `SCN`, you must use `=` to connect to the specified value. If you do not specify the `UNTIL` clause, the restore will default to the latest endpoint. The `timestamp` parameter only supports the `YYYY-MM-DD HH24:MI:SS.FF` format, accurate to the nanosecond. For more information about how to select the `timestamp` and `scn` values during restore, see Physical restore parameters.
restore_option	Supports parameters such as `pool_list`, `locality`, `primary_zone`, `concurrency`, `kms_encrypt`, and `method`, which are separated by `&`. When specifying `locality` and `primary_zone`, it is recommended to keep them consistent with the source tenant. If they are inconsistent, load balancing operations may occur after the tenant is restored and activated as the primary tenant, which may affect performance. `pool_list`: Required. Specifies the resource pools to be created for the tenant to be restored. Multiple resource pools are separated by commas (,). `locality`: Optional. Specifies the Locality information for the new tenant's replicas. It must match the Zone information in the `pool_list` of the cluster where the new tenant is located. When keeping consistency with the source tenant, it is recommended to have the same number of F replicas as the source tenant. If not explicitly specified, the system will default to setting one F replica in each Zone based on the `zone_list` in the `resource_pool`. Example: `locality='F@z1,F@z2,F@z3'`. `primary_zone`: Optional. Specifies the preferred location for the leader replicas of the new tenant. It must match the `pool_list` and `locality`, meaning the Zone information must match, and there must be at least two Paxos members in the `primary_region`. When keeping consistency with the source tenant, it is recommended to have the same number of primary zones as the source tenant. If not explicitly specified, the system will randomly distribute the leader replicas across the Zones specified in the `zone_list` of the `locality`. Example: `primary_zone='z1'` `concurrency`: Optional. Specifies the concurrency level for data restore. If not explicitly specified, the default value is equal to the `MAX_CPU` value assigned to the tenant. For example, if the `MAX_CPU` value assigned to the system tenant is 16, the `concurrency` value will be 16. Example: `concurrency=50` `kms_encrypt`: Optional. If set to `true`, it indicates that the `kms_encrypt_info` specified during restore must be used. `method`: Optional. Specifies the data restore method. It is case-insensitive. Valid values: `full`: Full restore. If not explicitly specified, the default value is `full`. `quick`: Quick restore. Example: `method=quick`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	Specifies the key backup information for the encrypted tenant. This parameter is required only when the source tenant is configured with transparent encryption. The parameters are as follows: `backup_key_path`: The backup path of the key. `password`: The encryption password set when the key was backed up.
description	User-specified description information. Optional.

Examples

Restore the mysql tenant from the NFS data backup path and NFS log archive path to the specified timestamp 2022-06-01 00:00:00, specify the resource pool as restore_pool, the replica locality as F@z1,F@z2,F@z3, and the primary_zone as z1, and perform a full restore.
```
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 the NFS data backup path and NFS log archive path to the latest archive point, specify the resource pool as restore_pool, and set the data restore concurrency concurrency to 50, and perform a full 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 from the NFS data backup path and NFS log archive path to the specified SCN, specify the resource pool as restore_pool, and perform a fast restore.

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 the OSS backup path and OSS log archive path to the specified timestamp 2022-06-01 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

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

s3_region specifies the region where the S3 bucket is located.

Restore the mysql tenant from the OBS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

 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 from the GCS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

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 from the COS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

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

OceanBase

Customer Stories

Documentation

RESTORE