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.


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)
 [oracle@host01 trace]$vi amdbhost.out

Related links:


11G R2 RAC Index
11g R2 RAC : Services
Service Created Using EM Does Not Update DD or OCR
Workload Management in 11g R2 RAC : Failover



Your comments and suggestions are welcome!