ORA-29283 : invalid file operation is quite a common error.
It happens mostly because of the two reasons
- 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 directory does not have appropriate permission for the Oracle user from which database is started.
If you are doing any group level change on Unix and oracle users is involved,it is advised to bounced both the database and listener also
This can also happen if you have create 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 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
- 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, Truss file shows a block of file is read and subsequent blocks cannot be read or corrupted because of NLS (ORA_NLS10) settings.
Following error might be shown in 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 setting across the database and listener.
Either both the database and Listener has 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 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 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