OracleAppsDBAKK1: General Troubleshooting and Debugging and Analysing Topics For EBS (Doc ID 970213.1)

Applies to:

Oracle Order Management - Version 11.5.10.0 and later
Information in this document applies to any platform.
***checked for relevance 13-May-2013***

Main Content

Purpose

This bulletin will provide diagnostics steps for analyzing a issue

Troubleshooting Steps

Performance Issues

1. Scripts that will be helpful to diagnose a hanging request

Sql : 1 - This SQL gives the current status of the request from fnd_concurreent_requests

SELECT r.request_id,
fcptl.user_concurrent_program_name,
ph.meaning req_phase,
st.meaning req_status,
to_char(r.request_date,'DD-MON-YYYY HH24:MI:SS') request_date,
to_char(r.actual_start_date,'DD-MON-YYYY HH24:MI:SS') actual_start_date,
to_char(r.actual_completion_date,'DD-MON-YYYY HH24:MI:SS') actual_completion_date,
r.oracle_process_id
FROM fnd_concurrent_requests r,
fnd_concurrent_programs_tl fcptl,
fnd_lookups ph,
fnd_lookups st
WHERE r.request_id IN (&request_id1, &request_id2, ...)
AND r.concurrent_program_id = fcptl.concurrent_program_id
AND fcptl.language = 'US'
AND ph.LOOKUP_CODE = r.phase_code
AND ph.LOOKUP_TYPE = 'CP_PHASE_CODE'
AND st.LOOKUP_CODE = r.status_code
AND st.LOOKUP_TYPE = 'CP_STATUS_CODE';

Sql : 2 - This SQL gives the current sql statement that the request is processing

SELECT r.request_id,
sq.sql_text,
ss.SID,
ss.SERIAL#
FROM fnd_concurrent_requests r,
v$process p,
v$session ss,
V$SQLAREA sq
WHERE r.request_id IN (&request_id1, &request_id2, ...)
AND p.spid = r.oracle_process_id
AND ss.paddr = p.addr
AND sq.ADDRESS = ss.SQL_ADDRESS;

Sql : 3 - This SQL gives the wait events

SELECT r.request_id,
w.event,
w.wait_time,
w.seconds_in_wait,
w.state
FROM fnd_concurrent_requests r,
v$process p,
v$session ss,
V$SESSION_WAIT w
WHERE r.request_id IN (&request_id1, &request_id2, ...)
AND p.spid = r.oracle_process_id
AND ss.paddr = p.addr
AND w.SID = ss.SID;

Sql : 4 - This SQL gives the details whether the hanging is happening because of a lock in the object

SELECT SESSION_ID,
MODE_HELD,
BLOCKING_OTHERS
FROM DBA_DML_LOCKS
WHERE name = '&object_name';

Note : The object name that is to be used here is the table that the request currently accessing which can be obtained from sql : 2

2. Tracing

i.Normal concurrent leve1 trace

Navigation :
System Adminstrator > Concurrent > Program > Define
Check the field : Enable Trace
Enabling this check box will enable the trace for the concurrent request.To get the trace file perform the following action plan

SELECT value
FROM v$parameter
WHERE name = 'user_dump_dest';

The output will be something like
/global/oracle/vismfg02/db/tech_st/10.2.0/admin/vismfg02_celalnx20/udump

The output from the above SQL is the directory in which the trace file will be stored.Now to get the correct trace file get the process_id value from the fnd_concurrent_requests for the corresponding request_id.The trace file would have generated in the same name as process_id

ii.Tracing using the profile 'Initialization SQL Statement - Custom'

1. Just before launching the the program or performing any activity in the application set the profile 'Initialization SQL Statement - Custom' to the following value at user level.
ALTER SESSION SET TRACEFILE_IDENTIFIER='SR{SR number here}' max_dump_file_size=unlimited EVENTS='10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'

2. Launch the concurrent request or perform the activity

3. Once the program is over set the profile back to NULL (or the original value, if there was any).

4. A trace file would have been generated which will have the word 'SR{SR number here}' in its name. Identify the trace and file name using the below sql

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id;

5. Please run tkprof on this trace file and provide the output as well. While running tkprof make sure that the sort option used is 'exeela,fchela,prsela'

iii.Tracing from SQLPlus

1. alter session set statistics_level=all;
2. alter session set tracefile_identifier='ORGPERF';
3. alter session set events '10046 trace name context forever, level 12';
4. Execute the problematic sql
5. exec dbms_session.reset_package;
6. exit
7. Provide all raw trace files generated in user_dump_dest with the string
'ORGPERF' as part of the trace file name.

Note : For both 2 and 3rd methods instead of level 12,the trace can be set as level 4 and level 8 also depending upon the requirement.

Generic SQL

i.SQL To identify the patch that has been applied to get the file version

The following SQL gives the complete history about a particular file.From that we can identify what are the patches that has been applied for a specific file.

SELECT AT.NAME appl_top_name
, DECODE (f.app_short_name
, 'DUMMY', NULL
, 'SQLAP', 'AP'
, 'SQLGL', 'GL'
, 'OFA', 'FA'
, f.app_short_name) product
, DECODE (f.subdir, 'DUMMY', NULL, f.subdir) DIRECTORY
, f.filename
, fv.VERSION
|| DECODE (fv.translation_level
, 0, NULL
, ':' || TO_CHAR (fv.translation_level) ) VERSION
, TO_CHAR (pr.end_date, 'dd-MM-yyyy hh24:mi:ss') date_applied
, ap.patch_name patch_id, ap.applied_patch_id, pr.end_date
, version_segment1, version_segment2, version_segment3
, version_segment4, version_segment5, version_segment6
, version_segment7, version_segment8, version_segment9
, version_segment10, translation_level, pr.patch_run_id
, pr.patch_top, pr.patch_action_options
, TO_CHAR (pr.start_date, 'dd-MM-yyyy hh24:mi:ss')
, pr.program_run_id, pr.session_id, pd.patch_driver_id
, pd.driver_file_name, pd.platform
FROM ad_appl_tops AT
, ad_applied_patches ap
, ad_patch_drivers pd
, ad_patch_runs pr
, ad_patch_run_bugs prb
, ad_file_versions fv
, ad_patch_run_bug_actions prba
, ad_files f
WHERE f.file_id = prba.file_id
AND prba.executed_flag = 'Y'
AND prba.patch_run_bug_id = prb.patch_run_bug_id
AND pr.appl_top_id = AT.appl_top_id
AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id
AND pd.applied_patch_id = ap.applied_patch_id
AND prba.patch_file_version_id = fv.file_version_id
AND upper(AT.applications_system_name) = '&SID'
AND upper(f.filename) in ('&file_name_in_caps')
GROUP BY f.app_short_name
, f.subdir
, f.filename
, AT.NAME
, fv.VERSION
, fv.version_segment1
, fv.version_segment2
, fv.version_segment3
, fv.version_segment4
, fv.version_segment5
, fv.version_segment6
, fv.version_segment7
, fv.version_segment8
, fv.version_segment9
, fv.version_segment10
, fv.translation_level
, ap.patch_name
, pr.end_date
, ap.applied_patch_id
, pr.patch_run_id
, pr.patch_top
, pr.patch_action_options
, pr.start_date
, pr.program_run_id
, pr.session_id
, pd.patch_driver_id
, pd.driver_file_name
, pd.platform
ORDER BY f.app_short_name
, f.subdir
, AT.NAME
, version_segment1 DESC
, version_segment2 DESC
, version_segment3 DESC
, version_segment4 DESC
, version_segment5 DESC
, version_segment6 DESC
, version_segment7 DESC
, version_segment8 DESC
, version_segment9 DESC
, version_segment10 DESC
, translation_level DESC
, pr.end_date DESC ;

ii.SQL to find out the completion status and the time of run for concurrent requests

This SQL gives the entire history about a concurrent request.If customer claims that the performance has suddenly degraded then we can use this SQL to identify the run time of all the runs of this specific request.So that we will come to has the run time has increased gradually or all of a sudden.

SELECT f.user_concurrent_program_name,
r.actual_start_date,
r.actual_completion_date,
(r.actual_completion_date - r.actual_start_date) * 24 * 60 * 60
actual_time_sec,
ph.meaning req_phase,
st.meaning req_status
FROM apps.fnd_concurrent_requests r,
apps.fnd_concurrent_programs_tl f,
apps.fnd_lookups ph,
apps.fnd_lookups st
WHERE f.user_concurrent_program_name ='&concurrent_request_name'
AND r.concurrent_program_id = f.concurrent_program_id
AND ph.lookup_code = r.phase_code
AND ph.lookup_type = 'CP_PHASE_CODE'
AND st.lookup_code = r.status_code
AND st.lookup_type = 'CP_STATUS_CODE' ;

iii.SQL to find the body and spec version from the database:

In some cases the database version of a file and the $PROD_TOP version may not match or in few cases the package body might not have got compiled or an application process may be throwing some error specifying a particular line from the package.In that case the following sql can be used

To identify the package and specification version

SELECT text
FROM all_source
WHERE name in UPPER('package name') AND line < 5;

To identify particular line of a package

SELECT text
FROM all_source
WHERE name in UPPER('package name')
AND line BETWEEN < line1 > AND < line2 >;

iv.SQL to find the database version:

SELECT comp_name,version,status
FROM dba_registry;

v.SQL to identify the columns of an index:

SELECT dic.index_owner, dic.index_name, dic.column_name,di.last_analyzed
FROM dba_ind_columns dic,dba_indexes di
WHERE di.table_name = '&table_name'
AND di.table_name = dic.table_name
AND di.index_name = dic.index_name ;

vi.SQL to identify when the statistics have been gathered on a table:

SELECT last_analyzed
FROM dba_tables
WHERE table_name = ‘&table_name' ;

vii.SQL to gather statistics from sqlplus:

EXEC fnd_stats.gather_table_stats('&table_name');

viii.SQL to identify if the patch is applied

SELECT *
FROM ad_bugs
WHERE bug_number = ‘&bug_number’ ;

or

SELECT *
FROM ad_applied_patches
WHERE patch_name = '&patch_name' ;

ix.SQL to identify the application version

SELECT release_name
FROM fnd_product_groups ;

x.SQL to get profile option value from backend

SELECT profile_option_id,level_id,profile_option_value
FROM fnd_profile_option_values
WHERE profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name =
(SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE user_profile_option_name = '&user_profile_option_name')) ;

Example :

SELECT profile_option_id,level_id,profile_option_value
FROM fnd_profile_option_values
WHERE profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name =
(SELECT profile_option_name
FROM fnd_profile_options_tl
WHERE user_profile_option_name = 'MSO: Lead Time Control')) ;

PROFILE_OPTION_ID	LEVEL_ID	PROFILE_OPTION_VALUE
1005365	10001	VIOLATE_MIN_PROC_TIMES

xi.SQL to refresh snapshots from backend

SELECT mrp_ap_refresh_s.nextval
FROM dual;

EXEC dbms_snapshot.refresh('&schemaname.snapshotname','&mode');

Generic UNIX Commands

i.Command to get the version of the file inside an executable

strings -a <EXECUTABLE_NAME> | grep -i '<FILE_NAME>' | grep '$Header'

Example:
strings -a $MSO_TOP/bin/MSONWS64.exe | grep -i ‘msnosbpf.lcc’ | grep ‘$Header’

$Header: msnosbpf.lcc 120.78.12000000.5 2007/10/03 23:48:36 emalea ship $

ii.Command to get all the file versions inside an executable

strings -a <exe name> | grep -i '$Header' > <file name>

Example:
strings -a $MSO_TOP/bin/MSONWS64.exe | grep -i '$Header' > versions

iii.Command to relink a schema using adrelink

h adrelink.sh force=y ranlib=y "<schema_name>"

Example:
sh adrelink.sh force=y ranlib=y "MSC"

iv.Command to relink an executable using adrelink

sh adrelink.sh force=y ranlib=y "<schema_name> <executable_name>"

Example:
sh adrelink.sh force=y ranlib=y "MSC MSCNSP"

v.Command to get Ulimit settings

$ ulimit -aS : This gives the soft limit values
$ ulimit -aH : This gives the hard limit values

vi.Command to get check the difference in an object between an ODF and database.

adodfcmp odffile= <file_name> userid=apps/apps mode=views
logfile=/tmp/adodfcmp.log touser=apps/apps priv_schema=apps/apps changedb=n

Example:

adodfcmp odffile=$MSD_TOP/patch/115/odf/msdsr.odf userid=apps/apps mode=views
logfile=/tmp/adodfcmp.log touser=apps/apps priv_schema=apps/apps changedb=n

Note : Mode can be either table or view depending upon what we want to check.The log file <adodfcmp.log> that gets created list out the differences in the view definition between the database and the ODF file.The parameter changedb indicates whether we want to get the database in sync with the ODF.The value 'n' just list out the differences and do not correct the changes.If we want to correct the changes then we have to set the value as y (changedb = y).

Other Useful Notes For Diagnosing

Document 117129.1 : How to get a Trace for And Begin to Analyze a Performance ,
Document 419728.1 : How To Gather Statistics On Oracle Applications 11.5.10(and above) - Concurrent Process,Temp Tables, Manually ,
Document 130686.1 : How to Generate Form, Library and Menu for Oracle ,
Document 164661.1 : ORA-60 / Deadlocks Most Common Causes ,
Document 215187.1 : SQLTXPLAIN.SQL - Enhanced Explain Plan and related ,
Document 77483.1 : External Support FTP site: Information Sheet ,
Document 132629.1 : How to know which row is locked by what user.,
Document 245974.1 : FAQ - How to Use Debug Tools and Scripts for the APS Suite ,
Document 134007.1 : Concurrent Processing - Concurrent Manager Recovery Troubleshooting Wizard

References

NOTE:245974.1 - FAQ - How to Use Debug Tools and Scripts for the VCP (aka APS) and EBS Applications
NOTE:419728.1 - Concurrent Processing - How To Gather Statistics On Oracle Applications Release 11i and/or Release 12 - Concurrent Process,Temp Tables, Manually
NOTE:117129.1 - How to get a Trace first And Begin to Analyze a E-Business Applications Performance Issue
NOTE:130686.1 - E-Business Suite Technology Stack Steps To Generate Forms, Libraries, And Menus for Oracle Applications 11i and R12
NOTE:132629.1 - How to Determine Which Row is Locked by What User Using a PL/SQL Procedure and a Pro*C Program
NOTE:215187.1 - All About the SQLT Diagnostic Tool
NOTE:77483.1 - External Support FTP site: Information Sheet
NOTE:164661.1 - How To deal with INITTRANS/MAXTRANS ORA-00060 / Deadlocks

OracleAppsDBAKK1

Wednesday, November 6, 2019

General Troubleshooting and Debugging and Analysing Topics For EBS (Doc ID 970213.1)