Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 17 September 2012

Upgrading Step by Step Oracle 9i to 10g with DBUA

It is easier to upgrade your existing Oracle9i Database using DBUA. In the past, there were several tedious, manual processes that had to be outlined and documented, tested and proven, prior to upgrading. With Oracle Database 10g, the upgrade process is available through the Oracle 10g Enterprise Manager. All tasks, processes, and post-upgrade tasks are available after your installation of the Oracle Database 10g binaries. Direct upgrade from, or higher, or higher, and or higher to the newest Oracle Database 10g release is supported. Here in below example Oracle 9i Database will be upgraded to Oracle Database 10g Release 2. The same process is available for Oracle Database 11g Release 1.
  1. Install oracle 10.2 (software only) with its entire companion oracle 10g product (if you have Jserver, InterMedia, HTTPserver, Spatial, Ultra search in your existing database).
  2. Prior to upgrade check (if there is a patch set release and critical patch update for oracle10g release 2) required patch should be installed.
  3. Analyze your database before upgrading to new release by running pre-upgrade tool (A SQL script that comes with new oracle 10g release).
        Login to the system as the owner of the ORACLE_HOME directory of oracle 10g.
        Copy the utlu102i.sql from the location ORACLE_HOME\rdbms\admin to any temporary location.
        Start SQL*Plus and connect to the database as a user with SYSDBA privileges.
        Run the script utlu102i.sql by setting the spool to a log file for later analysis.
        Analysis the script output and makes recommended changes and restart the database. Run the utlu102i.sql script again and check if any recommendations. Once ensure there is no recommended checklist then proceed to the next step.
SQL> spool ora102.log
SQL> spool off
  1. Make a list of invalid objects
Select object_name, owner from dba_objects where status='INVALID';
  1. Run from ORACLE_HOME/bin/netca to configure a Oracle 10g listener
Consider at this point you have already done the oracle 10g installation and reset the environment variable for oracle 10g release 2. Please check my other related post for analyze script out put and many more details. Below are the step by step details for setting oracle 10g listener.
Select “listener Configuration” and click on NEXT
Select “Add” and click NEXT
Select “Listener Name” and Click Next
Select “TCP” and click Next
Select the “Listener Port number” and click Next
Check “NO” and click Next
At this point consider your listener configuration is successful and you are already analyzed all the required parameter for Upgration by using the utlu102i.sql and modified the pfile as per the requirement.
After ensuring all the parameter setting is done as per the upgraded database guide move for the next step to start the DBUA.
This can be started using the dbua command in Linux environments or from the Start menu (Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Database Upgrade Assistant) in Windows environments. Check my other post of: Different way and their comprising benefit of Migration/Upgration in oracle database
Welcome screen appear click on “Next”
DBCA automatic detect the presence of 9i database select the database you want to upgrade and press Next.
Click Yes to Move forward
Keep check on “Do not move database files as part of upgrade” click Next.
Provide location of SYSAUX tablespace (The location where all other your oracle 10g tablespace reside, Keep AUTOEXTEND check mark active) and click Next
Check mark on “Recompile invalid object at the end of upgrade” and click Next.
You have already taken “Cold backup” before start the upgrade process so here ignore the backup by checking on “I have already backed up my database” and proceed to Next.
Specify to “configure the database with Enterprises Manager” option.
Specify to configure the flash recovery area option (if you want) otherwise you can later do this.
Provide the password for DBSNMP (use the same password of all account).
Review the database upgrade summary
Now the DBUA will start the upgrade progress by showing a progress bar “wait until to complete”.
At last the DBUA will successfully complete Upgration by showing the upgrade results.
After successful Upgration results gather the statistics of database and compare it and also verify the performance of database. Some other post upgration task you have to perform. Check my other post for details of post Upgration work.


  1. BlueHost is ultimately the best hosting company for any hosting plans you might need.


    Professional trading signals sent to your cell phone daily.

    Start following our signals right now and gain up to 270% per day.