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


诊断文件类型及作用:

[oracle@s80sit ~]$ 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,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,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 *fromdeptcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 9 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 5 8 0 4------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 5 17 0 4Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 84Rows 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 STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 9 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 5 8 0 4------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 5 17 0 4Misses in library cache during parse: 1OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 3 0.00 0.00 0 0 0 0Fetch 3 0.00 0.00 0 9 0 3------- ------ -------- ---------- ---------- ---------- ---------- ----------total 7 0.00 0.00 0 9 0 3Misses in library cache during parse: 1Misses 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.trcTrace file compatibility: 11.1.0.7Sort 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.

原文链接:https://www.f2er.com/oracle/212935.html

猜你在找的Oracle相关文章