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