The Geek Diary

TSPITR fails With RMAN-06553

by

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:

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 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:

  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

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/';
}

You May Also Like