impdp全庫導(dǎo)入報(bào)錯(cuò)總結(jié)

問題表述：impdp全庫導(dǎo)入時(shí)，會(huì)遇到大量報(bào)錯(cuò)，有些報(bào)錯(cuò)可以忽略，有些報(bào)錯(cuò)需要處理，做個(gè)總結(jié)。

操作系統(tǒng)：aix–>linux

數(shù)據(jù)庫版本：11.1.0.7–>11.2.0.4

操作：impdp

參考文檔：詳見各具體報(bào)錯(cuò)

詳細(xì)報(bào)錯(cuò)及處理過程：

報(bào)錯(cuò)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

解決：

添加參數(shù)STREAMS_CONFIGURATION=N

報(bào)錯(cuò)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

解決：

忽略，也可以手動(dòng)執(zhí)行失敗的sql，可以執(zhí)行成功。

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

報(bào)錯(cuò)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

解決：

源庫和目標(biāo)庫的global_name不一致。導(dǎo)入的時(shí)候，重新設(shè)置global_name，導(dǎo)入完成后，再改回去?；蛘呖梢院雎?。

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

報(bào)錯(cuò)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個(gè)失敗語句
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

解決：

經(jīng)分析，報(bào)錯(cuò)語句的定義，有用到system、sys用戶的自定義對(duì)象，而目標(biāo)庫沒有自定義對(duì)象，由于導(dǎo)出導(dǎo)入時(shí)排除掉system、sys、MDSYS等系統(tǒng)用戶，導(dǎo)致建表失敗。重新導(dǎo)出，不用排除系統(tǒng)用戶，再次重新導(dǎo)入，該報(bào)錯(cuò)消失。

報(bào)錯(cuò)5：導(dǎo)入時(shí)后臺(tái)alert有告警

導(dǎo)入時(shí)，alert告警

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

解決：

bug ，不需處理。導(dǎo)出導(dǎo)入沒有什么影響，可以忽略。

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

報(bào)錯(cuò)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

解決：

系統(tǒng)用戶對(duì)象，新庫已存在，所以導(dǎo)入報(bào)錯(cuò)，這種報(bào)錯(cuò)在ful=y方式導(dǎo)入的時(shí)候會(huì)遇到很多，包括ORA-29364、ORA-29357等等。對(duì)比對(duì)象檢查無誤后，忽略。

報(bào)錯(cuò)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數(shù)據(jù)未導(dǎo)入。檢查源端和目標(biāo)端的對(duì)象及相關(guān)信息，除了目標(biāo)端沒有對(duì)sys.AQ_JMS_TEXT_MESSAGE的權(quán)限外，無其他異常。查詢mos，指出錯(cuò)誤的原因是源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫之間類型對(duì)象 SYS.AQ_JMS_TEXT_MESSAGE 的哈希碼不匹配。

處理：導(dǎo)入時(shí)添加選項(xiàng) TRANSFORM=oid:n

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

報(bào)錯(cuò)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"，·····
。。。。。。。

解決：

手動(dòng)創(chuàng)建第一個(gè)索引成功。

第二個(gè)索引，由于表里已存在重復(fù)數(shù)據(jù)，導(dǎo)致無法創(chuàng)建唯一索引，跟業(yè)務(wù)溝通后，該索引由業(yè)務(wù)處理。

報(bào)錯(cuò)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
。。。。。。。

解決：

函數(shù)失效，該問題后續(xù)處理無效對(duì)象時(shí)處理?？赏ㄟ^批量重新編譯失效對(duì)象處理。

與函數(shù)失效報(bào)錯(cuò)類似的，后面還有存儲(chǔ)過程、觸發(fā)器、視圖、包、包體、同義詞等等，該類問題可放在數(shù)據(jù)對(duì)比及失效對(duì)象處理過程里面處理。

報(bào)錯(cuò)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
。。。。。。。

解決：

獲取報(bào)錯(cuò)索引的ddl語句，手動(dòng)創(chuàng)建。

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 ;

手動(dòng)創(chuàng)建報(bào)錯(cuò)：

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

原因是11GR2以后不允許創(chuàng)建重復(fù)列的函數(shù)索引了。去掉重復(fù)的列，創(chuàng)建成功。同理，后面報(bào)錯(cuò)的索引也去除掉重復(fù)的列再次手動(dòng)創(chuàng)建。

與該問題類似的，還有創(chuàng)建視圖時(shí)，11GR2 group by的限制加強(qiáng)，導(dǎo)致低版本的部分視圖定義無法在高版本創(chuàng)建成功，需人工分析，手動(dòng)修改ddl語句創(chuàng)建。