Perform physical restore

This topic describes how to perform tenant-level physical restore by using commands. Currently, OceanBase Database supports full restore and quick restore at the tenant level based on time points. For more information about the differences between full restore and quick restore, see Physical backup and restore overview.

Restores in OceanBase Database can be performed within the same cluster or across different clusters.

The restore process first restores the required macroblock data from the corresponding full backups and incremental backups to the target tenant based on the user's input command, and simultaneously pulls and replays transaction logs.

Prerequisites

Before performing physical restore, ensure that all pre-restoration preparations are complete. For specific operations, see Preparations before restore.

Considerations

• The current version backs up tenant-level parameters during data backup. However, the backed-up tenant-level parameters are only for configuration reference and are not directly restored to the tenant during physical restore. For detailed information about backing up parameters, see Back up parameters.

• When executing the restore command, if the quick restore method is used, note the following after quick restore is completed:

  • The restored tenant cannot manually initiate major compactions, cannot perform data backups, and cannot serve as the primary database through switchover/failover. It can only exist as a standby database.

  • During the use of the restored tenant, ensure that the OBServer node hosting all units of the tenant has access to the data in the source tenant's backup. If the backup medium used for the source tenant's backup is NFS, it must not be in an 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 the backup.

   You only need to set a restore password if one was added during data backup.

   SET DECRYPTION IDENTIFIED BY 'password';
   

   Replace password with the password added during backup. If the passwords set for full backup and incremental backup are different, you need to enter multiple passwords, separated by commas (the full backup password comes first, followed by the incremental backup passwords).

   Example where the passwords for full and incremental backups are the same:

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

   Example where the passwords for full and incremental backups are different:

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

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

   Note

   If the data is not encrypted or the original Key Management Service can be accessed during restore, skip this step.

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

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

   Note

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

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

   Full restore

   Quick restore

   The statements are as follows:

   • Restore to a specified timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL TIME='timestamp' WITH 'pool_list=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_list=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_list=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 statements are as follows:

   • Restore to a specified timestamp

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL TIME='timestamp' 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];
     

   • Restore to a specified SCN

     ALTER SYSTEM RESTORE dest_tenant_name FROM uri UNTIL SCN=scn 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];
     

   • 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];
     

   Some parameters in the statement are described as follows:

   • TIME='timestamp', SCN=scn: Specify the end point of restoration. Restore to this point, including it. For detailed information on selecting TIME or SCN, see Physical restore related parameters.

   • uri: Specify the data backup path data_backup_dest and log archive path log_archive_dest set during backup. This value must include at least one data backup path and one log archive path, separated by commas (,).

     • If a backup target path was specified using the TO syntax during data backup (for example, backing up to a specified directory A), then the data backup path in uri should be the backup target path specified during backup (A), not the default path configured by the system parameter data_backup_dest. The format remains A,log_archive_dest.
     • If the tenant has performed an archive backup with the DELETE INPUT parameter, the format is A,log_archive_dest,backup_archive_dest.

   • pool_list: Enter the user's resource pool. Multiple resource pools are separated by commas (,).

   • locality, primary_zone: Specify the locality information for the new tenant replica distribution and the preferred location for the leader replica. Optional. For detailed information about these parameters, see ALTER SYSTEM - RESTORE.

     Notice

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

   • concurrency: Specifies the concurrency level for data restore. Optional. By default, it equals the MAX_CPU value of the units allocated to the tenant.

   • kms_encrypt: Optional. When set to true, the kms_encrypt_info specified before restore is required during restore.

   • method: Specifies the method for data restore. Case-insensitive. Valid values:

     • full: Full restore. If not explicitly specified, the default is full.
     • quick: Quick restore.

   • WITH KEY FROM 'backup_key_path' ENCRYPTED BY 'password': Specifies the key backup information for encrypted tenants. Specify the key backup information only when transparent encryption is configured for the source tenant.

     • backup_key_path: the backup path of the key.

     • password: the encryption password set during key backup.

     For operations related to key backup, see Back up keys in Prepare before backup.

   For detailed descriptions of each parameter in physical restore, see ALTER SYSTEM - RESTORE.

   Examples:

   Alibaba Cloud OSS

   NFS

   AWS S3

   Object storage compatible with the S3 protocol

   • Restore tenant mysql from the OSS backup path and log archive path to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as restore_pool, perform a full restore, and restore the source tenant's key information.

     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 tenant mysql from the data backup path and log archive path to the specified SCN 1658285759724047000, specify the resource pool as restore_pool, and 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 SCN=1658285759724047000 WITH 'pool_list=restore_pool&method=full';
     

   • Restore tenant mysql from the data backup path and log archive path to the current latest archive point, specify the resource pool as restore_pool, set the restore concurrency concurrency to 50, and 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 tenant mysql from the data backup path and log archive path to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as restore_pool, set the replica locality to F@z1,F@z2,F@z3, and 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 tenant mysql from the data backup path and log archive path to the specified timestamp 2020-06-01 00:00:00, specify the resource pool as restore_pool, set the replica locality to F@z1,F@z2,F@z3, and specify primary_zone as z1 to 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 change in the url parameter, the usage of other parameters is consistent with OSS.

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

     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 tenant mysql from the data backup path and log archive path to the current latest archive point, specify the resource pool as restore_pool, set the restore concurrency concurrency to 50, and 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 change in the url parameter, the usage of other parameters is consistent with that of NFS.

   Restore tenant mysql from the S3 backup path and S3 log archive path to the specified timestamp 2024-01-15 00:00:00, specify the resource pool as restore_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 change in the url parameter, the usage of other parameters is consistent with that of OSS.

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

     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 tenant mysql from the GCS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specify the resource pool as restore_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 tenant mysql from the COS backup path and log archive path to the specified timestamp 2023-06-01 00:00:00, specify the resource pool as restore_pool, and perform a full restore.

     obclient> ALTER SYSTEM RESTORE mysql FROM 's3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***,s3://oceanbase-test/backup/archive?host=cos.ap-xxxx.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 restored is created, if you need to accelerate the restore, you can configure the number of concurrent threads for the restore task using the parameter ha_high_thread_score.

   1. Check the oceanbase.DBA_OB_TENANTS view to see if the Meta tenant corresponding to the tenant to be restored has been created.

      The statement is as follows:

      SELECT * FROM oceanbase.DBA_OB_TENANTS;
      

      In the query results, the tenant whose TENANT_NAME column starts with META$ is the Meta tenant. When the status of the Meta tenant is NORMAL, it indicates that the Meta tenant has been created successfully.

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

   2. Set the parameter ha_high_thread_score.

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

      An example statement is as follows:

      ALTER SYSTEM SET ha_high_thread_score =10 TENANT = mysql;
      

What to do next

• After initiating a restore task, you can view the progress and results of the restore through views. For specific operations, see View restore progress.

• After the restore task is completed, if you are restoring backup data from a lower version to a cluster of a higher version, you also need to upgrade the restored tenant. For specific operations, see Upgrade a tenant after restore completion.

• The physical restore process is unified with that of a physical standby database. After physical restore, the tenant becomes a standby tenant. Subsequently, this tenant can provide related services as a standby tenant or be promoted to a primary tenant to provide services. For detailed operations on continuing log archiving and replaying logs from the source tenant as a standby tenant, see Segmented log replay for a standby tenant; for detailed operations on promoting a standby tenant to a primary tenant, see Promote a standby tenant to a primary tenant.

References

For more information about restore, see Restore process.