Purpose
You can use this statement to restore the data of tenants.
Note
- You can restore only data blocks, rather than CLOGs, from a snapshot backup at the tenant level.
- Restoration from a snapshot backup at the tenant level can only be initiated by the root user in the sys tenant.
Syntax
alter_system_restore_stmt:
ALTER SYSTEM restore_action;
restore_action:
/* Restore data by tenant */
RESTORE <dest_tenant_name> FROM <source_tenan_tname> AT 'uri' UNTIL 'timestamp'
WITH 'restore_option';
/* Add the path to which the data is restored */
ADD RESTORE SOURCE 'uri';
/* Clear the data path added */
CLEAR RESTORE SOURCE;
/* Restore data from a snapshot backup at the tenant level */
RESTORE <dest_tenant_name> FROM <source_tenant_name> AT <backup_file_dest_list>
WITH <restore_option>;
/* Restore data by table */
RESTORE <table_name_list> FOR <dest_tenant_name> FROM <source_tenant_name> AT 'uri' UNTIL
'timestamp' WITH 'restore_option';
/* Restore the required macroblock data from the corresponding full and incremental backups at the backup destination to the standby tenant and then pulls and replays the transaction logs */
RESTORE tenant_name FROM restore_path UNTIL restore_checkpoint
WITH 'pool_list=<pool_name>&primary_zone=<zone_name>';
Parameters
| Parameter | Description |
|---|---|
| table_name_list | The tables to be restored in a tenant. The table names in the list must contain the database (MySQL tenant) or user (Oracle tenant) to which the table belongs. |
| dest_tenant_name | The name of the tenant to which the data is restored. |
| source_tenant | The source cluster for which the data is backed up. |
| uri | The value of backup_dest specified during backup, or the path to which the data is restored. |
| backup_file_dest_list | A list of locations where the files restored are located. Separate multiple locations with commas (,). Currently, these locations must point to destinations of the same type. |
| timestamp | The restoration timestamp, which must be later than or equal to START_TIME in the CDB_OB_BACKUP_SET_DETAILS view of the earliest baseline backup, and earlier than or equal to MAX_NEXT_TIME in the CDB_OB_BACKUP_ARCHIVELOG_SUMMARY view of the log backup. |
| restore_option | The restoration options. The supported options are:
|
| tenant_name | The name of the standby tenant. |
| restore_path | The backup destination. |
| restore_checkpoint | The point in time to restore to. |
| pool_list | The list of resource pools. |
| primary_zone | The primary cluster. |
Examples
Restore the data of a tenant.
obclient> ALTER SYSTEM RESTORE restored_trade FROM trade AT 'oss://antsys-oceanbasebackup/backup_rd/20200323?host=cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access_id=xxx&access_key=xxx' UNTIL ' 2020-03-23 08:59:45' WITH 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool'; obclient> ALTER SYSTEM RESTORE restored_trade FROM trade AT 'file:///data/nfs/physical_backup_test/20200520' UNTIL '2020-05-21 09:39:54.071670' WITH 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool';For more information about how to restore the data of a tenant, see "Perform full restoration" in OceanBase Database Administrator Guide.
Restore the data of specified tables in a tenant.
The following example restores the data of
sales,products, andorderstables in the backup oftenant1tonew_tenant1, and the version of the restored data is2021-02-28 08:59:45.obclient> ALTER SYSTEM RESTORE `crm`.`sales`, `crm`.`products`, `hr`.`employees` FOR new_tenant1 FROM tenant1 AT 'oss://antsys-oceanbasebackup/backup_rd/?host=cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access_id=xxx&access_key=xxx' UNTIL '2021-02-28 08:59:45' WITH 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool';For more information about how to restore the data of specified tables in a tenant, see "Restore a specified table" in OceanBase Database Administrator Guide.
Restore data from a tenant-level snapshot backup.
obclient> ALTER SYSTEM RESTORE new_tenant1 FROM tenant1 AT 'file:///ob_backup/' WITH 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool';For more information about how to take a tenant-level snapshot backup and restore the data, see "Snapshot backup and restoration for tenants" in OceanBase Database Administrator Guide.
Execute the
RESTOREstatement on the target cluster to create and restore the standby tenant from backups and archives of the tenant.obclient> ALTER SYSTEM RESTORE restore_oracle_tenant FROM 'file:///data/1/zbf271370/backup//ob_backup_oracle_tenant/archive,file:///data/1/zbf271370/backup//ob_backup_oracle_tenant/data' UNTIL TIME='2022-11-11 15:04:23.825558' with 'pool_list=small_pool_2&primary_zone=z1'; Query OK, 0 rows affected