oracle下获得TRACE文件名称

当数据库发生故障时，DBA首先查看的便是警报日志文件和跟踪文件。每个服务进程和后台进程在运行过程中都会将一些特殊的信息写入到其对应的跟踪文件中，比如当某个进程发现了一些内部错误时就会将这些错误信息记录到它的跟踪文件中，而DBA就可以找到相应的跟踪文件了解具体的错误信息了。
那么我们怎么才可以获得跟踪信息呢?
在oracle11g之前，可以通过如下脚本获取，脚本gettrcname.sql的内容如下：
select a.value||b.symbol||c.instance_name||'_ora_'||d.spid||'.trc' trace_file
from
(select value from v$parameter where name='user_dump_dest') a,
(select substr(value,-6,1) symbol from v$parameter where
name='user_dump_dest') b,
(select instance_name from v$instance) c,
(select spid from v$version s,v$process p,v$mystat m where s.paddr=p.addr
and s.SID=m.SID and m.statistic#=0) d
/

(这是运行在windows和Linux环境下的oracle通用的)

到了oracle11g就提供了一种更为简便的方式获取跟踪文件的名称，oracle11g新引入了
ADR(automatic diagnostic repository)自动诊断库，如今在v$diag_info这个视图中我们
就可以轻松获取到跟踪文件的名称。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25594.trc

这里的'Default Trace File'是缺省的会话跟踪文件名称。

我们进行如下操作来查看跟踪文件：
SQL> alter session set sql_trace=true;
Session altered.
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfileorcl.ora
SQL> ! head /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25594.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25594.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:        i686
Instance name: orcl

而对于后台进程的跟踪文件可以通过v$process视图来查询，在oracle11g中添加了
一个新的字段TRACEFILE用来记录跟踪文件的名称。

SQL> select program,tracefile from v$process;
PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
PSEUDO
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_0.trc
oracle@localhost.localdomain (PMON)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_24547.trc
oracle@localhost.localdomain (VKTM)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_vktm_24549.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (GEN0)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_gen0_24553.trc
oracle@localhost.localdomain (DIAG)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_24555.trc
oracle@localhost.localdomain (DBRM)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbrm_24557.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (PSP0)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_psp0_24559.trc
oracle@localhost.localdomain (DIA0)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dia0_24561.trc
oracle@localhost.localdomain (MMAN)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mman_24563.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (DBW0)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_24565.trc
oracle@localhost.localdomain (LGWR)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_24567.trc
oracle@localhost.localdomain (CKPT)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ckpt_24569.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (SMON)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_24571.trc
oracle@localhost.localdomain (RECO)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_reco_24573.trc
oracle@localhost.localdomain (MMON)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mmon_24576.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (MMNL)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mmnl_24578.trc
oracle@localhost.localdomain (D000)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_d000_24580.trc
oracle@localhost.localdomain (S000)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_s000_24582.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (TNS V1-V3)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26447.trc
oracle@localhost.localdomain (ARC0)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc0_24668.trc
oracle@localhost.localdomain (ARC1)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc1_24686.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (ARC2)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc2_24688.trc
oracle@localhost.localdomain (QMNC)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_qmnc_24690.trc
oracle@localhost.localdomain (ARC3)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_24692.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (CJQ0)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_24706.trc
oracle@localhost.localdomain (VKRM)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_vkrm_24710.trc
oracle@localhost.localdomain (SMCO)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smco_25057.trc

PROGRAM
------------------------------------------------
TRACEFILE
--------------------------------------------------------------------------------
oracle@localhost.localdomain (W000)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_w000_26329.trc
oracle@localhost.localdomain (Q000)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_q000_24729.trc
oracle@localhost.localdomain (Q001)
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_q001_24731.trc

30 rows selected.