10g Upgrade issues

This weekend we are upgrading our E-Business Suite database from 9i to 10g (10.2.0.2 to be specific). Today while performing some final checks I noticed that an initialization parameter was not set properly in our test environment.

I had used the script bde_chk_cbo.sql from Metalink note: 174605.1 which builds a report consisting of the current, required and default values of your initialization parameters. The parameter in question was optimizer_secure_view_merging and according to the report it was undefined. Which means its using the default value of TRUE. According to Note 216205.1, Database Initialization Parameters for Oracle Applications 11i this parameter should be set to FALSE.

I checked my logs of the test upgrade and I did indeed set this parameter to FALSE. So I performed a search on metalink and found Note 404646.1, How To Set Parameter optimizer_secure_view_merging = FALSE Using Autoconfig. In summary, autoconfig isn't updating your pararameter file properly unless your on version 115.7 of the template file $ORACLE_HOME/appsutil/template/afinit_db102.ora. We have version 115.3 installed. This parameter is needed to avoid some performance issues with dictionary queries. If this applies to your environment, you should review the metalink note for the workaround.

Over the past month or so we have upgraded many environments to 10g, reverted some back to 9i, etc. It started to get confusing as to which environment was running 10g and which were still on 9i. (We have about 14 dev/test/project environments right now, with more planned.) One of my coworkers started up a 9i database with the 10g Oracle Home and we noticed something interesting:

ALERT: Compatibility of the database is changed from pre-10i to 10.2.0.0.0.
Increased the record size of controlfile section 2 to 256 bytes
The number of logical blocks in section 2 remains the same
Increased the record size of controlfile section 4 to 428 bytes
Control file expanded from 1614 blocks to 1644 blocks
Increased the record size of controlfile section 9 to 56 bytes
Control file expanded from 1644 blocks to 1672 blocks
Increased the record size of controlfile section 10 to 200 bytes
Control file expanded from 1672 blocks to 1694 blocks
kcv_lh_or_upgrade: 10.2 upgrading 1 incarnations
Control file expanded from 1694 to 1990 blocks for upgrade.
Setting recovery target incarnation to 1
Wed Jun 20 00:11:34 2007
Successful mount of redo thread 1, with mount id 764203184
Wed Jun 20 00:11:34 2007
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Jun 20 00:11:34 2007
ALTER DATABASE OPEN


As you can probably guess by the messages above, the control file was modified. When the database was shutdown and started with the 9i software the following happened:


ORA-00201: controlfile version 9.2.0.0.0 incompatible with ORACLE version
9.2.0.0.0


Yikes! On Monday I'm going to see if I can recreate the control files and startup the database but right now i'm off to upgrade our production EBS database to 10g.