How to clone my database ?

I need to clone my database named PROD_DB but without deleting the original

how to do it ???

Help me please

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
You could stop the db, copy the files (to the other server), and create the database over there
--> be aware that this leaves you with 2 database with the same internal DBID

You can export the database, create a "blank" database (same tablespaces etc), and then import the dump over there

You can use RMAN to DUPLICATE the database
The following steps must be on Source -Production instance/Server                        

                        Step 1. Shutdown database in normal mode and start it up in restricted                                      
                        Step 2. Take the backup of control file
                        Step 3. Shutdown database again  in normal mode.              
                        Step 4. Copy /FTP  init parameter file ,control file script  and all the                                      
                                   database file on the destination server/location, once all the files                                      
                                   are successfully copied, you may startup the database normally.

The following steps must be on destination – Clone instance/server
                        Step 5. Edit init parameter file and control file script.
                        Step 6.  New Environment setup .
                        Step 7. Connect with svrmgrl and recreate control file
                        Step 8. Open the database in resetlogs.
                        Step 9. Shutdown the database in normal mode
                        Step 10. Take the cold backup and start the database in archive/non                                          
                                     archive mode.

lobitojcAuthor Commented:
thank's sujit
in the case of two databases are in the same server what change in your explanation ???

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

make sure that you are specifying a new sid, and a new dbname
For giving the db a new name,
1) create a backup controlfile trace
2) for the new sid, in the init file give the database a new name
3) change the trace file command to specify the new database
4) after creating the new controlfile recover the database
SQL> recover database with backup controfile;

on second thoughts
AngelIII's answer (exp/imp) seems to be the best solution if your database is not too big (dmp file crossing 2gb)
lobitojcAuthor Commented:
can you be more specific
i'm new in oracle i need to know te complete process to make a clone of my database
how commands to create a backup controlfile or how to change the trace file command etc etc.

thank's so much guys
SQL>alter database backup controlfile to trace;
will create the trace file. To locate the same give the commands (logged on with the userid "sys as sysdba")
SQL>show parameter background_dump_dest
SQL> select spid from v$session s, v$process p where
s.paddr=p.addr and s.username='SYS';
(Make sure that you are the only on connected as sys)
the file will be <path from background_dump_dest>\ora_<spid from the second command>.ora

Shutdown your database
and copy the datafiles and logfiles to a new location

now in the command prompt(I am assuming that you are on Windows)
C:\ oradim -new -sid <newsid>
c:\set ORACLE_SID=<newsid>
Modify your trace file to leave only the create controfile command, and identify the new locations of the files.
SQL>create controlfile....
SQL> recover using backup controlfile;
SQL> alter database open resetlogs


use the database configuration asst to create a new database
c:\set ORACLE_SID=<oldsid>
c:\exp system/manager full=y file=newdb.dmp compress=n
c:\set ORACLE_SID=<newsid>
c:\imp system/manager full=y file=newdb.dmp ignore=y

if you have any problems or errors please get back to us

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I've used this many times.  If you are on a Unix server, pull this note up on Metalink.

Doc ID:       Note:18070.1
Subject:       How to Make a Copy of a Database on the Same Unix Machine
Mark GeerlingsDatabase AdministratorCommented:
Please tell us which operating system your server uses.  If it is Windows, it is possible to run two separate Oracle databases (instances) on one server, but it may not be wise to do that, unless you carefully set the sizes of the two SGAs and use the Windows Performance Monitor to watch for resource contention and ajdust the SGAs to reduce the contention.
lobitojcAuthor Commented:
i'm running my database in Windows XP

Can you explain to me why i need to  set SGS's sizes ???

What's resource contention ???
Mark GeerlingsDatabase AdministratorCommented:
"What's resource contention ?"
When you have two (or more) processes competing for the same resource, like CPU cycles, or memory (RAM) or disk reads or writes.  Competition for these computer resources is very likely with multiple Oracle databases on one server.

Why do you need to tune the Oracle SGAs carefully if you have two Oracle databases on the same Windows computer?
To try to minimize resource contention, and give you the best possible performance with what your hardware can support.

I've been running corporate-sized Oracle databases on Windows for nine years, and it can be done, but it works best if you have 6-12 SCSI disks in the server, with some of them RAID1, some RAID5 and possibly some non-RAID or RAID0 with the database files spread out carefully to take advantage of the different characteristics of the different RAID levels.  For example, the Oracle system and data tablespaces should be on RAID5, the rollback (or undo) tablespace(s) and the redo logs should be on RAID1, and the temp tablespace doesn't need RAID protection at all, so it is fine on RAID0 or non-RAID.  It certainly should not be on RAID5.  Likewise, the Windows swap file should *NOT* be on a RAID5 disk!

You also really have to watch for memory contention with multiple Oracle databases on one server.  That's where the parameters in the init*.ora file (or the spfile) become significant, since they control the size of the SGA and directly affect how Oracle uses memory.  You want to use as much RAM as possible for caching data blocks and SQL statements, but you don't want Windows to start paging memory out to its swap file, or your performance will really suffer.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i would say point split <anand_2000v and markgeer>, and eventually  sujit_kumar
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.