LOB字段列写数据报错ORA-600 [25027]问题处理

最新推荐文章于 2024-09-30 09:47:12 发布

上帝的二公子

最新推荐文章于 2024-09-30 09:47:12 发布

阅读量658

点赞数 16

文章标签：数据库性能优化

本文链接：https://blog.csdn.net/gaoda00136/article/details/139815867

版权

LOB字段列插入数据时遇到ORA-600 [25027]、ORA-600 [ktsplbfmb-dblfree] 错误的问题，在处理过程中发现是遇到ORA-01555 问题；
最终通过分析trace文件、设置errorstack等方式找出三个表的LOB字段列有问题，并进行了相应处理。
主要过程是：
1.分析trace文件、设置errorstack等方式找出错误的LOB字段列
2.使用DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY包校验，使用RMAN进行物理坏块校验
3.使用MOS提供的处理LOB ORA-01555的PL/SQL来找出报错的LOB段中的行的ROWID及报错信息
4.将报错的LOB行按ROWID更新为空值(丢了数据)
5.MOVE LOB段到其它表空间(产生大量redo/目标表空间容量需要关注)
6.检查相关索引是否失效并处理
7.DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY包校验，应用软件使用测试。
参考MOS文档：
ORA-600 [ktsplbfmb-dblfree] During Insert in to a LOB (文档 ID 783593.1)
ORA-600 [25027] (文档 ID 284433.1)
ORA-00600 [25027] [x] [0] Raised by Insert into LOB (文档 ID 1608861.1)
IF: ORA-1555 on LOB Data (文档 ID 1950896.1)
LOB Corruption With ORA-22924 And ORA-01555 After The LOB Shrink Space Operations When DB Has Fix Of 21246723 (文档 ID 2377683.1)
LOBs and ORA-01555 troubleshooting (文档 ID 846079.1)

具体处理过程：

TRACE中找到报错的LOB对象信息：
*** 2018-08-15 23:52:11.953
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00600: ?2?′′??2???027], [28], [0], [], [], [], [], [], [], [], [], []
----- Current SQL Statement for this session (sql_id=4uy64tf4a6fb8) -----
UPDATE ZY_DOC_BINGLIJLHTML_V4 SET HTMLVALUE=:CLOB WHERE BINGLIJLID='b9c8cb06-748d-48e1-a0aa-9a8b7affa016'

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41 call kgdsdst() 000000000 ? 000000000 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
ksedst1()+103 call skdstdst() 000000000 ? 000000000 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
ksedst()+39 call ksedst1() 000000000 ? 000000001 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
dbkedDefDump()+2746 call ksedst() 000000000 ? 000000001 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
ksedmp()+41 call dbkedDefDump() 000000003 ? 000000000 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
dbkdaKsdActDriver() call ksedmp() 000000003 ? 000000000 ?
+1960 7FFEAF28B4A0 ? 7FFEAF28B578 ?

###################################
数据库中进行处理：
SQL> exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('EMR3','SYS_LOB0000188094C00003$$','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK);
BEGIN DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('EMR3','SYS_LOB0000188094C00003$$','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsp_check_repair_blk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 335
ORA-06512: at line 1
SQL> alter table EMR3.ZY_DOC_BINGLIJLXML_V4 modify lob(XMLVALUE)(pctversion 40);

Table altered.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL> Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS);
Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS)
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'ZY_DOC_BINGLIJLXML_V4' and owner='EMR3';

COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PCTVERSION RETENTION
---------- ----------
XMLVALUE
40

SQL> col column_name for a30
SQL> /

COLUMN_NAME PCTVERSION RETENTION
------------------------------ ---------- ----------
XMLVALUE 40

SQL> alter table EMR3.ZY_DOC_BINGLIJLXML_V4 modify lob(XMLVALUE)(retention);

Table altered.

SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'ZY_DOC_BINGLIJLXML_V4' and owner='EMR3';

COLUMN_NAME PCTVERSION RETENTION
------------------------------ ---------- ----------
XMLVALUE 10800

SQL>
SQL>
SQL> Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS);
Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS)
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
SQL> select count(*) from corrupt_lobs20180815;

COUNT(*)
----------
0

SQL> 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);
num number;
begin
for cursor_lob in (select rowid r, XM 2 3 4 5 6 7 8 9 10 LVALUE from EMR3.ZY_DOC_BINGLIJLXML_V4) loop
begin
num := dbms_lob.instr (cursor_lob.XMLVALUE, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupt_lobs20180815 values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corr 11 12 13 14 15 16 17 18 upt_lobs20180815 values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs20180815 values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/ 19 20 21 22 23 24 25 26

PL/SQL procedure successfully completed.

SQL> select * from corrupt_lobs20180815;

CORRUPT_ROWID ERR_NUM
------------------ ----------
AAAuG1AA/AABQA0AAB 1555
AAAuG1AA/AABQA0AAF 1555
AAAuG1AA/AABQA0AAG 1555
AAAuG1AA/AABQC0AAA 1555
AAAuG1AA/AABQV3AAB 1555
AAAuG1AA/AABQV3AAC 1555
AAAuG1AA/AABQV3AAD 1555

7 rows selected.

SQL> update EMR3.ZY_DOC_BINGLIJLXML_V4
set XMLVALUE = empty_clob()
where rowid in (select corrupt_rowid from SYS.corrupt_lobs20180815);
commit; 2 3
7 rows updated.

SQL>

Commit complete.

SQL> commit;

Commit complete.

SQL> Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS);

Table altered.
SQL> exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('EMR3','SYS_LOB0000188094C00003$$','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK);

PL/SQL procedure successfully completed.

SQL>
SQL> /

OWNER
---------------
INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
TABLE_NAME
--------------------------------------------------------------------------------
EMR3
PK_ZY_DOC_BINGLIJLXML_V4
UNUSABLE
ZY_DOC_BINGLIJLXML_V4

SQL> alter index EMR3.PK_ZY_DOC_BINGLIJLXML_V4 rebuild online;
Index altered.