Handy RMAN Important Script When Planning to Restore and Recover 


1) Find Archive sequence based on time

select sequence#,to_char(completion_time, 'dd-mm-yyyy hh24:mi:ss'), to_char(next_time,'dd-mm-yyyy hh24:mi:ss') from v$archived_log order by 1 asc;


2) Find the space required for Restore

RMAN> run {
set until time "to_date('2022 JAN 11 15:31','YYYY MON DD HH24:MI')";
report schema;
}2> 3> 4>

executing command: SET until clause

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name <db_unique_name>

List of Permanent Datafiles
===========================
File Size(MB) Tablespace                   RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    830      <tablespace name>             ***    /<db file path>/<db file name>.dbf
2    980      <tablespace name>             ***    /<db file path>/<db file name>.dbf
3    205      <tablespace name>             ***    /<db file path>/<db file name>.dbf
4    100      <tablespace name>             ***    /<db file path>/<db file name>.dbf
5    345      <tablespace name>             ***    /<db file path>/<db file name>.dbf
6    100      <tablespace name>             ***    /<db file path>/<db file name>.dbf

The datafile's directories and diskgroups must pre-exist and have sufficient space to hold the datafiles. Otherwise you need to use the SET NEWNAME to restore to an alternate location that has the permission and space availability.

Note: you may also need space in the archive log destination if the required archivelogs will be also be restored from their backuppieces

3)  Get RMAN script execution log

RMAN> SPOOL TRACE TO RMAN_script.OUT
SET ECHO ON
# place the rest of your rman commands here
SPOOL TRACE OFF;

4) Get Summary for RMAN Backup

RMAN> spool trace to <spool file name>;
set echo on;
list incarnation of database;
list backup summary;
list backup of datafile 1;
list copy of datafile 1;
spool trace off;

5) Preview Restore and Recovery

You can apply RESTORE ... PREVIEW to any RESTORE operation to create a detailed list of every backup to be used in the requested RESTORE operation, and the necessary target SCN for recovery after the RESTORE operation is complete. This command accesses the RMAN repository to query the backup metadata, but does not actually read the backup files to ensure that they can be restored.

Blockrecovery can also be previewed:

RMAN> blockrecover corruption list preview;

 
A restore preview will list all backups required for the entire restore and recovery operation. From this list you can then go ahead and confirm that the backuppieces are physically available on disk or tape before performing the actual restore.

Useful items from the restore preview:

Preview Database Restore

RMAN> run {
2> set until time "to_date('2022 JAN 11 11:22','YYYY MON DD HH24:MI')";
3> restore database preview;
4> }

Example Output

BS Key  Type LV         Size            Device Type    Elapsed Time   Completion Time     
------- ---- --         ----------      -----------    ------------   --------------------
266     Incr 0 [1]      1.49G[2]        DISK[3]        00:01:31[4]       2022 JAN 11 11:21:57 [5]
        BP Key: 268   Status: AVAILABLE[6]  Compressed: NO[7] Tag: <TAG NAME>
        Piece Name: /<backup piece path>/<backuppiece name>[8]

  List of Datafiles in backup set 266 [9]
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 53204035   2022 JAN 11 11:20:27 /<db file path>/<db file name>
  4    0  Incr 53204035   2022 JAN 11 11:20:27 /<db file path>/<db file name>
  7    0  Incr 53204035   2022 JAN 11 11:20:27 /<db file path>/<db file name>
  9    0  Incr 53204035   2022 JAN 11 11:20:27 /<db file path>/<db file name>
...
  List of Archived Logs in backup set 268
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    317     53204027   2022 JAN 11 11:20:25 53204084   2022 JAN 11 11:22:03
Media recovery start SCN is 53204027
Recovery must be done beyond SCN 53204036 to clear datafile fuzziness[10]
Finished restore at 2022 JAN 11 11:24:07

[1] This is an incremental level 0 backup
[2] The backuppiece is 1.49G in size
[3] This is a backup to DISK rather than tape
[4] The backuppiece took 1 minute and 31 seconds to backup
[5] The time when this backuppiece completed
[6] The backuppiece is available for use according to the RMAN repository
[7] This is NOT a compressed backup
[8] This is the backuppiece. It must exist on disk for use.
[9] the datafiles contained in this backuppiece
[10] this is just informational message. It simply telling you that the SET UNTIL must reflect a time/SCN that is beyond this value before the database can be opened



6)  Preview ControlFile Restore

If perform a database restore to a point in time in the past, you should also restore the controlfile as it will reflect the database structure at the time. This is especially important if there are tablespaces or datafiles being added or dropped over the database's lifetime.

 An example of a controlfile restore preview:

RMAN> run {
set until time "to_date('Jun 18 2013 08:00:00','Mon DD YYYY HH24:MI:SS')";
restore controlfile preview;
}

using target database control file instead of recovery catalog
..

executing command: SET until clause

Starting restore at 01/11/2022 22:28:04


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
52857   Full    19.00M     SBT_TAPE    00:00:09     01/11/2022 04:02:04
       BP Key: 52857   Status: AVAILABLE  Compressed: NO  Tag: <tag name>
       Handle: <backuppiece name [1]  Media: <media name> [2]
 Control File Included: Ckp SCN: 238614135722   Ckp time: 01/11/2022 04:01:55

 
List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
52858   430.50M    SBT_TAPE    00:00:36     01/11/2022 09:01:10
       BP Key: 52858   Status: AVAILABLE  Compressed: NO  Tag: <tag name>
       Handle: Handle: <backuppiece name [1]  Media: <media name>[2]

 List of Archived Logs in backup set 52858
 Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
 ---- ------- ---------- ------------------- ---------- ---------
 1    14822   238614135104 01/11/2022 04:00:28 238614215594 01/11/2022 08:40:24 [3]
validation succeeded for backup piece
Media recovery start SCN is 238614135722
Recovery must be done beyond SCN 238614135722 to clear datafile fuzziness
validation succeeded for backup piece
Finished restore at 01/11/2022 22:28:13
released channel: dev_0

[1] "Handle" is the backuppiece name
[2] Media is the lable given to the tape
[3] This is the achivelog needed to recover this controlfile

That is, you need to ensure that the backuppiece [1] on tape [2] is loaded and ready for use 04:02:04
       BP Key: 52857   Status: AVAILABLE  Compressed: NO  Tag: <tag name>
       Handle: <backuppiece name [1]  Media: <media name> [2]
 Control File Included: Ckp SCN: 238614135722   Ckp time: 01/11/2022 04:01:55

 
List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
52858   430.50M    SBT_TAPE    00:00:36     01/11/2022 09:01:10
       BP Key: 52858   Status: AVAILABLE  Compressed: NO  Tag: <tag name>
       Handle: Handle: <backuppiece name [1]  Media: <media name>[2]

 List of Archived Logs in backup set 52858
 Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
 ---- ------- ---------- ------------------- ---------- ---------
 1    14822   238614135104 01/11/2022 04:00:28 238614215594 01/11/2022 08:40:24 [3]
validation succeeded for backup piece
Media recovery start SCN is 238614135722
Recovery must be done beyond SCN 238614135722 to clear datafile fuzziness
validation succeeded for backup piece
Finished restore at 01/11/2022 22:28:13
released channel: dev_0

[1] "Handle" is the backuppiece name
[2] Media is the lable given to the tape
[3] This is the achivelog needed to recover this controlfile

That is, you need to ensure that the backuppiece [1] on tape [2] is loaded and ready for use



7) Recovery Preview

Recovery Preview is only available from 11g onwards.

Once the database is restored, mounted, then the recovery can also be previewed:

RMAN> run {
set until time "to_date('2022 JAN 11 15:31','YYYY MON DD HH24:MI')";
recover database preview;
}

8)  Validating Restore

As an alternative to RESTORE ... PREVIEW, you can use the RESTORE ... VALIDATE HEADER command. In addition to listing the files needed for restore and recovery, the RESTORE ... VALIDATE HEADER command validates the backup file headers to determine whether the files on disk or in the media management catalog correspond to the metadata in the RMAN repository.

When planning your restore and recovery operation, use RESTORE ... PREVIEW or RESTORE . VALIDATE HEADER to ensure that all required backups are available or to identify situations in which you may want to direct RMAN to use or avoid specific backups.

 RMAN> restore database validate;

Starting restore at 11 JAN 2022 14:41:23
using channel ORA_DISK_1
using channel ORA_DISK_2

skipping datafile 6; already restored to file <db file path>/<db file name>
datafile 7 will be created automatically during restore operation
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_2: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /< backuppiece path>/<backuppiece name>
channel ORA_DISK_2: reading from backup piece /< backuppiece path>/<backuppiece name>
channel ORA_DISK_2: ORA-19870: error while restoring backup piece /< backuppiece path>/<backuppiece name>
ORA-19505: failed to identify file "/< backuppiece path>/<backuppiece name>"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

channel ORA_DISK_1: piece handle=/< backuppiece path>/<backuppiece name> tag=<tag name>
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
failover to previous backup

datafile 7 will be created automatically during restore operation
channel ORA_DISK_1: scanning datafile copy /<datafile copy path>/<datafile copy name>
channel ORA_DISK_2: scanning datafile copy /<datafile copy path>/<datafile copy name>
failover to previous backup

datafile 7 will be created automatically during restore operation
Finished restore at 11 JAN 2022 14:41:53


RMAN> list backuppiece '/<backuppiece path>/<backuppiece name>';


List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
43      43      1   1   AVAILABLE   DISK        /<backuppiece path>/<backuppiece name>

 

From the above we can see that the backuppiece  is missing from disk even though it is listed as AVAILABLE for use. So you need to locate this backuppiece before performing the actual restore.



Refer:
Checklist for an RMAN Restore (Doc ID 1554636.1)


If you like please follow and comment