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文件的最大值