Upgrading an OWB Project to 11gR2
Mark has been out front and center in demonstrating the new features of OWB 11gR2, both in the OWB Unconference and in numerous blog postings over the last few months. But one thing we haven't covered yet on the blog is what the upgrade of a current OWB project looks like. I will be demonstrating how to upgrade an 11.1.0.6 OWB project to 11gR2, but I will also point out some of the different steps that should be taken if your version of OWB is 10g. The database I'm going to be upgrading is from the VM I put together to do this blog posting... it consists of two mappings and a single process flow.
As OWB installs as part of the database now, the upgrade to 11gR2 begins with an update to the database, and regardless of the intial platform, most of the database upgrade looks the same. I start by running the database installer, and when prompted for configurations preferences, I select "Upgrade an existing database".
After the software install is complete, the Database Upgrade Assistant (DBUA) launches automatically to handle upgrading existing databases. The Assistant handles the basic tasks of starting the database with STARTUP UPGRADE, and then executing the appropriate catalog scripts to manage the modifications to the data dictionary, as well as other stored objects owned by the various default Oracle schemas.
During the database upgrade, I can see all the different components of the Oracle Server being upgraded, including the Java server, the XML server, the OLAP catalog, etc.
The only interesting thing about my database upgrade is that the process changed the default port for the listener from 1521 to 1522. When I create demonstration database VM's on my Mac, I typically use dynamic listener registration, which eliminates the need for a listener.ora file. Instead, the database uses the information in the pfile or spfile to register services to listen for, but I'm not really sure why the Upgrade Assistant thought I needed to change the port number.
This change is problematic for OWB installations, as I already know that I will have to modify all the database and process flow locations to use this new port number. It's a huge inconvenience that I could eliminate by moving away from dynamic listener registration and creating a listener.ora file. However, it seems like a decent thing to work through in the name of science, so I leave it and move on.
After the database upgrade is complete, I now have the database mounted with the new ORACLE_HOME, therefore the 11gR2 binaries and database objects are available for this database, and if I were to create a new OWB project, then that project would use the 11gR2 version of OWB. But pre-existing OWB projects don't get upgraded automatically, and there's quite bit of massaging required to make this happen. The process of upgrading the OWB repository is one instance where the upgrade steps will differ depending on which database version is being upgraded. Because 11gR1 already has the concept of the "workspace" built in, the schema OWBSYS already exists, so the main modification to the repository is the addition of the OWBSYS_AUDIT schema. To make the required change to the repository, the $ORACLE_HOME/owb/UnifiedRepos/cat_owb_reposupgrade.sql script is executed as SYSDBA.
SQL> @cat_owb_reposupgrade Role altered. Grant succeeded. Grant succeeded. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Commit complete.
If the upgrade is performed against a 10gR2 database, the OWBSYS schema doesn't exist yet, and needs to be created. In this case, the $ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql should be executed instead.
The next step is to run the OWB Repository Assistant to complete the migration of the repository. I'm not exactly sure why the Repository Assistant couldn't have included the previous step of running either the cat_owb_reposupgrade.sql or cat_owb.sql scripts. It's not a major effort to do that step manually, but simplifying the process wherever possible is a definite benefit.
On Linux, the Repository Assistant is executed using the $ORACLE_HOME/owb/bin/unix/reposinst.sh script. One of the first questions I get asked is what sort of operation I would like to complete, and I select "Upgrade repository to current release of Oracle Warehouse Builder".
I am then asked what sort of upgrade option I would like to perform. I choose "Upgrade repository on the same database instance", and then the upgrade process takes off.
The other options include "Export entire repository to a file" or "Import entire repository from a file". According to the documentation, we would have to perform these two steps manually if our repository was 10g, first exporting the entire contents to a file, and then importing them. I don't know what aspect of the 10g upgrade limits the "upgrade in place" option. Conceptually, the lack of workspace functionality is quite a significant change, but I'm not certain it's significant enough that it can't be handled by a single migration option in the Repository Assistant.
Anyway.. back to 11gR1. While the Repository Upgrade is underway, we see several different informational messages concerning the status of the upgrade. Finally, I get a message that states: "Upgrading repository completed successfully". Not the best grammar... but it'll do.
Once the migration is complete, I launch the OWB Client to have a look around and notice that the mappings and process flows are all deployed, and the deployment and execution history is all intact. However, I know I have to modify the port number for all the locations (because it was changed to 1522 during the database upgrade), and also update the version from 11.1 to 11.2 for all the database locations. There are two options for completing this, and because I'm using a VM, I was able to test both options by first trying one, rolling back, and then trying the other. Both worked successfully for me, so pick your poison.
First, I unregister all the locations, make the required modifications, and then reregister them. Then I redeploy all the dependent objects (mappings, process flows), and this worked fine. However, I only had two mappings and a single process flow. If this were an actual OWB project for a data warehouse, there would be lots of mappings and process flows, so you would have to decide if you think this is worth it.
The second choice is to use the Repository Browser to upgrade the locations, and the instructions are in the documentation. I start the Repository Browser listener by executing $ORACLE_HOME/owb/bin/unix/startOwbbInst.sh, and then point a browser to https://localhost:8999/owbb/RABLogin.uix?mode=design. After logging into the Repository Browser, I choose Location Reports. I can start with any of the database or process flow locations by clicking the "Unknown" hyperlink in the the Validation column.
I set the port number to 1522, and then click Update Details. Then I click Get Status, and click Upgrade.
This completes the upgrade of the locations. Now it's time to execute the process flow, which in turn executes the two mappings.
So at this point, I have my OWB project migrated and working, but I'm not taking advantage of any of the new features that Mark has been blogging about. I'll tackle that with my next post... how to modify mappings to make better use of the Code Template functionality.