TSPITR fails With RMAN-06553 (Doc ID 2078790.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
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
CHANGES
DB_CREATE_FILE_DEST is no longer defined.
CAUSE
Expected behavior based on the following documentation:
"Auxiliary set data files can have Oracle Managed Files (OMF) in the target and can use Automatic Storage Management (ASM) or non-ASM storage. TSPITR performs name conversion differently when the DB_FILE_NAME_CONVERT initialization parameter is set and the OMF files are in ASM or non-ASM storage."
Problem occurs because DB_CREATE_FILE_DEST is not defined. It is needed when tablespace is dropped including contents and datafiles. When 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:
1. Create a new tablespace and add an OMF datafile
2. Unset DB_CREATE_FILE_DEST
3. Backup database to include the new tablespace
4. Drop the newly created tablespace including contents but keep the datafile
5. Do TSPITR - the process should be flawless
Test 2:
1. Create a new tablespace and add a OMF datafile
2. Unset DB_CREATE_FILE_DEST
3. Backup database to include the new tablespace
4. Drop the newly created tablespace including contents and datafile
5. 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
SOLUTION
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>/';
}