【Oracle 筆記 】Find What Patches Have Been Applied to Oracle Database

1. login as ORACLE_HOME dba
2. command: $ORACLE_HOME/OPatch/opatch lsinventory


[dba@dev OPatch]$ cd $ORACLE_HOME/OPatch
[dba@dev OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/proddb/proddb/11.2.2
Central Inventory : /etc/oraInventory
   from           : /u01/proddb/proddb/11.2.2/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.2.0
Log file location : /u01/proddb/proddb/11.2.2/cfgtoollogs/opatch/opatch2014-11-13_11-50-21AM_1.log

Lsinventory Output file location : /u01/proddb/proddb/11.2.2/cfgtoollogs/opatch/lsinv/lsinventory2014-11-13_11-50-21AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  10160615     : applied on Fri Apr 05 00:09:38 CST 2013
Unique Patch ID:  13348800
   Created on 17 Jan 2011, 05:06:03 hrs PST8PDT
   Bugs fixed:
     10160615

Patch  10149223     : applied on Fri Apr 05 00:08:03 CST 2013
Unique Patch ID:  13073567
   Created on 25 Oct 2010, 03:35:14 hrs PST8PDT
   Bugs fixed:
     10149223



--------------------------------------------------------------------------------

OPatch succeeded.


To know what db patches the DB server has applied，just look up table, registry$history . For example:



time                           action     name    ver                ID  COMMENTS                   BUNDLE_SERIES
------------------------------ ---------- ------- ---------- ----------  ------------------------   ---
17-OCT-09 09.51.47.000000 PM   UPGRADE    SERVER  10.2.0.1.0             Upgraded from 9.2.0.8.0
18-OCT-09 12.39.34.000000 AM   UPGRADE    SERVER  10.2.0.4.0             Upgraded from 10.2.0.1.0
25-OCT-09 07.43.22.000000 PM   APPLY      SERVER  10.2.0.4            2  PSU 10.2.0.4.2             PSU
25-OCT-09 07.59.46.000000 PM   CPU                              6452863  view recompilation
21-MAR-10 09.42.19.000000 AM   APPLY      SERVER  10.2.0.4            3  PSU 10.2.0.4.3             PSU
21-MAR-10 09.44.01.000000 AM   CPU                              6452863  view recompilation
13-JUN-10 10.35.37.000000 AM   APPLY      SERVER  10.2.0.4            4  PSU 10.2.0.4.4             PSU
13-JUN-10 10.41.06.000000 AM   CPU                              6452863  view recompilation
04-APR-13 09.36.51.293731 PM   VIEW INVAL                       8289601  view invalidation
                               IDATE
04-APR-13 09.36.51.781277 PM   UPGRADE    SERVER  11.2.0.2.0             Upgraded from 10.2.0.4.0
04-APR-13 09.38.39.183463 PM   APPLY      SERVER  11.2.0.2            0  Patchset 11.2.0.2.0        PSU

11 rows selected.



事實上，Oracle 也提供一個非常簡單驗證上 Patch 是否成功的方式，即是檢查 AD_BUGS 這表，檢查方式如下:

SELECT * FROM AD_BUGS WHERE BUG_NUMBER = <patch_number>

參考

1. OPatch Utility and Commands, https://docs.oracle.com/cd/B16240_01/doc/em.102/e15294/options.htm