(originally published at Pythian)
Its all started from the question on communities.oracle.com
An "enhancement" in 11.2 prevents RMAN duplicating non self-contained subset of a database
where new feature of RMAN in 11.2 was shown which prevents duplication of sets of tablespaces if they are not self-contained.
I took debug of duplication and figured out that RMAN internally executes procedure dbms_tts.transport_set_check
preventing any further copy operations if tablespaces do not satisfy TTS requirements.
Now the plot thickens and considering RMAN shows everything that it does in log file I decided to repeat the same steps
but one by one and manually.
First of all I took log of successful duplication for the database but only for one
self-contained tablespace 'T'
rman target sys/pwd@//oel5/d122 auxiliary sys/...@temp \
cmdfile=duplicate_wts.cmd debug trace=wts.out
Recovery Manager: Release 11.2.0.2.0 - Production on ...
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN-06005: connected to target database: D122 (DBID=3977667195)
RMAN-06549: connected to auxiliary database: TEMP (not mounted)
run {
duplicate target database to temp from active database
tablespace 'T'
spfile
set db_file_name_convert '/u02/oradata/d122', '/u02/oradata/temp'
set log_file_name_convert '/u02/oradata/d122', '/u02/oradata/temp'
set sga_target '600M';
}
exit
It produced the output of commands executed by RMAN internally
and went successfully opening database with resetlogs but failed
on the step of dropping not copied tablespaces since they were not self-contained.
RMAN-06958: Executing: drop tablespace "P5" including contents cascade constraints RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at ... RMAN-05501: aborting duplication of target database RMAN-06136: ORACLE error from auxiliary database:
ORA-14404: partitioned table contains partitions in a different tablespace Recovery Manager complete.
But I got the code that was performed by RMAN during duplication
and generally those blocks were headed by message
"RMAN-08161: contents of Memory Script:"
although had to add "CREATE CONTROLFILE" block as well:
{
backup as copy reuse
targetfile '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiled122.ora' auxiliary format
'/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletemp.ora' ;
sql clone "alter system set spfile= ''/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletemp.ora''";
}
...
{
sql clone "alter system set db_name =
''TEMP'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/u02/oradata/d122'', ''/u02/oradata/temp'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u02/oradata/d122'', ''/u02/oradata/temp'' comment=
'''' scope=spfile";
sql clone "alter system set sga_target =
600M comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
...
{
sql clone "alter system set db_name =
''D122'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TEMP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u02/oradata/d122/control01.ctl';
restore clone controlfile to '/u02/app/oracle/fast_recovery_area/d122/control02.ctl' from
'/u02/oradata/d122/control01.ctl';
alter clone database mount;
}
...
{
sql clone 'alter database flashback off';
set newname for datafile 1 to
"/u02/oradata/temp/system01.dbf";
set newname for datafile 2 to
"/u02/oradata/temp/sysaux01.dbf";
set newname for datafile 3 to
"/u02/oradata/temp/undotbs01.dbf";
set newname for datafile 8 to
"/u02/oradata/temp/t.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u02/oradata/temp/system01.dbf" datafile
2 auxiliary format
"/u02/oradata/temp/sysaux01.dbf" datafile
3 auxiliary format
"/u02/oradata/temp/undotbs01.dbf" datafile
8 auxiliary format
"/u02/oradata/temp/t.dbf" ;
sql 'alter system archive log current';
}
...
{
backup as copy reuse
archivelog like
"/u02/app/oracle/fast_recovery_area/D122/archivelog/2011_10_19/o1_mf_1_401_79wsdj3m_.arc"
auxiliary format
"/u02/app/oracle/fast_recovery_area/TEMP/archivelog/2011_10_19/o1_mf_1_401_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
...
{
set until scn 11283882;
recover
clone database
skip forever tablespace "USERS",
"P5",
"P4",
"P3",
"P2",
"P1" delete archivelog
;
}
...
{
sql clone "alter system set db_name =
''TEMP'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
...
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEMP" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u02/oradata/temp/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u02/oradata/temp/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u02/oradata/temp/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u02/oradata/temp/system01.dbf'
CHARACTER SET WE8MSWIN1252
...
{
set newname for tempfile 1 to
"/u02/oradata/temp/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u02/oradata/temp/sysaux01.dbf",
"/u02/oradata/temp/undotbs01.dbf",
"/u02/oradata/temp/t.dbf";
switch clone datafile all;
}
...
{
Alter clone database open resetlogs;
}
Based on the code generated by RMAN I created my own block
but had to divide it on several portions where first part
included all actions before copy of archive logs to auxiliary site
because of simple reason - I was not able to provide max SCN in latest sequence
(which will be generated by "sql 'alter system archive log current';")
at the time of script start and there is no such variable as current latest
sequence or SCN number:
run {
backup as copy reuse
targetfile '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiled122.ora' auxiliary format
'/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletemp.ora' ;
sql clone "alter system set spfile= ''/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletemp.ora''";
sql clone "alter system set db_name = ''TEMP'' comment= ''duplicate'' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/u02/oradata/d122'', ''/u02/oradata/temp'' comment= '''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u02/oradata/d122'', ''/u02/oradata/temp'' comment= '''' scope=spfile";
sql clone "alter system set sga_target = 600M comment= '''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =''D122'' comment=''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TEMP'' comment=''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u02/oradata/d122/control01.ctl';
restore clone controlfile to '/u02/app/oracle/fast_recovery_area/d122/control02.ctl' from
'/u02/oradata/d122/control01.ctl';
alter clone database mount;
sql clone 'alter database flashback off';
set newname for datafile 1 to "/u02/oradata/temp/system01.dbf";
set newname for datafile 2 to "/u02/oradata/temp/sysaux01.dbf";
set newname for datafile 3 to "/u02/oradata/temp/undotbs01.dbf";
set newname for datafile 8 to "/u02/oradata/temp/t.dbf";
backup as copy reuse
datafile 1 auxiliary format "/u02/oradata/temp/system01.dbf"
datafile 2 auxiliary format "/u02/oradata/temp/sysaux01.dbf"
datafile 3 auxiliary format "/u02/oradata/temp/undotbs01.dbf"
datafile 8 auxiliary format "/u02/oradata/temp/t.dbf";
sql 'alter system archive log current';
}
The code ran without problems, I took latest SCN and sequence number
and tried to copy them to auxiliary side but got error due to non-existed
directory for archive logs and had to create it manually.
Going forward, I was able to catalog data files copies and switched to them
with further successful recovery. However, everything of that was done
at auxiliary side with connection to it as "target".
Trying to do it with clone clause from primary side I was not able to catalog
copies and recovery failed restoring data files.
Skipping that not clear part yet, I proceed further and changed the name
from D122 to TEMP in spfile although could not find one of "shutdown"
operations being presented in printed "memory scripts".
And here started the part which I could not understand for a long time -
creation of controlfile with system data file and catalog operation of rest
of data files which were copied. It was my understanding that control file
should be created with all required data files and there is no other way to
"catalog" datafile copies in control file. But following RMAN "recipe" I took contents of
the code, recreated control file and cataloged datafiles, but switch operation failed:
CREATE CONTROLFILE REUSE SET DATABASE "TEMP" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u02/oradata/temp/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u02/oradata/temp/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u02/oradata/temp/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u02/oradata/temp/system01.dbf'
CHARACTER SET WE8MSWIN1252
/
Control file created.
RMAN> run {
set newname for tempfile 1 to
"/u02/oradata/temp/temp01.dbf";
switch tempfile all;
catalog datafilecopy "/u02/oradata/temp/sysaux01.dbf",
"/u02/oradata/temp/undotbs01.dbf",
"/u02/oradata/temp/t.dbf";
switch datafile all;
}
executing command: SET NEWNAME
using target database control file instead of recovery catalog
cataloged datafile copy
datafile copy file name=/u02/oradata/temp/sysaux01.dbf RECID=1 STAMP=765296913
cataloged datafile copy
datafile copy file name=/u02/oradata/temp/undotbs01.dbf RECID=2 STAMP=765296913
cataloged datafile copy
datafile copy file name=/u02/oradata/temp/t.dbf RECID=3 STAMP=765296913
RMAN>
RMAN> list datafilecopy all;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
1 2 A 23-OCT-11 11673680 23-OCT-11
Name: /u02/oradata/temp/sysaux01.dbf
Tag: TAG20111023T135321
2 3 A 23-OCT-11 11673680 23-OCT-11
Name: /u02/oradata/temp/undotbs01.dbf
Tag: TAG20111023T135321
3 8 A 23-OCT-11 11673680 23-OCT-11
Name: /u02/oradata/temp/t.dbf
Tag: TAG20111023T135321
RMAN> switch datafile 2,3,8 to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 10/23/2011 14:29:20
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 2
I checked trace file of RMAN duplicate trying to find any hidden commands,
checked notes about duplicate on MOS, even created SR for it but
did not get the answer how RMAN does it.
And finally command "set newname for tempfile ..." revealed the secret -
set newname should be executed in the same block where datafile copies
are cataloged! As simple as that!
I set names for copies, "cataloged" them, opened database with resetlogs
and got the same picture for other files in v$datafile as when RMAN did duplication
run {
set newname for datafile 2 to "/u02/oradata/temp/sysaux01.dbf";
set newname for datafile 3 to "/u02/oradata/temp/undotbs01.dbf";
set newname for datafile 8 to "/u02/oradata/temp/t.dbf";
catalog clone datafilecopy "/u02/oradata/temp/sysaux01.dbf",
"/u02/oradata/temp/undotbs01.dbf", "/u02/oradata/temp/t.dbf";
switch clone datafile all;
}
select name, file#, status from v$datafile;
NAME
-------------------------------------------------
FILE# STATUS
---------- -------
/u02/oradata/temp/system01.dbf
1 SYSTEM
/u02/oradata/temp/sysaux01.dbf
2 RECOVER
/u02/oradata/temp/undotbs01.dbf
3 RECOVER
/u02/oradata/temp/t.dbf
8 RECOVER
run {
Alter clone database open resetlogs;
}
NAME
---------------------------------------------------------
FILE# STATUS
---------- -------
/u02/oradata/temp/system01.dbf
1 SYSTEM
/u02/oradata/temp/sysaux01.dbf
2 ONLINE
/u02/oradata/temp/undotbs01.dbf
3 ONLINE
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00004
4 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00005
5 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00006
6 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00007
7 RECOVER
/u02/oradata/temp/t.dbf
8 ONLINE
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00009
9 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00010
10 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00011
11 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00012
12 RECOVER
/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00013
13 RECOVER
Have a good day!
