• 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. Understanding Flashback Table Feature in Oracle Database
  2. Oracle Database 12c New feature: Local Temporary Tablespaces
  3. Oracle SQL Script to Detect Tablespace Fragmentation
  4. Oracle 12c Multitenant Architecture: Troubleshooting Ora-12518
  5. How to startup an Oracle Database Instance using spfile or pfile(init.ora) parameter file
  6. Beginners Guide to Flash Recovery Area in Oracle Database
  7. ORA-12547: TNS:lost Contact (Oracle 12c2)
  8. CentOS / RHEL : Installing and Configuring ASMLib
  9. Oracle Database: Profile Limits (Resource Parameter(s)) Are Not Enforced / Do Not Work
  10. How to configure Partitioned Block Devices (Non-ASMLIB) And Assign Them To ASM

You May Also Like

Primary Sidebar

Recent Posts

  • qsub Command Examples in Linux
  • qsub: command not found
  • qrcp Command Examples in Linux
  • qmrestore Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright