监控Oracle11G R2数据库的操作
监控Oracle11G R2数据库的操作,可以实现如下的目的:
Ø 了解数据库数据库的跟踪日志与警告日志的位置
Ø 启用会话跟踪,对相关数据进行查询与优化
使用跟踪日志与警告日志监控错误
trace file:当进程检查到内部错误时,进程就产生错误信息到trace file中。
每一个服务器进程与后台进程能够写到它相关联的trace 文件中,trace file可以用于优化应用程序与实例。Oracle 11G R2的跟踪文件就是以*.trc的文件存在对应的系统目录中
Alert log:就是按时间先后顺序产生的信息与错误日志,包括如下信息:
Ø 所有的内部错误,包括块损坏与死锁错误
Ø 管理员的操作,如创建,修改,删除表达式,启动,关闭数据库等信息
Ø 关于共享服务器与分发器的功能信息与错误
Ø 当数据库与实例启动时,初始化参数为非默认值时,都要记录下这些非初始化参数
Alert log 以xml形式的文件与txt文件的方式存在于oracle系统对应目录中
周期性的检查实例的警告日志与跟踪文件,了解系统进程是否遇到问题。
控制trace 文件大小
参数max_dump_file_size控制trace文件大小
SQL> show parameter max_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string unlimited
SQL>
控制trace 文件
当oracle数据库写trace文件时,有可能需要控制trace文件的数量与trace信息的类型
当服务器进程遇见严重错误时,跟踪文件记录服务器的错误行为。 设置初始化参数sql_trace,用来描述sql 跟踪设备为执行所有的sql表达式的语句所产生的跟踪信息,并把这些信息写入ADR信息中。
使用select * from V$diag_info,得到如下表格信息
注意:Diag trace 与Default trace file,Diag trace 描述服务器进程与后台进程跟踪文件的目录,Default trace file 描述当前会话启用跟踪后,所产生的跟踪文件。
INST_ID | NAME | VALUE |
1 | Diag Enabled | TRUE |
1 | ADR Base | C:\APP\ADMINISTRATOR |
1 | ADR Home | C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb |
1 | Diag Trace | C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace |
1 | Diag Alert | C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\alert |
1 | Diag Incident | C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\incident |
1 | Diag Cdump | c:\app\Administrator\diag\rdbms\newdb\newdb\cdump |
1 | Health Monitor | C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\hm |
1 | Default Trace File | C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_ora_3844.trc |
1 | Active Problem Count | 0 |
1 | Active Incident Count | 0 |
为共享服务器会话阅读trace 文件
阅读trace文件使用的命令是trcsess 与tkprof命令来阅读。
使用服务器产生的警告监控数据库操作
服务器产生的警告信息是oracle数据库服务器端对即将发生的错误的一个通知,将对如下信息产生警告:
Ø 每秒的物理读
Ø 每秒的用户提交数
Ø sql服务的响应时间
为服务器产生警告设置与取回阈值
使用set_threshold与get_threshold过程为服务器警告值来查看与改变阈值的设定。
查看服务器产生的警告
用oracle的企业管理器来查看服务器产生的警告信息
监控性能
监控死锁
监控等待事件
Oracle数据库的操作常用的视图
V$lock;
dba_lockers
dba_waiters
dba_ddl_locks
dba_dml_locks
dba_lock
v$session_wait
在oracle 11G中提供了跟踪文件,来优化应用程序与实例
1. oracle 11G R2中查看是否启用会话跟踪(即控制trace 文件)
SQL> show parameter sql_trace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_trace boolean FALSE
2. 启用会话跟踪
(注意:如果用户没有启用会话跟踪,在每一个会话中能够查询到default trace file,但实际位置并没有对应的物理文件。)
利用sqlplus 语句,登陆数据库,默认没有启用会话跟踪。
sql> create tabale T as select * from dba_objects
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter session set tracefile_identifier='testtrace02';
Session altered.
SQL> select * from t where object_id <50;
3. 查看跟踪文件的位置(oracle 11G r2中default trace file 就是新产生的trace file 文件位置)
SQL> col name format a30
SQL> col value form a65
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- ------------------------------ -----------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base C:\APP\ADMINISTRATOR
1 ADR Home C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb
1 Diag Trace C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace
1 Diag Alert C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\alert
1 Diag Incident C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\incident
1 Diag Cdump c:\app\Administrator\diag\rdbms\newdb\newdb\cdump
1 Health Monitor C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\hm
1 Default Trace File C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_ora_2632_
testtrace02.trc
INST_ID NAME VALUE
---------- ------------------------------ -----------------------------------------------------------------
1 Active Problem Count 0
1 Active Incident Count 0
11 rows selected.
SQL>
4. 通过命令tkprof查看trace 文件内容
SQL> $tkprof C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_ora_2632_testtrace02.trc c:\app\02.txt
TKPROF: Release 11.2.0.3.0 - Development on Wed May 3 17:25:59 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
SQL>
在文件02.txt 中部分内容摘选
***********************************************************************
select * from T where object_id <50
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 181 0 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 182 0 48
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 12
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
48 48 48 TABLE ACCESS FULL T (cr=181 pr=0 pw=0 time=35 us cost=52 size=414 card=2)
*******************************************************************************
5. 在V$process 视图中查看后台进程的跟踪文件
SQL> col name format a15
SQL> col tracefile format a65
SQL> select pid,pname,program,tracefile from v$process;
PID PNAME PROGRAM TRACEFILE
---------- ----- ---------------------------------------------------------------- -----------------------------------------------------------------
1 PSEUDO C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_ora_0.trc
2 PMON ORACLE.EXE (PMON) C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_pmon_1068
.trc
3 PSP0 ORACLE.EXE (PSP0) C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_psp0_1512
.trc
4 VKTM ORACLE.EXE (VKTM) C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_vktm_896.
trc
5 GEN0 ORACLE.EXE (GEN0) C:\APP\ADMINISTRATOR\diag\rdbms\newdb\newdb\trace\newdb_gen0_1204
在新的版本中如何查看其他用户的信息
6. 使用系统管理员权限执行其他用户执行会话跟踪
1)查看用户会话
SQL> select sid,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
11 1
12 1
SID SERIAL# USERNAME
---------- ---------- ------------------------------
13 1
14 1
15 1
16 1
20 3
21 533 TESTDBA
22 57 NEWDBA
23 51
24 463 NEWDBA
28 3
29 37
SID SERIAL# USERNAME
---------- ---------- ------------------------------
31 1
34 567
35 381 NEWDBA
37 129 SYS
38 959 SYS
40 677 SYS
42 285 SYS
29 rows selected.
2)用操作系统权限用户执行其他用户的会话跟踪
C:\Users\Administrator>set oracle_sid=newdb
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 3 17:49:46 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exec dbms_system.set_sql_trace_in_session(21, 533, true); # 启用
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(21, 533, false); #结束
在客户端执行select语句,在server端产生跟踪文件
SQL> select * from dba_users;
实际测试,在没有启用会话跟踪时,通过select * from v$diag_info, 虽然default trace file路径,但在服务器端并没有存在对应的trc文件,启用跟踪会话后,执行查询语句,服务器端产生对应的跟踪文件。
参考网址:
http://yedward.net/post/140.html
http://chenxy.blog.51cto.com/729966/752054
http://www.cnblogs.com/limengqiang/archive/2013/04/18/OracleTraceOne.html