RESTORE|V4.3.0| docs|Distributed Database

RESTORE

Last Updated：2024-04-19 08:42:50 Updated

Purpose

You can use the ALTER SYSTEM RESTORE statement to restore backup data for a tenant.

Limitations and considerations

OceanBase Database supports physical restore within a cluster or across clusters.
Before you execute this statement, you must check the version of the OceanBase database that hosts the backup data to be restored. OceanBase Database supports only the restore of backup data to OceanBase Database of the same or a later version.
Before you execute this statement, make sure that you have created the resources for the tenant to be restored. For more information, see Preparations.
By default, a tenant restored by the ALTER SYSTEM RESTORE statement is a standby tenant. If you want to activate it as the primary tenant to provide services, you must execute the ALTER SYSTEM ACTIVATE STANDBY statement. For more information about the ALTER SYSTEM ACTIVATE STANDBY statement, see ACTIVATE STANDBY.

Required privileges

Only the root user in the sys tenant (root@sys) can execute this 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

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` specified 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), and Amazon Simple Storage Service (S3) 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 system change number (SCN) up to which data 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, data is restored up to the latest timestamp. A timestamp must be in the `YYYY-MM-DD HH24:MI:SS.FF` format, accurate to nanoseconds. For more information about how to specify `timestamp` and `scn`, see Parameters related to physical restore.
restore_option	The restore options, including `pool_list`, `locality`, `primary_zone`, and `concurrency`. Separate the options with ampersands (`&`). `pool_list`: required. The resource pool of the tenant. Multiple resource pools must be separated with commas (,). `locality`: optional. The locality of replicas of the new tenant, which must match the zone information of `pool_list` of the cluster to which the new tenant belongs. By default, if this parameter is not specified, a full-featured replica is set for each zone in `zone_list` of the resource pool. Example: `locality='F@z1,F@z2,F@z3'` `primary_zone`: optional. 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 the leader to zones specified by `zone_list` of `locality`. Example: `primary_zone='z1'` `concurrency`: optional. The restore concurrency. If `concurrency` is not specified, the `MAX_CPU` value assigned to the tenant is used as the default value. Example: `concurrency=50`
WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'	The key backup information of the encrypted tenant. You need to specify the key backup information for a tenant restore only when the tenant is configured with transparent encryption. `backup_key_path`: the backup path of the key. `password`: the password that you set when you backed up the key.
description	The description. This parameter is optional.

Examples

Restore the mysql tenant from an NFS data backup path and an NFS log archive path to the timestamp 2022-06-1 00:00:00. Set the resource pool to restore_pool, 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 an NFS data backup path and an NFS log archive path up to the latest archive timestamp. Set the resource pool to restore_pool and restore 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 an NFS data backup path and an NFS log archive path 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 an OSS data backup path and an OSS log archive path to the timestamp 2022-06-1 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 COS data backup path and a COS log archive path to the timestamp 2022-06-1 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 the 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 an S3 data backup path and an S3 log archive path 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.