[Solved] Record an error of expdp export ora-01555 caused by lob damage

Error:

expdp user1/XXXXXXXX directory=szdata1 dumpfile=szhzinfo_20180319.dmp logfile=szhzinfo_20180319.log exclude=STATISTICS,INDEX tables=TKINFO QUERY=\”WHERE voteproccesstime between 20180304000000 and 20180304235959 \”

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-31693: Table data object “USER1″.”TKINFO” failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snapshot too old

The most immediate response to the ORA-01555 error is whether the undo tablespace size is sufficient, and whether the undo_retention parameter is set too small.

After verification, none of the above problems are caused.

Since the table has BLOB type columns, after searching MOS suspected that the BLOB is corrupted

IF: ORA-1555 Error During Export on LOB Data (Document ID 1950937.1)
LOBs and ORA-01555 troubleshooting (Document ID 846079.1)

Start troubleshooting for corrupt LOB field rows: 1.

1. create a table to store the rowid of the lob damaged rows

SQL> create tablecorrupted_lob_data(corrupt_rowid rowid, err_num number);

SQL> DESC LOBDATA

Name Null?Type
———- ——— ————
ID NOT NULL NUMBER
DOCUMENT BLOB

2. Execute the following plsql block to find the rows with corrupt lob

declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop
begin
n := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw (‘889911’)) ;
exception
when error_1578 then
insert intocorrupted_lob_datavalues (cursor_lob.r, 1578);
commit;
when error_1555 then
insert intocorrupted_lob_datavalues (cursor_lob.r, 1555);
commit;
when error_22922 then
insert intocorrupted_lob_datavalues (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/

Enter value for lob_column: BYTE_IMAGE
Enter value for table_owner: USER1
Enter value for table_with_lob: TKINFO
old 10: for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop
new 10: for cursor_lob in (select rowid r, BYTE_IMAGE from USER1.TKINFO) loop
old 12: num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw (‘889911’)) ;
new 12: num := dbms_lob.instr (cursor_lob.BYTE_IMAGE, hextoraw (‘889911’)) ;

3. query result found rowid AAAhS4AAUAAE3IRAAC row blob column is corrupted

SQL> select * from corrupt_lobs;

CORRUPT_ROWID ERR_NUM
—————— ———-
AAAhS4AAUAAE3IRAAC 1555

Modify the export statement to skip the corrupt blob rows, re-export, and export successfully

expdp user1/XXXXXXXX directory=szdata1 dumpfile=szhzinfo_20180319.dmp logfile=szhzinfo_20180319.log exclude=STATISTICS,INDEX tables=TKINFO QUERY=\”WHERE rowid NOT IN \(\’AAAhS4AAUAAE3IRAAC\’\) and voteproccesstime between 20180304000000 and 20180304235959 \”

Similar Posts: