SQL to show 11i Profile Options

After some Profile options were turned on Site, Application, Responsibility, or User level, you might forget to turn them off which may lead to application behavior variation by user or responsibility. Metalink Doc ID: 146705.1 provides a SQL script to display all saved values for the specified profile options. Here is what it looks like on running it:

SQL> @a_profile_opt_R11i.sql

Enter value for profile_like: Utili%Diag%

Creation Date:        27-DEC-99        Created By:    AUTOINSTALL
Date Active From:   01-JAN-80        To:
Profile Option Name:   DIAGNOSTICS
User Profile Name:      Utilities:Diagnostics
Profile Description:
Value determines whether diagnostic utilities, such as Examine, may be used
Level Value              Profile Value
------- --------------  ----------
Site    SITE             N
User   JJOHN         Y

Code from the Oracle document:

Program
-------
 - - - - - - - - - - - Code begins here - - - - -- - - - - - - - -
rem     
rem     File:      a_profile_opt_R11i.sql
rem     Created:   Vitaliy Mogilevskiy (vit100gain@earthlink.net)
rem     Modified:  Eric Santos, Run in 11i and NLS 
rem     Desc:      Reports Profile Options For Oracle Applications
rem                with NOT NULL values, groups by Profile Option Name
rem                breaks by SITE, RESPONSIBILITY, APPLICATION, USER
rem                Takes Parameter Name as search string
rem                Allows you to view ALL possible values for profile
rem                including SITE, RESPONSIBILITY, APPLICATION and USER.
rem                This is impossible in Oracle Apps GUI mode
rem


clear col
clear breaks

set pages 9000
set lines 132
set verify off

col pov    format a45 word_wrapped  heading "Profile Value"
col lo     format a5                heading "Level"
col lov    format a40               heading "Value"
col pon    noprint    new_value n_pon
col upon   noprint    new_value n_upon
col sda    noprint    new_value n_sda
col eda    noprint    new_value n_eda
col cd     noprint    new_value n_cd
col cb     noprint    new_value n_cb
col d      format a78 word_wrapped noprint    new_value n_d

break on pon skip page

ttitle -
       "Creation Date:   "      n_cd    "    Created By: "        n_cb   -
       skip 1 -
       "Date Active From:"      n_sda   "    To:"     n_eda  -
       skip 1 -
       "Profile Option Name: "  n_pon   -
       skip 1 -
       "User Profile Name:   "  n_upon  -
       skip 1 -
       "Profile Description: "          -
       skip 1 -
       n_d                              -

select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Site'                                   lo
,             'SITE'                                   lov
,             fpov.profile_option_value                pov
from       FND_PROFILE_OPTIONS_TL      fpot
,          FND_PROFILE_OPTIONS         fpo
,          FND_PROFILE_OPTION_VALUES   fpov
,          FND_USER                    fu
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.application_id       = fpov.application_id
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpot.language            = Userenv('Lang')
and      fpov.level_id            = 10001         /* Site Level */
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Apps'                                   lo
,             fa.application_name                      lov
,             fpov.profile_option_value                pov
from      FND_PROFILE_OPTIONS_TL      fpot
,         FND_PROFILE_OPTIONS         fpo
,         FND_PROFILE_OPTION_VALUES   fpov
,         FND_USER                    fu
,         FND_APPLICATION_TL          fa
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpot.language            = Userenv('Lang')
and      fpov.level_id            = 10002      /* Application Level */
and      fpov.level_value         = fa.application_id
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Resp'                                   lo
,             frt.responsibility_name                   lov
,             fpov.profile_option_value                pov
from     FND_PROFILE_OPTIONS_TL      fpot
,        FND_PROFILE_OPTIONS         fpo
,        FND_PROFILE_OPTION_VALUES   fpov
,        FND_USER                    fu
,        FND_RESPONSIBILITY_TL          frt
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name        = fpo.profile_option_name
and      fpo.profile_option_id           = fpov.profile_option_id
and      fpo.created_by                  = fu.user_id
and      frt.language                    = Userenv('Lang')
and      fpot.language                   = Userenv('Lang')
and      fpov.level_id                   = 10003  /* Responsibility Level */
and      fpov.level_value                = frt.responsibility_id
and      fpov.level_value_application_id = frt.application_id
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'User'                                   lo
,             fu2.user_name                            lov
,             fpov.profile_option_value                pov
from     FND_PROFILE_OPTIONS_TL      fpot
,        FND_PROFILE_OPTIONS         fpo
,        FND_PROFILE_OPTION_VALUES   fpov
,        FND_USER                    fu
,        FND_USER                    fu2
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpov.level_id            = 10004      /* User Level */
and      fpov.level_value         = fu2.user_id
and      fpot.language            = Userenv('Lang')
order by upon, lo, lov
/

undefine profile_like

ttitle off

 - - - - - - - - - - - -  Code ends here  - - - -  - - - - - - - -

How to Get a Debug Log in 11i

A debug log is different from a trace file. Usually, a trace file is saved on the database server (see Metalink Note 141278.1 for 11i tracing).

You have to make a change in Profile to have debug information written into the Log file of a concurrent program. For example, below action will get the debug log for "PRC: Transaction Inventory":

System Adminstrator / Application Administration --> Profile
Set profile option "PA: Debug Mode" to "Yes"
for user USER_NAME (who will run the concurrent program)

For HTML web apps, it is not easy to get debug information or trace file. One way you can try is to turn on the trace on SQL. I tried that on BOM Configurator and got the trace when I turned on the trace only on Site level which may overload the system. Following action will turn on SQL trace on a user level:

Set profile option "Utilities: SQL Trace" to "Yes"
for user USER_NAME

Steps to read the trace file:
1. Retrieve the trace file, usually identified by timestamp.
2. Issue a command like the following to create a TKPROF version of the trace file:
tkprof filename.trc output_filename.txt sys=no explain=apps/password

OS Process ID for Troubled Concurrent Manager

From time to time, some concurrent manger hangs and it can not be fixed by using adcmctl.sh to re-start ICM. The reason is that the OS process for the concurrent manager may be "dead" and you have to kill it at OS level before the concurrent manager can be re-started.

Steps for finding concurrent manager, such as Output Post Processor, stuck in 'Initializing' or other phase:

. Log on to EBS Forms or to Oracle Application Manager.
. Find the concurrent manager in question.
. Click on "Process" button or Drill down to find the processes shown as active or initializing.
. If the operating system process ID shown is running on the concurrent processing node, kill that process.
. Activate the concurrent manager.
. Verify that the concurrent manager comes up and the number of active managers is equal to the target number.

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