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 echo on
       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資訊,可在前臺程式中顯示最近失敗的作業,或透過郵件提醒最近失敗的作業.