Wednesday, May 26, 2010

LOB Corruption prevented export of a Table

Few days back, I noticed the following error while DataPump was exporting a table containing BLOB column:
ORA-31693: Table data object "<<tablename>>" 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

I tried to export that table with the old export utility, but that too was not successful.

Searched the Oracle Support Documents and got the below script:

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 () len from ) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (, 1, 1+ (page * charpp)) into c from 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;
/

The execution of the above script showed two records have corrupted blocks. I deleted those records and attempted export (using DataPump). This time it was successfull.

I am looking for ways to recover such corrupted blocks.

Tuesday, May 25, 2010

ORA-00600: internal error code, arguments: [kcbnew_3], [6], [1], [51907], [], [], [], []

Ours is a Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production database and is being accessed by our Forms10g product "eFreight Suite", a freight forwarding package.

Yesterday, I noticed "ORA-00600: internal error code, arguments: [kcbnew_3], [6], [1], [51907], [], [], [], []" error in the alert log and some of our users complained that, the Forms Application also throws such error.

Got the problem solved by clearing the buffer cache by executing
alter system flush buffer_cache;