Script to Trace the Session causing issue on the Database

Download the script from HERE.

#!/bin/bash

clear scr

echo "*********************************************"
echo "************** TRACING SESSION **************"
echo "*********************************************"

sqlplus -S / as sysdba@$1 <<EOF

spool /tmp/p1.txt

show parameter TIMED_STATISTICS

spool off

EOF

val1=`cat /tmp/p1.txt | egrep -i "TRUE|FALSE"`


if [[ "$val1" =~ FALSE ]]; then

echo "change the parameter TIMED_STATISTICS to TRUE"

read permission

if [[ "$permission" =~ Y|y ]]; then

sqlplus -S / as sysdba<<EOF

alter session set TIMED_STATISTICS=TRUE;

EOF

fi

fi

rm -f /tmp/p1.txt

sqlplus -S / as sysdba <<EOF

spool /tmp/p1.txt

show parameter sql_trace

spool off

EOF

val2=`cat /tmp/p1.txt | egrep -i "TRUE|FALSE"`

if [[ "$val2" =~ FALSE ]]; then
    echo "Do you want to change the Parameter SQL TRACE value to TRUE at SESSION LEVEL"

read permission

if [[ "$permission" =~ Y|y ]]; then

sqlplus -S / as sysdba <<EOF

alter session set SQL_TRACE=TRUE;

EOF

fi

fi

rm -f /tmp/p1.txt


sqlplus -S / as sysdba@$1 <<EOF


show parameter STATISTICS_LEVEL
show parameter DIAGNOSTICE_DEST
show parameter USER_DUMP_DEST
sho parameter event

prompt Enabling the 10046 Trace Event at Session Level;
alter session set events='10046 trace name context forever, level 12';

prompt Finding the Oracle Session causing Database Issues;

SET LINE 500 PAGESIZE 50
SELECT S.SID,S.SERIAL#,S.MODULE,S.USERNAME,S.STATUS,S.MACHINE,S.TERMINAL,P.SPID
FROM V\$SESSION S ,V\$PROCESS P
WHERE S.PADDR=P.ADDR
AND P.SPID=$2
/

EOF

sqlplus -prelim / as sysdba <<EOF

prompt Gathering the Trace Report;
oradebug setospid $2;
oradebug event 10046 trace name context forever, level 12;
prompt Gathered Trace File Name;
oradebug tracefile_name;
oradebug event 10046 trace name context off;

EOF

OUTPUT.

[oracle@node1 ~]$ top

top - 18:23:45 up 12 days, 17:22,  3 users,  load average: 3.82, 2.39, 1.80

Tasks: 224 total,   6 running, 218 sleeping,   0 stopped,   0 zombie

Cpu(s):  3.9%us, 36.4%sy,  0.0%ni, 10.8%id, 47.5%wa,  0.3%hi,  1.0%si,  0.0%st

Mem:   3042676k total,  2861800k used,   180876k free,   335884k buffers

Swap:  1534168k total,   158732k used,  1375436k free,  1298864k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

 7363 oracle    16   0 1011m  90m  87m R 31.4  3.0   0:14.71 oraclenode1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

  676 oracle    15   0 1032m  46m  44m S  1.3  1.6   0:26.86 ora_lgwr_node1

  674 oracle    15   0 1036m 297m 274m S  1.0 10.0   0:56.96 ora_dbw0_node1

[oracle@node1 script]$ sh trace.sh node1 7363

*********************************************

************** TRACING SESSION **************

*********************************************

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

timed_statistics                     boolean     TRUE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sql_trace                            boolean     FALSE

Do you want to change the Parameter SQL TRACE value to TRUE at SESSION LEVEL

yes

Session altered.

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

statistics_level                     string      TYPICAL

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/nod

                                                 e/node1/trace

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

event                                string

xml_db_events                        string      enable

Enabling the 10046 Trace Event at Session Level

Session altered.

Finding the Oracle Session causing Database Issues

       SID    SERIAL# MODULE                                                           USERNAME                       STATUS   MACHINE                      TERMINAL                SPID

---------- ---------- ---------------------------------------------------------------- ------------------------------ -------- ---------------------------------------------------------------- ------------------------------ ------------------------

        52       3550 SQL*Plus                                                         TEST                           ACTIVE   node1.oracle.com             pts/2                           7363

Gathering the Trace Report

Oracle pid: 48, Unix process pid: 7363, image: oracle@node1.oracle.com (TNS V1-V3)

Statement processed.

Gathered Trace File Name

/u01/app/oracle/diag/rdbms/node/node1/trace/node1_ora_7363.trc

Statement processed.