Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002

    Unanswered: Refreshing QA from Prod

    Hello All,
    I am trying to find a way to refresh my QA environment with Production data. Can any body suggest a fastest way to achieve this, assuming that I have space available on QA to handle production sizes.

  2. #2
    Join Date
    Apr 2002
    California, USA
    Suppose you setup RMAN and running UNIX, then check for the procedure to clone database Clone Oracle DB on another host with RMAN

    Compile all the commands in shell script and voila..

    Check the latest Oracle DBA forums at:

    Hope that helps,

    Senior Oracle DBA
    Last edited by clio_usa; 05-16-02 at 02:57.

  3. #3
    Join Date
    May 2002
    Phoenix, AZ
    Just in case you aren't using RMAN at this time...

    Another option is to use exp and imp for these tasks. I'm assuming your database is under 50g, otherwise the imp will probably be too slow.

    BIG ISSUE: If your tablespaces are names exactly the same in the QA database as they are in the PROD database, then you can do an export of the data from prod and simply import the data into a NEW/FRESH/EMPTY qa schema using the fromuser=ProdApp touser=QAApp Ignore=Y options. The ignore tells import to ignore create statements that fail because they already exist -- like your tablespaces. All you have to do is create a user with permissions - no objects - and let imp bring them all in for you.


    If you have different tablespace names in QA than you have in PROD,
    you can still use this technique, but you have to create all the schema objects first with the correct (qa) tablespace names. Imp will ignore the create failures due to existing objects and will start importing the data.

    And assuming you have scripts to create the schema owner and all of the schema objects in the desired tablespaces....

    1. Truncate all the data by dropping the schema owner user and then re-create the schema owner and the schema objects from your scripts.
    e.g. drop user qa_app cascade;

    2. Re-create the schema owner. See attached file cr_user.sql

    3. Re-create the schema objects by running your create scripts while connected as the schema owner (qa_app or whatever).

    3. Disable all foreign key constraints. See attached file mk_disable_enable_fks.sql which generates 2 scripts...

    4. Export data from prod
    exp userid=system/xxxx@prod file=prod.dmp buffer=1048576 compress=N full=Y
    exp userid=system/xxxx@prod file=prod.dmp buffer=1048576 compress=N owner=PRODApp

    5. If the sequence next values are important to you, then drop your fresh ones and let imp bring them in from prod at the nextval they were at then! The following query will generate the drop statemetns for you.
    select 'drop sequence_name ' || sequence_name || ';'
    from user_sequences;

    5. imp userid=system/xxx@qa file=prod.dmp buffer=1048576 commit=Y fromuser=PRODApp touser=QAApp ignore=Y constraints=N

    6. Enable the foreign key constraints using the script created in step 3

    7. Done.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts