Perform full restoration

This topic describes how to restore data by using commands. OceanBase Database supports tenant-level point-in-time full restoration.

Restore data by using commands

You can run the corresponding commands to restore full backup data from the backup destination, incorporate incremental backup data to the full backup data, and then apply the backup transaction logs.

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 <dest_tenant_name> FROM <source_tenan_tname> at 'uri' UNTIL 'timestamp' WITH 'restore_option';
   

   The following table describes the parameters.

   Parameter	Description
   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.
   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 data 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, 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.

   Sample commands:

   • NFS

     obclient> ALTER SYSTEM RESTORE restored_trade FROM trade at 'file:///data/nfs/backup' until '2020-05-21 09:39:54.071670' with 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool;
     

   • OSS

     obclient> ALTER SYSTEM RESTORE restored_trade FROM trade at 'oss://antsys-oceanbasebackup/backup_rd/?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';
     

   • Tencent Cloud COS

     obclient> ALTER SYSTEM RESTORE restored_trade from trade at 'cos://backup-1304745170/backup_rd/20210127?host=cos.ap-nanjing.myqcloud.com&access_id=xxx&access_key=xxx&appid=xxx' until ' 2020-03-23 08:59:45' with 'backup_cluster_name=ob20daily.backup&backup_cluster_id=1&pool_list=restore_pool';
     

10. Run the following commands to view the restoration progress:

    • View the restoration progress of the root table:

      obclient> SELECT svr_ip,role, is_restore, COUNT(*) FROM __all_root_table AS a, (SELECT value FROM __all_restore_info WHERE name='tenant_id') AS b WHERE a.tenant_id=b.value GROUP BY role, is_restore, svr_ip ORDER BY svr_ip, is_restore;
      

      Valid values of is_restore:

      • 0: indicates a normal replica.

      • 1: indicates a logically restored replica.

      • 2: indicates a physically restored replica that requires the restoration of the baseline data.

      • 3: indicates a physically restored replica that requires the restoration of minor compaction data.

      • 4: indicates a physically restored replica that requires the restoration of clogs.

      • 5: indicates a physically restored replica that requires a minor compaction.

      • 6: indicates a physically restored replica that waits for the minor compactions of all replicas to complete.

      • 7: indicates a physically restored replica that contains a member list.

      Valid values of role:

      • 1: indicates a leader.

      • 2: indicates a follower.

      • 3: indicates a leader in restoration.

    • View the restoration progress of the meta table:

      obclient> SELECT svr_ip,role, is_restore, COUNT(*) FROM __all_virtual_meta_table AS a, (SELECT value FROM __all_restore_info WHERE name='tenant_id') AS b WHERE a.tenant_id=b.value GROUP BY role, is_restore, svr_ip ORDER BY svr_ip, is_restore;
      

      or

      obclient> SELECT svr_ip ,is_restore, COUNT(*) FROM __all_virtual_partition_store_info WHERE tenant_id>1002 group by svr_ip,is_restore order by svr_ip, is_restore;
      

11. Run the following commands to view the restoration results:

    obclient> SELECT * FROM __all_restore_info;
    

    obclient> SELECT * FROM __all_restore_history;