ORA-29283: invalid file operation is quite a common error.
It happens mostly because of the two reasons
(1) The directory and file should have appropriate permission at the OS level for the same user which started the Oracle database
For example
The directory does not exists on OS
ls /u555/app/oracle
No such file or directory
SQL> declare
F_LOG utl_file.file_type;
begin
F_LOG := utl_file.fopen('/u555/app/oracle','k', 'w');
end;
/ 2 3 4 5 6
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
This also happens if the directory does not have the appropriate permission for the Oracle user from which the database is started.
If you are doing any group-level change on Unix and oracle users are involved, it is advised to bounce both the database and listener also
This can also happen if you have created an oracle directory like this
SQL> create directory TESTDIR as '/u555/oracle/tmp'; SQL> grant read,write on directory TESTDIR to public;
But it is not existing in OS
SQL> declare
F_LOG utl_file.file_type;
begin
F_LOG := utl_file.fopen('TESTDIR','k', 'w');
end;
/ 2 3 4 5 6
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
This can also happen when you are reading the file and the OS oracle user does not have permission to do it
SQL> declare
F_LOG utl_file.file_type;
begin
F_LOG := utl_file.fopen('/u555/app/oracle','k', 'r');
end;
/ 2 3 4 5 6
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
(2) The second most reason for this failure is the setting of ORA_NLS10 in the Oracle database and listener environment.
If you enable truss on the process, the Truss file shows a block of the file is read and subsequent blocks cannot be read or corrupted because of NLS (ORA_NLS10) settings.
The following error might be shown in the truss
file read error
file write error
internal error
invalid maximum line size
invalid file name
directory access denied
invalid offset specified for seek
file remove operation failed
file rename operation failed
A stream error occurred during compression or uncompression.
A data error occurred during compression or uncompression.
invalid mime header tag
invalid encoded string
The compressed representation is too big
It is recommended to have consistent settings across the database and listener.
Either both the database and Listener have the same ORA_NLS10 set or both have the values unset.
Setting ORA_NLS10
ORACLE_SID=TEST ORA_NLS10=< > sqlplus / as sysdba shutdown immediate startup lsnrctl stop TEST lsnrctl start TEST
Unsetting ORA_NLS10
ORACLE_SID=TEST unset ORA_NLS10 sqlplus / as sysdba shutdown immediate startup lsnrctl stop TEST lsnrctl start TEST
If the database and listener are being started using Oracle cluster, we can do this setting in the srvctl.
We can check the setting of ORA_NLS using the below command in the OS
Find the PMON process
ps -ef|grep pmon|grep TEST For Linux: $ strings /proc/<pmon process ID>/environ | grep NLS For Solaris: $ pargs -e <process ID> | grep NLS
Find the listener process
ps -ef|grep list|grep TEST For Linux: $ strings /proc/<pmon process ID>/environ | grep NLS For Solaris: $ pargs -e <process ID> | grep NLS
Example
We can do following the to reproduce the issue
ORA_NLS10=< >
sqlplus / as sysdba
shutdown immediate
startup
lsnrctl stop TEST
lsnrctl start TEST
Now unset ORA_NLS10
Now Setup Bequeath connection
sqlplus / as sysdba
SQL> declare
F_LOG utl_file.file_type;
begin
F_LOG := utl_file.fopen('TESTDIR','k', 'w');
end;
/ 2 3 4 5 6
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
I hope you like this post on How to solve ORA-29283: invalid file operation and this helps in your troubleshooting
Also,Read
How to change apps password in R12.2
FNDLOAD Command: FNDLOAD command/loader is a general-purpose utility that moves structured data between a text file and a database in an EBS environment.
ORA-29280: invalid directory path
ORA-29285: file write error