Home » RDBMS Server » Server Administration » Trace non-invokers sessions of a different instance (Oracle Database 19c)
Trace non-invokers sessions of a different instance [message #683615] Sun, 07 February 2021 14:51 Go to next message
Andrey_R
Messages: 362
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I have an application creating some 5 different Oracle sessions from a single client PID.
One of these is running a process I want to trace.

Unfortunately, the application design has limitations that prevent me from being able to initiate execution of commands to oracle from a session by my choice,
nor know which session is executing the stuff that I want to trace ( However, I do know that one of these does, so I am interested in tracing all of them..)

I also am not able to run any session commands, such as "dbms_application_info.set_client_info(..." in order to mark sessions for tracing.

I am trying to trace all of them with dbms_monitor. In a non-RAC environment this works fine to give the application the commands to perform the trace:
( This demonstration gives only one session, since the rules of the forum require SQL*Plus examples, but in the application I have multiple sessions of different instances running from one client(windows) process )

SQL> set lines 1000 pages 20000
SQL> set num 5
SQL> col start_trace_command for a50
SQL> col end_trace_command for a50
SQL> col tracefile for a50
SQL> select gs.inst_id,
  2         'exec dbms_monitor.session_trace_enable(session_id=>' || SID ||
  3         ' ,serial_num=>' || gs.SERIAL# || ',binds=>true,waits=>true);' AS start_trace_command,
  4         'exec dbms_monitor.session_trace_disable(session_id=>' || SID ||
  5         ' ,serial_num=>' || gs.SERIAL# || ');' AS end_trace_command,
  6         gp.TRACEFILE
  7    from gv$session gs, gv$process gp
  8   where gp.ADDR = gs.paddr
  9     and gs.INST_ID = gp.INST_ID
 10     and substr(process, 1, instr(process, ':', 1) - 1) =
 11         (select substr(process, 1, instr(process, ':', 1) - 1) as os_pid
 12            from gv$session
 13           where userenv('sessionid') = audsid
 14             and instr(process, ':') > 0);

INST_ID START_TRACE_COMMAND                                END_TRACE_COMMAND                                  TRACEFILE
------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
      1 exec dbms_monitor.session_trace_enable(session_id= exec dbms_monitor.session_trace_disable(session_id /location01/log/diag/rdbms/mydb/MYDB/trace/MYD
        >693 ,serial_num=>40600,binds=>true,waits=>true);  =>693 ,serial_num=>40600);                         B_ora_10819.trc


SQL>



When I am trying to do it on a RAC environment - if the "main" session from which it runs and some of the monitored sessions are connected to different instances - the session for trace will not be identified.


I can find out the sessions I want to trace and which instance are they on ,the commands to perform the trace with DBMS_MONITOR, and the trace file name,
However, I cannot figure out how to set client_info data for them from a different session, or how to trace all sessions that answer the combination known to me which is SID,SERIAL#,INST_ID

So the question is..

1. Is there a way to solve the issue by tracing multiple sessions of a different instance than the instance of the session from which I want to trace, by SID,SERIAL#,INST_ID
or
2.
is there a way to set client_info or similar session-parameters from a remote session,
so I can use procedures such as DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE that allow me to trace by an identifier, in case I am able to set it to the sessions I want...

The documentation I reviewed:

https://docs.oracle.com/database/121/ARPLS/d_monitor.htm#ARPLS67162

Many thanks in advance,
Andrey

[Updated on: Sun, 07 February 2021 15:01]

Report message to a moderator

Re: Trace non-invokers sessions of a different instance [message #683616 is a reply to message #683615] Sun, 07 February 2021 15:12 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
Andrey, I have to apologize because I do not understand what you are describing.
I think you have one process running on one Windows machine which has launched multiple sessions against the database. THese have been load balanced across several instances. Is that correct?
You say that you can find out (how?) which sessions to trace. So what is the problem?
Re: Trace non-invokers sessions of a different instance [message #683617 is a reply to message #683615] Sun, 07 February 2021 15:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. You can try using a db link to the other instance:
exec dbms_monitor.session_trace_enable@other_instance(session_id=...
2. No way, even for a local session.

Re: Trace non-invokers sessions of a different instance [message #683618 is a reply to message #683617] Sun, 07 February 2021 15:52 Go to previous messageGo to next message
Andrey_R
Messages: 362
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Sun, 07 February 2021 23:12
Andrey, I have to apologize because I do not understand what you are describing.
I think you have one process running on one Windows machine which has launched multiple sessions against the database. THese have been load balanced across several instances. Is that correct?
You say that you can find out (how?) which sessions to trace. So what is the problem?
Thank you John Watson for the reply.

Yes, you understand correctly ( and phrased it much better,simpler than me Embarassed), this is what I am trying to do.

Example of the situation:

- I have 5 sessions in a 2-node RAC, ran by same oracle client (windows) process ( application uses it )
- 1 is the "main", 4 others are running parts of the work(code, sending requiests to do SQLs to oracle) in parallel
- I want to trace all of them, since I cannot know due to application design limitations which does the SQL that is slow
- Main session on inst_id=1, 2 of the others are of inst_id=1 , another 2 are on inst_id=2

I want to start trace on each of them, by actions invoked from the 1 session of inst_id=1.
On all 4, including the inst_id=2 sessions.


I can trace the sessions that are on inst_id=1 with dbms_monitor.session_trace_enable, from my main one which is also inst_id=1
But the ones with inst_id=2 - trace will not work for them, since there is no option to add inst_id specification for the procedure.

The DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE traces all sessions where client_id was set, but it needs to be done from the sessions of inst_id=2 ( well, from all 4 sessions basically), to which I don't have access..

I am trying to see if there's a way I didn't observe to either set the client_id from another session so I can specify it in the DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE,
Or if I can find another procedure that can start trace for the inst_id=2 sessions, using sid,serial# & inst_id parameters I provide it as input



Michel Cadot wrote on Sun, 07 February 2021 23:28

1. You can try using a db link to the other instance:
exec dbms_monitor.session_trace_enable@other_instance(session_id=...
2. No way, even for a local session.

Thanks for the reply.

1. Application design/customer limitations won't allow me to create db links Sad
2. If I would have access to the sessions, to each as "the local session", I would execute ( from each of the 4 )


SQL> exec dbms_session.set_identifier ( client_id => 'marked_for_trace');

PL/SQL procedure successfully completed.

Then would trace by ( from the main session) :

SQL> exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE( client_id => 'marked_for_trace');

PL/SQL procedure successfully completed.
Re: Trace non-invokers sessions of a different instance [message #683619 is a reply to message #683617] Sun, 07 February 2021 17:01 Go to previous messageGo to next message
Andrey_R
Messages: 362
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Sun, 07 February 2021 23:28

1. You can try using a db link to the other instance:
exec dbms_monitor.session_trace_enable@other_instance(session_id=...

Can you please refer me to any documentation/examples of how this can work ?
Re: Trace non-invokers sessions of a different instance [message #683620 is a reply to message #683618] Mon, 08 February 2021 01:34 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
Quote:
dbms_session.set_identifier ( client_id => 'marked_for_trace');
Could you put this in the body of an AFTER LOGON ON SCHEMA trigger?
Re: Trace non-invokers sessions of a different instance [message #683621 is a reply to message #683619] Mon, 08 February 2021 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Andrey_R wrote on Mon, 08 February 2021 00:01
Michel Cadot wrote on Sun, 07 February 2021 23:28

1. You can try using a db link to the other instance:
exec dbms_monitor.session_trace_enable@other_instance(session_id=...
Can you please refer me to any documentation/examples of how this can work ?

No reference but not a big deal to test it (2 sessions in 2 different databases MIKA and MIKB):
09:16:48 MIKA> select sid,serial#,sql_trace from v$session where sid=sys_context('userenv','sid');
       SID    SERIAL# SQL_TRAC
---------- ---------- --------
       148          3 DISABLED
09:16:54 MIKB> exec dbms_monitor.session_trace_enable@mika(session_id=>148,serial_num=>3)

PL/SQL procedure successfully completed.
09:17:01 MIKA> select sid,serial#,sql_trace from v$session where sid=sys_context('userenv','sid');
       SID    SERIAL# SQL_TRAC
---------- ---------- --------
       148          3 ENABLED
09:17:07 MIKB> exec dbms_monitor.session_trace_disable@mika(session_id=>148,serial_num=>3)

PL/SQL procedure successfully completed.
09:17:12 MIKA> select sid,serial#,sql_trace from v$session where sid=sys_context('userenv','sid');
       SID    SERIAL# SQL_TRAC
---------- ---------- --------
       148          3 DISABLED
They were in 2 different Oracle versions.

[Updated on: Mon, 08 February 2021 02:22]

Report message to a moderator

Re: Trace non-invokers sessions of a different instance [message #683623 is a reply to message #683621] Mon, 08 February 2021 08:32 Go to previous message
Andrey_R
Messages: 362
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Mon, 08 February 2021 10:21

Andrey_R wrote on Mon, 08 February 2021 00:01
Michel Cadot wrote on Sun, 07 February 2021 23:28

1. You can try using a db link to the other instance:
exec dbms_monitor.session_trace_enable@other_instance(session_id=...
Can you please refer me to any documentation/examples of how this can work ?
No reference but not a big deal to test it (2 sessions in 2 different databases MIKA and MIKB):
09:16:48 MIKA> select sid,serial#,sql_trace from v$session where sid=sys_context('userenv','sid');
       SID    SERIAL# SQL_TRAC
---------- ---------- --------
       148          3 DISABLED
09:16:54 MIKB> exec dbms_monitor.session_trace_enable@mika(session_id=>148,serial_num=>3)

PL/SQL procedure successfully completed.
09:17:01 MIKA> select sid,serial#,sql_trace from v$session where sid=sys_context('userenv','sid');
       SID    SERIAL# SQL_TRAC
---------- ---------- --------
       148          3 ENABLED
09:17:07 MIKB> exec dbms_monitor.session_trace_disable@mika(session_id=>148,serial_num=>3)

PL/SQL procedure successfully completed.
09:17:12 MIKA> select sid,serial#,sql_trace from v$session where sid=sys_context('userenv','sid');
       SID    SERIAL# SQL_TRAC
---------- ---------- --------
       148          3 DISABLED
They were in 2 different Oracle versions.

Looks helpful, but involves customer database's cooperation, setting up specific instance connection strings e.t.c
Thank you for the idea, however, interesting approach.



John Watson wrote on Mon, 08 February 2021 09:34
Quote:
dbms_session.set_identifier ( client_id => 'marked_for_trace');
Could you put this in the body of an AFTER LOGON ON SCHEMA trigger?

That may require privileges on customer databases I am not owner of.
But there seems to be hope from the application side, I may get to have the audsid carved into the client_id by an applicative "logon-trigger".

Better late than never, I guess.


Many thanks both for your ideas and assistance.

Best regards,
Andrey
Previous Topic: Trying to export a table that contains blobs
Next Topic: ORA-04031: unable to allocate 4064 bytes of shared memory
Goto Forum:
  


Current Time: Sun Apr 11 16:26:04 CDT 2021