Re: copy or transfer a db to another

Joel Bergeron wrote:
>
> What is the best way to copy a db(schema) to another with all triggers,
> procedures, views and data?

COPYING ORACLE DBs WITHOUT EXPORT/IMPORT You many have seen this before but if you have not please read on...

Modify the init.ora of the target db to incorporate any necessary items from
the source DB. Don't forget to modify the control_files, rollback segments
and DB name parameters. In the source database create the controlfile script
by issuing the command:

       alter database backup controlfile to trace; This will create a script to recreate the control file for the source db.
Copy the control file script and take note of the files mentioned in it. To
copy the database just copy the source db datafiles (after you shutdown normal or immediate the source db) and redo logs to the target db. Modify
the create controlfile script to reflect the location of the new DB. Change
the first line of the script to set the new DB name:

       CREATE CONTROLFILE REUSE SET DATABASE NEWNAME RESETLOGS You do not need to recover the database so you can remove the commands which
follow the create controlfile command. All you need to do after the controlfiles are created is to issue the command:

       alter database open resetlogs;

Now that the script is modified all you need to do is to make sure that the
controlfile for the new DB is in place and you are ready to roll. Make sure
you export ORACLE_SID to the new DB name and then login to svrmgrl and do a
connect internal. Run the create controlfile script and vola, you have copied an entire database (probably in half the time an export / import would
have taken).

You should request a backup of the new DB as soon as possible since it will
not be recoverable until you do that. You will also want to change the global_name of the new DB since it will inherit the old source DB global_name
(alter database rename global_name to 'newdbname';. You will also want to
place the DB in archivelog mode after you are finished.

JD

--
JD White
Database Administration
j.white_at_interpath.net

Office:	919.388.6254
Fax:	919.388.6578
Pager:	800.614.7512

1700 Perimeter Park Drive
MD-A218
Morrisville, NC 27560

Received on Mon Apr 12 1999 - 08:31:43 CDT