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!!!
—————————————————————————————————————-
I believe the same exists since 10.2 version
I have not tried this scenario in 10g but pls refer to following link which mentions that cluster command line parameter was introduced in 11g only.
http://www.oracle.com/technetwork/database/datapump11gr2-twp-rac-132795.pdf
Regards
Anju Garg
Yes, that’s correct. The point I wanted to emphasize is that the same is reproducible on a stand-alone DB with ASM as well. It exists from 10.2. Thanks.
Hi,
I encounter the same problem in 12c.
simply changed the OracleService) logon user and it solved the problem.
Works for me!!! Thanks!!!
But i create a new directory on user.
expdp ssb/oracle@pdb1 dumpfile=DP_DIR:supp_exp tables=supplier logfile=DP_DIR:log_exp.log
Obs. This enviroment is only for test. Thks!
THANK YOU ETAY!!!!!!!
I just review the permissions on the path, the problem was it can’t write files on the export path