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

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;