View other's Output files and Log files within a Responsibility

By default, user can not view the Output of concurrent program/request submitted by others. This setup will allow all users of a responsibility to view each others log and report outputs.

. Login to Apps as SYSADMIN
. Navigate to SysAdmin > Profile > System.
. From the "Find System Profile Values" window query up the Profile named "Concurrent: Report Access Level"
. In the "System Profile Values" window find "Concurrent: Report Access Level" profile.
. Set the Site Column LOV to "Responsibility" (be careful not to set the Site Column to “User” because that will completely open the system up where all requests will be available to all users regardless of level)
. Set the "Concurrent: Report Access Level" profile to "Responsibility" (Setting this value to “User” here exhibits the behavior where a user is restricted from viewing other users’ concurrent requests).

The important profile value to set is "Concurrent: Report Access Level", and also the setting is at the responsibility level, instead of system level.

UPDATE: For R12, Profile option "Concurrent: Report Access Level" does not exist and it is more difficult to see other's Output file. Check Oracle document IDs 804296.1 and 2112509.1

Directory defined by $APPLPTMP & $APPLTMP

Oracle Doc ID 1355735.1 (Difference between APPLPTMP and APPLTMP Directories in EBS) tells the difference between APPLPTMP and APPLTMP Directories in EBS. Here is more info on troubleshooting. 

  

- Variable $APPLPTMP is defined on EBS apps server, but the directory does not have to exist on the apps forms/web server.

Instead, the directory has to exist on the database server. Otherwise, below error will show up in CM program log. I also saw similar error when the folder has too many files.

ORACLE error 20100 in FDPSTP
Cause: FDPSTP failed due to ORA-20100: File o0275459.tmp creation for FND_FILE filed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.FND_FILE", line 410
ORA-06512

And, make sure the directory is part of the database parameter utl_file_dir:
SQL> select name, value from v$parameter where name = 'utl_file_dir';

To test its setup, run
SQL> exec FND_FILE.PUT_LINE(fnd_file.log, 'Hello World!');
A new file with a name like l000xxxx.tmp is created in the first directory in the db parameter utl_file_dir, containing 'Hello World!'. 

UPDATE: *warning* In 12c database, after above statement, it needs to run CLOSE line. Otherwise the log file size may be 0!
SQL> exec fnd_file.close;

Below line will work but will not create a file o000xxxx.tmp and write one line to it until CLOSE line was run.

SQL> exec FND_FILE.PUT_LINE(fnd_file.output, 'Hello output!');

SQL> exec fnd_file.close;

- Variable $APPLTMP is the location to save temporary files from forms run-time on the forms/web server. A cron job can be scheduled to delete old files in that directory.
One day, server experienced files filling up $APPLTMP quickly with files in 2GB size:
-rw-r----- 1 user1 users 2147483647 Feb 4 07:12 OFN6pYCK.t
-rw-r----- 1 user1 users 2147483647 Feb 4 07:16 OFNF0HJf.t
-rw-r----- 1 user1 users 2007334912 Feb 4 07:39 OFVuhSzG.t

The file holds entries of hours earlier than the file's timestamp:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Subledger Accounting: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
XLAACCUP module: Accounting Program
+---------------------------------------------------------------------------+
Current system time is 04-FEB-20XX 01:02:47 
... ... ...

I found debug log was turned on by "FND: Debug Log Enabled =Yes" on site level. So, make sure below settings are off in Profile options:

FND: Debug Log Enabled = No
SLA: Enable Diagnostics = No
AR: Enable Debug Message Output = No

- Variable s_forms_tmpdir in CONTEXT_FILE also defines a location holding forms temp files.
On a busy day, Sys Admin saw /tmp was almost full by 7 GB file /tmp/file75jBt9.TMP, while EBS R12 variable s_forms_tmpdir sets to /tmp directory. Even this file was marked "deleted", the space was not released as long as a frmweb / forms session had it open by a user who was extracting a large portion of year-end data.

UPDATE in Sept 2022:

Starting in Oracle Database 18c, parameter UTL_FILE_DIR is no longer supported. Instead, specify the name of a directory object. 

Since the UTL_FILE_DIR initialization parameter is deprecated, it is no longer listed in V$SYSTEM_PARAMETER and related views:

SQL> SELECT value FROM v$system_parameter WHERE name='utl_file_dir' ;

no rows selected

SQL> SELECT value FROM v$parameter WHERE name='utl_file_dir' ;

VALUE

--------------------------------------------------------------------------------

/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

And, it can not be altered at DB level:

SQL> alter system set utl_file_dir='/path/to/utl_dir';

Log files from various programs in 11i

Log files are most important in helping troubleshooting. Various parograms in 11i put log files in different locations.

CONTENTS (See Metalink Note 130183.1)
--------
1. Internal manager
2. Concurrent managers
3. Concurrent programs
4. AD, FND and other utilities
5. User exits
6. Forms server
7. SQL*NET, NET8 listener
8. Apache, Jserv
9. SSP5 (iProcurement)
10. PO Document Approval Manager
11. XML Publisher (Output Post Processor)

1. INTERNAL MANAGER (also called ICM)
-------------------
a. when the ICM is started by adcmctl.sh, a log file is created in

$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG

File format is: $<SID>_mmdd.mgr
(or <mgrname>.mgr. mgrname is figured in startmgr, and is 'std' by default. startmgr is a shell script under $FND_TOP/bin/) (version depend?)

- To debug the ICM startup, set the value of DIAG_PARAM to Y in adcmctl.sh.

- The ICM log file on startup can be also located by SQL:

SELECT 'LOG=' fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'AND fcp.process_status_code = 'A';

b. directly from Oracle Applications :

System Administrator responsability

Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Internal Manager Log

2. CONCURRENT MANAGERS (See note 105133.1)
----------------------
a. retrieve Manager log files under :

$APPLCSF/$APPLLOG (if you have defined common directory) or
$FND_TOP/$APPLLOG

File format is:
w<Concurrent Process Id>.mgr (log from one of Concurrent managers)
c<Concurrent Process Id>.mgr (Conflict Resolution manager log)
t<Concurrent Process Id>.mgr (Transaction manager log)
s<Concurrent Process Id>.mgr
FNDxxxxx.mgr

If you see errors on cartridge in the logfile, below query will help to identify the queue:

SELECT q.concurrent_queue_id, q.concurrent_queue_name
FROM apps.fnd_concurrent_queues q, apps.fnd_cp_services s
WHERE enabled_flag = 'Y'
AND q.manager_type = s.service_id
AND s.cartridge_handle = 'CMDCART'; -- for example

b. directly from Oracle Applications :

System Administrator responsability
Navigator: Concurrent => Requests, choose a request ...
menu: Special => Manager Log
or
Navigator: Concurrent => Manager => Administer, button Processes (Concurrent Processes window) => button Manager Log

3. CONCURRENT PROGRAMS
----------------------
a. retrieve Concurrent Program log files under :

$APPLCSF/$APPLLOG (if $APPLCSF and $APPLLOG exist) or
$<module_top>/$APPLLOG

File format is: l<Concurrent Request Id>.req

- If the concurrent program generates a output file, it goes to $APPLCSF/$APPLOUT.

- SQL can be used to get the exact file names for Log file and Output file:

SELECT logfile_name, outfile_name, outfile_node_name, last_update_date
FROM apps.FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = &requestID;

- There are also log files for concurrent requests under $COMMON_TOP/admin/log/$SID_$hostname. (Will they be useful only when you have Forms auditing on?)

b. directly from Oracle Applications :

System Administrator responsability (for own and other users requests) :
Navigator: Concurrent => Requests, button View Log...

Note: User may not be able to view other's Output (and Log) files.

4. AD, FND AND OTHER UTILITIES
-------------------------------
Some AD utilities like adpatch, adadmin, etc. generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file names are :

adunload.log
adrelink.log
adlibout.log
adlibin.log
adfrmgen.log
admrgpch.log
adrepgen.log
adctrl.log
admvcode.log
adaimgr.log
adwork01.log, adwork02.log, adwork03.log, ...

Utility adodfcmp generates log file under $<module_top>/admin/odf/ or $<module_top>/patch/<version>/odf/, file name is:

adodfcmp.log

FND utilities like fdfcmp, FNDFFVGN, FNDMDGEN generate log file under $APPL_TOP/admin or $APPL_TOP/install/log, file name is :

L<request_id>.req

5. USER EXITS (See note 292058.1)
--------------
User exits from forms generate log file under directory from where the forms server is started if variable FDUDEBUG is set to ON. File name is:

userexit.log

6. FORMS SERVER (See note 471921.1)
----------------
When starting forms server you can specify a log file name.

Exemples:

Windows: f60srv32 log=c:\temp\f60srv.log
Unix: f60ctl start port=9000 log=/temp/f60srv.log
(f60ctl is a script located in $ORACLE_HOME/bin)

Note: $COMMON_TOP/admin/log/$SID_$hostname/f60svrm.txt has very basic info from executing adfrmctl.sh

7. SQL*NET, NET8 LISTENER
--------------------------
You can specify LOG_DIRECTORY_<LISTENER_NAME> and LOG_FILE_<LISTENER_NAME> specific values for theses parameters in listener.ora.

You can set LOG_FILE and TRACE_FILE variables from a lsnrctl session.

There are also LOG_DIRECTORY_SERVER and LOG_FILE_SERVER parameters in sqlnet.ora.

8. APACHE, JSERV
----------------
Logs from Apache server are under $APACHE_TOP/logs, file names are:
error_log
access_log
httpds_access_log
httpds_error_log
sqlnet.log

Logs from Jserv are under $ORACLE_HOME/Apache/Jserv/logs (?), file names are:
jserv.log
debug.log

Note: it is recommended to clean up these log files, shutdown and restart Apache server before trying to reproduce the issue and analyze.

$APACHE_TOP is not defined in any $APPL_TOP/*.env file ("grep APACHE_TOP $APPL_TOP/*.env" gets nothing).

9. SSP5 (iPROCUREMENT)
---------------------
Apache and Jserv logs are useful to check for iProcurement (SSP5), you can also add these lines in ssp_init.txt (should be under $ORACLE_HOME/Apache/Jserv/etc) to generate specific SSP5 log file:

DebugOutput=<Directory/File name> (preferably under $APACHE_TOP/logs)
DebugLevel=5
DebugSwitch=ON

Note: don't forget to restart Apache server after editing ssp_init.txt.

10. PO Document Approval Manager
------------------------------------
Responsibility - System Administrator responsibility (Nav - Concurrent Manager - Administer).
Locate and find the PO Document Approval Manager and then click on Processes button and then select an Active concurrent manager process and click on the Manager Log button to display the log file.

11. Output Post Processor
---------------------------
See Metalink note 364547.1

Table SYS.HISTGRM$ owned by SYS

SYS.HISTGRM$ holds statistics for tables. When we used csscan to converte a database to Unicode UTF-8 character set, it reported that SYS.HISTGRM$ had one row of Convertible data. We got "ORA-701 object necessary for warm starting database cannot be altered" from issuing command "truncate table sys.histgrm$".

We did followings to work it out:

1. Find the rowid from the csscan report on the record in SYS.HISTGRM$ that is Convertible.

2. Identify the object in SYS.HISTGRM$ and in the csscan report: SQL> select a.owner, a.object_name, a.object_type
from all_objects a, sys.histgrm$ b
where a.object_id = b.obj# and b.rowid='AAAAD7AABAAANS+ABL'
group by a.owner, a.object_name, a.object_type;
OWNER OBJECT_NAME OBJECT_TYPE
----- ----------- -----------
DSS BIN$P3VMZ66Y4JTgQwoBAoDglA==$0 TABLE
-- It shows that object is in recycle bin.

3. Purge the recyclebin as SYSDBA:
SQL> PURGE TABLE "BIN$P3VMZ66Y4JTgQwoBAoDglA==$0" ;
SQL> purge DBA_RECYCLEBIN;
-- Now, "select * from DBA_RECYCLEBIN;" should got zero rows.
-- Note: If it is a regular table, run a package to delete the satats:
-- exec DBMS_STATS.DELETE_TABLE_STATS ('OWNER','TABLE_NAME');

After those steps, SYS.HISTGRM$ did not show up in the csscan report.

Notes:
Step 1 and Step 2 can be replaced by running script @?/nls/csscan/sql/analyze_histgrm.sql (which uses csmv$errors).
Step 3 is to delete the stats on involved tables in order to have HISTGRM$ contain just the clob records. These clob records can be altered with the csalter.plb script.

UNDO tablespace -- useful SQL

Here are useful queries when the database hits with ORA-1555 or ORA-30036 error in UNDO tablespace.

1. UNDO Tablespace sizing information:
---------------------------------------------------------------
Undo Tablespace : APPS_UNDOTS1
Undo Retention_ : .25 Hrs (900)
Max Qry Length_ : 8.72 Hrs (31403)
Max Undo Used__ : 116.97 MB (??)
Undo Size Reqd_ : 54 MB
Undo TabSp Size : 4380 MB
UNEXPIRED Blks_ : 2352 (18 MB)
EXPIRED Blks___ : 11952 (93 MB)
ACTIVE Blks____ : 128 (1 MB)
UNDO Advisory : Enough undo space available in APPS_UNDOTS1 - 4,361 MB

Qureies to get above info:

SQL> show parameter undo
NAME TYPE VALUE
-------------------------- -------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string APPS_UNDOTS1

---- SQL to check if Expired extents exist
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
From note (460481.1): In case no undo space is left, then we try to use unexpired extents (Undo Extent required to honour UNDO_RETENTION). This sometimes results in ORA-1555 errors. Now if you do not have unexpired extents also, then you need to add space to undo tablespace.
If there are no expired extents and we see only Unexpired extents and Active extents then this is most likely Undo sizing issue. In this case, check if Undo Tablespace is correctly sized. Else try reducing value for UNDO_RETENTION.

---- Number of MBytes needed
SELECT ((UR * (UPS * DBS)) + (DBS * 24)) / (1024*1024) AS "MBytes"
FROM
(SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), -- 86400 = seconds in a day
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
MBYTES
------
54

-- If it's not autoextensible, it may hit
-- "ORA-30036 - Unable To Extend Undo Tablespace". To change it:
-- "ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND ON;"
SQL> select file_name, autoextensible, maxbytes/1024 max_KB, bytes/1024 used_KB
from dba_data_files where tablespace_name='APPS_UNDOTS1';

FILE_NAME AUTOEXTENSIBLE MAX_KB USED_KB
--------- -------------- ---------- ----------
/pjyti/oradata/data01/undo01.dbf YES 1843200 2048000
/pjyti/oradata/data01/undo02.dbf YES 1843200 1269760
/pjyti/oradata/data01/undo03.dbf YES 1843200 1167360

---- Total size
SQL> select sum(bytes)/1024 Total_KB from dba_data_files
where tablespace_name='APPS_UNDOTS1';
TOTAL_KB
----------
4487168

---- Free space
SQL> select sum(bytes)/1024 Free_KB from dba_free_space
where tablespace_name='APPS_UNDOTS1';
FREE_KB
----------
4465664

---- Max query length
SQL> select inst_id, max(maxquerylen)
from gv$undostat group by inst_id;
INST_ID MAX(MAXQUERYLEN)
------- ----------------
1 31403

2. Troubleshooting
-----------------------------------------------------------

---- Identify the instance where the error occurs (in RAC):
SQL> SELECT INSTANCE_NUM,TABLESPACE_NAME,SEGMENT_NAME,STATUS
FROM DBA_ROLLBACK_SEGS;
or
SQL> select stat.inst_id, seg.segment_name, seg.tablespace_name
from dba_rollback_segs seg, gv$rollstat stat
where seg.segment_id = stat.usn
and seg.segment_name in ('_SYSSMU7$', '_SYSSMU14$');

INST_ID SEGMENT_NAME TABLESPACE_NAME
------- ------------ ---------------
1 _SYSSMU7$ APPS_UNDOTS1
2 _SYSSMU14$ APPS_UNDOTS1

---- List of active transactions
SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE' ;

---- The UNDO tablespace percent of space in-use now (420525.1)
select
((select (nvl(sum(bytes),0))
from dba_undo_extents
where tablespace_name='APPS_UNDOTS1'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes)
from dba_data_files
where tablespace_name='APPS_UNDOTS1')
"PCT_INUSE"
from dual;

---- Number of errors during the time period
SQL> select inst_id,
to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT UNEXPIRED_STOLEN, EXPSTEALCNT EXPIRED_STOLEN,
SSOLDERRCNT ORA_1555, NOSPACEERRCNT OUT_OF_SPACE, MAXQUERYLEN
from gv$undostat -- v$undostat does not have inst_id
where begin_time between to_date('04/02/2008 23:30:00','MM/DD/YYYY HH24:MI:SS')
and to_date('04/03/2008 07:00:00','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;
INST_ID BEGIN_TIME UNEXPIRED_STOLEN EXPIRED_STOLEN ORA_1555 OUT_OF_SPACE MAXQUERYLEN
------- ---------- ------------ ----------- ----------- ------------- -----------
1 04/03/2008 05:47 0 0 0 0 1832
1 04/03/2008 05:57 0 0 0 0 2205
1 04/03/2008 06:07 0 0 0 0 478
1 04/03/2008 06:17 0 0 0 0 1293
1 04/03/2008 06:27 0 0 0 0 627
1 04/03/2008 06:37 0 0 1 0 1228
1 04/03/2008 06:47 0 0 0 0 3506
1 04/03/2008 06:57 0 0 0 0 192

. When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.
. If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.
. If the column NOSPACEERRCNT is non-zero, then there is a serious space problem

Currently there are sufficient expired and unexpired space is available for active transactions. No more ORA-1555 errors reported in the alert log file.
After analyzing the session that hits the ORA-1555 error, we see the error has occured for SELECT statment after running hours which is more than undo_retention time.
Conclusion: user needs to tune the select query.

3. Add datafile if it is really needed
-----------------------------------------------------------
ALTER tablespace APPS_UNDOTS1
ADD datafile '/pjyti/oradata/data01/undo04.dbf'
SIZE 200M AUTOEXTEND ON NEXT 20M MAXSIZE 1800M;