Oracle :ORA-29283: INVALID FILE OPERATION USING ORACLE USER AS SECONDARY GROUP ON UNIX

Problem :

The permissions on directory are :
drwxrwxr-x 2 oraext oraext 1392 2008-10-29 16:32 files
oraext is the secondary group of oracle user.

1. When the anonymous block is run as SYS user it runs succesfully.

create or replace directory dir_temp as '/home/oraext/files';
declare
f utl_file.file_type;
begin
f := utl_file.fopen('DIR_TEMP', 'something.txt2', 'w');
utl_file.put_line(f, 'line one: some text');
utl_file.put_line(f, 'line two: more text');
utl_file.fclose(f);
end;/


2. When logged in to OS as oracle user and run the following command the file gets created .

However

When the anonymous block is run as an other schema, the following error is thrown :
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at line 4

READ and WRITE on the directory has been granted to the other schema.

Solution :

Restart the listener. The issue got resolved.

QUESTION========
What could be the reason for getting ora-29283 error , if though the oracle user can create a file using touch command on the OS ?

ANSWER======
Once you have done any changes to the folders accessed by utl_file directoiries you should always restart the listener process. Restarting the listener will inherit the recently changes permission and hence enabling oracle user to write a file from database using utl_file package succesfully.

Oracle : Recompling invalid object using utlrp.sql and utlprp.sql

Reference :
http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php

utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0".

The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

Oracle : Example to set the utl_file_dir parameter

Syntax :
alter system set utl_file_dir='{directory 1}', '{directory 2}' ... '{directory n}' scope=spfile;

Example :
alter system set utl_file_dir='/home/oracle/a', '/home/oracle/b' scope=spfile;

Take note that this parameter needs a restart to be effective.

Oracle : PL/SQL to test writing to an OS directory from the database.

Many time I had developer hitting errors as such
ERROR : ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation


Below is an sql I got from somewhere that can be used to test out writing / reading from the directory.

( Also check that you have set the parameter utl_file_dir correctly )

SET SERVEROUTPUT ON
DECLARE
fid UTL_FILE.FILE_TYPE;
v VARCHAR2(32767);

PROCEDURE recNgo (str IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('UTL_FILE error ' str);
UTL_FILE.FCLOSE (fid);
END;

BEGIN

/* Change the directory name to one to which you at least
THINK you have read/write access.
*/

fid := UTL_FILE.FOPEN ('{change directory name here}', '{change existing file name here}', 'R');
UTL_FILE.GET_LINE (fid, v);
dbms_output.put_line (v); UTL_FILE.FCLOSE (fid);

fid := UTL_FILE.FOPEN ('{change directory name here}', '{change new file name here}', 'W');
UTL_FILE.PUT_LINE (fid, v); UTL_FILE.FCLOSE (fid);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN recNgo ('invalid_path');
WHEN UTL_FILE.INVALID_MODE THEN recNgo ('invalid_mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN recNgo ('invalid_filehandle');
WHEN UTL_FILE.INVALID_OPERATION THEN recNgo ('invalid_operation');
WHEN UTL_FILE.READ_ERROR THEN recNgo ('read_error');
WHEN UTL_FILE.WRITE_ERROR THEN recNgo ('write_error');
WHEN UTL_FILE.INTERNAL_ERROR THEN recNgo ('internal_error');

END;/

Hi

Ok just another blog that I will be starting.

Just an short introduction about myself, I am Andrew, currently a DBA working mainly on Oracle databases. Been in IT line for approximately 8 years.

The purpose of this blog is to record down the daily problems and solutions I had encountered.

In doing so I hope to build an knowleage base which can be referenced easily by myself as well as many others who are facing similar situations. :)