Discoverer 10g (10.1.2.48.18 in my server) provides a script to collect all logs for troubleshooting. The script called collectlogs.sh is located in $ORACLE_HOME/discoverer/util. (Note $ORACLE_HOME could be null in the Discoverer installation acconut).
I ran it by below line. It created two files: the .tar file, and loglist.txt file for listing all log files collected in the tar file.
$ pwd
/sjy/disco/product/disco_1012/discoverer/util
$ ./collectlogs.sh disco_log.tar > loglist.txt
Initializing ORB...
ORB initialized successfully.
Checking for Discoverer "ServicesStatus"...
"ServicesStatus" is running.
Checking the adminstrator set soft limit on no. of sessions ...
Current value of soft limit = 50 sessions.
Hint: Administrators can increase the soft limit on Discoverer sessions by modifying
"//ias-component[id='Discoverer']/process-type[id='SessionServer']/process-set/@maxprocs" attribute in the file /sjy/disco/product/disco_1012/opmn/conf/opmn.xml
Checking for Discoverer Preferences component...
Found Discoverer Preferences component.
Binding to Discoverer Server...
Successfully bound to Discoverer Server.
Checking that the machine has only one network card...
Yes.
cp: cannot stat `/spulti/disco/product/disco_1012/discoverer/util/diag.log': No such file or directory
cp: cannot stat `/spulti/disco/product/disco_1012/network/admin/sqlnet.ora': No such file or directory
cp: omitting directory `/spulti/disco/product/disco_1012/opmn/logs/states'
For troubleshooting purpose, three commands are useful to check the status of Discoverer processes and environment:
$ $ORACLE_HOME/dcm/bin/dcmctl getstate -v -d
Current State for Instance:sjy_disco.auohsjy02.com
Component Type Up Status In Sync Status
==============================================
1 home OC4J Up True
2 HTTP_Server HTTP_Server Up True
3 OC4J_BI_Forms OC4J Up True
$ opmnctl status
Processes in Instance: sjy_disco.auohsjy02.com
-------------------+--------------------+---------+---------
ias-component process-type pid status
-------------------+--------------------+---------+---------
DSA DSA N/A Down
LogLoader logloaderd N/A Down
dcm-daemon dcm-daemon 11231 Alive
WebCache WebCache 9828 Alive
WebCache WebCacheAdmin 9814 Alive
OC4J home 9815 Alive
OC4J OC4J_BI_Forms 9816 Alive
HTTP_Server HTTP_Server 9817 Alive
Discoverer ServicesStatus 9818 Alive
Discoverer PreferenceServer 9819 Alive
$ emctl status iasconsole
TZ set to US/Central
Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://auohsjy02.com:10923/emd/console/aboutApplication
Oracle Enterprise Manager 10g Application Server Control is running.
Friday, May 16, 2008
Wednesday, May 14, 2008
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).
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).
Friday, May 2, 2008
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.
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.
Wednesday, April 30, 2008
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:
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
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
Subscribe to:
Comments (Atom)