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 22.214.171.124 if – and only if – your database got created with 2k block size.
If your database got created with 2k block size, and you attempt an upgrade from Oracle 12.1.0.x to Oracle 126.96.36.199 the upgrade – regardless of DBUA or catctl.pl – will fail with an ORA-1450: maximum key length (1478) exceeded.
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)
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)
create index i_radm_pe2 on sys.radm_pe$(pe_obj#, pe_name)
ERROR at line 1:
ORA-01450: maximum key length (1478) exceeded
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;
Sorry for the inconvenience – and thanks to Francesco for logging the issue and to Alessandro for alerting our team.
All credits go to Ricardo Maeda as I knew that we’ll release a Bundle Patch for Oracle 188.8.131.52 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 184.108.40.206. There will be a first bigger Proactive Bundle Patch in July at the usual schedule – but this one is at least a start.
Patch 2579308 – Oracle 220.127.116.11.170516BP
Plus in addition get the OPatch version 18.104.22.168.7 via patch 6880880.
The BP contains:
First 22.214.171.124 Bundle Patch – Contents: Database BP and GI PSU
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 removeOPTIMIZER_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.
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-06435: recovery catalog owner is CATALOG5
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN-06444: error creating dbms_rcvcat package body
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
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:
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
This got caused by a wrong library being shipped wrongly with the July PSU and BP meant for ZDLRA only.
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
We are planning to upgrade from 126.96.36.199.1 to 188.8.131.52.5.
The plan is to create new 184.108.40.206.5 oracle home and
gradually moving PDB from 220.127.116.11.1 to 18.104.22.168.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:
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
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: