temporary files

[latifa]

Hi Erman,

Requests can't write temporary file as per error message follwoing:

FDPSTP failed due to ORA-20100: FND_FILE could not write to file o1083344.tmp.

According to DI:261693.1, utl_file_dir should be the same as APPLPTMP and rights to write should be done for both users: database and application. Checking this directory on the database server, The owner is database user and the right of (.) is for all like this:

drwxrwxrwx   2 oracle dba   35682 Jul 15 22:48 .
drwxrwxrwt 2169 root     sys       360645 Jul 16 15:22 ..

utl_file_dir contains APPLPTMP but it's not the first value (second position between 3 values).

I have done the test :

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

however no file in the APPLPTMP directory :(

What's wrong ?

This behavior is happening for all the child requets.


Thanks in advance for your help.

Regards,
LD

[latifa]

Hi Erman,

I found the test file in the first directory done in utl_file_dir .

shul we change it to the value of APPLPTMP ?

In this case, the databse should be restarted I think ?

Regards,
LD

[ErmanArslansOracleBlog]

Good..
Yes, you should change the UTL_FILE_DIR to have the same first value as the $APPLPTMP.

And yes, db restart is required for modifying the utl_file_dir.
So, update your spfile and restart your db.

[latifa]

Thank you Erman for your reply.

I have just two questions on this topic,
1. how can my temporary test file could be created in /usr/tmp (not in /usr/tmp/instance_name) and it doesn't give error (and many others requests) ?
I found other requests executed after and their temporary files were generated in /usr/tmp without errors.

2. shul we run concurrent program "Purge Concurrent Request and/or Manager Data" for the Entry ="All" and mode ="Age" ?

Regards,
LD

[ErmanArslansOracleBlog]

1) This is a configuration related thing. If it creates in /usr/tmp and then uses/reads it from there, you don't need to expect any errors.

2) "ENTITY = ALL"  means ->  Purge of concurrent requests, concurrent managers, request log files,                    
manager log files and report output files.
Mode : "AGE = Some Value" is for -> The number of days for which to save concurrent request history.

The only option which purges all tables is the option "ENTITY = ALL"

Well, it depends..Recommendations is to run it every day for purging X (maybe 30.. it depends on your case) days older data.
Using Entity ALL and Mode=Age together is a good option. For instance : ENTITY=ALL and Mode=Age and value is 30

Do not forget -> Concurrent request data will cause audit information to be lost.. The audit info which is used Signon Audit Concurrent Request Report.

Also see -> Concurrent Processing - Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) (Doc ID 104282.1)

[latifa]

Hi Erman,

We decided at that time to not change the value of APPLPTMP because it ran fine after that.

Actually, we have again the issue :)

Suggestion: how about to change the directory completly, I mean create a new file system specific for that aim (APPLPTMP and that utl_file_dir) ?

How can I find the the root cause to have the issue which is a bit strange ?

Thank you in advance for help.

LD

[ErmanArslansOracleBlog]

You said you ran fine after that?
Anyone else writing to this directory? Anything that may overwrite a temp file that your concurrent requests is trying to write? Having multiple EBS instance in the same system using the same APPLTMP may cause these kinds of issues as well.

If you need further investigation, send me the following;

1) the diskspace available in the filesystem or mount that stores the APPLTMP directory
2) The value of APPLTMP
3) The value of utl_file_dir
4)The name of the failing conc request
5)The logfile of the failing conc request
6)The actual error that you see.

[latifa]

Hi Erman,

The directory is the standard one : /usr/tmp/PROD1

the /usr/tmp is also used by the system.

OEM has detected that this directory was full at that time: I think no need to investigate more :)


1. Diskspace :

 /usr/tmp/PROD1$ df -k .
Filesystem           1024-blocks        Used   Available Capacity  Mounted on
rpool/VARSHARE/tmp      47185886     6700182     5172298    57%    /var/tmp

2. APPLPTMP=/usr/tmp/PROD1
3. utl_file_dir=/usr/tmp, /usr/tmp/PROD1, /data/flf/files/PROD1/data/out
4 & 5. the name of the concurrent request: l22096669.req
6. No actual errors

the prvious error message was : ORA-20100: Le fichier FND_FILE n'a pas pu écrire dans le fichier l1138366.tmp.
Une erreur du système d'exploitation s'est produite lors de l'opération d'écriture.
Contactez votre administrateur système. (TEMP_DIR=/usr/tmp/PROD1).

I am sorry it's in french, I'll translate if you need (but it's the same as the first one I posted in this topic)

Regards,
Latifa

[ErmanArslansOracleBlog]

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.

APPLTMP is something different. APPLTMP does not need a utl_file_dir entry. It  the temporary directory used by the application. (it is not accessed through the database.)

So anyways, APPLTPTMP directory must be accessible with Full permission and it must be the first entry in UTL_FILE_DIR on Database Node.

So why don't you change the utl_file_dir parameter accordingly?

[latifa]

Hi Erman,

How are you ? I hope fine.

The issue is still giving us headhake :)

The directory has been changed to utl_file_dir value but sometimes works and sometimes not !

Last time, they ajusted queue of concurrent managers and may be it was the reason for solving the issue for a while.

Any idea to check ? shull we ask Oracle (I mean SR)?

Thank you in advance for your precious advices.

Regards,
Latifa

[ErmanArslansOracleBlog]

Hi Latifa,

Thanks :)

*What is the filesystem that are using ? ( for the directories used in utl_file_dir)

*Do you have any other EBS instance running in the same servers?

*Do you have any incorrect directory present in utl_file_dir?

*Do you see the file reported in the error is present there? After getting the error, when you check the file is it present or not?

*What is the DB Version -> There is an event to be set in 19C (CDB level)

Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1) -- To enable the UTL_FILE functionality on which the supplemental UTL_FILE_DIR parameter depends, ensure that you have set the following event at the CDB level in your database initialization parameters: event='10946 trace name context forever, level 8454144'

*What is the actual thing that is failing and reporting this error? For instance: "Maintenance Work Order Detail Report"

*Are you aligned with the conf given in Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)?  

[latifa]

Hi Erman,

*What is the filesystem that are using ? ( for the directories used in utl_file_dir):

     APPLPTMP=/usr/tmp/PROD1
      utl_file_dir=/usr/tmp/PROD1,/usr/tmp,/Dir1/dir2/dir3/PROD1/data/out
     /usr is not a file system (df -k doesn't give it out)
        /Dir1/dir2/dir3 is file system
       
        APPLPTMP=/usr/tmp/PROD1
       
        when I am in /usr/tmp/PROD1, I do df -kh .
       
        Filesystem             Size   Used  Available Capacity  Mounted on
        rpool/VARSHARE/tmp      45G   321M        11G     3%    /var/tmp



*Do you have any other EBS instance running in the same servers?
No We have only One EBS instance.
However Database contains almost 10 instances (only one for EBS and it's in RAC)

*Do you have any incorrect directory present in utl_file_dir? I don't understand but I think No

*Do you see the file reported in the error is present there? After getting the error, when you check the file is it present or not?

Yes but they are empty.

*What is the DB Version -> There is an event to be set in 19C (CDB level)

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)
 -- To enable the UTL_FILE functionality on which the supplemental UTL_FILE_DIR parameter depends, ensure that you have set the following
 event at the CDB level in your database initialization parameters: event='10946 trace name context forever, level 8454144'

*What is the actual thing that is failing and reporting this error? For instance: "Maintenance Work Order Detail Report"


Requests are about immobilization and comptabilization and they specific programs


*Are you aligned with the conf given in Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite
 Releases 12.1 and 12.2 (Doc ID 2525754.1)?  

Every thing is ok about configuration

Thank you in advance for your help.

Regards,
Latifa

[ErmanArslansOracleBlog]

This may be related with RAC..
Maybe one session creates the files from node 1, and later other session tries to write to the file from node 2..
So having those temp directories like $APPLPTMP point to the directories on a shared file system accessible to all the RAC nodes, may fix the issue.

This is stated in Oracle docs -> Set the $APPLPTMP environment variable on all of the CP nodes to the value of the UTL_FILE_DIR entry in the init.ora on the database nodes. (**This value should point to a directory on a shared file system**)
 

[latifa]

Hi Erma,

You are right Erman, it's well written that APPLPTMP should point to a directory on a shared file system.

How can we test this :) ?

Thank you for your help.

Regards,
Latifa

[ErmanArslansOracleBlog]

You should check the filesystem that the directory pointed by the APPLPTMP resides.. If it is a local directory based on a local mount, you should change it to a shared mount.. Work with your OS & Storage admins.

[latifa]

Erman,

I am sorry I need more clarification: if the /usr/tmp/PROD1 is different for both nodes, so we can create the same file on both nodes ?!

Regards,

Latifa

[ErmanArslansOracleBlog]

Different.. so local..
So you should use NFS, or a cluster filesystem to have a shared directory structure.. shared directory means  having the same directory accessible from all the nodes..

[latifa]

Thank you Erman.

However, you confirme that APPLTMP is different from APPLPTMP (you said this UP).

Because, we faced previuosly a bug 21344904 - S_APPLTMP should not be shared (unpublished defect).

Regards,
Latifa

[ErmanArslansOracleBlog]

Yes APPLTMP is different.
APPLPTMP is for PLSQL..

Read the following MOS note for more details about the difference:

Difference between APPLPTMP and APPLTMP Directories in EBS (Doc ID 1355735.1)

[Samia]

Just additionnal information; we have to check both nodes of the RAC :)

sometimes it's not present so it may make issue.

Regards,
Latifa