Journey from 9.0.1 to 9.2.0.8: Part-I

I would like to share my experience on how we migrated the production instance from Oracle 9iR1 (9.0.1) to Oracle 9iR2 (9.2.0.8) in August 2008.

To begin with, let me provide a background on what was the situation before I joined this company.

Our company's ERP is based on Oracle Forms/Reports 6i and Oracle Database 9i . Ever since the ERP was implemented, i.e. more than 5-6 years back, the database was never looked upon as there was no DBA in the company and the Vendors gave the thought "if it ain't broke, don''t fix it". The production instance was configured on a Pentium III Dual Core (1GHZ, 1.5GB RAM, 40GB HDD with RAID 5 Configuration) having Windows 2000 Server.

The developers were supporting the ERP as well as new modules developed were being added. Over a period of time the database grew, number of users increased and the database started suffering due to performance degradation and unexpected crashes in the middle of the business hours.

In the event of a crash, the only thing what the Developers and IT Admins did was restarted the Server. IT Admins delegated the responsibility to the developers as it was out of their purview. And the developers, having no or limited Database Administration experience, had no clue why the crashes were happening in the first place.

Initially, the Vendors analyzed the incidents and instructed the developers to increase the size of undo tablespace (200M --> 500M), to add a datafile on any tablespace that exceeds 70% occupied space, to increase the SGA (600M --> 900M) and resize the Buffer Cache & Shared Pool, to reduce open cursors (2500 --> 500), to increase Redo Log Groups (3 --> 6), and to delete the statistics on few tables that had them. The database was supposed to run with Rule Based Optimizer (RBO) mode, so the Vendors advised not to gather statistics on any of the tables.

There were lots of issues seen related to blocking locks. For which, the developers had to either ask the blocking session's user to save the work and exit or kill the blocking sessions using Toad.

Users, time and again, complained about slow performance and session hangs. Some of their queries, mostly specific to Reports, which had a possibility of being addressed were put through query rewrite using hints or tuning, the effect of which was mostly unpredictable due to Rule Based optimization.

Well, needless to say, the developers had to find ways to resolve the incidents/issues for which they had limited knowledge/experience. Keeping in mind the downtime implications, they had quite some tough times. Fortunately, apart from crash incidents, there were no Disaster Recovery Situations, and the business continued at the same pace.

In the next part, I will elaborate on my perception & resolutions applied on the above problems that the company was facing, when I joined the company.