Oracle Warehouse Builder 10g currently does not support Data Guard that makes it easy to switch databases or migrate to another server and database. I ran into this similar limitation and had originally opted to redeploying all the mapping from the MDL export. This process what taking me between 4 to 6 hours because the Control Center GUI is extremely (not to mention painfully) slow. Finaly we worked with Oracle support to find a better solution to this problem. After some testing and several successful switched and migrations I have formulated the steps below.

Goal
Move SID and OWB installation and mappings to another server.

Pre-migration Steps

  1. Prepare 2 servers, app server and database server to have identical installation versions of database and OWB to the original systems.
  2. Have the DBA take a baseline of current setup using the owbcollect.sql that Oracle provides with OWB. This helps to compare after the installation of the new system is done. We do this step now because usually we end up putting it on standby mode before proceeding with the migration.
  3. Clone the SID and data and restore on new server. This process is beyond the scope here – usually the DBA takes care of this. I remember vaguely of the steps involved from my Oracle DBA training but I never had the opportunity to put it to practice.
  4. After the SID has been migrated and the database is running – start up OWB on the new server using the new SID information. OWB_HOME/owb/bin/unix/local_service_login.sh -startup /path/to/OWB  You can check to see if this is running by using ps -ef | grep OWB. You’ll receive a readout of the SID info at the end of the output.

Update the location credentials stored in the Control Center:

  1. Start the OWB Browser Listener. For Linux : OWB_home/owb/bin/unix/startOwbbInst.sh
  2. Start the Repository Browser. https://HOSTNAME:8999/owbb/RABLogin.uix?mode=design
  3. Connect to Warehouse Builder Browser as the Control Center Owner.
  4. Select “Locations Report” under Reports – Deployments
  5. Select the Location you would like to update
  6. Click on the “Unknown” link in the Validation column. (last column)
  7. In the “Connection Details” section of the Location Validation Report page, update the Host, Port and Service Name accordingly. Update to SID details to the new database server.
  8. Click on the “Update Details” button.
  9. Click on the “Test Location” button to check the location.
  10. Repeat steps above for each location that requires changes.
  11. Finally, Update the Control Center location “PlatformSchema” the same way as explained in steps above.

Update the Location Credentials stored in the Design Repository (DBA task)

  1. Using SQLPlus, connect as the warehouse builder repository owner.
  2. Execute the script UpdateLocation.sql (you can get script at Meta-link)
  3. SQL>@UpdateLocation.sql LOCATION_NAME HOST PORT SERVICE_NAME
  4. Do the above for each location that your OWB utilizes and provide the new server HOST, PORT and SERVICE_NAME(SID).

Note: LOCATION_NAME must be upper case or you will get a not found error.

Update the Control Centers Credentials stored in the Design Repository (DBA task)

  1. Using SQLPlus, connect as the warehouse builder repository owner.
  2. Execute the script UpdateControlCenter.sql attached to this document for each Control Center
  3. SQL>@UpdateControlCenter.sql CONTROL_CENTER_NAME HOST PORT SERVICE_NAME
  4. Do the above for each location that your OWB utilizes and provide the new server HOST, PORT and SERVICE_NAME(SID).
Note: LOCATION_NAME must be upper case or you will get a not found error.
Also: we were originally confused regarding the CONTROL_CENTER_NAME. This is almost always “DEFAULT_CONTROL_CENTER”.
Test the Migration
Log into the Design Center and then the control center to test a deploy or two to make sure all is working. Try one from staging and one from data warehouse. If successful deploy – then you should be good.
Finally run the baseline script on the new system using owbcollect.sql and compare with the original one you took prior to starting this process.Thats it – you’re done. You actually save about 5 hours with this process.

Troubleshooting
 The only problem I’ve seen is when DBA was trying to update the locations credentials is that we we using the wrong location name. Or location that was being used in another environment but not with the system we were presently working with. Especially with confusion of lower environments having more locations for testing than production. Other than that I have not seen any other issues.

« »