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 (c) 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 (c) 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

郑全

注意日志文件。