Enable Trace for long running Concurrent Request in Apps 11i/R12

This post covers overview of How to troubleshoot long running concurrent request in  Oracle Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 1145)

Step 2 : Run below command to find SPID, provide concurrent request ID (1145 in my case) when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;

REQUEST_ID        SID    SERIAL# OSUSER           PROCESS    SPID
—————----------------------------------------------------------------------------
   1145                  514            28         applmgr             17794        2987.
.

Step 3.1 : Check and confirm SPID on Database Node
$   ps-ef | grep 2987
  proddb   2987     1  0 13:30:43 ?        0:03 oracledbr12 (LOCAL=NO)

Step 3.2 : Set OSPID (2987 in my case) for ORADEBUG
SQL> oradebug setospid 2987

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name

/oracle/apps/proddb/10.2.0/admin/prod_CSDoracle/udump/ prod _ora_2987.trc

Wait for 15-20 minutes

Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like
$ tkprof prod _ora_2987.trc prod _ora_2987.txt explain=apps/<****>  sort=(exeela,fchela) sys=no

Step 6 : Check TKPROF file to find root cause of slow concurrent requet

How to enable trace in Oracle DB level

1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background processes

sql_trace = TRUE

to disable trace:

sql_trace = FALSE

- or -

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

to stop trace run:

SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;

2. Enable trace at session level

to start trace:

ALTER SESSION SET sql_trace = TRUE;

to stop trace:

ALTER SESSION SET sql_trace = FALSE;

- or -

EXECUTE dbms_session.set_sql_trace (TRUE);

EXECUTE dbms_session.set_sql_trace (FALSE);

- or -

EXECUTE dbms_support.start_trace;

EXECUTE dbms_support.stop_trace;

3. Enable trace in another session

Find out SID and SERIAL# from v$session. For example:

SELECT * FROM v$session WHERE osuser = OSUSER;

to start trace:

EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop trace:

EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

- or -

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);

- or -

-- Oracle 10.1

EXECUTE DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>TRUE);

EXECUTE DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>FALSE);

ORA-01555: Snapshot too old : rollback segment number XXX with name NNN too small.

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

 

select rb.segment_name, rs.optsize, rs.hwmsize
from v$rollstat rs, dba_rollback_segs rb where rs.usn = rb.segment_id;

alter rollback segment rbs0 storage (optimal 100m);

alter system set undo_retention=10800; 

Regularly used some DBA/AppsDBA related command and scripts

Hi, After a long time I am getting time to post something new for  DBA/AppsDBA guys. You can't get this types of things in any blogs..... These things are very useful to all DBA's.....These commands will help him to work on the server without any hesitation. Please review and if you like post comment, so that I can post a lot things for you guys.
 

 == > How do you identify the Hostname ?

$Hostname –a: gives all the information about your operating system i.e Operating system name, kernel release, kernel version, kernel name.

$Hostname –v : displays kernel version

$Hostname –s : displays kernel name

$Hostname –n : displays node name

$Hostname –r : displays kernel release

== >  What is the apps Domain Name ?

$dnsdomainname gives the domain name of applications

$hostname –d : gives the apps domain name

$hostname –i : gives the ip address of that machine.

$hostname –f : gives the hostname and domain name of the machine

== > Check whether middle tier components are running on this node or not ?



$ps –ux |grep –i iAS or http (for apache server) 
$ps –ux | grep –i f60srvm or f60webmx or f60( for forms server 
$ps –ux |grep –i rep60 or rwmts (for reports server) 
$ps –ux |grep –i fndlibr (for concurrent processing server) 
$adalnctl.sh status (for apps listener) 
$ lsnrctl status APPS_ (for apps listener)

== > How do you identify the DB Tier for a given instance

 $ df –k displays mount points. If it shows the mount points as

/sid/oracle, /sid/oradata then we can say that it is db tier.

== > Check whether the db services are running or not ?  

$ps –ef |grep –i ora_pmon_SID 

$ps –ef |grep –i smon_smon_SID 

$ps –ux |grep –i ora_dbw0_SID 

$ps –ux |grep –I ora_lgwr_SID 

$ps –ux |grep –i ora_ckpt_SID 

If any one of these services are running on this node then we can say that it is DB tier.

== > How do you identify the No. of nodes in Middle tier

From FND_NODES we can find out the no of nodes in the Middle tier.

select node_name,support_cp,support_forms,support_admin,support_web, Support_db from FND_NODES;

Here we can see the no of nodes for an apps instance and we can see how many nodes belongs to apps tier and how many nodes belongs to db tier.

if APPL_TOP is shared, we can find out the no of nodes in middle tier by seeing the number of .xml files. (as many no of .xml files that many no of nodes)

== > How do you identify the No. of nodes in DB TIER?

Ans: > select node_name, support_db from FND_NODES;

== > How do you identify the No. of nodes for an Apps Instance

Ans: FND_NODES gives the no of nodes for an Apps Instance.

sql> select node_name from FND_NODES;

this displays the no of nodes for an apps instance.

== > specify the command to test the connectivity to Database Node

$tnsping

$TWO_TASK