Interesting method of creation copy database in the same ASM diskgroup.

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s