We are currently engaged with an assignment to migrate over 50 productions database to Exadata of different sizes , different OS and different business priority. Each passing day, we encounter some technical challenges and of course each situation educates and adds more knowledge.
I would like to share a small post on how to list/view the patching information in Oracle >= 12.1.0 version:
As of 12.1.0.2, you can extract the database patches information using the following methods:
1. Traditional 'opatch -lsinventory'
2. Review database alert.log
3. Use the dbms_qopatch package
Review database alert.log
We notice an Oracle 12.1.0.2 instance (at least on Exadata x5-2) dumping current patch information in its alert.log. This is pretty good, you no longer need to query lsinventory to know the patches that are applied. Just review your alert.log to know the patching information.
Below is the excerpt of the alert.log:
Wed May 25 17:59:05 2016
===========================================================
Dumping current patch information
===========================================================
Patch Id: 20243804
Patch Description: DATABASE BUNDLE PATCH: 12.1.0.2.5 (20243804)
Patch Apply Time: 2016-04-19 17:01:42 GMT+03:00
Bugs Fixed: 13498243,13640676,14165431,14254610,14643995,15894842,15996428,
16010876,16293223,16356176,16359751,16556115,16619249,16870214,16938780,
Use the dbms_qoptch package:
with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.*
from a,
xmltable('InventoryInstance/patches/*'
passing a.patch_output
columns
patch_id number path 'patchID',
patch_uid number path 'uniquePatchID',
description varchar2(80) path 'patchDescription',
applied_date varchar2(30) path 'appliedDate',
sql_patch varchar2(8) path 'sqlPatch',
rollbackable varchar2(8) path 'rollbackable'
) x;
Apparently there is a bug wen the query is executed on 12.1.0.1. Read "12.1.0.1 datapatch issue : ORA-27477: "SYS"."LOAD_OPATCH_INVENTORY_1" already exists (Doc ID 1934882.1)"
I would like to share a small post on how to list/view the patching information in Oracle >= 12.1.0 version:
As of 12.1.0.2, you can extract the database patches information using the following methods:
1. Traditional 'opatch -lsinventory'
2. Review database alert.log
3. Use the dbms_qopatch package
Review database alert.log
We notice an Oracle 12.1.0.2 instance (at least on Exadata x5-2) dumping current patch information in its alert.log. This is pretty good, you no longer need to query lsinventory to know the patches that are applied. Just review your alert.log to know the patching information.
Below is the excerpt of the alert.log:
Wed May 25 17:59:05 2016
===========================================================
Dumping current patch information
===========================================================
Patch Id: 20243804
Patch Description: DATABASE BUNDLE PATCH: 12.1.0.2.5 (20243804)
Patch Apply Time: 2016-04-19 17:01:42 GMT+03:00
Bugs Fixed: 13498243,13640676,14165431,14254610,14643995,15894842,15996428,
16010876,16293223,16356176,16359751,16556115,16619249,16870214,16938780,
Use the dbms_qoptch package:
with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.*
from a,
xmltable('InventoryInstance/patches/*'
passing a.patch_output
columns
patch_id number path 'patchID',
patch_uid number path 'uniquePatchID',
description varchar2(80) path 'patchDescription',
applied_date varchar2(30) path 'appliedDate',
sql_patch varchar2(8) path 'sqlPatch',
rollbackable varchar2(8) path 'rollbackable'
) x;
Apparently there is a bug wen the query is executed on 12.1.0.1. Read "12.1.0.1 datapatch issue : ORA-27477: "SYS"."LOAD_OPATCH_INVENTORY_1" already exists (Doc ID 1934882.1)"
