SHOW RESTORE PREVIEW

Purpose

You can use this statement to show all backup sets and archivelog pieces required to restore to the specified timestamp.

Limitations and considerations

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.

Required privileges

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

Syntax

SHOW RESTORE PREVIEW;

Examples

1. Specify the original data backup and log archive paths, and specify the timestamp to restore to as 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. Query the backup sets and archivelog pieces required to restore to the specified timestamp or SCN.

   SHOW RESTORE PREVIEW;
   

   The return 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 SCN 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 specify the new path oss://test_bucket/archive/piece_1.

References

• RESTORE PREVIEW
• Perform restore from the specified paths