重庆思庄oracle技术分享- EXPDP导出数据到ASM磁盘组

1.在ASMCMD下创建放置文件的目录

ASMCMD>cd +fra/orcl/

ASMCMD> mkdir dpbak

ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y ARCHIVELOG/
Y AUTOBACKUP/
Y BACKUPSET/
Y CONTROLFILE/
Y DUMPSET/
Y ONLINELOG/
N dpbak/
ASMCMD>

2.数据库里面创建目录
SQL> conn / as sysdba
Connected.
SQL> create directory expdp_asm_dir as ‘+fra/orcl/dpbak’;
Directory created.
SQL> grant read,write on directory expdp_asm_dir to hr;
Grant succeeded.
SQL> create directory expdp_log_dir as ‘/home/oracle’;
Directory created.
SQL> grant read,write on directory expdp_log_dir to hr;
Grant succeeded.
SQL>

3.导出数据

[oracle@dbserver ~]$ expdp hr/hr directory=expdp_asm_dir dumpfile=hr.dmp
Export: Release 12.1.0.2.0 - Production on Mon Mar 2 11:04:26 2020
Copyright © 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing 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

报错了，日志文件需要放在文件系统，修改日志文件的路径。

[oracle@dbserver ~]$ expdp hr/hr directory=expdp_asm_dir dumpfile=hr.dmp logfile=expdp_log_dir:hr.log
Export: Release 12.1.0.2.0 - Production on Mon Mar 2 11:08:10 2020
Copyright © 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options
Starting “HR”.“SYS_EXPORT_SCHEMA_02”: hr/******** directory=expdp_asm_dir dumpfile=hr.dmp logfile=expdp_log_dir:hr.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported “HR”.“SYS_EXPORT_SCHEMA_01” 217.1 KB 1241 rows
. . exported “HR”.“COUNTRIES” 6.460 KB 25 rows
. . exported “HR”.“DEPARTMENTS” 7.125 KB 27 rows
. . exported “HR”.“EMPLOYEES” 17.08 KB 107 rows
. . exported “HR”.“EMP_SQLLDR” 9.046 KB 107 rows
. . exported “HR”.“JOBS” 7.109 KB 19 rows
. . exported “HR”.“JOB_HISTORY” 7.195 KB 10 rows
. . exported “HR”.“LOCATIONS” 8.437 KB 23 rows
. . exported “HR”.“REGIONS” 5.546 KB 4 rows
Master table “HR”.“SYS_EXPORT_SCHEMA_02” successfully loaded/unloaded

---

Dump file set for HR.SYS_EXPORT_SCHEMA_02 is:
+FRA/orcl/dpbak/hr.dmp
Job “HR”.“SYS_EXPORT_SCHEMA_02” successfully completed at Mon Mar 2 11:08:58 2020 elapsed 0 00:00:45

导出文件放在磁盘组了

4.验证
ASMCMD> cd +fra/orcl/dpbak
ASMCMD> ls
hr.dmp
ASMCMD> ls -l
Type Redund Striped Time Sys Name
DUMPSET UNPROT COARSE MAR 02 11:00:00 N hr.dmp => +FRA/ORCL/DUMPSET/HRSYS_EXPORT_SCHEMA_02_94920_1.343.1033988895