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 (?)

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 (?)

Most common mistakes:
1. Not reading the documentation
2. Not moving to LMT
3. Not moving to and understanding UNDO rather than RBS
4. Whining about the loss of svrgmrl
5. Not knowing which init parameters have been deprecated
6. Not understanding the spfile and how to alter it

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

netcomrade@karlson.dmitriev.net (NetComrade) wrote in message news:<slrnc3pr5b.t17.netcomrade@karlson.dmitriev.n et>...
> 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 (?)

I would say that FIRST thing to do is upgrade to 9.2.0.4. This will
make things much easier (it's my own experiece talking).

Good luck.

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

netcomrade@karlson.dmitriev.net (NetComrade) wrote in message news:<slrnc3pr5b.t17.netcomrade@karlson.dmitriev.n et>...
> 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 (?)

The commonest mistake I've seen made is one that has been discussed
here just recently: the default cache and the recycle cache and the
keep cache are now three separate entries whose sizes need to be added
up to give you your total buffer cache size, whereas before the keep
and recycle caches were merely subtracted away from a total cache size
set by db_block_buffers. If you literally translate block_buffers into
db_cache_size, buffer_pool_keep into db_keep_cache_size and
buffer_pool_recycle into db_recycle_cache_size, then you're in for a
shock because your memory consumption is going to go through the roof.

Regards
HJR

netcomrade@karlson.dmitriev.net (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)

Poorly, in my experience. Combining PGA_A_T with Shared Server seemed to
make the CBO hopelessly confused. It got completely addicted to hash,
at an expense of hundreds or thousands of fold slowdown. (Of course, we
never should have been using Shared Server in the first place, but it only
took me 18 months to win that battle.)


> * workarea_size_policy defaults to auto if pga_aggregate_target is set

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB