Mir Sayeed Hassan – Oracle Blog

Export Dumpfile error with ORA-31617: unable to open dump file for write in RAC Database

Error occur while performing the Schema export in RAC Database.

Error Occur.

ORA-31693: Table data object "PRG1"."IRM":"P1575" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/SWITCHBKP/DUMPBKP/PRG1_08042024_07.dmp" for write
ORA-19505: failed to identify file "/SWITCHBKP/DUMPBKP/PRG1_08042024_07.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Check the database status.

SQL> select status, open_mode, database_role, version from v$database, v$instance;

STATUS   OPEN_MODE     DATABASE_ROLE     VERSION
---------------------------------------------------
OPEN     READ WRITE    PRIMARY          11.2.0.4.0

Verify the database is RAC or not.

SQL> show parameter cluster.

NAME                  TYPE       VALUE
--------------------------------------
cluster_database    boolean      TRUE

Create the Directory at OS Level and database level.

[oracle@prgdb ~]$ mkdir /SWITCHBKP/DUMPBKP/

SQL> create or replace directory DUMPBKP as '/SWITCHBKP/DUMPBKP/';
Directory created.

SQL> grant read, write on directory DUMPBKP to sys;
Grant succeeded.

SQL> grant read, write on directory DUMPBKP to system;
Grant succeeded.

Verify the directory.

SQL> select OWNER,DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where directory_name='DUMPBKP';

OWNER      DIRECTORY_NAME       DIRECTORY_PATH
------------------------------------------------
SYS          DUMPBKP         /SWITCHBKP/DUMPBKP/

Check the Parfile or expdp file

SOLUTION:

- In a RAC Database, If you are taking an export with parallel option and the physically datapump directory is not shared between the nodes/not exist in all the RAC Nodes, In this case the expdp wiil failed with above ORA-31617.
- Therefore the remote node is unable to access/write the dumps in that directory.
- To overcome this issue, Give the CLUSTER=N Parameter in expdp and dun it., this will start exporting of job and its write only on local node.

Initiate the export with Parfile:

[oracle@swdbn1 DUMPBKP]$ cat PRG1_08042024.par
USERID=system
DIRECTORY=DUMPBKP
DUMPFILE=PRG1_08042024_%U.dmp
LOGFILE=PRG1_08042024.log
COMPRESSION=ALL
PARALLEL=9
SCHEMAS=PRG1

Or

[oracle@prgdb DUMPBKP]$ expdp DIRECTORY=DUMPBKP DUMPFILE=PRG1_08042024_%U.dmp LOGFILE=PRG1_08042024.log COMPRESSION=ALL PARALLEL=9 SCHEMAS=PRG1 CLUSTER=N

Export: Release 11.2.0.4.0 - Production on Mon Apr 8 12:17:17 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: system
Password: ********* (Give system password)

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/**** DIRECTORY=DUMPBKP DUMPFILE=PRG1_08042024_%U.dmp LOGFILE=PRG1_08042024.log COMPRESSION=ALL PARALLEL=9 SCHEMAS=PRG1 CLUSTER=N
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3028. GB
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "PRG1"."IRM":"P1608" 1.716 GB 26490593 rows
. . exported "PRG1"."TRACEX":"P1574" 1.981 GB 30360107 rows
. . exported "PRG1"."TRNS":"P1575" 2.122 GB 32570201 rows
........
........
........continue... successfully.

======Hence tested & verified in our test env======