ITPub博客

首页 > 数据库 > Oracle > 执行impdp时出现ORA-39154错误的解决案例

执行impdp时出现ORA-39154错误的解决案例

原创 Oracle 作者:oliseh 时间:2015-01-24 11:02:04 0 删除 编辑


一次数据表的导入导出操作在使用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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1616696