Issues in upgrading from 9i to 10g

Last week I had a chance to upgrade a database to The size of the database was around 800 GB. The major applications connecting to the database were developed in Pro*C and Oracle Forms. The upgrade itself pretty smooth but there were few glitches around that needed to be handled. Just thought about documenting all the issues:

  • Few users in the database were assigned the CREATE SESSION privilege through a password protected role (That role was the default role for that user). onwards, password protected roles can’t be set as default roles. The alternate is to either disable the password for the role or assign CREATE SESSION directly to the user, not through a role.
  • After the upgrade, few procedures became invalid and while compiling started giving ORA-00918: COLUMN AMBIGUOUSLY DEFINED. The issue was bug 2846640 which is fixed in 10.2. Actually, in few of the queries using ANSI syntax, the developer didn’t qualify the column names with table names. It worked fine in 9i but due to the bug getting fixed in 10g, it started giving ORA-00918. The simple solution is to prefix the column name with the table name.
  • Few of the application schema owner users complained that they were not able to modify the procedures/packages in their own schemas. The schemas were not assigned CREATE PROCEDURE privilege but as per documentation, they should be able to modify the existing procedures/packages owned by them. This again is a documentation bug. It worked fine in 9i but in 10g onwards you need to have either a CREATE PROCEDURE or ALTER ANY PROCEDURE privilege (a risky one) to be able to edit the PL/SQL units in your own schema.

These were few of the issues encountered, rest of the upgrade was super smooth !

Happy upgrading !

Upgrade 10gR1 to 10gR2 – DBUA error

Today I was upgrading 10gR1 to 10gR2 ( on Linux x86. The upgrade went almost fine (except that I had to install one package and change few kernel parameters) but while running DBUA to upgrade databases, it gave an error:

Could not get database version from the Oracle Server component. The CEP file rdbmsup.sql does not provide the version directive


Start of root element expected. Upgrade Configuration file
'C:\Oracle10g2\cfgtoollogs\dbua\test\upgrade5\upgrade.xml' is not a valid XML file.

I searched in the metalink and found that this all happens due to customized glogin.sql file which was there in my case also. And removing that customization made DBUA rock 🙂

You might want to check here, here and here.