Re: duplicating database

From: Lizzie <GreenRaven_at_worldnet.spamless.att.net>
Date: 19 Sep 1999 15:53:15 GMT
Message-ID: <01bf02b7$50849480$d3b44f0c@liznet.bcgroup.com>

I just needed to do the same thing, and got a good procedure from someone else out here.
In a nutshell, what you can do is generate a sql script that will recreate your control files by running a trace on the instance you are copying, then you copy all the data files for that instance to the new location, then edit that script to reflect the new location/name and run it to rebuild the files.

Here is a copy of one of the instruction sets that I was given:

Here is a way to clone your database that is much faster and cleaner than exp/imp you can place your datafiles on any disk or remove tablespace or datafiles you know are not used or Rename the database. You can do it from tape it hot backup with the database in archive mode.

Steps

Choose the physical layout of the database datafiles & that you have the need space.
Update the init.ora and config.ora files that is if you use both. change the logs if location it is different the database name and many memory parameters if the box is smaller.
Grerate control file script. You can do this from the database you are cloning with the command: ALTER DATABASE BACKUP CONTROLFILE TO TRACE; the script generated by the command can be found in the location specified by background _dump_dest parameter. rename the file exp. (control.sql)
Edit the control file script Delete everything accept the create controlfile statement from the control.sql remove all occurrences of Reuse in the control.sql The Reuse option grants Oracle an authorization to overwrite any existing files of the same size. Replace NORESTLOGS with RESETLOGS. Replace the database name if needed and add reuse set.

Replace the paths names of all the datafiles & logfiles with the new path names if needed.

CREATE CONTROLFILE reuse set DATABASE "FINP" resetlogs ARCHIVELOG

    MAXLOGFILES 64
    MAXLOGMEMBERS 5
    MAXDATAFILES 1022
    MAXINSTANCES 10
    MAXLOGHISTORY 1000
LOGFILE 5. Restore the datafiles from tape or from the database shutdown or in

hot backup mode with the database in archiving mode.

6. If you are cloning from production in hot backup mode. After you have moved the lasted datafiles and taking the database out of hot backup up do the following.

alter system checkpoint;
alter system switch logfile;
Move over all but the current archive log. If you do not the database will ask for log after log.

******Rememeber to setup the environment of the database**********

7. Create Control file

commands:

A. svrmgrl
B. startup nomount;
C. @control.sql
D. alter database open resetlogs;

**********You will get an error like
ORA-01195 online backup of file needs more recovery to be consistent ORA-01110 data file 1; '/u01/oradata/finp/system.dbf'

E. recover database using backup controlfile until cancel;

alter all the archived logs have been applied.

F. alter database open resetlogs.

This process worked well for me. The only thing you have to watch out for is to make sure all the references to your old database (the one you are copying from) are removed from the control file build script, or you run the trashing the old stuff.

Also, if you use a pfile other than the default one you will have to add the pfile to the STARTUP NOMOUNT statement that you'll find in the control file trace script. All our instances are different names so we always use the pfile= in every startup.

In addition, after the "alter database open resetlogs" I took the instance down clean, then brought it up normally. Call me supersitious, but it seemed cleaner that way!

Someone else out here also mentioned you might need to do an alter database set global_name something-or-other (I don't remember exact syntax) but
I didn't do that and it seems ok right now....

Good luck... Received on Sun Sep 19 1999 - 10:53:15 CDT