Upgrade Oracle to 11.1.0.7

April 27th, 2009 Posted in Oracle How-To

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;
Tags: , ,
This entry was posted on Monday, April 27th, 2009 at 5:29 pm and is filed under Oracle How-To. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “Upgrade Oracle to 11.1.0.7”

  1. auslaw Says:

    June 2nd, 2009 at 9:27 am

    I have 3 instances running on 11.1.0.6.0

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

    Will running this after installing the patch update all the instances?



  2. Matt Says:

    June 2nd, 2009 at 3:55 pm

    Yes, you need to run the upgrade script on each instance.



  3. Manim Says:

    September 15th, 2010 at 5:10 am

    Matt,

    I did all the steps you have provided. I checked the version but still shows as

    SQL> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
    PL/SQL Release 11.1.0.6.0 – Production
    CORE 11.1.0.6.0 Production
    TNS for 32-bit Windows: Version 11.1.0.6.0 – Production
    NLSRTL Version 11.1.0.6.0 – Production

    5 rows selected.

    There is an error appeared in the upgrade log file:

    Oracle Database 11.1 Post-Upgrade Status Tool 09-15-2010 12:29:13
    .
    Component Status Version HH:MM:SS
    .
    Oracle Server
    . SP2-0042: unknown command “GRem” – rest of line ignored.
    . ORA-20000: Ultra Search version is 11.1.0: patch not applicable
    . ORA-06512: at line 18
    . ORA-14452: attempt to create, alter or drop an index on temporary table
    already
    . in use
    . SP2-0310: unable to open file
    “C:\ORANT\Oracle11g\product\11.1.0\db_1\RDBMS\ADMIN\a1101000.sql”
    . VALID 11.1.0.6.0 00:32:55
    . VALID 11.1.0.6.0
    JServer JAVA Virtual Machine
    . VALID 11.1.0.6.0 00:00:00
    Oracle Workspace Manager
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    OLAP Analytic Workspace
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    OLAP Catalog
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle OLAP API
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle Enterprise Manager
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle XDK
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle Text
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle XML Database
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle Database Java Packages
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle Multimedia
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Spatial
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle Ultra Search
    . VALID 11.1.0.6.0 00:00:00
    . VALID 11.1.0.6.0
    Oracle Expression Filter
    . VALID 11.1.0.6.0 00:00:00
    . INVALID 11.1.0.6.0
    Oracle Rule Manager
    . INVALID 11.1.0.6.0 00:00:08
    . INVALID 3.0.1.00.08
    Oracle Application Express
    . INVALID 3.0.1.00.08 00:00:00
    Gathering Statistics
    . 00:02:48
    Total Upgrade Time: 00:35:55

    PL/SQL procedure successfully completed.

    Can you please suggest any idea to get it done.
    My OS: Windowsxp
    Current Oracle verion: 11.1.0.6.0
    Tried to upgrade : 11.1.0.7.0



Leave a Reply