Upgrade from Oracle 9i to 10g

Steps to upgrade 9i to 10g:

  1. Take a manual cold backup or RMAN backup of all running databases. You can get backup steps through below links:


  1. Create sysaux tablespace which is new mandatory tablespace in oracle 10g.


Create tablespace sysaux datafile '/u01/oradata/cobra/sysaux01.dbf'

size 500m extent management local

segment space management auto;


  1. Compile invalid objects and take count of all invalid objects.


SQL>select count (*) from dba_objects where status=’INVALID’;


  1. if you have not already 10g software installed in different home then install it .

     Now perform preupgrade check, connect to the database which need to be upgraded

and perform following commands.


SQL>spool /u01/log/preupgrade.sql;



  1. Shutdown the database with immediate or normal option.


SQL>shutdown immediate;


  1. Now copy pfile/spfile and password file from existing 9i home to new 10g home.

   using simple ‘cp’ command.

And update the pfile and delete obsolete parameters as per 10g.


  1. Update profile as per new oracle 10g home.


  1. Update “/var/opt/oracle/oratab” in solris and “/etc/oratab” in linux to point new oracle 10g home.


  1. Now upgrade the database as below:


            Export ORACLE_SID=<sid_name>

            Sqlplus “/ as sysdba”


              SQL>spool /u01/log/upgrade.sql

             SQL>startup upgrade



  1. Recompile for any invalid objects as step 3 and compare with previous result.


SQL>shutdown immediate



  1. now run post upgrade check utility as follows:


SQL>spool /u01/log/postupgrade.sql;






You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply