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活動狀態.或都過郵件的形式
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1309182/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 系統監控建置二(共三)Oracle
- Oracle 系統監控建置一(共三)Oracle
- PostgreSQL的監控三(zabbix)SQL
- 簡易防火牆建置與流量統計之三(轉)防火牆
- FMCS廠務監控系統，全廠三維視覺化管理視覺化
- 打造雲原生大型分散式監控系統 (三): Thanos 部署與實踐分散式
- Mysql 監控系統MySql
- 監控系統元件元件
- 表空間監控（三）tablespace detailAI
- 資料庫監控指令碼（三）資料庫指令碼
- 實時監控系統，統一監控企業APIAPI
- ORACLE監控系統錯誤日誌過程Oracle
- 監控Oracle系統中鎖的常用指令碼Oracle指令碼
- 加油站三維視覺化監控系統，安全管理智慧運營視覺化
- Mac系統監控工具Mac
- 打造前端監控系統前端
- 手刃前端監控系統前端
- JavaWeb的監控系統JavaWeb
- Cacti 監控 AIX 系統AI
- 智慧工地監控系統
- 智慧影片監控系統
- prometheus之docker監控與告警系列（三）PrometheusDocker
- asp.net core監控—引入Prometheus（三）ASP.NETPrometheus
- 資訊系統中的內控三要素
- 幽默！分散式系統共識演算法的三階段分散式演算法
- Oracle 三個監聽檔案Oracle
- LoadRunner監控Linux的三種方法Linux
- 系統監控&JVM監控指標資料查詢JVM指標
- 運維監控系統 PIGOSS BSM的監控策略運維Go
- Oracle某行系統SQL優化案例(三)OracleSQL優化
- 【kamus】Oracle系統設計兩三言Oracle
- 駕駛員監控系統（DMS）
- python搭建系統監控Python
- sysstat——系統效能監控神器
- Docker 容器監控系統初探Docker
- Prometheus監控報警系統Prometheus
- 直播間截留監控系統
- zabbix系統監控部署（上）