IT博客汇 | [原]IMPDP 报错 ORA-31655: no data or metadata objects selected for job问题

[原]IMPDP 报错 ORA-31655: no data or metadata objects selected for job问题

mchdba发表于 2017-05-08 15:30:55

上周给开发小伙伴迁移了23张表到测试环境，今天小伙伴来找我了，说数据还是老的,比如PLAN6_NODE表线上有3W多条，测试环境还是2W多条，明显不对哈，不是线上的数据。我一check，确实数量不对，原因在哪里呢？

查看历史操作记录，找上次impdp操作的日志

[oracle@t_217 ~]$history |grep impdp |grep zhangorcl_20170505
  669  2017-05-05 15:54:45 time impdp \'stuc/testpd2015\' directory=DIR_DUMP_T1 table_exists_action=replace dumpfile=zhangorcl_20170505.dmp schemas=stuc LOGFILE=dw_20170505_1.log
  684  2017-05-08 15:05:58 history |grep impdp |grep zhangorcl_20170505
[oracle@t_217 ~]$

看impdp命令，后面带有schemas=stuc参数，查看操作日志，如下：

[oracle@t_217 ~]$more dw_20170505_1.log
;;; 
Import: Release 11.2.0.1.0 - Production on Fri May 5 15:54:45 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "stuc"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
ORA-39039: Schema expression "IN (SELECT object_name FROM "stuc"."SYS_IMPORT_SCHEMA_01" WHERE process_order = -56 AND duplicate BETWEEN 1 AND 1)" contains no valid schemas.
ORA-31655: no data or metadata objects selected for job
Starting "stuc"."SYS_IMPORT_SCHEMA_01":  "stuc/********" directory=DIR_DUMP_T1 table_exists_action=replace dumpfile=zhangorcl_20170505.dmp schemas=stuc LOGFILE=dw_20170505_1
.log 
Job "stuc"."SYS_IMPORT_SCHEMA_01" successfully completed at 15:54:49
[oracle@t_217 ~]$

分析，可能上周事情太多了，只看到后面的successfully completed ，而没有注意到前面的ORA-31655: no data or metadata objects selected for job。应该是备份文件dmp没有真正impdp导入进来。这个可能和expdp的模式有关，因为expdp的命令有tables=xxxx类似的参数设置

expdp命令如下:

expdp ‘stuc/”k3I_$p!stH403_21c”’@PD1 DIRECTORY=DIR_DUMP_01 DUMPFILE=zhangorcl_20170505.dmp tables=PLAN6_BUILDING,PLAN6_DELAY_NODE,PLAN6_HISTORY,PLAN6_INFO,PLAN6_MESSAGE,PLAN6_MESSAGE_SHARED,PLAN6_MESSAGE_USER_READ,PLAN6_NODE,PLAN6_NODE_AVERAGE,PLAN6_NODE_HAIYANG,PLAN6_NODE_LEVEL4_1213,PLAN6_NODE_TEMP,PLAN6_PROJECT,PLAN6_TEMPLET,PLAN6_TEMPLET_NODE,PLAN6_TEMPLET_NODE_EDIT,PLAN6_TEMPLET_NODE__TODEL,PLAN6_WARNING LOGFILE=BIS_CONT_BIS_STORE_IDS_161217.log;

而我impdp的时候，指定了schemas=stuc，结果导致报错，它会去比对sys里面的schema信息，因为oracle的impdp在这里以为是跨schema数据迁移，但是我这里impdp的时候是原始自己的stuc账号来进行impdp的，其实不需要再指定schema参数了，那么为了能真正顺利成功的impdp，就要去掉schema=stuc这个参数，试下效果吧

[oracle@t_217 ~]$time impdp \'stuc/testpd2015\' directory=DIR_DUMP_T1 table_exists_action=replace dumpfile=zhangorcl_20170505.dmp LOGFILE=dw_20170508_1.log

Import: Release 11.2.0.1.0 - Production on Mon May 8 14:50:34 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "stuc"."SYS_IMPORT_FULL_03" successfully loaded/unloaded
Starting "stuc"."SYS_IMPORT_FULL_03":  "stuc/********" directory=DIR_DUMP_T1 table_exists_action=replace dumpfile=zhangorcl_20170505.dmp LOGFILE=dw_20170508_1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "stuc"."PLAN6_WARNING"                 342.9 KB    3023 rows
. . imported "stuc"."PLAN6_NODE"                    7.996 MB   34351 rows
. . imported "stuc"."PLAN6_HISTORY"                 4.331 MB   26316 rows
. . imported "stuc"."PLAN6_MESSAGE_USER_READ"       4.091 MB   32835 rows
. . imported "stuc"."PLAN6_MESSAGE"                 3.266 MB   14152 rows
. . imported "stuc"."PLAN6_NODE_LEVEL4_1213"        969.3 KB    3928 rows
. . imported "stuc"."PLAN6_DELAY_NODE"              625.1 KB    4675 rows
. . imported "stuc"."PLAN6_MESSAGE_SHARED"          299.7 KB    2102 rows
. . imported "stuc"."PLAN6_NODE_AVERAGE"            93.70 KB     909 rows
. . imported "stuc"."PLAN6_NODE_TEMP"               74.01 KB     255 rows
. . imported "stuc"."PLAN6_TEMPLET_NODE"            85.38 KB     751 rows
. . imported "stuc"."PLAN6_INFO"                    37.89 KB     234 rows
. . imported "stuc"."PLAN6_NODE_HAIYANG"            30.85 KB      54 rows
. . imported "stuc"."PLAN6_PROJECT"                 20.21 KB      45 rows
. . imported "stuc"."PLAN6_TEMPLET_NODE_EDIT"       26.85 KB     474 rows
. . imported "stuc"."PLAN6_TEMPLET_NODE__TODEL"     33.39 KB     156 rows
. . imported "stuc"."PLAN6_BUILDING"                    0 KB       0 rows
. . imported "stuc"."PLAN6_TEMPLET"                     0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'ROLE_PL_READONLY' does not exist
Failing sql is:
GRANT SELECT ON "stuc"."PLAN6_NODE_HAIYANG" TO "ROLE_PL_READONLY"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'ROLE_DWETL' does not exist
Failing sql is:
GRANT SELECT ON "stuc"."PLAN6_NODE_HAIYANG" TO "ROLE_DWETL"
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "stuc"."SYS_IMPORT_FULL_03" completed with 99 error(s) at 14:50:58


real    0m25.493s
user    0m0.025s
sys 0m0.018s
[oracle@t_217 ~]$

然后一查询，测试环境数据也是3W多条了，数据正常了，这里一个经验结论就是：expdp后面如果是tables=tablename1,tablename2,tablename3…..这样的，impdp的时候如果用了它原始账号，就不需要加schema=schema1这样的参数了。