ORA-39002: invalid operation-ORA-39070: Unable to open the log file.-ORA-29283: invalid file operation-ORA-06512: at “SYS.UTL_FILE”, line 536-ORA-29283: invalid file operation

Cause:

While trying to export using expdp , the below error was thrown

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu

Export: Release 12.1.0.2.0 - Production on Mon Jun 28 23:25:14 2021

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Workaround:

Check the export directory from database

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
DATA_PUMP_DIR
/app01/oracle/product/base/admin/orcl12x/dpdump/
            0

Check if the directory exist in filesystem

[oracle@orcl19x ~]$ ls -lrt /app01/oracle/product/base/admin/orcl12x/dpdump/
ls: cannot access /app01/oracle/product/base/admin/orcl12x/dpdump/: No such file or directory

Give directory which already exist on your filesystem. Also check for oracle ownership and permissions on the export directory

[oracle@orcl19x ~]$ ls -lrt /data01/
total 10292

[oracle@orcl19x /]$ ls -lrt |grep data01
drwxrwxrwx.   7 oracle oinstall  4096 Jun 28 23:28 data01


[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu

Export: Release 12.1.0.2.0 - Production on Mon Jun 28 23:28:09 2021

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATAPUMP dumpfile=view_exp.dmp logfile=view_exp.log views_as_tables=test_vu
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SYS"."TEST_VU"                             5.656 KB      13 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /data01/view_exp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 28 23:29:00 2021 elapsed 0 00:00:09

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s