Oracle 系统监控建置二(共三)
-----------------------------------------监控表空间
----1,
create table CHECK_TABLESPACE
(
IP VARCHAR2(20),
SERVICE VARCHAR2(10),
TABLESPACE_NAME VARCHAR2(20),
MEGS_ALLOC NUMBER(8),
MEGS_FREE NUMBER(8),
MEGS_USED NUMBER(8),
PCT_FREE NUMBER(8,2),
PCT_USED NUMBER(8,2),
MAX NUMBER(8),
LEAVING NUMBER(8),
RECORDDATE DATE default sysdate
);
create index IND_CHECK_TABLESPACE_01 on CHECK_TABLESPACE (RECORDDATE,IP);
----2.
--oracle db job中加入,每天採集一次
--interval=sysdate+1
--what:
begin
for v in
(select aa.tablespace_name a1,aa.megs_alloc a2, aa.megs_free a3,aa.megs_used a4,aa.pct_free a5,aa.pct_used a6,aa.max a7 from
(select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
round(maxbytes/1048576,2) Max
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)and a.tablespace_name not in ('SYSTEM','SYSAUX','EXAMPLE','USERS','UNDOTBS1','TEMP' )
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) Pct_Free,
100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) pct_used,
round(f.maxbytes / 1048576, 2) max
from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
and h.tablespace_name not in ('SYSTEM','SYSAUX','EXAMPLE',
'USERS','UNDOTBS1','TEMP' )
group by h.tablespace_name, f.maxbytes
ORDER BY 1) aa) loop
insert into check_tablespace@lnk_rh10g02
values('123.0.0.51','rh10g01',v.a1,v.a2,v.a3,v.a4,v.a5,v.a6,v.a7,v.a7-v.a4,sysdate);
end loop;
end;
----3,通过採集表空间信息,可在前台程序显示最新一次的表空间信息,哪些表空间将不足,表空间的增长情况.或邮件提醒哪些表空间将不足
----------------------------------------监控备援
----1,
create table CHECK_STANDBY
(
HOST VARCHAR2(20),
SERVICE VARCHAR2(20),
PROCESS VARCHAR2(20),
STATUS VARCHAR2(20),
SEQUENCE VARCHAR2(20),
FRIST_TIME VARCHAR2(20),
NEXT_TIME VARCHAR2(20),
APPLIED VARCHAR2(20),
RECORDDATE DATE default sysdate
);
create index IND_CHECK_STANDBY_01 on CHECK_STANDBY(RECORDDATE,HOST);
----2.
--在standby db的linux系统中加入,一天採集两次,此频率可根据备援重要程度来修改
--crontab -l
10,30 6 * * * sh /exp/check/check_standby_rh10g01s.sh
--cat check_standby_rh10g01s.sh
rm /exp/check/rh10g01s/sb_*
. $HOME/.bash_profile
export ORACLE_SID=rh10g01s
if [ $ORACLE_SID != 'rh10g01s' ];
then
exit
fi
/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus -s "/as sysdba" <
set head on
spool /exp/check/rh10g01s/checksb
set linesize 120 pagesize 10000
col name format a50
select a.process,a.status,b.sequence#,b.first_time,b.next_time,b.applied from v\$managed_standby a,v\$archived_log b
where b.sequence# in (select max(sequence#) from v\$archived_log) and process not in ('ARCH','MRP0','RFS');
spool off
exit
EOF
path="/exp/check/rh10g01s"
cat $path/checksb.lst|grep -v r|awk '{print $1}' >$path/sb_check1
cat $path/checksb.lst|grep -v r|awk '{print $2}' >$path/sb_check2
cat $path/checksb.lst|grep -v r|awk '{print $3}' >$path/sb_check3
cat $path/checksb.lst|grep -v r|awk '{print $4}' >$path/sb_check4
cat $path/checksb.lst|grep -v r|awk '{print $5}' >$path/sb_check5
cat $path/checksb.lst|grep -v r|awk '{print $6}' >$path/sb_check6
cat $path/sb_check2|sed s/^/,\'\/g>$path/sb_check_2
cat $path/sb_check3|sed s/^/,\'\/g>$path/sb_check_3
cat $path/sb_check4|sed s/^/,\'\/g>$path/sb_check_4
cat $path/sb_check5|sed s/^/,\'\/g>$path/sb_check_5
cat $path/sb_check6|sed s/^/,\'\/g>$path/sb_check_6
cat $path/sb_check_2|sed s/$/\'\/g>$path/sb_check_22
cat $path/sb_check_3|sed s/$/\'\/g>$path/sb_check_33
cat $path/sb_check_4|sed s/$/\'\/g>$path/sb_check_44
cat $path/sb_check_5|sed s/$/\'\/g>$path/sb_check_55
cat $path/sb_check_6|sed s/$/\'\/g>$path/sb_check_66
cat $path/sb_check1|sed s/^/insert\ into\ check_standby\(host,service,process,status,sequence,frist_time,next_time,applied\)\ values\(\'123\.0\.0\.50\',\'rh1g01s\'\,\'/g>$path/sb_check_1
cat $path/sb_check_1|sed s/$/\'\/g>$path/sb_check_11
paste $path/sb_check_11 $path/sb_check_22 >$path/sb_check_21
paste $path/sb_check_21 $path/sb_check_33 >$path/sb_check_31
paste $path/sb_check_31 $path/sb_check_44 >$path/sb_check_41
paste $path/sb_check_41 $path/sb_check_55 >$path/sb_check_51
paste $path/sb_check_51 $path/sb_check_66 >$path/sb_check_61
cat $path/sb_check_61|sed s/$/\)\;/g >>$path/sb_check.sql
export ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1/"
echo 'exit' >>$path/sb_check.sql
$ORACLE_HOME/bin/sqlplus mns/mns@rh10g02 @$path/sb_check.sql
----3.通过一天採集两次备援的状态信息,可在前台程序中显示备援最近状态,或在收集到备援故障时邮件提醒
----------------------------------------------监控备份
----1,
create table CHECK_BACKUP
(
HOST VARCHAR2(20),
SERVICE VARCHAR2(10),
OPERATION VARCHAR2(20),
STATUS VARCHAR2(30),
START_TIME DATE,
END_TIME DATE,
DATASIZE NUMBER(8),
object_type VARCHAR2(20),
RECORDDATE DATE default sysdate,
BACKTYPE VARCHAR2(10)
);
create index IND_CHECK_BACKUP_01 on CHECK_BACKUP (RECORDDATE,HOST);
----2.
--oracle db job中加入,每小时採集一次
--interval=trunc(sysdate,'HH')+1/24
--what:
begin
for v in (select a.SESSION_RECID a1,a.row_type,a.operation a2,a.status a3,a.start_time a4,a.end_time a5,
round(a.OUTPUT_BYTES / 1024 / 1024, 2) output_bytes,a.object_type from v$rman_status a
where trunc(start_time, 'DD') = trunc(sysdate - 1, 'DD')
and operation='BACKUP'order by start_time ) loop
insert into check_backup@lnk_rh10g02
values('123.0.0.51','rh10g01',v.a2,v.a3,v.a4,v.a5,v.output_bytes,v.object_type,sysdate,'RMAN');
end loop;
end;
----3.通过採集备份信息,可在前台程序中显示备备最近状态,或在收集在备份故障时邮件提醒
--------------------------------------------监控作业
----1,
create table CHECK_JOB
(
HOST VARCHAR2(20),
service VARCHAR2(10),
JOB NUMBER,
LOG_USER VARCHAR2(30),
LAST_DATE DATE,
NEXT_DATE DATE,
TOTAL_TIME NUMBER,
BROKEN VARCHAR2(1),
INTERVAL VARCHAR2(200),
FAILURES NUMBER,
WHAT VARCHAR2(4000),
RECORD_DATE DATE
);
create index INX_CHECK_JOB_01 on CHECK_JOB (RECORD_DATE,HOST);
----2.
--oracle db job中加入,每小时採集一次
--interval=trunc(sysdate,'HH')+1/24
--what:
insert into check_job@lnk_rh10g02
select '123.0.0.51' as host,'rh10g01' service, job,log_user,last_date,next_date,total_time,broken,interval,failures, what,sysdate as record_date
from dba_jobs where schema_user 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');
----3.通过採集job信息,可在前台程序中显示最近失败的作业,或通过邮件提醒最近失败的作业.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28539951/viewspace-1309181/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle 系统监控建置一(共三)
下一篇：
Oracle 系统监控建置三(共三)
请登录后发表评论
登录
全部评论
