expdp fails when DIRECTORY parameter is used

All posts relating to Oracle database administration.

Moderator: Tim...

expdp fails when DIRECTORY parameter is used

Postby sridhartempalle » Wed Dec 18, 2013 7:39 pm

Whenever I try to run the expdp with DIRECTORY paramter, the job fails. Without any parameter the job executes succesfully.

As sysdba:
SQL> create directory dpump_dir1 as '/u01/app1/oracle/oradata/dump_dir';
Directory created.

SQL> grant read, write on directory dpump_dir1 to sridhar;
Grant succeeded.

SQL> select * from all_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DPUMP_DIR1
/u01/app1/oracle/oradata/dump_dir

expdp without any parameters:
[oracle@localhost ~]$ expdp sridhar/sridhar

Export: Release 10.2.0.1.0 - Production on Wednesday, 18 December, 2013 22:02:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SRIDHAR"."SYS_EXPORT_SCHEMA_01": sridhar/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
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/TABLE/TABLE
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/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SRIDHAR"."EMPLOYEE" 6.015 KB 5 rows
. . exported "SRIDHAR"."EMP_NEW02" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_HASH":"SYS_P21" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_HASH":"SYS_P22" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_LIST":"EAST_SALES" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_LIST":"NORTH_SALES" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_LIST":"SOUTH_SALES" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_LIST":"WEST_SALES" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_RANGE":"SALES_Q1" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_RANGE":"SALES_Q2" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_RANGE":"SALES_Q3" 0 KB 0 rows
. . exported "SRIDHAR"."SALES_DATA_RANGE":"SALES_Q4" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P1"."SYS_SUBP23" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P1"."SYS_SUBP24" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P1"."SYS_SUBP25" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P1"."SYS_SUBP26" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P2"."SYS_SUBP27" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P2"."SYS_SUBP28" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P2"."SYS_SUBP29" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P2"."SYS_SUBP30" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P3"."SYS_SUBP31" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P3"."SYS_SUBP32" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P3"."SYS_SUBP33" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P3"."SYS_SUBP34" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P4"."SYS_SUBP35" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P4"."SYS_SUBP36" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P4"."SYS_SUBP37" 0 KB 0 rows
. . exported "SRIDHAR"."SCOUT_GEAR_RANGE_HASH":"P4"."SYS_SUBP38" 0 KB 0 rows
Master table "SRIDHAR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SRIDHAR.SYS_EXPORT_SCHEMA_01 is:
/u01/app1/oracle/product/10.2.0.1.0/db_1/rdbms/log/expdat.dmp
Job "SRIDHAR"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:03:55

expdp with parameters:
[oracle@localhost ~]$ expdp sridhar/sridhar@prod full=Y directory=DPUMP_DIR1 dumpfile=prodfull.dmp logfile=expdpprod.log

Export: Release 10.2.0.1.0 - Production on Thursday, 19 December, 2013 0:47:05

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining 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 475
ORA-29283: invalid file operation

[oracle@localhost ~]$

The user sridhar has sysdba privileges.
1) Why does the job fail when I use paramters?
2) What should I do to overcome this failure?
sridhartempalle
Member
 
Posts: 41
Joined: Mon May 06, 2013 7:13 pm

Re: expdp fails when DIRECTORY parameter is used

Postby Tim... » Wed Dec 18, 2013 9:10 pm

Hi.

1) Always add the trailing slashes to paths. Some features don't handle the missing slash very well.

create directory dpump_dir1 as '/u01/app1/oracle/oradata/dump_dir/';

2) The directory must exist on the OS and the Oracle software owner (oracle) must have permission to read/write there. Remember, creating the directory object does not create the physical directory.

3) When you don't specify the directory, the default directory (DATA_PUMP_DIR) is used. The associated OS path for this definitely exists and has the correct permissions.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: expdp fails when DIRECTORY parameter is used

Postby sridhartempalle » Thu Dec 19, 2013 10:21 am

Hi Tim,

Thanks a lot. I relied on the database object that I had created and it was solved once I created a physical directory. This point I didn't grasp to the perfection previously which caused the error to appear. The data pump export completed with an error which states the following:

Dump file set for SRIDHAR.SYS_EXPORT_FULL_01 is:
/u01/app1/oracle/oradata/dump_dir/prodfull.dmp
Job "SRIDHAR"."SYS_EXPORT_FULL_01" completed with 1 error(s) at 15:33:22

Error:
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39139: Data Pump does not support XMLSchema objects. TABLE_DATA:"OE"."PURCHASEORDER" will be skipped.

The error explanation suggests usage of original export and import utilities to move this object.
Would using it along with the datapump pose any problem for the database while importing it?
sridhartempalle
Member
 
Posts: 41
Joined: Mon May 06, 2013 7:13 pm

Re: expdp fails when DIRECTORY parameter is used

Postby Tim... » Thu Dec 19, 2013 12:03 pm

Hi.

I notice you are on an old version of the DB (and exp). It would be interesting to see what would happen if your database were patched to 10.2.0.5. You may find the issue is resolved.

Before doing exp/imp of that missing table, I would probably raise an SR with Oracle support asking how best to do this transfer...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 3 guests