Wednesday

ORA-20100 and FIND_FILE

BEGIN
*
ERROR at line 1:
ORA-20006: ORA-20100: File l0005230.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.OUR_CUSTOM_CODE", line 949
ORA-06512: at line 2

Thats how I started my day. To say I hate ORA-20100 errors would not accurately portray my feelings. However, after reading Tom Kytes blog posting titled "Why do people do this?" I know why. Guess what is on line 949. ;)

Unlike previous occurrences of this error, its a concurrent manager temp file. So I know right away that the directory in question is pointed to by the environment variable APPLPTMP.

We have hit this error before and usually its the utl_file_dir database parameter. I guess some of us DBA's have fat fingers and even tho we double check this parameter, sometimes a typo sneaks through during cloning. I have a script which writes to each of our utl file directories. So I could rule this out as being a problem.

I can't remember where I obtained the code below, but here is what I use. I write to a file, then try and read from it. Exceptions below give me a good indication of what the problem may be.

CREATE OR REPLACE PROCEDURE utl_test IS
file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
col1 NUMBER; -- C1 retrieved from testtab table
retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file
BEGIN
file_handle := UTL_FILE.FOPEN('dir1', 'filetest.txt', 'W');
UTL_FILE.PUT_LINE(file_handle, 'This is a test');
UTL_FILE.FCLOSE(file_handle);
file_handle :=utl_file.fopen('dir1', 'filetest.txt', 'R');
UTL_FILE.GET_LINE(file_handle, retrieved_buffer);
dbms_output.put_line(retrieved_buffer);
utl_file.fclose(file_handle);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(file_handle);Troubleshooting ORA-20100 on Concurrent Processing
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(file_handle);
END;



The next common resolution was to check the location to which the APPLPTMP environment variable points. We have multiple environments on our test dev servers and its not uncommon for one environment to own a tmp file, thus blocking another environment from writing to it. In this case, the file l0005230.tmp existedand was owned by the proper user but was zero size.

-rw-r--r-- 1 oravis dba 0 Feb 5 10:06 l0005230.tmp

We have hit OS fragmentation issues in the past. Just to be safe I copied a large file to the tmp directory without a problem. We have had partitions with 20GB free and have been unable to create a 100k file. A system reboot usually fixed that.

I was out of ideas. I have never gotten this far with this particular error before. So I fired up Metalink. Note:261693.1 Troubleshooting ORA-20100 on Concurrent Processing, listed the options I have tried above and a few more. One suggestion is to use FND_FILE which is the package used by the application to write to OS files.

SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

If everything is setup correctly, this will write to a file in the directory pointed to by APPLPTMP. Don't go to this directory looking for a file named FND_FILE.LOG. You won't find it. The data, "THIS IS A TEST", will be written to a temp file. So look at tmp files created around the time you executed that command.

$cat l0006340.tmp
THIS IS A TEST


So at this point, none of the suggested troubleshooting steps have pinpointed the problem. To summarize, we have verified:
  • that the UTL_FILE_DIR init parameter is correct and each location is writeable.
  • that the application should be able to write to the OS using FND_FILE. The file does get created but the app can't write data to it.
The only thing left was to try restarting services. A bounce of the concurrent managers didn't resolve the issue. So we bounced the entire application and database. This finally resolved the issue. Why? No idea. There were a bunch of processes that didn't shutdown properly so we killed them. Maybe one of those had a resource open that was blocking. If you have encountered this before and found the root cause, feel free to share the information. ;)

4 comments:

Anonymous said...

Hi Dave,

I have seen a similar issue a long time ago and it was because another process had the file handle for the same file. From your evidence it could be this. One way is to check for open files with lsof, alternately you could also attach truss to the process that were detached.

cheers

Pete

Unknown said...

Hey Pete,

Great idea, I didn't even think of it. I've been looking for good opportunities to learn a little more about those commands.

tnx!

Anonymous said...

If you had to bounce your database server to fix the issue, it might probably be because your Utl_file_dir init parameter isnt dynamic and you added it after the database started.

Unknown said...

Thanks for the feedback. In this case tho, I used a script which writes to each of the utl_file_dir locations to verify everything is ok. The file did get created by the application but no data would be pushed to it. It was a weird problem.