oracle 11g 診斷檔案

標籤：oracle   11g 診斷檔案

show parameter diagnostic_dest;

SQL> show parameter diagnostic_dest

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest              string     /opt/oracle/app

linux命令查看如下：

ll /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

SQL> show parameter dump;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
background_core_dump             string     partial
background_dump_dest             string     /opt/oracle/app/diag/rdbms/orc
                         l/orcl/trace
core_dump_dest                 string     /opt/oracle/app/diag/rdbms/orc
                         l/orcl/cdump
max_dump_file_size             string     unlimited
shadow_core_dump             string     partial
user_dump_dest                 string     /opt/oracle/app/diag/rdbms/orc
                         l/orcl/trace

診斷檔案類型及作用：

[[email protected] ~]$ ll  /opt/oracle/app/diag/rdbms/orcl/orcl/trace/

-rw-r----- 1 oracle oinstall  213614 Aug 24 12:35 alert_orcl.log
-rw-r----- 1 oracle oinstall    5008 Aug 11 22:00 orcl_cjq0_2829.trc
-rw-r----- 1 oracle oinstall     380 Aug 11 22:00 orcl_cjq0_2829.trm
-rw-r----- 1 oracle oinstall    3088 Aug 23 22:00 orcl_cjq0_3527.trc
-rw-r----- 1 oracle oinstall     240 Aug 23 22:00 orcl_cjq0_3527.trm
-rw-r----- 1 oracle oinstall     962 Aug  1 22:27 orcl_ckpt_2582.trc
-rw-r----- 1 oracle oinstall      59 Aug  1 22:27 orcl_ckpt_2582.trm

-rw-r----- 1 oracle oinstall     997 Jul 27 02:00 orcl_vkrm_815.trc
-rw-r----- 1 oracle oinstall      71 Jul 27 02:00 orcl_vkrm_815.trm
-rw-r----- 1 oracle oinstall    1001 Jul 30 02:00 orcl_vkrm_9998.trc
-rw-r----- 1 oracle oinstall      72 Jul 30 02:00 orcl_vkrm_9998.trm
-rw-r----- 1 oracle oinstall    1579 Jul 27 09:54 orcl_vktm_2560.trc
-rw-r----- 1 oracle oinstall     127 Jul 27 09:54 orcl_vktm_2560.trm
-rw-r----- 1 oracle oinstall    1467 Aug 19 00:38 orcl_vktm_3049.trc
-rw-r----- 1 oracle oinstall     105 Aug 19 00:38 orcl_vktm_3049.trm

2：診斷檔案日誌的分類分為兩類： 

1： alterSID.log     -----background trace files （後台進程追蹤檔案）   

2： trace files         -----user trace file （使用者trace 檔案）

alert_<sid>.log
資料庫的啟動、停止
記錄所有非預設值的初始化參數
記錄日誌的切換情況
記錄檢查點的完成情況
記錄資料庫工作時遭遇的錯誤資訊

後台進程的追蹤檔案：
<sid>_進程名字_進程pid.trc
記錄後台進程工作時的狀態資訊和報錯資訊
只與故障診斷相關，與效能無關！
通過警報日誌的報錯資訊概要找到有意義的trc檔案

使用者進程的追蹤檔案：
<sid>_ora_服務進程的系統pid.trc
記錄user process所發出的資訊
可以通過命令截獲user process發出的sql語句
與故障診斷和效能調整都相關

下面實戰

開啟指定使用者的後台跟蹤功能
找到想跟蹤的使用者:
select  sid,serial#,username,machine from v$session where username=‘SCOTT‘

select * from dept;

select sid,serial#,username,machine from v$session where username=‘SCOTT‘;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
MACHINE
----------------------------------------------------------------
    15       23 SCOTT
oracle0.example.com

用系統包開啟跟蹤

exec dbms_system.SET_SQL_TRACE_IN_SESSION(15,23,true);



exec dbms_system.SET_SQL_TRACE_IN_SESSION(15,23,false);


尋找追蹤檔案：
select spid from v$process p,v$session s where p.addr=s.paddr and s.sid=15;
SQL> select spid from v$process p,v$session s where p.addr=s.paddr and s.sid=15;

SPID
------------------------
3629

trace
$ORACLE_SID_ora_pid.trc
orcl_ora_3629.trc

追蹤檔案:
Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3791.trc

使用tkprof程式格式化使用者追蹤檔案
tkprof orcl_ora_3629.trc 1.txt
使用tkprof程式格式化使用者追蹤檔案的時候屏蔽無用的第歸sql
tkprof orcl_ora_3629.trc 1.txt sys=no

cat 1.txt 內容如下：

TKPROF: Release 11.2.0.1.0 - Development on Wed Aug 24 15:59:34 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Trace file: /opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_30360.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 3154rqzb8xudy
Plan Hash: 3383998547
select *
from
 dept


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          9          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          5          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          5         17          0           4

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
      4  TABLE ACCESS FULL DEPT (cr=8 pr=5 pw=0 time=0 us cost=3 size=80 card=4)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          9          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          5          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          5         17          0           4

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.00          0          9          0           3

Misses in library cache during parse: 1
Misses in library cache during execute: 1

    1  user  SQL statements in session.
    3  internal SQL statements in session.
    4  SQL statements in session.
********************************************************************************
Trace file: /opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_30360.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
       3  internal SQL statements in trace file.
       4  SQL statements in trace file.
       2  unique SQL statements in trace file.
      56  lines in trace file.
       0  elapsed seconds in trace file.

本文出自 “梁小明的部落格” 部落格，請務必保留此出處http://7038006.blog.51cto.com/7028006/1842048

oracle   11g 診斷檔案