Perform physical restore

This topic describes how to perform tenant-level physical restore by using an SQL statement. OceanBase Database supports tenant-level full restore based on points in time.

After you execute the restore statement, the system restores the required macroblock data from the corresponding full and incremental backups at the backup destination to the destination tenant and then pulls and replays the transaction logs.

Prerequisites

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

Procedure

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

2. (Optional) Execute the following statement to set a restore password for the backup.

   You need to set a restore password for the backup only if a password is added during data backup.

   obclient [(none)]> SET DECRYPTION IDENTIFIED BY 'password';
   

   In the statement, password must be replaced with the password added during data backup. If the password for full backup is different from the password for incremental backup, you must enter the password for full before the password for incremental backup and separate them with a comma.

   For example:

   obclient [(none)]> SET DECRYPTION IDENTIFIED BY '******';
   
   obclient [(none)]> SET DECRYPTION IDENTIFIED BY '******','******';
   

3. Execute the following statement to start a restore job:

   • Restore to a specified timestamp

     obclient [(none)]> ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL TIME=timestamp WITH restore_option [DESCRIPTION description];
     

   • Restore to a specified SCN

     obclient [(none)]> ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL SCN=scn WITH restore_option [DESCRIPTION description];
     

   • Restore to the latest checkpoint

     obclient [(none)]> ALTER SYSTEM RESTORE dest_tenant_name FROM uri WITH restore_option [DESCRIPTION description];
     

   • uri specifies the data backup path and log archive path, and UNTIL [TIME|SCN] specifies the end checkpoint for restore.

   • To restore to a specified TIME or SCN, you must join the parameter name and value with an equal sign (=).

   • In restore_option, you can specify pool_list, locality, primary_zone, and kms_encrypt. pool_list is required and the others are optional.

   For more information about TIME, SCN, and other parameters, see Parameters related to physical restore.

   Here are some examples:

   • NFS

     • Restore the mysql tenant from the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the specified timestamp 2020-06-01 00:00:00, and set the resource pool to restore_pool.

       obclient [(none)]> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool';
       

     • Restore the mysql tenant from the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the latest archive checkpoint, and set the resource pool to restore_pool.

       obclient [(none)]> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' WITH 'pool_list=restore_pool';
       

     • Restore the mysql tenant from the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the specified SCN 1658285759724047000, and set the resource pool to restore_pool.

       obclient [(none)]> 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 the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the specified timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool and the locality of replicas to F@z1,F@z2,F@z3.

       obclient [(none)]> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool&locality=F@z1,F@z2,F@z3';
       

     • Restore the mysql tenant from the data backup path file:///data/nfs/backup/data and log archive path file:///data/nfs/backup/archive to the specified timestamp 2020-06-01 00:00:00. Set the resource pool to restore_pool, the locality of replicas to F@z1,F@z2,F@z3, and primary_zone to z1.

       obclient [(none)]> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool&locality=F@z1,F@z2,F@z3&primary_zone=z1';
       

   • Object Storage Service (OSS)

     Except for the url parameter, the settings of other parameters are consistent with those when NFS is used.

     • Restore the mysql tenant from the OSS backup path oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx and OSS log archive path oss://oceanbase-test-bucket/backup/archive/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx to the specified timestamp 2020-06-01 00:00:00, and set the resource pool to restore_pool.

       obclient [(none)]> 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='2020-06-01 00:00:00' WITH 'pool_list=restore_pool';
       

4. (Optional) After you create the meta tenant corresponding to the tenant to be restored, set the ha_low_thread_score parameter to specify the backend scheduling weight for the restore task to speed up the restore.

   1. Query the oceanbase.DBA_OB_TENANTS view to check whether the meta tenant is created.

      obclient [(none)]> SELECT * FROM oceanbase.DBA_OB_TENANTS;
      

      In the query results, tenant names starting with META$ in the TENANT_NAME column indicate meta tenants. Meta tenants in the TENANT_STATUS_NORMAL state are successfully created.

   2. Specify the ha_high_thread_score parameter.

      The tenant-level ha_high_thread_score parameter specifies the current number of high-availability worker threads with a high priority. Default value: 0. Value range: [0, 100]. We recommend that you set this parameter to 10. For more information about the ha_high_thread_score parameter, see ha_high_thread_score.

      Sample statement:

      obclient [(none)]> ALTER SYSTEM SET ha_high_thread_score =10 TENANT = mysql;
      

What to do next

After you initiate a restore job, you can query the corresponding views to check the restore progress and results. For more information, see Query the restore progress.

Physical restore is associated with Physical Standby Database. A physically restored tenant serves as a standby tenant. You can change its role to PRIMARY based on your business needs. For more information about how to proceed to log replay from the source tenant, see Replay logs to a standby tenant. For more information about how to change the role of a standby tenant to PRIMARY, see Change the role of a standby tenant to PRIMARY.