Oracle 系統監控建置
--參考了一些網上的資料,本文講述如何建置一個基本的oracle資料庫監控系統.適用於中小公司對多臺資料庫的整合監控.
--所謂監控系統也就是對資料庫中系統資料的採集,以達到監控的目的.可以透過前臺程式顯示,也可以透過郵件的方式提醒.
--監控的物件有oracle:db 狀態,監聽狀態,top 5,表空間,備援,備份,作業,分割槽表,會話
-------------- os:cpu ,硬碟,記憶體

--假設oracle version 10.2.0.1 64bit,os version rhel 5.6 x64
----監控db,ip=123.0.0.52 instance_name=rh10g02  schema=mns passwd=mns
----被監控db,ip=123.0.0.51 instance_name=rh10g01  schemea=system passwd=system
----被監控standby db,ip=123.0.0.50 instance_name=rh10g01s  schemea=system passwd=system
----以下  "1."開頭指在監控系統資料庫上建置 "2."開頭指在被監控資料庫上建置  "3."開頭指可實現功能的說明

---------------------------------------------基礎表:
---1.
create table CHECK_DBINFO          --DB列表
(
  HOST       VARCHAR2(20),       --IP
  INSTANCE_NAME VARCHAR2(20),
  database_role varchar2(20),       --區分是primary還是standby
  is_backup    varchar2(1)       --標示是否備份
);

------------------------------------------監控DB狀態,監聽狀態
----1,
create table CHECK_DB_STATUS --db狀態
(
  RECORDDATE DATE,
  HOST       VARCHAR2(20),
  INSTANCE_NAME VARCHAR2(20),
  STATUS       VARCHAR2(20)  --db狀態 open/mount/unmount
);

create index IDX_CHECK_DB_STATUS on CHECK_DB_STATUS (RECORDDATE,HOST);

--DB監聽的狀態
create table CHECK_DBLSNR_STATUS --監聽狀態
(
  RECORDDATE DATE,
  HOST    VARCHAR2(20),
  STATUS    VARCHAR2(1) --1代表正常,0代表異常
);

create index IDX_CHECK_DBLSNR_STATUS_01 on CHECK_DBLSNR_STATUS (RECORDDATE, HOST);

----2.
--linux系統中加入,每10分鐘採集一次
--crontab -l
*/10  * * * * sh /exp/check/checkstatus.sh
--cat /exp/check/checkstatus.sh
#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH:.
export ORACLE_SID=rh10g01

#check listener
/dev/null > /exp/check/checklsnrctl.sql

lsn=`lsnrctl status|grep -i error|wc -l`

if [ $lsn != '0' ] ;then
      lsnstat='0'
else
      lsnstat='1'
fi

echo "insert into check_dblsnr_status select sysdate,'123.0.0.51','$lsnstat' from dual;" >>  /exp/check/checklsnrctl.sql
echo 'exit' >>  /exp/check/checklsnrctl.sql
$ORACLE_HOME/bin/sqlplus mns/mns@rh10g02  @/exp/check/checklsnrctl.sql

#check database status
$ORACLE_HOME/bin/sqlplus -s "/as sysdba" <       declare v_dbstat varchar(20);
      begin
      select status into v_dbstat from v\$instance;

      insert into check_db_status@lnk_rh10g02 select sysdate,'123.0.0.51','rh10g01',v_dbstat from dual;
      end;
      /
exit
EOF

------3.收集DB狀態,監聽狀態,可以透過前臺程式顯示最近一天DB的狀態,監聽的狀態,或在異常時以郵件的形式傳送資訊.

-----------------------------------------監控DB time /TOP 5
----1,
create table CHECK_DBTIME
(
  HOST       VARCHAR2(20),
  INSTANCE_NAME VARCHAR2(20),
  b_snap_id integer,
  e_snamp_id integer,
  b_time date,
  e_time date,
  db_time integer,
  db_cpu integer
);
create index idx_CHECK_DBTIME_01 on CHECK_DBTIME(b_time,e_time,HOST);
create table CHECK_DBTOP
(
  HOST       VARCHAR2(20),
  INSTANCE_NAME VARCHAR2(20),
  b_snap_id integer,
  e_snamp_id integer,
  b_time date,
  e_time date,
  event varchar2(50),
  waits integer,
  time integer,
  pctwtt integer,
  wait_class varchar2(50)
);
create index idx_CHECK_DBTOP_01 on CHECK_DBTOP(b_time,e_time,HOST);
----2,
--oracle db job中加入,每小時採集一次
--interval=sysdate+1/24
--what:
declare
   v_dbid integer;
   v_b_snap_id integer;
   v_e_snap_id integer;
   v_b_time date;
   v_e_time date;
   v_db_time integer;
   v_db_cpu integer;
begin
   SELECT b.dbid,b.snap_id ,e.snap_id,trunc(sysdate,'HH')-1/24,trunc(sysdate,'HH')
   into v_dbid,v_b_snap_id,v_e_snap_id,v_b_time,v_e_time
   FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
   where b.dbid=e.dbid and
   trunc(sysdate,'HH')-1/24 between b.begin_interval_time and b.end_interval_time
   and trunc(sysdate,'HH') between e.begin_interval_time and e.end_interval_time;

   SELECT sum(case when snap_id=v_e_snap_id then VALUE else -value end)/1000000 into v_db_time
   FROM DBA_HIST_SYS_TIME_MODEL
   WHERE SNAP_ID       in( v_b_snap_id,v_e_snap_id)
AND DBID          = v_dbid
AND INSTANCE_NUMBER = 1
and stat_name in ('DB time');

SELECT sum(case when snap_id=v_e_snap_id then VALUE else -value end)/1000000 into v_db_cpu
   FROM DBA_HIST_SYS_TIME_MODEL
   WHERE SNAP_ID       in( v_b_snap_id,v_e_snap_id)
AND DBID          = v_dbid
AND INSTANCE_NUMBER = 1
and stat_name in ('DB CPU');

insert into check_dbtime@lnk_rh10g02
values('123.0.0.51','rh10g01',v_b_snap_id,v_e_snap_id,v_b_time,v_e_time,v_db_time,v_db_cpu);
insert into  check_dbtop@lnk_rh10g02
SELECT '123.0.0.51','rh10g01',v_b_snap_id,v_e_snap_id,v_b_time,v_e_time,
   EVENT, WAITS, TIME, DECODE(WAITS, NULL, TO_NUMBER(NULL), 0, TO_NUMBER(NULL),TIME/WAITS*1000) AVGWT,
   PCTWTT, WAIT_CLASS
   FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
         FROM (SELECT E.EVENT_NAME                         EVENT,
                        E.TOTAL_WAITS - NVL(B.TOTAL_WAITS,0) WAITS,
                        (E.TIME_WAITED_MICRO -
                        NVL(B.TIME_WAITED_MICRO,0)) / 1000000 TIME,
                        100 * (E.TIME_WAITED_MICRO -
                                    NVL(B.TIME_WAITED_MICRO,0))/ 1000000 / v_db_time PCTWTT,
                        E.WAIT_CLASS                         WAIT_CLASS
                  FROM DBA_HIST_SYSTEM_EVENT B,
                     DBA_HIST_SYSTEM_EVENT E
                  WHERE B.SNAP_ID(+)       = v_b_snap_id
                  AND E.SNAP_ID          = v_e_snap_id
                  AND B.DBID(+)          = v_dbid
                  AND E.DBID             = v_dbid
                  AND B.INSTANCE_NUMBER(+)  = 1
                  AND E.INSTANCE_NUMBER    = 1
                  AND B.EVENT_ID(+)       = E.EVENT_ID
                  AND E.TOTAL_WAITS       > NVL(B.TOTAL_WAITS,0)
                  AND E.WAIT_CLASS       != 'Idle'
               UNION ALL
               SELECT 'CPU time'                            EVENT,
                        TO_NUMBER(NULL)                      WAITS,
                        v_db_cpu    TIME,
                        100 * v_db_cpu/ v_db_time  PCTWTT,
                        NULL                                  WAIT_CLASS
                  FROM DUAL
                  WHERE v_db_cpu    > 0)
         ORDER BY TIME DESC, WAITS DESC)
   WHERE ROWNUM <= 5;
end;

Oracle 系統監控建置一(共三)

相關文章