Re: Copy Database

In article <4qcl16$boa_at_cronkite.seas.gwu.edu>, Shaochun Lin <lovedog_at_gwis2.circ.gwu.edu> writes
>Please help! I want to find best way to dupliate a database, the basic
>idea in my mind is, create a new database and create exact size of
>tablespaces and doing full export and full import. Does someone can give
>me a advise and are there any hole in this procedure. Thanks, SC

One approach which has been used at this site is to do the following...

1. Backup your controlfile for your 'source' Instance to a trace file: alter database backup controlfile to trace;
2. Shutdown the 'source' Instance tidily
3. Copy all the data/index files and on-line ReDo Log files to files of a different name which will be used in the 'target' Instance
4. Create a new initSID.ora for your 'target' Instance
5. Edit the control file trace produced above to make the following changes... Amend all Data File Names to match those in the 'target' Instance Amend the Instance Name to be that of the 'target' Instance Insert the word 'SET' before the word 'DATABASE' on the CREATE CONTROLFILE statement, and change the NORESETLOGS to RESETLOGS. Remove the RECOVER DATABASE line Amend the last line to be ALTER DATABASE OPEN RESETLOGS
6. Log in to svrmgrl on your 'target' Instance connect internal; execute the amended controlfile script

At this point it is safe to start up the 'source' instance again.

This may seem a little more long-winded than doing a Create Db followed by full exports and imports but the beauty of it is that you can script the whole thing and use the same approach to refresh databases as and when required (you don't need to recreate the initSID.ora or the control file if the physical DB structure hasn't changed)

We use this approach during Testing Phases of our project and we manage to have 1/2Gb Database refreshed and back up and running within about 15 minutes of the request coming in.

We are currently running Oracle 7.1.6 under AIX 3.2.5b and it works fine for us.

Hope this is of use (for a small fee I can supply scripts which will do it all for you!!)

-- 
Brian McCabe
McCabe IT Consulting Limited
brian_at_mitc.demon.co.uk