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，并重新登录验证，问题得到解决。生产不建议对基表直接修改，或修改前备份。