Thursday 23 August 2007

Upgrade an Oracle 10g database to Oracle 11g using dbua

Oracle suggests several ways for upgrading a database from a previous version into 11g R1.

  • Database Upgrade Assistant (DBUA)
  • Manual upgrade using SQL scripts and utilitites
  • Export and Import utilities
  • CREATE TABES AS... SQL statement.

In this post I am trying to upgrade my Oracle installation and a database on my Ubuntu 7.10 Desktop from Oracle (10.2.0.1) to Oracle 11g (11.1.0.6.0) using DBUA. During this very messy job, I hit couple of problems, looked around in blogs and here is how I got it all sorted.

1. Install New Oracle Software in a seperate directory in a new home

I have downloaded the Oracle 11g (11.1.0.6.0) version from Oracle here , then I kept my old $ORACLE_BASE directory as: /usr/local/oracle and created a new $ORACLE_HOME on my Desktop like this /usr/local/oracle/product/11.1.0.6.0/. I installed the Oracle 11g version in there using OUI (Oracle Universal Installer). Hit lots of errors, as I am using an uncertified OS version (Ubuntu 7.10). I implemented different workarounds, most of them just ignoring all the errors :-) Still installs!

Blogs which I found very useful while doing this were:


    http://www.pythian.com/blogs/549/installing-oracle-11g-on-ubuntu-linux-704


    http://www.dizwell.com/prod/node/1046

    http://advait.wordpress.com/2007/09/07/upgrading-to-oracle-database-11g/



Although the bloggers above indicate gentle ways of dealing with installing Oracle 11g on uncertified OS (like Ubuntu), I followed the crude way and "Ignored All" warning messages until I have completed the installation of Oracle 11g softwarre. Next in step 2 I talk about upgrading my existing Oracel 10g database using dbua (Database Upgrade Assistant).



2. Upgrading existing databases to the New Oracle 11g (11.1.0.6.0) version using dbua



Now is time to do the upgrade. Setting all ENV parameters ORACLE_HOME and ORACLE_BASE correctly to point to the new Oracle 11g software (don't forget oratab), I fired up dbua Database Upgrade Assistant and started upgrading my database.

ADVAIT’S ORACLE BLOG http://advait.wordpress.com/2007/09/07/upgrading-to-oracle-database-11g/
explains the command line method for upgrading databases from Oracle 10g 10.2.x.x version to Oracle 11g 11.1.0.6.0. Very good. Well done Advait!

It solved all my critical upgrade issues which I have encountered using dbua. Especially the timezone 4 bug identified as 396387.1 in metalink is very well explained in Advait's blog. This one hits versions 10.2.x.x, most of them.

I have ignored lots of INVALID object warnings from dbua and went ahead with the upgrade. Had a bumpy ride with Enterprise Manager Configuration not succeeding (says failed), skipped that as well as I could do it later. I have managed to upgrade and got the Oracle 11g prompt:


SQL*Plus: Release 11.1.0.6.0 - Production on Thu Dec 6 22:40:21 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select name from v$database;
NAME
---------
TESTDB01