1

ora-利来国际app

jieguo 2021-11-09
161

关于
案例诊断:oracle anydata 数据类型存储的自定义类型丢失后ora-21700

提到的expdp 报错ora-21700: object does not exist or is marked for delete错误.

通过重建type跟踪数据字典insert可知需插入到如下表涉及oid变更:

alter session set events '10046 trace name context forever, level 12'; 
create or replace type jyc.t_stu as object (
  stu_num varchar2(10),
  stu_name varchar2(10)
);
/
alter session set events '10046 trace name context off'; 

将旧oid替换更新新建的type即可解决:

select type_name,type_oid from sys.dba_types as of timestamp to_timestamp(‘2021-11-09 10:41:18’,‘yyyy-mm-dd hh24:mi:ss’) where type_name=‘xxx’; --old xxx
select type_name,type_oid from sys.dba_types where type_name=‘xxx’;–new xxx

update sys.attribute$ where toid='old xxx' where toid='new xxx';
update sys.type$ where toid='old xxx',tvoid='old xxx' where toid='new xxx';
update sys.oid$ set oid$='old xxx' where oid$='new xxx';
update sys.kottd$ set sys_nc_oid$='old xxx' where sys_nc_oid$='new xxx';
update sys.kottb$ set sys_nc_oid$='old xxx' where sys_nc_oid$='new xxx';--无记录
update sys.kotad$ set sys_nc_oid$='old xxx' where sys_nc_oid$='new xxx';--无记录
update sys.kottbx$ set sys_nc_oid$='old xxx' where sys_nc_oid$='new xxx';--无记录

10:25:52 sql> select id,dump(msg,16) dump_v from jyc.test_anydata where id=5;

    id----------dump_v

     5

typ=58 len=77: 0,1,0,0,0,0,0,1,0,0,0,13,62,58,0,37,48,90,0,31,0,0,2d,1,85,1,2d,1,1,2,4,0,6c,d0,51,f6,bf,86,6d,e,8e,e0,53,81,34,a8,c0,76,7,0,1,0,0,0,0,d,84,1,fe,0,0,0,d,1,31,3,6a,79
,63,0,0,0,0,0,0,0,0

     5

typ=58 len=77: 0,1,0,0,0,0,0,1,0,0,0,13,62,59,0,37,48,90,0,31,0,0,2d,1,85,1,2d,1,1,2,4,0,6c,d0,51,f6,bf,86,6d,e,8e,e0,53,81,34,a8,c0,76,7,0,1,0,0,0,0,d,84,1,fe,0,0,0,d,1,31,3,6a,79
,63,0,0,0,0,0,0,0,0

10:26:01 sql>

另外跟踪报错的方法参考:

09:46:20 sql> alter session set events '21700 trace name errorstack forever,level 3';
session altered.
09:47:56 sql> select   sys.anydata.gettypename(msg) type_name,count(*) from jyc.test_anydata group by sys.anydata.gettypename(msg);
select   sys.anydata.gettypename(msg) type_name,count(*) from jyc.test_anydata group by sys.anydata.gettypename(msg)
                                                                                        *
error at line 1:
ora-21700: object does not exist or is marked for delete
ora-06512: at "sys.anydata", line 174
09:48:11 sql> alter session set events '21700 trace name errorstack off';
session altered.

在$oracle_base/diag/rdbms/trace/下xxx.trc

完整恢复测试记录:

[[email protected] ~]$ sqlplus / as sysdba
 
sql*plus: release 19.0.0.0.0 - production on tue nov 9 10:42:54 2021
version 19.3.0.0.0
 
利来娱乐 copyright (c) 1982, 2019, oracle.  all rights reserved.
 
 
connected to:
oracle database 19c enterprise edition release 19.0.0.0.0 - production
version 19.3.0.0.0
 
sql> alter session set container=jyc;
 
session altered.
 
sql> set line 160
sql> desc dba_types;
 name                                                                                      null?    type
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 owner                                                                                              varchar2(128)
 type_name                                                                                          varchar2(128)
 type_oid                                                                                           raw(16)
 typecode                                                                                           varchar2(128)
 attributes                                                                                         number
 methods                                                                                            number
 predefined                                                                                         varchar2(3)
 incomplete                                                                                         varchar2(3)
 final                                                                                              varchar2(3)
 instantiable                                                                                       varchar2(3)
 persistable                                                                                        varchar2(3)
 supertype_owner                                                                                    varchar2(128)
 supertype_name                                                                                     varchar2(128)
 local_attributes                                                                                   number
 local_methods                                                                                      number
 typeid                                                                                             raw(16)
 
sql> col type_name for a10
sql> select type_name,type_oid from dba_types where owner='jyc';
 
type_name  type_oid
---------- --------------------------------
t_stu1     d051f6bf86790e8ee0538134a8c07607
 
sql> type_nametype_nametype_nameselect type_name,type_oid from dba_types where ^c
 
sql> select type_name,type_oid from dba_types where type_oid='d051f6bf866d0e8ee0538134a8c07607';
 
no rows selected
 
sql> select type_name,type_oid from dba_types where type_oid='d051f6bf86720e8ee0538134a8c07607';
 
no rows selected
 
sql> select type_name,type_oid from dba_types where type_oid='d051f6bf86790e8ee0538134a8c07607';
 
type_name  type_oid
---------- --------------------------------
t_stu1     d051f6bf86790e8ee0538134a8c07607
 
sql> 
sql> 
sql> 
sql> 
sql> select oid$ from sys.oid$ where oid$='d051f6bf86790e8ee0538134a8c07607';
 
oid$
--------------------------------
d051f6bf86790e8ee0538134a8c07607
 
sql> select oid$ from sys.oid$ where oid$='d051f6bf866d0e8ee0538134a8c07607';
 
oid$
--------------------------------
d051f6bf866d0e8ee0538134a8c07607
 
sql> select oid$ from sys.oid$ where oid$='d051f6bf86720e8ee0538134a8c07607';
 
no rows selected
 
sql> desc oid$
 name                                                                                      null?    type
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 user#                                                                                     not null number
 oid$                                                                                      not null raw(16)
 obj#                                                                                      not null number
 index#                                                                                    not null number
 
sql> select toid from attribute$ where toid='d051f6bf86720e8ee0538134a8c07607';
 
no rows selected
 
sql>  select toid from attribute$ where toid='d051f6bf866d0e8ee0538134a8c07607';
 
toid
--------------------------------
d051f6bf866d0e8ee0538134a8c07607
d051f6bf866d0e8ee0538134a8c07607
 
sql> select toid from attribute$ where toid='d051f6bf86790e8ee0538134a8c07607';
 
toid
--------------------------------
d051f6bf86790e8ee0538134a8c07607
d051f6bf86790e8ee0538134a8c07607
 
sql> select sys.anydata.gettypename(msg) type_name,count(*) from jyc.test_anydata group by sys.anydata.gettypename(msg);
 
type_name    count(*)
---------- ----------
sys.number          2
jyc.t_stu1          1
sys.varcha          1
r2
 
sys.date            2
jyc.t_stu           2
 
sql> drop type jyc.t_stu1;
 
type dropped.
 
sql> select sys.anydata.gettypename(msg) type_name,count(*) from jyc.test_anydata group by sys.anydata.gettypename(msg);
select sys.anydata.gettypename(msg) type_name,count(*) from jyc.test_anydata group by sys.anydata.gettypename(msg)
                                                                                      *
error at line 1:
ora-21700: object does not exist or is marked for delete
ora-06512: at "sys.anydata", line 174
 
 
sql> select * from jyc.test_anydata;
error:
ora-21700: object does not exist or is marked for delete
 
 
 
no rows selected
 
sql> select toid from attribute$ where toid='d051f6bf86790e8ee0538134a8c07607';
 
no rows selected
 
sql> create or replace type jyc.t_stu1 as object (
  2    stu_num varchar2(10),
  3    stu_name varchar2(10)
  4  );
  5  /
 
type created.
 
sql> select type_name,type_oid from dba_types where owner='jyc';
 
type_name  type_oid
---------- --------------------------------
t_stu1     d0532d42897b493be0538134a8c01bd4
 
sql> select type_name,type_oid from dba_types where type_oid='d0532d42897b493be0538134a8c01bd4';
 
type_name  type_oid
---------- --------------------------------
t_stu1     d0532d42897b493be0538134a8c01bd4
 
sql> select type_name,type_oid from sys.dba_types as of timestamp to_timestamp('2021-11-09 10:41:18','yyyy-mm-dd hh24:mi:ss') where type_name='t_stu1';
 
type_name  type_oid
---------- --------------------------------
t_stu1     d051f6bf86790e8ee0538134a8c07607
 
sql> select type_name,type_oid from sys.dba_types where type_name='t_stu1';
 
type_name  type_oid
---------- --------------------------------
t_stu1     d0532d42897b493be0538134a8c01bd4
 
sql> update sys.type$ set toid='d051f6bf86790e8ee0538134a8c07607',tvoid='d051f6bf86790e8ee0538134a8c07607' where toid='d0532d42897b493be0538134a8c01bd4';
 
1 row updated.
 
sql> update sys.oid$ set oid$='d051f6bf86790e8ee0538134a8c07607' where oid$='d0532d42897b493be0538134a8c01bd4';
 
1 row updated.
 
sql> update sys.kottd$ set sys_nc_oid$='d051f6bf86790e8ee0538134a8c07607' where sys_nc_oid$='d0532d42897b493be0538134a8c01bd4';
 
1 row updated.
 
sql> update sys.kottb$ set sys_nc_oid$='d051f6bf86790e8ee0538134a8c07607' where sys_nc_oid$='d0532d42897b493be0538134a8c01bd4';
 
0 rows updated.
 
 
sql> update sys.kotad$ set sys_nc_oid$='d051f6bf86790e8ee0538134a8c07607' where sys_nc_oid$='d0532d42897b493be0538134a8c01bd4';
 
0 rows updated.
 
sql> update sys.kottbx$ set sys_nc_oid$='d051f6bf86790e8ee0538134a8c07607' where sys_nc_oid$='d0532d42897b493be0538134a8c01bd4';
 
0 rows updated.
 
sql> commit;
 
commit complete.
 
sql> update attribute$ set toid='d051f6bf86790e8ee0538134a8c07607' where toid='d0532d42897b493be0538134a8c01bd4';
 
2 rows updated.
 
sql> commit;
 
commit complete.
 
sql> select * from jyc.test_anydata;
 
        id
----------
msg()
----------------------------------------------------------------------------------------------------------------------------------------------------------------
         1
anydata()
 
         2
anydata()
 
         3
anydata()
 
 
        id
----------
msg()
----------------------------------------------------------------------------------------------------------------------------------------------------------------
         4
anydata()
 
         5
anydata()
 
         5
anydata()
 
 
        id
----------
msg()
----------------------------------------------------------------------------------------------------------------------------------------------------------------
         4
anydata()
 
         7
anydata()
 
 
8 rows selected.
 
sql> select sys.anydata.gettypename(msg) type_name,count(*) from jyc.test_anydata group by sys.anydata.gettypename(msg);
 
type_name    count(*)
---------- ----------
sys.number          2
jyc.t_stu1          1
sys.varcha          1
r2
 
sys.date            2
jyc.t_stu           2
disconnected from oracle database 19c enterprise edition release 19.0.0.0.0 - production
version 19.3.0.0.0
[[email protected] ~]$ expdp jyc/[email protected] dumpfile=t2.dmp logfile=t2.log directory=dmp tables=jyc.test_anydata
 
export: release 19.0.0.0.0 - production on tue nov 9 11:16:25 2021
version 19.3.0.0.0
 
利来娱乐 copyright (c) 1982, 2019, oracle and/or its affiliates.  all rights reserved.
 
connected to: oracle database 19c enterprise edition release 19.0.0.0.0 - production
flashback automatically enabled to preserve database integrity.
starting "jyc"."sys_export_table_01":  jyc/********@jyc dumpfile=t2.dmp logfile=t2.log directory=dmp tables=jyc.test_anydata 
processing object type table_export/table/table_data
processing object type table_export/table/index/statistics/index_statistics
processing object type table_export/table/statistics/table_statistics
processing object type table_export/table/statistics/marker
processing object type table_export/table/procact_instance
processing object type table_export/table/table
. . exported "jyc"."test_anydata"                        6.109 kb       8 rows
master table "jyc"."sys_export_table_01" successfully loaded/unloaded
******************************************************************************
dump file set for jyc.sys_export_table_01 is:
  /home/oracle/dmp/t2.dmp
job "jyc"."sys_export_table_01" successfully completed at tue nov 9 11:16:35 2021 elapsed 0 00:00:09
最后修改时间:2021-11-09 15:13:45
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论