Sunday, April 8, 2012

Data pump export to a network mapped drive fails with ORA-39002 ORA-39070

Problem Description
A folder from a network of another machine is mapped to Z: drive in the Oracle server machine. Proper permission is given in that folder. Oracle directory is created as follows:

SQL> create or replace directory z as 'Z:\backupset';

Now whenever we try to do data pump export it fails with ORA-39002, ORA-39070 and ORA-29283: invalid file operation.

C:\Users\Administrator>expdp schemas=idencraft userid=system/sys directory=z logfile=data_pump_dir

Export: Release 11.2.0.3.0 - Production on Sun Apr 8 11:56:15 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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 536
ORA-29283: invalid file operation

If we change the log file location to any local directory of the server then error message change like following:
C:\Users\Administrator>expdp schemas=idencraft userid=system/sys directory=z logfile=data_pump_dir:log.log

Export: Release 11.2.0.3.0 - Production on Sun Apr 8 11:56:29 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "z:\backupset\expdat.dmp"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Cause of the Problem
The CREATE DIRECTORY command used the drive letter, not the UNC naming convention (\\<server>\<sharepoint>).
In this example: CREATE DIRECTORY dump_dir AS 'Z:\backupset';

The account running the command does not have the necessary privileges to access the mapped network disk.
The user LOCAL SYSTEM normally will not have file system permissions or network permissions.

Solution of the Problem
Use UNC naming convention (\\<server>\<sharepoint>) on CREATE DIRECTORY command.
SQL> create or replace directory z as '\\172.16.1.36\c$\DB_Backup\backupset';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>expdp schemas=idencraft userid=system/sys directory=z log
file=data_pump_dir:log.log

Export: Release 11.2.0.3.0 - Production on Sun Apr 8 11:58:44 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  schemas=idencraft userid=system/*****
*** directory=z logfile=data_pump_dir:log.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 130.0 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_
PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

No comments: