impdp全库导入报错总结

问题表述：impdp全库导入时，会遇到大量报错，有些报错可以忽略，有些报错需要处理，做个总结。

操作系统：aix–>linux

数据库版本：11.1.0.7–>11.2.0.4

操作：impdp

参考文档：详见各具体报错

详细报错及处理过程：

报错1： Cannot set an SCN larger than the current SCN

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
>>> Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropped and recreated. See My Oracle Support article number 1380295.1.

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
复制

解决：

添加参数STREAMS_CONFIGURATION=N

报错2：ORA-39083 ORA-01031

ORA-39083: Object type TABLE:"ORDDATA"."ORDDCM_DOCS_TMP" failed to create with error:
ORA-01031: insufficient privileges

Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "ORDDATA"."ORDDCM_DOCS_TMP" ("DOC_ID" NUMBER(*,0) NOT NULL ENABLE, "DOC_NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE, "DOC_TYPE_ID" NUMBER NOT NULL ENABLE, "DOC_CONTENT" "SYS"."XMLTYPE"  NOT NULL ENABLE, "ORACLE_INSTALL" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, "CREATE_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE) ON COMMIT PRESERVE ROWS
复制

解决：

忽略，也可以手动执行失败的sql，可以执行成功。

参考：ORA-39083/ORA-1031 While Importing An ORDDATA Table (Doc ID 1909772.1)

报错3：ORA-39083 ORA-23327

ORA-39083: Object type PRE_TABLE_ACTION failed to create with error:
ORA-23327: imported deferred rpc data does not match GLOBAL NAME of importing db
复制

解决：

源库和目标库的global_name不一致。导入的时候，重新设置global_name，导入完成后，再改回去。或者可以忽略。

参考：IMPDP - ORA-23327 (does Not Match GLOBAL NAME) On PRE_TABLE_ACTION (Doc ID 1568721.1)

报错4：ORA-39117 ORA-39083 ORA-31000

ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "AR"."AR_REV_REC_QT" ("Q_NAME" VARCHAR2(30 BYTE), "MSGID" RAW(16), "CORRID" VARCHAR2(128 BYTE), "PRIORITY" NUMBER, "STATE" NUMBER, "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO" TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN" NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" NUMBER, "ENQ_TID" VARCHAR2(30 BYTE), "DEQ_TIME" TIMESTAMP (6), "DEQ_UID" NUMBER, "DEQ_TID

ORA-39083: Object type TABLE:"AZ"."AZ_REQUESTS" failed to create with error:
ORA-31000: Resource 'http://isetup.oracle.com/2006/selectionsets.xsd' is not an XDB schema document

Failing sql is:
CREATE TABLE "AZ"."AZ_REQUESTS" ("JOB_NAME" VARCHAR2(45 BYTE) NOT NULL ENABLE, "REQUEST_TYPE" VARCHAR2(1 BYTE) NOT NULL ENABLE, "USER_ID" NUMBER(15,0) NOT NULL ENABLE, "REQUEST_ID" NUMBER(15,0) NOT NULL ENABLE, "INSTANCE_NAME" VARCHAR2(45 BYTE) NOT NULL ENABLE, "JOB_DESC" VARCHAR2(1800 BYTE), "PREVIOUS_REQ_IDS" VARCHA

ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "CS"."CS_SERVICE_REQUEST_IQT" ("Q_NAME" VARCHAR2(30 BYTE), "MSGID" RAW(16), "CORRID" VARCHAR2(128 BYTE), "PRIORITY" NUMBER, "STATE" NUMBER, "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO" TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN" NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" NUMBER, "ENQ_TID" VARCHAR2(30 BYTE), "DEQ_TIME" TIMESTAMP (6), "DEQ_UID" NUMBER,

………    --总共40个失败语句
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
复制

解决：

经分析，报错语句的定义，有用到system、sys用户的自定义对象，而目标库没有自定义对象，由于导出导入时排除掉system、sys、MDSYS等系统用户，导致建表失败。重新导出，不用排除系统用户，再次重新导入，该报错消失。

报错5：导入时后台alert有告警

导入时，alert告警

Thu Nov 18 11:23:51 2021
The value (225) of MAXTRANS parameter ignored.
复制

解决：

bug ，不需处理。导出导入没有什么影响，可以忽略。

参考：ORA-39083/ORA-1031 While Importing An ORDDATA Table (Doc ID 1909772.1)

报错6：ORA-00955

Processing object type DATABASE_EXPORT/SCHEMA/LIBRARY/LIBRARY
ORA-39083: Object type LIBRARY failed to create with error:
ORA-00955: name is already used by an existing object
Failing sql is:
CREATE LIBRARY "DMSYS"."DMUTIL_LIB" TRUSTED AS STATIC
ORA-39083: Object type LIBRARY failed to create with error:
ORA-00955: name is already used by an existing object
Failing sql is:
CREATE LIBRARY "DMSYS"."DMSVM_LIB" TRUSTED AS STATIC
ORA-39083: Object type LIBRARY failed to create with error:
ORA-00955: name is already used by an existing object
复制

解决：

系统用户对象，新库已存在，所以导入报错，这种报错在ful=y方式导入的时候会遇到很多，包括ORA-29364、ORA-29357等等。对比对象检查无误后，忽略。

报错7：ORA-31693 ORA-39779

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "BOM"."BOM_COMPONENTS_B" 256.9 MB 8421374 rows
ORA-31693: Table data object "APPLSYS"."WF_NOTIFICATION_OUT" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-39779: type "SYS"."AQ$_JMS_TEXT_MESSAGE" not found or conversion to latest version is not possible
. . imported "BOM"."BOM_OPERATION_SEQUENCES" 306.9 MB 13609281 rows
。。。。。。
. . imported "APPLSYS"."AQ$_FND_CP_GSM_OPP_AQTBL_T" 12.51 MB 678228 rows
ORA-31693: Table data object "APPLSYS"."WF_JAVA_DEFERRED" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-39779: type "SYS"."AQ$_JMS_TEXT_MESSAGE" not found or conversion to latest version is not possible
. . imported "GL"."XLA_GLT_1198707" 9.822 MB 321263 rows
。。。。。。。
复制

解决：

表APPLSYS.WF_NOTIFICATION_OUT数据未导入。检查源端和目标端的对象及相关信息，除了目标端没有对sys.AQ_JMS_TEXT_MESSAGE的权限外，无其他异常。查询mos，指出错误的原因是源数据库和目标数据库之间类型对象 SYS.AQ_JMS_TEXT_MESSAGE 的哈希码不匹配。

处理：导入时添加选项 TRANSFORM=oid:n

参考：ORA-39779 on SYS.AQ$_JMS_TEXT_MESSAGE During IMPDP (Doc ID 2103360.1)

报错8：ORA-01452 cannot CREATE UNIQUE INDEX; duplicate keys found

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
Failing sql is:
CREATE UNIQUE INDEX "ICX"."ICX_SESSION_ATTRIBUTES_U1" ON "ICX"."ICX_SESSION_ATTRIBUTES" ("SESSION_ID", "NAME") PCTFREE 10 INITRANS 11 MAXTRANS 255 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "APPS_TS_TX_IDX" PARALLEL 1
ORA-31685: Object type INDEX:"CUX"."QA_RESULTS_N16" failed due to insufficient privileges. Failing sql is:
CREATE INDEX "CUX"."QA_RESULTS_N16" ON "QA"."QA_RESULTS" ("PLAN_ID", "CHARACTER4"，·····
。。。。。。。
复制

解决：

手动创建第一个索引成功。

第二个索引，由于表里已存在重复数据，导致无法创建唯一索引，跟业务沟通后，该索引由业务处理。

报错9：ORA-39082

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type ALTER_FUNCTION:"xxxxx"."xxxxx" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"xxxxx"."xxxxx" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"xxxxx"."xxxxx" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"xxxxx"."xxxxx" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"xxxxx"."xxxxx" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"xxxxx"."xxxxx" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"xxxxx"."xxxxx" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"xxxxx"."xxxxx" created with compilation warnings
。。。。。。。
复制

解决：

函数失效，该问题后续处理无效对象时处理。可通过批量重新编译失效对象处理。

与函数失效报错类似的，后面还有存储过程、触发器、视图、包、包体、同义词等等，该类问题可放在数据对比及失效对象处理过程里面处理。

报错10：ORA-00907

ORA-39083: Object type VIEW failed to create with error:
ORA-00923: FROM keyword not found where expected
Failing sql is:
CREATE FORCE VIEW "xxxxx"."xxxxx" ("xxxxxx", "xxxxx", 
ORA-39083: Object type VIEW failed to create with error:
ORA-00933: SQL command not properly ended
Failing sql is:
CREATE FORCE VIEW "xxxxx"."xxxxx" ("xxxxx", "xxxxx", 
ORA-39083: Object type VIEW failed to create with error:
ORA-00907: missing right parenthesis
Failing sql is:
CREATE FORCE VIEW "xxxxx"."xxxxx" ("xxxxx", "xxxxx", "xxxxx"Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-54015: Duplicate column expression was specified
Failing sql is:
CREATE UNIQUE INDEX "xxxxx"."I_SNAP$xxxxx" ON "xxxxx"."xxxxx" (xxxxx("xxxxx"), 
..........
ORA-39083: Object type INDEX failed to create with error:
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"xxxxx"."xxxxx" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"xxxxx"."xxxxx" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"xxxxx"."xxxxx" creation failed
。。。。。。。
复制

解决：

获取报错索引的ddl语句，手动创建。

set long 99999999
select dbms_metadata.get_ddl('INDEX','&indexname','&owner') from dual;

DBMS_METADATA.GET_DDL('INDEX','tablename','owner')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX APPS."I_SNAP$_FEM_BAL_NACC_HIER_1"
ON APPS.FEM_BAL_NACC_HIER_L2_MV
(
xxxxx("col1"),
xxxxx("col2"),
xxxxx("col3"),
xxxxx("col4"),
........
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE tablestapcename ;
复制

手动创建报错：

ERROR at line 1:
ORA-54015: Duplicate column expression was specified
复制

原因是11GR2以后不允许创建重复列的函数索引了。去掉重复的列，创建成功。同理，后面报错的索引也去除掉重复的列再次手动创建。

与该问题类似的，还有创建视图时，11GR2 group by的限制加强，导致低版本的部分视图定义无法在高版本创建成功，需人工分析，手动修改ddl语句创建。