11g通过xtts迁移升级到19c（u2l）ora-m6米乐安卓版下载

背景描述：某客户计划将一套重要业务系统数据库由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-