A reader, April 15, 2016 - 9:38 pm UTC
Thanks Connor , for your time and help.
I checked these MOS docs . Still , it does not help.
BTW , this is on Windows Server.
For me , SCN based / time based recovery works fine with no issues. Only the regular one as shown in the question has issues.
I also tried , by adding NOREDO in the duplicate clause ; for that I got ORA-01152 at the end of the process.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/13/2016 17:46:47
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01152: file 5 was not restored from a sufficiently old backup
ORA-01110: data file 5: 'E:\ORADATA\DBDUP\UNDOTBS01.DBF'
April 16, 2016 - 2:40 am UTC
Is this single instance or RAC ?
A reader, April 16, 2016 - 10:56 am UTC
Single instance.
12.1.0.2 on windows 2012 64 bit
April 18, 2016 - 3:31 am UTC
Can you post the entire duplicate rman log please.
Thanks,
Connor
A reader, April 18, 2016 - 8:46 pm UTC
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 11 18:13:43 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUPPROD (DBID=8431227017)
using target database control file instead of recovery catalog
connected to auxiliary database: DBDUP (not mounted)
RMAN> run {
2>
3> allocate auxiliary channel c89 type sbt PARMS="SBT_LIBRARY=C:\PROGRA~1\avs\bin\orasbt64.dll" send '"--flagfile=d:\dup.txt" "--bindir=C:\PROGRA~1\avs\bin" "--logfile=F:\logs\DBDUP_c89.log"';
4> duplicate target database to DBDUP NOFILENAMECHECK
5> spfile
6> set control_files='E:\oradata\DBDUP\CONTROLFILE\CONTROL1.CTL','E:\oradata\DBDUP\CONTROLFILE\CONTROL2.CTL'
7> set db_file_name_convert='E:\oradata\DUPPROD','E:\oradata\DBDUP'
8> set log_file_name_convert='E:\oradata\DUPPROD','E:\oradata\DBDUP'
9> set db_recovery_file_dest='F:\FRA\DBDUP'
10> set log_archive_dest_2='location=E:\oradata\DBDUP\LOGFILE'
11> set log_archive_dest_3='location=E:\oradata\DBDUP\LOGFILE2'
12> set diagnostic_dest='D:\app\oracle\'
13> set local_listener='';
14> release channel c89;
15> }
16>
allocated channel: c89
channel c89: SID=1702 device type=SBT_TAPE
channel c89: EMC|Avamar (avtar backup)
Starting Duplicate Db at 11-APR-16
current log archived
contents of Memory Script:
{
set until scn 2450561;
restore clone spfile to 'D:\APP\oracle\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEDBDUP.ORA';
sql clone "alter system set spfile= ''D:\APP\oracle\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEDBDUP.ORA''";
}
executing Memory Script
executing command: SET until clause
Starting restore at 11-APR-16
WARNING: A restore time was estimated based on the supplied UNTIL SCN
channel c89: starting datafile backup set restore
channel c89: restoring SPFILE
output file name=D:\APP\oracle\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEDBDUP.ORA
channel c89: reading from backup piece c-3431927017-20160411-01
channel c89: piece handle=c-3431927017-20160411-01 tag=TAG20160411T175543
channel c89: restored backup piece 1
channel c89: restore complete, elapsed time: 00:00:15
Finished restore at 11-APR-16
sql statement: alter system set spfile= ''D:\APP\oracle\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEDBDUP.ORA''
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBDUP'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set control_files =
''E:\oradata\DBDUP\CONTROLFILE\CONTROL1.CTL'', ''E:\oradata\DBDUP\CONTROLFILE\CONTROL2.CTL'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''E:\oradata\DUPPROD'', ''E:\oradata\DBDUP'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''E:\oradata\DUPPROD'', ''E:\oradata\DBDUP'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''F:\FRA\DBDUP'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''location=E:\oradata\DBDUP\LOGFILE'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_3 =
''location=E:\oradata\DBDUP\LOGFILE2'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''D:\app\oracle\'' comment=
'''' scope=spfile";
sql clone "alter system set local_listener =
'''' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DBDUP'' comment= ''duplicate'' scope=spfile
sql statement: alter system set control_files = ''E:\oradata\DBDUP\CONTROLFILE\CONTROL1.CTL'', ''E:\oradata\DBDUP\CONTROLFILE\CONTROL2.CTL'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''E:\oradata\DUPPROD'', ''E:\oradata\DBDUP'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''E:\oradata\DUPPROD'', ''E:\oradata\DBDUP'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''F:\FRA\DBDUP'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''location=E:\oradata\DBDUP\LOGFILE'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_3 = ''location=E:\oradata\DBDUP\LOGFILE2'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''D:\app\oracle\'' comment= '''' scope=spfile
sql statement: alter system set local_listener = '''' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2701131776 bytes
Fixed Size 3835736 bytes
Variable Size 1157630120 bytes
Database Buffers 1526726656 bytes
Redo Buffers 12939264 bytes
allocated channel: c89
channel c89: SID=436 device type=SBT_TAPE
channel c89: EMC|Avamar (avtar backup)
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPPROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DBDUP'' 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
sql statement: alter system set db_name = ''DUPPROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DBDUP'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2701131776 bytes
Fixed Size 3835736 bytes
Variable Size 1157630120 bytes
Database Buffers 1526726656 bytes
Redo Buffers 12939264 bytes
allocated channel: c89
channel c89: SID=436 device type=SBT_TAPE
channel c89: EMC|Avamar (avtar backup)
Starting restore at 11-APR-16
channel c89: starting datafile backup set restore
channel c89: restoring control file
channel c89: reading from backup piece c-3431927017-20160411-01
channel c89: piece handle=c-3431927017-20160411-01 tag=TAG20160411T175543
channel c89: restored backup piece 1
channel c89: restore complete, elapsed time: 00:00:07
output file name=E:\ORADATA\DBDUP\CONTROLFILE\CONTROL1.CTL
output file name=E:\ORADATA\DBDUP\CONTROLFILE\CONTROL2.CTL
Finished restore at 11-APR-16
database mounted
Using previous duplicated file E:\ORADATA\DBDUP\SYSTEM01.DBF for datafile 1 with checkpoint SCN of 2448392
Using previous duplicated file E:\ORADATA\DBDUP\SYSAUX01.DBF for datafile 3 with checkpoint SCN of 2448394
Using previous duplicated file E:\ORADATA\DBDUP\UNDOTBS01.DBF for datafile 5 with checkpoint SCN of 2448393
Using previous duplicated file E:\ORADATA\DBDUP\USERS01.DBF for datafile 6 with checkpoint SCN of 2448395
contents of Memory Script:
{
set until scn 2450561;
set newname for datafile 1 to
"E:\ORADATA\DBDUP\SYSTEM01.DBF";
set newname for datafile 3 to
"E:\ORADATA\DBDUP\SYSAUX01.DBF";
set newname for datafile 5 to
"E:\ORADATA\DBDUP\UNDOTBS01.DBF";
set newname for datafile 6 to
"E:\ORADATA\DBDUP\USERS01.DBF";
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
contents of Memory Script:
{
catalog clone datafilecopy "E:\ORADATA\DBDUP\SYSTEM01.DBF",
"E:\ORADATA\DBDUP\SYSAUX01.DBF",
"E:\ORADATA\DBDUP\UNDOTBS01.DBF",
"E:\ORADATA\DBDUP\USERS01.DBF";
switch clone datafile 1 to datafilecopy
"E:\ORADATA\DBDUP\SYSTEM01.DBF";
switch clone datafile 3 to datafilecopy
"E:\ORADATA\DBDUP\SYSAUX01.DBF";
switch clone datafile 5 to datafilecopy
"E:\ORADATA\DBDUP\UNDOTBS01.DBF";
switch clone datafile 6 to datafilecopy
"E:\ORADATA\DBDUP\USERS01.DBF";
}
executing Memory Script
cataloged datafile copy
datafile copy file name=E:\ORADATA\DBDUP\SYSTEM01.DBF RECID=1 STAMP=908907346
cataloged datafile copy
datafile copy file name=E:\ORADATA\DBDUP\SYSAUX01.DBF RECID=2 STAMP=908907346
cataloged datafile copy
datafile copy file name=E:\ORADATA\DBDUP\UNDOTBS01.DBF RECID=3 STAMP=908907346
cataloged datafile copy
datafile copy file name=E:\ORADATA\DBDUP\USERS01.DBF RECID=4 STAMP=908907346
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=908907346 file name=E:\ORADATA\DBDUP\SYSTEM01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=908907346 file name=E:\ORADATA\DBDUP\SYSAUX01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=908907346 file name=E:\ORADATA\DBDUP\UNDOTBS01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=908907346 file name=E:\ORADATA\DBDUP\USERS01.DBF
contents of Memory Script:
{
set until scn 2450561;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 11-APR-16
starting media recovery
Oracle instance started
Total System Global Area 2701131776 bytes
Fixed Size 3835736 bytes
Variable Size 1157630120 bytes
Database Buffers 1526726656 bytes
Redo Buffers 12939264 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBDUP'' 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 = ''DBDUP'' 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 04/11/2016 18:16:32
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 17 and starting SCN of 2449847 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 16 and starting SCN of 2449583 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 2448619 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 14 and starting SCN of 2448447 found to restore
Recovery Manager complete.
A reader, April 18, 2016 - 8:47 pm UTC
Starting Duplicate Db at 11-APR-16
current log archived
contents of Memory Script:
{
set until scn 2450561;
restore clone spfile to 'D:\APP\oracle\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEDBDUP.ORA';
sql clone "alter system set spfile= ''D:\APP\oracle\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEDBDUP.ORA''";
}
executing Memory Script
executing command: SET until clause
Starting restore at 11-APR-16
WARNING: A restore time was estimated based on the supplied UNTIL SCN
channel c89: starting datafile backup set restore
channel c89: restoring SPFILE
output file name=D:\APP\oracle\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEDBDUP.ORA
channel c89: reading from backup piece c-3431927017-20160411-01
channel c89: piece handle=c-3431927017-20160411-01 tag=TAG20160411T175543
channel c89: restored backup piece 1
channel c89: restore complete, elapsed time: 00:00:15
Finished restore at 11-APR-16
sql statement: alter system set spfile= ''D:\APP\oracle\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILEDBDUP.ORA''
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBDUP'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set control_files =
''E:\oradata\DBDUP\CONTROLFILE\CONTROL1.CTL'', ''E:\oradata\DBDUP\CONTROLFILE\CONTROL2.CTL'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''E:\oradata\DUPPROD'', ''E:\oradata\DBDUP'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''E:\oradata\DUPPROD'', ''E:\oradata\DBDUP'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''F:\FRA\DBDUP'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''location=E:\oradata\DBDUP\LOGFILE'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_3 =
''location=E:\oradata\DBDUP\LOGFILE2'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''D:\app\oracle\'' comment=
'''' scope=spfile";
sql clone "alter system set local_listener =
'''' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DBDUP'' comment= ''duplicate'' scope=spfile
sql statement: alter system set control_files = ''E:\oradata\DBDUP\CONTROLFILE\CONTROL1.CTL'', ''E:\oradata\DBDUP\CONTROLFILE\CONTROL2.CTL'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''E:\oradata\DUPPROD'', ''E:\oradata\DBDUP'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''E:\oradata\DUPPROD'', ''E:\oradata\DBDUP'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''F:\FRA\DBDUP'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''location=E:\oradata\DBDUP\LOGFILE'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_3 = ''location=E:\oradata\DBDUP\LOGFILE2'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''D:\app\oracle\'' comment= '''' scope=spfile
sql statement: alter system set local_listener = '''' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2701131776 bytes
Fixed Size 3835736 bytes
Variable Size 1157630120 bytes
Database Buffers 1526726656 bytes
Redo Buffers 12939264 bytes
allocated channel: c89
channel c89: SID=436 device type=SBT_TAPE
channel c89: EMC|Avamar (avtar backup)
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPPROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DBDUP'' 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
sql statement: alter system set db_name = ''DUPPROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DBDUP'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2701131776 bytes
Fixed Size 3835736 bytes
Variable Size 1157630120 bytes
Database Buffers 1526726656 bytes
Redo Buffers 12939264 bytes
allocated channel: c89
channel c89: SID=436 device type=SBT_TAPE
channel c89: EMC|Avamar (avtar backup)
Starting restore at 11-APR-16
channel c89: starting datafile backup set restore
channel c89: restoring control file
channel c89: reading from backup piece c-3431927017-20160411-01
channel c89: piece handle=c-3431927017-20160411-01 tag=TAG20160411T175543
channel c89: restored backup piece 1
channel c89: restore complete, elapsed time: 00:00:07
output file name=E:\ORADATA\DBDUP\CONTROLFILE\CONTROL1.CTL
output file name=E:\ORADATA\DBDUP\CONTROLFILE\CONTROL2.CTL
Finished restore at 11-APR-16
database mounted
Using previous duplicated file E:\ORADATA\DBDUP\SYSTEM01.DBF for datafile 1 with checkpoint SCN of 2448392
Using previous duplicated file E:\ORADATA\DBDUP\SYSAUX01.DBF for datafile 3 with checkpoint SCN of 2448394
Using previous duplicated file E:\ORADATA\DBDUP\UNDOTBS01.DBF for datafile 5 with checkpoint SCN of 2448393
Using previous duplicated file E:\ORADATA\DBDUP\USERS01.DBF for datafile 6 with checkpoint SCN of 2448395
contents of Memory Script:
{
set until scn 2450561;
set newname for datafile 1 to
"E:\ORADATA\DBDUP\SYSTEM01.DBF";
set newname for datafile 3 to
"E:\ORADATA\DBDUP\SYSAUX01.DBF";
set newname for datafile 5 to
"E:\ORADATA\DBDUP\UNDOTBS01.DBF";
set newname for datafile 6 to
"E:\ORADATA\DBDUP\USERS01.DBF";
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
contents of Memory Script:
{
catalog clone datafilecopy "E:\ORADATA\DBDUP\SYSTEM01.DBF",
"E:\ORADATA\DBDUP\SYSAUX01.DBF",
"E:\ORADATA\DBDUP\UNDOTBS01.DBF",
"E:\ORADATA\DBDUP\USERS01.DBF";
switch clone datafile 1 to datafilecopy
"E:\ORADATA\DBDUP\SYSTEM01.DBF";
switch clone datafile 3 to datafilecopy
"E:\ORADATA\DBDUP\SYSAUX01.DBF";
switch clone datafile 5 to datafilecopy
"E:\ORADATA\DBDUP\UNDOTBS01.DBF";
switch clone datafile 6 to datafilecopy
"E:\ORADATA\DBDUP\USERS01.DBF";
}
executing Memory Script
cataloged datafile copy
datafile copy file name=E:\ORADATA\DBDUP\SYSTEM01.DBF RECID=1 STAMP=908907346
cataloged datafile copy
datafile copy file name=E:\ORADATA\DBDUP\SYSAUX01.DBF RECID=2 STAMP=908907346
cataloged datafile copy
datafile copy file name=E:\ORADATA\DBDUP\UNDOTBS01.DBF RECID=3 STAMP=908907346
cataloged datafile copy
datafile copy file name=E:\ORADATA\DBDUP\USERS01.DBF RECID=4 STAMP=908907346
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=908907346 file name=E:\ORADATA\DBDUP\SYSTEM01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=908907346 file name=E:\ORADATA\DBDUP\SYSAUX01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=908907346 file name=E:\ORADATA\DBDUP\UNDOTBS01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=908907346 file name=E:\ORADATA\DBDUP\USERS01.DBF
contents of Memory Script:
{
set until scn 2450561;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 11-APR-16
starting media recovery
Oracle instance started
Total System Global Area 2701131776 bytes
Fixed Size 3835736 bytes
Variable Size 1157630120 bytes
Database Buffers 1526726656 bytes
Redo Buffers 12939264 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBDUP'' 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 = ''DBDUP'' 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 04/11/2016 18:16:32
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 17 and starting SCN of 2449847 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 16 and starting SCN of 2449583 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 2448619 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 14 and starting SCN of 2448447 found to restore
Recovery Manager complete.
April 27, 2016 - 10:00 am UTC
(Sorry to take so long to get back to you).
I'm not sure I see a problem with RMAN as such.
You've asked for a duplicate as of SCN 2450561
and the datafiles you commence with are at SCN's
2448392
2448394
2448393
2448395
So you need archives that span 2448392 to 2450561, and RMAN could not find them. Are you sure you have backups of those archives available to RMAN ?
A reader, April 28, 2016 - 3:17 pm UTC
Hi Connor
I did not ask for "SCN upto ". This was the script used .
RMAN> run {
2>
3> allocate auxiliary channel c89 type sbt PARMS="SBT_LIBRARY=C:\PROGRA~1\avs\bin\orasbt64.dll" send '"--flagfile=d:\dup.txt" "--bindir=C:\PROGRA~1\avs\bin" "--logfile=F:\logs\DBDUP_c89.log"';
4> duplicate target database to DBDUP NOFILENAMECHECK
5> spfile
6> set control_files='E:\oradata\DBDUP\CONTROLFILE\CONTROL1.CTL','E:\oradata\DBDUP\CONTROLFILE\CONTROL2.CTL'
7> set db_file_name_convert='E:\oradata\DUPPROD','E:\oradata\DBDUP'
8> set log_file_name_convert='E:\oradata\DUPPROD','E:\oradata\DBDUP'
9> set db_recovery_file_dest='F:\FRA\DBDUP'
10> set log_archive_dest_2='location=E:\oradata\DBDUP\LOGFILE'
11> set log_archive_dest_3='location=E:\oradata\DBDUP\LOGFILE2'
12> set diagnostic_dest='D:\app\oracle\'
13> set local_listener='';
14> release channel c89;
15> }
16>
May 01, 2016 - 10:44 am UTC
Sorry, poor choice of words on my part.
Once the duplicate commenced, you can see from the output:
"contents of Memory Script:
{
set until scn 2450561;
recover
clone database
delete archivelog
;
}"
that the *process* is in effect choosing the SCN to recover to. So you'll still need those necessary archives.
What privilege is missing ?
Rajeshwaran Jeyabal, August 22, 2016 - 7:41 am UTC
Team,
Could you help me to understand what privilege is missing here ?
demo@ORA12C> drop table emp purge;
Table dropped.
demo@ORA12C> create table emp as select * from scott.emp;
Table created.
demo@ORA12C>
demo@ORA12C>
demo@ORA12C> grant select on emp to rman_user ;
Grant succeeded.
demo@ORA12C>
From RMAN got this error.
RMAN> @d:\script.sql
RMAN> run {
2> select * from dual;
3> select * from demo.emp;
4> }
using target database control file instead of recovery catalog
D
-
X
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 08/22/2016 13:05:54
ORA-01031: insufficient privileges
RMAN> **end-of-file**
RMAN>
August 22, 2016 - 10:33 am UTC
What privs does "rman_user" have ?
What privilege is missing ?
Rajeshwaran Jeyabal, August 22, 2016 - 11:31 am UTC
I am on 12.1.0.2 so granted just "sysbackup" role to "rman_user".
idle> conn rman_user/rman_user@ora12c as sysbackup
Connected.
sysbackup@ORA12C>
sysbackup@ORA12C>
sysbackup@ORA12C> select * from session_privs;
PRIVILEGE
----------------------------------------
SYSBACKUP
SELECT ANY TRANSACTION
SELECT ANY DICTIONARY
RESUMABLE
CREATE ANY DIRECTORY
ALTER DATABASE
AUDIT ANY
CREATE ANY CLUSTER
CREATE ANY TABLE
UNLIMITED TABLESPACE
DROP TABLESPACE
ALTER TABLESPACE
ALTER SESSION
ALTER SYSTEM
14 rows selected.
sysbackup@ORA12C> select * from session_roles;
ROLE
----------------------------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
2 rows selected.
sysbackup@ORA12C>
August 23, 2016 - 1:25 am UTC
I can't replicate
SQL> create user rman_backup identified by rman_backup;
User created.
SQL> grant sysbackup to rman_backup;
Grant succeeded.
SQL> select * from dba_tab_privs
2 where table_name = 'EMP';
no rows selected
C:\Users\comcdona>rman target rman_backup/rman_backup
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 23 09:22:37 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
connected to target database: NP12 (DBID=1914458800)
RMAN> run {
2> select * from dual;
3> select * from scott.emp;
4> }
using target database control file instead of recovery catalog
D
-
X
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 09-DEC-82 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500
30
7876 ADAMS CLERK 7788 12-JAN-83 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected
Is there anything I'm missing ?
Backup Tablespace using FORMAT option
Rajeshwaran, Jeyabal, September 16, 2016 - 12:47 pm UTC
Team,
Started reading about RMAN from product documentation
http://docs.oracle.com/database/121/BRADV/rcmquick.htm#BRADV89352 Have set the FRA in 12c database
demo@ORA12C> show parameter db_recovery
NAME TYPE VALUE
--------------------------------------------- ----------- -------------------------
db_recovery_file_dest string D:\app\Vnameit\fast_recov
db_recovery_file_dest_size big integer 10G
demo@ORA12C>
From RMAN when tried to backup the Tablespace WITHOUT FORMAT option backup piece goes into FRA location.
RMAN> backup tablespace users tag 'my_temp_backup'
2> ;
Starting backup at 16-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=D:\APP\VNAMEIT\ORADATA\ORA12C\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 16-SEP-16
channel ORA_DISK_1: finished piece 1 at 16-SEP-16
piece handle=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\BACKUPSET\2016_09_16\O1_MF_NNNDF_MY_TEMP_BACKUP_CXQSTZJ6_.BKP tag=MY_TEMP_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-SEP-16
But when tried to backup the Tablespace WITH FORMAT option, backup piece goes into Oracle Home directory.
RMAN> backup tablespace users tag 'my_temp_backup' format 'AL_%d_TS_%t_PS_%p' ;
Starting backup at 16-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=D:\APP\VNAMEIT\ORADATA\ORA12C\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 16-SEP-16
channel ORA_DISK_1: finished piece 1 at 16-SEP-16
piece handle=D:\APP\VNAMEIT\PRODUCT\12.1.0\DBHOME_1\DATABASE\AL_ORA12C_TS_922730844_PS_1 tag=MY_TEMP_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-SEP-16
RMAN> exit
Recovery Manager complete.
Why does the FORMAT option pushes the backup piece into Oracle home rather than FRA location?
September 17, 2016 - 3:18 pm UTC
From
http://docs.oracle.com/database/121/BRADV/rcmquick.htm "Option: FORMAT
Description:Specifies a location and name for backup pieces and copies. You must use substitution variables to generate unique file names."
Notice that it is name *and* location. So in the absence of a location being specified in your format, I'm assuming you are getting the default location.
Similarly, for the FRA, the database is in control of the names:
"The files placed in this location are maintained by Oracle Database and the generated file names are maintained in Oracle Managed Files (OMF) format."
Backup Tablespace using FORMAT option
Rajeshwaran, Jeyabal, September 20, 2016 - 2:27 pm UTC
I'm assuming you are getting the default location.
So How to specify (or modify) the FORMAT option to tell RMAN that the backup piece should go into FRA rather than Oracle home?
September 20, 2016 - 5:03 pm UTC
That's my point - the FRA is about the *database* managing the files (and their names).
The documentation should perhaps be more clear on this.
Incremental Level 1 Backup
Rajeshwaran, Jeyabal, September 28, 2016 - 2:56 pm UTC
Team,
Reading through this, from backup and recovery user guide
http://docs.oracle.com/database/121/BRADV/rcmquick.htm#BRADV89353 ....
If no level 0 backup exists in either the current or parent database incarnation when you run a level 1 backup, then RMAN makes a level 0 backup automatically.
....
RMAN> list backup;
specification does not match any backup in the repository
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
RMAN> list archivelog all;
specification does not match any archived log in the repository
RMAN> backup incremental level 1 tablespace TS_DEMO ;
Starting backup at 28-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=D:\APP\VNAMEIT\ORADATA\ORA12C\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 28-SEP-16
channel ORA_DISK_1: finished piece 1 at 28-SEP-16
piece handle=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\BACKUPSET\2016_09_28\O1_MF_NNND1_TAG20160928T201305_CYQOQTB5_.BKP tag=TAG20160928T201305 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 28-SEP-16
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
66 Incr 1 1.39G DISK 00:01:02 28-SEP-16
BP Key: 67 Status: AVAILABLE Compressed: NO Tag: TAG20160928T201305
Piece Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\BACKUPSET\2016_09_28\O1_MF_NNND1_TAG20160928T201305_CYQOQTB5_.BKP
List of Datafiles in backup set 66
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
9 1 Incr 5530280400755 28-SEP-16 D:\APP\VNAMEIT\ORADATA\ORA12C\DEMO.DBF
RMAN>I dont have any prior backup sets or Image copies to the Tablespace TS_DEMO, but how do i prove that RMAN has done a Level-0 backup in this case?
from the list backup command it still shows LV as "1", where should i need to check for Level-0 backup created by RMAN automatically ?
September 30, 2016 - 9:28 am UTC
I think it refers to *database* backps, eg
RMAN> list backup ;
specification does not match any backup in the repository
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
RMAN> list archivelog all;
specification does not match any archived log in the repository
RMAN> backup incremental level 1 database format 'D:\oracle\%U';
Starting backup at 30-SEP-16
using channel ORA_DISK_1
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 5 found
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=C:\ORACLE\ORADATA\DB11\USERS01.DBF
input datafile file number=00002 name=C:\ORACLE\ORADATA\DB11\SYSAUX01.DBF
input datafile file number=00001 name=C:\ORACLE\ORADATA\DB11\SYSTEM01.DBF
input datafile file number=00005 name=C:\ORACLE\ORADATA\DB11\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 30-SEP-16
channel ORA_DISK_1: finished piece 1 at 30-SEP-16
piece handle=D:\ORACLE\0GRH4A6D_1_1 tag=TAG20160930T170933 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=C:\ORACLE\ORADATA\DB11\UNDOTMP.DBF
input datafile file number=00003 name=C:\ORACLE\ORADATA\DB11\USERS_MV01.DBF
channel ORA_DISK_1: starting piece 1 at 30-SEP-16
channel ORA_DISK_1: finished piece 1 at 30-SEP-16
piece handle=D:\ORACLE\0HRH4A84_1_1 tag=TAG20160930T170933 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 30-SEP-16
Starting Control File and SPFILE Autobackup at 30-SEP-16
piece handle=C:\ORACLE\PRODUCT\11.2.0.4\DBHOME_1\DATABASE\C-1427489661-20160930-00 comment=NONE
Finished Control File and SPFILE Autobackup at 30-SEP-16
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Incr 0 1.76G DISK 00:00:51 30-SEP-16
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20160930T170933
Piece Name: D:\ORACLE\0GRH4A6D_1_1
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 83265423 30-SEP-16 C:\ORACLE\ORADATA\DB11\SYSTEM01.DBF
2 0 Incr 83265423 30-SEP-16 C:\ORACLE\ORADATA\DB11\SYSAUX01.DBF
4 0 Incr 83265423 30-SEP-16 C:\ORACLE\ORADATA\DB11\USERS01.DBF
5 0 Incr 83265423 30-SEP-16 C:\ORACLE\ORADATA\DB11\EXAMPLE01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Incr 1 824.52M DISK 00:00:37 30-SEP-16
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20160930T170933
Piece Name: D:\ORACLE\0HRH4A84_1_1
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 1 Incr 83265442 30-SEP-16 C:\ORACLE\ORADATA\DB11\USERS_MV01.DBF
6 1 Incr 83265442 30-SEP-16 C:\ORACLE\ORADATA\DB11\UNDOTMP.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 9.36M DISK 00:00:00 30-SEP-16
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20160930T171114
Piece Name: C:\ORACLE\PRODUCT\11.2.0.4\DBHOME_1\DATABASE\C-1427489661-20160930-00
SPFILE Included: Modification time: 30-SEP-16
SPFILE db_unique_name: DB11
Control File Included: Ckp SCN: 83265464 Ckp time: 30-SEP-16
RMAN>
RMAN Duplicate to other version
A reader, September 30, 2016 - 11:32 am UTC
Is it possible to do rman duplication or rman backup from 11.2.3.0 Standard Edition version 64 bit to 11g Enterprise Edition Release 11.2.0.1.0 64 bit?
October 01, 2016 - 1:31 am UTC
I havent tried it. Even if it does work, note that at the end of the duplication, you would need to run the 11.2.0.3 => 11.2.0.4 upgrade scripts to bring the database up to the new version.
Incremental Level 1 Backup
Rajeshwaran, Jeyabal, October 01, 2016 - 1:08 pm UTC
Team,
Looks like now it seems to works fine.
RMAN> list backup ;
specification does not match any backup in the repository
RMAN> list copy ;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
RMAN> list archivelog all;
specification does not match any archived log in the repository
RMAN> backup incremental level 1 tablespace USERS ;
Starting backup at 01-OCT-16
using channel ORA_DISK_1
no parent backup or copy of datafile 6 found
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=D:\APP\VNAMEIT\ORADATA\ORA12C\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 01-OCT-16
channel ORA_DISK_1: finished piece 1 at 01-OCT-16
piece handle=D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\BACKUPSET\2016_10_01\O1_MF_NNND0_TAG20161001T183447_CYZF3HMK_.BKP tag=TAG20161001T183447 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-OCT-16
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Incr 0 1.91M DISK 00:00:01 01-OCT-16
BP Key: 76 Status: AVAILABLE Compressed: NO Tag: TAG20161001T183447
Piece Name: D:\APP\VNAMEIT\FAST_RECOVERY_AREA\ORA12C\BACKUPSET\2016_10_01\O1_MF_NNND0_TAG20161001T183447_CYZF3HMK_.BKP
List of Datafiles in backup set 75
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 0 Incr 5530280572341 01-OCT-16 D:\APP\VNAMEIT\ORADATA\ORA12C\USERS01.DBF
RMAN>Even with no prior backup and copies available, now when requested "level 1" backup for tablespace USERS - RMAN goes into level "0" by default.
no parent backup or copy of datafile 6 found
channel ORA_DISK_1: starting incremental level 0 datafile backup set
Not sure, what went wrong prior.
October 01, 2016 - 11:55 pm UTC
THanks for getting back to us
A reader, October 03, 2016 - 6:44 am UTC
Is it possible to do rman duplication on same host.
For Student Learning Purpose.
October 04, 2016 - 11:12 am UTC
Yes. For example, I've done duplicate-for-standby on the same machine.
Just setup the tnsnames entries as per normal so that the 'connect target' and 'connect auxiliary' commands work just as per normal.
Om, October 04, 2016 - 3:27 pm UTC
It's necessity to have a standby ( using rman duplicate) database same as production database as per follow like
Directory structure of physical data file and index file?
October 05, 2016 - 1:00 am UTC