By Mike Dietrich-Oracle on Aug 18, 2015
I really don't want to turn this blog into something making our database look bad. But in this case it is really necessary as it is VERY UNUSUAL that we recommend to patch the database BEFORE upgrade.
Just for clarification:
The following topic will affect databases 22.214.171.124.9+, 126.96.36.199.0 and 188.8.131.52.1 only - those (and only those) need to be patched BEFORE upgrade. The topic is fixed in 184.108.40.206 but as it gets introduced with the BEFORE upgrade database version you'll have to apply the fix before upgrade. The inclusion of the fix in 220.127.116.11 means only that the misbehavior won't happen there again. But as it is a meta data dictionary corruption you'll have to apply the fix before as otherwise it will break during or after the upgrade.
First of all, thanks to Ehtiram Hasanov (cleverbridge AG) and Oliver Pyka (http://www.pyka.de/) for highlighting this to me. And sorry for hitting this issue ...
After upgrading to Oracle Database 18.104.22.168 you'll get one of the below errors when trying to read data:
- ORA-07445: exception encountered: core dump [qcsIsColInFro()+358] [SIGSEGV] [ADDR:0x4] [PC:0xCDB4A26] [Address not mapped to object] 
- ORA-12899 / ORA-607
- ORA-600 [kdmv_check_row_2:IMCU row has wrong contents]
- ORA-600 [kddummy_blkchk]
- ORA-600 [kdBlkCheckError]
- ORA-600 [klaprs_12]
- ORA-600 
- ORA-600 
Basically this happens when you try to drop a column with a DEFAULT value and a NOT NULL definition - it ends up with dropped column data being written to disk leading to block corruptions. This causes problems for generating undo which cannot be applied; a ROLLBACK fails.
If you need more information please look up this MOS Note about
Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption
Versions being affected:
- These versions require to be patched BEFORE upgrade:
- Oracle Database 22.214.171.124.9 and above (may happen with earlier PSUs as well)
Solution: Apply the fix 17325413 on top - see below
- Oracle Database 126.96.36.199.0 and 188.8.131.52.1
Solution: Apply the most recent PSU
- These versions can get you the issue if you haven't patched BEFORE upgrade:
- Oracle Database 184.108.40.206
- Oracle Database 220.127.116.11
Workaround and/or Fix:
The MOS Note about Bug 17325413 - Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption explains the workaround WHEN you hit this issues.
As a precaution you will have to make sure that you applied one of those fixes BEFORE upgrading to Oracle Database 18.104.22.168. as the fix for Bug 17325413 is included in all those mentioned below (list is taken from above MOS Note as well).
- 22.214.171.124 (Server Patch Set)
- 126.96.36.199.3 Database Patch Set Update
- 188.8.131.52.2 Database Patch Set Update
- 184.108.40.206 Bundle Patch 4 for Exadata Database
- 220.127.116.11 Bundle Patch 23 for Exadata Database
- 18.104.22.168 Patch 7 on Windows Platforms
- 22.214.171.124 Patch 1 on Windows Platforms
- 126.96.36.199 Patch 29 on Windows Platforms
- Patch 17325413 is available only for 188.8.131.52 psu 9 and psu 12 but based on Oracle Support research (and customer's experience - see below) it should work also on higher PSU levels for Oracle Database 184.108.40.206.
- There's a support note out there offering a solution once you hit the issues:
MOS Note: 2017572.1 - ORA-7445 [qcsIsColInFro] Querying After Upgrade to 12c
But based on Ehtiram's experience this is not a complete solution and requires additional work and testing.
Let me quote Ehtiram:
"Yes, the ‘uncomplete’ bug fix(patch#17325413) was only available up to PSU 12 for 220.127.116.11, but we already had 18.104.22.168+PSU 14 and at that time applied PSU 15.
The oracle support confirmed that, we can use the patch version(patch#17325413) for PSU 12 and that should work also in the higher PSU versions. That did work actually, patch applied without a problem.But as I mentioned, manual workaround scripts from oracle(note 17325413) are not correct/complete. I just considered all the columns in the database, which are nullable, made them ‘not null’ and immediately back to ‘null’. Anyway, affected columns will be the subset of them. Although, there were some kind of ‘false positives’(because nullable column of dba_tab_cols does not always represents the truth). We did fix them back to to original status."
The best way to avoid this is really to apply the patch (or the PSU/BP including the patch) before upgrading.
The issue has been mentioned in "Oracle 22.214.171.124 - Known Issues and Alerts" (MOS Note:1562139.1) under "Issues Introduced":
But that does jump into your eye as a thing you need to fix before upgrade.
We'll see if we can get the issue added to the 12c MOS Notes as "Upgrade Issues".