Datapump

ORA-31617: unable to open dump file “/export/data.dmp” for write

by

This morning a user came to me because they were getting what appeared to be a Permission error

FLASHBACK automatically enabled to preserve database integrity.
Starting "myschema"."SYS_EXPORT_SCHEMA_06":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 252.1 GB
ORA-31693: Table data object "myschema"."mytable" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/nfs/unix/export/myschema01.dmp" for write
ORA-19505: failed to identify file "/nfs/unix/export/myschema01.dmp"
ORA-17503: ksfdopn:4 Failed to open file /nfs/unix/export/msychema01
ORA-17500: ODM err:File does not exist

I checked the directory listed, /nfs/unix/export. It is mounted on both nodes of the cluster.

Checked file permissions, using touch I created a file as the oracle user, and deleted it, no permissions issues there.

It turns out it was the directory object itself! I looked at the job definition, and it was using the directory /export

Export is a link to the /nfs/unix/export directory. The second node had link, but not the first! What threw me off was the directory object itself was only pointing to /export, but the error message was pointing to the full NFS path. Dropping and re-creating the directory object in the database pointing to /nfs/unix/export/ resolve the issue.

ORA-02396: exceeded maximum idle time, please connect again

by

I was doing a schema import last night, over a database link. Lack of sleep had set in and I was getting the following error

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX 
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [INDEX:"SCHEMA_OWNER"."TABLE2"] 
ORA-02396: exceeded maximum idle time, please connect again 

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 
ORA-06512: at "SYS.KUPW$WORKER", line 11259 

----- PL/SQL Call Stack ----- 
object line object 
handle number name 
0x2156ad620 27116 package body SYS.KUPW$WORKER 
0x2156ad620 11286 package body SYS.KUPW$WORKER 
0x2156ad620 13515 package body SYS.KUPW$WORKER 
0x2156ad620 3173 package body SYS.KUPW$WORKER 
0x2156ad620 12035 package body SYS.KUPW$WORKER 
0x2156ad620 2081 package body SYS.KUPW$WORKER 
0x20d5f9bb0 2 anonymous block 

KUPP$PROC.CHANGE_USER 
KUPP$PROC.CHANGE_USER 
NONE connected to AAR00287 
DBMS_LOB.TRIM 
DBMS_LOB.TRIM 
DBMS_LOB.TRIM 
DBMS_LOB.TRIM 
DBMS_METADATA.FETCH_XML_CLOB 
DBMS_METADATA.FETCH_XML_CLOB 
In procedure DETERMINE_FATAL_ERROR with ORA-02396: exceeded maximum idle time, please connect again

Now if you aren’t tired this should be a pretty obvious fix, the idle_time value on the remote server was set to 60 and was disconnecting me during the index build section of the import. I altered the profile to give my user/profile unlimited idle_time. Problem solve, import complete.

Mind your spaces

by

I am trying to export a database, everything except for a single table. I need to send a copy of the database to the vendor, but they don’t need a table that takes up %90 of the space in the database due to it storing a massive number of BLOBs and CLOBs

This should be really simple

$ expdp "'/ as sysdba'" directory=A dumpfile=fullexport.dmp exclude=TABLE:"IN ('TABLEOFCLOBS')"

Export: Release 11.2.0.4.0 - Production on Fri Jun 17 10:37:01 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression

That syntax looks correct to me! Maybe I copy/pasted in a special character or something…. I type out the full command by hand and get the same error. I am stumped!

idontgetit

I worked the googles on the internet machine, and see a bunch of examples that look pretty much identical to mine. Then I notice it. I have a space after the “IN” key word.

$ expdp "'/ as sysdba'" directory=A dumpfile=fullexport.dmp exclude=table:"IN('TABLEOFCLOBS')" FULL=Y

Export: Release 11.2.0.4.0 - Production on Fri Jun 17 10:38:14 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" directory=A dumpfile=exportfile.dmp exclude=table:IN('TABLEOFCLOBS') FULL=Y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 53.28 GB

Thwarted by spaces again! It seems trival, and kind of incorrect since the “IN” in a sql statement needs that space there.