Troubleshooting: ORA-00600: [kkpo_rcinfo_defstg:delseg], [xxxx] & ora-600 [25027] & ora-600 [ktadrprc-1]
最近接触一套110TB数据库前不久因存储坏导致的数据库出现了一些问题， 因库较大客户不同意重建库，前期其他人已在存储恢复后强拉起了数据库，并反应做了如推进SCN , DROP undo segment等。但后期的问题很多，这里只记录一个db alert 中出现的ORA-00600: [kkpo_rcinfo_defstg:delseg], [xxxx] 问题，在网上没有找到相似案例及解决方法的资料，多次测试算是还原了该问题并解。
环境是11.2.0.2 2NODES RAC ON AIX
# DB alert log
Sweep [inc][278134]: completed
2016-10-14 09:33:02.360000 +08:00
Errors in file /oradb/diag/rdbms/anbob/anbob1/trace/anbob1_ora_43057606.trc (incident=278135):
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [2729136], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2016-10-14 09:33:14.078000 +08:00
Errors in file /oradb/diag/rdbms/anbob/anbob1/trace/anbob1_ora_17432728.trc (incident=278123):
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [2716688], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2016-10-14 09:33:16.666000 +08:00
Errors in file /oradb/diag/rdbms/anbob/anbob1/trace/anbob1_ora_18678206.trc (incident=278124):
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [2729136], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2016-10-14 09:34:02.958000 +08:00
Sweep [inc][278135]: completed
Sweep [inc][278124]: completed
Sweep [inc][278123]: completed
[oracle@hbltp750u:/home/oracle] sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 14 10:06:31 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
sys@ORCL>@p deferred_seg
NAME VALUE
---------------------------------------- ----------------------------------------
deferred_segment_creation TRUE
SQL> select object_name,subobject_name,owner,object_type from dba_objects where object_id=2729136;
OBJECT_NAME SUBOBJECT_NAME OWNER OBJECT_TYPE
--------------------- -------------- ----------------- ------------------
LDR201609 P20160924_23 anbob TABLE PARTITION
SQL> select count(*) from anbob.LDR201609 partition(P20160924_23);
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [2729136], [], [], [], [], [], [], [], [], [], []
SQL>alter table anbob.LDR201609 modify partition P20160924_23 allocate extent(size 10m);
ERROR at line 1:
ORA-00600: internal error code, arguments:ora-600 [25027] [104]
SQL> alter table anbob.LDR201609 move partition P20160923_23 ;
alter table anbob.LDR201609 move partition P20160923_23
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], [], [], [], [], []
Note:
数据库11g默认的延迟段创建特性是启用的，根据ORA-00600 [kkpo_rcinfo_defstg:delseg], [2729136] 猜测是与延迟段创建相关，并且第一个参数应该是OBJECT_ID, 无论是查询, MOVE, 手动扩展空间都报ora-600 内部错误。
ORA-600 [kkpo_rcinfo_defstg:delseg] [A]
ARGUMENTS:
Arg [a] OBJECT ID (OID)
ORA-600 [25027] [a] [b]
VERSIONS:
versions 9.2 and above
ARGUMENTS:
Arg [a] Tablespace Number (TSN)
Arg [b] Decimal Relative Data Block Address (RDBA)
In 12c it includes Multitenant information:
Arg [a] 0 if Multitenant is not enabled or 0 if there is not Root CDB session, 1 ROOT PDBID, otherwise PDBID top session
Arg [b] PDBID
Arg [c] Tablespace Number (TSN)
Arg [d] Decimal Relative Data Block Address (RDBA)
ora-600 [ktadrprc-1] due to a data dictionary inconsistency.
根据上面的三个错误，初步判断应该与数据字典不一致相关，并从MOS 上下载hcheck4.sql 检查了一下。
H.Check Version 4.1 --------------------------------------- Catalog Version 11.2.0.2.0 (1102000200) db_name: WLLT 18-OCT-2016 14:22:04 --------------------------------------- ... .- OrphanedIndexPartition ... 1102000200 <= *All Rel* : Ok 10/18 14:47:04 .- OrphanedIndexSubPartition ... 1102000200 <= *All Rel* : Ok 10/18 14:47:05 .- OrphanedTable ... 1102000200 <= *All Rel* : Ok 10/18 14:47:05 .- OrphanedTablePartition ... 1102000200 <= *All Rel* : Ok 10/18 14:47:07 HCKE-0020: Orphaned TABPART$ (no SEG$) (Doc ID 1360890.1) ORPHAN TABPART$: OBJ=2716688 TS=104 RFILE/BLOCK=0 0 BO#=2676259 SegType= ORPHAN TABPART$: OBJ=2716680 TS=104 RFILE/BLOCK=0 0 BO#=2676259 SegType= ORPHAN TABPART$: OBJ=2716672 TS=104 RFILE/BLOCK=0 0 BO#=2676259 SegType= ... ORPHAN TABPART$: OBJ=2716528 TS=104 RFILE/BLOCK=0 0 BO#=2676259 SegType= ORPHAN TABPART$: OBJ=2716520 TS=104 RFILE/BLOCK=0 0 BO#=2676259 SegType= ORPHAN TABPART$: OBJ=2729136 TS=104 RFILE/BLOCK=0 0 BO#=2676259 SegType= ...
Note:
MOS 中对HCKE-0020: Orphaned TABPART$ (no SEG$) (Doc ID 1360890.1) 该错误的解释
Data in the listed table partition cannot be accessed. There is a deferred segment creation option in 11g which allows a table partition to exist with no segment (until rows are inserted) but this test accounts for such partitions . Hence for reported table partitions they look like they should have a segment attached but do not.
因为数据库未禁用延迟段创建，所以开始觉的这么多的no SEG$ 并不奇怪。如果观察了基表对于延迟段的创建前后，创建表未分配段前是在obj$/partobj$, tab$/parttab$,deferred_stg$中记录，但是不会在seg$中有分配记录，分配段后是delete deferred_stg$,并更新tab$/parttab$, 在seg$中分配记录关连。 在不同的版本中更新可能不同，如有表上存在其它对象，基表修改更多。
周末没事想研究一下这个问题，自己的测试库中做了一下测试，终于还原了该问题并解决。测试环境11.2.0.4，因为没有和生产相同的环境，但不影响测试。
anbob@ORCL> create table anbob.tpart(id number, name varchar2(100)) 2 partition by range(id) 3 (partition p_100 values less than (101), 4 partition p_200 values less than(201), 5 partition p_300 values less than(301)); Table created. anbob@ORCL>@o tpart owner object_name object_type status OID D_OID CREATED LAST_DDL_ --------- ------------- ------------------ --------- -------- --------- --------- --------- ANBOB TPART TABLE VALID 87860 23-OCT-16 23-OCT-16 ANBOB TPART TABLE PARTITION VALID 87861 87861 23-OCT-16 23-OCT-16 ANBOB TPART TABLE PARTITION VALID 87863 87863 23-OCT-16 23-OCT-16 ANBOB TPART TABLE PARTITION VALID 87862 87862 23-OCT-16 23-OCT-16
同样使用Hcheck4.sql检查数据字典。
H.Check Version 4.1
---------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: ORCL
23-OCT-2016 07:04:45
---------------------------------------
Catalog Fixed
Procedure Name Version Vs Release Run
------------------------------ ... ---------- -- ---------- ---
...
.- PoorStorage ... 1102000400 <= *All Rel* : Ok 10/23 07:04:46 .- MissTabSubPart ... 1102000400 > 900010000 : n/a
.- PartSubPartMismatch ... 1102000400 > 1102000100 : n/a
.- TabPartCountMismatch ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedTabComPart ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47 .- ZeroIndSubPart ... 1102000400 > 902000100 : n/a
.- MissingSum$ ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- MissingDir$ ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- DuplicateDataobj ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- ObjSynMissing ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- ObjSeqMissing ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedUndo ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedIndex ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedIndexPartition ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedTable ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedTablePartition ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- MissingPartCol ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
.- OrphanedSeg$ ... 1102000400 <= *All Rel* : Ok 10/23 07:04:47
...
Note:
注意到OrphanedTablePartition项并没有打印出我们创建的未分配段的表（延迟段创建特性未禁用，segment未分配）。为什么生产库上就会显示呢？ 还是从hcheck.sql脚本开始，我找到那段。
select i.obj#, i.ts#, i.file#, i.block#, i.bo#, s.type#
from seg$ s, tabpart$ i
where i.ts#=s.ts#(+)
and i.file#=s.file#(+)
and i.block#=s.block#(+)
and i.dataobj# is not null /* A Physical object, Excludes IOT */
and nvl(s.type#,0)!=5
and bitand(i.flags, 65536) != 65536 /* Exclude DEFERRED Segment */;
what is mean the seg$.type?
/* 1 = UNDO, 2 = SAVE UNDO, 3 = TEMPORARY, 4 = CACHE, 5 = DATA, 6 = INDEX */
/* 7 = SORT 8 = LOB 9 = Space Header 10 = System Managed Undo */
Note:
上面如果提示HCKE-0020是因为seg$ 和tabpart$关连后的验证后的结果，最可疑就是seg$.type#和tabpart$.flags。因为目前没办法连问题库，所以只是猜测（后期已确认和模拟的一致）.
sys@ORCL>select ts#,file#,block#,flags,bitand(flags, 65536) from tabpart$ where obj#=87861;
DATAOBJ# TS# FILE# BLOCK# FLAGS BITAND(FLAGS,65536)
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
87861 4 0 0 65536 65536
sys@ORCL>insert into anbob.tpart values(1,'a');
1 row created.
sys@ORCL> select dataobj#,ts#,file#,block#,flags,bitand(flags, 65536) from tabpart$ where obj#=87861;
DATAOBJ# TS# FILE# BLOCK# FLAGS BITAND(FLAGS,65536)
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
87861 4 4 35154 0 0
Note:
对比了一下做分配前后flags的变化，分配前是65536,分配后是0. 下面尝试更新flags。 注意以下操作不要在生产库上操作，除非你非常的了解并对自己修改负责。
sys@ORCL>update tabpart$ set flags=0 where obj#=87862;
1 row updated.
sys@ORCL>commit;
Commit complete.
sys@ORCL>alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
sys@ORCL>select * from anbob.tpart partition(p_200);
select * from anbob.tpart partition(p_200)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [87862], [], [], [], [], [], [], [], [], [], []
sys@ORCL> select i.obj#, i.ts#, i.file#, i.block#, i.bo#, s.type#
2 from seg$ s, tabpart$ i
3 where i.ts#=s.ts#(+)
4 and i.file#=s.file#(+)
5 and i.block#=s.block#(+)
6 and i.dataobj# is not null /* A Physical object, Excludes IOT */
7 and nvl(s.type#,0)!=5
8 and bitand(i.flags, 65536) != 65536 /* Exclude DEFERRED Segment */;
OBJ# TS# FILE# BLOCK# BO# TYPE#
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
87862 4 0 0 87860
sys@ORCL>alter table anbob.tpart modify partition p_200 allocate extent(size 10m);
alter table anbob.tpart modify partition p_200 allocate extent(size 10m)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25027], [4], [0], [], [], [], [], [], [], [], [], []
sys@ORCL>alter table anbob.tpart move partition p_200;
alter table anbob.tpart move partition p_200
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], [], [], [], [], []
# DB alert log
Sweep [inc][278134]: completed
2016-10-14 09:33:02.360000 +08:00
Errors in file /oradb/diag/rdbms/anbob/anbob1/trace/anbob1_ora_43057606.trc (incident=278135):
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [2729136], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2016-10-14 09:33:14.078000 +08:00
Errors in file /oradb/diag/rdbms/anbob/anbob1/trace/anbob1_ora_17432728.trc (incident=278123):
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [2716688], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2016-10-14 09:33:16.666000 +08:00
Errors in file /oradb/diag/rdbms/anbob/anbob1/trace/anbob1_ora_18678206.trc (incident=278124):
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [2729136], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2016-10-14 09:34:02.958000 +08:00
Sweep [inc][278135]: completed
Sweep [inc][278124]: completed
Sweep [inc][278123]: completed
# Call stack in Trace file
----- Abridged Call Stack Trace ----- ksedsts()+888<-kjzduptcctx()+345<-dbkedDefDump()+7127<-dbgexPhaseII()+2017<-dbgexProcessError()+3253 <-dbgePostErrorKGE()+1491<-dbkePostKGE_kgsf()+52< -kgerinv_internal()+49<-kgerinv()+31<-kgeasnmierr()+126
Note:
这里我们还原了错误,下面尝试用交换分区解决问题。
sys@ORCL>create table anbob.tpart_bak as select * from anbob.tpart where 1=0;
create table anbob.tpart_bak as select * from anbob.tpart where 1=0
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [87862], [], [], [], [], [], [], [], [], [], []
sys@ORCL>create table anbob.tpart_bak as select * from anbob.tpart partition(p_100) where 1=0;
Table created.
Elapsed: 00:00:00.14
sys@ORCL> alter table anbob.tpart exchange partition p_200 with table anbob.tpart_bak;
alter table anbob.tpart exchange partition p_200 with table anbob.tpart_bak
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsssun_segment2], [4], [0], [0], [], [], [], [], [], [], [], []
Note:
交换分区无法规避该问题， 下面修正数据字典的flags字段，并测试修改后表操作是否正常。
sys@ORCL>update tabpart$ set flags=65536 where obj#=87862; 1 row updated. sys@ORCL>alter system flush shared_pool; System altered. sys@ORCL>select * from anbob.tpart partition(p_200); no rows selected Elapsed: 00:00:00.00 sys@ORCL>alter table anbob.tpart move partition p_200; Table altered. sys@ORCL>alter table anbob.tpart modify partition p_200 allocate extent(size 10m); Table altered. sys@ORCL>alter table anbob.tpart exchange partition p_200 with table anbob.tpart_bak; Table altered.
Summary:
因为某些原因数据字典表不一致，导到该表在查询或导出时都会提示ora-600 [kkpo_rcinfo_defstg:delseg] 错误，因为数据库使用延迟段创建，手动分配segment时提示ORA-600 [25027],对分区做MOVE时会提示ORA-600 [ktadrprc-1]， 使用hcheck脚本检查会提示Orphaned TABPART$ ,然后通过脚本中找到字典的错误，修改数据字典后记的flush shared_pool, 使用RAC 时刷新所有实例shared_pool，并重新登录验证，问题得到解决。生产不建议对基表直接修改，或修改前备份。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。