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

Use the correct download to patch APEX from 5.1.0 to 5.1.1

When you plan to upgrade Oracle Application Express (APEX) from version 5.1.0 to 5.1.1 please be aware to download a patch from MOS instead the 5.1.1 version from OTN.

The text on the OTN page is a bit misleading – and a customer I work with downloaded the wrong version which led to a failed upgrade – even though the scripts seem to be made for this patch upgrade.

APEX 5.1.1 Full Version Download

Oracle APEX 5.1.1 download on OTN

Even though the page says “This is a cumulative patch set for Application Express 5.1.0” the page offers you a full install and not the patch for an existing APEX 5.1.0 installation.

See the message in the middle of the page starting with “For existing Application Express 5.1.0 installations, please download … from My Oracle Support.”

The patch you’ll need is available via this download link: patch 25341386

–Mike

Enabling ADAPTIVE Features of Oracle 12.2 in 12.1

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS.

For more information please see:

But Oracle Database 12.2 on-premises is not out yet – so what
should you do when upgrading to Oracle Database 12.1 – or struggling
with some of the “adaptive” features in Oracle 12.1?

It recommends to adopt the Oracle Database 12.2 defaults when
upgrading to Oracle Database 12.1. This can be achieved by installing
two patches – we call it the recommended approach.

    • The patch for bug# 22652097 introduces the two parameters OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS, and in addition removes the parameter OPTIMIZER_ADAPTIVE_FEATURES.
    • The patch for bug# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.

Please make sure you’ll remove OPTIMIZER_ADAPTIVE_FEATURES from your spfile:

alter system reset optimizer_adaptive_features;

when applying the patches.

Both patches should help as well once you have upgraded already to Oracle Database 12.1 but encountering performance issues.

Please note that it’s not necessary to set OPTIMIZER_DYNAMIC_SAMPLING to a
non-default value because the patches will disable the use of adaptive dynamic sampling
to match the default behavior in Oracle Database 12.2 when both new parameters are used in their default settings.

Further Information:

–Mike

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:

  • OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
  • OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default

Nigel Bayliss, our “Optimizer” Product Manager has blogged about it already with way more detailed insights.

But as Oracle Database 12.2 on-premises is not available how should you deal with this feature in Oracle Database 12.1?

Enabling ADAPTIVE Features of Oracle 12.2 in 12.1

 

 

–Mike
.

RMAN Recovery Catalog with July PSU: How to resolve RASCHEMAVER and BASCHEMAVER schema mismatch

Related blog posts:


Thanks to Ah Huat Tan from Amway IT Services for keeping me updated!
Actually as I see that more people who got hit by this issue so I’d consider it to be worth to write about it.

Problem and Analysis

You’d apply the July 2016 PSU or BP. According to the readme you are required to upgrade your RMAN catalog afterwards. The readme says:

If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it:

$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;

I think there’s a second “upgrade catalog” command necessary but I may be wrong.
Anyhow, if you do this after applying the July 2016 PSU or BP you may see the following error:

RMAN>
RMAN-06435: recovery catalog owner is CATALOG5
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN>
RMAN-06444: error creating dbms_rcvcat package body
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06443: error upgrading recovery catalog

When you try now to recompile the package manually you’ll get:

    SYS@CATDB> alter package catalog5.DBMS_RCVCAT compile body;

    Warning: Package Body altered with compilation errors.

    SYS@CATDB> show error
    Errors for PACKAGE BODY CATALOG5.DBMS_RCVCAT:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    11328/12 PL/SQL: Item ignored
    11328/12 PLS-00201: identifier 'BASCHEMAVER.VERSION' must be declared
    11340/6  PL/SQL: SQL Statement ignored
    11342/13 PL/SQL: ORA-00942: table or view does not exist
    11345/9  PL/SQL: Statement ignored
    11345/9  PLS-00320: the declaration of the type of this expression is incomplete or malformed

    11348/3  PL/SQL: Statement ignored
    11348/7  PLS-00320: the declaration of the type of this expression is incomplete or malformed

    11353/3  PL/SQL: Statement ignored
    11353/54 PLS-00320: the declaration of the type of this expression is incomplete or malformed

Explanation

This got caused by a wrong library being shipped wrongly with the July PSU and BP meant for ZDLRA only.

Solution

Please apply patch for Bug 24461826RMAN-06443: ERROR UPGRADING RECOVERY CATALOG AFTER APPLYING PATCH. The bug is non-public and therefore not visible.

Sorry for the inconvenience.

–Mike

OJVM patch: Standby-First patching, yes or no?

I have blogged in the past more than once about the “wonderful” topic of OJVM patching.

Anyhow,  I’d like to summarize a few typical questions sent to me in the past months regarding OJVM patching.

Do I need to apply the OJVM patch every time I apply a PSU or BP?

Unfortunately this is not a one-time-and-then-you-are-set operation. You’ll have to do it every time.

How do I find out if OJVM is used in my database?

I tried to nail this down in this blog post here:

Java in the database – OJVM non-rolling patches – OJVM Removal

But the best solution would be to not install OJVM from the beginning if you have no intention of using it. It’s far more simple to install it later on than to remove it.

Can OJVM patch being rolling installable or standby-first applicable?

No, unfortunately the OJVM patch is neither rolling installable in a RAC database nor can it be applied in the Standby-First patching method.
Please see: MOS Note:1929745.1: Oracle Recommended Patches — OJVM PSU Patches

Is the Mitigation Patch a valid workaround to OJVM patching downtime?

Well, I leave this up to you. But if you have never heard of the OJVM Mitigation patch before please see again MOS Note:1929745.1 – Oracle Recommended Patches — OJVM PSU Patches:

For situations where the latest OJVM PSU cannot be installed immediately there is a “Mitigation Patch” that can be used. The “Mitigation Patch” is an interim solution to protect against all currently known (Jul 2015) Oracle JavaVM security vulnerabilities in the database until such time as the OJVM PSU can be installed. It can also be used to protect database versions no longer covered by error correction support.

The “Mitigation Patch”:

  • is applicable only to database homes, not client nor Grid homes
  • is only applicable to databases that have JavaVM installed
  • has no dependency on the DB PSU (or equivalent) level
  • can be installed in a RAC Rolling manner
  • is a SQL only patch that needs to be installed and activated in each database
    • hence it can be installed standby first but it requires SQL steps to be executed to be effective, which cannot be done on a read only standby
  • affects use of Java and Java development in the database
  • has been reviewed for January 2015, April 2015, July
    2015, October 2015, January 2016, April 2016 and July 2016 and provides
    mitigation against all currently known OJVM vulnerabilities
  • can be downloaded here: Patch:19721304

Does OJVM Patching affect the Grid Infrastructure Rolling Patching?

No, it doesn’t. Oracle Grid Infrastructure patching is always rolling and does not get affected by the OJVM patch.

I’m pretty sure this does not answer all your questions but please don’t hesitate to open SRs with Oracle Support. I will update this FAQ from time to time.

–Mike

PDB unplug/plug/patch with PSUs or BPs

This question was posted on the blog a few days ago referring to my previous blog posts describing the two techniques to upgrade in an Oracle Multitenant environment:

Question: 

We are planning to upgrade from 12.1.0.2.1 to 12.1.0.2.5.
The plan is to create new 12.1.0.2.5 oracle home and
gradually moving PDB from 12.1.0.2.1 to 12.1.0.2.5.
Should I follow the same steps?
Is there any document for what we want to do?

And luckily there is a MOS Note out there describing the steps:

The part this note is not talking about is the unplug/plugin operations in detail.
But this is something you’ll find here:

Upgrade PDBs – One at a Time (unplug/plug)

–Mike

Speed up Upgrade Phase 65 with a new catuposb.sql

Credits to Chris Smids from Proximus in Belgium 🙂 Thanks, Chris!!!


Upgrade to Oracle 12.1.0.2 is slow in phase: #65 ?

You are wondering why phase: #65 of the database upgrade to Oracle Database 12.1.0.2 takes quite a while. You dig down into the catupgrd0.log and recognized this statement taking a while:

dbms_output.put_line('catuposb, update 4 - rows updated ' || rows_updated);
END; -- end of update for system internally generated objs
/

The cause for this issue is buried in the script catuposb.sql hitting stale histograms which did not get refreshed even if you gathered dictionary stats before the upgrade as recommended.

Offered Workarounds

Important things to know

  • Bug 21744290 – catuposb.sql can be slow during upgrade depending on no. of objects in the DB
    is supposed to be fixed in the April 2016 BPs and PSU – but in Chris’ case Oracle Support confirmed that even though Chris had the BP the issue still happens. And this observation is correct. The fix from December 2015 is missing in the catuposb.sql distributed with the April 2016 PSU and BP.When you compare it now with the version Oracle Support is distributing you’ll find the fix being included – but the two more recent fixes in the current catuposb.sql (see above) are missing.
    We’ll sort this out and I will update you via this blog post.
  • These are the lines missing in the version of catuposb.sql distributed with the April 2016 PSU/BP:
    -- bug 22178855: gather table stats on user$ after update to avoid slow
    -- select in update 4
    dbms_stats.gather_table_stats('SYS', 'USER$');
    

What should you do?

Actually it is not supported to edit “our” scripts in ?/rdbms/admin so please wait for a clean version of catuposb.sql being distributed. I will update the blog asap. In between I think it should be ok to gather stats on USER$ beforehand in order to prevent this from happening:

SQL> exec dbms_stats.gather_table_stats('SYS', 'USER$');

Or you’ll wait for the July 2016 Proactive BP and PSU which should be available on July 19, 2016. It will contain the correct version of the script including all fixes.

–Mike

RMAN Catalog Upgrade fails – ORA-02296 – error creating modify_ts_pdbinc_key_not_null

This issue got raised to my via a customer I know for quite a while – all credits go to Andy Kielhorn for digging down into that issue and solving it.

Failed RMAN Catalog Upgrade from 11.2.0.4 to 12.1.0.2

The RMAN catalog upgrade:

SQL> @?/rdbms/admin/dbmsrmansys.sql

$ rman CATALOG rman/xxx@rman01

RMAN> UPGRADE CATALOG; 

RMAN> UPGRADE CATALOG;

failed with the following sequence of error messages:

error creating modify_ts_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_tsatt_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_df_pdbinc_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_tf_pdb_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

error creating modify_bs_pdb_key_not_null
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02296: cannot enable (RMAN.) - null values found

Andy found also these bugs in MOS – but no helpful information included:

  • Bug 20861957
    ORA-2296 DURING UPGRADE CATALOG TO 12.1,0.1 IN AN
    11.2 DATABASE
  • Bug 19677999
    CATALOG SCHEMA UPGRADE TO 12.1.0.2 FAILED

The  Solution

There seems to be a potential inconsistency in the RMAN catalog when the PDB/CDB mechanisms get introduced. This does not necessarily happen – but it can happen.

The workaround is described in:

  • Bug 19677999 : CATALOG SCHEMA UPGRADE TO 12.1.0.2 FAILED

==> Connect to catalog schema and clear the table having null details

delete bdf
where not exists (select 1 from dbinc where dbinc.dbinc_key = bdf.dbinc_key);
delete bcf where not exists (select 1 from dbinc where dbinc.dbinc_key = bcf.dbinc_key);
commit;

==> After clearing the offending rows , upgrade catalog worked

But please use this workaround only under Oracle Support’s supervision. I did document it here to ease your verification.

Andy fixed it with:

    update [rmancat_owner].dbinc set PARENT_DBINC_KEY=NULL
    where (DBINC_KEY) IN
       (SELECT DBINC_KEY  from [rmancat_owner].ts
        where pdbinc_key is null);
    commit;

but please open an SR and point Oracle Support to the bug and the potential workarounds in case you hit the issue.

–Mike