Rman duplicate is one of the nicest features oracle added in recent years and we make use of it a lot, It first came in with oracle 9 if memory serves and has been enhanced with every version since then. However there are cases where it's not available - such as pre 9i database (which of course no one uses any more......), if you dont have access to the live database as sysdba for any reason (pre 11.2) or perhaps if you don't even use rman (wake up and smell the coffee).
The example below shows how a duplicate can be done (from live backups on tape) without actually using the duplicate command - it relies on you having copied your (in my case tsm, though the principle is the same with any media management software) config from live to test.
The reason the live tsm config has to be copied over is that all backups are recorded in a tsm database (in some proprietary format) against the server/db name. Unless you'pretend' to be that server you cannot restore the files. I think some vendors actually have some settings that prevent you from being able to restore to another server unless you have some special security setting or approval in place. In this case it was not necessary.
So lets kick off the example - I want to duplicate 'LIVE' to 'TEST'
The first thing i do is create an entry in the oratab for 'LIVE' on the test server, i then run oraenv to set up the correct environment for it.
Then i create the most basic of pfile's for the database (initTEST.ora) which just has one line in it
db_name=LIVE
This instance can now be started up into nomount mode (so just the memory and processes are created).
OK now we have something we can connect to with rman but we need a way of telling rman that we want to restore the live backup of LIVE to this database. The rman catalog has loads of databases in and even though our SID is LIVE it will not let us just use that - everything it does is driven off the dbid of the database that was backed up.
So how do we find the dbid?
Easy - just query the rman catalog:
sqlplus rman/rman@rcat
1* select dbid from rc_database where name='LIVE'
SQL> /
DBID
----------
2363561245
Now we have the dbid we can use that to do the first stage of the restore - the spfile (this step isn't strictly necessary you could just make up a few values and it would work OK - but it's nice to have the same settings that live does).
So now we connect to our new target and the rman catalog and restore the spfile
[oracle@server]:LIVE:/tsm/LIVE/conf# rman target=/ catalog=rman/rman@rcat
Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jan 26 22:17:53 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: LIVE (not mounted)
connected to recovery catalog database
RMAN> set dbid=2363561245
executing command: SET DBID
database name is "LIVE" and DBID is 2363561245
RMAN> run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/tsm/LIVE/conf/tdpo.opt)';
restore spfile;}2> 3>
allocated channel: t1
channel t1: sid=36 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting restore at 26-JAN-14
channel t1: starting datafile backupset restore
channel t1: restoring SPFILE
output filename=/oracle/10.2.0.4/dbs/spfileLIVE.ora
channel t1: reading from backup piece cf_auto_c-2363561245-20140126-2d
channel t1: restored backup piece 1
piece handle=cf_auto_c-2363561245-20140126-2d tag=TAG20140126T220900
channel t1: restore complete, elapsed time: 00:00:03
Finished restore at 26-JAN-14
released channel: t1
RMAN>
Now based on the content of this spfile we can create all the appropriate directories it needs (trace and audit destination etc) we should also create any directories/filesystems that are needed to host the datafile/logfile (an easy way to do this for a lot of the files is to use the 'report schema' command from rman that gives a nice summary).
The parameters from the spfile should be extracted and put into the pfile in use (note this can be done in a single command from rman i think - restore spfile as pfile '/tmp/xxx.ora'; will work i think)
Anyway assuming we have now created all the correct filesystems/directories and the like we can move on to the next stage (I'm assuming here that all files are going to be restore to the same location as they were on live - if they aren't you need to look at the DB_FILE_NAME_CONVERT /LOG_FILE_NAME_CONVERT parameters to auto rename files as they are restored).
Ok the next stage (after restarting the database with the updated parameter file)is to bring the controlfile back
RMAN> run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/tsm/LIVE/conf/tdpo.opt)';
restore controlfile;}2> 3>
allocated channel: t1
channel t1: sid=444 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting restore at 26-JAN-14
channel t1: starting datafile backupset restore
channel t1: restoring control file
channel t1: reading from backup piece cf_auto_c-2363561245-20140126-2d
channel t1: restored backup piece 1
piece handle=cf_auto_c-2363561245-20140126-2d tag=TAG20140126T220900
channel t1: restore complete, elapsed time: 00:00:04
output filename=/oracle/LIVE/origlog/control01.ctl
output filename=/oracle/LIVE/mirrlog/control02.ctl
output filename=/oracle/LIVE/oradata/control03.ctl
Finished restore at 26-JAN-14
released channel: t1
RMAN>
Again pretty simple - the file is duplexed to whatever is specififed in the control_files parameter of your new init file.
At this point we can now mount the database using these controlfiles
RMAN> sql 'alter database mount';
sql statement: alter database mount
RMAN>
And now it is mounted we can restore from the most recent full backup
RMAN> run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/tsm/LIVE/conf/tdpo.opt)';
restore database;}
2> 3>
allocated channel: t1
channel t1: sid=324 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting restore at 27-JAN-14
skipping datafile 30; already restored to file /oracle/LIVE/oradata/ts_sapbw12.dbf
skipping datafile 33; already restored to file /oracle/LIVE/oradata/endur0106.dbf
skipping datafile 5; already restored to file /oracle/LIVE/oradata/aw20s0101.dbf
skipping datafile 9; already restored to file /oracle/LIVE/oradata/users0101.dbf
skipping datafile 11; already restored to file /oracle/LIVE/oradata/endur0102.dbf
skipping datafile 7; already restored to file /oracle/LIVE/oradata/endur0101.dbf
skipping datafile 10; already restored to file /oracle/LIVE/oradata/USERS_TIV01.dbf
skipping datafile 4; already restored to file /oracle/LIVE/oradata/users01.dbf
skipping datafile 6; already restored to file /oracle/LIVE/oradata/aw20x0101.dbf
skipping datafile 23; already restored to file /oracle/LIVE/oradata/endur0103.dbf
skipping datafile 2; already restored to file /oracle/LIVE/oradata/undotbs01.dbf
skipping datafile 17; already restored to file /oracle/LIVE/oradata/users_tiv_itm6.dbf
skipping datafile 34; already restored to file /oracle/LIVE/oradata/endur0107.dbf
skipping datafile 25; already restored to file /oracle/LIVE/oradata/eisdba04.dbf
skipping datafile 29; already restored to file /oracle/LIVE/oradata/ts_sapbw11.dbf
skipping datafile 31; already restored to file /oracle/LIVE/oradata/endur0105.dbf
skipping datafile 13; already restored to file /oracle/LIVE/oradata/ts_sapbw01.dbf
skipping datafile 16; already restored to file /oracle/LIVE/oradata/ts_sapbw04.dbf
skipping datafile 21; already restored to file /oracle/LIVE/oradata/MATLAB_SYS01.dbf
skipping datafile 8; already restored to file /oracle/LIVE/oradata/eisdba01.dbf
skipping datafile 24; already restored to file /oracle/LIVE/oradata/ts_sapbw08.dbf
skipping datafile 28; already restored to file /oracle/LIVE/oradata/ts_sapbw10.dbf
skipping datafile 3; already restored to file /oracle/LIVE/oradata/sysaux01.dbf
skipping datafile 18; already restored to file /oracle/LIVE/oradata/ts_sapbw05.dbf
skipping datafile 27; already restored to file /oracle/LIVE/oradata/ts_sapbw09.dbf
skipping datafile 12; already restored to file /oracle/LIVE/oradata/eisdba02.dbf
skipping datafile 14; already restored to file /oracle/LIVE/oradata/ts_sapbw02.dbf
skipping datafile 20; already restored to file /oracle/LIVE/oradata/ts_sapbw06.dbf
skipping datafile 15; already restored to file /oracle/LIVE/oradata/ts_sapbw03.dbf
skipping datafile 22; already restored to file /oracle/LIVE/oradata/ts_sapbw07.dbf
skipping datafile 32; already restored to file /oracle/LIVE/oradata/dbspi_users01.dbf
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/LIVE/oradata/system01.dbf
restoring datafile 00019 to /oracle/LIVE/oradata/eisdba03.dbf
restoring datafile 00026 to /oracle/LIVE/oradata/endur0104.dbf
channel t1: reading from backup piece database_online_LIVE_837465391_20140121_118164.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837465391_20140121_118164.rman tag=TAG20140121T200028
channel t1: restore complete, elapsed time: 00:09:56
Finished restore at 27-JAN-14
released channel: t1
RMAN>
(note a load of skips at the start - thats just because i ran it then killed it and started again - it realised that a lot of the files were already restored)
Now we need to pick a time we want to restore to that is after the last backup but before 'now' - i picked a time 24 hours ago and we'll recover to that.
RMAN> run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/tsm/LIVE/conf/tdpo.opt)';
recover database until time "to_date('26/01/2014 13:30:00','dd/mm/yyyy hh24:mi:ss')";}2> 3>
allocated channel: t1
channel t1: sid=324 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting recover at 27-JAN-14
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00030: /oracle/LIVE/oradata/ts_sapbw12.dbf
destination for restore of datafile 00033: /oracle/LIVE/oradata/endur0106.dbf
channel t1: reading from backup piece database_online_LIVE_837806444_20140125_118404.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837806444_20140125_118404.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:01:25
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /oracle/LIVE/oradata/aw20s0101.dbf
destination for restore of datafile 00009: /oracle/LIVE/oradata/users0101.dbf
destination for restore of datafile 00011: /oracle/LIVE/oradata/endur0102.dbf
channel t1: reading from backup piece database_online_LIVE_837806750_20140125_118405.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837806750_20140125_118405.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:07
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /oracle/LIVE/oradata/endur0101.dbf
destination for restore of datafile 00010: /oracle/LIVE/oradata/USERS_TIV01.dbf
channel t1: reading from backup piece database_online_LIVE_837806985_20140125_118406.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837806985_20140125_118406.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:15
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /oracle/LIVE/oradata/users01.dbf
destination for restore of datafile 00006: /oracle/LIVE/oradata/aw20x0101.dbf
destination for restore of datafile 00023: /oracle/LIVE/oradata/endur0103.dbf
channel t1: reading from backup piece database_online_LIVE_837807180_20140125_118407.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837807180_20140125_118407.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:07
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /oracle/LIVE/oradata/undotbs01.dbf
destination for restore of datafile 00017: /oracle/LIVE/oradata/users_tiv_itm6.dbf
destination for restore of datafile 00034: /oracle/LIVE/oradata/endur0107.dbf
channel t1: reading from backup piece database_online_LIVE_837807415_20140125_118408.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837807415_20140125_118408.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:25
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00013: /oracle/LIVE/oradata/ts_sapbw01.dbf
destination for restore of datafile 00016: /oracle/LIVE/oradata/ts_sapbw04.dbf
destination for restore of datafile 00021: /oracle/LIVE/oradata/MATLAB_SYS01.dbf
channel t1: reading from backup piece database_online_LIVE_837807786_20140125_118410.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837807786_20140125_118410.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:15
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /oracle/LIVE/oradata/eisdba01.dbf
destination for restore of datafile 00024: /oracle/LIVE/oradata/ts_sapbw08.dbf
destination for restore of datafile 00028: /oracle/LIVE/oradata/ts_sapbw10.dbf
channel t1: reading from backup piece database_online_LIVE_837808001_20140125_118411.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837808001_20140125_118411.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:15
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00012: /oracle/LIVE/oradata/eisdba02.dbf
destination for restore of datafile 00014: /oracle/LIVE/oradata/ts_sapbw02.dbf
destination for restore of datafile 00020: /oracle/LIVE/oradata/ts_sapbw06.dbf
channel t1: reading from backup piece database_online_LIVE_837808526_20140125_118416.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837808526_20140125_118416.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:15
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00025: /oracle/LIVE/oradata/eisdba04.dbf
destination for restore of datafile 00029: /oracle/LIVE/oradata/ts_sapbw11.dbf
destination for restore of datafile 00031: /oracle/LIVE/oradata/endur0105.dbf
channel t1: reading from backup piece database_online_LIVE_837807560_20140125_118409.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837807560_20140125_118409.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:55
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oracle/LIVE/oradata/system01.dbf
destination for restore of datafile 00019: /oracle/LIVE/oradata/eisdba03.dbf
destination for restore of datafile 00026: /oracle/LIVE/oradata/endur0104.dbf
channel t1: reading from backup piece database_online_LIVE_837808176_20140125_118412.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837808176_20140125_118412.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:25
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /oracle/LIVE/oradata/sysaux01.dbf
destination for restore of datafile 00018: /oracle/LIVE/oradata/ts_sapbw05.dbf
destination for restore of datafile 00027: /oracle/LIVE/oradata/ts_sapbw09.dbf
channel t1: reading from backup piece database_online_LIVE_837808341_20140125_118413.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837808341_20140125_118413.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:00:55
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00015: /oracle/LIVE/oradata/ts_sapbw03.dbf
destination for restore of datafile 00022: /oracle/LIVE/oradata/ts_sapbw07.dbf
destination for restore of datafile 00032: /oracle/LIVE/oradata/dbspi_users01.dbf
channel t1: reading from backup piece database_online_LIVE_837808711_20140125_118417.rman
channel t1: restored backup piece 1
piece handle=database_online_LIVE_837808711_20140125_118417.rman tag=TAG20140125T200044
channel t1: restore complete, elapsed time: 00:02:55
starting media recovery
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94593
channel t1: restoring archive log
archive log thread=1 sequence=94594
channel t1: reading from backup piece redolog_LIVE_837808358_20140125_118414_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837808358_20140125_118414_1.rman tag=TAG20140125T203238
channel t1: restore complete, elapsed time: 00:01:56
archive log filename=/oracle/LIVE/oraarch/1_94593_658791716.arc thread=1 sequence=94593
archive log filename=/oracle/LIVE/oraarch/1_94594_658791716.arc thread=1 sequence=94594
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94595
channel t1: reading from backup piece redolog_LIVE_837809116_20140125_118419_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837809116_20140125_118419_1.rman tag=TAG20140125T204516
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94595_658791716.arc thread=1 sequence=94595
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94596
channel t1: reading from backup piece redolog_LIVE_837811961_20140125_118421_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837811961_20140125_118421_1.rman tag=TAG20140125T213240
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94596_658791716.arc thread=1 sequence=94596
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94597
channel t1: restoring archive log
archive log thread=1 sequence=94598
channel t1: reading from backup piece redolog_LIVE_837815703_20140125_118423_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837815703_20140125_118423_1.rman tag=TAG20140125T223503
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94597_658791716.arc thread=1 sequence=94597
archive log filename=/oracle/LIVE/oraarch/1_94598_658791716.arc thread=1 sequence=94598
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94599
channel t1: restoring archive log
archive log thread=1 sequence=94600
channel t1: reading from backup piece redolog_LIVE_837819387_20140125_118425_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837819387_20140125_118425_1.rman tag=TAG20140125T233627
channel t1: restore complete, elapsed time: 00:00:03
archive log filename=/oracle/LIVE/oraarch/1_94599_658791716.arc thread=1 sequence=94599
archive log filename=/oracle/LIVE/oraarch/1_94600_658791716.arc thread=1 sequence=94600
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94601
channel t1: restoring archive log
archive log thread=1 sequence=94602
channel t1: reading from backup piece redolog_LIVE_837822990_20140126_118427_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837822990_20140126_118427_1.rman tag=TAG20140126T003630
channel t1: restore complete, elapsed time: 00:00:08
archive log filename=/oracle/LIVE/oraarch/1_94601_658791716.arc thread=1 sequence=94601
archive log filename=/oracle/LIVE/oraarch/1_94602_658791716.arc thread=1 sequence=94602
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94603
channel t1: reading from backup piece redolog_LIVE_837826590_20140126_118429_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837826590_20140126_118429_1.rman tag=TAG20140126T013629
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94603_658791716.arc thread=1 sequence=94603
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94604
channel t1: reading from backup piece redolog_LIVE_837830190_20140126_118431_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837830190_20140126_118431_1.rman tag=TAG20140126T023630
channel t1: restore complete, elapsed time: 00:01:36
archive log filename=/oracle/LIVE/oraarch/1_94604_658791716.arc thread=1 sequence=94604
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94605
channel t1: reading from backup piece redolog_LIVE_837833785_20140126_118433_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837833785_20140126_118433_1.rman tag=TAG20140126T033625
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94605_658791716.arc thread=1 sequence=94605
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94606
channel t1: reading from backup piece redolog_LIVE_837837386_20140126_118435_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837837386_20140126_118435_1.rman tag=TAG20140126T043625
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94606_658791716.arc thread=1 sequence=94606
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94607
channel t1: restoring archive log
archive log thread=1 sequence=94608
channel t1: reading from backup piece redolog_LIVE_837840985_20140126_118437_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837840985_20140126_118437_1.rman tag=TAG20140126T053625
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94607_658791716.arc thread=1 sequence=94607
archive log filename=/oracle/LIVE/oraarch/1_94608_658791716.arc thread=1 sequence=94608
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94609
channel t1: restoring archive log
archive log thread=1 sequence=94610
channel t1: reading from backup piece redolog_LIVE_837844591_20140126_118439_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837844591_20140126_118439_1.rman tag=TAG20140126T063631
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94609_658791716.arc thread=1 sequence=94609
archive log filename=/oracle/LIVE/oraarch/1_94610_658791716.arc thread=1 sequence=94610
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94611
channel t1: reading from backup piece redolog_LIVE_837848189_20140126_118441_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837848189_20140126_118441_1.rman tag=TAG20140126T073629
channel t1: restore complete, elapsed time: 00:00:37
archive log filename=/oracle/LIVE/oraarch/1_94611_658791716.arc thread=1 sequence=94611
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94612
channel t1: restoring archive log
archive log thread=1 sequence=94613
channel t1: reading from backup piece redolog_LIVE_837851788_20140126_118443_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837851788_20140126_118443_1.rman tag=TAG20140126T083628
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94612_658791716.arc thread=1 sequence=94612
archive log filename=/oracle/LIVE/oraarch/1_94613_658791716.arc thread=1 sequence=94613
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94614
channel t1: restoring archive log
archive log thread=1 sequence=94615
channel t1: reading from backup piece redolog_LIVE_837855403_20140126_118445_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837855403_20140126_118445_1.rman tag=TAG20140126T093643
channel t1: restore complete, elapsed time: 00:00:08
archive log filename=/oracle/LIVE/oraarch/1_94614_658791716.arc thread=1 sequence=94614
archive log filename=/oracle/LIVE/oraarch/1_94615_658791716.arc thread=1 sequence=94615
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94616
channel t1: restoring archive log
archive log thread=1 sequence=94617
channel t1: reading from backup piece redolog_LIVE_837859071_20140126_118447_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837859071_20140126_118447_1.rman tag=TAG20140126T103751
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94616_658791716.arc thread=1 sequence=94616
archive log filename=/oracle/LIVE/oraarch/1_94617_658791716.arc thread=1 sequence=94617
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94618
channel t1: restoring archive log
archive log thread=1 sequence=94619
channel t1: reading from backup piece redolog_LIVE_837862789_20140126_118449_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837862789_20140126_118449_1.rman tag=TAG20140126T113949
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94618_658791716.arc thread=1 sequence=94618
archive log filename=/oracle/LIVE/oraarch/1_94619_658791716.arc thread=1 sequence=94619
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94620
channel t1: restoring archive log
archive log thread=1 sequence=94621
channel t1: reading from backup piece redolog_LIVE_837866494_20140126_118451_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837866494_20140126_118451_1.rman tag=TAG20140126T124134
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94620_658791716.arc thread=1 sequence=94620
archive log filename=/oracle/LIVE/oraarch/1_94621_658791716.arc thread=1 sequence=94621
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=94622
channel t1: reading from backup piece redolog_LIVE_837870089_20140126_118453_1.rman
channel t1: restored backup piece 1
piece handle=redolog_LIVE_837870089_20140126_118453_1.rman tag=TAG20140126T134129
channel t1: restore complete, elapsed time: 00:00:04
archive log filename=/oracle/LIVE/oraarch/1_94622_658791716.arc thread=1 sequence=94622
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-JAN-14
released channel: t1
RMAN>
There we see the initial apply of incremental backups followed by a series of archive logs to bring it to the correct time we asked for.
Now we should just be able to open the database with the resetlogs option
In my case here i ran it from rman and got some sort of exception but it seemed to half work - when i tried again in sqlplus i got this message
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
I then just tried to open and got
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Errors in file /oracle/admin/LIVE/udump/LIVE_ora_32308.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Mon Jan 27 11:58:00 2014
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 32308
Which is suspect is what the rman process got but threw the wrong error.
ANyway - lets see what is wrong:
startup upgrade followed by this quick select confirms the issue
SQL> select comp_name,version from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION
------------------------------
Oracle Database Catalog Views
10.2.0.3.0
Oracle Database Packages and Types
10.2.0.3.0
Oracle Workspace Manager
10.2.0.1.0
COMP_NAME
--------------------------------------------------------------------------------
VERSION
------------------------------
Oracle Enterprise Manager
10.2.0.3.0
We have 10.2.0.4 software opening a 10.2.0.3 database.
Lets run a quick catupgrd to bring it up to date (normally you wouldnt be doing this step.....)
Now we have a complete copy of LIVE called LIVE up and running on the test server - now we just need to rename it - lets use nid for that.
Girst we shut down the database and bring it up in mount mode and then run
[oracle@server]:LIVE:/oracle/home/oracle# nid target=sys/"password123!" dbname=TEST
DBNEWID: Release 10.2.0.4.0 - Production on Mon Jan 27 20:40:16 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database LIVE (DBID=2363561245)
Connected to server version 10.2.0
Control Files in database:
/oracle/LIVE/origlog/control01.ctl
/oracle/LIVE/mirrlog/control02.ctl
/oracle/LIVE/oradata/control03.ctl
Change database ID and database name LIVE to TEST? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2363561245 to 2136135728
Changing database name from LIVE to TEST
Control File /oracle/LIVE/origlog/control01.ctl - modified
Control File /oracle/LIVE/mirrlog/control02.ctl - modified
Control File /oracle/LIVE/oradata/control03.ctl - modified
Datafile /oracle/LIVE/oradata/system01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/undotbs01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/sysaux01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/users01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/aw20s0101.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/aw20x0101.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/endur0101.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/eisdba01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/users0101.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/USERS_TIV01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/endur0102.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/eisdba02.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw02.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw03.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw04.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/users_tiv_itm6.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw05.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/eisdba03.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw06.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/MATLAB_SYS01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw07.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/endur0103.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw08.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/eisdba04.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/endur0104.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw09.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw10.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw11.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/ts_sapbw12.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/endur0105.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/dbspi_users01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/endur0106.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/endur0107.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/TEMP_01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/temps0001.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/TEMP_TIV_01.dbf - dbid changed, wrote new name
Datafile /oracle/LIVE/oradata/temp_tiv_itm6.dbf - dbid changed, wrote new name
Control File /oracle/LIVE/origlog/control01.ctl - dbid changed, wrote new name
Control File /oracle/LIVE/mirrlog/control02.ctl - dbid changed, wrote new name
Control File /oracle/LIVE/oradata/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2136135728.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
We then have to update oratab, reset the env once doing that and amend DB_NAME and DB_UNIQUE_NAME in the init file (along with any other settings).
After that we just need to bring it up and resetlogs and we have the test database back
[oracle@server]:TEST:/oracle/10.2.0.4/dbs# s
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 27 20:42:35 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2286040 bytes
Variable Size 545562152 bytes
Database Buffers 524288000 bytes
Redo Buffers 1605632 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL>
So it's a fair bit of work, the rman part could all be put into one script, and you could have a pfile etc ready beforehand so it's not too much more work than duplicate if you run it regularly.
Comments
Post a Comment