Simon Krenger

One of the regularly recurring tasks is to apply a Patchset to a database. This post contains the basic steps required to apply Patchset 10.2.0.5 to an unpatched Oracle 10g Database (10.2.0.1). I am using this post mainly for my own reference, so please do not expect an extensive step-by-step guide. Note that you need Oracle Support (formerly Metalink) to access and download the patches.

Before we begin, I must state that I provide these instructions based on my experiences with database upgrades and these are by no means complete. When applying a Patchset, always refer to the patch_note.html provided in the installation media. This means you can use my steps only as a coarse guide. These steps can also be used to create your own “Upgrade Checklist”.

1. Backup your database with RMAN
   Before performing a database upgrade it is important to backup your database, in case anything goes wrong.
2. Read the Patch Notes
   As I already stated in my introduction, refer to the Patch Notes provided by Oracle to get platform-specific information on the upgrade process.
3. Check your schemas for invalid objects
   To check your database for invalid objects, execute the following query:

    

   SELECT COUNT(*) FROM dba_objects WHERE status='INVALID';

   This number should be 0. If there are any invalid objects, try to recompile them (using utlrp.sql). If there are still invalid objects after recompilation, contact the owner of the invalid objects (query the DBA_OBJECTS view to see the owner) or note the number and proceed with the next step. Also note this site for more informations on recompilation

4. Shutdown Database

   SHUTDOWN IMMEDIATE

   It is important that the database is properly shut down (either SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE). If you use SHUTDOWN ABORT, there will be errors when you want to apply the patch.

5. Install Patchset
   In this step, unpack the Patchset you downloaded and run the Installer (“./runInstaller” or “setup.exe”, depending on your platform). This will try to launch the Installer in graphical mode. If you have no GUI, edit the responsefile in the “response” folder and run the installer with the parameters “-silent -responseFile <path-to-file>”. This is a pretty straight-forward process.
6. Start DB in UPGRADE mode
   Open up SQLPlus, login AS SYSDBA and run the following command to start the database in migration mode:

    

   STARTUP UPGRADE

7. Checks before Upgrading
   To make sure your database is ready for patching, run the “utlu102i.sql” script by typing the following command:

    

   @?/rdbms/admin/utlu102i.sql

   Check the output for errors and verify your database is ready for upgrading.

8. Apply the patch
   Alright, here we go. To actually upgrade your database, run the following script:

    

   @?/rdbms/admin/catupgrd.sql

   This will start the upgrade process which can take quite a while, depending on the size of your database and the machine you are using. I recommend to spool the output of this script into a file. This way, you can easily analyse the process if anything goes wrong.

9. Restart database
   Everything alright? Nice! Now let’s restart the database:

    

   SHUTDOWN IMMEDIATE
   STARTUP

10. Recompile objects
    After the upgrade, check if there are any invalid objects (this is very likely) using the command described above (Check your schemas for invalid objects). If there are any invalid objects, recompile them by running the following query:

     

    @?/rdbms/admin/utlrp.sql

11. Post Upgrade Checks
    Run the following command to perform a Post-Upgrade-Check on your database:

     

    @?/rdbms/admin/utlu102s.sql

Using the steps mentioned above, I was able to upgrade a database in a relatively short time-period. Note that I tested these steps on a Windows x64 machine and installed the Patchset with the OUI GUI. Using responsefiles, the installation of the Patchset can be achieved without a GUI.