expdp Archives - ORACLE IN ACTION

I received this error message when I was trying to perform a data pump export of SH schema in parallel in a RAC database. I proceeded as follows:

Current scenario:
Name of the cluster: cluster01
Number of nodes : 3 (host01, host02, host03)
RAC Database version: 11.2.0.3
Name of RAC database : orcl
Number of instances : 3

Created a directory object pointing to shared storage which is accessible by all the three instances of the database

SQL>drop directory dp_shared_dir;
SQL>create directory DP_SHARED_DIR as '+DATA/orcl/';
SQL>grant read, write on directory dp_shared_dir to public;

Issued the command to export SH schema in parallel across all active Oracle RAC instances with parallelism = 6 which resulted in error ORA-39070

[oracle@host01 root]$ expdp system/oracle@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y dumpfile='expsh%U.dmp' reuse_dumpfiles=y

Export: Release 11.2.0.3.0 - Production on Tue Dec 8 14:45:39 2015
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

Cause:
The error message indicates that Log file cannot be opened. Since directory parameter points to a shared location on an ASM disk group and log file is not supported on it, I received the above error.

Solution:
I modified my command and explicitly specified log file to be created on local file system pointed to by the directory object DATA_PUMP_DIR. Subsequently, export was performed successfully.

[oracle@host01 root]$ expdp system/oracle@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile='expsh%U.dmp' reuse_dumpfiles=y

Export: Release 11.2.0.3.0 - Production on Tue Dec 8 15:14:11 2015

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_SCHEMA_10": system/********@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile=expsh%U.dmp reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 273.8 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
.....
.....
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_10" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_10 is:
+DATA/orcl/expsh01.dmp
+DATA/orcl/expsh02.dmp
+DATA/orcl/expsh03.dmp
+DATA/orcl/expsh04.dmp
+DATA/orcl/expsh05.dmp
+DATA/orcl/expsh06.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_10" successfully completed at 15:20:49

I hope it helps!!!

—————————————————————————————————————-

Related links:

Home

11gR2 RAC Index