Sunday, December 13, 2009

Export fails with EXP-00002: error in writing to export file

Problem Description
I ran my script exp10g_zip.sh and I failed with error below.
EAIAPP:/opt/oracle/admin/EAIAPP/scripts>./exp10g_zip.sh EAIAPP 1 Y ALL
./exp10g_zip.sh: line 172: /usr/contrib/bin/gzip: No such file or directory
ERROR MSG: ./exp10g_zip.sh FAILED WITH AN ORACLE (EXP) ERROR ON HOST db1-eai.pst.hrn.clearitlab.com
LOG FILE: /opt/oracle/admin/EAIAPP/logs/exp10g_zip.sh_21273.log
RC: 1
From the /opt/oracle/admin/EAIAPP/logs/exp10g_zip.sh_21273.log logfile (which is export logfile) it shows,
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
Export done in UTF8 character set and AL16UTF16 NCHAR character set
EXP-00002: error in writing to export file
EXP-00000: Export terminated unsuccessfully
From the script logfile it says
INVOKING DATABASE BACKUP Sun, 13 Dec 2009 00:11:44.


Export: Release 10.2.0.4.0 - Production on Sun Dec 13 00:11:44 2009

Copyright (c) 1982, 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
Export done in UTF8 character set and AL16UTF16 NCHAR character set
EXP-00002: error in writing to export file
EXP-00000: Export terminated unsuccessfully

DATABASE BACKUP COMPLETE 00:11:44. EXPORT FILE /opt/oracle/admin/EAIAPP/exports/EAIAPP_20091213_001144.dmp

./exp10g_zip.sh FAILED WITH AN ORACLE (EXP) ERROR. SYNOPSIS WRITEN TO STDOUT FOR LSF. EXIT WITH RC=1

exit 1
And my script looked like below,
if mkfifo  $PIPE
then
trap "/bin/rm $PIPE 2>/dev/null" HUP INT QUIT TERM
/usr/contrib/bin/gzip < $PIPE > $EXPFILE.Z&

if [ $EXP_USERS = "ALL" ] ; then


  $ORACLE_HOME/bin/exp exp_dba/$DB_EXP_DBA file=$PIPE full=Y direct=Y rows=$EXP_DATA compress=N log=$EXP_LOG STATISTICS=NONE  >> $LOGFILE 2>&1
else

  $ORACLE_HOME/bin/exp exp_dba/$DB_EXP_DBA file=$PIPE OWNER=\(${EXP_USERS}\) direct=Y rows=$EXP_DATA compress=N log=$EXP_LOG STATISTICS=NONE >> $LOGFILE 2>&1

fi

/bin/rm $PIPE 2>/dev/null

fi


RC=$?
Cause of the Problem
After running script whenever I see ./exp10g_zip.sh: line 172: /usr/contrib/bin/gzip: No such file or directory I immediately thought that location of gzip is wrong and hence error returned but then after seeing EXP-00002: error in writing to export file I guessed that it might be the problem because of oracle failed to write dump in the operating file system.

Solution of the Problem
In order to solve "EXP-00002: error in writing to export file" error you need to look for following things:

1) The export file could not be written to disk anymore, probably because the disk is full or the device has an error.

Check the disk free usage by issuing command,
$df -h

And check whether device has any error or not by creating file into it and writing some.
$vi test_device.txt

and write some contents and save it. If it is ok then device has no problem in it.

2) It may be the cause because of your file systems do not support a certain limit (eg. dump file size > 2Gb). If you see dumpfile you might see like it's size like 1.99G and after exporting next table it fails because file system does not support file more than 2G. In that case you need to divide the dump into several files. For that include a filesize parameter as well as add more dumpfiles with the file parameter.
Example:
exp arju/arju
full=y 
file=exp%U.dmp 
log=export.log 
filesize=2000M
3) Make sure no limitation is shown by issuing ulimit -a.

4) Note that while export operation, Export selects from the DUAL table to obtain the current date and other information. So when DUAL table has been truncated, you cannot select any data from it any more. If a select is not possible, "EXP-00002: error in writing to export file" will be produced.

To solve it,

i) Check who owns the DUAL table (should be SYS only) and if there is a public
synonym for it:
SQL> connect / as sysdba
   SQL> select created,owner,object_type from dba_objects 
         where object_name='DUAL';

   CREATED           OWNER                          OBJECT_TYPE
   ----------------- ------------------------------ ------------------
   29-JUL-2009 00:06 SYS                            TABLE
   29-JUL-2009 00:06 PUBLIC                         SYNONYM
Also make sure, Only DUAL table exists under SYS schema. Delete other copies if there is any.

ii)Check if there is a row in dual, if not, add one:
SQL> select * from dual;
   no rows selected

   SQL> insert into dual values ('X');
   1 row created.

   SQL> commit;
   Commit complete.

   SQL> select * from dual;
   D
   -
   X
iii)Now re-run the export.

But in my case these solution did not solve my problem. If you check the script 
trap "/bin/rm $PIPE 2>/dev/null" HUP INT QUIT TERM
/usr/contrib/bin/gzip < $PIPE > $EXPFILE.Z&
and error message it says at first /usr/contrib/bin/gzip: No such file or directory. Then I checked whether gzip already exist in the location /usr/contrib/bin/ and saw it is not there. By using,
which gzip I see its location is /bin/gzip and so in the script I used following lines and it solved my problem.
trap "/bin/rm $PIPE 2>/dev/null" HUP INT QUIT TERM
/bin/gzip < $PIPE > $EXPFILE.Z&

Related Documents
Export/Import from Enterprise Manager
Original Export/Import

Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)