Wednesday, August 3, 2011

DBA: DC City Stories: Datapump

Oracle always has a way with error message. Why can't they make it clear and precise?

I was doing a database export using datapump for a client, and I passed in PARFILE. The following error was spat out:

ksunsodb01:oracle\(KTECH\)@ expdp parfile=expdp.par

Export: Release - 64bit Production on Thursday, 07 July, 2011 11:09:21

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

Connected to: Oracle Database 10g Enterprise Edition Release - 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

Not very clear huh? Well, what Oracle was complaining about was that it was missing the Datapump directory on a OS level. Therefore, check which directory your PARFILE is using: 

ksunsodb01:oracle\(KTECH\)@  cat parfile_expdp_WMDATA.par
USERID="/ as sysdba"
flashback_time="to_timestamp(to_char(sysdate, 'dd/mon/yyyy hh24:mi:ss'), 'dd/mon/yyyy hh24:mi:ss')"

In our case, the directory is "DATA_PUMP_DIR". Find out where this directory is mapped to:

ksunsodb01:oracle\(KTECH\)@  sqlplus /nolog

SQL*Plus: Release - Production on Thu Jul 7 11:14:07 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
SQL> select directory_path, directory_name from dba_directories;

DIRECTORY_NAME                                                        DIRECTORY_PATH                     
/u06/flash2/exports/OTECH/         DATA_PUMP_DIR

And check that the directory actually exists. In our case it doesn't, so modify it to a directory that exists:

SQL> create or replace directory DATA_PUMP_DIR as '/u06/flash1/export/KTECH/';

Directory created.
SQL> exit

Now, we're ready to run again. :)

No comments:

Post a Comment