Hi,
I have 2 databases SRCDB and DESTDB in 11.2.0.4 version running on the same 2 node RAC cluster.
I am trying to duplicate and refresh DESTDB from SRCDB.
I'm trying to refresh with the below command. I created a manual listener DESTDB1_refresh for duplicate.
connect target cl_dba/mypass@SRCDB1
connect auxiliary cl_dba/mypass@DESTDB1_refresh
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/var/opt/tsm/tdpoSRCDB1.opt)';
run
{
SET UNTIL TIME 'TRUNC(SYSDATE-3) - 1/(24*60)'; ######## Translates to 30112016 23:59:00 when run on 4/12/2016
DUPLICATE TARGET DATABASE TO DESTDB
spfile parameter_value_convert 'SRCDB', 'DESTDB', 'srcdb', 'destdb'
set cluster_database 'FALSE'
set audit_file_dest '%%to_adump%%'
set db_file_name_convert '+DATA/SRCDB', '+DATA/DESTDB', '+RECO/SRCDB', '+RECO/DESTDB'
set log_file_name_convert '+DATA/SRCDB', '+DATA/DESTDB', '+RECO/SRCDB', '+RECO/DESTDB';
}
###############################################
LOG:
###############################################
RMAN> connect target *
2> connect auxiliary *
3> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
4> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/var/opt/tsm/tdpoSRCDB1.opt)';
5> run
6> {
7> SET UNTIL TIME 'TRUNC(SYSDATE-3) - 1/(24*60)';
8> DUPLICATE TARGET DATABASE TO DESTDB
9> spfile parameter_value_convert 'SRCDB', 'DESTDB', 'srcdb', 'destdb'
10> set cluster_database 'FALSE'
11> set audit_file_dest '%%to_adump%%'
12> set db_file_name_convert '+DATA/srcdb', '+DATA/destdb', '+RECO/srcdb', '+RECO/destdb'
13> set log_file_name_convert '+DATA/srcdb', '+DATA/destdb', '+RECO/srcdb', '+RECO/destdb';
14> }
15> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
16> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/refreshes/RMAN/Backups/%d/%d_full_backup_%T_%s_%p' MAXPIECESIZE 8 G;
17>
connected to target database: SRCDB (DBID=3426971342)
connected to auxiliary database: DESTDB (not mounted)
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/var/opt/tsm/tdpoSRCDB2.opt)';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/var/opt/tsm/tdpoSRCDB1.opt)';
new RMAN configuration parameters are successfully stored
executing command: SET until clause
Starting Duplicate Db at 04/12/2016 04:57:44
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=534 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=686 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=838 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=914 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=990 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=1066 device type=DISK
contents of Memory Script:
{
set until scn 2830983562430;
restore clone spfile to '+DATA/destdb/spfiledestdb.ora';
sql clone "alter system set spfile= ''+DATA/destdb/spfiledestdb.ora''";
}
executing Memory Script
executing command: SET until clause
Starting restore at 04/12/2016 04:58:05
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
WARNING: A restore time was estimated based on the supplied UNTIL SCN
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring SPFILE
output file name=+DATA/destdb/spfiledestdb.ora
channel ORA_AUX_SBT_TAPE_1: reading from backup piece CTR-c-3426971342-20161130-0b.bck
channel ORA_AUX_SBT_TAPE_1: piece handle=CTR-c-3426971342-20161130-0b.bck tag=TAG20161130T225440
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
Finished restore at 04/12/2016 04:58:10
sql statement: alter system set spfile= ''+DATA/destdb/spfiledestdb.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''DESTDB'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set control_files =
''+RECO/destdb/controlfile/current.256.882217185'', ''+DATA/destdb/controlfile/current.256.882217185'' comment=
'''' scope=spfile";
sql clone "alter system set service_names =
''DESTDB_LB_DEFAULT, SRCDB_DTP1_DEFAULT, SRCDB_S1_DEFAULT'' comment=
'''' scope=spfile";
sql clone "alter system set cluster_database =
FALSE comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''%%to_adump%%'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+DATA/srcdb'', ''+DATA/destdb'', ''+RECO/srcdb'', ''+RECO/destdb'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+DATA/srcdb'', ''+DATA/destdb'', ''+RECO/srcdb'', ''+RECO/destdb'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DESTDB'' comment= ''duplicate'' scope=spfile
sql statement: alter system set control_files = ''+RECO/destdb/controlfile/current.256.882217185'', ''+DATA/destdb/controlfile/current.256.882217185'' comment= ''''
scope=spfile
sql statement: alter system set service_names = ''DESTDB_LB_DEFAULT, SRCDB_DTP1_DEFAULT, SRCDB_S1_DEFAULT'' comment= '''' scope=spfile
sql statement: alter system set cluster_database = FALSE comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''%%to_adump%%'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+DATA/srcdb'', ''+DATA/destdb'', ''+RECO/srcdb'', ''+RECO/destdb'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+DATA/srcdb'', ''+DATA/destdb'', ''+RECO/srcdb'', ''+RECO/destdb'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 6413680640 bytes
Fixed Size 2265224 bytes
Variable Size 5318381432 bytes
Database Buffers 1073741824 bytes
Redo Buffers 19292160 bytes
contents of Memory Script:
{
set until scn 2830983562430;
sql clone "alter system set control_files =
''+RECO/destdb/controlfile/current.6120.929545915'', ''+DATA/destdb/controlfile/current.390.929551763'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''SRCDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DESTDB'' 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 = ''+RECO/destdb/controlfile/current.6120.929545915'', ''+DATA/destdb/controlfile/current.390.929551763'' comment= ''
Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''SRCDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 6413680640 bytes
Fixed Size 2265224 bytes
Variable Size 5318381432 bytes
Database Buffers 1073741824 bytes
Redo Buffers 19292160 bytes
Starting restore at 04/12/2016 04:58:57
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=534 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=686 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=838 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=914 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=990 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=1066 device type=DISK
Oracle instance started
Total System Global Area 6413680640 bytes
Fixed Size 2265224 bytes
Variable Size 5318381432 bytes
Database Buffers 1073741824 bytes
Redo Buffers 19292160 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DESTDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
}
executing Memory Script
sql statement: alter system set db_name = ''DESTDB'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/04/2016 04:59:31
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-06024: no backup or copy of the control file found to restore
Recovery Manager complete.
###########################################
Here is the backup details from SRCDB
###########################################
RMAN> list backup of database summary;
.
.
.
.
13435 B 0 A SBT_TAPE 24112016 23:22:59 1 1 NO TAG20161124T230820
13436 B 0 A SBT_TAPE 24112016 23:24:28 1 1 NO TAG20161124T230820
13474 B 1 A SBT_TAPE 25112016 23:12:54 1 1 NO TAG20161125T231021
13475 B 1 A SBT_TAPE 25112016 23:12:56 1 1 NO TAG20161125T231021
13510 B 1 A SBT_TAPE 26112016 23:12:59 1 1 NO TAG20161126T230957
13511 B 1 A SBT_TAPE 26112016 23:13:02 1 1 NO TAG20161126T230957
13549 B 1 A SBT_TAPE 27112016 23:11:54 1 1 NO TAG20161127T230911
13550 B 1 A SBT_TAPE 27112016 23:11:57 1 1 NO TAG20161127T230911
13585 B 0 A SBT_TAPE 28112016 23:22:47 1 1 NO TAG20161128T230816
13586 B 0 A SBT_TAPE 28112016 23:24:13 1 1 NO TAG20161128T230816
13624 B 1 A SBT_TAPE 29112016 23:12:50 1 1 NO TAG20161129T231033
13625 B 1 A SBT_TAPE 29112016 23:13:06 1 1 NO TAG20161129T231033
13705 B 0 A SBT_TAPE 01122016 23:23:39 1 1 NO TAG20161201T230823
13706 B 0 A SBT_TAPE 01122016 23:25:15 1 1 NO TAG20161201T230823
13761 B 1 A SBT_TAPE 02122016 23:11:42 1 1 NO TAG20161202T230936
13762 B 1 A SBT_TAPE 02122016 23:12:06 1 1 NO TAG20161202T230936
13800 B 1 A SBT_TAPE 03122016 23:12:40 1 1 NO TAG20161203T230847
13801 B 1 A SBT_TAPE 03122016 23:13:35 1 1 NO TAG20161203T230847
.
.
.
RMAN> list backup of controlfile summary;
.
.
.
13631 B F A SBT_TAPE 30112016 00:11:26 1 1 NO TAG20161130T001120
13634 B F A SBT_TAPE 30112016 02:24:53 1 1 NO TAG20161130T022450
13637 B F A SBT_TAPE 30112016 04:24:25 1 1 NO TAG20161130T042423
13640 B F A SBT_TAPE 30112016 06:24:24 1 1 NO TAG20161130T062421
13643 B F A SBT_TAPE 30112016 08:24:22 1 1 NO TAG20161130T082418
13646 B F A SBT_TAPE 30112016 10:24:13 1 1 NO TAG20161130T102411
13649 B F A SBT_TAPE 30112016 12:24:14 1 1 NO TAG20161130T122411
13652 B F A SBT_TAPE 30112016 14:39:20 1 1 NO TAG20161130T143917
13655 B F A SBT_TAPE 30112016 16:39:18 1 1 NO TAG20161130T163915
13658 B F A SBT_TAPE 30112016 18:39:31 1 1 NO TAG20161130T183928
13661 B F A SBT_TAPE 30112016 20:54:34 1 1 NO TAG20161130T205430
13664 B F A SBT_TAPE 30112016 22:54:43 1 1 NO TAG20161130T225440
13665 B F X DISK 30112016 23:19:11 1 1 NO TAG20161130T231909
13670 B F A SBT_TAPE 01122016 00:54:53 1 1 NO TAG20161201T005450
13673 B F A SBT_TAPE 01122016 02:54:34 1 1 NO TAG20161201T025431
13676 B F A SBT_TAPE 01122016 04:54:38 1 1 NO TAG20161201T045435
.
.
.
RMAN> list backup of controlfile;
.
.
.
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
13661 Full 33.75M SBT_TAPE 00:00:04 30112016 20:54:34
BP Key: 13675 Status: AVAILABLE Compressed: NO Tag: TAG20161130T205430
Handle: CTR-c-3426971342-20161130-0a.bck Media: 6697
Control File Included: Ckp SCN: 2830983434044 Ckp time: 30112016 20:54:30
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
13664 Full 33.75M SBT_TAPE 00:00:03 30112016 22:54:43
BP Key: 13678 Status: AVAILABLE Compressed: NO Tag: TAG20161130T225440
Handle: CTR-c-3426971342-20161130-0b.bck Media: 12855
Control File Included: Ckp SCN: 2830985950605 Ckp time: 30112016 22:54:40
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
13665 Full 33.73M DISK 00:00:02 30112016 23:19:11
BP Key: 13679 Status: EXPIRED Compressed: NO Tag: TAG20161130T231909
Piece Name: /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/c-3426971342-20161130-0c
Control File Included: Ckp SCN: 2830983558998 Ckp time: 30112016 23:19:09
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
13670 Full 33.75M SBT_TAPE 00:00:03 01122016 00:54:53
BP Key: 13684 Status: AVAILABLE Compressed: NO Tag: TAG20161201T005450
Handle: CTR-c-3426971342-20161201-00.bck Media: 20375
Control File Included: Ckp SCN: 2830993343627 Ckp time: 01122016 00:54:50
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
13673 Full 33.75M SBT_TAPE 00:00:03 01122016 02:54:34
BP Key: 13687 Status: AVAILABLE Compressed: NO Tag: TAG20161201T025431
Handle: CTR-c-3426971342-20161201-01.bck Media: 14717
Control File Included: Ckp SCN: 2830993377544 Ckp time: 01122016 02:54:31
.
.
.
Something to note is that the SRCDB was restored from a restorepoint(flashback database) a few minutes before our PIT of duplicate.
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SRCDB 3426971342 PARENT 1 24062015 14:08:35
2 2 SRCDB 3426971342 PARENT 2781938798942 01102015 10:58:27
3 3 SRCDB 3426971342 PARENT 2796833087331 05032016 16:08:16
4 4 SRCDB 3426971342 PARENT 2799433592704 19032016 12:45:31
5 5 SRCDB 3426971342 PARENT 2802762183344 16042016 17:42:04
6 6 SRCDB 3426971342 PARENT 2822012075552 31082016 00:07:41
7 7 SRCDB 3426971342 PARENT 2830899056492 29112016 23:31:44
8 8 SRCDB 3426971342 CURRENT 2830983557110 30112016 23:16:07
RMAN>
Can you please help on how to attempt the duplicate? I'm unable to put SRCDB to previous incarnation as it is a critical PROD server.