Oracle 12.2.0.1 Bundle Patch 12.2.0.1.170516 DBBP on Linux x86-64 is available

All credits go to Ricardo Maeda as I knew that we’ll release a Bundle Patch for Oracle 12.2.0.1 sometime this week – but I couldn’t find it linked from the usual MOS notes. And please don’t ask my why that is.

Anyhow, with patch 2579308 you’ll get access to the first BP for Oracle Database 12.2.0.1. There will be a first bigger Proactive Bundle Patch in July at the usual schedule – but this one is at least a start.

12.2.0.1.170516BP

Patch 2579308 – Oracle 12.2.0.1.170516BP

Plus in addition get the OPatch version 12.2.0.1.7 via patch 6880880.

The BP contains:

12.2.0.1.170516BP

First 12.2.0.1 Bundle Patch – Contents: Database BP and GI PSU

Obrigado, Ricardo!

–Mike

PSU or BP? Patch Set Update or Bundle Patch?

Well, in my new role as unofficial Junior Product Manager for Patching (just kidding) I get asked once a day (at least!) via email or in customer meetings or workshops: Should we take the PSUs or the BPs?

Should we take the PSUs or the BPs ?

PSUs are Patch Set Updates, BPs are (sometimes called: Proactive) Bundle Patches.

And the answer is very simple:

  • If you have an Oracle Engineered System: Take the Bundle Patches for Engineered Systems
  • In all other cases:
    • If you are on Oracle Database 12.1.0.x or newer: Take the Bundle Patches
    • If you are on Oracle Database 11.2.0.4: Take the Patch Set Updates
    • If you are on a release below Oracle Database 11.2.0.4: Upgrade!

Is there an official recommendation or guideline?

Yes, of course, there’s one. Very well explained in MOS Note: 1962125.1 – Overview of Database Patch Delivery Methods:

Bundle Patch Recommendation

Recommendation for Oracle 12c: Bundle Patches

What else do you need to know?

  1. Q: Is there a difference between “Bundle Patch” and “Proactive Bundle Patch“.
    A: It’s the same thing.
  2. Q: Can I apply Exadata Bundle Patches on non-Exadata Systems in Oracle 11g?
    A: Even though this is technically possible for Linux, there are certain restrictions. This is only supported in a standby configuration where one part is operated on an Exadata. See the FAQ at the end of MOS Note: 1962125.1 – Overview of Database Patch Delivery Methods
  3. Q: Can I flip from PSUs to BPs?
    A: When you approach a release or patch set upgrade (i.e. Oracle Database 11.2.0.3 to Oracle Database 12.1.0.2, or Oracle Database 12.1.0.1 to Oracle Database 12.1.0.2) you will start from scratch and have the full choice. But in-between a release you’ll have to deinstall at least the sql changes and roll in the new sql changes when you change between PSUs and BPs or vice versa. See my previous blog posts about switching from PSUs to BPs: https://mikedietrichde.com/2016/05/03/can-i-apply-a-bp-on-top-of-a-psu-or-vice-versa/
  4. Q: Why does Oracle still deliver PSUs in Oracle 12c even though it recommends to use the BPs?
    A: I don’t know. I can just assume that some customers insist to get the PSUs having a smaller number of fixes meaning potentially lower effect on their systems. But personally I totally disagree. When you look up the current issues list for Oracle 12.1.0.2 you will find out that many of the fixes are included in the BPs but not in the PSUs. If it would be my choice, I can perfectly (and better) live without PSUs but only getting BPs instead.
  5. Q: I have issues with the patches’ readme – can you explain it to me?
    A: No. Please log an SR. I get the “readme complaint” from almost every customer I see. And I see all the points and agree in most of them. Still, I’m not the owner of patching nor the owner of the readme’s. Telling it me is good – but telling it Oracle Support via an SR, and force a bug to be logged is the much better solution. Please please please, do log SRs when you are not happy with the patches’ readme, when things are unclear or wrongly carried over or whatever. The readmes get written by humans and they will need your feedback to improve the readmes.

–Mike

Keep your patch versions between Grid Infrastructure and Databases Homes in synch

Patch RecommendationI’ve had some interesting discussions with Anil Nair, our RAC Product Manager and a customer in the past days. The customer was looking for a definite statement that they can have a higher version of Patch Set Updates (PSUs) or Proactive Bundle Patches (BPs) in the Database homes than in the Grid Infrastructure home managing the resources.

Can you have different PSU/BP versions between Database and GI homes?

Yes, you can have a higher version PSU or BP in the Database home than in the Grid Infrastructure home managing the resources. This is implicitly documented in MOS Note 337737.1 – Oracle Clusterware (CRS/GI) – ASM – Database Version Compatibility where it says:

The Oracle Grid Infrastructure (GI) /Clusterware (CRS) version must be of the highest version down to the 4th digit in the possible combinations at all times.

Therefore you can deviate that this rule does not apply for the 5th digit anymore. Anil and I discussed that this may be documented a bit more clear – and he will take care.

Should you have different PSU/BP versions between Database and GI homes?

Well, this question is a bit harder. I can see why some customers have a higher patch level (5th digit) in the Database homes than in their GI homes. Actually some of my customers have this setup as well. GI is pretty stable above a certain patch level and often gets not treated with the same importance than the database homes as the need for patching in database homes is more obvious and visible. When it’s stable don’t touch it. And honestly speaking, patching is no fun – but hard work.

But we strongly recommend that you keep your patch levels in synch. If you apply the GI PSU of April 2017 then please apply the Database PSU or BP of April 2017 as well. These are the combinations we test internally. Please bear in mind that while the GI and DB are separate entities, they are tightly integrated and work cohesively. This way you mitigate the potential risk of an untested issue by having differences in the 5th digit.

And as a final hint:
Grid Infrastructure PSUs (and BPs) can be applied always in a rolling fashion causing no downtime. Just saying …

–Mike

 

DBMS_QOPATCH does not work in PDBs (right now)

Thanks to Murthy who commented on this blog post and Jeannette Holland (SimCorp) who opened an SR resulting in an ER.

DBMS_QOPATCH in Multitenant

DBMS_QOPATCH will deliver useful information about installed patches only when executed within the CDB$ROOT. It has been designed this way for security reasons in Oracle Database 12.1 but I can easily see a need to check for installed patches within a PDB as well.

Testcase

I “borrowed” this test case from Jeannette’s SR:

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

 NAME     CON_ID DBID       CON_UID    GUID
 -------- ------ ---------- ---------- ------------------
 CDB$ROOT      1 3424772713 1          47C8525C0DFE49...
 PDB$SEED      2 3983775695 3983775695 E6204BB1F6EB4F...
 MYPDB1        3 7270044002 7270044002 B975668B860049...
 MYPDB2        4 1943363979 1943363979 BCD7AAFAF3F641...

In a PDB:

ALTER SESSION SET container = myPDB;

Session altered.

SQL> select * from OPATCH_XML_INV ;
 ERROR:
 ORA-29913: error in executing ODCIEXTTABLEOPEN callout
 ORA-29400: data cartridge error
 KUP-04080: directory object OPATCH_LOG_DIR not found

no rows selected

SQL> select dbms_qopatch.get_opatch_install_info from dual;
 ERROR:
 ORA-20001: Latest xml inventory is not loaded into table
 ORA-06512: at "SYS.DBMS_QOPATCH", line 1986
 ORA-06512: at "SYS.DBMS_QOPATCH", line 133

In the CDB:

SQL> ALTER SESSION SET container = cdb$root;
Session altered.

SQL> select * from OPATCH_XML_INV ;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <InventoryInstance>

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-2d1c0910-36ac-429b-98db-96a353d423b6</UId><targetTyp

Solution

There’s no solution available right now for Oracle Database 12.1.0.2. And this behavior does not seem to be documented yet. The SR resulted in an (unpublished) Enhancement Request. In a PDB the following workaround may help in Oracle Database 12.1.0.2:

 select patch_id, patch_uid, version, action, action_time, status, description from dba_registry_sqlpatch;

But this is not as fancy and easy to deal with as an API call to a DBMS package.

I tested in Oracle Database 12.2.0.1 – and there everything seems to work fine there 🙂

SQL>
 create pluggable database PDB3 admin user adm identified by adm
 file_name_convert=( '/u02/oradata/CDB2/pdbseed',
 '/u02/oradata/CDB2/pdb3');

Pluggable database created.

SQL> alter pluggable database pdb3 open;
Pluggable database altered.

SQL> alter session set container=pdb3;
Session altered.

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-3cb04a3a-3999-4767-86f1-bc845cab158e</UId><targetTyp

SQL> select * from OPATCH_XML_INV ;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<InventoryInstance> <ora

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

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
--------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
-----------------------------------------

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB3                           READ WRITE NO

Cheers
–Mike

New SPFILE parameters in Oracle Database 12.1.0.2 with July 2016 (and newer) PSU/BP

New Parameters in Oracle Database 12.1.0.2 with July 2016 PSU/BP

By following an internal discussion and checking parameter changes between Patch Set Updates (PSU) and Proactive Bundle Patches (BP) I learned that we introduced two new SPFILE parameters in Oracle Database 12.1.0.2 with the July PSU and BP. One is documented in the patch readme, the other one can be found right now only in the Oracle Database 12.2.0.1 manual:

The Oracle 12.2 documentation about ALLOW_GROUP_ACCESS_TO_SGA, the parameter which appears not in the Oracle 12.1 documentation right now, says:

ALLOW_GROUP_ACCESS_TO_SGA controls group access to shared memory on UNIX platforms.

The default value is FALSE, which means that database shared memory is created with owner access only. In Oracle Database releases prior to Oracle Database 12c Release 2 (12.2.0.1), database shared memory was created with owner and group access.

When this parameter is set to TRUE, database shared memory is created with owner and group access. This behavior grants permissions to DBAs to manage shared memory outside the database, but also allows DBAs to read and write to shared memory, which may not be desirable for certain installations.

So there’s a tiny correction required:
It should say “prior to Oracle Database 12.1.0.2 July 2016 PSU/BP”.

The ENCRYPT_NEW_TABLESPACES parameter came in for the cloud deployments and is documented in the description
of the July Proactive BP
:

12.1.0.2.DBBP:160719 21281607E Transparently encrypt tablespace at creation in Cloud (adds “encrypt_new_tablespaces”)

 

Why does ALLOW_GROUP_ACCESS_TO_SGA appear in Oracle Database 12.1.0.2?

Simple reasons: it will make Oracle Database 12.1.0.2 more secure. Default for this parameter is FALSE – which actually changes behavior but may not affect you at first sight. And that’s why I blog about it.

You will recognize that the Oracle executable runs now with permission “600” – whereas it was “640” before. See my example of an 12.1.0.2 database with the January 2017 BP in place:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 23298063   oracle     600        2932736    76                      
0x00000000 23330832   oracle     600        1040187392 38                      
0x00000000 23363601   oracle     600        5455872    38                      
0xc8969114 23396370   oracle     600        20480      38       
...

whereas my 11.2.0.4 database runs with different permissions:

$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status     
0x00000000 22872084   oracle     640        12582912   21                      
0x00000000 22904853   oracle     640        721420288  21                      
0xd41b1c5c 22937622   oracle     640        2097152    21 
...

Does this effect the connection of your applications to the database?
No, of course not.

It has only an effect if you try to access the SGA from the OS level, i.e. attaching to the shared memory segment. In the old behavior an OS user being in the same group can attach and read from the SGA. With the new “600” protection only the OWNER can attach to it – and read out the SGA.

This is the standard behavior in Oracle Database 12.2.0.1 and onward. And it has been backported to the July 2016 PSU and Proactive Bundle Patches which are cumulative, i.e it is in all following PSUs and BPs included as well.

–Mike

DBA_REGISTRY_HISTORY vs DBA_REGISTRY_SQLPATCH

At the DOAG Conference in November in Nürnberg in November 2016 a customer asked me right after my talk about “Upgrade to Oracle Database 12.2. – Live and Uncensored” why the DBA_REGISTRY_HISTORY does not get updated when he applies a Bundle Patch and follows all instructions including the “./datapatch -verbose” call.

I was wondering as well and asked him to open an SR. Which he did. And he received the message from Support that it is not supposed to appear in Oracle 12c anymore this way but only in DBA_REGISTRY_SQLPATCH. Now I dug a bit deeper internally to get a clear statement (thanks to Carol (my boss) and Rae (my teammate) and Scott (the man who owns datapatch) for following up!).

Patch Query in Oracle Database 11g

Tim Hall has published this simple and quite helpful script to query applied PSUs and BPs in Oracle Database 11g:
Script to monitor DBA_REGISTRY_HISTORY

And the output in my environment looked like this:

ACTION_TIME           ACTION  NAMESPE VERSION  ID      COMMENTS             BUN
 -------------------- ------- ------- -------- ------- -------------------- ---
 01-JUL-2016 15:24:56 APPLY   SERVER  11.2.0.4 160419  PSU 11.2.0.4.160419  PSU
 21-OCT-2016 17:40:32 APPLY   SERVER  11.2.0.4 161018  PSU 11.2.0.4.161018  PSU

But running the same script on Oracle Database 12.1.0.2 returnes (as for the customer) “no rows selected“.

Patch Query for Oracle Database 12c

Since Oracle Database 12.1.0.1 we use DBA_REGISTRY_SQLPATCH instead of DBA_REGISTRY_HISTORY to track PSUs and BPs applied to the database. I used this script: check_patches.sql.

My output in Oracle Database 12.1.0.2 looks like this:

ACTION_TIME          ACTION  STATUS   DESCRIPTION          VERSION  PATCH_ID BUND
-------------------- ------- -------- -------------------- -------- -------- ----
21-OCT-2016 17:29:36 APPLY   SUCCESS  DBP: 12.1.0.2.161018 12.1.0.2 24340679 DBBP

when using this tiny script:

SET LINESIZE 400
SET PAGESIZE 100
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
 action,
 status,
 description,
 version,
 patch_id,
 bundle_series
 FROM   sys.dba_registry_sqlpatch
 ORDER by action_time;

But the question remains if – as in Oracle Database 12.1.0.1 – both views should get updated.

Explanation

In 11.2.0.4, we used the script catbundle.sql to apply bundle patches.  It uses DBA_REGISTRY_HISTORY only.  For 12.1.0.1 with the introduction of datapatch, we now have the (much better) DBA_REGISTRY_SQLPATCH.  This is used for both, bundle and non-bundle patches.  In Oracle Database 12.1.0.1. for bundle patches we actually called catbundle internally, so in 12.1.0.1 both registries were updated for bundle patches.
Starting in 12.1.0.2, however, only DBA_REGISTRY_SQLPATCH is queried and updated for bundle and non
bundle patches.

Update [Dec 23, 2016]

After discussing this and other issues with the owners of datapatch my teammate Rae logged a bug for this issue as we believe both views should be updated as it happened in 12.1.0.1. Bug# 25269268 tracks the issue.

–Mike

October 2016 Proactive BP got replaced

Just received a message from Oracle Support this early morning as I did install the Proactive Bundle Patch from October 2016 into my Oracle Database 12.1.0.2 environment saying:

Dear Oracle Customer,

You are receiving this email because our recordsindicate you downloaded the following patch:

Patch number: 24448103
Release: DB Proactive Bundle 12.1.0.2.161018
Platform: Linux x86-64

This patch has been replaced and is now available for download. Please review section 1.1 of the
following My Oracle Support note for further technical details and instructions:

Note: 2171506.1 – Oracle Database Proactive Patch 12.1.0.2.161018 Known Issues

Issue found:

SCAN Listener or local listener fails to start

The symptom of failed to start SCAN listener resource happens in environments that have been upgraded from 11.2 to 12.1.

The Oct2016 Proactive Bundle Patch 12.1.0.2.161018 Patch 24448103 has been uploaded again with a fix for this issue as of 30-Oct-2016 8am PST.

The My Oracle Support Note: 2166451.1
– “SCAN Listener or local listener fails to start after applying Patch
23273629 – Oracle Grid Infrastructure Patch Set Update 12.1.0.2.160719
(Jul2016) or Oct DB BP patch 24448103” has more information

It didn’t affect me as I don’t have the SCAN listener in my environments. But you should be aware of this.

–Mike

October 2016 PSU and BP – Database Patching?

What will you get when you download the most recent Oracle Database PSU or BP from October 2016?

MOS Note: 1683799.1 – 12.1.0.2 Patch Set – Availability and Known Issues is not entirely clear. Therefore lets shed some light …

The Matrix

This matrix in MOS Note: 1683799.1 tells you about the availability of PSUs and BPs for a regular database installation (non-RAC, non-Exadata). But it doesn’t clearly tell you what’s included – and the names being used aren’t very revealing either.

Non Exadata Non RAC

Document Description Rolling RAC Patch Download
Note:24448103.8 12.1.0.2.161018 Database Proactive Bundle Patch (Oct 2016) Yes Patch:24448103
Note:24436306.8 Combo of 12.1.0.2.161018 OJVM PSU and 12.1.0.2.161018 DBBP (Oct 2016) Part Patch:24436306
Note:24433133.8 Combo of 12.1.0.2.161018 OJVM PSU and 12.1.0.2.161018 DB PSU (Oct 2016) Part Patch:24433133
Note:24315824.8 Oracle JavaVM Component 12.1.0.2.161018 Database PSU (Oct 2016) (OJVM PSU) No Patch:24315824
Note:24006101.8 12.1.0.2.161018 (Oct 2016) Database Patch Set Update (DB PSU) Yes Patch:24006101

My Matrix

I translate this into:

Non Exadata Non RAC

Document Description Rolling RAC Patch Download DB PSU DB BP GI PSU OJVM
Note:24448103.8 12.1.0.2.161018 Database Proactive Bundle Patch (Oct 2016) Yes Patch:24448103 X X
Note:24436306.8 Combo of 12.1.0.2.161018 OJVM PSU and 12.1.0.2.161018 DBBP (Oct 2016) Part Patch:24436306 X X X
Note:24433133.8 Combo of 12.1.0.2.161018 OJVM PSU and 12.1.0.2.161018 DB PSU (Oct 2016) Part Patch:24433133 X X
Note:24315824.8 Oracle JavaVM Component 12.1.0.2.161018 Database PSU (Oct 2016) (OJVM PSU) No Patch:24315824 X
Note:24006101.8 12.1.0.2.161018 (Oct 2016) Database Patch Set Update (DB PSU) Yes Patch:24006101 X

How to apply a Proactive Bundle Patch?

If you’ve never done it before, applying a Proactive Bundle Patch to a database-only installation is not very complicated. Please see my own step-by-step instructions here (but don’t forget to check the current readme as well please!).

More Information:

–Mike

October 2016 PSU and Proactive BP are available

When the leafs are falling down …

… then it’s time for the October 2016 Patch Set Update (PSU) and Proactive Bundle Patches (BP).

Things you need to know:

Interesting information:

  • Newly scheduled final patches:

    • Oracle Database 12.1.0.2 – July 2021 (See MOS Note 742060.1)
    • Oracle Database 11.2.0.4 – October 2020 (See MOS Note 742060.1)

I’ll update you as soon as I applied the BP to my 12.1.0.2 and the PSU to my 11.2.0.4 environment.

–Mike

Can I apply a BP on top of a PSU? Or vice versa? PART 2

I thought I won’t blog about this again:

Can I apply a BP on top of a PSU? Or vice versa?

But then a colleague of mine raised this simple question:

  • “I have a customer that would like to change from patching using PSU to patching using Bundle Patch. I am
    wondering what happens if my home has had several PSUs installed. Before applying a BP, would I need to rollback one by one all the PSUs that have been installed in reverse order (tedious) OR only the latest PSU (good)?”

Unfortunately the “simple” solution is hidden deep down in the documentation and not mentioned (as far as I could see) in any MOS Note.

The secret is the opatch option “all_subpatches“.

And this is the solution

  • All PSU sub-patches have to be rolled back
  • Use the -all_subpatches option – it will roll them all back in the correct sequence.
    • Note: all overlay patches need rolled back at first
  • Example:
    % opatch rollback –id  -all_subpatches
  • Documentation says:
    “This option is valid ONLY for composite patches. It allows the user to rollback all sub-patches of a composite series in one shot.”

Hope this helps here and there …

–Mike