Home » RDBMS Server » Server Utilities » ORA-39070: Unable to open the log file (Oracle 11.2.0.3.0 , Linux 6)
ORA-39070: Unable to open the log file [message #610487] Thu, 20 March 2014 10:36 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

I would like to share the issue i faced during export data pump.

1.When trying to take table backup i get below error.

[oracle@RAC2 log]$ expdp DEMO directory=DATA_PUMP_DIR DUMPFILE=exp_ORCL_table.dmp  tables=LOG LOGFILE=exp_ORCL_table.log

Export: Release 11.2.0.3.0 - Production on Wed Mar 19 13:31:00 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing optio
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid


2.Check available space in mount points and create a new directory pointing the default directory 'DATA_PUMP_DIR'

[oracle@RAC02 oraworkspace]$ mkdir backup
[oracle@RAC02 oraworkspace]$ cd backup
[oracle@RAC02 backup]$ ls
[oracle@RAC02 backup]$ mkdir export
[oracle@RAC02 backup]$ cd export
[oracle@RAC02 export]$ pwd
/oraworkspace/backup/export


3.Login to database and create the directory mapping to new directory and grant read ,write privileges to user as below and then run the export.

[oracle@RAC02 export]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 19 14:32:11 2014

Copyright (c) 1982, 2011, Oracle.  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, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/DB1/ccr/state

SYS                            DATA_PUMP_DIR
/u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/log/

SYS                            ORA_ALERTLOG_LOC1
/u01/app/oracle/diag/rdbms/orcl/ORCL1/trace


SQL> create or replace directory DATA_PUMP_DIR as '/oraworkspace/backup/export' ;

Directory created.

SQL> grant read,write on directory DATA_PUMP_DIR to User01;

Grant succeeded.

4.Rerun the export command
[oracle@RAC02 export]$ expdp DEMO directory=DATA_PUMP_DIR DUMPFILE=exp_ORCL_table.dmp  tables=CHANGELOG LOGFILE=exp_ORCL_table.log
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

To grant read ,write privilege to directory to all the users in database use the below command.


SQL>  grant read,write on directory DATA_PUMP_DIR to public;

Grant succeeded.

5.Post validation

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0
                                                              /DB1/ccr/state

SYS                            DATA_PUMP_DIR                  /oraworkspace/backup/export
SYS                            ORA_ALERTLOG_LOC1              /u01/app/oracle/diag/rdbms/orcl
                                                              /ORCL1/trace

Note:--
Make sure you create new directory on server and map default directory to that location and grant read , write permission to user.

Seniors:
Please review the steps and make changes wherever required.

Thanks,

[Updated on: Thu, 20 March 2014 12:04]

Report message to a moderator

Re: ORA-39070: Unable to open the log file [message #610905 is a reply to message #610487] Tue, 25 March 2014 13:44 Go to previous message
youngryand
Messages: 10
Registered: March 2014
Location: Madison, WI
Junior Member
It seems unclear to me if your failure was observed before or after you granted privs on the directory to PUBLIC. It seems, based on what you show in your #4 section, that the grant to PUBLIC was executed after the failure.

Therefore, in order to achieve success, you would have had to have granted privs on the directory explicitly to DEMO before the export, since you are performing the export as DEMO. However, your text copies show that you granted privs on the directory explicitly to USER01, not to DEMO.

Am I right?

Ryan

Also, note that we are assuming that you are logging into the same database every time w/out knowing that for sure. I noticed that what I assume to be your ORACLE_SID in the command prompt is usually RAC02, that in #1, it is shown as RAC2.
Previous Topic: sqlldr ORA-01841
Next Topic: IMPDP hangs on table data
Goto Forum:
  


Current Time: Tue Sep 30 16:25:29 CDT 2014

Total time taken to generate the page: 0.13742 seconds