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
A request for inclusion into the next Bundle Patches had been filed as well.
–Mike
Hi Mike,
I had applied this patch (17325413) prior to upgrade and applied this patch (20880215) as well on 12c home.But i still facing this issue
Hi Mike,
I’m still facing this issue, applied this patch (17325413) prior to upgrade and 20880215 on 12c home.
Tan,
please kindly log an SR with Support to let them check. Please understand that I can diagnose your issue.
Cheers
Mike