Issue with 2k db_block_size – ORA-1450 when upgrading from Oracle 12.1 to Oracle 12.2.0.1

Recently we got alerted by a customer and a colleague from Italy about an issue with the upgrade from Oracle 12.1.0.x to Oracle 12.2.0.1 if – and only if – your database got created with 2k block size.

Problem

If your database got created with 2k block size, and you attempt an upgrade from Oracle 12.1.0.x to Oracle 12.2.0.1 the upgrade – regardless of DBUA or catctl.pl – will fail with an ORA-1450: maximum key length (1478) exceeded.

Analysis

In the catupgrd0.log – regardless of using the DBUA or the command line upgrade with catctl.pl – you’ll get the following error pattern:

SQL> create unique index i_radm_pe1 on sys.radm_pe$(pe_name)
   2  /
create unique index i_radm_pe1 on sys.radm_pe$(pe_name)
                                      *
ERROR at line 1:
ORA-01450: maximum key length (1478) exceeded

SQL> create index i_radm_pe2 on sys.radm_pe$(pe_obj#, pe_name)
  2  /
create index i_radm_pe2 on sys.radm_pe$(pe_obj#, pe_name)
                               *
ERROR at line 1:
ORA-01450: maximum key length (1478) exceeded

Solution

Please apply the one-off patch 24714096/25976885 (backport on top of 12.2.0.1 for bug 24714096 (HIT ORA-01450 WHEN UPGRADE SI DB FROM 11204 TO 12.2 WITH DB_BLOCK_SIZE 2K).

You may verify your current database block size with this queries:

SQL> show parameter db_block_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_block_size			     integer	 8192


SQL> select TABLESPACE_NAME, BLOCK_SIZE from DBA_TABLESPACES order by 2;

TABLESPACE_NAME 	       BLOCK_SIZE
------------------------------ ----------
SYSTEM				     8192
SYSAUX				     8192
USERS				     8192
TEMP				     8192
UNDOTBS1			     8192

Sorry for the inconvenience – and thanks to Francesco for logging the issue and to Alessandro for alerting our team.

–Mike

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

 

The OJVM Patching Saga – and how to solve it – Part V

Related Posts on
The OJVM Patching Saga – and how to solve it“:

MOS Note released explaining patching without STARTUP UPGRADE mode

An important note regarding OJVM rolling patching has been published on MyOracle Support:

MOS Note: 2217053.1
RAC Rolling Install Process for the “Oracle JavaVM Component Database PSU” (OJVM PSU) Patches

“Beginning with the Jan2017 OJVM PSU patchset for 11.2.0.4 and for 12.1.0.2, this document defines a few specific situations where the OJVM PSU patchset can be postinstalled into each database while the database remains in unrestricted “startup” mode. This will allow a “Conditional Rolling Install” ability for the OJVM PSU patchsets for 11.2.0.4 and for 12.1.0.2″

This document applies to the 11.2.0.4 and to the 12.1.0.2 Jan 2017 OJVM PSU.

PLEASE NOTE:

The PSU’s readme still says that the OJVM PSU is not RAC Rolling installable. This is not correct but as the PSU got released before the note got finalized the information did not get updated. The information gets added to the Known Issues section and eventually a new readme may be released.

–Mike

The OJVM Patching Saga – and how to solve it – Part IV

Related Posts on
The OJVM Patching Saga – and how to solve it“:

What is missing with the “Mitigation Patch?

Michael, the team lead from a large Oracle customer mailed me the other day. He wanted to disable Java on all their +600 databases by using the Mitigation Patch for OJVM. But he received a strange error when trying to enable the mitigation patch which is clearly included in the April 2016 PSU he is using on his Oracle 12.1.0.2 databases:

SQL>  exec dbms_java_dev.disable
BEGIN dbms_java_dev.disable; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_JAVA_DEV.DISABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

MOS didn’t reveal an immediate solution. And we’ve exchanged some emails detecting some issues with one-offs applied only on one cluster node. But actually that was not the root cause for the mitigation patch not allowing to disable java.

We did check if the SQL changes from the April 2016 PSU really had been applied with the script I published here:

DBA_REGISTRY_HISTORY vs DBA_REGISTRY_SQLPATCH

But the results were ok – datapatch had been executed in the database:

SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A60
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;

ACTION_TIME          ACTION   STATUS  DESCRIPTION                                            VERSION  PATCH_ID BUN
-------------------- -------- ------- ------------------------------------------------------ -------- -------- ---
02-MAR-2016 08:59:35 APPLY    SUCCESS Database Patch Set Update : 12.1.0.2.5 (21359755)      12.1.0.2 21359755 PSU
02-MAY-2016 09:44:45 APPLY    SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127) 12.1.0.2 22291127 PSU
13-MAY-2016 12:52:01 ROLLBACK SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127) 12.1.0.2 22291127 PSU
17-MAY-2016 11:16:56 APPLY    SUCCESS Database Patch Set Update : 12.1.0.2.160419 (22291127) 12.1.0.2 22291127 PSU

The Solution

Finally Michael found the solution.
This tiny little script didn’t get run: dbmsjdev.sql

It only gets mentioned in the PSU’s and BPs READMEs and easily can be overlooked.
Furthermore I couldn’t find it mentioned in any other MOS note.

And I missed it on my previous blog post as well and added it now.

Once you executed dbmsjdev.sql then you can disable Java by using:

SQL> exec dbms_java_dev.disable
PL/SQL procedure successfully completed.

Further information

–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

Where is the Jan 2017 PSU for Oracle Database 11.2.0.4?

Where is it?

This question reached me a week ago:
Where is the January 2017 PSU for Oracle Database 11.2.0.4?

What’s the deal?” was my first thought – just go and download it from MOS.
Well, not really …

Where to download Proactive Bundle Patches, Patch Set Updates and Patch Sets?

My favorite note is:
MOS Note:1454618.1
Quick Reference to Patch Numbers for PSU, SPU(CPU), BPs and
Patchsets

It gives me access to all the download links for the patch bundles I’m looking for.

But for 11.2.0.4. there’s only “NA” (not available) stated:

And a very small asterisk “m”:

Why is there no January 2017 PSU for Oracle Database 11.2.0.4?

The comment says it all – but I was looking for a more precise explanation. And Roy knew where to look at (thanks!):

  • MOS Note: 854428.1
    Patch Set Updates for Oracle Products

    Section 3 (Patch Sets Updates Lifecycle) says:

    • PSUs will reach a plateau of non-security content as the version stabilizes. Once this is reached, there will be a tapering off of non-security content. Oracle expects this plateau to be reached with the third or fourth PSU.
    • PSU content will be primarily security-related once the next patch set in the series is released.

So yes, this is possible and somehow documented.
As there are no Security Fixes for Oracle Database 11.2.0.4 this quarter there are no SPUs or PSU released.

Further Information?

Please see the blog post of Rodrigo Jorge from Enkitec with more details:

Obrigado, Rodrigo!

–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