2

I am trying to use expdp to perform an export on some tables of my Oracle DB

my code:

expdp AAA/***@xxx schemas=AAA include=TABLE:\"= \'TBL001\'\" directory=DUMP_DIR dumpfile=dmpfile.dmp logfile=lgfile.txt

This is what I get:

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

I checked online and I guess it is a problem of corssing different OS and/or authorization

This is my situation:

  • Oracle DB on Linux machine with CentOS 7
  • directory=DUMP_DIR is a directory object on DB. It was created as I saw the path through Linux Machine = smb://Win_machine1/dump_oracle/
  • DUMP_DIR is located on a windows server 2008 R2 (Win_machine1)
  • I can reach DUMP_DIR from Oracle Machine giving windows credentials
  • I launch a .bat file in Win_machine1 with expdp command written above

Q1: Authorization from Oracle Machine through Win Server? I suppose Oracle uses AAA to access DB and perform export operation, but which user accesses the directory DUMP_DIR? When I located it in CentOS it asked me for credentials (and I told it to remember), but I don't know if it stored them or if expdp uses them. (AAA is not a domain user)

Could it be this the problem or should I check something else?

Thank you, Marcello

cubick
  • 293
  • 3
  • 13
Marcello Manfredini
  • 61
  • 2
  • 8
  • The Oracle software owner access the OS directory, not the user running the export. You may need to access the share from that account, saving credentials, but only if that is a valid/safe thing to do (whose credentials?). I'm not sure if Oracle will know how to handle the `smb://` path format; if not can you mount that so you can refer to a normal local mountpoint path? – Alex Poole Apr 19 '17 at 10:46
  • The share is located in the same server where I execute expdp, so the Windows user logged in Win_machine1 is that same that has privileges to open the share. I don't know how to translate smb:// path or if it is possible... But I think I must use CentOS location because DB is mounted there. – Marcello Manfredini Apr 19 '17 at 12:06
  • Not sure what you mean. The Oracle user on the Linux box is accessing the share via the directory object - that user (not whoever you run expdp as on the Linux box) has to be able to access that share. Have you verified access as that Oracle user? – Alex Poole Apr 19 '17 at 12:09
  • The Oracle user can access the share, but it requires windows authentication. The problem is that I can reach the share via Linux, but it doesen't seem to be able to do the same via expdp. I think I can solve it using one of theese ways do you agree? 1. translate smb to windows path 2. giving expdp windows credentials – Marcello Manfredini Apr 19 '17 at 12:39
  • Maybe (1) though I assume you means Linux path, via a fixed mount, but not (2) - how would you supply Windows credentials? The username and password you're passing are database credentials. Just for fun, can you remove the last `/` from the path in the directory definition, if it ends with one (as your question suggests)? – Alex Poole Apr 19 '17 at 12:43
  • (1) Right, Linux path via fixed mount. (2) I was dreaming too big :D I tried without '/' but it still doesn't work, same error – Marcello Manfredini Apr 19 '17 at 12:52

2 Answers2

1

I found the solution:

Create directory under /mnt/ in Linux using mkdir

cd /mnt

mkdir mydir

then mount it using mount

mount -t cifs -o username,password //servername/sharename /mnt/mydir/

where username and password is Windows-user able to access the share and //servername/sharename is the path of my share (servername = IP address if not able to resolve DNS)

Then create directory on Oracle DB

CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/mnt/mydir'

expdp is correctly working

expdp AAA/***@xxx schemas=AAA include=TABLE:\"= \'TBL001\'\" directory=DUMP_DIR dumpfile=dmpfile.dmp logfile=lgfile.txt

Marcello Manfredini
  • 61
  • 2
  • 8
0

Related to bug ticket 8313127 the DataPump does not support CIFS file system. Oracle recommends to use NFS file system instead.

flyaround
  • 333
  • 1
  • 5
  • 16