Monitor Discoverer Processes (dis51ws) Not Terminated by Timeout

If user does not use "file > exit" or "file > close" to gracefully close the workbook or Discoverer, an orphaned session may be created. Some times, user may get error while trying to connect to Discoverer Viewer and Plus:

A connection error has occurred.
- Attempt 1. CORBA proto : Hard limit on number of sessions reached. Please contact your administrator or retry after some time.
Hint: An administrator can further diagnose connection problems by running the "checkdiscoverer" script under <ORACLE_HOME>/discoverer/util.

Here is a useful korn shell script:

$ cat script.ksh
# script to display dis51ws processes over 5 hours old.
# you need to be in the korn shell to run this.

echo "\nDD:HH:MM:SS PID Process"
echo "------------------------"
for i in `ps -A -o etime,pid,args grep dis51ws sed 's/ /_/g'`
do
myday=`echo $i awk '{ fred2=substr($1,1,2);print fred2}'`
myhour=`echo $i awk '{ fred2=substr($1,4,2);print fred2}'`
# the above line extracts the hour .

if test $myday = "__*"
then
if test $myhour != "__*"
then
if test $myhour -gt 5
# the last number here is the hour limit -
# you can customize this from 0 to 23.
then
process=`echo $i sed 's/_/ /g'`
echo $process
fi
fi
else # if any day at all is listed, then the proc must be over 5 hours.
process=`echo $i sed 's/_/ /g'`
echo $process
fi
done
echo "\n"

$ for i in ` ksh script.ksh awk '{ print $2}'`
> do
> kill -9 $i
> done


Note that Discoverer uses $ORACLE_HOME/discoverer/util/perf.txt to set up the timeout variable: Timeout = 1800 seconds. $ORACLE_HOME/opmn/conf/opmn.xml also has entries for time: <start timeout="600" /> <stop timeout="120" /> (not sure what it's for).

Profile Options Could Stop Concurrent Jobs

In production instance, MRP Workbench does not have data. The concurrent job "Purchasing MRP (Report)" failed with error in the log:
Request restarted at :01-MAY-2008 01:05:57
**Starts**01-MAY-2008 01:05:57
**Ends**01-MAY-2008 01:05:57
The set completed normally with outcome Error. The outcome was determined by the final stage, Load/Copy/Merge MDS (10).

The log on program "Load/Copy/Merge MDS" says that
APP-MRP-22132: Cannot get profile option MRP_DEBUG
Cause: The current routine cannot retrieve a value for the specified profile option.

Below query confirms that two profile options are blank because of human error, as it returns 0 row:

SELECT fpo.profile_option_name Profile,
fpov.profile_option_value Value,
decode(fpov.level_id,10001, 'SITE',10002, 'APPLICATION',10003, 'RESPONSIBILITY', 10004,'USER') "LEVEL",
fa.application_short_name App,
fr.responsibility_name Responsibility,
fu.user_name "USER"
FROM fnd_profile_option_values fpov,
fnd_profile_options fpo,
fnd_application fa,
apps.fnd_responsibility_vl fr,
fnd_user fu, fnd_logins fl
WHERE fpo.profile_option_id=fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.application_id(+)=fpov.level_value_application_id
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value
and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN
and fpo.profile_option_name in ('MRP_TRACE','MRP_DEBUG')
order by 1,3

After setting profile options "MRP:Debug" and "MRP:Trace" to 'No' at Site level, the MRP request set runs successfully.

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.