Patching Oracle 11g Databases
The most common types of Oracle patches are Patchsets and Critical Patch Updates (CPU). A Patchset upgrades an Oracle database from one version to another (22.214.171.124 to 126.96.36.199). A CPU is issued approximately every quarter.
In most cases there are two main phases to all Oracle patches. The first phase patches the binary application files and the second phase patches the database itself. The following covers the common steps to applying Oracle database patches in a non-RAC environment. The patch 21104036(39) is used in the below CPU example session for the database DB1.
Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1). In Windows there will be only be one bundled patch where Unix\LINUX environments will have PSU & SPU patches to download.
- REVIEW THE PATCH NOTES! These instructions are only a guide.
- Ensure the ORACLE_HOME and ORACLE_SID environment variables are set.
- If this is a Windows system:
- Install the patch from the same drive letter your ORACLE_HOME is on (C:).
- There are known bugs when installing Oracle patches from a different drive than the ORACLE_HOME.
- Ensure all console Windows open with: Run As administrator
- Determining which patch to download or apply for your OS\Oracle version can be confusing. If need be create an Oracle Support case to be 100% sure.
Oracle CPU Patches
1. Confirm opatch is working and your inventory is valid. OS> %ORACLE_HOME%\OPatch\opatch lsinventory Look for: OPatch succeeded. 2. Perform a clean shutdown of all Oracle services (databases, Oracle listeners, Oracle agents etc.). If NIX: OS> emctl stop dbconsole OS> lsnrctl stop SQLPLUS> shutdown immediate If Windows: a. Disable the (non-Oracle) Windows Management Instrumentation service. OS> sc config Winmgmt start= disabled b. Stop Windows Services OS> net stop msdtc OS> net stop winmgmt c. Stop Oracle Services OS> net stop Oracle<ListenerSvcName> OS> net stop OracleMTSRecovery OS> net stop Oracle<InstancSvcName> Check Windows services ensuring all ORACLE services down. 3. Set PATH for Perl Example from Administrator console used to apply patches: set PATH=%ORACLE_HOME%\perl\bin;%PATH% 4. Change to the directory where the CPU patch has been unzipped and apply patch. OS> cd C:\software\oracle\cpu\21104036 OS> %ORACLE_HOME%\OPatch\opatch apply Answer prompts (some examples below): Email address: <your email address> Oracle Support Hub URL: NONE Proxy specification: NONE Is the local system ready for patching? [y|n] y Process runs...(aprox 15 min.) oci.dll Error If you get the message that the file oci.dll is being used simply rename it then retry. Example: ORACLE_HOME\bin\oci.dll to ORACLE_HOME\bin\zoci.dll Look for: OPatch succeeded. or OPatch completed with warnings. Also check the opatch-[timestamp].log file generated in %ORACLE_HOME%\cfgtoollogs\opatch for any errors. 5. Startup core database and services. If NIX: SQLPLUS> startup OS> lsnrctl start If Windows: a. Re-enable the (non-Oracle) Windows Management Instrumentation service. OS> sc config Winmgmt start= auto b. Start Windows Services OS> net start msdtc OS> net start winmgmt c. Start Oracle Services OS> net start Oracle<InstancSvcName> OS> net start OracleMTSRecovery OS> net start Oracle<ListenerSvcName> Check Windows services ensuring all ORACLE services up. 6. Run the CPU patch catwinbundle.sql script. The name may be different for NIX (catcpu.sql etc.). a. OS> sqlplus sys/pw@MyDB AS SYSDBA c. SQLPLUS> @%ORACLE_HOME%\Bundle\Patch39\catwinbundle.sql Process runs...(aprox 15 min.) d. SQLPLUS> QUIT If successful you may see (exact output differs per patch): PL/SQL procedure successfully completed. 7. Check the Oracle inventory to confirm patch is present. %ORACLE_HOME%\OPatch\opatch lsinventory 8. Check\recompile any INVALID Objects. OS> sqlplus sys/pw@MyDB AS SYSDBA SQLPLUS> SELECT object_name FROM dba_objects WHERE status='INVALID'; SQLPLUS> @%ORACLE_HOME%\rdbms\admin\utlrp.sql SQLPLUS> SELECT object_name FROM dba_objects WHERE status='INVALID';
For some OS's you made need to run this with he zero (0) option. Example: @utlrp.sql 0
Oracle Patchsets (188.8.131.52 etc.)
The following covers the common steps to applying an Oracle major patchset in a non-RAC environment. The patchset 184.108.40.206 is used in the below example session for the database DB1.
1. Perform a clean shutdown of all Oracle services (databases, Oracle listeners, Oracle agents, and so on.) that run from the Oracle Home where you install this patch. OS> emctl stop dbconsole SQLPLUS> shutdown immediate OS> lsnrctl stop If Windows: Stop the "Distributed Transaction Coordinator" service (this is not an Oracle service). Check Windows services ensuring all ORACLE services down. 2. Start patch installer (Windows: setup.exe, UNIX: ./runInstaller). Welcome Select: Next Specify Home Details Confirm correct Name and Path entries then select Next. Product-Specific Prerequisite Checks. Confirm no issues found then select Next. Summary Select: Install Process runs (takes aprox. 10 minutes on a 2 mhz system)... On UNIX systems, when prompted run root script (as root). End of Installation Select: Exit 3. Startup exactly as shown and run catupgrd.sql script. OS> lsnrctl start If Windows, start the Oracle Windows service (OracleServiceDB1) OS> sqlplus /nolog SQLPLUS> connect sys/<enter_password> as sysdba SQLPLUS> startup upgrade SQLPLUS> @?\rdbms\admin\catupgrd.sql Process runs (takes aprox. 45 minutes on a 2 mhz system)... 4. Bounce database and run utlrp.sql script. SQLPLUS> shutdown immediate SQLPLUS> startup SQLPLUS> @?\rdbms\admin\utlrp.sql 5. Startup other services. OS> emctl start dbconsole If Windows start the "Distributed Transaction Coordinator" service.
On Windows if attempting to apply a patch and you get a message that files that are still open:
- Ensure all Oracle Services set to Manual.
- Reboot box and try again.
Remember to set them back to Automatic when done.
If you get any "File Not Found" errors try unzipping again to a subdirectory then rerun patch. In some related cases you may need to re-download the patch again as it may be corrupt. This is usually a problem for patchsets but no so much for CPU patches.
If Windows, you may find the native tool to unzip large zipped patch files does not unzip all the files. Use a current version of WinZip or the like to unzip in these cases.
APPENDIX - Deinstall CPU (for single instance)
1. Perform a clean shutdown of all Oracle services (databases, Oracle listeners, Oracle agents etc.). * OS> emctl stop dbconsole * SQLPLUS> shutdown immediate OS> lsnrctl stop If Windows: Stop the "Distributed Transaction Coordinator" service (this is not an Oracle service). Check Windows services ensuring all ORACLE services down. 2. If not already done, set the ORACLE_HOME environment variable to the correct Oracle home that was patched. OS> set ORACLE_HOME=<Oracle Home Path> 3. Go to the directory where you downloaded the patch: OS> cd C:\software\patches\cpu\21104036 4. Ensure that the directory containing the OPatch script appears in your PATH setting. 5. Enter the following command to run OPatch: OS> %ORACLE_HOME%\OPatch\opatch rollback -id 21104036 6. Inspect the opatch<timestamp>.log file generated for any errors. cd %ORACLE_HOME%\cfgtoollogs\opatch
1. Start the OracleService. 2. For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and follow these steps: OS> cd %ORACLE_HOME%\BUNDLE\Patchnn OS> sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @catcpu_rollback.sql SQL> QUIT Check the %ORACLE_HOME%\cfgtoollogs\catbundle\catbundle_WINBUNDLE_ <ORACLE_SID>_ROLLBACK_<timestamp>.log log file for any errors. 3. If catcpu_rollback.sql reports any Invalid Objects, compile the invalid objects as follows. For large numbers of objects, this compilation step can take some time. If you are applying this patch through the Oracle Enterprise Manager console, you will be prompted to run this compilation script. OS> cd %ORACLE_HOME%\rdbms\admin OS> sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> @utlprp.sql 0 - Check for invalid objects: SQL> SELECT object_name FROM dba_objects WHERE status='INVALID';