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);
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, bandanavalue from conflu.bandana) loop
    begin
      n := dbms_lob.instr (cursor_lob.bandanavalue, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupted_lob_data values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupted_lob_data values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/


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';
commit;

Now Confluence works!

No comments:

Post a Comment

ZDLRA. ra_install.jar. Exception in thread "main" java.sql.SQLException: ORA-28040

If you run the  Oracle Backup module ra_install.jar and got the message: Exception in thread "main" java.sql.SQLException: ORA-2...