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 NOFILENAMECHECKFirst 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):- Same: Directory structure are identical.
This is in case of cloning to different box only.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
- Totally different: Directory structure differs in all locationn.
This may be in both cases. Same or different box!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
- Mixed: Some locations are same and some are different.
This is in case of cloning to different box only!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
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!
