ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle 系统监控建置三(共三)

Oracle 系统监控建置三(共三)

原创 Oracle 作者：selectshen 时间：2014-10-24 21:19:16 0 删除 编辑

-----------------------------------------------监控分区表
----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活动状态.或都过邮件的形式 

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

0
0
上一篇： Oracle 系统监控建置二(共三)
下一篇： mysql for linux 安装
请登录后发表评论 登录
全部评论
selectshen

注册时间：2014-01-05

  • 博文量
    169
  • 访问量
    1567868

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593