Oracle 9.x to 9.x Upgrade

1) Install Oracle binaries with new patch set

2) Check for invalid objects and run utlrp.sql to recompile

SQL> SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*)
FROM DBA_OBJECTS WHERE STATUS<>’VALID’
GROUP BY OWNER, OBJECT_TYPE, STATUS;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

3) Shutdown database and listener

4) Copy the parameter file to new oracle home and make sure
SHARED_POOL_SIZE & JAVA_POOL_SIZE greater than 150mb

5) Change /etc/oratab to new oracle home and relogin to set correct Oracle home

6) Connect as sysdba and run migrate script

connect / as sysdba
startup migrate
spool patch.log
@$ORACLE_HOME/rdbms/admin/catpatch.sql
spool off

7) Review for any errors

8) shutdown and startup

9) Run utlrp.sql to recompile any invalid objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

10) Modify the listener.ora file with new ORACLE_HOME and start the listener

11) Start listener