Oracle警告、跟踪文件(10046、死锁等跟踪)

跟踪文件由各个后台进程生成,警报日志中记录关键操作包括:
    ·所有启动和关闭命令,包括中间命令,如alter database mount
    ·实例的所有内部错误(ORA-600错误,只能报告给Oracle Support解决)
    ·任何检测到的数据文件块损坏情况
    ·任何已经发生的死锁
    ·影响数据库物理结构的所有操作,如创建或重命名数据文件和联机重做日志
    ·调整内部参数值的alter system命令
    ·所有日志开关和日志归档文件

以Oracle死锁为例:

确定警告日志文件的存放目录

SQL>show parameter dump_dest

Oracle警告、跟踪文件(10046、死锁等跟踪)

详细的trc日志如下:

*** 2015-12-15 08:47:31.756
DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock: Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000a000d-0009fdf2 31 745 X 34 817 X
TX-000b0012-00064744 34 817 X 31 745 X session 745: DID 0001-001F-001BA3CC session 817: DID 0001-0022-000E48FD
session 817: DID 0001-0022-000E48FD session 745: DID 0001-001F-001BA3CC Rows waited on:
Session 745: obj - rowid = 0001B049 - AAAbBJAAHAAEN4SAAF
(dictionary objn - 110665, file - 7, block - 1105426, slot - 5)
Session 817: obj - rowid = 0001B049 - AAAbBJAAHAAEN4SAAE
(dictionary objn - 110665, file - 7, block - 1105426, slot - 4) ----- Information for the OTHER waiting sessions -----
Session 817:
sid: 817 ser: 32301 audsid: 7129480 user: 93/LC019999
flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 34 O/S info: user: SYSTEM, term: PERFTESTDB66, ospid: 21068
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: Administrator, term: WANGZG-INSPUR, ospid: 2736:11836
machine: WORKGROUP\WANGZG-INSPUR program: plsqldev.exe
application name: PL/SQL Developer, hash value=1190136663
action name: SQL 窗口 - 新建, hash value=2127054360
current SQL:
update tableName
set name
= 'tkk1215'
where xxxxxxxxxxxxxxx
----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=01yxw4cmbqgg3) -----
update tableName
set name
= 'xyy1215'
where xxxxxxx
===================================================

SQL语句跟踪
1.用户级自我跟踪

alter session set sql_trace=true;

在同一个session中执行SQL语句,将会产生跟踪文件sid_ora_spid.trc(spid为系统进程id):

/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_spid.trc

2.系统级跟踪

    alter system set sql_trace=true scope=memory;

在任何session中执行SQL语句,产生跟踪文件sid_ora_spid.trc(spid为系统进程id):

/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_spid.trc

alter system set sql_trace=false scope=memory;

3.用户级的DBA跟踪

使用Oracle的PL/SQL包DBMS_SYSTEM

(1)从session性能视图中查出用户的sid和serial#.

    select s.username,s.sid,s.serial#,p.spid,s.program
from v$session s,v$process p
where s.paddr = p.addr
and s.username is not null
and p.background is null;

USERNAME                              SID    SERIAL# SPID                     PROGRAM

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

SYSTEM                                 13      35114  9172                     plsqldev.exe

SYSTEM                                248      32295  9225                     plsqldev.exe

SYS                                       14      36202  9111                     sqlplus@gymvm (TNS V1-V3)

(2)跟踪用户会话

exec sys.dbms_system.set_sql_trace_in_session(248, 32295, true);

在任何id为248的session中执行SQL语句,产生跟踪文件sid_ora_spid.trc(spid为系统进程id):

/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_9225.trc

(3)停止跟踪

exec sys.dbms_system.set_sql_trace_in_session(248, 32295, false);

(4) 10046/10053是Oracle提供的内部事件,是对SQL_TRACE的增强

    10046事件可以设置以下四个级别:
    1 - 启用标准的SQL_TRACE功能,等价于sql_trace
    4 - Level 1 加上绑定值(bind values)
    8 - Level 1 + 等待事件跟踪
    12 - Level 1 + Level 4 + Level 8

    alter session set tracefile_identifier = 'tkk0114';
   alter session set events '10046 trace name context forever, level 12';
   alter session set events '10046 trace name context off';

   exec sys.dbms_system.set_ev(9, 437, 10046, 12, 'tkk0114');

     exec sys.dbms_system.set_ev(9, 437, 10046, 0, 'tkk0114');

    ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
    ALTER SESSION SET EVENTS '10053 trace name context off';

如果你用的是其他用户来执行dbms_system,则可以这样:
         sqlplus 'sys/pass as sysdba'
         SQL>grant execute on dbms_system to <your_username>;

4.SQL_TRACE跟踪SQL语句执行情况的内容
    ·解析、执行、取数据的计数

·CPU时间和占用时间

·物理读和逻辑读

·处理行数目

·所解析的用户名

·每次提交和回滚的情况.

5.格式化10046跟踪文件(tkprof)

    使用tkprof程序可以将跟踪文件转化成可以阅读的文本文件,tkprof语法:

tkprof tracefile outputfile [explain=user/password] [table=schema.tablename] 
                                [print=integer] [insert= ] [sys= ] [sort= ] ...

$ tkprof tracefile outputfile

上一篇:c 浮点数


下一篇:C# Mvc中文件下载