As part of upgrading all our production databases from 8i & 9i to 10g, last Friday, early morning, we had successfully upgraded our core banking business critical database from 9i to 10gR2.
Prior to this upgrade, we had done the upgrade with other business critical databases, but, this database is very specially, critical, thus our management wanted a special concern while upgrading this database and looked-for an immediate fallback option.
We had one week of time to plan everything and simulate the upgrade on development server to measure the exact downtime and fall back procedure and application testing. Our several attempted simulations took maximum 1 hr of time and we have requested for 1 hr downtime and fortunately we have got 1 hr down time for the upgrade.
Following methods have came to our mind:
Transportable Tablespaces, Rolling Upgrade.
Streams : Since we require the fallback option, we tested using streams where we need to install 10g software on a different machine, create database, export and import and configure Streams to replicate changes from 9i to 10g and vice versa. On a granted cut off time, divert all connection from 9i to 10g and still keep the stream replication between 9i and 10g as the fallback option.
We didn’t like this method, because it involved export and import and we don’t want to use export and import facility for such an important upgrade.
And we finally zeroed the Standby method idea. In this method, what we did was, first create a standby database on the same server where primary up and running, keep primary and standby in sync, disconnect all session from the primary database, stop primary database, switchover standby database to primary database. Once the standby became primary, fulfilling all perquisites, like creating SYSAUX tablespace and other stuff, we have started the manual upgrade. The ‘catupgrade’ took 12 minutes for 64 gb database size.
We were very much glad as the upgrade activity finished in 50 minutes and we still had 10 minutes to do all our pre-checks.
In case we couldn’t finish the upgrade in the given time, we simple stopped the standby which turn primary, and started the old primary database as fall back option.
After an upgrade, there were couple of issues which took less time to resolve.