Tuesday, June 21, 2011

CLOB-related DataPump issue

Yesterday while building a demo-instance for the upcoming KScope'11 I've hit a strange Oracle bug. Here is the story:
- a single schema is transferred from 10.2.0.5 to 11.2.0.1 (both Windows 32 bit) via datapump
- importing of the data fails with fatal error (in the middle of data import)
- I took export log, found exact table it fails to import (by looking at the last successful load), and tried to import just one table. It also failed with the same fatal error:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_TABLE_01" stopped due to fatal error at 10:38:53

- in the alert.log I found a bit more details

ORA-07445: exception encountered: core dump [kcblnb_dscn()+38] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0xDD94FE] [UNABLE_TO_READ] []

Metalink pretty quickly gave me an answer: Doc ID 1313537.1:
- Using datapump to import a table that has mixed CACHE/NOCHACHE LOB columns results in an ORA-7445.
- Eventually there will be patch (11.2.0.3), but for now the workaround is to use regular EXP/IMP

I tested the workaround option - it does its job. So, beware!

P.s. Since I am talking for years about manual adjusting CLOB storage parameters, I decided that it is my duty to create this post! As usual, advanced features are causing side-effects...

P.p.s. Forgot to mention - just FYI about old EXP/IMP module: you cannot export data from 10g database using 11g client utilities. The export must be done using native 10g utilities - otherwise a pretty strange error is thrown:


EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier

No comments: