Re: Common mistakes when upgrading to 9i

NetComrade wrote:

> Hi,
>
> We are finally on a path to upgrade from 8i (8.1.7.4) to 9i (9.2.0.1).
> I wanted to learn from your experiences on things that can go wrong.
> I am going to list a few things I've read up on, and if you're kind enough,
> you'll let me know if I missed anything important
>
> * sga_max_size should be examied
> * pga_aggregate_target default is 25MB, if set DB ignores
> sort_area_size
> hash_area_size
> bitmap_merge_area_size
>
> How do above two affect MTS (Shared Server)
>
> * workarea_size_policy defaults to auto if pga_aggregate_target is set
> * db_cache_size replaces db_block_buffers and is in bytes
> * statistics_level defaults to typical
> * automatic undo management should be researched (but not req'd)
> * db_block_checksum is true by default in 9.2 (?)

Everything Daniel and Carlos said.

Even if you can migrate, there is a certain "cleanness" to creating a new database and export/import your data (if you can afford that long of an outage). Be sure to use the old version of export and the new version of import.

When creating your database, use FORCE LOGGING, set the sys and system passwords at create time, set your default temporary tablespace once for all users. Might as well create your SYSTEM tablespace using LMT, even though it's not required (yet).

Finally, TEST TEST TEST. We ran into a number of glitches:

1. errors (PL/SQL) that were always silent errors under 8i were suddenly being reported as such under 9i.
2. DATE datatypes apparently changed quite a bit under the covers, probably due to new timezone support. If you have "invalid" date values (outside the supported range) that were never a problem under 8i, these can cause bizarre and inconsistent query results under 9i. Search metalink note 91207.1 if you have access for more information. This typically happens when third-party apps such as Java insert date values. Use an expression like substr(dump(date_column),1,35) to help identify these values.
3. some optimizer defaults, such as the _b_tree_bitmap_plans parameter have changed from 8i to 9i and can wreak havoc with previously tuned queries.
4. beware the affects of SET SQLPLUSCOMPAT 9.0.0 in your SQL*Plus scripts.
5. if you are using JDBC clients, be sure to upgrade them. In fact any Oracle client older than 8.1.7 is not supported for connectivity to 9i databases.

This is all I can remember for now, wish I'd documented every little thing.

--Mark Bole Received on Thu Feb 26 2004 - 10:31:42 CST