The Problem
TSPITR fails With RMAN-06553 when the tablespace (using OMF files) is dropped including contents and datafiles:
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 10/22/2015 16:43:08 RMAN-03015: error occurred in stored script Memory Script RMAN-06553: DB_CREATE_FILE_DEST must be set for SET NEWNAME … TO NEW
The Solution
This is an expected behavior based on the following documentation:
Problem occurs because DB_CREATE_FILE_DEST is not defined. It is needed when a tablespace is dropped including contents and datafiles. When a tablespace is dropped including contents but datafiles are kept then DB_CREATE_FILE_DEST is not needed since RMAN is aware of the location of the datafile.
This behaviour can be demonstrated using the following simple tests:
Test 1:
- Create a new tablespace and add an OMF datafile
- Unset DB_CREATE_FILE_DEST
- Backup database to include the new tablespace
- Drop the newly created tablespace including contents but keep the datafile
- Do TSPITR – the process should be flawless
Test 2:
- Create a new tablespace and add a OMF datafile
- Unset DB_CREATE_FILE_DEST
- Backup database to include the new tablespace
- Drop the newly created tablespace including contents and datafile
- Do TSPITR – the process fails with error
RMAN-03002: failure of recover command at 11/16/2015 22:20:35 RMAN-03015: error occurred in stored script Memory Script RMAN-06553: DB_CREATE_FILE_DEST must be set for SET NEWNAME … TO NEW
Use “SET NEWNAME FOR DATAFILE [df_number] to “[desired_path]“, where [df_number] is the datafile number and [desired_path] is the desired destination and datafile name.
For example:
RUN { SET NEWNAME FOR DATAFILE 4 TO "/desired location/users.dbf"; RECOVER TABLESPACE users UNTIL TIME "to_date('02-nov-2015 12:53:08','dd-mon-yyyy hh24:mi:ss')" AUXILIARY DESTINATION '/desired location/'; }