6 Replies Latest reply: Jan 30, 2013 11:46 AM by Pavan Kumar RSS

    Refreshing

    984741
      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
          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
            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
              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)
                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
                  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
                    Pavan Kumar
                    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