Solved

DataPump Export to Network Mapped Drive

Posted on 2008-10-14
5
7,171 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 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

11 Experts available now in Live!

Get 1:1 Help Now