Restore a specified table

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

Procedure

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

2. Run the following command to disable log backup (optional):

   obclient> ALTER SYSTEM NOARCHIVELOG;
   

   Note

   Versions earlier than V2.2.7x do not support running restoration tasks on a cluster that has log backup enabled because restoration interrupts log backup. We recommend that you disable log backup before starting data restoration on versions earlier than V2.2.7x.

3. Create a resource unit.

   Example:

   obclient> CREATE RESOURCE UNIT box_16c96g max_cpu 16, max_memory 103079215104, max_iops 10240, max_disk_size 53687091200, max_session_num 64, MIN_CPU=16, MIN_MEMORY=103079215104, MIN_IOPS=10240;
   

4. Create a resource pool.

   Example:

   obclient> CREATE RESOURCE POOL restore_pool unit = 'box_16c96g', unit_num = 1, zone_list = ('z1','z2','z3');
   

5. Run the following command to configure encryption information:

   Note

   If the data is not encrypted or the original KMS is available, skip this step.

   obclient> SET @kms_encrypt_info = '<encryption string>';
   

   <encryption string> is the value of EXTERNAL_KMS_INFO. EXTERNAL_KMS_INFO is a tenant-specific configuration item.

6. Verify restoration configurations.

   Check whether restore_concurrency is 0. If yes, run the following command:

   obclient> ALTER SYSTEM SET restore_concurrency = 50;
   

7. Modify the restoration idle time. This step is optional.

   _restore_idle_time is 1 minute by default. The restoration procedure contains three idle periods, which are 3 minutes in total. To reduce the wait time during restoration performance testing, you can run the following command to shorten the idle time to 10s:

   obclient> ALTER SYSTEM SET _restore_idle_time = '10s';
   

8. Run the following command to set a restoration password as required:

   obclient> SET DECRYPTION IDENTIFIED BY 'password';
   

   A restoration password is required only when a password has been specified during backup. If the passwords for full backup and incremental backup are different, multiple passwords must be entered and separated with commas (,). Example:

   obclient> SET DECRYPTION IDENTIFIED BY 'password1', 'password2';
   

9. Run the following command to start the restoration 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	Specifies 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. Separate multiple table names with commas (,). To represent a single table: 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 (``). For example: crm`.`sales`, `crm`.`products
   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 of which the data is backed up.
   uri	The value of backup_dest specified during backup.
   recover_end_time	The restoration 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 restoration options, including backup_cluster_name, backup_cluster_id, pool_list, locality, 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. * locality (optional): the locality information of the tenant. * kms_encrypt (optional): specifies whether encryption information is required. When the value is true, the value of kms_encrypt_info specified in Step 5 is required for restoration.

   The following example restores the sales, products, and orders tables in the backup of tenant1 to new_tenant1, and 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';
   

10. You can use the oceanbase.CDB_OB_RESTORE_PROGRESS view to check the restoration progress.

    obclient> SELECT * FROM oceanbase.CDB_OB_RESTORE_PROGRESS;
    

11. After the restoration is completed, you can use the oceanbase.CDB_OB_RESTORE_HISTORY view to check the restoration result.

    obclient> SELECT * FROM oceanbase.CDB_OB_RESTORE_HISTORY;