Export Dumpfile error with ORA-31617: unable to open dump file for write in RAC Database
Posted by Mir Sayeed Hassan on April 8th, 2024
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.