Tuesday Feb 02, 2016

How to find out if a PSU has been applied? DBMS_QOPATCH

pflaster.jpgSince we change the PSU and BP patch numbering from Oracle Database 12.1.0.2.PSU6 to 12,1,0,2,160119 it is almost impossible to distinguish from the patch name only if you have applied a PSU or a BP.

But:
In Oracle Database 12c there's a package available which is very useful to query plenty of information about patches from within the database: DBMS_QOPATCH.

Here are a few helpful examples which I created by checking in our DBaaS Cloud database.

Which patches have been applied (or rolled back)?

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 20415564
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description : Database PSU 12.1.0.2.3, Oracle JavaVM Component (Apr2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20415564/18617752/
                  20415564_apply_ORCL_CDBRO
OT_2015Jun24_06_18_09.log
        Status : SUCCESS

Patch Id : 20299023
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description : Database Patch Set Update : 12.1.0.2.3 (20299023)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20299023/18703022/
                  20299023_apply_ORCL_CDBRO
OT_2015Jun24_06_18_11.log
        Status : SUCCESS

Patch Id : 20848415
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20848415/18918227/
                  20848415_apply_ORCL_CDBRO
OT_2015Jun24_06_18_15.log
        Status : SUCCESS

Patch Id : 20848415
        Action : ROLLBACK
        Action Time : 24-JUN-2015 06:52:31
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20848415/18918227/
                  20848415_rollback_ORCL_CD
BROOT_2015Jun24_06_52_29.log
        Status : SUCCESS

Patch Id : 20618595
        Action : APPLY
        Action Time : 24-JUN-2015 13:52:13
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20618595/18956621/
                  20618595_apply_ORCL_CDBRO
OT_2015Jun24_13_52_12.log
        Status : SUCCESS

Patch Id : 20618595
        Action : ROLLBACK
        Action Time : 24-JUN-2015 14:37:11
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20618595/18956621/
                  20618595_rollback_ORCL_CD
BROOT_2015Jun24_14_37_10.log
        Status : SUCCESS

Patch Id : 20415564
        Action : ROLLBACK
        Action Time : 27-JAN-2016 17:43:18
        Description : Database PSU 12.1.0.2.3, Oracle JavaVM Component (Apr2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20415564/18617752/
                  20415564_rollback_MIKEDB_
CDBROOT_2016Jan27_17_42_16.log
        Status : SUCCESS

Patch Id : 21555660
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description : Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/
                  21555660_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_17.log
        Status : SUCCESS

Patch Id : 21359755
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description : Database Patch Set Update : 12.1.0.2.5 (21359755)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/
                  21359755_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_18.log
        Status : SUCCESS

Patch Id : 21962590
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21962590/19426224/
                  21962590_apply_MIKEDB_CDB
ROOT_2016Jan27_17_42_21.log
        Status : SUCCESS

PL/SQL procedure successfully completed.
.

Where's my home and inventory?

SQL> set pagesize 0

SQL> set long 1000000 

SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;

Home and Inventory
-------------------------------------------------------------

Oracle Home     : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory    
    : 
/u01/app/oraInventory


Has a specific patch been applied?

Lets check for the latest PSU. 

SQL> select xmltransform(dbms_qopatch.is_patch_installed('21359755'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;

Patch installed?
-------------------------------------------------------

Patch Information:
         21359755:   applied on 2015-10-22T21:48:17Z

.

What's tracked in my inventory?

The equivalent of opatch lsinventory -detail ...

SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual; 

Oracle Querayable Patch Interface 1.0
----------------------------------------------------------------
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/app/oraInventory
----------------------------------------------------------------

Installed Top-level Products (1):
                                    12.1.0.2.0
Installed Products ( 135)
                               ...

.

Additional Information and Patches

If you need more helpful examples you may check this excellent blog post by Simon Pane (Pythian):

And credits to Martin Berger for sending me this important information:

Just in case there are multiple DBs running from the same O_H, and someone      
queries dbms_qopatch.get_opatch_lsinventory automated from all DBs (as in       
automated monitoring/reporting scripts) I'd recommend Patch 20599273 -          
otherwise there might be strange XM errors due to race conditions. 

.

--Mike 

Wednesday Jan 20, 2016

Oracle January 2016 CPU PSU BP available now - BE AWARE OF CHANGES IN PATCH NUMBERING

Last night the PSUs and BPs for January 2016 have been made available for download on support.oracle.com.

Oracle Critical Patch Update Advisory - January 2016

http://www.oracle.com/technetwork/topics/security/cpujan2016-2367955.html 

It contains 248 security fixes across all products and platforms. And of course important non-security fixes - and that's why we recommend to apply the PSUs (or the BPs in case you are on Exadata or an Oracle In-Memory user) as soon as possible. 

Change in Patch Numbering

Please be aware that as of November 2015 there's been a change in patch numbering introduced which most of you may not be aware of. A database PSU was named 12.1.0.2.5 before (or I used to call it 12.1.0.2.PSU5 before to make clear that a PSU and not a BP has been applied). But the new notation will change the 5th digit to a 6-digit-number to include the date. See MOS Note:2061926.1 for details.

Example:

  • Before: Oracle Database 12c PSU October 2015 ... 12.1.0.2.5
  • Now: Oracle Database 12c PSU January 2016 ... 12.1.0.2.160119 

More Information? 

--Mike

Thursday Oct 22, 2015

PSU (and CPU/SPU) October 2015 got released

October 21, 2015 - Oracle released the October 2015 SPU/CPU, PSU and BPs. 

See:

For Oracle Database 12.1.0.2 and Oracle Grid Infrastructure access the PSUs from here - if you have only a single instance database you can safely take the Combo patch as you'll get downtime anyways - but for customers running RAC you need to evaluate the OJVM component as this will incur downtime which the database-only patch does not require as it can be applied rolling.

Patch Set Updates

Document Description Rolling RAC Patch Download
Note:21555660.8 Oracle JavaVM Component 12.1.0.2.5 Database PSU (Oct 2015) (OJVM PSU) No Patch:21555660
Note:21520444.8 Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 DB PSU (Oct 2015) Part Patch:21520444

No patch found at the moment [Mike]
Note:21359755.8 12.1.0.2.5 (Oct 2015) Database Patch Set Update (DB PSU) Yes Patch:21359755

Grid Infrastructure

Document Description Rolling RAC Patch Download
Note:21523260.8 Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 GI PSU (Oct 2015) Part Patch:21523260
Note:21523234.8 12.1.0.2.5 (Oct 2015) Grid Infrastructure Patch Set Update (GI PSU) Yes Patch:21523234


And be aware to patch your DBaaS Cloud databases as well - this is not done automatically ;-)

--Mike

.

Monday Aug 17, 2015

DBCA 12c and "datapatch.pl" - things to know

A few weeks ago I did blog about the DBUA (Database Upgrade Assistant) not executing 'datapatch' (i.e. not applying the SQL changes involved with a SPU/PSU/BP) automatically:

Again, please note that this behavior DOES NOT APPLY to command line upgrades done with catctl.pl - as you can see from this somewhat disturbing messages during the upgrade in phase 65 and phase 69 (which are not errors but just informational messages for datapatch's execution):

Datapatch phases command line upgrade catctl.pl

But afterwards I have learned that things are worse.
The same behavior is true when you create a database.

Not a typo.
You create a fresh database with DBCA (Database Configuration Assistant), you are a honest customer, you have followed our advice and applied the most recent PSU (or SPU or BP) into your Oracle Home. You don't even deploy one of the preconfigured databases but use the CREATE CUSTOM DATABASE option of DBCA. And the database will run from the patched home - but the SQL changes haven't been applied to it

Reason?

DBCA does not call 'datapatch' for database changes.

I consider this even worse than the DBUA behavior as the person who upgrades a database in most cases is aware of the future home. But the person who either deploys a new database or asks for one to be deployed is often not identical with the person who did patch the homes.

And there's no warning displayed yet nor (afaik) is there a MOS note available talking about it.

How do you fix the issue?

After creating a new database make sure to run:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

and double check with DBA_REGISTRY_SQLPATCH view:

select  PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
from DBA_REGISTRY_SQLPATCH
order by BUNDLE_SERIES;

Actually to be 100% you may find some patch information in DBA_REGISTRY_SQLPATCH showing that the JAVAVM patch has been applied in case you've installed the Combo version of the PSU. But you'll miss the database changes.
.

Related Blog Posts datapatch.pl:

--Mike


Addition - Sept 18, 2015:

Please be aware that the same thing happens on ODA (Oracle Database Appliance) with the oakcli. This will be fixed in the version 12.1.2.5 (ODA/oakcli)
.

Monday Apr 20, 2015

Oracle PSU and BP April 2015 is available

As of April 14, 2015:

The April 2015 PSU/BP is available!

Here's the most important information:

Please find below the links to the Recommended Patches and Patch Numbers for each of your database releases:

12.1.0.2
 Availability and Known issues for 12.1.0.2      Note:1683799.1
12.1.0.1
 Availability and Known issues for 12.1.0.1      Note:1565082.1 
11.2.0.4
 Availability and Known issues for 11.2.0.4      Note:1562139.1
 
11.2.0.3
 Availability and Known issues for 11.2.0.3      Note:1348336.1

11.1.0.7
 List of fixes included in 11.1.0.7              Note:601739.1

-Mike

Monday Mar 09, 2015

Applying a PSU or BP to a Single-/Multitenant Environment

I have already explained in broad details a while ago how to:

But one may miss the steps for applying a PSU (Patch Set Update) or BP (Bundled Patch) to a Single-/Multitenant Environment. At first everything will work just the same if you choose the Everything-at-Once strategy as datapatch will adjust all the required things regardless of being executed in a stand-alone or a singe/Multitenant environment.

But what happens if you apply a PSU or a BP to one of your Multitenant environments and want to move PDBs one after another (or a few at the same time) to the new environment?
Or revert a PSU by plugging out from a CDB with the PSU inside - and plug it back into a CDB with a lower version or no PSU at all? 

First step - Check Plug In Compatibility 

Before you can even start your unplug/plug operation you should always perform the plugin check. This is divided in two simple steps:

  1. Create the XML description file for your PDB in CDB_SOURCE
    exec DBMS_PDB.DESCRIBE ('/home/oracle/PDB1_unplug.xml', 'PDB1');
  2. Run the plug in check in CDB_DEST
    begin
      if DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/PDB1_unplug.xml','PDB1') then  
      DBMS_OUTPUT.PUT_LINE('No violations found - you can relax');
    else
      DBMS_OUTPUT.PUT_LINE('Violations found - check PDB_PLUG_IN_VIOLATIONS');
    end if;
    end;
    /

No Plugin Violations?

Then please follow the procedure described in:
http://blogs.oracle.com/UPGRADE/entry/upgrade_pdbs_one_at_a
without the upgrade part as you don't need to upgrade anything in this case of course. 


Higher patch in CDB_DEST than in CDB_SOURCE?

Then run this query:

select TYPE,MESSAGE,ACTION from PDB_PLUG_IN_VIOLATIONS where NAME='PDB1';

It will tell you to execute datapatch:

TYPE    MESSAGE
------  ----------------------------------------------------------------------------
ERROR   PSU bundle patch 1 (PSU Patch 4711): Installed in the CDB but not in the PDB

ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

Lower patch in CDB_DEST than in CDB_SOURCE?

Now this becomes a bit more tricky. See the output of PDB_PLUG_IN_VIOLATIONS:

TYPE  MESSAGE
----- ----------------------------------------------------------------------------
ERROR PSU bundle patch 1 (PSU Patch 4711): Installed in the PDB but not in the CDB

ACTION
------------------------------------------------
Call datapatch to install in the PDB or the CDB

Huh? Install???
What does this mean? Should I install now the current CDB/PDB's PSU into my target environment before being able to step down? 

Actually I think this message is misleading. And when you look into the MyOracle Support Note describing this under scenario 3 (MOS Note:1935365.1 - Multitenant Unplug/Plug Best Practices) you'll see that the author silently assumed as well that is is more likely that you'll remove the patch from the PDB. 

But how do you remove changes which came in with datapatch from within a PDB only?

You will need to run datapatch -rollback on the affected PDBs only:

$> datapatch -rollback <patch id> –force [–bundle_series] -pdbs <pdb1,pdb2,...,pdbn>

For further information see:

--Mike 

Wednesday Oct 16, 2013

October 2013 PSUs and CPUs - News for 12c

Last night CET the most recent Patch Set Updates (PSU) and Critical Patch Updates (CPU aka SPU) got published on MOS. And there's a significant and remarkable change for Oracle Database 12c onwards. MOS Note: 1571391.1 - Patch Set Update and Critical Patch Update October 2013 Availability Document says:

2.1 Database Security Patching from 12.1.0.1 Onwards

Starting with Oracle Database version 12.1.0.1, Oracle only provides Patch Set Updates (PSU) to meet the Critical Patch Update (CPU) program requirements for security patching. Security Patch Updates (SPU) will no longer be available. Oracle has moved to this simplified model due to popular demand. The PSU is Oracle's preferred proactive patching mechanism since 2009.

For more information, see My Oracle Support Note 1581950.1, Database Security Patching from 12.1.0.1 onwards.

That's a real change. It's not harmful at all as our recommendation for years now is to apply PSUs as they contain not only the security patches but also the important fixes for critical issues. And apply them asap - the day we publish the Security Patch Advisory with some details is the day when external security experts go public as well with their findings.

MOS Note: 756671.1 - Oracle Recommended Patches -- Oracle Database will guide you to the databases patches for your platform. If you miss the PSU for Oracle Database 10.2.0.5 (the Pre-Release Announcement stated that there will be a PSU for 10.2.0.5) my personal understanding is: as Oracle 10.2.0.5 went out of Extended Support in July 2013 there won't be any October PSU released anymore.

And I'll apply the new 12.1.0.1 Oct13 PSU now to my Oracle Database 12c Multitenant environment as well :-)

Don't forget:
MOS Note: 224346.1 - OPatch: Where Can I Find the Latest Version of OPatch?
Find it via Patch Placeholder 6880880

-Mike

About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search

Archives
« February 2016
SunMonTueWedThuFriSat
 
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
     
       
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers