Monday, August 30, 2010

Curious RMAN-05001 in database duplicate

RMAN duplicate is one of the coolest Oracle features. Idea is to clone database in a most easiest way, allowing DBAs to create database replica in any possible way in shortest possible time.

Regardless scenario, new directory structure in auxiliary database (replica database) is totally independent from target database (one that is use as a source for cloning). For that you have two options which allows DBA redirection of datafile's location:
DB_FILE_NAME_CONVERT
NOFILENAMECHECK
First one, DB_FILE_NAME_CONVERT is like mapper for directory structures, while second NOFILENAMECHECK allows to reuse the same target locations for the auxiliary (without any mapping).

Do not use NOFILENAMECHECK when target and destination database are on the same box because datafiles from target database will be overwritten!

The problem

So according mentioned in praxis there are 3 possible scenarios according new datfiles location (in our example let db_name remains the same):
  1. Same: Directory structure are identical.
    Target database Auxiliary database
    C:\oradata\hcpro = C:\oradata\hcpro
    D:\oradata\hcpro = D:\oradata\hcpro
    E:\oradata\hcpro = E:\oradata\hcpro
    F:\oradata\hcpro = F:\oradata\hcpro
    This is in case of cloning to different box only.

  2. Totally different: Directory structure differs in all locationn.
    Target database Auxiliary database
    C:\oradata\hcpro --> C:\neworadata\hcpro
    D:\oradata\hcpro --> D:\neworadata\hcpro
    E:\oradata\hcpro
    F:\oradata\hcpro --> E:\neworadata\hcpro
    This may be in both cases. Same or different box!

  3. Mixed: Some locations are same and some are different.
    Target database Auxiliary database
    C:\oradata\hcpro = C:\oradata\hcpro
    D:\oradata\hcpro --> D:\oradata\hcpro
    F:\oradata\hcpro
    E:\oradata\hcpro --> E:\oradata\hcpro
    G:\oradata\hcpro
    This is in case of cloning to different box only!
let me show real RMAN DUPLICATE commands for all three cases. In examples auxiliary database has the same name (hcpro) and all needed changes are done through RMAN (not through init.ora parameter).

1. Same

Tipical example for RMAN DUPLICATE command would be:
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       NOFILENAMECHECK
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
As you can see NOFILENAMECHECK option ensure that all is automatic-same as on target database.

2. Totally different

RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       DB_FILE_NAME_CONVERT =('C:\ORADATA\HCPRO\' , 'C:\NEWORADATA\HCPRO\'
                              'D:\ORADATA\HCPRO\' , 'D:\NEWORADATA\HCPRO\'
                              'F:\ORADATA\HCPRO\' , 'D:\NEWORADATA\HCPRO\'
                              'E:\ORADATA\HCPRO\' , 'E:\NEWORADATA\HCPRO\'
                             )
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\NEWORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\NEWORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\NEWORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
As you can see DB_FILE_NAME_CONVERT option make mappings from target to auxiliary locations. Image this option like "global" replace path string function which Oracle do on the fly.

3. Mixed

And now we come to situation which is (at least for me) curious. Because it is based for duplicate on different box, many users think that this is valid command:
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       DB_FILE_NAME_CONVERT =('F:\ORADATA\HCPRO\' , 'D:\ORADATA\HCPRO\'
                              'G:\ORADATA\HCPRO\' , 'E:\ORADATA\HCPRO\'
                             )
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
Directories that are not mentioned should remain as they are! But this gives mentioned RMAN-05001 error:
allocated channel: aux1
channel aux1: sid=4 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-AUG-10
released channel: aux1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/27/2010 07:13:20
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\DWH_NDX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\INDEXES02.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\INDEXES01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\SYSAUX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\UNDOTBS01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\SYSTEM01.DBF conflicts with a file used by the target database

RMAN>

When you change command in a way:
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       DB_FILE_NAME_CONVERT =('C:\oradata\hcpro\' , 'C:\oradata\hcpro\'
                              'D:\oradata\hcpro\' , 'D:\oradata\hcpro\'
                              'F:\oradata\hcpro\' , 'D:\oradata\hcpro\'
                              'E:\oradata\hcpro\' , 'E:\oradata\hcpro\'
                              'G:\oradata\hcpro\' , 'E:\oradata\hcpro\'
                             )
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
that produce the same error:
allocated channel: aux1
channel aux1: sid=3 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-AUG-10
released channel: aux1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/27/2010 15:32:31
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\DWH_NDX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\INDEXES02.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\INDEXES01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\SYSAUX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\UNDOTBS01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\SYSTEM01.DBF conflicts with a file used by the target database

RMAN>
<>

The solution

After some investigation I come to the solution which is composed of both options in RMAN command: NOFILENAMECHECK and DB_FILE_NAME_CONVERT, regardless this looks funny!
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       NOFILENAMECHECK
       DB_FILE_NAME_CONVERT =('F:\ORADATA\HCPRO\' , 'D:\ORADATA\HCPRO\'
                              'G:\ORADATA\HCPRO\' , 'E:\ORADATA\HCPRO\'
                             )
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
The result comes OK:
allocated channel: aux1
channel aux1: sid=3 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-AUG-10

contents of Memory Script:
{
   set until scn  14251842631;
   set newname for datafile  1 to
 "D:\ORADATA\HCPRO\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\ORADATA\HCPRO\UNDOTBS01.DBF";
   set newname for datafile  3 to
 "D:\ORADATA\HCPRO\SYSAUX01.DBF";
   set newname for datafile  4 to
 "E:\ORADATA\HCPRO\GIS01.DBF";
   set newname for datafile  5 to
 "E:\ORADATA\HCPRO\INDEXES01.DBF";
   set newname for datafile  6 to
 "D:\ORADATA\HCPRO\USERS01.DBF";
   set newname for datafile  7 to
 "E:\ORADATA\HCPRO\USERS_2_01.DBF";
   set newname for datafile  8 to
 "E:\ORADATA\HCPRO\USERS_2_O2.DBF";
   set newname for datafile  9 to
 "E:\ORADATA\HCPRO\TOOLS01.DBF";
   set newname for datafile  10 to
 "D:\ORADATA\HCPRO\USERS02.DBF";
   set newname for datafile  11 to
 "E:\ORADATA\HCPRO\INDEXES02.DBF";
   set newname for datafile  12 to
 "E:\ORADATA\HCPRO\DWH_DATA01.DBF";
   set newname for datafile  13 to
 "E:\ORADATA\HCPRO\DWH_NDX01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 27-AUG-10

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORADATA\HCPRO\SYSTEM01.DBF
restoring datafile 00003 to D:\ORADATA\HCPRO\SYSAUX01.DBF
restoring datafile 00005 to E:\ORADATA\HCPRO\INDEXES01.DBF
restoring datafile 00006 to D:\ORADATA\HCPRO\USERS01.DBF
restoring datafile 00008 to E:\ORADATA\HCPRO\USERS_2_O2.DBF
restoring datafile 00010 to D:\ORADATA\HCPRO\USERS02.DBF
channel aux1: reading from backup piece G:\RMAN_BACKUP\HCPRO\DF728053918_S6435_S1
...

The End

My testing environment was:
  • Windows 2008 x64
  • Oracle Enterprise 10.2.0.4 x64

Hope this will help someone in the future with similar problems.

Cheers!
at

7 comments:

  1. Marko SuticAugust 30, 2010 at 9:19 AM

    To be honest I've never used RMAN duplicate for cloning database. But I must test this in testing environment. Nice example Damir ;)

    Regards,
    Marko

    ReplyDelete
  2. Damir VadasAugust 30, 2010 at 10:03 AM

    Marko,

    THX for your reply. RMAN DUPLICATE with quick ORACLE_HOME cloning in 11g gives DBA real nice opportunity to establish DEV instance in few hours...what gives development enough time to react in a case or huge problems...
    Soon I'll wright another topic on DUPLICATE subject soon when I get time.
    Damir

    ReplyDelete
  3. UnknownAugust 9, 2012 at 12:49 PM

    scripts are very helpful.Thanks alot

    ReplyDelete
  4. AnonymousFebruary 28, 2013 at 2:32 PM

    Good post, but you can add DB_FILE_NAME_CONVERT to init file and do not use it in script.
    Thanks for info, it was resolved my issue.

    ReplyDelete
  5. FraterSil Grau 1March 29, 2017 at 9:01 PM

    Pretty useful information you have posted, Damir. I have applied it quick and easy.

    ReplyDelete
  6. Mala.WFebruary 18, 2019 at 1:17 AM

    I am rebuilding my database on a new server with different mount point names and the rman clause "NOFILENAMECHECK" saved me today. Thanks so much!

    ReplyDelete