11g通过xtts迁移升级到19c（U2L）ORA-600报错处理

背景描述：某客户计划将一套重要业务系统数据库由AIX平台11g迁移到X86平台（云和恩墨zData）且数据库升级到19c。在正式迁移升级前进行测试，为减少对生产环境影响，本次测试将AIX平台的DG备份作为xtts源端。
基础环境：xtts源端：OS：AIX 7.1 DB：11.2.0.4
xtts目标端： OS：linux 7.9 DB：19.15

问题描述：迁移后在19c环境一个业务用户创建表报ORA-00600; inrernal error,code, arguments: [ktssinseg3], [14], [134224922]
问题原因：段头块信息已存在
解决方法：重建索引
分析过程：
1、使用业务用户hroper创建表

sqlplus hroper/password
SQL>create table test01 (id number);
create table test01
*
ERROR at line 1:
ORA-00600; inrernal error,code, arguments: [ktssinseg3], [14], [134224922], []
[],[],[],[],[],[],[]

2、数据库日志和trace分析
alert日志

...
2023-06-15T13:23:00.544371+08:00
Errors in file /u01/app/oracle/diag/rdbms/prod/prod2/trace/prod2_ora_132408.trc  (incident=186561):
ORA-00600: internal error code, arguments: [ktssinseg3], [14], [134224922], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/prod/prod2/incident/incdir_186561/prod2_ora_132408_i186561.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
...
trace日志
*** 2023-06-15T14:55:21.776621+08:00
*** SESSION ID:(1154.27870) 2023-06-15T14:55:21.776630+08:00
*** CLIENT ID:() 2023-06-15T14:55:21.776634+08:00
*** SERVICE NAME:(SYS$USERS) 2023-06-15T14:55:21.776638+08:00
*** MODULE NAME:(SQL*Plus) 2023-06-15T14:55:21.776642+08:00
*** ACTION NAME:() 2023-06-15T14:55:21.776646+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-06-15T14:55:21.776650+08:00
 
[TOC00000]
Jump to table of contents
Dump continued from file: /u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_255321.trc
[TOC00001]
ORA-00600: internal error code, arguments: [ktssinseg3], [14], [134224922], [], [], [], [], [], [], [], [], []
[TOC00001-END]
[TOC00002]
========= Dump for incident 234593 (ORA 600 [ktssinseg3]) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
ERROR: ktssinseg segment exist SH:14/0x08001c1a objd:6677822 SC: objd:627319 type:6 flag:0x420101
----- End of Customized Incident Dump(s) -----
[TOC00003-END]

*** 2023-06-15T14:55:21.809276+08:00
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00004]
----- Current SQL Statement for this session (sql_id=9yq36ry3nxmzv) -----
create table test01 (id number)
[TOC00004-END]
.....
----- Abridged Call Stack Trace -----
ksedsts()+426<-kjzduptcctx()+805<-kjzdpcrshnfy()+425<-dbkedKstDump()+27<-dbgdaExecuteAction()+354<-dbgerRunAction()+83
<-dbgerRunActions()+1352<-dbgexPhaseII()+549<-dbgexExplicitEndInc()+285<-dbgeEndDDEInvocationImpl()+314
<-ktssinseg()+5795<-ktsscrsegfmt()+1060<-ktsscrseg()+1563<-ktssctr_segment1()+858<-ktssctr_segment()
+287<-ktrsexecExecuteInExcepHdlr()+327<-ktrsexec()+193<-ktspcfs()+166<-ktsscf_segment()+232<-kkpoctds_crt_tmp_data_seg()+211 
----- End of Abridged Call Stack Trace -----
从日志和trace可以看到，创建表时段头信息已存在。

3、19c目标端元数据导入日志

...
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  
stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) 
VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN  DELETE FROM "SYS".
"IMPDP_STATS";   i_n := 'HR_EMP_PK';   i_o := 'CCMS';   EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,14,1,1,10,0,14,NV,NV,TO_DATE('2023-06-07 02:00:59',df),NV;  
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');   DELETE FROM "SYS"."IMPDP_STATS"; END; 

ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "HROPER"."HR_EMP_PK" does not exist or insufficient privileges
...

主键统计信息导入失败。
测试过程：
1、换一个表空间创建表

SQL>create table test01 (id number) tablespace hrtbs2;
Table created.
为该用户换一个表空间可以成功创建表

2、开启段的延时加载

SQL>create table test02(id number);
Table created.
使用默认表空间hrtbs可以成功创建表
sqlplus hroper/password
SQL> ALTER INDEX HROPER.HR_EMP_PK REBUILD ONLINE;
*
ALTER INDEX HROPER.HR_EMP_PK REBUILD ONLINE
ERROR at line 1:
ORA-00600; inrernal error,code, arguments: [ktssinseg3], [14], [134224922], []
[],[],[],[],[],[],[]
rebulid还是报ORA-600

3、关闭段的延时加载

conn / as  sysdba
SQL> alter system set deferred_segment_creation=false scope=both;
System altered.
sQL> show parameter deferred _segment_creation
NAME                                 TYPE          VALUE
--------------                --------------   -----------------
deferred_segment_creation          boolean         FALSE
sqlplus hroper/password
SQL>create table test03 (id number);
create table test03
*
ERROR at line 1:
ORA-00600; inrernal error,code, arguments: [ktssinseg3], [14], [134224922], []
[],[],[],[],[],[],[]
sqlplus hroper/password
SQL> ALTER INDEX HROPER.HR_EMP_PK REBUILD ONLINE;
*
ALTER INDEX HROPER.HR_EMP_PK REBUILD ONLINE
ERROR at line 1:
0RA-00600; inrernal rror,code, arguments: [ktssinseg3], [14], [134224922], []
[],[],[],[],[],[],[]

关闭段的延时加载创建表和rebulid索引报错与开始一致。

4、检查数据文件信息

SQL>select file#,rfile#,name,ts# from v$datafile where TS#=14;
FILE#     RFILE#              NAME                              TS#
67          117   +DATA/PROD/DATAFILE/hrtable_117.dbf            14 
68          106   +DATA/PROD/DATAFILE/hrtable_106.dbf            14
69          92    +DATA/PROD/DATAFILE/hrtable_92.dbf             14
70          51    +DATA/PROD/DATAFILE/hrtable_51.dbf             14
71          52    +DATA/PROD/DATAFILE/hrtable_52.dbf             14

5、查询对象和段信息

SQL>select owner,object_name from dba_objects where data_object_id=627319;
SQL>select owner,object_name from dba_objects where data_object_id=6677822
SQL>select owner,segment_name,SEGMENT_TYPE from dba_segments 
where TABLESPACE_NAME in (select name from ts$ where ts#=14) and HEADER_FILE=32 and HEADER_BLOCK=7194;

处理过程：
1、删除和重建索引

SQL>alter table EMPLOYESS drop constraint HR_EMP_PK;
SQL>alter table EMPLOYESS add constraint HR_EMP_PK primary key(emp_id);

2、创建表

SQL>create table test04(id number);
Table created.

可以正常创建表
3、rebulid索引

SQL> ALTER INDEX HROPER.HR_EMP_PK REBUILD ONLINE;
ERROR at line 1:
0RA-00600; inrernal rror,code, arguments: [[ktspffbmb:objdchk kcbnew-3]
[],[],[],[],[],[],[][],[],[]
rebulid索引报错信息发生了变化，通过以下方式处理
SQL>alter system flush shared_pool;
SQL>alter system flush buffer_cache;

SQL> ALTER INDEX HROPER.HR_EMP_PK REBUILD ONLINE;
ORA-08104: this index object 6670163 is being online built or rebuilt
说明：create /rebuild index online时会修改数据库字典表obj$,并在该索引用户下创建表sys_journal_obj#（具体的对象号）和在ind$、ind_online$表里(ind_online$字典基表记录了
ORA-08104处理办法：
declare
done boolean;
begin
   done:=dbms_repair.online_index_clean(6670163);---OBJECT_ID
end;
/

-the end-