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

-----------------------------------------------監控分割槽表
----1,
create table CHECK_PARTITION_TABLE
(
 IP                  VARCHAR2(15) not null,
 service          VARCHAR2(10) not null,
 OWNER               VARCHAR2(30) not null,
 TABLE_NAME          VARCHAR2(30) not null,
 PARTITIONING_TYPE   VARCHAR2(7),
 PARTITION_COUNT     NUMBER,
 STATUS              VARCHAR2(8),
 COLUMN_NAME         VARCHAR2(50),
 UPDATE_DATE         VARCHAR2(50),
 REMARKS             CHAR(1),
 DEF_TABLESPACE_NAME VARCHAR2(50)
);

----2.
--手動在oracle db上採集分割槽表資訊,一年一次
create  table temptb_partition_1
    as
    select table_owner,table_name,to_lob(high_value) high_value from dba_tab_partitions a
    where a.table_owner not in ('SYSTEM','SYS','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS ','EXFSYS','DMSYS ',
    'CTXSYS','XDB','ANONYMOUS','ORDPLUGINS','SI_INFORMTN_SCHEMA','ORDSYS','MDSYS','OLAPSYS','MDDATA',
    'SYSMAN','MGMT_VIEW','SCOTT');
 delete   from check_partition_table@lnk_rh10g02 where ip='123.0.0.51';
   commit;
  create table temptb_partition_2 as
  select  '123.0.0.51' ip,c.name ,a.owner,a.table_name,a.partitioning_type,a.partition_count,a.status,b.column_name,
  case when to_date(d.high_value,'yyyymmdd')>sysdate-30 then d.high_value else max_value end high_value,
 case when to_date(d.high_value,'yyyymmdd')>sysdate-30 then '1' else '0' end remarks,
 a.def_tablespace_name
 from dba_part_tables a,dba_part_key_columns b,v$database c,
 (     select table_owner,table_name,
 max(case when regexp_instr(high_value,'^*[12][09][0-9][0-9][01][0-9][0-3][1-9].*$')>0 then
 substr(high_value,regexp_instr(high_value,'^*[12][09][0-9][0-9][01][0-9][0-3][1-9].*$'),8)
 else '19000101' end) high_value,
 max(case when regexp_instr(high_value,'^*[12][09][0-9][0-9][01][0-9][0-3][1-9].*$')>0 then
 substr(high_value,regexp_instr(high_value,'^*[12][09][0-9][0-9][01][0-9][0-3][1-9].*$'),8)
 else to_char(high_value) end) max_value
 from  ( select table_owner,table_name,replace(replace(to_char(high_value),'-',''),'/','') high_value from temptb_partition_1 ) a
 group by table_owner,table_name  ) d
 where a.owner=b.owner and a.table_name=b.name and a.owner=d.table_owner and a.table_name=d.table_name
 and a.owner not in ('SYSTEM','SYS','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS ','EXFSYS','DMSYS ',
 'CTXSYS','XDB','ANONYMOUS','ORDPLUGINS','SI_INFORMTN_SCHEMA','ORDSYS','MDSYS','OLAPSYS','MDDATA',
 'SYSMAN','MGMT_VIEW','SCOTT');
 insert into check_partition_table@lnk_rh10g02
 select * from temptb_partition_2
 commit;
 drop table temptb_partition_1 purge;
 drop table temptb_partition_2 purge;
 
----3,透過採集分割槽表的資訊,可在前臺程式中顯示最近需要新增分割槽的分割槽表,或透過郵件提醒最近需要新增分割槽的分割槽表
 
 
-------------------------------------------------監控會話
----1.
create table CHECK_SESSION
(
 HOST        VARCHAR2(20),
 service     VARCHAR2(10),
 PROGRAM     VARCHAR2(48),
 TERMINAL    VARCHAR2(30),
 SCHEMANAME  VARCHAR2(30),
 STATUS      VARCHAR2(8),
 RECORD_DATE DATE,
 LOGON_TIME  DATE,
 SPID        VARCHAR2(12)
);

create index TIME_CHECK_SESSION_01 on CHECK_SESSION(RECORD_DATE,HOST);

create table check_SESSION_ALL
(
 HOST        VARCHAR2(20),
 service     VARCHAR2(10),  
 SESSION_NUM NUMBER,
 RECORD_DATE DATE
);

create table CHECK_TEMP
(
 HOST        VARCHAR2(13),
 spid         VARCHAR2(6),
 EMPLOY_TIME VARCHAR2(8),
 RECORD_DATE DATE
);
create index RECORD_CHECK_TEMP on CHECK_TEMP (RECORD_DATE,HOST);
----2.
--在linux系統中加入,十分釧採集一次
--crontab -l
*/10 * * * * sh /exp/sys/session.sh
--cat /exp/sys/session.sh
rm /exp/sys/session.sql
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=rh10g01
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=.:$ORACLE_HOME/bin:$PATH:.
/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus / as sysdba << eof
declare n number ;
begin
for h in (select s.PROGRAM,s.TERMINAL,s.SCHEMANAME,s.STATUS,p.spid,s.logon_time
                from V\$session s ,v\$process p
                     WHERE s.TYPE NOT in ('BACKGROUND') and p.addr like s.paddr)
loop
insert into check_session@lnk_rh10g02(host,service,PROGRAM,TERMINAL,SCHEMANAME,STATUS,spid,logon_time,RECORD_DATE)
values ('123.0.0.51','rh10g01',h.PROGRAM,h.TERMINAL,h.SCHEMANAME,h.STATUS,h.spid,h.logon_time,sysdate);
end loop ;
select  count(1) into n from v\$session where type not in ('BACKGROUND');
insert into check_session_all@lnk_rh10g02
(host,service,session_num,record_date)
values ('123.0.0.51','rh10g01',n,sysdate);
end;
/
commit;
exit;
eof
ps -ef |grep oracle |grep LOCAL > /exp/sys/session
awk '{print $2}' /exp/sys/session > /exp/sys/session2
awk '{print $7}' /exp/sys/session > /exp/sys/session7
cat /exp/sys/session2|sed s/^/,\'\/g>/exp/sys/session_2
cat /exp/sys/session7|sed s/^/,\'\/g>/exp/sys/session_7
cat /exp/sys/session_2|sed s/$/\'\/g>/exp/sys/session_22
cat /exp/sys/session_7|sed s/$/\'\/g>/exp/sys/session_77
cat /exp/sys/session2|sed s/^/insert\ into\ check_temp\ values\(\'123\.0\.0\.51\',\'/g>/exp/sys/session_27
cat /exp/sys/session_27|sed s/$/\'\/g>/exp/sys/session27
paste /exp/sys/session27 /exp/sys/session_77 >/exp/sys/session277
cat /exp/sys/session277|sed s/$/\,sysdate\)\;/g >>/exp/sys/session.sql
echo 'exit' >>/exp/sys/session.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
$ORACLE_HOME/bin/sqlplus mns/mns@rh10g02 @/exp/sys/session.sql

----3.透過採集session數量,session的列表,程式活動時間,可在前臺顯示session的變化情況,或郵件活動時間過長的session等異常.

--------------------------------------------------監控CPU,DISK,MEMORY
--1
create table CHECK_CPU
(
 HOST       VARCHAR2(20),
 CHECKTIME  VARCHAR2(20),
 USERUSE    NUMBER(8,4),
 NICE       NUMBER(8,4),
 SYSTEM     NUMBER(8,4),
 IOWAIT     NUMBER(8,4),
 IDLE       NUMBER(8,4),
 RECORDDATE DATE,
 CTIME      DATE default sysdate
);
create index ind_CHECKCPU_01 on CHECK_CPU(RECORDDATE,HOST);

create or replace trigger tg_check_cpu
 before insert on check_cpu for each row
begin
 :new.recorddate := case when to_char(sysdate, 'hh24') = '12' then trunc(sysdate) else trunc(sysdate) - 1 end;
end;

create table CHECK_DISK
(
 HOST       VARCHAR2(20),
 DISKNAME   VARCHAR2(50),
 TSIZE      VARCHAR2(20),
 USED       VARCHAR2(20),
 AVAIL      VARCHAR2(20),
 USE        VARCHAR2(10),
 MOUNTED    VARCHAR2(80),
 RECORDDATE DATE default sysdate
);
create index IND_CHECK_DISK_01 on CHECK_DISK(RECORDDATE,HOST);

create table CHECK_MEM
(
 HOST       VARCHAR2(20),
 MEMTYPE    VARCHAR2(20),
 TOTAL      NUMBER(10),
 USED       NUMBER(10),
 FREE       NUMBER(10),
 RECORDDATE DATE default sysdate
);
create index IND_CHECK_MEM_01 on CHECK_MEM(RECORDDATE,HOST);

--2
--在linux系統中加入
--crontab -l
51  23 * * *  df -h > /exp/sys/disk
51  23 * * *  free -m > /exp/sys/mem
0    0 * * *  iostat -xt 300 144 > /exp/sys/cpudiskam
0   12 * * *  iostat -xt 300 144 > /exp/sys/cpudiskpm
52  23 * * *  sh /exp/sys/check.sh
15  12 * * *  sh /exp/sys/checkcpuam.sh
15  00 * * *  sh /exp/sys/checkcpupm.sh

--cat /exp/sys/check.sh
rm -rf /exp/sys/disk_* /exp/sys/mem_* /exp/sys/cpu_* /exp/sys/disk.sql /exp/sys/mem.sql /exp/sys/cpu.sql
cat /exp/sys/disk|grep -v r|awk '{print $1}' >/exp/sys/disk1
cat /exp/sys/disk|grep -v r|awk '{print $2}' >/exp/sys/disk2
cat /exp/sys/disk|grep -v r|awk '{print $3}' >/exp/sys/disk3
cat /exp/sys/disk|grep -v r|awk '{print $4}' >/exp/sys/disk4
cat /exp/sys/disk|grep -v r|awk '{print $5}' >/exp/sys/disk5
cat /exp/sys/disk|grep -v r|awk '{print $6}' >/exp/sys/disk6
cat /exp/sys/disk2|sed s/^/,\'\/g>/exp/sys/disk_2
cat /exp/sys/disk3|sed s/^/,\'\/g>/exp/sys/disk_3
cat /exp/sys/disk4|sed s/^/,\'\/g>/exp/sys/disk_4
cat /exp/sys/disk5|sed s/^/,\'\/g>/exp/sys/disk_5
cat /exp/sys/disk6|sed s/^/,\'\/g>/exp/sys/disk_6
cat /exp/sys/disk_2|sed s/$/\'\/g>/exp/sys/disk_22
cat /exp/sys/disk_3|sed s/$/\'\/g>/exp/sys/disk_33
cat /exp/sys/disk_4|sed s/$/\'\/g>/exp/sys/disk_44
cat /exp/sys/disk_5|sed s/$/\'\/g>/exp/sys/disk_55
cat /exp/sys/disk_6|sed s/$/\'\/g>/exp/sys/disk_66
cat /exp/sys/disk1|sed s/^/insert\ into\ check_disk\(host,diskname,tsize,used,avail,use,mounted\)\ values\(\'123\.0\.0\.51\',\'/g>/exp/sys/disk_1
cat /exp/sys/disk_1|sed s/$/\'\/g>/exp/sys/disk_11
paste /exp/sys/disk_11 /exp/sys/disk_22 >/exp/sys/disk_21
paste /exp/sys/disk_21 /exp/sys/disk_33 >/exp/sys/disk_31
paste /exp/sys/disk_31 /exp/sys/disk_44 >/exp/sys/disk_41
paste /exp/sys/disk_41 /exp/sys/disk_55 >/exp/sys/disk_51
paste /exp/sys/disk_51 /exp/sys/disk_66 >/exp/sys/disk_61
cat /exp/sys/disk_61|sed s/$/\)\;/g >>/exp/sys/disk.sql

cat /exp/sys/mem|grep -v r|awk '{print $1}' >/exp/sys/mem1
cat /exp/sys/mem|grep -v r|awk '{print $2}' >/exp/sys/mem2
cat /exp/sys/mem|grep -v r|awk '{print $3}' >/exp/sys/mem3
cat /exp/sys/mem|grep -v r|awk '{print $4}' >/exp/sys/mem4
cat /exp/sys/mem2|sed s/^/,\/g>/exp/sys/mem_22
cat /exp/sys/mem3|sed s/^/,\/g>/exp/sys/mem_33
cat /exp/sys/mem4|sed s/^/,\/g>/exp/sys/mem_44
cat /exp/sys/mem1|sed s/^/insert\ into\ check_mem\(host,memtype,total,used,free\)\ values\(\'123\.0\.0\.51\',\'/g>/exp/sys/mem_1
cat /exp/sys/mem_1|sed s/$/\'\/g>/exp/sys/mem_11
paste /exp/sys/mem_11 /exp/sys/mem_22 >/exp/sys/mem_21
paste /exp/sys/mem_21 /exp/sys/mem_33 >/exp/sys/mem_31
paste /exp/sys/mem_31 /exp/sys/mem_44 >/exp/sys/mem_41
cat /exp/sys/mem_41|sed s/$/\)\;/g >>/exp/sys/mem.sql

echo 'exit' >>/exp/sys/mem.sql
echo 'exit' >>/exp/sys/disk.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
$ORACLE_HOME/bin/sqlplus mns/mns@rh10g02 @/exp/sys/mem.sql
$ORACLE_HOME/bin/sqlplus mns/mns@rh10g02 @/exp/sys/disk.sql

--cat /exp/sys/checkcpuam.sh
sysdir="/exp/sys/"
rm -rf /exp/sys/cpuam_* /exp/sys/cpuam2  /exp/sys/cpuam3 /exp/sys/cpuam4 /exp/sys/cpuam5 /exp/sys/cpuam6 /exp/sys/cpuam7  /exp/sys/cpuam.sql
cp /exp/sys/cpudiskam /exp/sys/cpudiskam.bak
i=1
cn=5
cat $sysdir/cpudiskam.bak|while read line
do
       if [ "$i" -eq "$cn" ];
       then
               echo $line|awk '{print $1}' >>$sysdir/cpuam3
               echo $line|awk '{print $2}' >>$sysdir/cpuam4
               echo $line|awk '{print $3}' >>$sysdir/cpuam5
               echo $line|awk '{print $4}' >>$sysdir/cpuam6
               echo $line|awk '{print $6}' >>$sysdir/cpuam7
               cn=$(($cn + 13))
       fi
    i=$(($i+1))
done
cp /exp/sys/cpudiskam /exp/sys/cpudiskam.bak
i=1
cn=3
cat $sysdir/cpudiskam.bak|while read line
do
       if [ "$i" -eq "$cn" ];
       then
               echo $line\'|awk '{print $2}' >>$sysdir/cpuam2
               cn=$(($cn + 13))
       fi
    i=$(($i+1))
done
cat /exp/sys/cpuam3|sed s/^/,\/g>/exp/sys/cpuam_33
cat /exp/sys/cpuam4|sed s/^/,\/g>/exp/sys/cpuam_44
cat /exp/sys/cpuam5|sed s/^/,\/g>/exp/sys/cpuam_55
cat /exp/sys/cpuam6|sed s/^/,\/g>/exp/sys/cpuam_66
cat /exp/sys/cpuam7|sed s/^/,\/g>/exp/sys/cpuam_77
cat /exp/sys/cpuam2|sed s/^/insert\ into\ check_cpu\(HOST,CHECKTIME,USERUSE,NICE,SYSTEM,IOWAIT,IDLE\)\ values\(\'123\.0\.0\.51\',\'/g>/exp/sys/cpuam_11
paste /exp/sys/cpuam_11 /exp/sys/cpuam_33 >/exp/sys/cpuam_31
paste /exp/sys/cpuam_31 /exp/sys/cpuam_44 >/exp/sys/cpuam_41
paste /exp/sys/cpuam_41 /exp/sys/cpuam_55 >/exp/sys/cpuam_51
paste /exp/sys/cpuam_51 /exp/sys/cpuam_66 >/exp/sys/cpuam_61
paste /exp/sys/cpuam_61 /exp/sys/cpuam_77 >/exp/sys/cpuam_71
cat /exp/sys/cpuam_71|sed s/$/\)\;/g >>/exp/sys/cpuam.sql
echo 'exit' >>/exp/sys/cpuam.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
$ORACLE_HOME/bin/sqlplus mns/mns@rh10g02 @/exp/sys/cpuam.sql


--cat /exp/sys/checkcpupm.sh
ssysdir="/exp/sys/"
rm -rf /exp/sys/cpupm_* /exp/sys/cpupm2 /exp/sys/cpupm3 /exp/sys/cpupm4 /exp/sys/cpupm5 /exp/sys/cpupm6 /exp/sys/cpupm7  /exp/sys/cpupm.sql
cp /exp/sys/cpudiskpm /exp/sys/cpudiskpm.bak
i=1
cn=5
cat $sysdir/cpudiskpm.bak|while read line
do
       if [ "$i" -eq "$cn" ];
       then
               echo $line|awk '{print $1}' >>$sysdir/cpupm3
               echo $line|awk '{print $2}' >>$sysdir/cpupm4
               echo $line|awk '{print $3}' >>$sysdir/cpupm5
               echo $line|awk '{print $4}' >>$sysdir/cpupm6
               echo $line|awk '{print $6}' >>$sysdir/cpupm7
               cn=$(($cn + 13))
       fi
    i=$(($i+1))
done
cp /exp/sys/cpudiskpm /exp/sys/cpudiskpm.bak
i=1
cn=3
cat $sysdir/cpudiskpm.bak|while read line
do
       if [ "$i" -eq "$cn" ];
       then
               echo $line\'|awk '{print $2}' >>$sysdir/cpupm2
               cn=$(($cn + 13))
       fi
    i=$(($i+1))
done
cat /exp/sys/cpupm3|sed s/^/,\/g>/exp/sys/cpupm_33
cat /exp/sys/cpupm4|sed s/^/,\/g>/exp/sys/cpupm_44
cat /exp/sys/cpupm5|sed s/^/,\/g>/exp/sys/cpupm_55
cat /exp/sys/cpupm6|sed s/^/,\/g>/exp/sys/cpupm_66
cat /exp/sys/cpupm7|sed s/^/,\/g>/exp/sys/cpupm_77
cat /exp/sys/cpupm2|sed s/^/insert\ into\ check_cpu\(HOST,CHECKTIME,USERUSE,NICE,SYSTEM,IOWAIT,IDLE\)\ values\(\'123\.0\.0\.51\',\'/g>/exp/sys/cpupm_11
paste /exp/sys/cpupm_11 /exp/sys/cpupm_33 >/exp/sys/cpupm_31
paste /exp/sys/cpupm_31 /exp/sys/cpupm_44 >/exp/sys/cpupm_41
paste /exp/sys/cpupm_41 /exp/sys/cpupm_55 >/exp/sys/cpupm_51
paste /exp/sys/cpupm_51 /exp/sys/cpupm_66 >/exp/sys/cpupm_61
paste /exp/sys/cpupm_61 /exp/sys/cpupm_77 >/exp/sys/cpupm_71
cat /exp/sys/cpupm_71|sed s/$/\)\;/g >>/exp/sys/cpupm.sql
echo 'exit' >>/exp/sys/cpupm.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
$ORACLE_HOME/bin/sqlplus mns/mns@rh10g02 @/exp/sys/cpupm.sql

----3.透過採集linux系統的CPU,硬碟,記憶體資訊,可在前臺顯示,最近硬碟使用情況,硬碟使用趨勢,記憶體是否異常,CPU活動狀態.或都過郵件的形式