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!
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
Note: Only a member of this blog may post a comment.