oradebug: example of building of the command syntax

Over time oradebug became an extremely powerful tool, yet there is no official documentation available apart of contextual help (sourced from SQL> oradebug doc).
Good news, there are several good blogs mitigating that deficiency (see the list close the end of this post).
That post is in turn an attempt to present in the structured way of achieving the understanding of building the syntax based on the example which I have used on my way to solve a problem described here.

The example which is our reference looks like this :

alter system set events 'trace[rdbms.nsmtio][sql:9ur4ytc0n99wr] {occurence:start_after 0, end_after 1000}' ;

Treat that command above as just a tiny sample. Needless to say, the scope of usage and the offerings of that interface are huge.

oradebug: from Contextual Help to Practical Commands

Admittedly, I’ve been always struggling before I managed to work out the correct syntax for the oradebug. Below is simply my attempt to present you with the path along the way I have applied to achieve the result I required.

Technically, I was in need to construct the syntax of a command which was to intercept an execution of a specific sql_id, produce the corresponding trace (the traced component was supposed to be NSMTIO).
I have spotted this trace file was fairly long.
Because it was sufficient to examine its beginning only, so I also chose to limit its content to the first 1000 lines.

If it helps, getting around oradebug resembles the way RMAN doc.text interface has been designed. You’ll see that you’ll be able to recursively dive deeper and deeper along the path of a certain topic, then possibly come back to the certain level, further exploring another (sub)topic starting from that (sub)level.
oradebug’s resemblance to searching through RMAN documentation is purely my impression and I hope I haven’t introduced any mess by that.

Let’s start from the “root”. This is how you invoke it and get to the starting point :

SQL > oradebug doc

Internal Documentation
**********************

  EVENT                           Help on events (syntax, event list, ...)
  COMPONENT       [<comp_name>]   List all components or describe <comp_name>

Clearly, we have two paths to explore – EVENT and COMPONENT
For the start, let’s dive into the branch EVENT :

SQL>  oradebug doc EVENT

Event Help:
***********

  Formal Event Syntax
  --------------------
    <event_spec>   ::= '<event_id> [<event_scope>]
                                   [<event_filter_list>]
                                   [<event_parameters>]
                                   [<action_list>]
                                   [off]'

    <event_id>     ::= <event_name | number>[<target_parameters>]

    <event_scope>  ::= [<scope_name>: scope_parameters]

    <event_filter> ::= {<filter_name>: filter_parameters}

    <action>       ::= <action_name>(action_parameters)

    <action_parameters> ::= <parameter_name> = [<value>|<action>][, ]

    <*_parameters> ::= <parameter_name> = <value>[, ]


Examples and Notes section
(…)

  Help sub-topics
  ---------------

    NAME    [<event_name>]      List all events or describe <event_name>
    SCOPE   [<scope_name>]      List all scopes or describe <scope_name>
    FILTER  [<filter_name>]     List all filters or describe <filter_name>
    ACTION  [<action_name>]     List all actions or describe <action_name>

At the end of this output related to EVENT we have a good hint how to build our command using the contextual help:

Help sub-topics
  ---------------

    NAME    [<event_name>]      List all events or describe <event_name>
    SCOPE   [<scope_name>]      List all scopes or describe <scope_name>
    FILTER  [<filter_name>]     List all filters or describe <filter_name>
    ACTION  [<action_name>]     List all actions or describe <action_name>

Now, how to read it? – Pay attention to the two grey windows, I will combine them.


SQL> alter system/session set events ‘do something... set an EVENT, perhaps trace?‘
So, we can do something , e.g. trace, but don’t get limit just to “trace” – in general I tend to think that this section should be represented by a verb.
Technically, for now you’ll set an event with the intention to do something with a (corresponding) component or error number (errno), etc. with the predefined scope (if applicable), then possibly apply the filtering criteria, eventually (and optionally) associate an action which is supposed to fire.

For the start try :

SQL> oradebug doc EVENT NAME
(rather long output)

You will notice several libraries listed, e.g. DIAG or RDBMS containing events which belong to the given library. What comes at handy, there is a short description of each event.
To get a feeling that you’ll have the possibility to dive deeper recursively, try for example:
SQL> oradebug doc EVENT NAME kg_event
or
SQL> oradebug doc EVENT NAME trace
Both, kg_event and trace (arguably, the most popular) are the predefined names of the events in the GENERIC and DIAG libraries, respectively.

You can also “step up one level higher” and list all events belonging to a library (e.g. RDBMS library):
SQL> oradebug doc EVENT NAME RDBMS
So, for now these events are invoked in the first section of your command.


So, once you decided which event to invoke, you can trace a component.
Now we need to come back to NAME.

If you delve into any of these items, you’ll get the contextual help.

Note, the list for NAME and ACTION is rather long which gives the notion how versatile this interface is.

SQL> oradebug doc event NAME
SQL> oradebug doc event SCOPE
SQL> oradebug doc event FILTER
SQL> oradebug doc event ACTION

…deeper into the EVENT –> NAME section :

SQL> oradebug doc EVENT NAME

Events in library DIAG:
------------------------------
trace[]              Main event to control UTS tracing
disable_dde_action[] Event used by DDE to disable actions
ams_trace[]          Event to dump ams performance trace records
ams_rowsrc_trace[]   Event to dump ams row source tracing
sweep_verification   Event to enable sweep file verification
enable_xml_inc_staging Event to enable xml incident staging format
dbg[]                Event to hook dbgtDbg logging statements
trace_suppress[]     Event for suppressing Trace

ok, so as an event specification we can choose “trace”.

Now, what can we trace ?
Let’s see :

..so deeper into the event – name – trace :

SQL> oradebug doc EVENT NAME TRACE

trace: Main event to control UTS tracing

Usage
-------
trace [ component       <string>[0] ]
   disk            < default | lowest | low | medium | high | highest | disable >,
   memory          < default | lowest | low | medium | high | highest | disable >,
   get_time        < disable | default | seq | highres | seq_highres >,
   get_stack       < disable | default | force >,
   operation       <string>[32],
   function        <string>[32],
   file            <string>[32],
   line            <ub4>,
   conuid          <ub4>

Exhausted “EVENT” path for TRACE. We see that we can trace a component.
Let’s delve into the “component” (note, for a while we returned to the “root”) :

SQL> oradebug doc component


Components in library RDBMS:
--------------------------
  SQL_Compiler                 SQL Compiler ((null))
    SQL_Parser                 SQL Parser (qcs)
    SQL_Semantic               SQL Semantic Analysis (kkm)
    SQL_Optimizer              SQL Optimizer ((null))(…)
(…)
Then somewhere underneath the RDBMS (you’ll need to skip many indentations) you’ll come across : 

  KXD                          Exadata specific Kernel modules (kxd)
    KXDAM                      Exadata Disk Auto Manage (kxdam)
    KCFIS                      Exadata Predicate Push (kcfis)
    NSMTIO                     Trace Non Smart I/O (nsmtio)
    KXDBIO                     Exadata Block level Intelligent Operations (kxdbio)
    KXDRS                      Exadata Resilvering Layer (kxdrs)
    KXDOFL                     Exadata Offload (kxdofl)
    KXDMISC                    Exadata Misc (kxdmisc)
    KXDCM                      Exadata Metrics Fixed Table Callbacks (kxdcm)
    KXDBC                      Exadata Backup Compression for Backup Appliance (kxdbc)

There are obviously plenty of components which can be traced.
If you are not immediately sure what you might be looking for, I suggest, spool the output of “oradebug doc component” and “grep -i” after a pattern of your choice.
Since I got the name of this component (NSMTIO) from other sources, I didn’t have to shoot blindly. Nonetheless, it’s worth to browse through the available components which can be traced. Needless to say, it’s a goldmine.

Ok, so for now we have:

alter system set events trace[RDBMS.NSMTIO]

Now that we know what kind of component we are about to trace, let’s see our options.
If you remember, we can follow the path:

TRACE (a component) –

SCOPE (choose the scope of your tracing, e.g. produce a trace file for a given sql_id. You can combine the sql_ids into a bundle, e.g. [sql:g3yc1js3g2689|7ujay4u33g337]) –

FILTER (set the optional criteria for filtering) –

ACTION (optionally fire an action if the condition following filtering gets fulfilled).

A word of caution, an ACTION can be disastrous, e.g.

SQL> oradebug doc event ACTION kill_instance
kill_instance
        - killing RDBMS instance
Usage
-------
kill_instance()

Now, coming back to the main syntax and let’s focus on our requirement.

Let’s explore the SCOPE branch :

SQL> oradebug doc EVENT SCOPE

Event scopes in library RDBMS:
------------------------------
SQL[]                sql scope for RDBMS
QUEUE[]              queue scope AQ

..and deeper (recursively) :

SQL> oradebug doc event SCOPE SQL

SQL: sql scope for RDBMS

Usage
-------
[SQL:  sql_id          <string>[20] ]

So at this level we have the following syntax (we added the section scope) :
alter system set events trace[RDBMS.NSMTIO][SQL:<sql_id_to_intercept>]
e.g.

alter system set events 'trace[RDBMS.NSMTIO][SQL:25wbs5bshgkpw]'

At this stage we managed to build valid oradebug statement which, once the query with sql_id of 25wbs5bshgkpw gets fired, will produce the trace file containing the specifics of the component NSMTIO from the RDBMS library.

Now, we may possibly want to put some filter upon (sometimes very long) trace file.
Let’s explore the FILTER section (again, I stepped “two floors up”) :

SQL> oradebug doc EVENT FILTER

There are several options – we will go for number of occurrences 
Note the error in the doc: a single “r”.
Worth to be aware while grepping after "occurrence" - it won't return any results.
Euqally, you need to duplicate this typo in your command syntax.

Event filters in library DIAG:
------------------------------
occurence            filter to implement counting for event checks
callstack            filter to only fire an event when a function is on the stack.If the nofname option is used, then the event is fired only when the function is not on the stack
eq                   filter to only fire an event when a == b
ne                   filter to only fire an event when a != b
gt                   filter to only fire an event when a > b
lt                   filter to only fire an event when a < b
ge                   filter to only fire an event when a >= b
le                   filter to only fire an event when a <= b
anybit               filter to only fire an event when (a & b) != 0
allbit               filter to only fire an event when (a & b) == b
nobit                filter to only fire an event when (a & b) == 0
bet                  filter to only fire an event when b <= a <= c
nbet                 filter to only fire an event when a < b or a > c
in                   filter to only fire an event when a is equal to any b .. p
nin                  filter to only fire an event when a is not equal to any b .. p
streq                filter to only fire an event when string s1 = s2 (up to <len> characters)
strne                filter to only fire an event when string s1 != s2 (up to <len> characters)
tag                  filter to only fire an event when a tag is set

Event filters in library RDBMS:
------------------------------
wait                 filter for specific wait parameters and wait duration
process              filter to set events only for a specific process
px                   filter to check identity of the process for fault injection

Event filters in library GENERIC:
------------------------------
errarg               filter to set error events only for a specific error argument

So, yet again a level deeper from FILTER:

SQL> oradebug doc event filter occurence

occurence: filter to implement counting for event checks

Usage
-------
{occurence:  start_after     <ub4>,
             end_after       <ub4> }

alter system set events ‘trace[RDBMS.NSMTIO][SQL:<sql_id_to_intercept>]{occurence: start_after <start_number>,  end_after <end_number>}’

So, the final oradebug syntax for the tracing of the component NSMTIO for a given SQL_ID (the first 1000 lines will be intercepted) is :

SQL> alter system set events 'trace[rdbms.nsmtio][sql:25wbs5bshgkpw] {occurence:start_after 0,  end_after 1000}' ;

From “high above” this command matches the following pattern (yet it misses the “ACTION” part):
alter system set events 'EVENT[COMPONENT][SCOPE] {FILTER}' ;

How to verify which oradebug tracing is currently active

There are certainly several good ways to efficiently carry out such checks.
I suggest something which works for me (best save it as e.g. lstrall.sql) :

# cat lstrall.sql
set echo on

oradebug setmypid
oradebug eventdump system

set echo off

Example:
Let us assume you set up 10046 (sql trace) for a sql_id with the following command (trsql.sql):

set echo on

alter system set events 'sql_trace [sql:&sql_id_to_10046] level 12';

set echo off

then you decided to force SQL Monitor (with no “PX or 5 seconds rule”) to intercept yet another sql_id and you wanted to cease after the first 50 executions. Assume, you used script trsqlmonNtimes.sql) :

set echo on

alter system set events 'sql_monitor [SQL: &sql_id_to_monitor] {occurence: start_after &start_after , end_after &end_after} force true' ;

set echo off

So, basically your entire session looked like this :

16:32:19 SYS @ MYDB2PLAY> @trsql
16:32:21 SYS @ MYDB2PLAY> alter system set events 'sql_trace [sql:&sql_id_to_10046] level 12';
Enter value for sql_id_to_10046: d2j2dkfpc3p39
old   1: alter system set events 'sql_trace [sql:&sql_id_to_10046] level 12'
new   1: alter system set events 'sql_trace [sql:d2j2dkfpc3p39] level 12'

System altered.

16:33:03 SYS @ MYDB2PLAY> @trsqlmonNtimes
16:33:07 SYS @ MYDB2PLAY> alter system set events 'sql_monitor [SQL: &sql_id_to_monitor] {occurence: start_after &start_after , end_after &end_after} force true' ;
Enter value for sql_id_to_monitor: dpc94m2f39th2
Enter value for start_after: 0
Enter value for end_after: 50
old   1: alter system set events 'sql_monitor [SQL: &sql_id_to_monitor] {occurence: start_after &start_after , end_after &end_after} force true'
new   1: alter system set events 'sql_monitor [SQL: dpc94m2f39th2] {occurence: start_after 0 , end_after 50} force true'

System altered.

Then you forgot what traces you set up (not really good, isn’t it..)
In such case this little helper will surely help you with listing all your “traps” :

16:33:23 SYS @ MYDB2PLAY> @lstrall

16:33:24 SYS @ MYDB2PLAY> oradebug setmypid
Statement processed.
16:33:24 SYS @ MYDB2PLAY> oradebug eventdump system
sql_monitor [SQL: dpc94m2f39th2] {occurence: start_after 0 , end_after 50} force true
sql_trace [sql:d2j2dkfpc3p39] level 12

To “untrace” them (it will hit all sql_ids):
alter system set events 'sql_trace off' ;
alter system set events 'sql_monitor off' ;

If you need to “untrace” specific sql_ids:
alter system set events 'sql_trace [sql:&sql_id_to_10046] off' ;
alter system set events 'sql_monitor [SQL: &sql_id_to_monitor] off' ;

Important note: these checks (and traps!) work within just the current instance.

Other sources which are definitely worth browsing

-oradebug as a roll-up-down list
-several brilliant examples of appliances of oradebug
-even broader explanation of oradebug with examples
-classical approach to oradebug (oradebug help, not oradebug doc)
-an overview how that tool evolved from the early stage of event handling to the current even more advanced level (both supported by in-depth examples)

oradebug and RMAN ?

oradebug’s resemblance of “travelling” through RMAN documentation is purely my impression and I hope I haven’t introduced any mess by that.
Let’s get a feeling though :

RMAN> list ;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "all, archivelog, backed, backuppiece, backupset, backup, completed, controlfilecopy, copy, datafilecopy, db_unique_name, device, expired, failure, foreign, global, guid, incarnation, like, proxy, preplugin, recoverable, restore, script, tag"
RMAN-01007: at line 1 column 6 file: standard input


RMAN> list archivelog
2>


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "end-of-file": expecting one of: "all, from, high, like, low, scn, sequence, time, until, double-quoted-string, integer, single-quoted-string"
RMAN-01007: at line 3 column 1 file: standard input


RMAN> list archivelog all
2>

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "end-of-file": expecting one of: "backed, completed, device, for, guid, like, recoverable, tag, ;"

RMAN> list archivelog all ;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name EXDBNAME
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
159671  1    43782   A 10.09.2024 09:05:50
        Name: +RECO/EXDBNAME/ARCHIVELOG/2024_09_10/thread_1_seq_43782.5617.1179307251

159673  1    43783   A 10.09.2024 09:20:51
        Name: +RECO/EXDBNAME/ARCHIVELOG/2024_09_10/thread_1_seq_43783.9875.1179308125

159679  1    43784   A 10.09.2024 09:35:25
        Name: +RECO/EXDBNAME/ARCHIVELOG/2024_09_10/thread_1_seq_43784.4286.1179309027