ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 读书笔记三 监控数据库操作

oracle 读书笔记三 监控数据库操作

原创 Linux操作系统 作者:anchen211 时间:2009-03-20 19:34:20 0 删除 编辑

oracle 读书笔记三 监控数据库操作

ORACLE监控数据库操作通过以下几种途径“
1 服务器端的警告

告警信息存放在SYS用户的ALERT_QUE队列中,OEM负责从此队列中读取信息并展现。这些信息也可以配置发送邮件或纸质信息给相应管理员。
如果某个信息不能写信ALERT_QUE队列,将会写到alert log.

使用API管理服务器端告警
如:设置USERS表空闲空间阈值的一般告警值为10M,严重告警阈值为2M并且取满消空间满(percent-full)阈值

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
   metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE,
   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_LE,
   warning_value           => '10240',
   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_LE,
   critical_value          => '2048',
   observation_period      => 1,
   consecutive_occurrences => 1,
   instance_name           => NULL,
   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
   object_name             => 'USERS');

DBMS_SERVER_ALERT.SET_THRESHOLD(
   metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_GT,
   warning_value           => '0',
   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_GT,
   critical_value          => '0',
   observation_period      => 1,
   consecutive_occurrences => 1,
   instance_name           => NULL,
   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
   object_name             => 'USERS');
END;
/

当用户调用的CPU时间超过8000ms时将发出警告,在用户调用超过10000ms时将发出严重警告
begin
DBMS_SERVER_ALERT.SET_THRESHOLD(
   metrics_id              => DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
   warning_operator        => DBMS_SERVER_ALERT.OPERATOR_LE,
   warning_value           => '8000',
   critical_operator       => DBMS_SERVER_ALERT.OPERATOR_LE,
   critical_value          => '10000',
   observation_period      => 1,
   consecutive_occurrences => 2,
   instance_name           => NULL,
   object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,
   object_name             => 'mystock');
end;
/


恢复表空间告警到默认值
DBMS_SERVER_ALERT.SET_THRESHOLD;


查看阈值设置信息
1
DECLARE
  warning_operator        BINARY_INTEGER;
  warning_value           VARCHAR2(60);
  critical_operator       BINARY_INTEGER;
  critical_value          VARCHAR2(60);
  observation_period      BINARY_INTEGER;
  consecutive_occurrences BINARY_INTEGER;
BEGIN
  DBMS_SERVER_ALERT.GET_THRESHOLD(DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
                                  warning_operator,
                                  warning_value,
                                  critical_operator,
                                  critical_value,
                                  observation_period,
                                  consecutive_occurrences,
                                  null,
                                  DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,
                                  'mystock');
  DBMS_OUTPUT.PUT_LINE('Warning operator: ' || warning_operator);
  DBMS_OUTPUT.PUT_LINE('Warning value: ' || warning_value);
  DBMS_OUTPUT.PUT_LINE('Critical operator: ' || critical_operator);
  DBMS_OUTPUT.PUT_LINE('Critical value: ' || critical_value);
  DBMS_OUTPUT.PUT_LINE('Observation_period: ' || observation_period);
  DBMS_OUTPUT.PUT_LINE('Consecutive occurrences:' ||
                       consecutive_occurrences);
END;

2 查看视图
SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';


额外的API
如果想不依赖于OEM而自己维护告警信息,需要订阅ALERT_QUE队列中的消息。这就需要使用DBMS_AQADM包的CREATE_AQ_AGENT和ADD_SUBSCRIBER存储过程
来进行创建代理并用此代理向ALERT_QUE进行订阅


查看告警数据
下列视图提供服务器的告警信息:
■ DBA_THRESHOLDS lists the threshold settings defined for the instance.
■ DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
■ DBA_ALERT_HISTORY lists a history of alerts that have been cleared.
■ V$ALERT_TYPES provides information such as group and type for each alert.
■ V$METRICNAME contains the names, identifiers, and other information about the system metrics.
■ V$METRIC and V$METRIC_HISTORY views contain system-level metric values in memory.

使用TRACE文件和ALERT文件监控数据库

TRACE文件:进程内部错误的DUMP信息

ALERT 日志保存以下信息
1 内部错误、块损坏信息、死锁错误信息
2 管理操作信息,如CREATE、ALTER、DROP操作语句,STARTUP、SHUTDOWN和ARCHIVELOG等操作;
3 共享服务器和分派器的相关信息或错误;
4 物化视图刷新时的相关错误;
5 例程启动时的一些无默认值参数的启动值


MAX_DUMP_FILE_SIZE:设置TRACE文件的最大值

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9375/viewspace-573260/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-04

  • 博文量
    129
  • 访问量
    176715