Perform physical restore

This topic describes how to perform physical restore at the tenant level by using commands. At present, OceanBase Database supports full restore and quick restore for tenants. The difference between full restore and quick restore and the limitations on quick restore are described in Restore process.

In OceanBase Database, restore can be performed within the same cluster or between different clusters.

The restore process retrieves the macroblocks that are required based on the user command from the corresponding full backup and incremental backup on the backup destination to the target tenant, and synchronously pulls and replays the transaction logs.

Prerequisites

Before you perform physical restore, make sure that you have completed the pre-recovery preparations. For more information, see Prepare for restore.

Considerations

After you perform a quick restore, pay attention to the following points:

• The restored tenant does not support manual major compactions, data backup, or switchover/failover to become the primary tenant. It can only exist as a standby tenant.
• During the restore period, make sure that the OBServer nodes where all units of the restored tenant are located can access the data backup of the source tenant. If the source tenant data backup uses the NFS backup media, the NFS must not be in the UMOUNT state.

Procedure

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

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

   You only need to set a password for restoring backups when you add a password during data backup.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   In the command, replace password with the password specified during the backup. If different passwords are specified for full backup and incremental backup, specify the passwords in the order of full backup password, incremental backup password, separated by a comma (,).

   An example where the passwords for full backup and incremental backup settings are the same is as follows:

   SET DECRYPTION IDENTIFIED BY '******';
   

   An example where the passwords for full backup and incremental backup settings are different is as follows:

   SET DECRYPTION IDENTIFIED BY '******','******';
   

3. Execute the following statement to set the encrypted information.

   Note

   If the original key management service can be accessed without encryption or after recovery, skip this step.

   obclient [(none)]> SET @kms_encrypt_info = '<encrypted string>';
   

   Here, <encrypted string> is the value of EXTERNAL_KMS_INFO, which is a tenant-level parameter.

   Note

   external_kms_info is used to store some key management information. For more information about this parameter, see external_kms_info.

4. Execute the following statement to start the restore task.

   Full restore

   Quick restore

   The statement is as follows:

   • Restore to a specified timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL TIME='timestamp' WITH 'pool_name=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}][&method=full]' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   • Restore to a specified SCN

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL SCN=scn WITH 'pool_name=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}][&method=full]' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   • Restore to the latest timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri WITH 'pool_name=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}][&method=full]' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   The statement is as follows:

   • Restore to a specified timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL TIME='timestamp' WITH 'pool_name=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}]&method=quick' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   • Restore to a specified SCN

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL SCN=scn WITH 'pool_name=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}]&method=quick' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   • Restore to the latest timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri WITH 'pool_list=pool_list[&locality=locality][&primary_zone=zone_name][&concurrency=int_num][&kms_encrypt={true | false}]&method=quick' [WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password'] [DESCRIPTION description];
     

   The following table describes the parameters in the statement:

   • TIME='timestamp' and SCN=scn: specify the endpoint to which the database is to be restored, namely, the point in time or the SCN to which the database is to be restored, and include this point in time or SCN. For more information about the parameters TIME and SCN, see Physical restore parameters.

   • uri: specifies the data backup path backup_data_dest and log archive path log_archive_dest for backup. This value must contain at least a data backup path and a log archive path, with English commas (,) used to separate the paths.

   • pool_list: Enter the user's resource pool. Separate multiple resource pools with commas (,).

   • locality, primary_zone, concurrency, and kms_encrypt: Optional. For more information about these parameters, see ALTER SYSTEM - RESTORE.

     Notice

     Physical restore does not support columnstore replicas. Therefore, you cannot specify C replicas when you set the locality for a new tenant. For more information about C replicas, see Columnstore replicas.

   • method: specifies the data restore method, which is case-insensitive. Valid values:

     • full: Full restore. If you do not specify a value, the default value is full.
     • quick: Quick restore.

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': specifies the key backup information for an encrypted tenant. You must specify the key backup information only when the source tenant uses transparent encryption and you want to restore data from the encrypted tenant.

     • backup_key_path: the backup path of the key.

     • password: the encryption password that you set when backing up the key.

     For more information about how to back up keys, see Backup Key in Prepare for backup.

   For more information about the parameters in physical restore, see ALTER SYSTEM - RESTORE.

   Here is an example of how to restore data:

   Alibaba Cloud OSS

   NFS

   AWS S3

   Compatible with object storage that supports the S3 protocol

   • Restore the mysql tenant to the specified timestamp of 2020-06-01 00:00:00 from the OSS backup path and log archiving path, using the restore_pool resource pool, with a full restore, and also restore the key information of the source tenant.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool' WITH KEY FROM 'oss://oceanbase-test-bucket/data_backup_dest/key?host=***.aliyun-inc.com&access_id=***&access_key=***' ENCRYPTED BY '******';
     

   • Restore the mysql tenant to the specified SCN 1658285759724047000, using the specified resource pool restore_pool, and perform a full restore from the data backup path and log archive path.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' UNTIL SCN=1658285759724047000 WITH 'pool_list=restore_pool&method=full';
     

   • Restore the mysql tenant from the data backup path and log archiving path to the current latest archive point. Specify the resource pool as restore_pool and the concurrency as concurrency with the value of 50. Perform a full restore.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' WITH 'pool_list=restore_pool&concurrency=50';
     

   • Restore the mysql tenant to the specified timestamp 2020-06-01 00:00:00, using the specified resource pool restore_pool, and the replica locality F@z1,F@z2,F@z3. Perform a full restore.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool&locality=F@z1,F@z2,F@z3';
     

   • Restore the mysql tenant to the specified timestamp of 2020-06-01 00:00:00, using the specified resource pool of restore_pool, the replica Locality of F@z1,F@z2,F@z3, and the primary zone of z1. Perform a full restore.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'oss://oceanbase-test-bucket/backup/data/?host=***.aliyun-inc.com&access_id=***&access_key=***,oss://oceanbase-test-bucket/backup/archive/?host=***.aliyun-inc.com&access_id=***&access_key=***' UNTIL TIME='2020-06-01 00:00:00' WITH 'pool_list=restore_pool&locality=F@z1,F@z2,F@z3&primary_zone=z1';
     

   Except for the url parameter, the usage of other parameters is the same as that in OSS.

   • Restore the mysql tenant to the specified timestamp 2020-06-01 00:00:00, from the specified data backup path and log archive path. Specify the resource pool as restore_pool. Perform a full restore and restore the key information of the source tenant.

     obclient> 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' WITH KEY FROM 'file:///data_backup_dest/key' ENCRYPTED BY '******';
     

   • Restore the mysql tenant from the data backup path and log archiving path to the current latest archive point. Specify the resource pool as restore_pool and the concurrency as concurrency with the value of 50. Perform a full restore.

     obclient> ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' WITH 'pool_list=restore_pool&concurrency=50';
     

   Except for the url parameter, the usage of other parameters is the same as that of NFS.

   Restore the mysql tenant to the specified timestamp 2024-01-15 00:00:00 from the specified S3 backup path and S3 log archive path, using the restore_pool resource pool, and perform a full restore.

   ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***, s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com&access_id=***&access_key=***&s3_region=***' UNTIL TIME='2024-01-15 00:00:00' WITH 'pool_list=restore_pool';
   

   Except for the url parameter, the usage of other parameters is the same as that in OSS.

   • Restore the mysql tenant to the specified timestamp of 2023-06-01 00:00:00, using the specified resource pool of restore_pool, and perform a full restore from the backup path of OBS and the log archive path of OBS.

     obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test-bucket/backup/data/?host=obs.***.myhuaweicloud.com&access_id=***&access_key=***,s3://oceanbase-test-bucket/backup/archive/?host=obs.***.myhuaweicloud.com&access_id=***&access_key=***' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';
     

   • Restore the mysql tenant to the specified timestamp of 2023-06-01 00:00:00 from the GCS backup path and log archiving path, using the restore_pool resource pool, and perform a full restore.

     obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test-bucket/backup/data/?host=https://storage.googleapis.com&access_id=***&access_key=***,s3://oceanbase-test-bucket/backup/archive/?host=https://storage.googleapis.com&access_id=***&access_key=***' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';
     

   • Restore the mysql tenant to the specified timestamp of 2023-06-01 00:00:00 from the COS backup path and log archive path, using the restore_pool resource pool, and perform a full restore.

     obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test/backup/data?host=cos.ap-****.myqcloud.com&access_id=***&access_key=***,s3://oceanbase-test/backup/archive?host=cos.ap-****.myqcloud.com&access_id=***&access_key=***' UNTIL TIME='2023-06-01 00:00:00' WITH 'pool_list=restore_pool';
     

5. (Optional) After the Meta tenant corresponding to the tenant to be recovered is successfully created, you can set the ha_high_thread_score parameter to specify the number of concurrent threads for the recovery task.

   1. Use the oceanbase.DBA_OB_TENANTS view to check whether the Meta tenant for the tenant to be restored has been created.

      The statement is as follows:

      SELECT * FROM oceanbase.DBA_OB_TENANTS;
      

      In the query result, a tenant whose name starts with META$ in the TENANT_NAME column is the Meta tenant. If the status of the Meta tenant is TENANT_STATUS_NORMAL, it indicates that the Meta tenant is successfully created.

      For more information about columns in the view, see DBA_OB_TENANTS.

   2. Set the parameter ha_high_thread_score.

      The tenant-level parameter ha_high_thread_score specifies the current number of worker threads for high-priority high availability threads. The default value is 0, and the value range is [0, 100]. We recommend that you set it to 10. For more information about the ha_high_thread_score parameter, see ha_high_thread_score.

      Here is an example:

      ALTER SYSTEM SET ha_high_thread_score =10 TENANT = mysql;
      

Next steps

• After you initiate a restore task, you can view the restore progress and results in the console. For more information, see View the restore progress.

• After a restore task is completed, if you restore data from an earlier backup to a later version cluster, you also need to upgrade the restored tenant. For more information, see Upgrade the tenant after the restore is completed.

• The physical restore process is the same as that for a physical standby database. After physical restore is completed, the tenant is in the standby state. You can use the tenant as a standby tenant to provide related services or switch the tenant to a primary tenant to provide services. To learn how to use a tenant as a standby tenant for log archiving and log replay to the source tenant, see Replay logs in segments for a standby tenant. To switch a standby tenant to a primary tenant, see Switch a standby tenant to a primary tenant.

References

For more information about restore, see Overview.