Archive for the ‘Upgrading Database’ Category

Manual upgrade from 9i to 10g Release 2

This procedure describes the steps necessary to manually upgrade a database from 9.2.x to 10.2.x. It assumes that you have already installed 10g onto the server. Of course, it is essential that you take a backup of your database before attempting any upgrade.

1. Compile any invalid objects


2. Create a sysaux tablespace

Having a sysaux tablespace is a requirement in 10g. So, if you haven’t already got one, create one now.

create tablespace sysaux

datafile ‘/apps/oracledb/oradata/PRODDB/datafile/sysaux.dbf’ size 512M

extent management local

segment space management auto

autoextend on next 20M


3. Run utlu102i.sql

This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.


Review the output and make any necessary alterations. Make a note of how many invalid objects there are.

4. Shut the database down with either normal or immediate

shutdown immediate

5. Copy the spfile (or pfile) and the password file from the existing home to the 10g one.


6. Edit oratab

Alter /etc/oratab (or /var/opt/oracle/oratab) to point to the10g home. Once done, rerun oraenv to bring the alteration into effect.

7. Upgrade the database

sqlplus “/ as sysdba”

startup upgrade

This next bit is the upgrade itself. It takes roughly half an hour to complete. Spool the output to a file so that you can review it afterward.


8. Recompile any invalid objects


Compare the number of invalid objects with the number noted in step 3. It should hopefully be the same or less.

9. Then check the status of the upgrade


10. Alter or remove initialisation parameters

Temporarily creating a pfile is the easiest way.

create pfile from spfile;

shutdown immediate

vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora

Alter/remove parameters identified in step 9. Set compatible to


create spfile from pfile;

shutdown immediate


its done….

Read Full Post »

Bitbach's Blog

Just another Oracle weblog @ WordPress

Daniel Westermann's Blog

simplicity...that's it

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog


Site for Ayurveda and Yoga articles


Few adventures in my slow running life...


4 out of 5 dentists recommend this WordPress.com site


Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World