oracle表空间监控实现过程

1. 改被监控机的防火墙

jiankong="XXX.XXX.XX.XXX"
$iptables -A INPUT -p tcp --dport 1521 -s $jiankong -j ACCEPT

 

2. 改监控机上的tns文件

MCARD4 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 60.206.XX.XX)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mcard)
    )
  )
 
注：1. (SERVER = DEDICATED) 这句是指定连接为专用oracle服务进程，有的机器加上才能连接，有的不需要，一般都加上。
    2. 集群的话还要改hosts
 
   
 
3. 在117上建立对各被监控机的DBlink

create database link mcard4 connect to mcard identified by XXXXXXX using 'MCARD4';

create database link GTAVT connect to "gtavatar" identified by "XXXXXX" using 'GTAVT';

注：1. gt为11g  所以要用以上格式，用户名和密码上都要加""
       否则会报：ORA-01017: invalid username/password; logon denied
                 ORA-02063: preceding line from GTAVT
    2. 由于要监控多台机器，一次使用到多个db_link，而oracle默认的open_links=4
       要将该值改大，并且该值不能both修改，要先改spfile，再重启数据库
       alter system set open_links=20 scope=spfile;
       shutdown immediate
       startup
       否则会报：ORA-02020: too many database links in use
      
      
      
4. 查表空占用量的sql语句

建一张存放表空间信息的表tbs_free
create table TBS_FREE (PCT_FREE NUMBER,TABLESPACE_NAME VARCHAR2(20),NAME VARCHAR2(20),CHECK_TIME VARCHAR2(20),IP VARCHAR2(20))

sql文件内容：       
set linesize 1000;      //设置行显示长度，不然打印出来会分行
delete from tbs_free;

insert into tbs_free select round(100-b.free/a.total*100) PCT_USED,a.tablespace_name,c.NAME,to_char(sysdate,'yyyy-mm-dd'),'222.73.121.4~7' from
(select sum(bytes)/1024/1024/1024 free,tablespace_name from dba_free_space@test group by tablespace_name) b,(select sum(bytes)/1024/1024/1024 total,tablespace_name
from dba_data_files@test group by tablespace_name) a,v$database@test c where a.tablespace_name=b.tablespace_name order by PCT_USED;

insert into tbs_free select round(100-b.free/a.total*100) PCT_USED,a.tablespace_name,'mcard4',to_char(sysdate,'yyyy-mm-dd'),'222.73.121.21' from
(select sum(bytes)/1024/1024/1024 free,tablespace_name from dba_free_space@mcard4 group by tablespace_name) b,(select sum(bytes)/1024/1024/1024 total,tablespace_name
from dba_data_files@mcard4 group by tablespace_name) a,v$database@mcard4 c where a.tablespace_name=b.tablespace_name order by PCT_USED;

commit;

select * from tbs_free;

quit

5. 监控脚本

export ORACLE_HOME=/usr/u01/app/product/10.2.0/db_1
export ORACLE_SID=mtest
export ORACLE_BASE=/usr/u01/app
export PATH=/usr/u01/app/product/10.2.0/db_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin
export NLS_LANG=American_America.zhs16gbk                 
free_pct=80                                               
sqlplus member/nyqs8ap @/home/oracle/tbs_free.sql>/home/oracle/tbs_free.txt

sed -n '54,143p' /home/oracle/tbs_free.txt>/home/oracle/tbs_free`date +%Y%m%d`.txt

awk '{print $1}' tbs_free`date +%Y%m%d`.txt>/home/oracle/free.txt    //取出第一域
while read dsk                                                       //dsk作为一个临时变量，数据就是上面取出的第一域的数据
do
if [ $dsk -gt $free_pct ]                                            //如果大于80
then
echo "some tablespaces out of datafile">/home/oracle/tbs_error.txt

else
echo "pct_free ok">/home/oracle/tbs_error.txt

fi
done

log=/home/oracle/tbs_error.txt
errorct=`grep -c out $log`
if [ $errorct -gt 0 ]
then

mutt -s "oracle tablespace is out of datafile!!!!!!!!!!!!!!!!!!!!!!!" -a /home/oracle/tbs_free`date +%Y%m%d`.txt kuja@square.com

else
mutt -s "oracle tablespace is ok~~~~~~" -a /home/oracle/tbs_free`date +%Y%m%d`.txt kuja@square.com

fi

rm -f /home/oracle/tbs_free`date -d "7 days ago" +%Y%m%d`.txt

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

转载于:http://blog.itpub.net/13379967/viewspace-617073/