eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 转载新闻一则--一只小野鸭的超能量 | Blog首页 | 如何重新配置Oracle的EM Database Control »

Oracle 11g新特性:跟踪文件名称的确定

在Oracle Database 11g之前,要想获得跟踪文件的名称,通常我们需要执行一系列的查询,常用的脚本如下:

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$session s, v$process p, v$mystat m
         WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
/

但是仍然有很多朋友会不断询问我关于这个脚本的情况,可见这个方法是有点曲折了。
在Oracle Database 11g中,Oracle以更简便的方式提供了跟踪文件的名称,这依赖于ADR(Automatic Diagnostic Repository)的引入,现在在v$diag_info视图中有这样一行数据:


SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc


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

SQL> alter session set sql_trace=true;

Session altered.

SQL> select count(*) from dba_users;

COUNT(*)
----------
10

SQL> alter session set sql_trace=false;

Session altered.

SQL> ! head -20 /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc
Trace file /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/11.1.0
System name: Linux
Node name: test126.hurray.com.cn
Release: 2.6.18-8.el5xen
Version: #1 SMP Fri Jan 26 14:42:21 EST 2007
Machine: i686
Instance name: eygle
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 13373, image: oracle@test126.hurray.com.cn (TNS V1-V3)


*** 2007-08-28 13:50:42.772
*** SESSION ID:(140.782) 2007-08-28 13:50:42.772
*** CLIENT ID:() 2007-08-28 13:50:42.772
*** SERVICE NAME:(SYS$USERS) 2007-08-28 13:50:42.772
*** MODULE NAME:(sqlplus@test126.hurray.com.cn (TNS V1-V3)) 2007-08-28 13:50:42.772

现在获得跟踪文件的名称就简单得多了。
对于后台进程的跟踪文件名称,可以通过查询v$process视图获得,这个视图新增加了一个字段TRACEFILE用于记录跟踪文件的名称。

其他参考链接:

http://www.eygle.com/archives/2004/12/howto_get_trace_filename.html

SQL> select program,TRACEFILE from v$process;

PROGRAM TRACEFILE
---------------------------------------- ------------------------------------------------------------
PSEUDO /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_0.trc
oracle@test126.hurray.com.cn (PMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_pmon_4033.trc
oracle@test126.hurray.com.cn (VKTM) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_vktm_4035.trc
oracle@test126.hurray.com.cn (DIAG) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_diag_4039.trc
oracle@test126.hurray.com.cn (DBRM) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dbrm_4041.trc
oracle@test126.hurray.com.cn (PSP0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_psp0_4043.trc
oracle@test126.hurray.com.cn (MMAN) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mman_4049.trc
oracle@test126.hurray.com.cn (DIA0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dia0_4047.trc
oracle@test126.hurray.com.cn (DBW0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_dbw0_4051.trc
oracle@test126.hurray.com.cn (LGWR) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_lgwr_4053.trc
oracle@test126.hurray.com.cn (CKPT) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ckpt_4055.trc

PROGRAM TRACEFILE
---------------------------------------- ------------------------------------------------------------
oracle@test126.hurray.com.cn (SMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_smon_4057.trc
oracle@test126.hurray.com.cn (RECO) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_reco_4059.trc
oracle@test126.hurray.com.cn (MMON) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mmon_4061.trc
oracle@test126.hurray.com.cn (MMNL) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_mmnl_4063.trc
oracle@test126.hurray.com.cn (W000) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_w000_8653.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_27976.trc
oracle@test126.hurray.com.cn (TNS V1-V3) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_13373.trc
oracle@test126.hurray.com.cn (CJQ0) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_cjq0_13885.trc
oracle@test126.hurray.com.cn (SMCO) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_smco_4077.trc
oracle@test126.hurray.com.cn (FBDA) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_fbda_4079.trc
oracle@test126.hurray.com.cn (QMNC) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_qmnc_4081.trc

PROGRAM TRACEFILE
---------------------------------------- ------------------------------------------------------------
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_27935.trc
oracle@test126.hurray.com.cn (q000) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_q000_4097.trc
oracle@test126.hurray.com.cn (q001) /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_q001_4101.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28014.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28363.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28418.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28382.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28380.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28422.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28463.trc
oracle@test126.hurray.com.cn /opt/oracle/diag/rdbms/eygle/eygle/trace/eygle_ora_28467.trc

33 rows selected.

-The End-


历史上的今天...
    >> 2012-09-03文章:
    >> 2010-09-03文章:
    >> 2009-09-03文章:
    >> 2008-09-03文章:
    >> 2005-09-03文章:

无觅

By eygle on 2007-09-03 14:06 | Comments (5) | Oracle12c/11g | 1579 |

5 Comments

其实也可以使用控制文件tracefile_identifier 参数来确定跟踪文件的名称,例如:
参数tracefile_identifie可以用来控制生成在user_dump_dest 目录下的trace文件名称

SQL> alter session set sql_trace=true ;

Session altered.

SQL> alter session set tracefile_identifier ='DEBUG';

Session altered.

在user_dump_dest 目录下的trace文件名称定义为:

INSTANCE_PID_ora_TRACEID.trc , 其中INSTANCE为ORACLE的实例名,PID为操作系统的进程ID(V$PROCESS.OSPID),TRACEID就是设置的tracefile_identifie值。

en,这也是一个好方法:)

Here is the original one, 我忘记了原文链接, 希望原作者见谅. :)

10g and upward.

There is an undocumented alter session command that allows you to control the name of the trace file written to user_dump_dest when tracing a session. It can also be used to create multiple tracefiles from the same session.

The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc
Where INSTANCE is the name of the Oracle instance, PID is the operating system process ID (V$PROCESS.OSPID) and TRACEID is a character string of your choosing.

ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=GARRY;

Use V$PROCESS.TRACEID to check the setting.

SQL> select username, traceid from v$process

USERNAME TRACEID
--------------- ----------
oracle GARRY

The file created in user_dump_dest would be named
orcl_ora_12586_GARRY.trc

还是我比较懒,做试验的时候,我一般将trace按时间排序,找最新的,哈哈^_^

跟踪其他的会话,情况是怎样呢?


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com