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”.
- Backup your database with RMAN
Before performing a database upgrade it is important to backup your database, in case anything goes wrong.
- 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.
- 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
- Shutdown Database
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.
- 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.
- Start DB in UPGRADE mode
Open up SQLPlus, login AS SYSDBA and run the following command to start the database in migration mode:
- Checks before Upgrading
To make sure your database is ready for patching, run the “utlu102i.sql” script by typing the following command:
Check the output for errors and verify your database is ready for upgrading.
- Apply the patch
Alright, here we go. To actually upgrade your database, run the following script:
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.
- Restart database
Everything alright? Nice! Now let’s restart the database:
SHUTDOWN IMMEDIATE STARTUP
- 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:
- Post Upgrade Checks
Run the following command to perform a Post-Upgrade-Check on your database:
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.