2 Replies Latest reply on Jun 14, 2013 6:26 PM by kirkladb

    Database Upgrade Issue


      Hello All, I have a bit of a problem. We have two(2) databases one OLTP and one Datawarehouse. We are upgrading the OLTP and DW database this coming weekend. The OLTP db is going from SE to SE and the DW db is going from EE to SE (via export/import). My problem is this, the OLTP database will take about 3 hours to upgrade and the DW db will take about 14 hours to import (due to the fact that there is no import parallelism in SE). We have trigger based events on the OLTP side so when something is inserted it flows to the DW database. Since there is an 11 hour difference in the times in which each is available over the weekend, can anyone come up with a way I can bring up my OLTP after 4 hours (which is the true money maker for the organization) and somehow maintain the triggered data so that it can be inserted in the DW db once it's available? We don't want to take a full 14 hour outage just waiting for the datawarehouse....any suggestions? Thanks everyone

        • 1. Re: Database Upgrade Issue

          I have had to deal with a similar situaton. Two suggestions:

          Parallelize the import. I know you can't use Data Pump parallelism, but you can run multiple Data Pump sessions. Perhaps one session per schema, or (if you can work out the dependencies) multiple sessions within a schema.

          Tune the import! There is a huge amount you can do. I have found the biggest benefits come from setting constraints to enable-novalidate before the export; make all the indexes nologging; set pga_aggregate_target, _smm_max_size, and _pga_max_size to several G. 

          Hope this helps.


          John Watson

          Oracle Certified Master DBA


          1 person found this helpful
          • 2. Re: Database Upgrade Issue

            Thanks John, I will try that....