How to solve ORA-29283 : invalid file operation

Last updated on April 4th, 2019 at 02:03 am

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 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
  1. 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

Leave a Reply