About Oracle Tracing

Today, it is not uncommon for a typical DBA to use session tracking, SQL tracking, and analysis, and I estimate that the usual methods are Sql_trace, 10046 events, and so on. In addition, if we need to track other sessions, what do we need to do? Everyone estimates that the most familiar is the use of oradebug, at least I like to do so. In fact, Oracle provides a very rich tracking methods, this article will introduce these methods, you can choose their favorite way and skilled use of them.

Trace Directory

First of all, I might be concerned about where the trace files we tracked are located, Oracle 10g and 11g store the trace directory is not the same, Oracle 10g is generally $oracle_base/admin/$ORACLE _sid/udump and $oracle _base/admin/$ORACLE _sid/bdump (often concerned about udump), while 11g introduces a new feature of ADR (Automatic diagnostic Repository), and the trace file is placed in {adr_base}/ The diag/rdbms/{database_name}/$ORACLE _sid/trace.

The Oracle 11g trace directory {Adr_base} is viewed as follows:

Luocs@maa> Show Parameter Diag

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

Diagnostic_dest string/u01/app/oracle

The foreground process of the trace file is directed to the directory specified by the User_dump_dest parameter, and the background process of the trace file uses the directory pointed to by the Background_dump_dest parameter. In either case, Trace's suffix is. trc.

Oracle 10g, we can view the trace directory as follows

Sys@ltb> Show Parameter User_dump_dest

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

User_dump_dest String/u01/app/oracle/admin/ltb/udump

Sys@ltb> Show Parameter Background_dump_dest

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

Background_dump_dest String/u01/app/oracle/admin/ltb/bdump

or get it by checking v$parameter.

Sys@ltb> select name, value from V$parameter where name in (' User_dump_dest ', ' background_dump_dest ');

NAME VALUE

----------------------------------- -----------------------------------------------------------------

Background_dump_dest/u01/app/oracle/admin/ltb/bdump

User_dump_dest/u01/app/oracle/admin/ltb/udump

In Oracle 11g, the trace directory specified by the foreground process and the background process is the same

Sys@maa> select name, value from V$parameter where name in (' User_dump_dest ', ' background_dump_dest ');

NAME VALUE

-------------------- -----------------------------------------------------------------

Background_dump_dest/u01/app/oracle/diag/rdbms/maa/maa/trace

User_dump_dest/u01/app/oracle/diag/rdbms/maa/maa/trace

In addition, if you are looking for the trace file for the current session, you can also use the following script:

sys@ltb> Column Trace New_val T

Sys@ltb> Select C.value | | '/' || D.instance_name | | ' _ora_ ' | |

2 A.spid | | '. TRC ' | |

3 case when e.value be not null Then ' _ ' | | E.value End Trace

4 from V$process A, v$session B, V$parameter C, V$instance D, V$parameter E

5 Where a.addr = B.paddr

6 and B.audsid = Userenv (' SessionID ')

7 and C.name = ' user_dump_dest '

8 and E.name = ' tracefile_identifier '

9/

TRACE

------------------------------------------------------------------