Oracle database PSU patch details - using SQL

-- To find applied PSU patch sets in Oracle
-- Using SQL and OS commands

CPU, PSU, SPU - Oracle Critical Patch Update Terminology Update

It all started in January 2005 with Critical Patch Updates (CPU).  Then Patch Set Updates (PSU) were added as cumulative patches that included priority fixes as well as security fixes.  As of the October 2012 Critical Patch Update, Oracle has changed the terminology to better differentiate between patch types.  This terminology will be used for the Oracle Database, Enterprise Manager, Fusion Middleware, and WebLogic.

Critical Patch Update (CPU) now refers to the overall release of security fixes each quarter rather than the cumulative database security patch for the quarter.  Think of the CPU as the overarching quarterly release and not as a single patch.

Patch Set Updates (PSU) are the same cumulative patches that include both the security fixes and priority fixes.  The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2).  Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version.

Security Patch Update (SPU) terminology is introduced in the October 2012 Critical Patch Update as the term for the quarterly security patch.  SPU patches are the same as previous CPU patches, just a new name.  For the database, SPUs can not be applied once PSUs have been applied until the database is upgraded to a new base version.

Bundle Patches are the quarterly patches for Windows and Exadata which include both the quarterly security patches as well as recommended fixes.

Note: Always visit oracle sites / news letters to get update with all Oracle patches

Method -1 : Using SQL query

$ sqlplus / as sysdba
sql>
select substr(action_time, 1, 30) action_time,
       substr(id, 1, 10) id,
       substr(action, 1, 10) action,
       substr(version, 1, 8) version,
       substr(BUNDLE_SERIES, 1, 6) bundle,
       substr(comments, 1, 20) comments
  from registry$history;

Sample output:
ACTION_TIME            ID    ACTION    VERSION    BUNDLE    COMMENT
-------------------            --    -----------    -------------    ------------    ---------------
21-OCT-11 04.48.04.498723 AM    0    APPLY    11.2.0.3        PSU    Patchset 11.2.0.2.0
19-AUG-13 06.07.26.023981 PM    7    APPLY    11.2.0.3        PSU    PSU 11.2.0.3.7

Method -2 : By OS command


$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory -bugs_fixed | egrep 'PSU|PATCH SET UPDATE'

Sample output:

bash-3.2$ ./opatch lsinventory -bugs_fixed | egrep 'PSU|PATCH SET UPDATE'
16619892   16619892  Fri Aug 16 22:11:20 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPU
16056266   16056266  Fri Aug 16 22:10:57 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPU
16368108   16056266  Fri Aug 16 22:10:57 GMT+05:30 2013RUNNING OWMV1120.PLB IN PSU 112036 GIVING ORA-0095
14727310   14727310  Fri Aug 16 22:10:43 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPU
14275605   14275605  Fri Aug 16 22:09:58 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPU
13923374   13923374  Fri Aug 16 22:09:49 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES
13696216   13696216  Fri Aug 16 22:09:20 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES
13343438   13343438  Fri Aug 16 22:08:15 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.1
12925041   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013112024GIPSU OLOGGERD CORE DUMP AT 'CRFLOGDB.C
13079948   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013CVU FILES NOT BEING PICKED UP FOR 11.2.0.2.4GIPSU
13348650   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.1
                                                       (INCLUDES DB PSU 11.2.0.3.1)
13396284   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3 GI PSU 1 HAS-CRS TRACKING BUG
13531373   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3.1GIPSU CVU CHECK FAIL FOR NETWORK
13540563   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013MERGE REQUEST ON TOP OF 11.2.0.3.1PSU FOR BUGS
13569812   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3.1GIPSU CVU FAIL TO DETERMINE IF ORACLE
                                                       NETWORK HICCUP PSU4 HAS BEEN APP
13696251   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.2
                                                       (INCLUDES DB PSU 11.2.0.3.2)
13919095   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.3
                                                       (INCLUDES DB PSU 11.2.0.3.3)
14001941   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013DOWNGRADE ISSUE FROM 11.2.0.3.1 TO 11.2.0.2 PSU4
14271305   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3.3GIPSU SIHA ORAAGENT.BIN ALWAYS CONSUME
14275572   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.4
                                                       (INCLUDES DB PSU 11.2.0.3.4)
15876003   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.5
16315641   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.6
16578706   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3.6GIPSU ROLLBACK WITH QOS FAILED IF
16619898   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.7

In simple :
$ cd $ORACLE_HOME/OPatch ( for rdbms home)
$ cd $GRID_HOME/OPatch ( for grid home in RAC)
$ ./opatch lsinventory
OR
$ ./opatch lsinventory -details

>> It will display all applied patch details  for rdbms home / grid home
Note : Always touch with Oracle support to apply latest PSU / CPU patch.