expdp ESTIMATE_ONLY=Y gives ORA-39070: Unable to open the log file.

##########################
## Error
##########################

ORA-39070: Unable to open the log file.

### Full Error

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
##########################

Error occured while trying to estimate the size of the size of the dump file using expdp

##########################
## Command Executed
##########################

expdp \'/ as sysdba\' tablespaces=USERS ESTIMATE_ONLY=Y

##########################
## Solution
##########################

### Ran the ESTIMATE_ONLY=Y option without Directory Option, failed in this database.

oralin_3 @ hostu07:/u01/home/oracle
> expdp \'/ as sysdba\' tablespaces=USERS ESTIMATE_ONLY=Y

Export: Release 11.2.0.3.0 - Production on Mon Sep 2 07:09:11 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 - 64bit 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

So initially what i did is ran the estimate_only command with directory option and it got succeeded.

expdp \'/ as sysdba\' tablespaces=USERS ESTIMATE_ONLY=Y directory=export_dump

I have never given directory option before for estimating the size of the dumpfile earlier.

------------------------------------------------------------------------------------------------------

Again i tried the same in another database ( orawin_1 ) without directory option and it has succeeded.

orawin_1 @ hostp03:/u01/home/oracle
> expdp \'/ as sysdba\' tablespaces=USERS ESTIMATE_ONLY=Y

Export: Release 11.2.0.3.0 - Production on Tue Sep 3 00:04:04 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 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  "/******** AS SYSDBA" tablespaces=USERS ESTIMATE_ONLY=Y
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at 00:04:52

So i investigated more on this and found below.

When a expdp command is run with ESTIMATE_ONLY=Y option, it doesnt create any dump file but it tries to create a log file, due to this i got the error when i ran 
the command in oralin_3 database.

You might be wondering why i didnt receive the same error in orawin_1 database its because from Oracle 10g onwards, a default directory named DATA_PUMP_DIR is created.

So if a directory option is not mentioned it will try to create the dumpfile and logfile in the location mentioned in DATA_PUMP_DIR directory.

In orawin_1 this directory exist and its physical location is also VALID.


OWNER           DIRECTORY_NAME            DIRECTORY_PATH
--------------- ------------------------- ------------------------------------------------------------
SYS             DATA_PUMP_DIR             /u01/app/oracle/product/11.2/db_10/rdbms/log/

SQL> !ls -ltr /u01/app/oracle/product/11.2/db_10/rdbms/log/
total 16
-rw-r--r-- 1 oracle dba 14365 Sep  3 00:06 export.log

But in oralin_3, the physical location of this directory is not available and it failed.