..troubleshooting, Working case

IMP-00060:type does not exist or has different identifier

2条评论

今天在做一个数据库迁移的时候，在imp的时候出现了一个奇怪的报错：

……
. . importing table         "APPLY_SERV_BAK070713"       1542 rows imported
. . importing table         "APPLY_SERV_BAK070724"       1546 rows imported
. . importing table                "APPLY_SMS_MSG"         61 rows imported
. . importing table            "APPLY_SMS_MSG_HIS"      30274 rows imported
IMP-00060: Warning: Skipping table "APP_USER"."AQROUTERLOG_QT" because object type "SYS"."AQ$_JMS_USERPROPARRAY"  does not exist or has different identifier
. . importing table                     "ARCHIVES"          0 rows imported
. . importing table                 "ARCHIVESTYPE"          0 rows imported
……

当时觉得非常奇怪，查询了”SYS”.”AQ$_JMS_USERPROPARRAY” 这个object是存在的，所以提示中说的不存在应该是不可能，而且exp的时候，都是成功导出的：

.......
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

当时也没仔细看提示还说了“or has different identifier”，就重新导入了一次，但是还是报了同样的错误。

再次测试先建立其中的表 “APP_USER”.”AQROUTERLOG_QT”，再导入数据，仍旧发现还是没有数据导入：

[oracle@my_testdb01 data]$ sqlplus app_user/app_pwd

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 10 14:10:46 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> show user                          
USER is "APP_USER"
SQL> select count(*) from AQRouterLog_QT;

  COUNT(*)
----------
         0

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
[oracle@my_testdb01 data]$ imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y

Import: Release 9.2.0.6.0 - Production on Fri Aug 10 14:11:19 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00060: Warning: Skipping table "APP_USER"."AQROUTERLOG_QT" because object type "SYS"."AQ$_JMS_USERPROPARRAY"  does not exist or has different identifier
Import terminated successfully with warnings.
[oracle@my_testdb01 data]$ 
[oracle@my_testdb01 data]$ sqlplus app_user/app_pwd

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 10 14:12:07 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select count(*) from AQRouterLog_QT;

  COUNT(*)
----------
         0

SQL> exit

怎么办？！！

这个时候上网查了相关的资料，才发现无法导入是因为原数据库和目标数据”SYS”.”AQ$_JMS_USERPROPARRAY”这个TYPE的OID两边不一致，可以采用的解决方法有2种：
（1）手工将目标数据库的这个type的oid改成和原数据库一致的，这样在imp的时候，检验时会检验到两边oid一致，能够导入。但是这个方法适合type是自己写的，不是数据库的数据字典里面的。像我刚刚这个的报错，试图手工修改就报错了：

SQL> CREATE OR REPLACE TYPE "SYS"."AQ$_JMS_USERPROPARRAY" oid '1E5616FAE4A68D31E040C10A01006348'as varray(100) of aq$_jms_userproparray
  2  /
CREATE OR REPLACE TYPE "SYS"."AQ$_JMS_USERPROPARRAY" oid '1E5616FAE4A68D31E040C10A01006348'as varray(100) of aq$_jms_userproparray
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

SQL> select referenced_name,referenced_type from dba_dependencies
  2  where name='AQ$_JMS_USERPROPARRAY';

REFERENCED_NAME      REFERENCED_TYPE
-------------------- ------------------------------
STANDARD             PACKAGE
AQ$_JMS_USERPROPERTY TYPE

报错有相关的依赖对象，顺着依赖的对象一路查下去，就到了STANDARD 这个package了，这个包是数据库里面的包，粗粗检查了一下，没有发现可以有修改oid的地方，也就不去动了。

（2）另一种的解决方式是在imp的时候使用toid_novalidate参数。使用的格式是：

imp ......  TOID_NOVALIDATE=(owner.type1,owner.type2,owner.type3...)

因此在上面的情况，我们可以使用imp语句为：
imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y TOID_NOVALIDATE=(SYS.AQ\$_JMS_USERPROPARRAY)

在这里，我们要特别注意一个细节问题，在win下，我们可以使用TOID_NOVALIDATE=(SYS.AQ$_JMS_USERPROPARRAY)，但是在unix下，$会被解释成变量的提示符，因此我们必须使用转义字符“\”，写成TOID_NOVALIDATE=(SYS.AQ\$_JMS_USERPROPARRAY)！（之前由于没注意到这个问题，害我捣鼓了一下午 -_-||）。

####### 没用转义字符 \，从报错信息我们看到TOID_NOVALIDATE是AQ，不是AQ$_JMS_USERPROPARRAY
[oracle@my_testdb01 data]$ imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y TOID_NOVALIDATE=(SYS.AQ$_JMS_USERPROPARRAY)

Import: Release 9.2.0.6.0 - Production on Fri Aug 10 14:14:24 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00060: Warning: Skipping table "APP_USER"."AQROUTERLOG_QT" because object type "SYS"."AQ$_JMS_USERPROPARRAY"  does not exist or has different identifier
IMP-00086: TOID "AQ" not found in export file
Import terminated successfully with warnings.
[oracle@my_testdb01 data]$ 

###### 使用转义字符，原来的报错不再出现，但是又出现一个需要novalidate的type
[oracle@my_testdb01 data]$ imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y TOID_NOVALIDATE=(SYS.AQ\$_JMS_USERPROPARRAY)

Import: Release 9.2.0.6.0 - Production on Fri Aug 10 14:21:15 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . skipping TOID validation on type SYS.AQ$_JMS_USERPROPARRAY
IMP-00060: Warning: Skipping table "APP_USER"."AQROUTERLOG_QT" because object type "SYS"."AQ$_JMS_MESSAGE"  does not exist or has different identifier
Import terminated successfully with warnings.
[oracle@my_testdb01 data]$ 
[oracle@my_testdb01 data]$ 

###### 为2个type添加novalidate后，不再报错
[oracle@my_testdb01 data]$ imp app_user/app_user file=tb_AQROUTERLOG_QT.dmp fromuser=app_user touser=app_user ignore=y TOID_NOVALIDATE=(SYS.AQ\$_JMS_USERPROPARRAY,sys.AQ\$_JMS_MESSAGE)

Import: Release 9.2.0.6.0 - Production on Fri Aug 10 14:22:16 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . skipping TOID validation on type SYS.AQ$_JMS_USERPROPARRAY
. . skipping TOID validation on type SYS.AQ$_JMS_MESSAGE
. . importing table               "AQROUTERLOG_QT"        113 rows imported
Import terminated successfully without warnings.
[oracle@my_testdb01 data]$

至此，我们imp成功！

相关文章

2条评论

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。