TRACING SERVICES IN RAC

In this post, I will demonstrate how we can trace all the sessions who connected to a RAC database using a service . In this case, trace files of different sessions will be generated on the servers hosting the instances they are connected to. Hence, we will need to combine their

trace files Enable tracing for a service and collect tracefiles generated in various instances and then run tkprof on the resulting file.

CURRENT SCENARIO:

Name of the admin managed RAC database : amdb

No. of nodes : 3

Host names : host01, host02, host03

- Create a service amdbs with

. preferred nodes host01 and host02

. Available nodes : host03

– enable tracing for sessions connected using amdbs service

SYS> exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE('amdbs');

– create 2 sessions using amdbs

[oracle@host01 ~]$ sqlplus system/oracle@amdbs

– Issue two different queries in two sessions –

SQL1>select * from hr.employees;

SQL2>select * from scott.emp;

– After queries have completed, disable tracing

SQL>  exec dbms_monitor.SERV_MOD_ACT_TRACE_disable('amdbs');

– Find out the instance names of the two sessions which issued

– the queries. Trace files will be created on corresponding servers

SQL1> sho parameter instance_name

NAME TYPE VALUE

———————————— ———– ——————————

instance_name string amdb3

SQL2> sho parameter instance_name

NAME TYPE VALUE

———————————— ———– ——————————

instance_name string amdb1

– Run trcsess on both the nodes to get the corresponding files containing statements executed by sessions connected using service amdbs

[oracle@host01 trace]$ cd /u01/app/oracle/diag/rdbms/amdb/amdb1/trace
trcsess output=amdbhost01.trc service=amdbs *.trc
[oracle@host03 trace]$ cd /u01/app/oracle/diag/rdbms/amdb/amdb3/trace
trcsess output=amdbhost03.trc service=amdbs *.trc

– combine trcsess output files from both nodes –

[oracle@host03 trace]$scp amdbshost03.trc host01:/u01/app/oracle/diag/rdbms/amdb/amdb1/trace/ 

[oracle@host01 trace]$ cat amdbhost01.trc amdbshost03.trc>amdbhost.trc 

[oracle@host01 trace]$ tkprof amdbhost.trc amdbhost.out

– check that tkprof output file amdbhost.out contains the statements issued by both the sessions connected using amdbs (serach for hr.employees and scott.emp)