Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

impdp, expdp error ORA-39070 has me dead in the water

832601Jul 11 2011 — edited Jul 18 2013
I'm writing backup and restore scripts for a software testing system using multiple combinations of servers (several flavors of Windows) and databases (SQL Server and Oracle). I am unable to automatically backup or restore Oracle databases using expdp or impdp because Oracle is issuing error ORA-39070: Unable to open the log file.

The log file is created, I issue the following command at the beginning of the session:

create or replace directory qa211_dumpdir as '\\<db_ip_address\H$\Backup\qa211';
create or replace directory qa211_logdir as '\\<test_ip_address>\C$\Temp';

<db_ip_address> is the IP for the Oracle database server.
<test_ip_address> is the IP for the software test computer. This is NOT the same as db_ip_address.
'\\<test_ip_address>\C$\Temp' is a public folder, anybody here in the company may read/write to it.

These commands are executed without error. I've tried specifying the directory paths both with and without the trailing backslash, that doesn't seem to make any difference.

I then execute the following command:

impdp qa211/qa211@qa211 DIRECTORY=qa211_dumpDir DUMPFILE=5.0.0_baseline.dmp LOGFILE=qa211_logDir:or_restore_out.txt

Which generates the following response:


Import: Release 11.2.0.1.0 - Production on Mon Jul 11 10:12:48 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, 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

The log file must be generated on the test machine, not on the database server. I use QTP scripts to examine the contents of the backup/restore log files to verify that the operations were performed successfully. If failures occur, I need the log files for analysis.

Any help would be greatly appreciated.

Comments

Sky13
I do not think this is a Data Pump issue. I think it is a problem with the DIRECTORY object. Can you map the drives to a drive letter and then define the DIRECTORY object based on the drive letter.

Also why are you using Data Pump for backup and restore? Have you considered RMAN (A fare better tool for the job)?
832601
At the risk of exposing my ignorance.... What the heck is RMAN? I've never heard of it before.

I had been using exp and imp for database backup and recovery prior to Oracle 11g, but those were no longer working because exp wouldn't export zero-length tables in 11g.

No, I can't map the drives.

Edited by: Lord Foul the Despiser on Jul 11, 2011 11:20 AM
832601
I took a quick look at RMAN, it doesn't appear to be a practical solution since it is an interactive environment. I need to be able to execute backup and restore directly from the Windows command line. If there is a way to do this with RMan, I sure don't understand how.
Sky13
Yes you can run RMAN from the Windows(Any OS that Oracle DBMS runs on) command line. I would really, REALLY recomend you use RMAN (Recovery Manager)!!!

However for the DIRECTORY object issue:
I am no longer at a shop that runs Oracle on Windows so I can not test this stuff out but... I remember a ton of problems in the windows OS based on who was logged into the server. please take a look at this support note 45172.1 it might help.
832601
Now I'm going to make myself look like a complete idiot, but I just spent a half-hour trying to find support note 41572. Not a chance, I haven't got the slightest idea where to even start looking on this nightmare of a web site.

I'm frustrated as hell! I'm not a DBA, I don't want to be a DBA. I just want a simple way to issue a command from a VBScript program to create a backup of a database and/or restore a backup of the database. I was perfectly happy with exp and imp until 11g comes along and now exp won't export zero-length tables (lots of unpopulated tables in our baseline) without specifying the full=y option. I would be okay with that except that a baseline db backup in 10g that was about 350k now consumes over 85 meg!

The RMAN documentation that I finally found is several hundred pages of BS. It all appears to be a very powerful program, but all I want to do is this: Create a backup file of a particular database on a particular disk and put the log file of the operation onto a different server without mapping a drive so I can verify that the process completed correctly. The nearest I can figure from what I read is that I have to write a program to write some kind of configuration file to submit to RMAN and then try to execute that mess. AGH!

Sorry for the rant, but this whole thing just seems so hopeless.
Sky13
I once was a VB programer. No I am a DBA. But I understand your fustration. What was the exact line you called to do your exp before trying to use Data Pump?
Srini Chavali-Oracle
I am no Windows expert, but this error may be due to Windows security. See MOS Doc 858401.1 (EXPDP To A Windows Mapped Network Drive Returns Errors ORA-39002,ORA-39070, ORA-29283, Ora-06512) for an explanation.

HTH
Srini
Sky13
Yes I agree... you are most likely running into a Windows security issue. When an oracle instance is created on Windows the services are setup to Log On as the SYSTEM account. For "Calls" coming from the database to be able to access access directories they must have privileges granted to the SYSTEM account. Basicaly the account that the OracleService[Instance Namer] service is running under has to have access to the UNC path.
832601
Thanks, Chris and Srini. Srini, you are probably correct in that the problem was being caused by Windows security, but I couldn't figure out how to get around it.

I finally figured out a way to make the process work. I gave up trying to write the log file to the test machine and wrote them to the database server itself. I probably could have saved myself some frustration by doing this earlier, but I was suffering from tunnel vision. Unfortunately, now the Oracle and SQL Server backup/restore processes are slightly different, but I can live with that for now. Eventually I will modify the SQL Server process to write it's log files to the server also, to maintain consistency.

The original exp and imp commands were as such:

exp dbuser/dbpword@dbname
file=backup_file_full_path_name.dmp
log=log_file_full_path_name.log

Before executing the import, I dropped all tables and views and purged the recyclebin.

imp dbuser/dbpword@dbname
file=backup_file_full_path_name.dmp
log=log_file_full_path_name.log
fromuser=dbuser
touser=dbuser

These have been replace with the following:

Before executing either of these scripts, I execute a command to create or replace the data dump directory.

expdp dbuser/dbpword@dbname
DIRECTORY=dumpDir
DUMPFILE=backup_file.dmp /* not full path */
LOGFILE=log_file.log /* not full path */
TABLESPACES=dbuser
COMPRESS=Y
REUSE_DUMPFILES=Y

Although it doesn't appear to be required anymore, I still drop all tables and views before executing the import.

impdp dbuser/dbpword@dbname
DIRECTORY=dumpDir
DUMPFILE=backup_file.dmp /* not full path */
LOGFILE=log_file.log /* not full path */
TABLE_EXISTS_ACTION=REPLACE

This appears to be working satisfactorily. It may be that rman is more appropriate for what I'm doing here, but Oracle makes it so hard to find clear, concise documentation, and they make it so damn difficult to execute anything from the command line, that I just can't take the time to learn yet another Oracle interface. Oracle may be a solid database once its installed and running, but in my humble opinion, their tools SUCK.
832601
In regards to the Windows security question, I guess that makes sense since exp and imp execute from the client, which means they have permissions to write to the local c: drive. Since expdp and impdp execute on the server, I guess they don't necessarily have access to the client's C: drive. Pretty confusing though, since in both cases I'm launching the programs from the client.

Like I said before, it may not be the most elegant solution, but I got it working and that's all I care about.
832601
I pulled out bloody clumps of hair (sad since I have so little left) to do it, but I figured out a work around.
874988
Hi,

It is correct:

create or replace directory qa211_dumpdir as '\\<db_ip_address\H$\Backup\qa211';
create or replace directory qa211_logdir as '\\<test_ip_address>\C$\Temp';

Mapping a network doesn't work for a Oracle directory, such as "X:\"

Now the issue is to try writing on the destination directory. In order to do that, you need the necessary Windows privilege.

So, try to change Oracle's Services logon (Windows -> Run -> services.msc) from the Local System Account to a user with privileges, make sure restart the services.
Luis_Fontes

Hi,

I believe I got the solution for this problem, but I'm using LINUX.

The fix was to change the permissions on the directory that the dump has to be written.

$ mkdir /dir1/PUMP_DIR

$ chmod 777 /dir1/PUMP_DIR

$ sqlplus / as sysdba

SQL> create directory DIR1 as '/dir1/PUMP_DIR';

$ expdp ...

The explanation is that I created the directory with my OS user (eg. john_doe) and expdp runs with oracle OS user.

So you have to give permission to oracle user write on YOUR directory.

In the example above, 777 sets the read/write permissions to everyone.

Give read/write permissions to the oracle user on your Windows server and retry the expdp operation.

Best regards,

Luis

Richard Harrison .

Hi,

If you want to stick with old exp/imp you can change the database parameter:

alter system set deferred_segment_creation=FALSE;

Then tables will continue to be created as they were in older version of oracle with no 'empty' segements causing the problem for exp. I assume you have to manually allocate extents though for tables created prior to when you changed this parameter.

The reason oracle introduced this functionality is that a lot of systems (especially big ERP/CRM systems) create thousands of tables many of which are never used - not creating the segment until a row is actually created can save a huge amount of space - they just didn;t apply the change to let exp/imp handle that as it's a deprecated utility. expdp/impdp are much better - it's just a change in mindset that now everything is on the server.

You could put an external table on top of the logfiles and then exposes that table to your users - so instead of openiong a logfile they can say select * from log...?

expdp is much better and exp will not support all functionality (as you've seen with the segment problem).

Hope that's useful.

Cheers,

Harry

http://dbaharrison.blogspot.com

1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 15 2013
Added on Jul 11 2011
14 comments
73,860 views