This discussion is archived
6 Replies Latest reply: Jan 30, 2013 9:46 AM by PavanKumar RSS

Refreshing

984741 Newbie
Currently Being Moderated
1) I want to refresh my test environment from my production database. What are my options? Production DB size is 400GB. OS:RHEL5 & Oracle version 10.2.0.4

2) If i make block change tacking is enabled on production db will it be enabled on cloned db automatically?
  • 1. Re: Refreshing
    sb92075 Guru
    Currently Being Moderated
    981738 wrote:
    1) I want to refresh my test environment from my production database. What are my options? Production DB size is 400GB. OS:RHEL5 & Oracle version 10.2.0.4
    RMAN> DUPLICATE DATABASE;
    2) If i make block change tacking is enabled on production db will it be enabled on cloned db automatically?
    a clone is a clone
  • 2. Re: Refreshing
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    1. Use export/import
    2. Use RMAN - backup & restore, duplicate, probably other ways too
    3. Use SAN technology to clone at the disk level
    4. Use Data Guard so you have a constantly refreshing DB, then use one of hte above methods on it so laod is not added to the PROD DB and you can start/stop it when you like
    5. Be selective in the data that you export and use export/import so you have a slim version of your PROD DB.

    Quite a few options. Depends on:

    1. How often you want to/have to do it
    2. Your business requirements
    3. Time window to do it
    4. Available resources

    Hope that helps.
    Rob

    P.S. When i say export/import I mean DataPump.

    Edited by: Rob_J on Jan 30, 2013 4:19 PM
    - added P.S
  • 3. Re: Refreshing
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    also
    Tips on Cloning a Demantra Schema/Database (ex. refreshing the DEV environment with PRD data) [ID 856857.1]
  • 4. Re: Refreshing
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    1) I want to refresh my test environment from my production database. What are my options? Production DB size is 400GB. OS:RHEL5 & Oracle version 10.2.0.4
    It depends how different your current test DB from the current prod DB.

    1. If structures are same, and data differs insignificantly - you may refresh test DB by finding different rows in each table and applying corrective DMLs.
    It is a way that requires most programming efforts but has least impact on both DBs.
    For example this SQL will insert rows from Prod to Test DB if these are not in test BD.

    insert into tableA
    select * from tableA@PROD p
    left join tableA t on t.PK=p.PK
    where t.PK is null;

    You can craft MERGE SQL to cover also updates and deletes.

    2. If structures are not same, or data differs significantly - you can use DataPump over db link,
    old exp/imp,
    RMAN duplicate or backup/restore.
  • 5. Re: Refreshing
    jgarry Guru
    Currently Being Moderated
    It depends what you want to test. If you have people wanting their own little part of the database for themselves, Rob's number 5 is the way to go. If you are doing development, that may also make sense for particular developers. If you are doing user acceptance testing for new development or QA things, you probably want to have some way of making repeatable changes after cloning. If you have a test database with a mix of all these, you probably don't want to clone, but use a mixture of things as appropriate, including perhaps using transportable tablespaces to move subsets of data. You also have to watch SLA's for backing up these tests, because that's someone's production too.
  • 6. Re: Refreshing
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    If I re-collect correctly, you might face issue,while performing rman clone with backup base duplication (with change tracking file with active). I can't recollect, but faced some issue with bct file.

    - Pavan Kumar N

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points