Archive

Posts Tagged ‘expdp’

expdp with sys without knowing password

February 19, 2013 6 comments

Simple but very handy way of taking exports without knowing database password.

expdp \’/ as sysdba\’ DIRECTORY=DATA_PUMP_DIR DUMPFILE=example.dmp LOGFILE=example.log SCHEMAS=MYSCHEMA

Advertisements
Categories: tips Tags: , ,

Why parallel expdp gives unable to write error

June 7, 2012 4 comments

Problem

Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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 “/gecici/expdp/MYSCHEMAS04.expdp” for write
ORA-19505: failed to identify file “/gecici/expdp/MYSCHEMAS04.expdp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Cause

Error triggered, because it is RAC and consists of two nodes, and the filesystem mounted only on one of the nodes.

I thought that , if datapump had started without parallel option, it would not go to other db node, and everything would go well.
Because it is not the case… Datapump is started in parallel mode, and it tries to start parallel processes in ALL nodes of the cluster.

Solution

In order to prevent to start the parallel process in other node, I changed the parameter parallel_force_local value as true.

And also ensure my TNS entry will allow to go to only to desired node, by giving SID instead of SERVICE_NAME in the connection description as below:

MYDB1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydb1.turkcell.tgc)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MYDB1)))

But it still continue to give the same error.

Here comes the solution:
In 11.2, CLUSTER parameter has come. It will enable expdp to work properly in RAC as below:

expdp system@MYDB1 SCHEMAS=MYSCHEMA1,MYSCHEMA2,MYSCHEMA3 DIRECTORY=DATAPUMPDIR LOGFILE=MYDB.log DUMPFILE=MYDB%U.expdp COMPRESSION=ALL EXCLUDE=STATISTICS PARALLEL=8 CLUSTER=N

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.