The fastest way to clone/copy oracle databases on the same machine

oracleperformance

I want to clone oracle 11g databases on the same machine and I need to change user etc. from e.g. PROD to DEV.

For that I use expdp/impdp, but this tends to be slow with increasing database size. I need to do this on linux and windows machines.

Is using expdp/impdp the fastest way to do this?

Or should I use a completly different way of doing this?
i.e. do not use the same machine,

use two machines i.e. two separate oracle servers

or install multiple oracle servers on the same machine?

Best Answer

I prefer to use the RMAN clone method. The overall method is to start with the PROD database, and then a DEV database that is in a NOMOUNT state with all datafiles/controlfiles/tempfiles/archivelogs removed:

oracle> rman target sys@prod CATALOG rman/x@rman_catdb

RMAN> list backup;

  1    1399    53289602077 15-DEC-11 53289629964 15-DEC-11
  1    1400    53289629964 15-DEC-11 53289657925 15-DEC-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
88      Full    20.52M     DISK        00:00:00     15-DEC-11
        BP Key: 88   Status: AVAILABLE  Compressed: NO  Tag: TAG20111215T133438
        Piece Name: /u01/backups/prod/PROD_20111215_c-4198857860-20111215-02
  SPFILE Included: Modification time: 15-DEC-11
  SPFILE db_unique_name: PROD
  Control File Included: Ckp SCN: 53295424420   Ckp time: 15-DEC-11

target database Password:
connected to target database: PROD (DBID=4198857860)

RMAN> connect auxiliary /

connected to auxiliary database: DEV (not mounted)

RMAN> run {
2> set until scn 53289602077;
3> duplicate target database to dev;
4> }

This gives me a block-for-block recreation of the production level database, and depending on how many threads you give RMAN, can be completed pretty quickly.

From there, I have a set of environment specific scripts I run that change passwords, null out email addresses, etc in the DEV database to configure it the way it needs to be.

Related Question