oracledba.help

Patching Oracle Databases

<- Install

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 22809813 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> 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
    set PATH=%ORACLE_HOME%\perl\bin;%PATH%
  4. Run opatch utility.
    Change to the directory where the CPU patch has been unzipped and apply patch.
     
      OS> cd C:\software\oracle\cpu\22809813
      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
    QC
    - Look for: OPatch succeeded. or OPatch completed with warnings.
    - Check log in \cfgtoollogs\opatch
  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 Oracle<ListenerSvcName>
    

    Check Windows services ensuring all ORACLE services up.
  6. Run the datapatch utility.
    The datapatch utility will then run the necessary apply
    scripts to load the modified SQL files into the database.
      OS> cd %ORACLE_HOME%/OPatch
      OS> datapatch -verbose
      Connecting to database...OK
      Bootstrapping registry and package to current versions...done
      Determining current state...done
      ...
    

    If successful you should see output similar to this:
    Patch 22809813: SUCCESS
  7. Check the logs files in \sqlpatch\<PatchNumber> for errors.
  8. Check the Oracle inventory to confirm patch is present. %ORACLE_HOME%\OPatch\opatch lsinventory
  9. 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'; 
    

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

<- Install