Tuesday, January 10, 2017

ORA-01555 after unclear shutdown and How to repair a CONFLUENCE ?

After holidays at NY 2017 the CONFLUENCE won't work.
In its logs the application say something about ORA-01555 and alert show the unclear shutdown.

There were no bad messages in database alert logs and any corrupted blocks, however.

To check the data integrity I run the full export of database. And Full export show the bad table:

ORA-31693: Table data object "CONFLU"."BANDANA" 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

The command

set long 9999999
select * from conflu.bandana ;
has finished successfully and show all 297 rows as if there is no error !

But the export raised the error ORA-01555.

The solution is:

create table corrupted_lob_data (corrupted_rowid rowid);
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;
  for cursor_lob in (select rowid r, bandanavalue from conflu.bandana) loop
      n := dbms_lob.instr (cursor_lob.bandanavalue, hextoraw ('889911')) ;
when error_1578 then
insert into corrupted_lob_data values (cursor_lob.r, 1578);
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r, 1555);
when error_22922 then
insert into corrupted_lob_data values (cursor_lob.r, 22922);
end loop;

This script checks any LOB data in the corrupted table.

Then I found a specific bad row:   select * from conflu.bandana where rowid='AAAUnkAAFAAABiEAAG';

And specific filed "bandanavalue" in this row.
Then I updated this field:

update conflu.bandana set   bandanavalue = empty_clob() where rowid='AAAUnkAAFAAABiEAAG';

Now Confluence works!

No comments:

Post a Comment

Could not locate shrept.lst make: *** [client_sharedlib] Error 1

 Installing the quarterly database patch i got unpleasant message : " Patching component oracle.sdo, Make failed to ...