執行impdp時出現ORA-39154錯誤的解決案例

oliseh發表於2015-01-24


一次資料表的匯入匯出操作在使用impdp匯入的時候遇到了ORA-39154,花了點時間解決了。
下面我在測試環境裡真實還原了這個錯誤,並附上解決思路和方案


#####建立測試表,不過表上的索引建在另一個schema下
sqlplus ad/123456
create table adtab1 tablespace ts_pub as select * from all_users;
SQL> select count(*) from adtab1;


  COUNT(*)
----------
        45


sqlplus mng/789012
create index ind_adtab1_uid on ad.adtab1(user_id);
create index ind_adtab1_crt on ad.adtab1(created);


#####在expdp所連的源庫及impdp所連的目標庫上都要建立好Directory物件,並且賦予執行使用者ad對於directory的讀寫許可權
sqlplus '/as sysdba'
create or replace directory tmpdir as '/home/oracle/chh/';
grant read,write on directory tmpdir to ad;


#####以sysdba身份將表從源庫匯出
expdp \"/ as sysdba\" tables=ad.adtab1 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes


---匯出過程正常
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=ad.adtab1 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "AD"."ADTAB1"                               6.781 KB      45 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/chh/ad.adtab1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:17:27


#####將dmp檔案傳輸到目標庫後以ad使用者執行impdp
REVOKE IMP_FULL_DATABASE FROM AD;
impdp ad/123456 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log


---匯入過程中出現ORA-39154錯誤,提示匯入的內容裡包含有不屬於AD使用者的物件,這部分物件沒有能夠正常匯入,但ad.adtab1表已經匯入成功了
ORA-39154: Objects from foreign schemas have been removed from import
Master table "AD"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "AD"."SYS_IMPORT_FULL_01":  ad/******** directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AD"."ADTAB1"                               6.781 KB      45 rows
Job "AD"."SYS_IMPORT_FULL_01" successfully completed at 08:20:11


出錯原因分析:
因為匯入的內容裡包括了統計資訊,統計資訊的相關操作在匯入的過程中是在sys.impdp_stats表裡進行的(從後面impdp生成的sql指令碼里可以看出來),ad使用者需要賦予imp_full_database權限才能匯入這部分統計資訊,這應該就是ORA-39154的成因


---索引沒有匯入進去
SQL> select count(*) from adtab1;


  COUNT(*)
----------
        45


SQL> select index_name,table_name from dba_indexes where table_name='ADTAB1';


no rows selected


#####賦給ad使用者imp_full_database許可權後再次進行impdp,這回ORA-39083取代了ORA-39154,問題出在為MNG.IND_ADTAB1_UID、MNG.IND_ADTAB1_CRT兩個索引生成統計資訊時發現這兩個索引並不存在,至此我們才發現了索引和表不在同一個schema的問題:表在ad使用者下,而索引卻建在了mng使用者下,這可能是開發人員的一個失誤,我們暫且不討論這樣建索引是否合理。
grant imp_full_database to ad;


impdp ad/123456 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log
Master table "AD"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "AD"."SYS_IMPORT_FULL_01":  ad/******** directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AD"."ADTAB1"                               6.781 KB      45 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "MNG"."IND_ADTAB1_UID" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "MNG"."IND_ADTAB1_CRT" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:
Job "AD"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 08:43:01


---目標庫檢查確實只有表匯入了進來
SQL> select count(*) from adtab1;


  COUNT(*)
----------
        45


SQL> select index_name,table_name from dba_indexes where table_name='ADTAB1';


no rows selected


#####是否因為ad使用者是一個普通使用者沒有許可權在mng使用者下建索引?於是用sysdba身份再次執行impdp,報錯依舊
impdp \"/ as sysdba\" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AD"."ADTAB1"                               6.781 KB      45 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "MNG"."IND_ADTAB1_UID" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "MNG"."IND_ADTAB1_CRT" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 08:50:27


SQL> select count(*) from adtab1;


  COUNT(*)
----------
        45


SQL> select index_name,table_name from dba_indexes where table_name='ADTAB1';


no rows selected




使用sysdba使用者匯入時也報相同的錯誤,應該不是由於許可權問題引起的,報錯資訊出現在匯入索引統計資訊的階段,因為MNG使用者下的兩個索引不存在導致了ORA-39083,難道是匯出的dmp檔案裡壓根就沒有包含這兩個索引的資訊?
#####將impdp內容重定向到指令碼檔案,發現指令碼里確實沒有這兩個索引的DDL語句
impdp \"/ as sysdba\" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log sqlfile=ad.adtab1.sql


---ad.adtab1.sql內容,發現除了建表和匯入索引的統計資訊外,沒有create index的步驟
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "AD"."ADTAB1"
   (    "USERNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
        "USER_ID" NUMBER NOT NULL ENABLE,
        "CREATED" DATE NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_PUB" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60);
  I_O VARCHAR2(60);
  NV VARCHAR2(1);
  c DBMS_METADATA.T_VAR_COLL;
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:
9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  i_n := 'IND_ADTAB1_UID';
  i_o := 'MNG';
  EXECUTE IMMEDIATE stmt USING 0,I_N,NV,NV,I_O,45,1,45,1,1,1,0,45,NV,NV,TO_DATE('2015-01-24 08:16:45',df),NV;


  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/
DECLARE I_N VARCHAR2(60);
  I_O VARCHAR2(60);
  NV VARCHAR2(1);
  c DBMS_METADATA.T_VAR_COLL;
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:
9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS";
  i_n := 'IND_ADTAB1_CRT';
  i_o := 'MNG';
  EXECUTE IMMEDIATE stmt USING 0,I_N,NV,NV,I_O,45,1,39,1,1,1,0,45,NV,NV,TO_DATE('2015-01-24 08:16:45',df),NV;


  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
  DELETE FROM "SYS"."IMPDP_STATS";
END;
/


現在方向就很明確了:expdp階段如何把mng使用者下的索引也帶上
解決方案如下:
######把表和索引的名稱填上,Schemas必須帶上ad,mng這兩個使用者,如果Schema不指定那麼預設在SYS下去找,include的功能還是很強大的
expdp \"/ as sysdba\" schemas=ad,mng include=TABLE:\"=\'ADTAB1\'\",INDEX:\"IN \(\'IND_ADTAB1_UID\',\'IND_ADTAB1_CRT\'\)\" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes


---匯出過程正常
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" schemas=ad,mng include=TABLE:"='ADTAB1'",INDEX:"IN ('IND_ADTAB1_UID','IND_ADTAB1_CRT')" directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "AD"."ADTAB1"                               6.781 KB      45 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/chh/ad.adtab1.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:22:14


注:上述匯出操作如果由普通使用者操作,那麼必須賦予該使用者exp_full_database許可權,否則會觸發如下錯誤
ORA-39165: Schema MNG was not found.
ORA-39168: Object path INDEX was not found.


#####在匯入目標庫之前先將impdp的結果輸出到指令碼檔案
impdp ad/123456 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log sqlfile=ad.adtab1.sql


---ad.adtab1.sql檔案內容中已經包含了create index的步驟
。。。其它部分略
-- CONNECT MNG
CREATE INDEX "MNG"."IND_ADTAB1_UID" ON "AD"."ADTAB1" ("USER_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_PUB" PARALLEL 1 ;


  ALTER INDEX "MNG"."IND_ADTAB1_UID" NOPARALLEL;
CREATE INDEX "MNG"."IND_ADTAB1_CRT" ON "AD"."ADTAB1" ("CREATED")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_PUB" PARALLEL 1 ;


  ALTER INDEX "MNG"."IND_ADTAB1_CRT" NOPARALLEL;
。。。其它部分略  


#####最後執行匯入
---匯入前賦予imp_full_database較色給ad,因為要匯入的內容裡包含了其它Schema資訊
grant imp_full_database to ad;


---匯入成功
impdp ad/123456 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log
Master table "AD"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "AD"."SYS_IMPORT_FULL_01":  ad/******** directory=tmpdir dumpfile=ad.adtab1.dmp logfile=imp_ad.adtab1.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "AD"."ADTAB1"                               6.781 KB      45 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "AD"."SYS_IMPORT_FULL_01" successfully completed at 09:35:56


####結果檢查OK
set linesize 180


SQL> select count(*) from adtab1;


  COUNT(*)
----------
        45
        
select owner,index_name,status,table_owner,table_name from dba_indexes where table_name='ADTAB1';
OWNER                          INDEX_NAME                     STATUS   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ -------- ------------------------------ ------------------------------
MNG                            IND_ADTAB1_CRT                 VALID    AD                             ADTAB1
MNG                            IND_ADTAB1_UID                 VALID    AD                             ADTAB1




總結:本例中導致ORA-39154的根本問題在於ad表上的索引沒有建在ad使用者下,這樣的情況稱為cross schema references,即不同schema的物件間存在關聯,cross schema references導致的impdp錯誤還是比較隱蔽的,好在我們使用了sysdba許可權從源庫expdp匯出表,然後透過impdp時有關統計資訊無法匯入的ORA-39154錯誤,一步一步追溯直至發現索引和表不在同一個schema下,問題才得以精確定位。這個案例也告訴我們在table mode export的方式下,如果依賴於a.taba表的物件,比如基於a.taba的索引名為index_b,建在了b使用者下,那麼下面的命令在匯出結果裡不會包含b使用者下的索引
expdp user/passwd tables=a.taba directory=dirname dumpfile=a.taba.dmp logfile=exp_a.taba.dmp
必須使用schemas、include引數精確指定表名和索引名稱,例如:
expdp user/passwd schemas=a,b include=TABLE:\"=\'TABA\'\",INDEX:\"IN \(\'INDEX_B\'\)\" directory=dirname dumpfile=a.taba.dmp logfile=exp_a.taba.dmp


順便提一句,如果我們一開始從源庫匯出表的時候沒有像下面這樣使用sysdba許可權
expdp \"/ as sysdba\" tables=ad.adtab1 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
而是像下面這樣使用ad使用者
expdp ad/123456 tables=ad.adtab1 directory=tmpdir dumpfile=ad.adtab1.dmp logfile=exp_ad.adtab1.log reuse_dumpfiles=yes
那麼在之後impdp時是不會收到任何報錯的。這樣造成的結果卻很嚴重:目標庫上的表應該有索引的欄位卻沒有建索引。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/53956/viewspace-1412988/,如需轉載,請註明出處,否則將追究法律責任。

相關文章