Solved

DataPump Export to Network Mapped Drive

Posted on 2008-10-14
5
7,816 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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 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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

717 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