Tracing session activity over a remote database link

Yesterday someone asked me how to trace a session that selects from a view in a remote database via a link. If they activated the trace on the local instance, they wouldn’t see the bulk of the work which was happening on the remote instance – just a bunch of SQL*Net Message from dblink waits.

There are a number of ways to approach this. For example, using Active Session History (ASH) on the remote database might give you the information you’re looking for without tracing, but only if you’re on 10g. I proposed a solution that’ll work on version 8.1 and later and tested it on 9.2.0.4

I suggested using a login trigger on the remote database because it would ensure that all of the remote work was captured properly without needing to go into the remote database, quickly spot the session of interest and enable tracing on it. The requirement was further complicated by the fact that this job is probably using parallel query. I was initially sure that I’d find tons of web references to this, but was surprised to be wrong, so I thought I may as well stick a simple example on the blog. I’ve stripped out a few lines here and there to make it shorter, but hopefully the gist is clear.

First I’ll go into the remote database and create the trigger. (Note – the only reason I forced parallel query on within the trigger was to simulate the questioner’s requirement more accurately)

SQL> select name from v$database;

NAME
---------
REMOTE92

SQL> connect / as sysdba
Connected.
SQL> create trigger trace_testuser
2       after logon on TESTUSER.schema
3       begin
4       execute immediate 'alter session set events ''10046 trace name context
forever, level 8''';
5       execute immediate 'alter session force parallel query';
6 end;
7 /

Trigger created.

Next I’ll check that there are no current TESTUSER sessions running on the remote database …

SQL> select * from v$session where username = 'TESTUSER';

no rows selected

and (at the shell prompt) check that there are no stray trace files kicking around that would confuse the end results.

[oracle@ISP4400 udump]$ pwd
/home/oracle/admin/REMOTE92/udump

[oracle@ISP4400 udump]$ ls -ltra
total 16
drwxr-xr-x 8 oracle dba 4096 Aug 3 23:58 ..
drwxr-xr-x 2 oracle dba 4096 Aug 3 23:58 .

[oracle@ISP4400 udump]$ cd ../bdump

[oracle@ISP4400 bdump]$ ls -ltra
total 32
drwxr-xr-x 8 oracle dba 4096 Aug 3 23:58 ..
drwxr-xr-x 2 oracle dba 4096 Aug 4 00:05 .
-rw-r--r-- 1 oracle dba 9447 Aug 4 00:22 alert_REMOTE92.log

Now that the remote instance is prepared, I’ll connect to the local instance – the one the job will run in – and show the different database name and database link details.

SQL> connect / as sysdba
Connected.
SQL> select name from v$database;

NAME
---------
TEST92

SQL> select * from dba_db_links
2 /

OWNER
------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
---------
PUBLIC
REMOTE92_TESTUSER
TESTUSER
REMOTE92
04-AUG-06

Now I’ll connect as TESTUSER and simulate application activity across the database link.

SQL> connect testuser/testuser
Connected.
SQL> select count(*) from test_tab1@remote92_testuser;

COUNT(*)
----------
1024000

Hopefully the remote instance will have generated a few trace files for the activity.

[oracle@ISP4400 bdump]$ ls -ltra
total 64
drwxr-xr-x 8 oracle dba 4096 Aug 3 23:58 ..
-rw-r--r-- 1 oracle dba 9447 Aug 4 00:22 alert_REMOTE92.log
drwxr-xr-x 2 oracle dba 4096 Aug 4 00:54 .
-rw-r----- 1 oracle dba 9227 Aug 4 00:54 remote92_p001_4200.trc
-rw-r----- 1 oracle dba 10568 Aug 4 00:54 remote92_p000_4198.trc
[oracle@ISP4400 bdump]$ ls -ltra ../udump
total 36
drwxr-xr-x 8 oracle dba 4096 Aug 3 23:58 ..
drwxr-xr-x 2 oracle dba 4096 Aug 4 00:54 .
-rw-r----- 1 oracle dba 14310 Aug 4 00:55 remote92_ora_4196.trc

Without doubt, the difficult part of this solution is limiting the collection scope. There might be all sorts of TESTUSER sessions on the remote database that I don’t want to trace, but the trigger could be modified to limit the tracing by checking which machine the session is logging in from, to give just one example.

In case people arrive here while searching for how to trace remote activity and bearing in mind this is only one approach, all other suggestions are welcome.