impdp ORA-31655错误处理一例

今天上午,收到开发同事发过来的邮件:

需要将从之前用EXPDP备份的dump文件中,将某张表还原到一个新的schema下。

电话沟通后,原来是想要将逻辑备份的dump文件中FR8_ZH这个用户下的SB_DATA_RIGHT,导入到同库下的FR8_TEST9这个schema下。

①  于是开始干活:

C:\Documents and Settings\Administrator>impdp directory=my_dump dumpfile=2012-01
-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=SB_DATA_RIGHT
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:32:53
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31655: 尚未为作业选择数据或元数据对象

已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"

启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump
file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=S
B_DATA_RIGHT

作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:33:19 成功完成

发现,报出ORA-31655的错误!!!

SQL> !oerr ora 31655

31655, 00000, "no data or metadata objects selected for job"

// *Cause:  After the job parameters and filters were applied,

//          the job specified by the user did not reference any objects.

// *Action: Verify that the mode of the job specified objects to be moved.

//          For command line clients, verify that the INCLUDE, EXCLUDE and

//          CONTENT parameters were correctly set.  For DBMS_DATAPUMP API

//          users, verify that the metadata filters, data filters, and

//          parameters that were supplied on the job were correctly set.
SQL>

而ORA-31655的错误是说,在导入数据的命令中,impdp没有找到正确的对象元数据。

②  加上INCLUDE关键字,重新执行导入:

 
C:\Documents and Settings\Administrator>impdp directory="my_dump" dumpfile=2012-
01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=SB_DATA_RIG
HT include=table:\"like \'SB_DATA_RIGHT%\'\"
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:36:35
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31655: 尚未为作业选择数据或元数据对象
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"

启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump
file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=S
B_DATA_RIGHT include=table:"like \'SB_DATA_RIGHT%\'
"
作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:37:02 成功完成

发现依然报ORA-31655的错误!

③ 不得已,查找Metalink,ORA-31655 On imdp As a Privileged User Wih INCLUDE= After Upgrade To 10.2.0.5/11.2 [ID 1225108.1] 得到下述的解决办法:

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.5 and later   [Release: 10.2 and later ]
Oracle Server – Enterprise Edition – Version: 11.2.0.1.0 to 11.2.0.2.0   [Release: 11.2 to 11.2]
Information in this document applies to any platform.

 

Symptoms

Datapump import raises an ORA-31655 “no data or metadata objects selected for job” on import when using the REMAP_SCHEMA and INCLUDE= clauses when performed as a privileged user (i.e. a user with the IMP_FULL_DATABASE role).

Changes

The database has been upgraded to 10.2.0.5 or 11.2.

Cause

This is intended behavior and occurs due to the fix for bug:6831823 which went in to 11.2 and 10.2.0.5.

The ORA-31655 error is now signaled following the fix for bug:6831823, as that bug which existed in previous versions meant that if a privileged user performed a table import from a full export dump, and the specified table name existed in multiple schemas, then all the tables would be imported where as only the table for the importing user should have been imported, as documented in the Oracle� Database Utilities 10g Release 2 (10.2) manual, chapter 3 ‘Data Pump Import’, under the definition of the TABLES= clause, which states “If you do not supply a schema_name, it defaults to that of the current user”.  This also applies to the INCLUDE= clause, and hence the error is now raised.

Documentation bug:10140472 has been created to get this more clearly stated under the INCLUDE and REMAP_SCHEMA options in future documentation sets.

Solution

Perform the import using the TABLES= clause rather than the INCLUDE=TABLE: clause, e.g.:

 

Change From:

impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”

To:

impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test tables=SCOTT.EMP,SCOTT.DEPT

or 

impdp scott directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:\”IN \(\’EMP\’, \’DEPT\’\)\” 

找到问题的原因后,执行下述命令重新导入成功!

 
C:\Documents and Settings\Administrator>impdp directory="my_dump" dumpfile=2012-
01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=fr8_zh.sb_d
ata_right
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:48:02
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"
启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump
file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=f
r8_zh.sb_data_right

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

. . 导入了 "FR8_TEST9"."SB_DATA_RIGHT"                 5.328 MB    4484 行

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT

处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:48:49 成功完成

最后,登录数据库后,检查FR8_TEST9用户下的SB_DATA_RIGHT表中的数据,发现一切正常。

发表评论

邮箱地址不会被公开。 必填项已用*标注