Ouch, this hurts: bug 20880215 – patch Oracle 12c

Just realized that my preivous blog post heading was misleading: it said “patch AFTER upgrade” which is misleading. You should apply this patch BEFORE you upgrade but of course to your new/future Oracle 12c home. If you hit the issue then apply the patch after upgrading to remedy it. Sorry for the confusion.

No updates for over a week? Sorry for that but too many workshops, swamped with OOW prep work – and a wonderful issue with the browser cache and the blog software putting all my new blog post work directly into /dev/null.

Thanks to Marcel Paul for highlighting this issue to me. I really benefit from such emails as I can learn a lot from those – and furthermore distribute information to many other tech folks as well.

Actually Marcel updated me as a follow up to me recent blog post:

describing an issue which will require to apply a patch before upgrade if you are not on Oracle 11.2.0.4.2 or higher – and the below issue is related to this one but requires a patch AFTER upgrade, regardless what your source version was.

Issue

Marcel let me know about an issue they saw with their upgrade from Oracle 11.2.0.3 to Oracle 12.1.0.2 after the upgrade causing a core dump and potential table metadata corruption when you’ll ADD a column to a table with a DEFAULT and ENABLE NOVALIDATE. This issue gets treated as bug 20880215 (ORA-7445 [QCSISCOLINFRO()+358] FOR ADD COLUMN WITH DEFAULT AND ENABLE NOVALIDATE)

Test Case

Very simple test case done by Marcel (thanks again!) – I could verify it within 30 seconds.

create table dummy(code varchar2(5), text varchar2(30));
insert into dummy(code, text) values ('CD1','Hello World');
commit;
select * from dummy;
alter table dummy ADD condition varchar2(3) DEFAULT 'YES' not null enable novalidate;
select * from dummy;

Result

First of all the client (in my case SQL*Plus) will disconnect with the meaningless ORA-3113. So lets have a look into the alert.log for more useful information:

Wed Oct 14 15:06:59 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0xCDB0046, qcsIsColInFro()+358] [flags: 0x0, count: 1]
Errors in file /oradata/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_2424.trc  (incident=3433) (PDBNAME=CDB$ROOT):
ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB0046] [Address not mapped to object] []
Incident details in: /oradata/diag/rdbms/cdb2/CDB2/incident/incdir_3433/CDB2_ora_2424_i3433.trc

Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

Wed Oct 14 15:07:08 2015
Dumping diagnostic data in directory=[cdmp_20151014150708], requested by (instance=1, osid=2424), summary=[incident=3433].

So we’ve got a core dump. And whatever we query now from the table involving the column “condition” we’ll get the same error.

Solution

Apply the fix for bug 20880215 (ORA-7445 [QCSISCOLINFRO+358] FOR ADD COLUMN WITH DEFAULT
AND ENABLE NOVALIDATE). You may have to request the fix for your platform if it hasn’t been done yet.

Further Information

• Bug 20880215 
  ORA-7445 [QCSISCOLINFRO+358] FOR ADD COLUMN WITH DEFAULT AND ENABLE NOVALIDATE
• Patch 20880215 for Intel Solaris x86
  No other one off patches available at the moment.
• See only the PLEASE NOTE section at the end of
  MOS Note:2017572.1
  ORA-7445 [qcsIsColInFro] Querying After Upgrade to 12c