Square DBA – Oracle DBA Talk

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;

Upgrade an Oracle 10g database to Oracle 11g

Most of my databases are Oracle 10G instances. I have one instance that is Oracle 11G, but that was a fresh install. I have a test 10G database with some old production data in it. I am going to up upgrade it to 11G and I will post my experience. After the upgrade I will be setting up a test RMAN configuration. The plan is to work on backup and recovery. A co-worker and I will practice breaking a test database in various ways and trying to recover the database. It should be fun, stay tuned.