异常宕机导致的LOB字段报错ORA-00600 ktspgfb-1

最新推荐文章于 2021-05-26 14:51:30 发布

Yushan Bai

最新推荐文章于 2021-05-26 14:51:30 发布

阅读量1.7k

点赞数 1

分类专栏： ORACLE 故障排查

本文链接：https://blog.csdn.net/haibusuanyun/article/details/104929725

版权

ORACLE 故障排查专栏收录该内容

195 篇文章 15 订阅

订阅专栏

又一次遇到某用户的LOB字段损坏的问题，数据库异常关闭导致了核心表的LOB字段异常，本次比较特殊从日志看是管理块出了问题PAGETABLE MANAGED LOB BLOCK，使用RMAN的块恢复无法解决。
具体报错是ORA-00600: 内部错误代码, 参数: [ktspgfb-1], 在校验LOB字段时报错ORA-00600: internal error code, arguments:
[ktsp_check_repair_blk:objdchk_kcbgcur_6]。
参考MOS文档ORA-600 [KTSPGFB-1] ON TABLE WITH LOB COLUMN (Doc ID 1968287.1)中给出的解决方法使用了最快速的移到LOB字段到另外的表空间，解决了此问题. 其它LOB字段问题的处理记录参考：https://blog.csdn.net/haibusuanyun/article/details/104087660

CAUSE
The issue is addressed in unpublished Bug 14756359 : ORA-600 [KTSPGFB-1] ON UPDATE OF TABLE WITH LOBS
which is closed as duplicate of Bug 14477795 closed with status 'Could Not Reproduce'.

The problem seems to be specific to 11.2.0.2 and above release.

SOLUTION
In this case it appears that there was a corruption in the L1 Bitmap Block so, to solve the issue, the table was dropped and recreated:
1)you can recreate table using import dump of table
or
2)Move LOB Data To Another Tablespace
or
3)Re-Organize a Table Online

相关TRACE日志：

[oracle@zyy-hisdx ~]$ tail -n 500 alert_orcl.log |more
Tue Mar 17 16:47:07 2020
LNS: Standby redo logfile selected for thread 1 sequence 202034 for destination LOG_ARCHIVE_DEST_2
Tue Mar 17 16:47:10 2020
Archived Log entry 84262 added for thread 1 sequence 202033 ID 0x59756ed4 dest 1:
Tue Mar 17 16:47:15 2020
Sweep [inc][108132]: completed
Tue Mar 17 16:47:53 2020
Errors in file /oracle/app/diag/rdbms/primary/orcl/trace/orcl_ora_23370.trc  (incident=108144):
ORA-00600: 内部错误代码, 参数: [ktspgfb-1], [], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

TRACE内容：
*** 2020-03-17 16:08:46.163
*** SESSION ID:(2060.181) 2020-03-17 16:08:46.163
*** CLIENT ID:() 2020-03-17 16:08:46.163
*** SERVICE NAME:(orcl) 2020-03-17 16:08:46.163
*** MODULE NAME:(w3wp.exe) 2020-03-17 16:08:46.163
*** ACTION NAME:() 2020-03-17 16:08:46.163
 
Dump of buffer cache at level 10 for tsn=24 rdba=277899906
BH (0x157dab200) file#: 66 rdba: 0x10906a82 (66/1075842) class: 8 ba: 0x1541f8000
  set: 79 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  dbwrid: 2 obj: 382325 objn: 188836 tsn: 24 afn: 66 hint: f
  hash: [0x515068cc8,0x515068cc8] lru: [0x283e13d80,0x157db1e30]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [0x4d074a0f8,0x4d074a0f8] objaq: [0x4d074a0e8,0x4d074a0e8]
  use: [NULL] wait: [NULL] fast-sh-pins: 1
  st: XCURRENT md: NULL fpin: 'ktspfwh40: ktsplbfmb' tch: 0
  flags:
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  buffer tsn: 24 rdba: 0x10906a82 (66/1075842)
  scn: 0x0d6a.5a6fc4e9 seq: 0x02 flg: 0x04 tail: 0xc4e92802
  frmt: 0x02 chkval: 0xe61a type: 0x28=PAGETABLE MANAGED LOB BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000001541F8000 to 0x00000001541FA000

做ERRORSTACK后找出报错语句及表名---UPDATE ABCDEFGTABLE SET HTMLVALUE=:CLOB ：
[root@zyy-hisdx log]# cat /oracle/app/diag/rdbms/primary/orcl/trace/orcl_ora_22901.trc|grep AAA
…………
user_id=66 user_name=AAA module=w3wp.exe action=
3294C9FF0 4D4503FF 04300152 546309C3 292F0601  [..AAA.0...cT../)]
2020-03-17 18:46:58.952869*:80688B96:sql_mon_query:keswx.c@4334:keswxWriteGlobalInfoToStream(): wrote to stream flags=1317 modLen=8 modName=w3wp.exe actLen=0 actName= userid=66 userLen=4 userName=AAA svcLen=4 svcName=orcl clidLen=0 clid=(null) progLen=8 progName=w3wp.exe plseoid=0 plsesid=0 plscoid=0 plscsid=0 txtLen=105 txt=UPDATE ABCDEFGTABLE SET HTMLVALUE=:CLOB WHERE BINGLIJLID='f9886e2a-e11d-48ec-b9dd-647f61d88679'

相关处理步骤：

用到的处理语句：
1.用ERRORSTACK来找出SQL及表
SQL>  alter system set events='600 trace name errorstack forever,level 3';
System altered.


2.查询表的信息
SQL> select object_type,owner,object_name from dba_objects where object_name='ABCDEFGTABLE';

OBJECT_TYPE
---------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
TABLE
AAA
ABCDEFGTABLE


SQL> desc AAA.ABCDEFGTABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BINGLIJLID                                NOT NULL VARCHAR2(36)
 BINGRENID                                 NOT NULL VARCHAR2(10)
 YINGERID                                  NOT NULL VARCHAR2(10)
 MOBANLX                                   NOT NULL VARCHAR2(20)
 HTMLVALUE                                          CLOB



3.查询及验证LOB字段，报错
SQL> select column_name, pctversion, retention,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where table_name = 'ABCDEFGTABLE' and owner='AAA';

COLUMN_NAME
--------------------------------------------------------------------------------
PCTVERSION  RETENTION
---------- ----------
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
HTMLVALUE
                    4
SYS_LOB0000188835C00005$$
TS_AAA


SQL>  exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('AAA','SYS_LOB0000188835C00005$$','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK);

BEGIN DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('AAA','SYS_LOB0000188835C00005$$','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:objdchk_kcbgcur_6], [1], [382325], [0], [0], [], [], [],
[], [], [], []
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 335
ORA-06512: at line 1


4.移动LOB字段--移动前计划好空间问题及产生的归档日志
SQL> select bytes/1024/1024 mb from dba_segments where segment_name='SYS_LOB0000188835C00005$$';

        MB
----------
     15573

SQL> Alter table AAA.ABCDEFGTABLE move lob(HTMLVALUE) store as (tablespace USERS);

Table altered.

SQL> col COLUMN_NAME for a20
SQL> col SEGMENT_NAME for a30
SQL> select column_name, pctversion, retention,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where table_name = 'ABCDEFGTABLE' and owner='AAA';

COLUMN_NAME          PCTVERSION  RETENTION SEGMENT_NAME                   TABLESPACE_NAME
-------------------- ---------- ---------- ------------------------------ --------------------
HTMLVALUE                                4 SYS_LOB0000188835C00005$$      USERS

5.校验LOB字段无误后重建索引 
SQL> exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('AAA','SYS_LOB0000188835C00005$$','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK);
PL/SQL procedure successfully completed.

SQL> col owner for a30
SQL> col index_name for a30
SQL> col table_name for a30
SQL> select  OWNER,INDEX_NAME,table_name,status from dba_indexes  where owner='AAA' and status<>'VALID';
OWNER                          INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------
AAA                           IDX                            PARTIONED                      N/A
AAA                           PK_ZY_DOC_BINGLIJLHTML_V4      ABCDEFGTABLE         UNUSABLE

SQL> select  OWNER,INDEX_NAME,table_name,status from dba_indexes  where owner='AAA' and table_name='ABCDEFGTABLE' and status<>'VALID';
OWNER                          INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------
AAA                           PK_ZY_DOC_BINGLIJLHTML_V4      ABCDEFGTABLE         UNUSABLE

SQL>  alter index AAA.PK_ZY_DOC_BINGLIJLHTML_V4 rebuild online;
Index altered.

SQL> select  OWNER,INDEX_NAME,table_name,status from dba_indexes  where owner='AAA' and table_name='ABCDEFGTABLE';
OWNER                          INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------
AAA                           SYS_IL0000188835C00005$$       ABCDEFGTABLE         VALID
AAA                           PK_ZY_DOC_BINGLIJLHTML_V4      ABCDEFGTABLE         VALID