如何在 ASM 存储中使用 Data Pump （expdp impdp）进行数据导出导入操作

如果在 Oracle Automatic Storage Management (Oracle ASM) 存储中使用 Data Pump 进行导入或导出操作, 必须使用
Oracle ASM 磁盘组的名称（而非操作系统目录路径名称）定义用于存放 dump 文件的 directory 对象，还要创建一个指向
操作系统目录路径的单独 directory 对象用于存放 data pump job 的日志文件。
示例：
在ASM中创建用于存放 dump 文件的 directory 对象；

22:09:58 sys@RAC> conn system/oracle
Connected.
22:10:04 system@RAC> CREATE or REPLACE DIRECTORY dp_dir as '+FRA/';
Directory created.

在操作系统目录路径中创建用于存放日志文件的 directory 对象：
[oracle@rac1 ~]$ mkdir /home/oracle/backup
22:10:15 system@RAC> CREATE or REPLACE DIRECTORY dp_log as '/home/oracle/backup';
Directory created.

授予 scott 用户访问上述目录对象的相应权限以及执行 data pump 任务的相应权限：

22:16:01 sys@RAC>  GRANT READ, WRITE ON DIRECTORY dp_dir TO scott;
Grant succeeded.

22:16:08 sys@RAC> GRANT READ, WRITE ON DIRECTORY dp_log TO scott;
Grant succeeded.

执行 expdp 导出 scott 用户下的 emp 表
expdp scott/tiger tables=scott.emp DIRECTORY=dp_dir DUMPFILE=emp.dmp LOGFILE=dp_log:emp.log

这里我们直接用 system 用户进行了导出操作

[oracle@rac1 ~]$ expdp system/oracle tables=scott.emp DIRECTORY=dp_dir DUMPFILE=emp.dmp LOGFILE=dp_log:emp.log
Export: Release 11.2.0.3.0 - Production on Tue May 21 22:24:22 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** tables=scott.emp DIRECTORY=dp_dir DUMPFILE=emp.dmp LOGFILE=dp_log:emp.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  +FRA/emp.dmp
  
[oracle@rac1 backup]$ pwd
/home/oracle/backup
[oracle@rac1 backup]$ ls
emp.log

假设：能否将日志文件直接存放在 ASM directory 中，而不单独创建操作系统目录路径的 directory ？
我们先试试？

[oracle@rac1 ~]$ expdp system/oracle tables=scott.emp DIRECTORY=dp_dir DUMPFILE=emp2.dmp EXCLUDE=INDEX,STATISTICS LOGFILE=dp_dir:emp.log
Export: Release 11.2.0.3.0 - Production on Tue May 21 22:32:44 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

从上面的错误就可以看出无法将 data pump 日志文件存放在 ASM 中，其实即使可以也无意义，因为存放在 ASM 中也不便于查看。

如何将expdp导出的 dump 文件移动到操作系统文件系统中呢？
这里我们使用 dbms_file_transfer.copy_file 包实现 ASM 和文件系统之间的拷贝：

DBMS_FILE_TRANSFER.COPY_FILE(
   source_directory_object       IN  VARCHAR2,
   source_file_name              IN  VARCHAR2,
   destination_directory_object  IN  VARCHAR2,
   destination_file_name         IN  VARCHAR2);

方法如下：
首先创建所需的 directory 对象

CREATE or REPLACE DIRECTORY asm_dir as '+FRA/'; --区别于前面的演示过程
CREATE or REPLACE DIRECTORY fs_dir as '/home/oracle/backup';--区别于前面的演示过程

[oracle@rac1 backup]$ ls
emp.log

22:48:11 system@RAC> exec dbms_file_transfer.copy_file('asm_dir','emp.dmp','fs_dir','maomi.emp');

[oracle@rac1 backup]$ ls -lt
total 140
-rw-r----- 1 oracle asmadmin 135168 May 21 22:50 maomi.emp
-rw-r--r-- 1 oracle asmadmin   1396 May 21 22:30 emp.log

使用拷贝出来的 dump 文件进行导入测试:

[oracle@rac1 backup]$ impdp system/oracle tables=scott.emp DIRECTORY=fs_dir DUMPFILE=maomi.emp LOGFILE=imp_emp.log
Import: Release 11.2.0.3.0 - Production on Tue May 21 22:54:16 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** tables=scott.emp DIRECTORY=fs_dir DUMPFILE=maomi.emp LOGFILE=imp_emp.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 22:55:59

转载请注明作者出处及原文链接，否则将追究法律责任：

作者：xiangsir

原文链接：http://blog.csdn.net/xiangsir/article/details/8957925

QQ：444367417

MSN：xiangsir@hotmail.com