SHOW RESTORE PREVIEW

Purpose

This statement is used to display the data backup sets (backup_set) and log archives (archivelog_piece) required for restoring to a specified time.

Limitations and considerations

This statement must be executed after the ALTER SYSTEM RESTORE...PREVIEW command has been successfully executed. Otherwise, the required results cannot be obtained.

Privilege requirements

To execute this statement, the current user must have the ALTER SYSTEM privilege.

Syntax

SHOW RESTORE PREVIEW;

Examples

1. Specify the original data backup path and log archive path, and expect to restore to the specified timestamp 2024-04-09 16:15:54.

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

2. Display the backup_set and archivelog_piece required for restoring to the specified time.

   SHOW RESTORE PREVIEW;
   

   The 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 the backup_set and archivelog_piece required for restoring to the specified time, as well as the storage paths of these files in the original data backup and log archive destinations. If you have manually moved some or all of the files to other paths or storage media for business needs, you need to maintain the mapping relationships from the original paths to the current paths for each backup_set and archivelog_piece based on your actual situation.

   For example, in the query result above, the archivelog_piece with BACKUP_PIECE equal to 1 needs to be used for restoration. However, this file has been manually moved to oss://test_bucket/archive/piece_1. Therefore, when using the ADD RESTORE SOURCE command to load the backup path, you need to select the new storage path oss://test_bucket/archive/piece_1.

References

• RESTORE PREVIEW
• Execute a restore from a specified path