Solved

DataPump Export to Network Mapped Drive

Posted on 2008-10-14
5
7,277 Views
Last Modified: 2010-04-21
Hello,

I am attempting to export data to a mapped Microsoft drive.  When I perform the export I get the error shown in the code snippet.

I have done the following:
1. Mapped the drive to the remote 2003 Server (from a Windows 2003 Server).
   net use M: \\192.168.1.42\c$
2. Successfully tested mapped drive (credentials etc. OK).
   dir M:\*.*
3. Created a Directory Object to the mapped drive.
   CREATE OR REPLACE DIRECTORY MEXPORTS AS 'M:\exports';
   GRANT read,write ON DIRECTORY MEXPORTS TO system;
4. Attempted schema export.
   expdp system/password DUMPFILE=scott.dmp DIRECTORY=MEXPORTS JOB_NAME=ExportSchema
LOGFILE=ExportSchema.log SCHEMAS=scott

* I received the same error even when specifying that no log file be used via: LOGFILE=NOLOGFILE
* I also tried creating the DO to just the root drive of the share (M:\) = same error.


What can you recommend?

Thanks,

Michael
Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 14 October, 2008 9:59:24
 

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.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 488

ORA-29283: invalid file operation

Open in new window

0
Comment
Question by:michael4606
  • 3
  • 2
5 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 22711879
You have also PHYSICALLY to create the directory
M:\exports
on that device using the OS tools.

There is a discussion here:

http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/b24361674e108969/ee4dfe9ca2bc842e
0
 

Author Comment

by:michael4606
ID: 22713198
schwertner:

Thanks for the response.  I read the content on the link about 5 times and am still not sure it is solving the problem or how they did it.  I did however attempt to create the Directory Object using a UNC path (created no problem plus granted privs as before using SYSTEM), copied files to it OK at the OS level but still getting the error.

Can you spell out the solution a bit more please?

Thanks again,

Michael
0
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
ID: 22718789
It is connected with the rights of the Oracle OS users (do not mix it with Oracle schema users like SYS and SYSTEM). Oracle accesses the directory (it should be created physically and to exist als logically) under the OS user domain on the OS level. So you should check what is the OS user that starts/stops Oracle servicesand to check if he has access rights to that directory.

A usable document:

Directory Permissions
=====================

Directories on FAT partitions
-----------------------------

FAT does not support file or directory permissions.

Directories on NTFS partitions
------------------------------

When an oracle instance is created the services that support it are setup
to Log On As the SYSTEM (or operating system) account. For UTL_FILE's
procedures to access directories that do not have access granted to
everyone they must have "Change" privileges granted to the SYSTEM account.
If the OracleServiceXXXX service has been altered to log on as another
account, it is this account that must be given access to the directories.

Accessing Network Directories
=============================

Netware Networked Directories
-----------------------------

For this to work ensure that the account that started the Oracle instance
has a mapped network drive connected using a fully qualified username/password
with the same drive letter and path as is specified by UTL_FILE_DIR.

NT Networked Directories
------------------------

When an oracle instance is created the services that support it are setup
to "Log On As" the SYSTEM (or operating system) account, this account has
very few privileges and no access to NT Domains. To access another NT machine
the OracleServiceXXXX must be setup to logon to the appropriate NT Domain as
a user who has access to the required location for UTL_FILE.

To change the default logon for the Oracle services, go to :

  -> Control Panel
     -> Services
        -> OracleServiceXXXX (where XXXX is the instance name)
           -> Startup
              -> Log On As

Choose the "This Account" radio button, then complete the appropriate
domain login information. Repeat this procedure for the TNS Listener :

  -> Control Panel
     -> Services
        -> OracleXXXXTNSListener (where XXXX is the Oracle Home Name)
           -> Startup
              -> Log On As

If the listener is not configured to start as the same user as the Oracle
Service user will fail to connect with "ORA-12500 TNS:listener failed to start
a dedicated server process". Once the services have been setup as a user with
the appropriate privileges the are two options for setting UTL_FILE_DIR :

a. Mapped Drive : To use a mapped drive, the user that the service starts as
must have setup a drive to match UTL_FILE_DIR and be logged onto the server
when UTL_FILE is in use.

b. Universal Naming Convention : UNC is preferable to Mapped Drives because
it does not require anyone to be logged on and UTL_FILE_DIR should be set to
a name in the form :

  \\\<machine name>\<share name>\<path>

  or

  "\\<machine name>\<share name>\<path>"
0
 

Author Closing Comment

by:michael4606
ID: 31505900
Despite this being a quirky topic your responses helped make it clearer.

Thanks!
0
 
LVL 47

Expert Comment

by:schwertner
ID: 22721804
Thank you!
Good luck!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle - Stored Procedure Privilge access 3 41
sort a spool into file output in oracle 1 22
Oracle sql query 7 52
Can't Access My Database 57 35
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now