Using Delphix to support Oracle upgrade

I’m working on upgrading a very buggy unpatched 11.1.0.7 Oracle database to a fully patched 11.2.0.4 version. I’m using Delphix to support the upgrade and it has been a big help so far. This is on the HP-UX 11.31 Itanium platform.

The great thing about using Delphix to support an upgrade is that my very first pass through the upgrade scripts was with a full-sized clone of production. In the past I probably started with a tiny subset or even an out of the box demo database for my first upgrade pass and even when I got to QA it wasn’t a full test of a production upgrade. This time, my first test was with all the data and that was very cool.

The main example of how this helped is that we had a lot of data in the SYS.WRI$_OPTSTAT_HISTGRM_HISTORY and SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY tables in production and this made the first upgrade of its clone take a long time. After two or three attempts at other ways to speed things up, I ended up applying patch 12683802 on an 11.1.0.7 Oracle home and this allowed me to truncate these two tables.

Delphix helped me here because I had an unused 11.1.0.7 Oracle home on a different host from the one I was doing the upgrade on. I didn’t want to apply a patch on the upgrade host because there were three other databases using the home and I didn’t want to bring them down or patch them. Delphix let me move the VDB that I was upgrading over to the host that had the unused home. Then I applied the patch there and ran the truncate using the dbms_stats.purge_stats(dbms_stats.purge_all) procedure call that the patch enabled.

Then I moved the VDB back to the host where I intended to do the upgrade, which already had the fully patched 11.2.0.4 binaries installed, and ran the upgrade there. Pretty cool. I think I did the most recent upgrade in about 3.5 hours with the empty OPTSTAT tables.

By the way, doing an upgrade within Delphix is easy. You just bring the VDB up on the old oracle home, do the upgrade as you normally would include all the shutdown and startup commands, and then within the Delphix GUI you let Delphix know the VDB is now on a new Oracle home by shutting it down, choosing the new home, and bringing it back up. Piece of cake.

Bobby