Ask TEM Dba

To manually upgrade an Oracle Instance from 10.2.0.3 to 10.2.0.4 is pretty much a strait forward routine if one already have a 10.2.0.4 home on the machine.

Simply shutdown the instance and edit the /etc/oratab and the /usr/local/oraenv (if this has been changed from the original version) to make sure your instance now uses the new ORACLE_HOME.

Startup the instance in upgrade mode and run the pre-check script.

SQL>sqlplus / as sysdba
SQL>startup upgrade
SQL>spool upgrade_info.log
SQL>@?/rdbms/admin/utlu102i.sql
SQL>spool off

Check the log for possible problems and correct them if needed

Now run the upgrade script

SQL>sqlplus / as sysdba
SQL>spool patch.log
SQL>@?/rdbms/admin/catupgrd.sql
SQL>spool off

Check the log for errors. Most likely there will be an error ORA-00904 regarding OTHER_XML for the package body DBMS_SQLPA.

Also at the end of the log a scary message saying

Component Status Version HH:MM:SS
Oracle Database Server INVALID 10.2.0.4.0 00:06:56

Looks bad but in this case it’s not
A select COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; will show what really went wrong.
Oracle Database Packages and Types 10.2.0.4.0 INVALID

The fix for this problem is to drop the plan_table and re-create it (See MetaLink Note: 565600.1)

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

This should fix the problem and the upgrade to 10.2.0.4 is done.