Oracle 11.2 Datapump ORA-31617, ORA-19505, ORA-27037

On a RAC setup, when I was performing an expdp export, it error out with following stack

expdp dumpfile=test_%u.dmp logfile=test.log directory=temp_expdp schemas=test parallel=4

Multiple errors similar to following

ORA-31693: Table data object "TEST"."TEST_15" failed to load/unload 
and is being skipped due to error: 
ORA-31617: unable to open dump file "/datadir/temp_expdp
/TEST_test_03.dmp" for write 
ORA-19505: failed to identify file "/datadir/temp_expdp
/TEST_test_03.dmp"
 ORA-27037: unable to obtain file status

Then after some investigation found that, the directory (DBA_DIRECTORY physical path) is missing from one of the nodes.

So tried with another CLI, where I connected the using a service, which is only available on one node of the cluster

expdp user/pwd@node1svr  dumpfile=test_%u.dmp logfile=test.log 
directory=temp_expdp schemas=test parallel=4

But datapump export failed again with the same error

Again executed the CLI & checked the session details from gv$session view.

       SID MACHINE      INST_ID
———- ————————————   ———–      
1029    rac-srv-01oracle@rac-srv-01 (DM00)     1        
21        rac-srv-01oracle@rac-srv-01 (DW02)     1      
1534   rac-srv-01oracle@rac-srv-01 (DW00)     1        
18       rac-srv-02oracle@rac-srv-02 (DW00)     2      
521     rac-srv-02oracle@rac-srv-02 (DW01)     2
 

This output shows that, connections are distributed to both the nodes, hence datapump error out.

So what are the options

1. Create a directory object on clustered file system

2. Export without using parallel clause

expdp dumpfile=test_%u.dmp logfile=test.log directory=temp_expdp 
schemas=test

3. Use CLUSTER option in expdp CLI

expdp dumpfile=test_%u.dmp logfile=test.log directory=temp_expdp 
schemas=test cluster=n

This option will to constrain the job to run on the instance where it is started

 

Advertisements
This entry was posted in Oracle Data Pump and tagged , , . Bookmark the permalink.

One Response to Oracle 11.2 Datapump ORA-31617, ORA-19505, ORA-27037

  1. gold price says:

    Once all of the required Oracle Database components have been copied from linux1 to linux3, the OUI prompts to execute the root.sh on the new Oracle RAC node. Navigate to the /u01/app/oracle/product/10.2.0/db_1 directory on linux3 and run root.sh as the “root” user account.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s