Database Software

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)?

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

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.

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.

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.

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?

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

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.

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.

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.

I pulled out bloody clumps of hair (sad since I have so little left) to do it, but I figured out a work around.

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.