Thursday, September 29, 2016

Oracle 11g R2 -- Check the Patchsets Applied to the Database using just SQL

Problem

You need to find out what patchsets (PSUs) have been applied to your database and what is the latest patchset (PSU) installed.

You don't have access to the console and/or to the OPatch tool.

You want to use just SQL to check the patchsets.

 

Solution

Use the DBA_REGISTRY_HISTORY view:

ACTION_TIME                     ACTION          NAMESPACE  VERSION         ID BUNDLE_SERIES COMMENTS               
------------------------------- --------------- ---------- ---------- ------- ------------- ------------------------
27-JAN-09 03.53.20.839532000 PM APPLY           SERVER     10.2.0.4         1 CPU           CPUJul2008             
15-MAY-10 11.23.58.113380000 AM CPU                                   6452863               view recompilation     
15-MAY-10 12.46.05.516308000 PM APPLY           SERVER     10.2.0.4         3 PSU           PSU 10.2.0.4.3         
15-MAY-10 12.57.19.290805000 PM CPU                                   6452863               view recompilation     
10-MAR-12 09.14.49.131217000 AM APPLY           SERVER     10.2.0.4         4 PSU           PSU 10.2.0.4.4         
10-MAR-12 09.27.42.850194000 AM APPLY           SERVER     10.2.0.4         7 OPSU          PSU 10.2.0.4.11        
07-JUL-12 01.59.03.080230000 PM APPLY           SERVER     10.2.0.4         8 OPSU          PSU 10.2.0.4.12        
21-MAY-16 11.03.08.723585000 PM VIEW INVALIDATE                       8289601               view invalidation      
21-MAY-16 11.03.48.279506000 PM UPGRADE         SERVER     11.2.0.4.0                       Upgraded from 10.2.0.4.0
22-MAY-16 12.29.34.759392000 AM APPLY           SERVER     11.2.0.4         8 PSU           PSU 11.2.0.4.8         

 10 rows selected

The COMMENTS columns will have additional information about the patchsets applied


Reference:
 

No comments: