clone a database on the same machine

[lwangf]

Hi.

I am going to clone a database on the same box. Please correct me if the following steps is wrong

Using exp/imp:
a. run create database script to create the database, run the catalog.sql and catproc.sql to create the data dictionary views and plsql procedures
b. create the tablespace structure same as the target database
c. create the users same as the target database
d. export the source database, then import to the target databsae.

I know there is another way using "create control file " to copy a database, but I have several questions:
a. do I still run the create database script, catalog.sql etc.
b. do I still need to create the tablespace structure for the target database

Thanks for help!

[marist89]

There are several threads related to this topic:
[url]http://www.dbasupport.com/forums/showthread.php?threadid=6179[/url]
[url]http://www.dbasupport.com/forums/showthread.php?threadid=5586[/url]

and on metalink:
[url]http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=15390.1[/url]

Basically, you will have to:
1. Shutdown (immediate or normal)
2. copy all your files (data files, control files, redo log files, archive log files, parameter files) to different directories on your box.
3. edit control file creation script & init.ora
4. Recreate your control files (see threads) with new instance name.

[r_sonakiya]

You need not to run any create database scripts.Don't even create the tablespace(s).Your backup of the database you are trying to clone will do all this. You just create a backup copy of your control file and edit it for new database.

Edit the db_name parameter of the init.ora file and change it to new name.

Stay Connected with NEW ORACLE_SID

Execute your just modified CONTROL_FILE(Double checkin this for STARTUP NOMOUNT COMMAND)

DO THE INCOMPLETE RECOVERY
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

then once it asls for REDO LOG file that you don't have <ENTER>

ALTER DATABASE RECOVER CANCEL
and it will come back saying 'statement processed'

Then at the SVRMGRL prompt
SVRMGRL>ALTER DATABASE OPEN RESETLOGS
SVRMGRL>SELECT NAME FROM V$DATABASE
to confirm the new database name
SVRMGRL>SHUTDOWN IMMEDIATE

[lwangf]

Thanks all!

I am still not quite sure about the recreate control file thing. Do I have to backup control file to trace first ?
How the tablespace be created?
Do I have to create the user account?

[marist89]

>I am still not quite sure about the recreate control file >thing. Do I have to backup control file to trace first ?
Yes.

>How the tablespace be created?
>Do I have to create the user account?
In your case, you want to clone the entire database, no? You have an exact copy of the database that existed before, you have just changed its name. Now, you have a database named DB1 and an exact copy named DB2.

[lwangf]

Thanks! It helps a lot.

One more question. When I issue "Alter database backup control file to trace" using svrmgrl, do I need to make a backup first, do I need to shutdown the source database?

[jmodic]

No, for dumping the controlfile to trace you don't have to shut down the database - in fact if you do you won't be able to create the trace file ;-)). For isuing ALTER DATABASE BACKUP CONTROLFILE TO TRACE the instance must know the contents of the controlfile, so the database must be open or at least in MOUNT state. There also isn't any point in backing up a database just because you are dumping the controlfile to trace (although there can never be too much of *valid* backups ;-)) ).

For other isues regarding the cloning process others have specified just about everything...