[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DataPump Export to Network Mapped Drive

Posted on 2008-10-14
5
Medium Priority
?
8,449 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 48

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 48

Accepted Solution

by:
schwertner earned 500 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 48

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

873 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