OS: Oracle Linux
DB: 11.2.0.1.0
1. First of all, define storage to duplicate database (we will use +DATA_DUP, +FRA_DUP here).
2. Backup target(source) database ora11g.
The backup locations are in the following output:
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
141 6.30M DISK 00:00:05 10-DEC-16
BP Key: 146 Status: AVAILABLE Compressed: YES Tag: TAG20161210T111816
Piece Name: +FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t111816_0.698.930223097
List of Archived Logs in backup set 141
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 5129793 10-DEC-16 5138592 10-DEC-16
1 2 5138592 10-DEC-16 5138690 10-DEC-16
1 3 5138690 10-DEC-16 5138931 10-DEC-16
1 4 5138931 10-DEC-16 5139928 10-DEC-16
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- -----------------------
142 Full 406.77M DISK 00:03:00 10-DEC-16
BP Key: 147 Status: AVAILABLE Compressed: YES Tag: TAG20161210T111827
Piece Name: +FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111
List of Datafiles in backup set 142
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 5139950 10-DEC-16 +DATA/ora11g/datafile/system.dbf
2 Full 5139950 10-DEC-16 +DATA/ora11g/datafile/sysaux.dbf
3 Full 5139950 10-DEC-16 +DATA/ora11g/datafile/undotbs1.dbf
4 Full 5139950 10-DEC-16 +DATA/ora11g/datafile/users.dbf
5 Full 5139950 10-DEC-16 +DATA/ora11g/datafile/example.dbf
6 Full 5139950 10-DEC-16 +DATA/ora11g/datafile/rcat_data_01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
143 30.00K DISK 00:00:01 10-DEC-16
BP Key: 148 Status: AVAILABLE Compressed: YES Tag: TAG20161210T112137
Piece Name: +FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297
List of Archived Logs in backup set 143
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5 5139928 10-DEC-16 5140052 10-DEC-16
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
144 Full 10.42M DISK 00:00:07 10-DEC-16
BP Key: 149 Status: AVAILABLE Compressed: NO Tag: TAG20161210T112139
Piece Name: +FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303
SPFILE Included: Modification time: 10-DEC-16
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 5140065 Ckp time: 10-DEC-16
RMAN>
The archive logs location are
ASMCMD [+fra/ora11g/archivelog] > ls
2016_12_10/
2016_12_25/
2016_12_29/
3. Add the below entry in the /etc/oratab file
BDUPDB:/u01/app/oracle/product/11.2.0/dbhome_1:N
4. Create the TNS ENTRY in the tnsnames.ora file
[loc:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin]
BDUPDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl11g)(PORT = 1521))
)
( CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = BDUPDB)
)
)
5. Edit listener.ora file
(/u01/app/grid/product/11.2.0/grid/network/admin/listener.ora)
Before editing we have to stop listener([grid@orcl11ggrid]$lsnrctl stop)
......
(SID_DESC =
(GLOBAL_DBNAME = DUPDB2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = BDUPDB)
)
)
.....
Then start listener ([grid@orcl11g grid]$ lsnrctl start)
6. Create initBDUPDB.ora file (created at $ORACLE_HOME/dbs) with following parameters
Option 1:
#####################
db_name=BDUPDB
control_files=('+DATA_DUP','+FRA_DUP')
## DB_FILE_NAME_CONVERT=('+DATA','+DATA_DUP')
LOG_FILE_NAME_CONVERT=('+DATA','+DATA_DUP','+FRA','+FRA_DUP')
db_recovery_file_dest='+FRA_DUP'
db_recovery_file_dest_size=10737418240
db_create_file_dest='+DATA_DUP'
#####################
Option 2:
#####################
db_name=BDUPDB
control_files=('+DATA_DUP','+FRA_DUP')
#DB_FILE_NAME_CONVERT=('+DATA','+DATA_DUP')
#LOG_FILE_NAME_CONVERT+('+DATA','+DATA_DUP','+FRA','+FRA_DUP')
db_recovery_file_dest='+FRA_DUP'
db_recovery_file_dest_size=10737418240
db_create_file_dest='+DATA_DUP'
#####################
The following initBDUPDB.ora file options will not work with OMF files.
Option 3:
#####################
db_name=BDUPDB
control_files=('+DATA_DUP','+FRA_DUP')
DB_FILE_NAME_CONVERT=('+DATA','+DATA_DUP')
LOG_FILE_NAME_CONVERT=('+DATA','+DATA_DUP','+FRA','+FRA_DUP')
db_recovery_file_dest='+FRA_DUP'
db_recovery_file_dest_size=10737418240
##db_create_file_dest='+DATA_DUP'
#####################
Option 4:
#####################
db_name=BDUPDB
control_files=('+DATA_DUP','+FRA_DUP')
##DB_FILE_NAME_CONVERT=('+DATA','+DATA_DUP')
LOG_FILE_NAME_CONVERT=('+DATA','+DATA_DUP','+FRA','+FRA_DUP')
db_recovery_file_dest='+FRA_DUP'
db_recovery_file_dest_size=10737418240
##db_create_file_dest='+DATA_DUP'
#####################
Option 5:
#####################
db_name=BDUPDB
control_files=('+DATA_DUP','+FRA_DUP')
DB_FILE_NAME_CONVERT=('+DATA','+DATA_DUP')
LOG_FILE_NAME_CONVERT=('+DATA','+DATA_DUP','+FRA','+FRA_DUP')
db_recovery_file_dest='+FRA_DUP'
db_recovery_file_dest_size=10737418240
db_create_file_dest='+DATA_DUP'
#####################
If we use initBDUPDB.ora content like in Option 5 then the following warning and error occurred. Some portion from output
…
database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set until scn 4325803;
set newname for datafile 1 to "+DATA_DUP/ora11g/datafile/system.dbf";
set newname for datafile 2 to "+DATA_DUP/ora11g/datafile/sysaux.dbf";
set newname for datafile 3 to "+DATA_DUP/ora11g/datafile/undotbs1.dbf";
set newname for datafile 4 to "+DATA_DUP/ora11g/datafile/users.dbf";
set newname for datafile 5 to "+DATA_DUP/ora11g/datafile/example.dbf";
set newname for datafile 6 to "+DATA_DUP/ora11g/datafile/rcat_data_01.dbf";
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
Starting restore at 05-OCT-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA_DUP/ora11g/datafile/system.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA_DUP/ora11g/datafile/sysaux.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA_DUP/ora11g/datafile/undotbs1.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA_DUP/ora11g/datafile/users.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA_DUP/ora11g/datafile/example.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA_DUP/ora11g/datafile/rcat_data_01.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/backupset/2016_10_03/nnndf0_tag20161003t110620_0.748.924260783
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece
+FRA/ora11g/backupset/2016_10_03/nnndf0_tag20161003t110620_0.748.924260783
ORA-19504: failed to create file "+DATA_DUP/ora11g/datafile/users.dbf"
ORA-17502: ksfdcre:4 Failed to create file +DATA_DUP/ora11g/datafile/users.dbf
ORA-15173: entry 'datafile' does not exist in directory 'ora11g' failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/05/2016 16:06:00
RMAN-05556: not all datafiles have backups that can be recovered to SCN 4325803
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN>
7. Create password file for new database
[oracle11g@orcl11g Desktop]$ cd $ORACLE_HOME/dbs
(must change path like $ORACLE_HOME/dbs before creating password file)
[oracle11g@orcl11g dbs]$ orapwd file=orapwBDUPDB entries=5
or just copy from source like
[oracle11g@orcl11g~]cp $ORACLE_HOME/dbs/orapwORA11G $ORACLE_HOME/dbs/orapwBDUPDB
8. Start target(source) database in mount state or open state.
(If the source database is open, then archiving must be enabled)
[oracle11g@orcl11g~]$ sqlplus / as sysdba
SQL> startup mount;
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
database mounted
9. Set environment for duplicated database as SID
[oracle11g@orcl11g oracle]$ . oraenv
ORACLE_SID = [ora11g] ? BDUPDB
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
10. Start auxiliary database in nomount state.
[oracle11g@orcl11g~]$ sqlplus / as sysdba
SQL> startup nomount;
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
11. Connect to target and auxiliary instance.
[oracle11g@orcl11g~]$ rman target sys/222333@ora11g auxiliary=sys/222333@BDUPDB
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Aug 25 18:07:33 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ora11g (DBID=740794324)
connected to auxiliary database: BDUPDB (not mounted)
RMAN>
12. We have some duplication options here.
Option 1:
RMAN> duplicate target database to BDUPDB;
Starting Duplicate Db at 10-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=99 device type=DISK
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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
contents of Memory Script:
{
sql clone "alter system set control_files = ''+DATA_DUP/bdupdb/controlfile/current.287.930241899'',
''+FRA_DUP/bdupdb/controlfile/current.316.930241899'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =''ORA11G'' comment=''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''BDUPDB'' 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 control_files = ''+DATA_DUP/bdupdb/controlfile/current.287.930241899'', '
''+FRA_DUP/bdupdb/controlfile/current.316.930241899'' comment= ''Set by RMAN'' scope=spfile
''+FRA_DUP/bdupdb/controlfile/current.316.930241899'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''BDUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Starting restore at 10-DEC-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303 tag=TAG20161210T112139
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output file name=+DATA_DUP/bdupdb/controlfile/current.287.930241899
output file name=+FRA_DUP/bdupdb/controlfile/current.316.930241899
Finished restore at 10-DEC-16
database mounted
contents of Memory Script:
{
set until scn 5164906;
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;
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
Starting restore at 10-DEC-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA_DUP
channel ORA_AUX_DISK_1: reading from backup piece
+FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111
+FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111
tag=TAG20161210T111827
tag=TAG20161210T111827
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:25
Finished restore at 10-DEC-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=51 STAMP=930242616 file name=+DATA_DUP/bdupdb/datafile/system.296.930241931
datafile 2 switched to datafile copy
input datafile copy RECID=52 STAMP=930242616 file name=+DATA_DUP/bdupdb/datafile/sysaux.295.930241931
datafile 3 switched to datafile copy
input datafile copy RECID=53 STAMP=930242617 file name=+DATA_DUP/bdupdb/datafile/undotbs1.294.930241933
datafile 4 switched to datafile copy
input datafile copy RECID=54 STAMP=930242617 file name=+DATA_DUP/bdupdb/datafile/users.297.930241929
datafile 5 switched to datafile copy
input datafile copy RECID=55 STAMP=930242618 file name=+DATA_DUP/bdupdb/datafile/example.293.930241933
datafile 6 switched to datafile copy
input datafile copy RECID=56 STAMP=930242618 file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.930241933
contents of Memory Script:
{
set until scn 5164906;
recover
clone database
delete archivelog;
}
executing Memory Script
executing command: SET until clause
Starting recover at 10-DEC-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file +FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_AUX_DISK_1: reading from backup piece
+FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297
+FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297
tag=TAG20161210T112137
tag=TAG20161210T112137
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA_DUP/bdupdb/archivelog/2016_12_10/thread_1_seq_5.319.930242623 thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=+FRA_DUP/bdupdb/archivelog/2016_12_10/thread_1_seq_5.319.930242623 RECID=946 STAMP=930242622
archived log file name=+FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039 thread=1 sequence=6
media recovery complete, elapsed time: 00:00:54
Finished recover at 10-DEC-16
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name = 'BDUPDB'' 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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: alter system set db_name = ''BDUPDB'' 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BDUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE +DATA_DUP/bdupdb/datafile/system.296.930241931'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA_DUP/bdupdb/datafile/sysaux.295.930241931",
"+DATA_DUP/bdupdb/datafile/undotbs1.294.930241933",
"+DATA_DUP/bdupdb/datafile/users.297.930241929",
"+DATA_DUP/bdupdb/datafile/example.293.930241933",
"+DATA_DUP/bdupdb/datafile/rcat_data.292.930241933";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA_DUP in control file
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/sysaux.295.930241931 RECID=1 STAMP=930242705
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/undotbs1.294.930241933 RECID=2 STAMP=930242705
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/users.297.930241929 RECID=3 STAMP=930242705
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/example.293.930241933 RECID=4 STAMP=930242705
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.930241933 RECID=5 STAMP=930242705
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=930242705 file name=+DATA_DUP/bdupdb/datafile/sysaux.295.930241931
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=930242705 file name=+DATA_DUP/bdupdb/datafile/undotbs1.294.930241933
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=930242705 file name=+DATA_DUP/bdupdb/datafile/users.297.930241929
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=930242705 file name=+DATA_DUP/bdupdb/datafile/example.293.930241933
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=930242705 file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.930241933
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 10-DEC-16
Option 2:
When we use UNTIL clause in backup-based duplication then we can recover to a point only *at* or after* the last datafile checkpoint SCN of
the backup.
the backup.
If there is also archive logs for following days after backup date then we can recover database until the last checkpoint SCN of database or
datafile. Also we can recover until the time or archive log SCN before the last checkpoint SCN of database or datafile.
datafile. Also we can recover until the time or archive log SCN before the last checkpoint SCN of database or datafile.
Example 1:
Here the last datafile and archivelog SCN is 5252624. Now recover will be until time 12/25/2016 20:58:31' which is equal to SCN 5241064.
Now the question comes to mind 'How we define time, SCN and sequence relations?'
If you need to duplicate your database to a point in time by scn, sequence or time, you can use the following query to see the relation
between time-scn-sequence.
between time-scn-sequence.
select NAME, SEQUENCE#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE#
from v$archived_log g where g.first_time>'10-DEC-16'
NAME FIRST_TIME FIRST_CHANGE# NEXT_TIME NEXT_CHANGE#
--------------- ---------- ------------- --------- ------------
+FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039 10-DEC-16 5140052 10-DEC-16 5164906
+FRA/ora11g/archivelog/2016_12_11/thread_1_seq_7.675.930349435 10-DEC-16 5164906 11-DEC-16 5189373
+FRA/ora11g/archivelog/2016_12_23/thread_1_seq_9.677.931384823 13-DEC-16 5198759 23-DEC-16 5212844
+FRA/ora11g/archivelog/2016_12_23/thread_1_seq_10.682.931384937 23-DEC-16 5212844 23-DEC-16 5216404
+FRA/ora11g/archivelog/2016_12_25/thread_1_seq_12.684.931553913 23-DEC-16 5219184 25-DEC-16 5241064
+FRA/ora11g/archivelog/2016_12_29/thread_1_seq_13.676.931901253 25-DEC-16 5241064 29-DEC-16 5252624
We get checkpoint SCN of database as
select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
525262
We get checkpoint SCN of datafile as
select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
--------------------------- ------------------
+DATA/ora11g/datafile/users.dbf 5252624
+DATA/ora11g/datafile/example.dbf 5252624
+DATA/ora11g/datafile/rcat_data_01.dbf 5252624
+DATA/ora11g/datafile/system.dbf 5252624
+DATA/ora11g/datafile/sysaux.dbf 5252624
+DATA/ora11g/datafile/undotbs1.dbf 5252624
6 rows selected
Output:
RMAN> duplicate target database to BDUPDB
2> UNTIL TIME "TO_DATE('12/25/2016 20:58:31','MM/DD/YYYY HH24:MI:SS')";
Starting Duplicate Db at 29-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK
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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
contents of Memory Script:
{
set until scn 5241064;
sql clone "alter system set control_files = ''+DATA_DUP/bdupdb/controlfile/current.295.931904921'',
''+FRA_DUP/bdupdb/controlfile/current.322.931904921''
comment= ''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name = 'ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name = ''BDUPDB''
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 = ''+DATA_DUP/bdupdb/controlfile/current.295.931904921'',
''+FRA_DUP/bdupdb/controlfile/current.322.931904921'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name =''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''BDUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Starting restore at 29-DEC-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=97 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303 tag=TAG20161210T112139
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output file name=+DATA_DUP/bdupdb/controlfile/current.295.931904921
output file name=+FRA_DUP/bdupdb/controlfile/current.322.931904921
Finished restore at 29-DEC-16
database mounted
contents of Memory Script:
{
set until scn 5241064;
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;
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
Starting restore at 29-DEC-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA_DUP
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111 tag=TAG20161210T111827
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:15
Finished restore at 29-DEC-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=51 STAMP=931905629 file name=+DATA_DUP/bdupdb/datafile/system.290.931904955
datafile 2 switched to datafile copy
input datafile copy RECID=52 STAMP=931905630 file name=+DATA_DUP/bdupdb/datafile/sysaux.291.931904957
datafile 3 switched to datafile copy
input datafile copy RECID=53 STAMP=931905630 file name=+DATA_DUP/bdupdb/datafile/undotbs1.288.931904957
datafile 4 switched to datafile copy
input datafile copy RECID=54 STAMP=931905630 file name=+DATA_DUP/bdupdb/datafile/users.287.931904955
datafile 5 switched to datafile copy
input datafile copy RECID=55 STAMP=931905631 file name=+DATA_DUP/bdupdb/datafile/example.289.931904957
datafile 6 switched to datafile copy
input datafile copy RECID=56 STAMP=931905631 file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.931904959
contents of Memory Script:
{
set until time "TO_DATE('12/25/2016 20:58:31','MM/DD/YYYY HH24:MI:SS')";
recover
clone database
delete archivelog ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 29-DEC-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file +FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039
archived log for thread 1 with sequence 7 is already on disk as file +FRA/ora11g/archivelog/2016_12_11/thread_1_seq_7.675.930349435
archived log for thread 1 with sequence 8 is already on disk as file +FRA/ora11g/archivelog/2016_12_13/thread_1_seq_8.697.930520873
archived log for thread 1 with sequence 9 is already on disk as file +FRA/ora11g/archivelog/2016_12_23/thread_1_seq_9.677.931384823
archived log for thread 1 with sequence 10 is already on disk as file +FRA/ora11g/archivelog/2016_12_23/thread_1_seq_10.682.931384937
archived log for thread 1 with sequence 11 is already on disk as file +FRA/ora11g/archivelog/2016_12_23/thread_1_seq_11.679.931384983
archived log for thread 1 with sequence 12 is already on disk as file +FRA/ora11g/archivelog/2016_12_25/thread_1_seq_12.684.931553913
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297 tag=TAG20161210T112137
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA_DUP/bdupdb/archivelog/2016_12_29/thread_1_seq_5.321.931905635 thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=+FRA_DUP/bdupdb/archivelog/2016_12_29/thread_1_seq_5.321.931905635 RECID=946 STAMP=931905635
archived log file name=+FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039 thread=1 sequence=6
archived log file name=+FRA/ora11g/archivelog/2016_12_11/thread_1_seq_7.675.930349435 thread=1 sequence=7
archived log file name=+FRA/ora11g/archivelog/2016_12_13/thread_1_seq_8.697.930520873 thread=1 sequence=8
archived log file name=+FRA/ora11g/archivelog/2016_12_23/thread_1_seq_9.677.931384823 thread=1 sequence=9
archived log file name=+FRA/ora11g/archivelog/2016_12_23/thread_1_seq_10.682.931384937 thread=1 sequence=10
archived log file name=+FRA/ora11g/archivelog/2016_12_23/thread_1_seq_11.679.931384983 thread=1 sequence=11
archived log file name=+FRA/ora11g/archivelog/2016_12_25/thread_1_seq_12.684.931553913 thread=1 sequence=12
media recovery complete, elapsed time: 00:04:11
Finished recover at 29-DEC-16
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name = ''BDUPDB'' 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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: alter system set db_name = ''BDUPDB'' 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BDUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'+DATA_DUP/bdupdb/datafile/system.290.931904955'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA_DUP/bdupdb/datafile/sysaux.291.931904957",
"+DATA_DUP/bdupdb/datafile/undotbs1.288.931904957",
"+DATA_DUP/bdupdb/datafile/users.287.931904955",
"+DATA_DUP/bdupdb/datafile/example.289.931904957",
"+DATA_DUP/bdupdb/datafile/rcat_data.292.931904959";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA_DUP in control file
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/sysaux.291.931904957 RECID=1 STAMP=931905910
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/undotbs1.288.931904957 RECID=2 STAMP=931905910
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/users.287.931904955 RECID=3 STAMP=931905910
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/example.289.931904957 RECID=4 STAMP=931905910
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.931904959 RECID=5 STAMP=931905910
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=931905910 file name=+DATA_DUP/bdupdb/datafile/sysaux.291.931904957
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=931905910 file name=+DATA_DUP/bdupdb/datafile/undotbs1.288.931904957
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=931905910 file name=+DATA_DUP/bdupdb/datafile/users.287.931904955
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=931905910 file name=+DATA_DUP/bdupdb/datafile/example.289.931904957
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=931905910 file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.931904959
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 29-DEC-16
RMAN>
Example 2:
We can use all following options.
Time 22:01:14 = last checkpoint SCN 5189373=sequence 45. The last checkpoint SCN is same with last archive log SCN here.
RMAN> duplicate target database to BDUPDB
2> UNTIL TIME "TO_DATE('12/13/2016 22:01:14','MM/DD/YYYY HH24:MI:SS')";
or
RMAN> duplicate target database to BDUPDB until SCN 5189373;
or
RMAN> duplicate target database to BDUPDB until sequence 45;
Output:
Starting Duplicate Db at 13-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK
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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
contents of Memory Script:
{
set until scn 5164906;
sql clone "alter system set control_files = ''+DATA_DUP/bdupdb/controlfile/current.287.930520057'',
''+FRA_DUP/bdupdb/controlfile/current.316.930520057'' comment= ''Set by RMAN'' scope=spfile";
''+FRA_DUP/bdupdb/controlfile/current.316.930520057'' comment= ''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name = ''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name = ''BDUPDB''
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 = ''+DATA_DUP/bdupdb/controlfile/current.287.930520057'', '
'+FRA_DUP/bdupdb/controlfile/current.316.930520057'' comment= ''Set by RMAN'' scope=spfile
'+FRA_DUP/bdupdb/controlfile/current.316.930520057'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''BDUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Starting restore at 13-DEC-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=11 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303 tag=TAG20161210T112139
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output file name=+DATA_DUP/bdupdb/controlfile/current.287.930520057
output file name=+FRA_DUP/bdupdb/controlfile/current.316.930520057
Finished restore at 13-DEC-16
database mounted
contents of Memory Script:
{
set until scn 5164906;
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;
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
Starting restore at 13-DEC-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA_DUP
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111 tag=TAG20161210T111827
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:05
Finished restore at 13-DEC-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=51 STAMP=930520756 file name=+DATA_DUP/bdupdb/datafile/system.295.930520091
datafile 2 switched to datafile copy
input datafile copy RECID=52 STAMP=930520756 file name=+DATA_DUP/bdupdb/datafile/sysaux.294.930520093
datafile 3 switched to datafile copy
input datafile copy RECID=53 STAMP=930520757 file name=+DATA_DUP/bdupdb/datafile/undotbs1.297.930520093
datafile 4 switched to datafile copy
input datafile copy RECID=54 STAMP=930520757 file name=+DATA_DUP/bdupdb/datafile/users.296.930520091
datafile 5 switched to datafile copy
input datafile copy RECID=55 STAMP=930520757 file name=+DATA_DUP/bdupdb/datafile/example.293.930520093
datafile 6 switched to datafile copy
input datafile copy RECID=56 STAMP=930520757 file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.930520093
contents of Memory Script:
{
set until time "TO_DATE('12/11/2016 22:23:45','MM/DD/YYYY HH24:MI:SS')";
recover
clone database
delete archivelog ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 13-DEC-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file +FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039
archived log for thread 1 with sequence 7 is already on disk as file +FRA/ora11g/archivelog/2016_12_11/thread_1_seq_7.675.930349435
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297 tag=TAG20161210T112137
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA_DUP/bdupdb/archivelog/2016_12_13/thread_1_seq_5.319.930520763 thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=+FRA_DUP/bdupdb/archivelog/2016_12_13/thread_1_seq_5.319.930520763 RECID=946 STAMP=930520762
archived log file name=+FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039 thread=1 sequence=6
archived log file name=+FRA/ora11g/archivelog/2016_12_11/thread_1_seq_7.675.930349435 thread=1 sequence=7
media recovery complete, elapsed time: 00:01:41
Finished recover at 13-DEC-16
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name = ''BDUPDB'' 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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: alter system set db_name = ''BDUPDB'' 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BDUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'+DATA_DUP/bdupdb/datafile/system.295.930520091'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA_DUP/bdupdb/datafile/sysaux.294.930520093",
"+DATA_DUP/bdupdb/datafile/undotbs1.297.930520093",
"+DATA_DUP/bdupdb/datafile/users.296.930520091",
"+DATA_DUP/bdupdb/datafile/example.293.930520093",
"+DATA_DUP/bdupdb/datafile/rcat_data.292.930520093";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA_DUP in control file
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/sysaux.294.930520093 RECID=1 STAMP=930520913
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/undotbs1.297.930520093 RECID=2 STAMP=930520913
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/users.296.930520091 RECID=3 STAMP=930520913
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/example.293.930520093 RECID=4 STAMP=930520913
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.930520093 RECID=5 STAMP=930520913
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=930520913 file name=+DATA_DUP/bdupdb/datafile/sysaux.294.930520093
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=930520913 file name=+DATA_DUP/bdupdb/datafile/undotbs1.297.930520093
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=930520913 file name=+DATA_DUP/bdupdb/datafile/users.296.930520091
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=930520913 file name=+DATA_DUP/bdupdb/datafile/example.293.930520093
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=930520913 file name=+DATA_DUP/bdupdb/datafile/rcat_data.292.930520093
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 13-DEC-16
Example 3:
The following duplication command will fail, because of the time in duplication command is ahead of last NEXT TIME in archived logs.
RMAN> duplicate target database to BDUPDB
2> UNTIL TIME "TO_DATE('12/13/2016 22:03:14','MM/DD/YYYY HH24:MI:SS')";
Starting Duplicate Db at 13-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK
RMAN-00571: ==========================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ======
RMAN-00571: ================================================
RMAN-03002: failure of Duplicate Db command at 12/13/2016 22:34:44
RMAN-06617: UNTIL TIME (13-DEC-16) is ahead of last NEXT TIME in archived logs (13-DEC-16)
Option 3:
We can use noredo parameter in duplication command if we don’t want to apply any archive logs.
RMAN> duplicate target database to BDUPDB noredo;
Starting Duplicate Db at 14-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK
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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
contents of Memory Script:
{
sql clone "alter system set control_files = ''+DATA_DUP/bdupdb/controlfile/current.295.930564111'',
''+FRA_DUP/bdupdb/controlfile/current.319.930564111''
''+FRA_DUP/bdupdb/controlfile/current.319.930564111''
comment= ''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name = ''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name = ''BDUPDB''
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 control_files = ''+DATA_DUP/bdupdb/controlfile/current.295.930564111'',
''+FRA_DUP/bdupdb/controlfile/current.319.930564111'' comment= ''Set by RMAN'' scope=spfile
''+FRA_DUP/bdupdb/controlfile/current.319.930564111'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''BDUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Starting restore at 14-DEC-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303 tag=TAG20161210T112139
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:18
output file name=+DATA_DUP/bdupdb/controlfile/current.295.930564111
output file name=+FRA_DUP/bdupdb/controlfile/current.319.930564111
Finished restore at 14-DEC-16
database mounted
contents of Memory Script:
{
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;
restore
clone database ;
}
executing Memory Script
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 14-DEC-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA_DUP
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111 tag=TAG20161210T111827
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:25
Finished restore at 14-DEC-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=51 STAMP=930564828 file name=+DATA_DUP/bdupdb/datafile/system.297.930564143
datafile 2 switched to datafile copy
input datafile copy RECID=52 STAMP=930564828 file name=+DATA_DUP/bdupdb/datafile/sysaux.296.930564143
datafile 3 switched to datafile copy
input datafile copy RECID=53 STAMP=930564828 file name=+DATA_DUP/bdupdb/datafile/undotbs1.293.930564145
datafile 4 switched to datafile copy
input datafile copy RECID=54 STAMP=930564829 file name=+DATA_DUP/bdupdb/datafile/users.294.930564143
datafile 5 switched to datafile copy
input datafile copy RECID=55 STAMP=930564829 file name=+DATA_DUP/bdupdb/datafile/example.292.930564145
datafile 6 switched to datafile copy
input datafile copy RECID=56 STAMP=930564829 file name=+DATA_DUP/bdupdb/datafile/rcat_data.289.930564147
contents of Memory Script:
{
recover
clone database
noredo
delete archivelog ;
}
executing Memory Script
Starting recover at 14-DEC-16
using channel ORA_AUX_DISK_1
Finished recover at 14-DEC-16
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =''BDUPDB'' 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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: alter system set db_name = ''BDUPDB'' 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BDUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE '+DATA_DUP/bdupdb/datafile/system.297.930564143'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA_DUP/bdupdb/datafile/sysaux.296.930564143",
"+DATA_DUP/bdupdb/datafile/undotbs1.293.930564145",
"+DATA_DUP/bdupdb/datafile/users.294.930564143",
"+DATA_DUP/bdupdb/datafile/example.292.930564145",
"+DATA_DUP/bdupdb/datafile/rcat_data.289.930564147";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA_DUP in control file
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/sysaux.296.930564143 RECID=1 STAMP=930564855
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/undotbs1.293.930564145 RECID=2 STAMP=930564855
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/users.294.930564143 RECID=3 STAMP=930564855
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/example.292.930564145 RECID=4 STAMP=930564855
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/rcat_data.289.930564147 RECID=5 STAMP=930564855
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=930564855 file name=+DATA_DUP/bdupdb/datafile/sysaux.296.930564143
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=930564855 file name=+DATA_DUP/bdupdb/datafile/undotbs1.293.930564145
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=930564855 file name=+DATA_DUP/bdupdb/datafile/users.294.930564143
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=930564855 file name=+DATA_DUP/bdupdb/datafile/example.292.930564145
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=930564855 file name=+DATA_DUP/bdupdb/datafile/rcat_data.289.930564147
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 14-DEC-16
Option 4:
The following duplication command options fails with SPFILE parameter on same host.
RMAN> duplicate target database to BDUPDB spfile;
RMAN> duplicate target database to BDUPDB spfile
until time "TO_DATE('10/03/2016 10:15:00','MM/DD/YYYY HH24:MI:SS');
If you are duplicating a database on the same machine you will probably need to create a PFILE/SPFILE manually from the primary database SPFILE, manually change the values and avoid the SPFILE clause in the duplicate command.
We can use spfile (without parameters like db_create_file_dest) when we duplicate database to another host with the same directory structure. When we removed spfile from run block, Oracle created spfile from our pfile. When we use spfile, Oracle restores spfile from backup and only changes dbname parameter.
Option 5:
In this option we use SPFILE with parameters. If we set parameters DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT then error occurred due to same host, ASM to ASM and OMF files. So we set other parameters as
RMAN> duplicate target database to BDUPDB
2> until SCN 5189373
3> SPFILE parameter_value_convert '+DATA', '+DATA_DUP'
4> set control_files='+DATA_DUP','+FRA_DUP'
5> set db_create_file_dest='+DATA_DUP'
6> set db_recovery_file_dest'+FRA_DUP';
Starting Duplicate Db at 03-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
set until scn 5189373;
restore clone spfile to '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileBDUPDB.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileBDUPDB.ora''";
}
executing Memory Script
executing command: SET until clause
Starting restore at 03-JAN-17
using channel ORA_AUX_DISK_1
WARNING: A restore time was estimated based on the supplied UNTIL SCN
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileBDUPDB.ora
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303 tag=TAG20161210T112139
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 03-JAN-17
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileBDUPDB.ora''
contents of Memory Script:
sql clone "alter system set db_name =''BDUPDB'' comment=''duplicate'' scope=spfile";
sql clone "alter system set control_files = ''+DATA_DUP'', ''+FRA_DUP'' comment= '''' scope=spfile";
sql clone "alter system set db_create_file_dest = ''+DATA_DUP'' comment= '''' scope=spfile";
sql clone "alter system set db_recovery_file_dest = ''+FRA_DUP'' comment= '''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''BDUPDB'' comment= ''duplicate'' scope=spfile
sql statement: alter system set control_files = ''+DATA_DUP'', ''+FRA_DUP'' comment= '''' scope=spfile
sql statement: alter system set db_create_file_dest = ''+DATA_DUP'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''+FRA_DUP'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2388422656 bytes
Fixed Size 2215664 bytes
Variable Size 1342177552 bytes
Database Buffers 1023410176 bytes
Redo Buffers 20619264 bytes
contents of Memory Script:
{
set until scn 5189373;
sql clone "alter system set control_files = ''+DATA_DUP/bdupdb/controlfile/current.297.932297031'',
''+FRA_DUP/bdupdb/controlfile/current.319.932297031'' comment= ''Set by RMAN'' scope=spfile";
''+FRA_DUP/bdupdb/controlfile/current.319.932297031'' comment= ''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name = ''BDUPDB'' 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 = ''+DATA_DUP/bdupdb/controlfile/current.297.932297031'',
''+FRA_DUP/bdupdb/controlfile/current.319.932297031'' comment= ''Set by RMAN'' scope=spfile
''+FRA_DUP/bdupdb/controlfile/current.319.932297031'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORA11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''BDUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2388422656 bytes
Fixed Size 2215664 bytes
Variable Size 1342177552 bytes
Database Buffers 1023410176 bytes
Redo Buffers 20619264 bytes
Starting restore at 03-JAN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=11 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/autobackup/2016_12_10/s_930223299.690.930223303 tag=TAG20161210T112139
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output file name=+DATA_DUP/bdupdb/controlfile/current.297.932297031
output file name=+FRA_DUP/bdupdb/controlfile/current.319.932297031
Finished restore at 03-JAN-17
database mounted
contents of Memory Script:
{
set until scn 5189373;
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;
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
Starting restore at 03-JAN-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA_DUP
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA_DUP
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/nnndf0_tag20161210t111827_0.692.930223111 tag=TAG20161210T111827
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:15
Finished restore at 03-JAN-17
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=51 STAMP=932297739 file name=+DATA_DUP/bdupdb/datafile/system.293.932297065
datafile 2 switched to datafile copy
input datafile copy RECID=52 STAMP=932297740 file name=+DATA_DUP/bdupdb/datafile/sysaux.294.932297065
datafile 3 switched to datafile copy
input datafile copy RECID=53 STAMP=932297740 file name=+DATA_DUP/bdupdb/datafile/undotbs1.292.932297065
datafile 4 switched to datafile copy
input datafile copy RECID=54 STAMP=932297740 file name=+DATA_DUP/bdupdb/datafile/users.296.932297065
datafile 5 switched to datafile copy
input datafile copy RECID=55 STAMP=932297740 file name=+DATA_DUP/bdupdb/datafile/example.289.932297067
datafile 6 switched to datafile copy
input datafile copy RECID=56 STAMP=932297741 file name=+DATA_DUP/bdupdb/datafile/rcat_data.287.932297067
contents of Memory Script:
{
set until scn 5189373;
recover
clone database
delete archivelog;
}
executing Memory Script
executing command: SET until clause
Starting recover at 03-JAN-17
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file +FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039
archived log for thread 1 with sequence 7 is already on disk as file +FRA/ora11g/archivelog/2016_12_11/thread_1_seq_7.675.930349435
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297
channel ORA_AUX_DISK_1: piece handle=+FRA/ora11g/backupset/2016_12_10/annnf0_tag20161210t112137_0.688.930223297 tag=TAG20161210T112137
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA_DUP/bdupdb/archivelog/2017_01_03/thread_1_seq_5.322.932297745 thread=1 sequence=5
channel clone_default: deleting archived log(s)
archived log file name=+FRA_DUP/bdupdb/archivelog/2017_01_03/thread_1_seq_5.322.932297745 RECID=946 STAMP=932297745
archived log file name=+FRA/ora11g/archivelog/2016_12_10/thread_1_seq_6.696.930240039 thread=1 sequence=6
archived log file name=+FRA/ora11g/archivelog/2016_12_11/thread_1_seq_7.675.930349435 thread=1 sequence=7
media recovery complete, elapsed time: 00:01:27
Finished recover at 03-JAN-17
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =''BDUPDB'' 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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2388422656 bytes
Fixed Size 2215664 bytes
Variable Size 1342177552 bytes
Database Buffers 1023410176 bytes
Redo Buffers 20619264 bytes
sql statement: alter system set db_name = ''BDUPDB'' 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 2388422656 bytes
Fixed Size 2215664 bytes
Variable Size 1342177552 bytes
Database Buffers 1023410176 bytes
Redo Buffers 20619264 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BDUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE '+DATA_DUP/bdupdb/datafile/system.293.932297065'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA_DUP/bdupdb/datafile/sysaux.294.932297065",
"+DATA_DUP/bdupdb/datafile/undotbs1.292.932297065",
"+DATA_DUP/bdupdb/datafile/users.296.932297065",
"+DATA_DUP/bdupdb/datafile/example.289.932297067",
"+DATA_DUP/bdupdb/datafile/rcat_data.287.932297067";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA_DUP in control file
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/sysaux.294.932297065 RECID=1 STAMP=932297860
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/undotbs1.292.932297065 RECID=2 STAMP=932297860
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/users.296.932297065 RECID=3 STAMP=932297860
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/example.289.932297067 RECID=4 STAMP=932297860
cataloged datafile copy
datafile copy file name=+DATA_DUP/bdupdb/datafile/rcat_data.287.932297067 RECID=5 STAMP=932297860
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=932297860 file name=+DATA_DUP/bdupdb/datafile/sysaux.294.932297065
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=932297860 file name=+DATA_DUP/bdupdb/datafile/undotbs1.292.932297065
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=932297860 file name=+DATA_DUP/bdupdb/datafile/users.296.932297065
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=932297860 file name=+DATA_DUP/bdupdb/datafile/example.289.932297067
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=932297860 file name=+DATA_DUP/bdupdb/datafile/rcat_data.287.932297067
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 03-JAN-17
RMAN>
That's it!!!
