1

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • I'm pretty noob on this. I know SQL and the basics of oracle administration. I'll look into RMAN - but could use any specifics I can get. Could you also post an example script to set passwords etc?
    – Eduard Ruffert
    Sep 11, 2014 at 19:12
  • I have a script, called refresh_pwd.sql that I use on the file system. It literally just contains "alter user blah identified by pwd;" over and over again. Because it is a Dev DB, the passwords are common knowledge.
    – captjackvane
    Sep 11, 2014 at 19:23
  • One other thing I forgot to mention, to do the RMAN clone you'll need to set some parameters in the DB before you put it in a NOMOUNT state: alter system set db_file_name_convert='+DATA/prod','+DATA/dev','+FRA/prod','+FRA/dev' scope=spfile; alter system set log_file_name_convert='+DATA/prod','+DATA/dev','+FRA/prod','+FRA/dev' scope=spfile; alter system set cluster_database=FALSE scope=spfile;
    – captjackvane
    Sep 11, 2014 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.