Migrate Oracle Data Warehouse 10g (DAC) repository to Oracle DAC 11g

We have been using DAC 10g in our ETL setup since few years now. It been serving all the purpose very well till now until few days back when suddenly we got a situation or demand where we want Oracle DAC 11g installed.

Going through the Oracle’s documentation, upgrading Oracle 10G DAC repository to 11g repository is a straight forward task and actually it is a straight forward task. Once you have the Oracle 11g DAC server running and Oracle DAC 11g client installed, you just need to point Oracle 11g DAC client to Oracle 10g DAC repository and it will upgrade it to 11g by default.

But what actually made me write this post if this is that simple.

Ok, so the actual reason is that, there is nothing mentioned for migrating a 10g repository to 11g DAC environment.

In our setup, we have multiple environments like DEV, TEST and Prod.

We upgrade our DEV environment to 11g and it went just smooth using Oracle documentation. Later on we got the request where we needed to Import Prod repository into the DEV (11g version) environment.

Searching Oracle’s Metalink and google, does not gave me any option where we can actually directly migrate 10g repository to 11g DAC setup.

So following are the steps that I formed or thought of to execute my plan.

1. Take a export backup or EXPDP backup of DAC reposiroty scheme from 10g version, which is in my case Export backup of DAC_REPO from BIPRD.                         exp dac_repo/password file=dac_repo_biprd.dmp statistics=none
2. 1. Create a new user to hold the copy of DAC_REPO. For my instance, I created this user in BIDEV.

   “Create user dac_repo_upg identified by password profile system default tablespace repo;

   grant dba to dac_repo_upg;”

3. 1. Import the export dump to new created dac_repo_upg

   imp dac_repo_upg/password fromuser=dac_repo touser=dac_repo_upg indexes=N file=dac_repo_biprd.dmp

4. 1. Now enable Upgrade Lockdown of the repository. This thing needs to be done from the DAC client. To avoid a new connection in DAC client, I did this from the backend database.

   update dac_repo_upg.W_ETL_SYSPROP set value=’true’ where name=’Repository Upgrade Lockdown’;

   commit;

5. Now, Open 11g DAC client. Create a new connection file pointing to new database schema “dac_repo_upg”.
6. Since, this is a 10g repository and client is 11g, when you login, it will ask for upgrading the repository. Click Yes and it will upgrade the 10g repository to 11g.
7. Once done,now you have source and target repository on same 11g version.
8. Now you have to follow standard procedure to merge 2 DAC repositories using “UPgrade Merge Wizard”.