Upgrade Oracle to 11.1.0.7

My only 11G instance was running 11.1.0.6 and needed to be upgrade it to 11.1.0.7 to fix a couple of bugs. Apparently this patchkit also includes some new functionality. Before starting the upgrade I needed to do a bit of housekeeping. This instance is relatively small so I performed a full database backup as well as a full database export. I also created a pfile for good measure.

create pfile from spfile ;

I like to have a list of invalid objects to I can compare the before and after.

select object_name, owner, object_type
from all_objects where status like ‘INVALID’;

Next I checked the prerequisites for this upgrade. The time_zone check is a very important check that needs to be made.

select version from v$timezone_file;

I am running version 4 so I am okay to upgrade. Check out MetaLink Note 568125.1 if you are running anything other than version 4. Next I ran the script utlu111i.sql in the $OH/rdbms/admin directory. It is designed to let you know if there are issues to address prior to the upgrade. Next I shutdown my Oracle instance and performed the upgrade through the GUI installer. All went well. Then it was time to upgrade my instance.

sqlplus /nolog
startup upgrade
spool upgrade.log
@?/rdbms/admin/catupgrd.sql
spool off

This process took about 90 minutes to run so plan enough time for your upgrade. The script was run without errors. The final step of the script shutdown the database. I started the database normally, then shut it down normally, then started it up again normally. I generally take these extra steps just to ensure everything looks okay. To check the post-upgrade status this script is helpful

@?/rdbms/admin/utlu111s.sql

Next I needed to fix some invalid object which I did with this script.

@?/rdbms/admin/utlrp.sql

The final step to the upgrade process is to take a full database backup. Now I am fully upgraded to the latest version of Oracle. To check my version I ran.

select * from v$version
And then
select comp_name,status,version from dba_registry;