Expdp ends with ORA-31693,ORA-02354,ORA-015555,ORA-22924 with lobobjects

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: Expdp ends with ORA-31693,ORA-02354,ORA-015555,ORA-22924


### Export Data Pump parameters ###
exp using DP,schema level
expdp bkp/*** dumpfile=work_dir:expdp_ecs.dmp logfile=DATA_PUMP_DIR:expdp_ecs.log schemas=ecs


While exporting using DP:
ORA-31693: Table data object "ECS"."CS_B_DOCUMENT":"OUTBOXDATE_MAXVAL" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old


Problem does not show while using normal exp , however during imp it gave the following errors:


Here what I got after I imported the healthy exp dmp file !

. . importing partition "CS_B_DOCUMENT":"OUTBOXDATE_2013" 0 rows imported
. . importing partition "CS_B_DOCUMENT":"OUTBOXDATE_MAXVAL"
IMP-00064: Definition of LOB was truncated by export
IMP-00028: partial import of previous table rolled back: 351622 rows rolled back
. . importing table "CS_B_DOCUMENT_FOLLOWUP" 0 rows imported




Explanation:


ORA-1555/ORA-22924 may occur when accessing LOB columns, even when the LOB RETENTION seems to be sufficient.
This may occur when LOB column resides in a MSSM (Manual Segment Space Management) tablespace.

It is also confirmed that the LOB column resides in a MSSM.

Please note that LOB RETENTION parameter has no effect if the LOB resides in a tablespace using MANUAL space management (MSSM). In order for LOB RETENTION to honour the UNDO_RETENTION period Automatic Segment Space Managemetn (ASSM) should be used.


If you want to use LOB retention for LOB columns to avoid ORA-1555, you must use ASSM (Automatic Segment Space Management) tablespace.


You cannot change the segment space management mode of a tablespace. If your LOB column is store on MSSM tablespace and you would like to use the ASSM option, you'd have to create a new tablespace using 'segment space management auto' followed by moving the objects to the new tablespace which was created to use Automatic Segment Space Management.

create tablespace assm_ts datafile
...
autoextend on
extent management local
segment space management auto; <==

alter table <table_name> move tablespace <ASSM_tablespace_name>;


If you can't move to ASSM and you need to store your LOB data on MSSM tablespace, you'd have to use PCTVERSION instead of RETENTION.

-- Example: Setting PCTVERSION to 20 percent
SQL> alter table <table_name> modify lob (<LOB_column>) (pctversion 20);


Please also refer the following note:

Note 800386.1
ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a MSSM tablespace

Solution:


From the above query identify the LOB name and also the PCT version.
If the PCT version is less than 50, then advise you to increase the PCTversion to 50% for the table CS_B_DOCUMENT_FOLLOWUP as follows:


SQL>alter table CS_B_DOCUMENT_FOLLOWUP MODIFY LOB <LOB NAME> ( PCTVERSION 50);

Once done, take a fresh export using exp. and re-attempt the import.


if the output for the file contains row data, then the LOB is corrupted.


Please refer note 787004.1, where clear steps is provided on how to identify the corrupt blocks and work around the issue.

set serverout on
exec dbms_output.enable(100000);
declare
pag number;
len number;
c varchar2(10);
charpp number := 8132/2;

begin
for r in (select rowid rid, dbms_lob.getlength (documentcontent) len
from ecs.CS_B_DOCUMENT) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (documentcontent, 1, 1+ (page * charpp))
into c
from ecs.CS_B_DOCUMENT
where rowid = r.rid;

exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/


there are corrupted LOB rows withing the segment. Please perform the action plan as per note 874562.1 to confirm the same.


Reference:
Note ID 452341.1 for detecting corrupted clob's


Note 874562.1
EXP-00056,ORA-24801 During Export

Get Oracle Certifications for all Exams
Free Online Exams.com