Sometimes I need to make a copy of database in the same ASM diskgroup for tests. What are the main steps you know to make a copy database? Backup database, backup controlfile, prepare pfile, restore controlfile, restore database, recover database.
For example you have one instance with datafile path ‘+DATA/base1’ and you want to create one more instance with datafile path ‘+DATA/base2′. Assume that you’ve done those steps, backup controlfile, backup database and prepared pfile. Obviously you need to use ‘db_unique_name’ in both instances pfiles/spfiles. What next? You should start your second instance with ‘nomount’ option and have to try to restore ‘controlfile’.
SQL> startup nomount pfile='/tmp/pfile';
RMAN> restore standby controlfile from '+FRA/base1/backupset/2014_03_14/ncnnf0_tag20140314t150912_0.266.842195359'; Starting restore at 14 mar 2014 15:19 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 output file name=+DATA/base2/controlfile/current.1803.842195953 Finished restore at 14 mar 2014 15:19
The next step you should do, mount database and to try to restore datafiles from backup which you previously prepared.
RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> restore datafile 1; using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to +DATA/base1/datafile/system.328.841732809 channel ORA_DISK_1: reading from backup piece +FRA/base1/backupset/2014_03_12/nnndf0_tag20140312t144756_0.261.842021421
But unfortunately you’ll stumbled upon pitfalls! Path should be ‘+DATA/base2’ compared ‘+DATA/base1’.
This had happened because in the ‘controlfile’ contains information about old path for files, path for the first instance! And this situation I use some trick with ‘datafilecopy’ option.
RMAN> backup as copy database format '+DATA'; Starting backup at 14 mar 2014 15:41 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00012 name=+DATA/base1/datafile/apps_undots2.274.841716353 output file name=+DATA/base2/datafile/apps_undots2.1807.842197313 tag=TAG20140314T154147 RECID=210180 STAMP=842197566 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:15 ... ... RMAN> switch database to copy;
After this datafiles will placed in correct place and we can use recover database.