Issues in upgrading from 9i to 10g

Last week I had a chance to upgrade a 9.2.0.7 database to 10.2.0.5. 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). 10.2.0.5 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 !

OTN forums – 2nd upgrade attempt

Well, as everybody has seen, last week they have again upgraded the OTN forums. And sadly it has been handled in a very poor manner. End result is that it is running terribly slow and throwing some errors many times. In my office, i cant even open it properly may be its too heavy or some issue with the internet. Also there has been lots of hues and cries about the new point system and all. That in my opinion is fine except that speed at which forums are running should be fine.

But Oracle itself handling an upgrade like this is really bad. It should have happened in a sweet manner. Or no money comes from OTN, so no experts out there ?

Since the upgrade, i am not feeling like posting and i think number of posts have also gone down.

Lets hope for some improvements…

Automating WordPress 2.5 upgrade

I recently upgraded my blog to WordPress 2.5. The manual process is real cumbersome. Today I came across few plugins which help in almost automating the upgrade stuff.

WordPress Automatic upgrade: As per description this plugin first takes backup and then upgrades. I didn’t try this one, though.

Instant Upgrade: I upgraded one of my blog using this. It doesn’t take care of backup but otherwise the upgrade was super smooth. Just few clicks and you are done.

Happy upgrading !