jueves, 9 de enero de 2014

ORA-06512: at "SYS.UTL_FILE"

oracle version : RAC 11.2.0.3 (2 nodes called fran1 and fran2)
OS: RHEL 6

I was trying to do a export of my schema and import into another database when sudenly:

[oracle@fran ~]expdp system/fran@fran1 directory=DUMP_DIR dumpfile=full_01112013.dmp logfile=full_01112013.log schemas=fran;

Export: Release 11.2.0.3.0 - 64bit Production on Wensday, 11 December, 2013 12:05:2 0
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0  - 64bit Production
With the Partitioning, Real Application Clusters, 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

hmm? why?

I checked the directory exists and It is writable:

SQL>  select directory_name, directory_path from dba_directories where directory_name='DUMP_DIR';

DIRECTORY_NAME                      DIRECTORY_PATH
----------------------------                       ------------------------------
DUMP_DIR                                   /backup/fran/


SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.6.0 - Production
[oracle@fran~]$ cd /backup/
[oracle@fran backup]$ ls -l
total 40
drwx------ 2 root   root     16384 Mar  9  2009 lost+found
drwxr-xr-x 2 oracle oinstall  4096 Mar 21 11:15 fran


Directory exists and are writable. User is "system" so it has the neccesary privileges to do the export (if you are trying with another user, check the privileges it has and be sure that it has "read" and "write" on that "directory")

So I tried again but this time without connect string:


[oracle@fran ~]expdp system/fran directory=DUMP_DIR dumpfile=full_01112013.dmp logfile=full_01112013.log schemas=fran;

Export: Release 11.2.0.3.0 - 64bit Production on Wensday, 11 December, 2013 12:05:2 0
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=DUMP_DIR dumpfile=full_01112013.dmp logfile= full_01112013.log schemas=fran
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.25 MB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "fran"."test1" 1.122 MB 1156 rows
. . exported "fran"."test2" 131.1 KB 3980 rows
. . exported "fran"."prueba" 7.710 KB 1 rows
. . exported "fran"."contacts" 6.054 KB 5 rows
. . exported "fran"."upgrading" 5.5 KB 2 rows
. . exported "fran"."partitioned" 5.492 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/opt/xxxxxxxxxx/3.0/admin/dbbackup/dump/xxxxxxxxxxx.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:15:41


successfully completed without errors.What was the reason then?

The reason can be because the connect string (TNS Name) is a load balancing connect string (remember I am in RAC)and whenever you try to use the connect string with expdp/impdp, it goes to the other node where the directory information is not available, or the directory might be a local folder which is not a shared one.

Hope it helps!!