Perform restore from the specified paths

After you move the backup sets and archivelog pieces of a tenant to other paths due to business needs, you can load them and then execute the RESTORE statement to restore the tenant.

Prerequisites

• You have obtained the paths of the backup sets and archivelog pieces to restore from.

• The backup sets and archivelog pieces to restore from belong to the same tenant. You can restore a tenant from only the backup sets and archivelog pieces that belong to the same tenant.

• You have completed the preparations. For more information, see Preparations.

Procedure

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

2. (Optional) Execute the ALTER SYSTEM RESTORE...PREVIEW statement to specify the original data backup and log archive paths as well as the timestamp or system change number (SCN) to restore to.

   The syntax is as follows:

   ALTER SYSTEM RESTORE FROM 'uri' UNTIL { TIME='timestamp' | SCN=scn } PREVIEW;
   

   The parameters are described as follows:

   • uri: the original data backup and log archive paths, which must be separated with a comma (,).

   • TIME='timestamp' | SCN=scn: the timestamp or SCN to restore to. You must explicitly specify this parameter in the statement.

   For more information about the parameters, see ALTER SYSTEM - RESTORE PREVIEW.

   Here are some examples:

   OSS

   NFS

   • Specify the original data backup and log archive paths, and specify the SCN to restore to as 1712650554000909004.

     obclient [oceanbase]> ALTER SYSTEM RESTORE FROM 'oss://test_bucket/data?host=***&access_id=****&access_key=****, oss://test_bucket/archive?host=***&access_id=****&access_key=****' UNTIL SCN = 1712650554000909004 PREVIEW;
     

   • Specify the original data backup and log archive paths, and specify the timestamp to restore to as 2024-04-09 16:15:54.

     obclient [oceanbase]> ALTER SYSTEM RESTORE FROM 'oss://test_bucket/data?host=***&access_id=****&access_key=****, oss://test_bucket/archive?host=***&access_id=****&access_key=****' UNTIL TIME = '2024-04-09 16:15:54' PREVIEW;
     

   • Specify the original data backup and log archive paths, and specify the SCN to restore to as 1712650554000909004.

     obclient [oceanbase]> ALTER SYSTEM RESTORE FROM 'file:///ob_backup/data, file:///ob_backup/archive' UNTIL SCN = 1712650554000909004 PREVIEW;
     

   • Specify the original data backup and log archive paths, and specify the timestamp to restore to as 2024-04-09 16:15:54.

     obclient [oceanbase]> ALTER SYSTEM RESTORE FROM 'file:///ob_backup/data, file:///ob_backup/archive' UNTIL TIME = '2024-04-09 16:15:54' PREVIEW;
     

3. (Optional) Execute the SHOW RESTORE PREVIEW statement to query the backup sets and archivelog pieces required to restore to the specified timestamp or SCN.

   Notice

   You must wait until the execution of the ALTER SYSTEM RESTORE...PREVIEW statement succeeds before you execute the SHOW RESTORE PREVIEW statement. Otherwise, the query result is not as expected.

   obclient [oceanbase]> SHOW RESTORE PREVIEW;
   

   A sample query result is as follows:

   +--------------+-----------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------+
   | backup_type  | backup_id | backup_dest                                                                    | description                                                                  |
   +--------------+-----------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------+
   | BACKUP_SET   |         1 | file:///ob_backup/data/backup_set_1_full                                       |                                                                              |
   | BACKUP_PIECE |         1 | file:///ob_backup/archive/piece_d1002r1p1                                      | start_scn_display: 20240409T155954, checkpoint_scn_display: 20240409T160154. |
   | BACKUP_PIECE |         2 | file:///ob_backup/archive/piece_d1002r1p2                                      | start_scn_display: 20240409T160154, checkpoint_scn_display: 20240409T160353. |
   | BACKUP_PIECE |         3 | file:///ob_backup/archive/piece_d1002r1p3                                      | start_scn_display: 20240409T160354, checkpoint_scn_display: 20240409T160553. |
   | BACKUP_PIECE |         4 | file:///ob_backup/archive/piece_d1002r1p4                                      | start_scn_display: 20240409T160554, checkpoint_scn_display: 20240409T160754. |
   | BACKUP_PIECE |         5 | file:///ob_backup/archive/piece_d1002r1p5                                      | start_scn_display: 20240409T160754, checkpoint_scn_display: 20240409T160953. |
   | BACKUP_PIECE |         6 | file:///ob_backup/archive/piece_d1002r1p6                                      | start_scn_display: 20240409T160954, checkpoint_scn_display: 20240409T161154. |
   | BACKUP_PIECE |         7 | file:///ob_backup/archive/piece_d1002r1p7                                      | start_scn_display: 20240409T161154, checkpoint_scn_display: 20240409T161354. |
   | BACKUP_PIECE |         8 | file:///ob_backup/archive/piece_d1002r1p8                                      | start_scn_display: 20240409T161354, checkpoint_scn_display: 20240409T161554. |
   +--------------+-----------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------+
   9 rows in set
   

   The query result shows all backup sets and archivelog pieces required to restore to the specified time point as well as their paths at the original data backup and log archive destinations. If you have moved some or all of the files to other paths or storage media due to business needs, you must maintain the mapping from the original path to the current path for each backup set and archivelog piece.

   For example, the preceding query result shows that the archivelog piece corresponding to the backup piece with an ID of 1 is required for the restore but the file has been moved to oss://test_bucket/archive/piece_1. Therefore, when you execute the ADD RESTORE SOURCE statement to load the backup path, you must use the new path oss://test_bucket/archive/piece_1.

4. Execute the ADD RESTORE SOURCE statement to load the backup sets and archivelog pieces to restore from.

   The syntax is as follows:

   ALTER SYSTEM ADD RESTORE SOURCE 'restore_path';
   

   The parameters in the syntax are described as follows:

   • restore_path: the path of the backup set or archivelog piece to load. You can specify one or more paths.

     Notice

     If the backup sets or archivelog pieces to load are stored in an object storage service, the backup sets must have the same host, access_id, and access_key settings, and so do the archivelog pieces.

   • You can execute this statement multiple consecutive times in the same session. The system incrementally adds the execution results to the restore source list.

   Here is an example:

   1. Load the backup set to restore from.

      obclient [oceanbase]> ALTER SYSTEM ADD RESTORE SOURCE 'file:///ob_backup/data/backup_set_1_full';
      

   2. Load the archivelog piece to restore from.

      obclient [oceanbase]> ALTER SYSTEM ADD RESTORE SOURCE 'file:///ob_backup/archive/piece_d1002r1p1,file:///ob_backup/archive/piece_d1002r1p2';
      

      obclient [oceanbase]> ALTER SYSTEM ADD RESTORE SOURCE 'oss://test_bucket/archive/piece_1?host=***&access_id=x****&access_key=****';
      

5. (Optional) If you find that the specified path of the backup set or archivelog piece is incorrect during loading, execute the following statement to clear the previous input, and then reload the backup set or archivelog piece from the correct path.

   obclient [oceanbase]> ALTER SYSTEM CLEAR RESTORE SOURCE;
   

6. Call the RESTORE statement after the loading from all the specified paths is completed.

   Notice

   • You must execute the RESTORE statement in the same session as the ADD RESTORE SOURCE statement. If the original session is interrupted or you use a new session, the previously loaded restore source list is cleared.
   • After you execute the ADD RESTORE SOURCE statement, you cannot restore a tenant by executing the RESTORE statement with a specified backup destination in the same session.
   • After you execute the RESTORE statement, the previously loaded restore source list is cleared regardless of whether the restore succeeds. If you want to call the RESTORE statement again, you must reload the backup sets and archivelog pieces to restore from.

   The syntax is as follows:

   ALTER SYSTEM RESTORE restore_tenant_name [UNTIL {TIME='timestamp' | SCN=scn} ] WITH 'restore_option';
   

   For more information about the statement, see RESTORE.

   For more information about the parameters, see [Parameters related to physical restore](800.parameters-of-the-restore.md).
   

   Here are some examples:

   • Restore the mysql tenant from the specified paths to the SCN 1658285759724047000, and set the resource pool to restore_pool.

     // Full restore
     obclient [oceanbase]> ALTER SYSTEM RESTORE mysql UNTIL SCN = 1712650554000909004 WITH 'pool_list=restore_pool';
     
     // Quick restore
     obclient [oceanbase]> ALTER SYSTEM RESTORE mysql UNTIL SCN = 1712650554000909004 WITH 'pool_list=restore_pool&method=quick';
     

   • Restore the mysql tenant from the specified paths to the timestamp 2024-04-09 16:15:54, and set the resource pool to restore_pool.

     // Full restore
     obclient [oceanbase]> ALTER SYSTEM RESTORE mysql UNTIL TIME = '2024-04-09 16:15:54' WITH 'pool_list=restore_pool';
     
     // Quick restore
     obclient [oceanbase]> ALTER SYSTEM RESTORE mysql UNTIL TIME = '2024-04-09 16:15:54' WITH 'pool_list=restore_pool&method=quick';
     

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 View the physical restore progress.

• If you restore a tenant from backup files of an earlier OceanBase Database version to a cluster of a later version, after the restore job is completed, you must upgrade the restored tenant. For more information, see Upgrade a tenant after restore.

• Physical restore is associated with the Physical Standby Database solution. A physically restored tenant serves as a standby tenant. You can switch it to the PRIMARY role based on your business needs. For information about how to proceed to log replay from the source tenant, see Replay logs to a standby tenant. For information about how to switch a standby tenant to the PRIMARY role, see Convert a standby tenant into primary tenant.

References

For more information about restore, see Restore process.