ON CALL DBA SUPPORT

— Database blog

SQL TRACE FOR STREAMS PROCESSES

Posted by ssgottik on 11/10/2013

STEP 01.GET THE SID OF THE PROCESSES RUNNING IN THE DATABASE USING ONE OF THE BELOW( As per your requirements):

— For the capture process:
SQL >  select SID, CAPTURE_NAME from v$streams_capture;
— For the propagation:
SQL > select QNAME, DESTINATION, PROCESS_NAME, SESSION_ID from dba_queue_schedules;
— For the apply process:
SQL > select SID, APPLY_NAME from v$streams_apply_server;

STEP 02. GET THE PID FROM THE SID :

SQL > select PID, p.PROGRAM from v$process p, v$session s where s.paddr=p.addr and sid=<SID obtained from queries above>;

STEP 03. BEGIN TARACING USING BELOW COMMANDS:

SQL > oradebug setorapid <PID from previous step>
Oracle pid: 259, Unix process pid: 99878, image: oracle@vmlinux1 (AS0B)
SQL > oradebug unlimit
SQL > oradebug Event 10046 trace name context forever, level 12

A trace file with the above PID will be genetrace . In my case the trace file looks like orcl_as01_99878.trc

STEP 04. STOP TRACING USING BELOW COMMAND:

SQL > oradebug Event 10046 trace name context off

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: