Oracle 10g upgrade.

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.


Mohammed Moinuddin Umair said...


Were there any performance issues after you upgrade to 10g.Did you add more memory to the server after upgrade.

Any specific parameter you change after upgrade.

How it is performing now.


The Human Fly said...

So for now performance issues have been noticed nor received any complaints.
However, I ran ADDM and found couple of sql and pl/sql which can be tuned to gain more performance.
Overall, going great.


Anonymous said...


I am assuming the upgrade was using logical standby?

Anonymous said...

Was the upgrade performed using a logical standby?

Anonymous said...

Was the upgrade performed using logical standby?

Anonymous said...

How did you sync standby to primary after upgrade ?

Anonymous said...

Hi, I am curious to know what environment you had. Did you migrate from single instance to RAC? How much time do you think it might take for 350+ G data with ASM?

Anonymous said...

It has been my experience that the number of database objects is more important than the overall size of the database when calculating the amount of time required for oracle upgrade. The number of database objects will be the driving factor in the size of the dictionary.

SM said...

Jaffr , I came across your blog and was great to have gone thru your experiences . I am a software tester with fair amount of know ledge of SQl . I plan to take the exam IZ0- 042 . what is it that I shoudl focus . I dont have an oracle background but have fairly worked in the environment as tech writer .. tester . Pl s give me sume suggestions
Santosh Mishra
Indianapolis USA

Unknown said...

I have taken hotbackup of my production database, and i have copied the files to standby server.
Now my point is that when doing so, is it necessary to create standby control file immediately or can be done later and copied to standby server