1. Start
  2. Unternehmen
  3. Blog
  4. Wo ist mein Tracefile?

Wo ist mein Tracefile?

Die Instrumentierung der Oracle Datenbank bietet weitreichende Möglichkeiten zur Analyse des Verhaltens der Datenbank. Das ist hilfreich bei der Untersuchung von Fehlern oder aber auch bei der Performanceanalyse. Die gesammelten Daten landen meist in einem Tracefile. Aber wie finde ich das richtige Tracefile? Seit es das Diagnostic Repository gibt, ist zumindest die Strukturierung einheitlich. Die Tracefiles einer Datenbank befinden sich unter <DIAG_HOME>/rdbms/<DB UNIQUE NAME>/<ORACLE SID/trace. Aber da liegen eine ganze Menge Dateien. Welche ist nun die gewünschte Datei?

Um diese Frage zu beantworten, gibt es mehrere Möglichkeiten. Am einfachsten schaut man nach den neuesten Dateien und versucht, die passende Datei zu finden. Zielführender ist aber, die Datei gezielt zu ermitteln. Das geht beispielsweise mit dem "oradebug" aus dem SQL*Plus heraus. Oradebug ermöglicht es, sich mit einem beliebigen Datenbankprozess zu verbinden. Ist die Verbindung hergestellt, kann man den aktuellen Tracefilenamen ermitteln.

 

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_4019652.trc

 

Aber das geht natürlich auch mit SQL-Mitteln. In der View V$PROCESS finden sich zu allen Prozessen auch deren Tracefilenamen:

 

SQL> select s.username, p.pname, p.tracefile
  2  from v$session s join v$process p on (s.paddr = p.addr)
  3* ;

   USERNAME    PNAME                                                                          TRACEFILE
___________ ________ __________________________________________________________________________________
            PMON     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_pmon_3595147.trc
            CLMN     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_clmn_3595151.trc
            PSP0     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_psp0_3595155.trc
            VKTM     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_vktm_3595309.trc
            GEN0     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_gen0_3595349.trc
            MMAN     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_mman_3595357.trc
SYS                  /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3609620.trc
            GEN1     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_gen1_3595363_3595364.trc
            SCMN     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_scmn_3595363_3595363.trc
            DIAG     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_diag_3595366.trc
SYS         OFSD     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ofsd_3595368_3595369.trc
            SCMN     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_scmn_3595368_3595368.trc
            DBRM     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_dbrm_3595371.trc
            VKRM     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_vkrm_3595377.trc
            SVCB     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_svcb_3595390.trc
            PMAN     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_pman_3595392.trc
            DIA0     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_dia0_3595398.trc
            DBW0     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_dbw0_3595407.trc
            LGWR     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_lgwr_3595417.trc
            CKPT     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ckpt_3595435.trc
            LG00     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_lg00_3595451.trc
            SMON     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_smon_3595462.trc
            LG01     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_lg01_3595466.trc
            SMCO     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_smco_3595468.trc
MPM                  /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3738526.trc
            RECO     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_reco_3595470.trc
            W000     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_w000_3595472.trc
            LREG     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_lreg_3595474.trc
            W001     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_w001_3595483.trc
            PXMN     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_pxmn_3595493.trc
            FENC     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_fenc_3595495.trc
            MMON     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_mmon_3595499.trc
            MMNL     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_mmnl_3595509.trc
            TMON     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_tmon_3595544.trc
            ARC0     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_arc0_3596233.trc
            M000     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_m000_3595876.trc
SYSRAC               /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3595557.trc
            M001     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_m001_3595878.trc
            TT00     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_tt00_3596227.trc
            ARC1     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_arc1_3596247.trc
            TT01     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_tt01_3596241.trc
            ARC2     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_arc2_3596252.trc
            ARC3     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_arc3_3596264.trc
            TT02     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_tt02_3596277.trc
            AQPC     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_aqpc_3596516.trc
            W003     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_w003_3597454.trc
            W002     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_w002_3596735.trc
            CJQ0     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_cjq0_3597713.trc
            W004     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_w004_3598234.trc
            QM02     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_qm02_3598839.trc
            Q001     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_q001_3598863.trc
            CL00     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_cl00_3605386.trc
            Q003     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_q003_3598911.trc
            Q004     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_q004_3598953.trc
            Q005     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_q005_3598980.trc
            M002     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_m002_3601188.trc
            M003     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_m003_3601193.trc
            Q006     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_q006_3601418.trc
            M004     /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_m004_3601367.trc

 

Aber welches davon ist nun das Tracefile meiner Session? Dazu schränkt man die Session per Session Context auf die eigene Session ein:

 

SQL> select s.username, p.pname, p.tracefile
  2  from v$session s join v$process p on (s.paddr = p.addr)
  3* where s.audsid = sys_context('USERENV', 'SESSIONID');

   USERNAME    PNAME                                                                 TRACEFILE
___________ ________ _________________________________________________________________________
MPM                  /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3738526.trc

 

Eine Übersicht über alle möglichen Diagnoseinformationen beinhaltet die View V$DIAG_INFO. Auch der Tracefilename findet sich darin:

 

SQL> select * from v$diag_info;

   INST_ID                     NAME                                                                     VALUE    CON_ID
__________ ________________________ _________________________________________________________________________ _________
         1 Diag Enabled             TRUE                                                                              0
         1 ADR Base                 /u01/app/oracle                                                                   0
         1 ADR Home                 /u01/app/oracle/diag/rdbms/orcl19a/orcl19                                         0
         1 Diag Trace               /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace                                   0
         1 Diag Alert               /u01/app/oracle/diag/rdbms/orcl19a/orcl19/alert                                   0
         1 Diag Incident            /u01/app/oracle/diag/rdbms/orcl19a/orcl19/incident                                0
         1 Diag Cdump               /u01/app/oracle/diag/rdbms/orcl19a/orcl19/cdump                                   0
         1 Health Monitor           /u01/app/oracle/diag/rdbms/orcl19a/orcl19/hm                                      0
         1 Default Trace File       /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3738526.trc            0
         1 Active Problem Count     0                                                                                 0
         1 Active Incident Count    0                                                                                 0
         1 ORACLE_HOME              /u01/app/oracle/product/dbse/19                                                   0

 

Hat man aber das Tracing für eine andere Session aktiviert, z.B. mittels eines Logon-Triggers, dann wird es schwieriger. Hier kann der Parameter TRACEFILE_IDENTIFIER nützlich sein. Denn dessen Wert wird in den Namen des Tracefiles aufgenommen. Somit lassen sich die zugehörigen Tracefiles leicht wiederfinden.

 

SQL> grant execute on dbms_monitor to mpm;

Grant succeeded.

SQL> create or replace trigger mpm.trg_enable_trace
  2  after logon on mpm.schema
  3  begin
  4    execute immediate 'alter session set max_dump_file_size=unlimited';
  5    execute immediate 'alter session set tracefile_identifier=''' || user || '''';
  6    dbms_monitor.session_trace_enable(waits=>true, binds=>true);
  7  end;
  8  /

Trigger TRG_ENABLE_TRACE compiled

 

Verbindet sich der Nutzer nun, entstehen entsprechende Tracefiles, die sich anhand des Wertes von TRACEFILE_IDENTIFIER wiederfinden lassen:

 

[oracle@vm121 ~]$ ll /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/*MPM*
-rw-r-----. 1 oracle dba 15304 Apr 30 11:17 /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3913585_MPM.trc
-rw-r-----. 1 oracle dba  3837 Apr 30 11:17 /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3913585_MPM.trm
-rw-r-----. 1 oracle dba  9216 Apr 30 11:17 /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3913642_MPM.trc
-rw-r-----. 1 oracle dba  2625 Apr 30 11:17 /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3913642_MPM.trm
-rw-r-----. 1 oracle dba  9216 Apr 30 11:17 /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3913695_MPM.trc
-rw-r-----. 1 oracle dba  2630 Apr 30 11:17 /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3913695_MPM.trm

 

Und wie komme ich an den Inhalt der Dateien, wenn ich keinen Zugang zum Server habe? Die entsprechenden Rechte vorausgesetzt, geht das über die Views V$DIAG_TRACE_FILE, um den Namen des Tracefiles zu finden, und V$DIAG_TRACE_FILE_CONTENTS:

 

SQL> select TRACE_FILENAME, CHANGE_TIME, MODIFY_TIME from v$diag_trace_file where TRACE_FILENAME like '%MPM%';

               TRACE_FILENAME                           CHANGE_TIME                           MODIFY_TIME
_____________________________ _____________________________________ _____________________________________
orcl19_ora_3913585_MPM.trc    30.04.24 10:17:12,000000000 +01:00    30.04.24 10:17:12,000000000 +01:00
orcl19_ora_3913642_MPM.trc    30.04.24 10:17:13,000000000 +01:00    30.04.24 10:17:13,000000000 +01:00
orcl19_ora_3913695_MPM.trc    30.04.24 10:17:15,000000000 +01:00    30.04.24 10:17:15,000000000 +01:00
orcl19_ora_3931970_MPM.trc    30.04.24 10:19:22,000000000 +01:00    30.04.24 10:19:22,000000000 +01:00

SQL>  select PAYLOAD from v$diag_trace_file_contents where TRACE_FILENAME='orcl19_ora_3913585_MPM.trc' order by LINE_NUMBER;

PAYLOAD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Trace file /u01/app/oracle/diag/rdbms/orcl19a/orcl19/trace/orcl19_ora_3913585_MPM.trc
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
Build label:    RDBMS_19.23.0.0.0DBRU_LINUX.X64_240405
ORACLE_HOME:    /u01/app/oracle/product/dbse/19
System name:    Linux
Node name:      vm121.support.robotron.de
Release:        5.4.17-2136.310.7.1.el8uek.x86_64
Version:        #2 SMP Wed Aug 17 15:14:08 PDT 2022
Machine:        x86_64
CLID:   P
Instance name: orcl19
Instance number: 1
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 3913585, image: oracle@vm121.support.robotron.de


*** 2024-04-30T11:17:11.075327+02:00 (PDB19A(3))

*** SESSION ID:(399.42907) 2024-04-30T11:17:11.075393+02:00
*** CLIENT ID:() 2024-04-30T11:17:11.075419+02:00
*** SERVICE NAME:(pdb19a) 2024-04-30T11:17:11.075428+02:00
*** MODULE NAME:(java@vm121.support.robotron.de (TNS V1-V3)) 2024-04-30T11:17:11.075437+02:00
*** ACTION NAME:() 2024-04-30T11:17:11.075445+02:00
*** CLIENT DRIVER:(jdbcoci : 19.23.0.0.0) 2024-04-30T11:17:11.075452+02:00
*** CONTAINER ID:(3) 2024-04-30T11:17:11.075461+02:00

=====================
PARSING IN CURSOR #140209629767672 len=224 dep=1 uid=87 oct=47 lid=87 tim=5769725779588 hv=580684217 ad='83cf0240' sqlid='43dqjdhj9t2dt'
begin
  execute immediate 'alter session set max_dump_file_size=unlimited';
  execute immediate 'alter session set tracefile_identifier=''' || user || '''';
  dbms_monitor.session_trace_enable(waits=>true, binds=>true);
end;

END OF STMT
EXEC #140209629767672:c=13313,e=13952,p=2,cr=56,cu=0,mis=1,r=1,dep=1,og=1,plh=0,tim=5769725779575
CLOSE #140209629767672:c=18,e=19,dep=1,type=0,tim=5769725780891

 

Es geht also eine ganze Menge mit Bordmitteln, um an Diagnoseinformationen zu kommen, auch ohne Zugang zum Datenbankserver.

Kommentare

Keine Kommentare

Kommentar schreiben

* Diese Felder sind erforderlich