Tuesday Apr 26, 2016

MOS Note 1454618.1: Quick Reference to Database PSUs, CPUs, BPs and Patchsets

Sometimes my mouse arm gets tired by clicking myself through MOS notes just to download a specific PSU or BP - and as I experiment a lot with PSUs and BPs right now I clicked a lot in the past days and nights. 

Usually I'd start with either MOS Note:161818.1 - then click on the release link (e.g. 12.1.0.x) in the left-most column, then into the Availability and Known Issues not (e.g. MOS Note:1683799.1 for Oracle Database 12.1.0.2.0) and then select the most recent patch from the list of Current Recommended Patches.

Even though we all agree that you should be ideally always on the most recent BP (or at least PSU) there may be situations where you need to access an older PSU or BP or just the CPU.
So what if you need for instance the October 2015 PSU?
This is obviously not linked from the above note.

My usual click routine guides me via the Critical Patch Updates and Security Alerts page, from there via the particular release date (e.g. October 2015) to the Database Server Version (e.g. linked from here is MOS Note:2037108.1) and then to the patch number I'm searching for.

Just learned a few days ago by accident that there's a MOS Note which I have on my favorites since then:

containing all this information - even for 8.1.7.4 patches if you still need them ;-)

After listing the base release first followed by the available patch sets it offers you the links to the PSUs, CPUs and BPs - and if you are looking for the patches containing the OJVM you'll find them by scrolling down towards the end of the note as well in a separate section. 

MOS Note Patches PSUs CPUs SPUs BPs

In fact a very useful MOS Note.
.

--Mike

Thursday Apr 21, 2016

Oracle April 2016 PSU and Proactive BPs are there

Hurray, it's Patching Day!

Sounds a bit like D-Day ;-) But April 19, 2016 the most recent April PSUs (Patch Set Updates) and BPs (Bundle Patches) got released.

Find all the necessary information with the below links: 

The important change in the April PSU/BP release:
The database patch for "Engineered Systems and Database In-Memory 12.1.0.2" luckily got renamed into "Proactive Bundle Patch 12.1.0.2". That is not only a rebranding but it should express that we would like to encourage you to apply the Bundle Patches
instead of the PSUs. Simple reason is that the BPs will contain optimizer fixes. 

In the MOS Note: 2102148.1 (Patch Set Update and Critical Patch Update April 2016 Availability Document) you'll find a section 3.1.4 linking to the database patches

This is the recommended one for Oracle Database 12.1.0.2:

  • Database Proactive Bundle Patch 12.1.0.2.160419 (Apr2016) Patch 22899531,

But right now it is available for Linux-x86-64, zLinux and Intel Solaris only. Not sure when the others will get released. Please find links to the regular PSUs and other ports and releases such as 11.2.0.4 and Windows etc in the above MOS Note: 2102148.1.

This is the list of fixes included in this Bundle Patch:

And don't worry about the name - I found out yesterday that not all MOS Notes have adopted the new naming convention to rename "Bundle Patches for Engineeered Systems and DB In-Memory" which was very misleading anyway into the new "Proactive Bundle Patches" naming. This may take a few additional days I'd guess ...

I will download it right now and patch my HOL environment.

And as usual don't forget the most recent version of opatch (Patch 6880880).

opatch download MOS

.

--Mike 
.


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

.

Tuesday May 26, 2015

Oracle 12.1.0.2 - Security Behavior Change with non-SYSDBA Triggers

Oracle Database SecuritySometimes things get revealed at unexpected occasions. This one happened during a recent customer upgrade to Oracle Database 12c with a 3rd party geospatioanl application installed (ESRI).

At the very end of the upgrade the customer saw many ORA-1031 (insufficient privileges) errors and it seemed to be that nothing was working correctly anymore. 

This happened during the run of catupend.sql. The following code path in  catupend.sql causes the error.

cursor ddl_triggers is                                       
   select o.object_id from dba_triggers t, dba_objects o     
    where t.owner = o.owner and t.trigger_name = o.object_name
      and o.object_type = 'TRIGGER'                          
      and (t.triggering_event like '%ALTER%' or              
    t.triggering_event like '%DDL%');     


ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SDE.DB_EV_ALTER_ST_METADATA
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 1279
ORA-06512: at line 20

Apparently there's no access to an application trigger anymore - which got deployed as a system trigger (for more information about ESRI's system trigger please click this link). Even though this is strange it doesn't seem like a big issue. But in fact it is as this procedure failed and caused other stuff not getting validated correctly. So subsequent actions (for instance the run of utlu121s.sql, the post upgrade script) failed with ORA-1031 as well pointing to DBMS_UTILITY.

The customer [Danke Andy!!!] itself found the workaround by pattern matching similar issues in MOS and trying some grants - the 3rd one did the trick:

  • GRANT ADMINISTER DATABASE TRIGGER to SDE;


So it was obvious that something in the security architecture in Oracle Database 12.1.0.2 had been changed - and somebody forgot to document it. Later on I've learned that this change got introduced with the July 2013 PSU/CPU as well. I don't blame the customer for not applying PSUs since almost two years - I knew that upfront and we are implementing a 2-PSUs-per-year strategy now with the upgrade to Oracle Database 12c. 

The system trigger ESRI had created couldn't be validated anymore under the context of the SDE (ESRI's application) user. Therefore it failed but caused other actions to fail as well.  

This behavior change is related to "SYSDBA privilege should not be available in non-SYS owned DR procedure / trigger execution" which is first fixed into 12.1.0.2, and then backported as part of CPU July-2013.
When SYS executes a non-SYS owned DR procedure or a Trigger, the SYS privileges would not available during the procedure/trigger execution. The procedure/trigger owner privileges prevail.

--Mike

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

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

Thursday Jan 19, 2012

Fundamental Oracle flaw revealed??? Really ...?

This Infoworld article from Jan 17, 2012  Fundamental Oracle flaw revealed did alert Oracle database customers.Infoworld has raised this issue to Oracle before going public with it. Patches are included in the Jan 2012 CPU and PSU. So again, it's strongly recommended to apply the Jan 2012 PSU (or CPU if you are just asking for security fixes) to your environments.

What is the background of this issue?
Everything in an Oracle database is dependent on the SCN (System Change Number). This number is crucial to ensure read consistency. It will always be just incremented and is defined as a large 48-bit integer (281 trillion SCNs). But the SCN can jump as well - especially in cases of distributed transactions. Besides that hard limit there's also a soft limit for the SCN (see the MOS Note for more information).
Distributed Transaction

Hot backup bug
Now there's a backup bug which will increment the SCN to a much higher value once ALTER DATABASE BEGIN BACKUP gets used. We call this putting tablespaces into hot backup mode. Actually I'd assume that most people out there (at least those doing backups on a regular basis) use RMAN - and RMAN does not need to put anything into hot backup mode when creating online backups as the real downside of the hot backup mode is an increased value of log information.
Strong recommendation: Use RMAN! And you may apply patch 12371955: "High SCN growth rate from ALTER DATABASE BEGIN BACKUP in 11g" to your environment.

Combination of backup up and distributed transactions
The people who've detected this issue paint now a large Oracle database infrastructure to the wall - with many databases running distributed transactions - and a misbehaving BEGIN BACKUP routine in combination. This would elevate the SCN over and over again - on all interconnected databases - over time as the SCN will be synched over and over again - and will do huge jumps because of the backup bug.

What's the real risk?
I'm not a security expert - but I've seen many customer environments in the real world. I'd say (and skilled DBAs gotten interviewed by Infoworld and others stated similar opinions) it may be just a small risk in larger environments where many databases are connected together - and CPUs or PSUs got not applied on a regular basis. The PSU/CPU fix will prevent the SCN to be incremented in extensive jumps by several ways.
I'd completly disagree with Infoworld's prediction that databases will crash or abandon - transactions won't be executed anymore and an error will be raised. Yes, this is bad enough - true - but the database(s) will remain open.

What should you do?
Apply the January 2012 PSU or CPU and hot backup fix covered by patch 12371955. But keep in mind

  • Take the PSUs over CPUs as PSUs will contain also important non-dictionary changing fixes whereas CPUs contain security fixes only
  • You can't put a CPU on top of a previous applied PSU
  • Both CPUs and PSU are cummulative 
  • And well, you'll need Extended Support to get acces to PSUs or CPUs for Oracle Database 10.1 and 10.2 - and yes, please don't cry: We've asked you to upgrade a looooooong time ago ;-)
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
« May 2016
SunMonTueWedThuFriSat
1
2
3
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
30
31
    
       
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