Metalink Source: Doc ID 261693.1
APPLIES TO:Oracle Application Object Library - Version 11.5.10.2 to 12.0.6 [Release 11.5 to 12]
Oracle Concurrent Processing - Version 11.5.10.0 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
PURPOSEProvide hints for application administrators and DBAs on how to troubleshoot ORA-20100 when running PL/SQL programs on Apps Concurrent Processing.
TROUBLESHOOTING STEPS
If a PL/SQL concurrent program can not write to an external file, you will receive error messages similar to:
MSG-00102: Error Message :ORA-20100: File o0000071.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 378
ORA-06512: at "APPS.FND_FILE", line 473
ORA-06512: at "APPS.AP_TRIAL_BALANCE_PKG", line 192
REP-1419: 'beforereport': PL/SQL program aborted.
ORACLE error 20100 in FDPSTP
Cause: FDPSTP failed due to ORA-20100: File o0036176.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 410
ORA-06512
NOTE: Applications also produces temporary PL/SQL output files used in concurrent processing. These files are written to a location on the database server node specified by the APPLPTMP environment setting. The APPLPTMP directory must be the same directory as specified by the utl_file_dir parameter in your database initialization file.
Rapid Install sets both APPLPTMP and the utl_file_dir parameter to the same default directory. As the temporary files placed in this directory may contain context sensitive information, it should be a secure directory on the database server node with read and write access for the database server owner. In a multi-node system, the directory defined by APPLPTMP does not need to exist on the application tier servers. During an upgrade with AutoUpgrade, you must provide the utl_file_dir parameter value for the APPLPTMP environment setting.
To isolate where the problem is, verify the following:
1) Make sure your environment is properly sourced.
For example, if you don't have your database environment sourced properly, you will be missing ORA_NLSxx, that is used to indicate where Oracle RDBMS/client software can locate the
defintions of Charactersets (used in NLS_LANG or as NLS_CHARACTERSET/
NLS_NCHAR_CHARACTERSET),NLS_SORT, NLS_LANGUAGE (or derived/related parameters)
or NLS_TERRITORY (or derived/related parameters).
ORA_NLS10 missing causes a non recognition of the usual chr(10) as a new line.
2) Make sure that the name of the file is valid (the file name should not include characters like "^").
3) Make sure that APPLPTMP is set to a valid directory and that BOTH the applmgr user and the database user have read and write permissions on that directory (normally, it can be set to the same directory as APPLPTMP).
Make sure to run autoconfig if you happen to modify APPLPTMP in the autoconfig context file.
If you have a RAC-enabled environment, make sure you are not missing any mount points you may have pointing to APPLPTMP/APPLTMP
4) Make sure the directory pointed by APPLPTMP is the first entry on the utl_file_dir. Also, verify that all the entries on the utl_file_dir are valid and that the applmgr has read/write permissions.
If using an spfile, verify the proper syntax to set utl_file_dir:
Ex.
ALTER SYSTEM SET UTL_FILE_DIR='directory1','directory2' scope=spfile;
5) Check if you can write a file directly using FND_FILE, which is the package used by the Application. From sqlplus, connected as the apps user, run:
SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
This should dump a file on APPLPTMP.
If this test works, it would indicate that FND_FILE is working fine and the problem is possibly with the Application.
You may want to leave only one entry on utl_file_dir for this test.
FND_FILE uses the UTL_FILE package, which can only write to directories specified in init.ora. APPLPTMP is a special directory used only for PLSQL temporary files. When a concurrent program uses the FND_FILE package, the concurrent manager uses APPLPTMP as the directory to which it writes temporary files. Thus, APPLPTMP must be one of the directories listed in init.ora in order for FND_FILE to work.
6) Make sure that the file does not exit on the directory pointed by APPLPTMP.
This issue may occur if the temporary file being created by the concurrent program already exists in that directory and that already existing file is owned by another user, therefore it cannot override it. For example, you have a cloned instance, and the APPLPTMP directory still keeps the temp files that were created in the source instance.
Verify the file stated in the error message you are getting (for the example above File o0000071.tmp) does not exist in the directory pointed by $APPLPTMP. If it does, do the following:
1. Stop all services. This is to make sure no user is logged on and making use of $APPLPTMP
2. Remove all files from directory pointed by $APPLPTMP
3. Restart all services
4. Re-test the failed process
7) If still having problems, check if you can write a file using UTL_FILE, which is used by FND_FILE.
Run the PL/SQL below, after changing <first entry on utl_file_dir> to the first entry on utl_file_dir (you may want to leave just one entry on utl_file_dir for this test).
set serveroutput on
DECLARE
file_location VARCHAR2(256) := '<first entry on utl_file_dir>';
file_name VARCHAR2(256) := 'utlfile1.lst';
file_text VARCHAR2(256) := 'THIS IS A TEST';
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen(file_Location, file_name, 'W');
UTL_FILE.put_line(file_id, file_text);
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;
/
This program should dump a file on the requested directory. If the test fails, the problem is probably on the Database side.
If it works, the problem is probably on FND_FILE. In this scenario, check the versions of AFCPPIOS.pls and AFCPPIOB.pls.
8) If still having problems, go to your temporary files directory (i.e. $APPLPTMP) and check is not full. If it is, do the following:
1. Shutdown all the concurrent processes and managers.
2. Verify that no FNDLIBR process is running on (ps -ef | grep FNDLIBR).
3. Delete all the files in the temporary directory.
4. Run the CMCLEAN.SQL script from Note 134007.1 (don't forget to commit)
5. Run the concurrent program "Purge Concurrent Request and/or Manager Data" for the Entry ="All" and mode ="Age"
6. Test the issue.
The mode value for the concurrent program "Purge Concurrent Request and/or Manager Data" can be the number of days before which the logs will be purged.
7. Make sure to verify UTL_FILE_DIR and APPLPTMP are set to same directory.
9) If still having problems, your SPFILE may be corrupt. Do the following to recreate your SPFILE:
1. Login to the database server using oraprod user
2. Create a new PFILE (aka init<sid>.ora file) from the current SPFILE
2.1. cd $ORACLE_HOME/dbs
2.2. sqlplus " / as sysdba"
2.3. SQL> create pfile from spfile
2.4. Make sure that there is a file with name "init<SID>.ora" has been created today
3. Edit "init<SID>.ora" file and change the value of the utl_file_dir parameter to a readable/writable directory (i.e. $APPLPTMP, "/usr/tmp")
4. Either rename the original spfile in order to startup the database with the new pfile or create a new spfile for pfile.
8. Restart the database
APPLIES TO:Oracle Application Object Library - Version 11.5.10.2 to 12.0.6 [Release 11.5 to 12]
Oracle Concurrent Processing - Version 11.5.10.0 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
PURPOSEProvide hints for application administrators and DBAs on how to troubleshoot ORA-20100 when running PL/SQL programs on Apps Concurrent Processing.
TROUBLESHOOTING STEPS
If a PL/SQL concurrent program can not write to an external file, you will receive error messages similar to:
MSG-00102: Error Message :ORA-20100: File o0000071.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 378
ORA-06512: at "APPS.FND_FILE", line 473
ORA-06512: at "APPS.AP_TRIAL_BALANCE_PKG", line 192
REP-1419: 'beforereport': PL/SQL program aborted.
ORACLE error 20100 in FDPSTP
Cause: FDPSTP failed due to ORA-20100: File o0036176.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 410
ORA-06512
NOTE: Applications also produces temporary PL/SQL output files used in concurrent processing. These files are written to a location on the database server node specified by the APPLPTMP environment setting. The APPLPTMP directory must be the same directory as specified by the utl_file_dir parameter in your database initialization file.
Rapid Install sets both APPLPTMP and the utl_file_dir parameter to the same default directory. As the temporary files placed in this directory may contain context sensitive information, it should be a secure directory on the database server node with read and write access for the database server owner. In a multi-node system, the directory defined by APPLPTMP does not need to exist on the application tier servers. During an upgrade with AutoUpgrade, you must provide the utl_file_dir parameter value for the APPLPTMP environment setting.
To isolate where the problem is, verify the following:
1) Make sure your environment is properly sourced.
For example, if you don't have your database environment sourced properly, you will be missing ORA_NLSxx, that is used to indicate where Oracle RDBMS/client software can locate the
defintions of Charactersets (used in NLS_LANG or as NLS_CHARACTERSET/
NLS_NCHAR_CHARACTERSET),NLS_SORT, NLS_LANGUAGE (or derived/related parameters)
or NLS_TERRITORY (or derived/related parameters).
ORA_NLS10 missing causes a non recognition of the usual chr(10) as a new line.
2) Make sure that the name of the file is valid (the file name should not include characters like "^").
3) Make sure that APPLPTMP is set to a valid directory and that BOTH the applmgr user and the database user have read and write permissions on that directory (normally, it can be set to the same directory as APPLPTMP).
Make sure to run autoconfig if you happen to modify APPLPTMP in the autoconfig context file.
If you have a RAC-enabled environment, make sure you are not missing any mount points you may have pointing to APPLPTMP/APPLTMP
4) Make sure the directory pointed by APPLPTMP is the first entry on the utl_file_dir. Also, verify that all the entries on the utl_file_dir are valid and that the applmgr has read/write permissions.
If using an spfile, verify the proper syntax to set utl_file_dir:
Ex.
ALTER SYSTEM SET UTL_FILE_DIR='directory1','directory2' scope=spfile;
5) Check if you can write a file directly using FND_FILE, which is the package used by the Application. From sqlplus, connected as the apps user, run:
SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
This should dump a file on APPLPTMP.
If this test works, it would indicate that FND_FILE is working fine and the problem is possibly with the Application.
You may want to leave only one entry on utl_file_dir for this test.
FND_FILE uses the UTL_FILE package, which can only write to directories specified in init.ora. APPLPTMP is a special directory used only for PLSQL temporary files. When a concurrent program uses the FND_FILE package, the concurrent manager uses APPLPTMP as the directory to which it writes temporary files. Thus, APPLPTMP must be one of the directories listed in init.ora in order for FND_FILE to work.
6) Make sure that the file does not exit on the directory pointed by APPLPTMP.
This issue may occur if the temporary file being created by the concurrent program already exists in that directory and that already existing file is owned by another user, therefore it cannot override it. For example, you have a cloned instance, and the APPLPTMP directory still keeps the temp files that were created in the source instance.
Verify the file stated in the error message you are getting (for the example above File o0000071.tmp) does not exist in the directory pointed by $APPLPTMP. If it does, do the following:
1. Stop all services. This is to make sure no user is logged on and making use of $APPLPTMP
2. Remove all files from directory pointed by $APPLPTMP
3. Restart all services
4. Re-test the failed process
7) If still having problems, check if you can write a file using UTL_FILE, which is used by FND_FILE.
Run the PL/SQL below, after changing <first entry on utl_file_dir> to the first entry on utl_file_dir (you may want to leave just one entry on utl_file_dir for this test).
set serveroutput on
DECLARE
file_location VARCHAR2(256) := '<first entry on utl_file_dir>';
file_name VARCHAR2(256) := 'utlfile1.lst';
file_text VARCHAR2(256) := 'THIS IS A TEST';
file_id UTL_FILE.file_type;
BEGIN
file_id := UTL_FILE.fopen(file_Location, file_name, 'W');
UTL_FILE.put_line(file_id, file_text);
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;
/
This program should dump a file on the requested directory. If the test fails, the problem is probably on the Database side.
If it works, the problem is probably on FND_FILE. In this scenario, check the versions of AFCPPIOS.pls and AFCPPIOB.pls.
8) If still having problems, go to your temporary files directory (i.e. $APPLPTMP) and check is not full. If it is, do the following:
1. Shutdown all the concurrent processes and managers.
2. Verify that no FNDLIBR process is running on (ps -ef | grep FNDLIBR).
3. Delete all the files in the temporary directory.
4. Run the CMCLEAN.SQL script from Note 134007.1 (don't forget to commit)
5. Run the concurrent program "Purge Concurrent Request and/or Manager Data" for the Entry ="All" and mode ="Age"
6. Test the issue.
The mode value for the concurrent program "Purge Concurrent Request and/or Manager Data" can be the number of days before which the logs will be purged.
7. Make sure to verify UTL_FILE_DIR and APPLPTMP are set to same directory.
9) If still having problems, your SPFILE may be corrupt. Do the following to recreate your SPFILE:
1. Login to the database server using oraprod user
2. Create a new PFILE (aka init<sid>.ora file) from the current SPFILE
2.1. cd $ORACLE_HOME/dbs
2.2. sqlplus " / as sysdba"
2.3. SQL> create pfile from spfile
2.4. Make sure that there is a file with name "init<SID>.ora" has been created today
3. Edit "init<SID>.ora" file and change the value of the utl_file_dir parameter to a readable/writable directory (i.e. $APPLPTMP, "/usr/tmp")
4. Either rename the original spfile in order to startup the database with the new pfile or create a new spfile for pfile.
8. Restart the database
No comments:
Post a Comment