Rman duplicate database (addendum)



I've recently had to follow my own blog post to duplicate a test database from a live one - the original is here: http://dbaharrison.blogspot.co.uk/2011/06/rman-duplicate-of-database-using.html

This didnt work for me due to a specific bug with 11.2 and block change tracking files. The metalink note didnt seem to work and was maybe because of our use of OMF.

I went through a whole series of attempts to get this to work and was met with a cascade of different erros with every method i attempted - a compendium of which are shown below

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/19/2014 10:51:48
RMAN-06403: could not obtain a fully authorized session
RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-00600: internal error code, arguments: [kck_rls_check must use (11,0,0,0,0) or lower], [kdt.c], [9576], [11.2.0.2.0], [], [], [], [], [], [], [], []
RMAN-04017: startup error description: ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

and

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 01/17/2014 16:39:41

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06026: some targets not found - aborting restore

RMAN-06729: no backup of the SPFILE found to restore




and



RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 01/17/2014 14:22:36

RMAN-05501: aborting duplication of target database

RMAN-06136: ORACLE error from auxiliary database: ORA-19755: could not open change tracking file

ORA-19750: change tracking file: '/oracle/LIVE/oradata/LIVE/change_tracking.ctf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3




and



DBGSQL:     AUXILIARY> begin :ofname := sys.dbms_backup_restore.convertFileName( fname   =>   :ifname, ftype   =>   :iftype, osftype =>   TRUE); end;
DBGSQL:        sqlcode = 6502
DBGSQL:         B :ofname = NULL
DBGSQL:         B :ifname =
DBGSQL:         B :iftype = 2
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/19/2014 11:18:50
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error
 




There were other but i got sick of cutting and pasting them all and actually lost track of the number of things i changed to make this work.

In the end this is a summary of what i had to do:

1) create a dummy (empty) block change tracking file in the same location as it wanted it in live (and usage of db_file convery in any way at all messes up with omf somehow and it just wont work)

2) add the nofilenamecheck clause to stop it throwing an error of reusing the live block change tracking file path

3) dont use an spfile

4) add the following line to the pfile "_compression_compatibility"='11.2.0'

After all this is in place and the command looks like this then everything seems to work

run
{set dbid 12345678;
set until time "to_date('16-Jan-2014 09:00:00','dd-mon-yyyy hh24:mi:ss')";
allocate auxiliary channel ch1 type 'SBT_TAPE' parms'ENV=(TDPO_OPTFILE=/tsm/LIVEP/conf/tdpo.opt)';
duplicate database 'LIVEP' to 'TEST5'  nofilenamecheck;
}


This results in a very long logfile of commands looking like this

[oracle@server]:TEST5:/oracle/home/oracle# rman rcvcat= rman/rman@cat auxiliary=/

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jan 19 11:21:21 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database
connected to auxiliary database: TEST5 (not mounted)

RMAN> run
2> {set dbid 12345678;
set until time "to_date('16-Jan-2014 09:00:00','dd-mon-yyyy hh24:mi:ss')";
allocate auxiliary channel ch1 type 'SBT_TAPE' parms'ENV=(TDPO_OPTFILE=/tsm/LIVEP/conf/tdpo.opt)';
duplicate database 'LIVEP' to 'TEST5'  nofilenamecheck;
3> 4> 5> 6> }

executing command: SET DBID

executing command: SET until clause

allocated channel: ch1
channel ch1: SID=760 device type=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

Starting Duplicate Db at 19-JAN-14

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1369579520 bytes

Fixed Size                     2226352 bytes
Variable Size                335546192 bytes
Database Buffers            1006632960 bytes
Redo Buffers                  25174016 bytes
allocated channel: ch1
channel ch1: SID=760 device type=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

contents of Memory Script:
{
   set until scn  260133893;
   sql clone "alter system set  control_files =
  ''/oracle/TEST5/oradata/TEST5/controlfile/o1_mf_7dsclzhs_.ctl'', ''/oracle/TEST5/recovery_area/TEST5/controlfile/o1_mf_7dsclzpw_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''LIVEP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''TEST5'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set  control_files =   ''/oracle/TEST5/oradata/TEST5/controlfile/o1_mf_7dsclzhs_.ctl'', ''/oracle/TEST5/recovery_area/TEST5/controlfile/o1_mf_7dsclzpw_.ctl'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''LIVEP'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''TEST5'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1369579520 bytes

Fixed Size                     2226352 bytes
Variable Size                352323408 bytes
Database Buffers             989855744 bytes
Redo Buffers                  25174016 bytes
allocated channel: ch1
channel ch1: SID=760 device type=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

Starting restore at 19-JAN-14

channel ch1: starting datafile backup set restore
channel ch1: restoring control file
channel ch1: reading from backup piece cf_auto_c-136557587-20140115-2c
channel ch1: piece handle=cf_auto_c-136557587-20140115-2c tag=TAG20140115T221233
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
output file name=/oracle/TEST5/oradata/TEST5/controlfile/o1_mf_7dsclzhs_.ctl
output file name=/oracle/TEST5/recovery_area/TEST5/controlfile/o1_mf_7dsclzpw_.ctl
Finished restore at 19-JAN-14

database mounted

contents of Memory Script:
{
   set until scn  260133893;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  9 to new;
   set newname for clone datafile  10 to new;
   set newname for clone datafile  11 to new;
   set newname for clone datafile  12 to new;
   set newname for clone datafile  13 to new;
   set newname for clone datafile  14 to new;
   set newname for clone datafile  15 to new;
   set newname for clone datafile  16 to new;
   restore
   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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 19-JAN-14

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00007 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885366_20140115_42323.rman
channel ch1: piece handle=database_online_LIVEP_836885366_20140115_42323.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:02:45
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00009 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885482_20140115_42324.rman
channel ch1: piece handle=database_online_LIVEP_836885482_20140115_42324.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:01:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00010 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885537_20140115_42325.rman
channel ch1: piece handle=database_online_LIVEP_836885537_20140115_42325.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:01:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00002 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_sysaux_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885582_20140115_42326.rman
channel ch1: piece handle=database_online_LIVEP_836885582_20140115_42326.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:55
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_system_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885617_20140115_42327.rman
channel ch1: piece handle=database_online_LIVEP_836885617_20140115_42327.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00008 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885632_20140115_42328.rman
channel ch1: piece handle=database_online_LIVEP_836885632_20140115_42328.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:25
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00016 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_dbspi_us_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885647_20140115_42329.rman
channel ch1: piece handle=database_online_LIVEP_836885647_20140115_42329.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00003 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_undotbs1_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885654_20140115_42330.rman
channel ch1: piece handle=database_online_LIVEP_836885654_20140115_42330.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00015 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885670_20140115_42331.rman
channel ch1: piece handle=database_online_LIVEP_836885670_20140115_42331.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00005 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_eisdba_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885677_20140115_42332.rman
channel ch1: piece handle=database_online_LIVEP_836885677_20140115_42332.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00012 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885691_20140115_42334.rman
channel ch1: piece handle=database_online_LIVEP_836885691_20140115_42334.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:25
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00011 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885684_20140115_42333.rman
channel ch1: piece handle=database_online_LIVEP_836885684_20140115_42333.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00013 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885698_20140115_42335.rman
channel ch1: piece handle=database_online_LIVEP_836885698_20140115_42335.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00004 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885715_20140115_42338.rman
channel ch1: piece handle=database_online_LIVEP_836885715_20140115_42338.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00014 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885705_20140115_42336.rman
channel ch1: piece handle=database_online_LIVEP_836885705_20140115_42336.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00006 to /oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_%u_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836885708_20140115_42337.rman
channel ch1: piece handle=database_online_LIVEP_836885708_20140115_42337.rman tag=TAG20140115T040925
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
Finished restore at 19-JAN-14

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=22 STAMP=837257450 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_system_9fqb6b42_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=23 STAMP=837257450 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_sysaux_9fqb4mxz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=24 STAMP=837257450 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_undotbs1_9fqb81wr_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=25 STAMP=837257451 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_9fqbbg0v_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=26 STAMP=837257451 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_eisdba_9fqb8x9j_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=27 STAMP=837257451 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqbbq97_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=28 STAMP=837257451 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fq9v6l5_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=29 STAMP=837257452 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fqb6sc5_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=30 STAMP=837257452 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb005j_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=31 STAMP=837257452 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb2pw7_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=32 STAMP=837257452 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9z62_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=33 STAMP=837257453 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9qk8_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=34 STAMP=837257453 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbb6v3_.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=35 STAMP=837257453 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbbjm1_.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=36 STAMP=837257453 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqb8hz0_.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=37 STAMP=837257454 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_dbspi_us_9fqb7lkk_.dbf

contents of Memory Script:
{
   set until time  "16-JAN-14";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 19-JAN-14
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fq9v6l5_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950074_20140115_42374.rman
channel ch1: piece handle=database_online_LIVEP_836950074_20140115_42374.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:25
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_sysaux_9fqb4mxz_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950095_20140115_42377.rman
channel ch1: piece handle=database_online_LIVEP_836950095_20140115_42377.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:25
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb005j_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950081_20140115_42375.rman
channel ch1: piece handle=database_online_LIVEP_836950081_20140115_42375.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00010: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb2pw7_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950088_20140115_42376.rman
channel ch1: piece handle=database_online_LIVEP_836950088_20140115_42376.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_system_9fqb6b42_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950110_20140115_42378.rman
channel ch1: piece handle=database_online_LIVEP_836950110_20140115_42378.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fqb6sc5_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950117_20140115_42379.rman
channel ch1: piece handle=database_online_LIVEP_836950117_20140115_42379.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00016: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_dbspi_us_9fqb7lkk_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950125_20140115_42380.rman
channel ch1: piece handle=database_online_LIVEP_836950125_20140115_42380.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_undotbs1_9fqb81wr_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950132_20140115_42381.rman
channel ch1: piece handle=database_online_LIVEP_836950132_20140115_42381.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00015: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqb8hz0_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950139_20140115_42382.rman
channel ch1: piece handle=database_online_LIVEP_836950139_20140115_42382.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_eisdba_9fqb8x9j_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950146_20140115_42383.rman
channel ch1: piece handle=database_online_LIVEP_836950146_20140115_42383.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00011: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9z62_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950161_20140115_42384.rman
channel ch1: piece handle=database_online_LIVEP_836950161_20140115_42384.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqbbq97_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950190_20140115_42388.rman
channel ch1: piece handle=database_online_LIVEP_836950190_20140115_42388.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00012: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9qk8_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950168_20140115_42385.rman
channel ch1: piece handle=database_online_LIVEP_836950168_20140115_42385.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_9fqbbg0v_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950197_20140115_42389.rman
channel ch1: piece handle=database_online_LIVEP_836950197_20140115_42389.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00013: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbb6v3_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950175_20140115_42386.rman
channel ch1: piece handle=database_online_LIVEP_836950175_20140115_42386.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00014: /oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbbjm1_.dbf
channel ch1: reading from backup piece database_online_LIVEP_836950183_20140115_42387.rman
channel ch1: piece handle=database_online_LIVEP_836950183_20140115_42387.rman tag=TAG20140115T220648
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03

starting media recovery

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=44428
channel ch1: restoring archived log
archived log thread=1 sequence=44429
channel ch1: restoring archived log
archived log thread=1 sequence=44430
channel ch1: restoring archived log
archived log thread=1 sequence=44431
channel ch1: reading from backup piece redolog_LIVEP_836958654_20140116_42393_1.rman
channel ch1: piece handle=redolog_LIVEP_836958654_20140116_42393_1.rman tag=TAG20140116T003045
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:45
archived log file name=/oracle/TEST5/recovery_area/TEST5/archivelog/2014_01_19/o1_mf_1_44428_9fqbol90_.arc thread=1 sequence=44428
channel clone_default: deleting archived log(s)
archived log file name=/oracle/TEST5/recovery_area/TEST5/archivelog/2014_01_19/o1_mf_1_44428_9fqbol90_.arc RECID=73551 STAMP=837257796
archived log file name=/oracle/TEST5/recovery_area/TEST5/archivelog/2014_01_19/o1_mf_1_44429_9fqbolbt_.arc thread=1 sequence=44429
channel clone_default: deleting archived log(s)
archived log file name=/oracle/TEST5/recovery_area/TEST5/archivelog/2014_01_19/o1_mf_1_44429_9fqbolbt_.arc RECID=73550 STAMP=837257795
archived log file name=/oracle/TEST5/recovery_area/TEST5/archivelog/2014_01_19/o1_mf_1_44430_9fqboldz_.arc thread=1 sequence=44430
channel clone_default: deleting archived log(s)
archived log file name=/oracle/TEST5/recovery_area/TEST5/archivelog/2014_01_19/o1_mf_1_44430_9fqboldz_.arc RECID=73549 STAMP=837257795
archived log file name=/oracle/TEST5/recovery_area/TEST5/archivelog/2014_01_19/o1_mf_1_44431_9fqbol63_.arc thread=1 sequence=44431
channel clone_default: deleting archived log(s)
archived log file name=/oracle/TEST5/recovery_area/TEST5/archivelog/2014_01_19/o1_mf_1_44431_9fqbol63_.arc RECID=73552 STAMP=837257796
media recovery complete, elapsed time: 00:00:09
Finished recover at 19-JAN-14
Oracle instance started

Total System Global Area    1369579520 bytes

Fixed Size                     2226352 bytes
Variable Size                369100624 bytes
Database Buffers             973078528 bytes
Redo Buffers                  25174016 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TEST5'' 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;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TEST5'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1369579520 bytes

Fixed Size                     2226352 bytes
Variable Size                369100624 bytes
Database Buffers             973078528 bytes
Redo Buffers                  25174016 bytes
allocated channel: ch1
channel ch1: SID=760 device type=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST5" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY     2336
 LOGFILE
  GROUP  1 ( '/oracle/LIVEP/oradata/LIVEP/onlinelog/o1_mf_1_7clk43jb_.log', '/oracle/LIVEP/recovery_area/LIVEP/onlinelog/o1_mf_1_7clk4432_.log' ) SIZE 100 M  REUSE,
  GROUP  2 ( '/oracle/LIVEP/oradata/LIVEP/onlinelog/o1_mf_2_7clk44ok_.log', '/oracle/LIVEP/recovery_area/LIVEP/onlinelog/o1_mf_2_7clk458b_.log' ) SIZE 100 M  REUSE,
  GROUP  3 ( '/oracle/LIVEP/oradata/LIVEP/onlinelog/o1_mf_3_7clk45t9_.log', '/oracle/LIVEP/recovery_area/LIVEP/onlinelog/o1_mf_3_7clk46fq_.log' ) SIZE 100 M  REUSE
 DATAFILE
  '/oracle/TEST5/oradata/TEST5/datafile/o1_mf_system_9fqb6b42_.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   catalog clone datafilecopy  "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_sysaux_9fqb4mxz_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_undotbs1_9fqb81wr_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_9fqbbg0v_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_eisdba_9fqb8x9j_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqbbq97_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fq9v6l5_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fqb6sc5_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb005j_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb2pw7_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9z62_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9qk8_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbb6v3_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbbjm1_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqb8hz0_.dbf",
 "/oracle/TEST5/oradata/TEST5/datafile/o1_mf_dbspi_us_9fqb7lkk_.dbf";
   switch clone datafile all;
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_sysaux_9fqb4mxz_.dbf RECID=1 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_undotbs1_9fqb81wr_.dbf RECID=2 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_9fqbbg0v_.dbf RECID=3 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_eisdba_9fqb8x9j_.dbf RECID=4 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqbbq97_.dbf RECID=5 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fq9v6l5_.dbf RECID=6 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fqb6sc5_.dbf RECID=7 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb005j_.dbf RECID=8 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb2pw7_.dbf RECID=9 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9z62_.dbf RECID=10 STAMP=837257831
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9qk8_.dbf RECID=11 STAMP=837257832
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbb6v3_.dbf RECID=12 STAMP=837257832
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbbjm1_.dbf RECID=13 STAMP=837257832
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqb8hz0_.dbf RECID=14 STAMP=837257832
cataloged datafile copy
datafile copy file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_dbspi_us_9fqb7lkk_.dbf RECID=15 STAMP=837257832

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_sysaux_9fqb4mxz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_undotbs1_9fqb81wr_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_9fqbbg0v_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_eisdba_9fqb8x9j_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqbbq97_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fq9v6l5_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_da_9fqb6sc5_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb005j_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_data_zam_9fqb2pw7_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=837257831 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9z62_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=11 STAMP=837257832 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_index_za_9fqb9qk8_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=12 STAMP=837257832 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbb6v3_.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=13 STAMP=837257832 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_bofit_in_9fqbbjm1_.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=14 STAMP=837257832 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_users_ti_9fqb8hz0_.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=15 STAMP=837257832 file name=/oracle/TEST5/oradata/TEST5/datafile/o1_mf_dbspi_us_9fqb7lkk_.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database force logging
Executing: alter database enable block change tracking

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 19-JAN-14
released channel: ch1

RMAN>

RMAN> exit

8 comments:

  1. Solution to block change tracking problem during duplicate :
    - During the datafile restore (after CF restore) , connect to DB (in mount state).
    - Execute : alter database disable block change tracking;

    ReplyDelete
  2. Hi,
    Thanks for that - vey neat. So you create a second session while the datafiles are being restored and in that just change the block change tracking option in the already restored controlfile using the standard command. In most cases you should have plenty of time to do that while the datafiles are restoring.

    Cheers,
    Rich

    ReplyDelete
  3. Hi Richard -
    Just wanted to say that Anonymous' suggestion of disabling block change tracking during the duplicate process resolved the ORA-19755 error that my team was encountering. Most other suggestions out there were not working because we needed to recover to a point in time (recovering logically corrupt data). We tried to put a dummy file in place, but were having issues doing that with ASM and OMF. For reference, this worked on 11.2.0.3.4.

    I think the suggestion is worth putting in a separate post (or an edit to your existing). Two days of research, and this is the only mention I saw of this tip. Just glad I read the comments section!

    Kevin

    ReplyDelete
  4. great post !! Thanks to Anonymous for the comments. I also struggled a lot with this error. After that I had to duplicate manually (long process). After disabling block change tracking, during restore worked perfect !!

    ReplyDelete
  5. Hello;

    We can use SET NEWNAME FOR BLOCK CHANGE TRACKING FILE TO '+DATA01' for version =>11.2

    Thierry

    ReplyDelete
  6. There appears to be no command like "SET NEWNAME FOR BLOCK CHANGE TRACKING FILE TO ....", even in 11.2.

    ReplyDelete
  7. Add the following to the db_file_name_convert
    'source db tracking file','new db tracking file'
    Specify the complete path and name

    ReplyDelete