How to enable SQL trace in EBS R12.2

SQL trace can be enabled in any E-Business module. The following possible ways could be used to enable TRACE for form, self-service page, or concurrent program.

Enable trace for Self Service Page

  • Set profile FND: Diagnostics = Yes at USER level.
  •  log in to the Personal Home Page as that user and select the Diagnostics link at the top of the page.
  • Select Set Trace Level and click Go.
  • Select the desired trace level and click Save.
  • Write down the trace id number(s).
  • Perform the activity that you want to trace.
  • Return to the ‘Diagnostics’ page.
  • Select `Set Trace Level’ and click Go.
  • Select ‘Disable Trace’ and click Go.
  • Write down the trace id number(s) if different.
  • Go to diagnostics_dest for your database and collect the raw trace file(s) suffixes by the trace id number(s) you have recorded.

Enable trace for forms

  • Set profile FND: Diagnostics = Yes at USER level.
  • log in to the Application
  • Navigate to the form where you want to trace
  • Turn on Tracing by using the menu option: Home > Diagnostics > Trace > Trace with waits
  •  A pop-up with the trace file name and location gets displayed. Note down the trace filename
  •  Perform the activity that you want to trace.
  • Return to Home > Diagnostics > Trace >
  •  Select ‘Disable Trace’ and click Go.
  • Go to diagnostics_dest for your database and collect the raw trace file(s) suffixes by the trace id number(s) you have recorded.

Users reporting issues for any custom forms then we should follow the below steps which are easy and very effective. When he runs the program at that time should enable trace using the below steps:

Login to front-end -> Help -> Diagnostics -> Trace -> "Trace with Binds and waits” .
Then there will be a trace file generate under the below patch:


$cd $ORACLE_HOME/admin/<$CONTEXT_NAME>/udump


Then after either Trace Analyzer or tkprof as shown below commands:

Enable trace for Oracle Concurrent Program

Navigate to Concurrent > Program > Define screen

  • Search for the concurrent program you want to trace
  • Check the Enable Trace box to turn on tracing for the concurrent program
  • Submit and run the concurrent program
  • Write down the request_id of your concurrent program job
  • Go back to the Define screen and un-check the Enable Trace box for this concurrent program
Retrieve the raw trace file using the request_id
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
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 apps.fnd_concurrent_requests req, gv$session ses, gv$process proc,
gv$parameter dest, gv$parameter dbnm, apps.fnd_concurrent_programs_vl prog,
apps.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
and prog.executable_id=execname.executable_id;

Enable trace for a concurrent running request

Run the below query to find the SPID and SID of the concurrent request

col addr format a11
col program format a20 trunc
col logon_time format a18
col osuser format a8 heading unixUsr
col p_user format a9 heading unixUsr
col terminal format a7 heading unixtrm
col command format 99 heading Cd
col machine format a7
col action format a10
col module format a10
col requestor format a20
col cmgr_job format a38 trunc heading 'CMgr_job'
set pagesize 24
Prompt Enter the Concurrent Request ID number:
select s.inst_id, fcr.request_id, fv.requestor, fv.Program cmgr_job,
p.PID,
p.SERIAL#,
p.USERNAME p_user,
p.SPID,
to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') Logon_Time,
s.program,
s.command,
s.sid,
s.serial#,
s.username,
s.process,
s.machine,
s.action,
s.module
from apps.fnd_concurrent_requests fcr,
apps.FND_CONC_REQ_SUMMARY_V fv,
gv$session s,
gv$process p
where fcr.request_id = &request_id
and p.SPID = fcr.oracle_process_id
and s.process = fcr.OS_PROCESS_ID
and s.inst_id = p.inst_id
and p.addr = s.paddr
and fv.request_id = fcr.request_id
;

Now trace can be enabled on the SID using oradebug or DBMS program as

SQL> oradebug setospid ( sid from above)
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
SQL>oradebug tracefile_name
prod _ora_1***.trc
Wait for 15-20 minutes
SQL> oradebug event 10046 trace name context off
Using DBMS program
Full level with wait event And bind trace

execute dbms_system.set_ev(‘sid’,’serial’,10046,12,’’);

To put trace off

execute dbms_system.set_ev(‘sid’,’serial’,10046,0,’’);

How to analyze the Trace files
Once the trace files are found, we can use the tkprof utility to find the culprit statement from the trace file
tkprof **_ora_**_a.trc **_ora_*_a.txt sys=no explain=apps/apps sort=prsela,exeela,fchela
— For current session only
SQL> ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 8’;
SQL> ALTER SESSION SET EVENTS ‘10046 trace name context off’;
— For current session / other session
SQL> CONN sys/password AS SYSDBA;   — user must have SYSDBA
SQL> ORADEBUG SETMYPID;                  — debug current session
SQL> ORADEBUG SETOSPID 1234;         — debug session with OS Process ID (SID)
SQL> ORADEBUG SETORAPID 123456;  — debug session with Oracle Process ID (SPID)
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8;
SQL> ORADEBUG TRACEFILE_NAME;    — display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

Trace on System Level:
How to enable trace on the entire system (Instance wide)
SQL> alter system set events ‘10046 trace name context forever,level 8’;
OR set the following event in init.ora file:
event=”10046 trace name context forever,level 8″

Comments

Post a Comment

Popular posts from this blog

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"

Image
 During connection from SQL Developer to EBS database got below error message: Status : Failure - Test failed: IO Error: Got minus one from a read call Following solution can be done : 1. Go to directory $ORACLE_HOME / network / admin 2. Modify sqlnet.ora file with following parameter : tcp.validnode_checking = no 3. If you don 't want to disable this, you can put the machine names as follows: tcp.invited_nodes=(machine1, machine2) 3. Bounce the listener. Due to security enhancements it is not good to put tcp.validnode_checking = no or comment out will through error when we start listener like below: Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shsebs7idb1.appsolworld.com)(PORT=1522))) TNS-00584: Valid node checking configuration error TNS-12560: TNS:protocol adapter error Better plan is to add localhost or IP address of client (Local Computer) in the sqlnet.ora file That will solve the problem NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME) SQLNET.EXPI
Read more

How to troubleshoot Long Running Concurrent Request in EBS 12.2

 The following points need to answer before jumping into troubleshooting: Are there any recent code changes done in the concurrent program?           Check long_running_sid and verify the recent code change of last_ddl_timestamp after a             discussion with the developer. Was this running long in the last few run as well, or was this time only?            ==> Check the program history. How much time does it take to complete?           Once again program history can give us some idea. Are these jobs fetching higher data compared to the last run           ===> Confirm on data change . Query Plan change will indicate this issue. Does this job runs at any specific time or it can be run anytime           ===> The nature of the job can help to understand the job's nature. Does this job fetch data using DB Link          ====> Check whether the DB link is involved or not. The below-given approach would help to conclude the gray areas. Check the load of the server using t
Read more

Few Important steps of Oracle Database Clone

 Make Sure oraInst.loc location is correct and accessble. [demantra@dsiddem ~]$ cat /etc/oraInst.loc inventory_loc=/orastage/oraInventory inst_group=dba [demantra@dsiddem ~]$ cd /orastage/oraInventory [demantra@dsiddem oraInventory]$ ls -ltr total 16 drwxrwx---. 5 demantra dba 4096 Jul 22 19:37 backup drwxrwx---. 2 demantra dba 4096 Jul 22 19:37 ContentsXML drwxrwx---. 2 demantra dba 4096 Jul 22 19:37 locks drwxrwx---. 3 demantra dba 4096 Jul 23 10:08 logs Remove Oracle Home from Inventory [demantra@dsiddem oraInventory]$ cd $ORACLE_HOME/oui/bin [demantra@dsiddem bin]$ ./runInstaller -detachHome ORACLE_HOME=$ORACLE_HOME Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB.   Actual 3039 MB    Passed The inventory pointer is located at /etc/oraInst.loc Run Clone.pl [demantra@dsiddem bin]$ echo $ORACLE_HOME /dnbsid/demantra/oracle/19.0.0 [demantra@dsiddem bin]$ echo $ORACLE_BASE /dnbsid/demantra [demantra@dsiddem bin]$ E01=ORACLE_HOME=$ORACLE_HOME [dema
Read more