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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. Oracle RMAN interview questions
  2. How to move ASM spfile to a shared device in RAC
  3. How to Create the Oracle Password File using orapwd Command
  4. Beginners Guide to Working with spfile in Oracle ASM
  5. Oracle Database : Understanding Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes
  6. Common Init.ora Parameters and Unix, Linux Kernel Parameters and Relationship Between Them
  7. How to Resize the Undo Tablespace in Oracle Database
  8. ORA-00257:Archiver Error, Connect Internal Only Until Freed
  9. How To Add New Disk to An Existing Diskgroup on RAC Cluster or Standalone ASM Configuration
  10. Oracle Database 12.2 RMAN Cross Platform Tablespace Transport Over Network

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary