ORA-31693;ORA-31617;ORA-19505;ORA-27037

今日闲来无事，写写上个月底的一天晚上数据库迁移时碰到的问题，在此做个总结。

OS环境：Linux-x86_64

数据库版本：oracle 11.2.0.3.0  RAC

导出脚本如下：

expdp pda/pda620@outpda2 directory=dat_dir dumpfile=dat_dir:emspda1%U.dmp dumpfile=dat_dir:emspda2%U.dmp dumpfile=dat_dir:emspda3%U.dmp schemas=pda  filesize=30g logfile=dat_dir:pda0628.log    parallel=4

导出时日志中报错信息如下：

ORA-31693: Table data object "pda"."TB_POSTCAR_MAIL_SECTION_STORE":"P20130601" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/home/oracle/expdp_dir/pda701.dmp" for write
ORA-19505: failed to identify file "/home/oracle/expdp_dir/pda701.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "pda"."TB_POSTCAR_MAIL_SECTION_STORE":"P20130606" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/home/oracle/expdp_dir/pda102.dmp" for write
ORA-19505: failed to identify file "/home/oracle/expdp_dir/pda102.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . exported "pda"."TB_MAIL_SECTION_STORE_HIST":"P20120925"  7.316 MB   76506 rows
ORA-31693: Table data object "pda"."TB_POSTCAR_MAIL_SECTION_STORE":"P20130605" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/home/oracle/expdp_dir/pda801.dmp" for write
ORA-19505: failed to identify file "/home/oracle/expdp_dir/pda801.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . exported "pda"."TB_MAIL_SECTION_STORE_HIST":"P20120930"  7.231 MB   75583 rows
. . exported "pda"."TB_P_CONFIG_DELIVER_SECTION_1"    6.831 MB  147617 rows
. . exported "pda"."TB_P_DELIVER_SECTION_NEW"         7.237 MB  136962 rows
. . exported "pda"."TB_P_DELIVER_SECTION_NEW_1_B"     6.892 MB  132718 rows
. . exported "pda"."TB_AVIATION_ARRIVED_INFO_ALL":"P20130610"  18.35 KB       6 rows
. . exported "pda"."TB_AVIATION_DEPARTED_INFO_ALL":"P20130618"  52.10 KB     174 rows
. . exported "pda"."TB_BUSI_PERF":"P20130610"         227.8 KB    4807 rows
. . exported "pda"."TB_BUSI_PERF":"P20130612"         79.96 KB    1667 rows

这个导出脚本是我之前到了n次的脚本，之前也是在相同的服务器，相同哦数据库下导出的，之前这么做就是为了演练那天晚上的迁移，以防止出现异常情况。

唯一的区别就是这次我添加了一个参数：parallel=4，也许就是这个参数导致的。但是我之前在10g的数据库上也使用过这个参数，当时非常的顺利，为何11g就不行了呢。查看oracle11g的官方文档：

Using PARALLEL During An Export In An Oracle RAC Environment

In an Oracle Real Application Clusters (Oracle RAC) environment, if an export operation has PARALLEL=1, then all Data Pump processes reside on the instance where the job is started. Therefore, the directory object can point to local storage for that instance.

If the export operation has PARALLEL set to a value greater than 1, then Data Pump processes can reside on instances other than the one where the job was started. Therefore, the directory object must point to shared storage that is accessible by all instances of the Oracle RAC.

看来是导出文件的目录不是两个节点共享的原因。要想创建一个共享的目录，需要一块单独的盘，一般采用NFS来创建，通过和系统组的同事沟通后，没有这样的磁盘用来做nfs。但是现在数据库迁移已经进行在一半，答应客户3个小时内完成的，否则就会影响业务，要么就回退。回退意味着第二天晚上还得加班。难道就没有别的办法了吗？

在这样紧急的情况下我出去洗了个脸，冷静了一下，认为这个完全是11g为了提升rac环境中的导出性能，在并行情况下才需要多个实例来工作。

那我只在单实例上操作不就行了吗？查看expdp的help，发现有个参数cluster。这个参数可以控制是否采用多实例导出。设置cluster=n后，即：

expdp pda/pda620@outpda2 directory=dat_dir dumpfile=dat_dir:emspda1%U.dmp dumpfile=dat_dir:emspda2%U.dmp dumpfile=dat_dir:emspda3%U.dmp schemas=pda filesize=30g logfile=dat_dir:pda0628.log parallel=4 cluster=n

执行后导出正常。没有报错！

 

在后面的导入过程也同样设置这个参数，导入正常！问题解决。保证了在规定的时间内完成数据库迁移。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12129601/viewspace-767112/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12129601/viewspace-767112/