Restore a specified table

OceanBase Database allows you to physically restore a specified table in a tenant.

Prerequisites

The preparations for restore are complete. For more information, see Preparations for restore.

Procedure

1. Log on to the sys tenant as the root user.

2. Execute the following statement to start a restore task:

   obclient> ALTER SYSTEM RESTORE  <table_name_list> FOR <dest_tenant_name>  FROM <source_tenant_name> AT 'uri'  UNTIL 'timestamp' WITH 'restore_option' ;
   

   The following table describes the parameters.

   Parameter	Description
   table_name_list	The tables to be restored in a tenant. Each table name in the list must contain the database (MySQL tenant) or user (Oracle tenant) to which the table belongs. Separate multiple table names with commas (,). Specify each table in the format of database_name.table_name or user_name.table_name. Example: crm.sales,crm.products,hr.employees Note A database or table name that contains special characters must be enclosed in grave accents (``). Example: `c&rm`.`s#ales`.
   dest_tenant_name	The name of the destination tenant to which the data is to be restored.
   source_tenant_name	The name of the source tenant for which the data is backed up.
   uri	The value of backup_dest specified during backup.
   timestamp	The restore timestamp, which must be later than or equal to START_TIME in the CDB_OB_BACKUP_SET_DETAILS view of the earliest data backup, and earlier than or equal to MAX_NEXT_TIME in the CDB_OB_BACKUP_ARCHIVELOG_SUMMARY view of the log backup.
   restore_options	The restore options, including backup_cluster_name, backup_cluster_id, pool_list, locality, primary_zone, and kms_encrypt. backup_cluster_name: required. The name of the source cluster. backup_cluster_id: required. Same as the value of cluster_id of the source cluster. 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 where the new tenant is located. By default, the locality of the source tenant is used. If the value specified for this parameter does not match the zone information of pool_list of the cluster where the new tenant is located, the system resets the locality to the default value. That is, the system sets an F replica for each zone based on zone_list of resource_pool. For example, the locality of the source tenant is "F@z1,F@z2,F@z3", and the zone distribution of the cluster where the new tenant is located is "z1,z2,z3,z4", the locality of the source tenant is used as the locality of the new tenant. If the locality of the source tenant is "F@z1,F@z2,F@z3,F@z4,F@z5", and the zone distribution of the cluster where the new tenant is located is "z1,z2,z3", the locality of the new tenant covers all zones of this cluster. That is, the locality of the new tenant is "F@z1,F@z2,F@z3". primary_zone: optional. The preferred position of the leader of the new tenant, which must match pool_list and locality. That is, it must match the zone information and meet the constraint that primary_region must have at least two Paxos members. By default, the primary zone of the source tenant is used. If the value specified for this parameter does not match pool_list or locality of the cluster where the new tenant is located, the system resets primary_zone to the default value. That is, leaders are randomly distributed to the zones based on zone_list of locality. For example, if the locality of the source tenant is "F@z1,F@z2,F@z3", the primary zone is z1, and the zone distribution of the cluster where the new tenant is located is "z1,z2,z3,z4", the primary zone of the new tenant is also z1. If the locality of the source tenant is "F@z1,F@z2,F@z3,F@z4,F@z5", the primary zone is z1, and the zone distribution of the cluster where the new tenant is located is "z1,z2,z3", the locality of the new tenant is "F@z1,F@z2,F@z3" and the primary zone is "z1,z2,z3". kms_encrypt: optional. Specifies whether encryption information is required. If the value is true, the value of kms_encrypt_info specified in restore preparations is required for restore.

   The following sample statement restores the sales, products, and orders tables in a backup of tenant1 to new_tenant1. The version of the restored data is 2021-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';