• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

TSPITR fails With RMAN-06553

by admin

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

Filed Under: oracle, Oracle 11g, oracle 12c, RMAN

Some more articles you might also be interested in …

  1. How to set udev rule for setting the disk permission on ASM disks when using multipath on CentOS/RHEL 6
  2. IPv4 Subnet Basics and Oracle Clusterware
  3. SQL: Difference between delete and truncate
  4. How to Create Undo Tablespace for a Newly Added RAC Instance (ORA-30012)
  5. Beginners Guide to Oracle Temporary Tablespace Groups
  6. SLES 12: Database Startup Error with ORA-27300 ORA-27301 ORA-27303 While Starting using Srvctl
  7. List of Operating System Processes Started by the OMS
  8. How To Set the Permission of the Files Created Using UTL_FILE
  9. How to Move tables and indexes to a different tablespace
  10. How to Remove or delete a Weblogic Server (WLS) Domain

You May Also Like

Primary Sidebar

Recent Posts

  • What are /dev/zero and /dev/null files in Linux
  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright