oracle impdp全库导入报错总结 -m6米乐安卓版下载

问题表述：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语句创建。