如何在 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