Upgrading to 10g - OK, I'm a bit behind

At long last, my final data warehouse customer on Oracle 9 is moving on to 10g. It has been a somewhat protracted move as for reasons of vendor support certification we had to adopt a specific order of refreshing their whole technology stack. That's not to say we have no Oracle 8i customers out there in pool of things I have a responsibility for: I have a customer frozen for ever on version 8, a legacy of the days when I thought DYNIX/ptx was the only way to run a performing database, but I digress.

In early summer we upgraded Oracle Warehouse Builder from version 9.2 to 10.2. This was a relatively simple upgrade that only affected the data warehouse server. Since then the very clunky reporting system using the most wrong-headed form of database clustering imaginable for the metadata repository has been moved from an active-passive Wintel server pair to a multi-processor SUN box and upgraded to the latest release of the reporting application. The old clustering had the unique property of being able crash both nodes at will when users were connected to the system (and sometimes when no users were present at all) but was impossible to shutdown with out the other node restarting. Well with a couple months of unclustered Solaris stability, we have come to the time finish the whole of their upgrade cycle (however I think that OWB 10.2.0.3 for Solaris must be out soon as the Linux and Windows versions have already hit Metalink, so there could be a bit more for us to do).

One of the junior DBAs from the DBA team did the data warehouse database upgrade for me; I think it was his first one, so he insisted that he did everything 'by hand' so that he could learn more than that he would have seen from running the upgrade assistant. I think I should have warned him about not being able to compile some of the PL/SQL packages before he spent an hour trying to work out why some objects were missing; These packages are used in partition exchange data loading and the table to exchange is created at runtime in the correct tablespace and dropped after the exchange is completed; still, if you have not seen it before it could surprise you.

So far our 1 terabyte, half-size (less history) test data warehouse has been running under 10g for a couple of days, we sorted out the database links (which we knew we have too) and shadowed the production data load batch to make sure that nothing fails in the middle of the night.

Getting OWB to deploy modules was a bit quirky for my developer - but by editing the target location he could ensure that the database password was correct and that the database target type was correctly showing 10.2.

We then got down to user query testing, which is another post