Oracle Release 12 : Concurrent Programs are failing with ORA-20100

Dear Readers,

Recently we had ORA-20100: Error: FND_FILE failure. Unable to create file when users were submitting the concurrent request.

The following error message occurred in the logfile,

----------------------------------------------------
Error Is : ORA-20100: Error: FND_FILE failure. Unable to create file, l0862319.tmp in the directory, /apps/orarpt/TEST/utl.
You will find more information in the request log.
-----------------------------------------------------

When we checked the directory /apps/orarpt/TEST/utl at database side, the tmp l0862319.tmp file created at the time of program execution.

So, all directory/file system permission settings are perfect.

Later when we tried to write 100 lines ( from Application node as APPS users ) in tmp on /apps/orarpt/TEST/utl directory using UTL_FILE package, we were able to create it.

set serveroutput on
DECLARE
file_location VARCHAR2(256) := '/apps/orarpt/TEST/utl';
file_name VARCHAR2(256) := 'utlfile.lst';

idx number := 1;
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen(file_Location,file_name, 'W');
for idx in 1..100 loop
UTL_FILE.put_line(file_id, idx);
end loop;
UTL_FILE.fclose(file_id);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH

THEN dbms_output.put_line('Invalid path ' || SQLERRM);
WHEN OTHERS
THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM);
END;

But when I tried to write 1000 lines, I was getting the following error,

SQL> set serveroutput on
DECLARE
file_location VARCHAR2(256) := '/apps/orarpt/TEST/utl';
file_name VARCHAR2(256) := 'utlfile1.lst';

idx number := 1;
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen(file_Location,file_name, 'W');
for idx in 1..1000 loop
UTL_FILE.put_line(file_id, idx);
end loop;
UTL_FILE.fclose(file_id);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH

THEN dbms_output.put_line('Invalid path ' || SQLERRM);
WHEN OTHERS
THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM);
END; SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
20 /
Others -29285 ORA-29285: file write error

PL/SQL procedure successfully completed.
SQL>

After doing lot of investigation, I bounced all DB services including the DB Listener and It fixed the issue.

After Bounce :

SQL> set serveroutput on
DECLARE
file_location VARCHAR2(256) := '/apps/orarpt/TEST/utl';
file_name VARCHAR2(256) := 'utlfile3.lst';

idx number := 1;
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen(file_Location,file_name, 'W');
for idx in 1..10000 loop
UTL_FILE.put_line(file_id, idx);
end loop;
UTL_FILE.fclose(file_id);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH

THEN dbms_output.put_line('Invalid path ' || SQLERRM);
WHEN OTHERS
THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM);
END; SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
20 /

PL/SQL procedure successfully completed.

SQL>


And users confirmed that the concurrent programs are completing successfully.

So, Not sure what is the Root cause for this issue, I just simply bounced DB services including listener.

Comments are most welcome.

Thanks for reviewing our blog...