oracledba.help
Legacy

Patching Oracle 11g Databases

<- Legacy

Overview

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 (11.2.0.3 to 11.2.0.4). 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.

Sales propaganda aside, patching an Oracle database is not a walk in the park. This is one of the most critical operations an Oracle DBA must perform. Hopefully this content better allows mere mortals to do it easier and more reliably.

Prerequisites

  • 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

Procedure

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 (11.2.0.4 etc.)

The following covers the common steps to applying an Oracle major patchset in a non-RAC environment. The patchset 11.2.0.4 is used in the below example session for the database DB1.

Procedure

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.

QC Check

Always check your database after you think your patch process is done to ensure it is not in an UPGRADE or other abnormal operational state. Use the below SQL to check: 

SELECT d.dbid,
       i.instance_name, i.host_name,
       d.open_mode, d.database_role,
       i.status, i.database_status,
       to_char(i.startup_time,'YYYY-MM-DD HH:MM') 
          AS "Started",
       d.log_mode, i.archiver, i.version
FROM v$database d, v$instance i 
WHERE UPPER(d.name) = UPPER(i.instance_name);

Common Errors

On Windows if attempting to apply a patch and you get a message that files that are still open:

  1. Ensure all Oracle Services set to Manual.
  2. 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)

Phase I

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

Phase II

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';

<- Legacy