多台ORACLE数据库表空间监控方案
亿网一，设计背景
由于所在公司ORACLE数据库较多，传统人工监控表空间的方式较耗时，且无法记录历史表空间数据，无法判断每日表空间增长量，在没有gridcontrol/cloudcontrol软件的情况下，笔者设计如下表空间监控方案，大家也可以根据自己的实际情况对下面的方案进行修改。
二，设计思路
通过dblink将来查询到的表空间数据集中汇总到一张表里通过crontab跑定时任务从各台服务器获取表空间使用情况信息。
三，具体实施步骤
1.所在oracle数据库ip地址信息（下面为举例说明具体情况要根据所在环境设置）
| hostname | 备注 | ipaddress | tnsname | instancename |
| tbsmonitor | 监控数据库 | 10.1.21.1 | tbsmonitor | tbsmonitor |
| database1 | 被监控数据库 | 10.1.21.2 | database1 | database1 |
| database2 | 被监控数据库 | 10.1.21.3 | database2 | database2 |
| database3 | 被监控数据库 | 10.1.21.4 | database3 | database3 |
2.在tbsmonitor主机上创建tbsmonitor表空间
| 1 | create tablespace tbsmonitor datafile ‘/opt/u01/app/oradata/tbsmonitor/tsmonitor.dbf’ size 50M autoextend on ; |
3.在tbsmonitor和database1/database2/database3上建立tbsmonitor用户用来做表空间监控。
| 1 | create user tsmonitor identified by I11m8cb default tablespace tsmonitor ; |
4.为了tbsmonitor用户赋权用来查找表空间使用情况。
| 1 2 3 4 5 | grant resource to tbsmonitor ; grant create session to tbsmonitor ; grant create table to tbsmonitor ; grant select on dba_data_files to tbsmonitor ; grant select on dba_free_space to tbsmonitor ; |
5.在tbsmonitor上建立database1/ database2/ database3的tnsnames.ora连接，在tnsnames.ora文件中加入
| 1 2 3 4 5 6 7 8 9 10 11 12 | DATABASE1 = ( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 10.1.21.1 ) ( PORT = 1521 ) ) ( CONNECT_DATA = ( SID = database1 ) ) ) DATABASE2 = ( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 10.1.21.2 ) ( PORT = 1521 ) ) ( CONNECT_DATA = ( SID = database2 ) ) ) DATABASE3 = ( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 10.1.21.3 ) ( PORT = 1521 ) ) ( CONNECT_DATA = ( SID = database3 ) ) ) |
6.修改/etc/hosts文件，如果有dns服务器的话可以略过
| 1 2 3 | 10.1.21.2 database1 10.1.21.3 database2 10.1.21.4 database3 |
7.在tbsmonitor主机设置dblink，这样就能通过dblink从被监控服务器远程抽取表空间信息。
| 1 2 3 4 5 6 7 8 9 | create database link TO _DATABASE1 connect to TSMONITOR identified by I11m08cb using ‘DATABASE1’ ; create database link TO _DATABASE2 connect to TSMONITOR identified by I11m08cb using ‘DATABASE2’ ; create database link TO _DATABASE3 connect to TSMONITOR identified by I11m08cb using ‘DATABASE3’ ; |
8.建立tbsmonitor表，表空间统计数据将插入这张表。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | create table tbsmonitor . tbsmonitor ( ipaddress VARCHAR2 ( 200 ) , instancename VARCHAR2 ( 200 ) , tablespace_name VARCHAR2 ( 200 ) , datafile _count NUMBER , size _mb NUMBER , free _mb NUMBER , used _mb NUMBER , maxfree NUMBER , pct _used NUMBER , pct _free NUMBER , time DATE ) tablespace tbsmonitor ; |
9. 在crontab中运行每日0点1分更新数据库表空间信息的脚本tbsmonitor.sh（我根据业务需要每日统计一次，大家也可以通过业务要求修改统计频率）
| 1 | 1 0 * * * / opt / u01 / app / oracle / tbsmonitor . sh |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | #!/bin/bash #FileName: tbsmonitor.sh #CreateDate:2016-01-1 #version:1.0 #Discription:take the basic information to insert into the table tbs_usage # Author:FUZHOU HOT #Email:15980219172@139.com ORACLE_SID = tbsmonitor ORACLE_BASE = / opt / u01 / app ORACLE_HOME = / opt / u01 / app / oracle PATH = $ ORACLE_HOME / bin : $ PATH ; export PATH export ORACLE_SID ORACLE_BASE ORACLE_HOME date >> / opt / u01 / app / oracle / tbsmonitor . sh sqlplus sys / I11m08cb as sysdba << EOF >> / opt / u01 / app / oracle / tbsmonitor . log 2 > & 1 @ / opt / u01 / app / oracle / tbsmonitor / tbsmonitor . sql ; @ / opt / u01 / app / oracle / tbsmonitor / database1 . sql ; @ / opt / u01 / app / oracle / tbsmonitor / database2 . sql ; @ / opt / u01 / app / oracle / tbsmonitor / database3 . sql ; EOF echo >> / opt / u01 / app / oracle / tbsmonitor . log |
11.创建插入脚本（拿database1举例，以此类推）
| 1 2 3 | / opt / u01 / app / oracle / tbsmonitor / database1 . sql ; / opt / u01 / app / oracle / tbsmonitor / database2 . sql ; / opt / u01 / app / oracle / tbsmonitor / database3 . sql ; / opt / u01 / app / oracle / tbsmonitor / tbsmonitor . sql ; |
Sql脚本如下
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | insert into tsmonitor . tbsmonitor SELECT utl_inaddr . get_host_address ( ‘DATABASE1’ ) ipaddress , ( select instance_name from v $ instance ) instancename , df . tablespace_name , COUNT ( * ) datafile_count , ROUND ( SUM ( df . BYTES ) / 1048576 ) size_mb , ROUND ( SUM ( free . BYTES ) / 1048576 , 2 ) free_mb , ROUND ( SUM ( df . BYTES ) / 1048576 – SUM ( free . BYTES ) / 1048576 , 2 ) used_mb , ROUND ( MAX ( free . maxbytes ) / 1048576 , 2 ) maxfree , 100 – ROUND ( 100.0 * SUM ( free . BYTES ) / SUM ( df . BYTES ) , 2 ) pct_used , ROUND ( 100.0 * SUM ( free . BYTES ) / SUM ( df . BYTES ) , 2 ) pct_free , sysdate time FROM dba_data_files @ TO _DATABASE1 df , ( SELECT tablespace_name , file_id , SUM ( BYTES ) BYTES , MAX ( BYTES ) maxbytes FROM dba_free_space @ TO_DATABASE1 GROUP BY tablespace_name , file_id ) free WHERE df . tablespace_name = free . tablespace_name ( + ) AND df . file_id = free . file_id ( + ) GROUP BY df . tablespace_name ORDER BY 6 ; |
12.查看表空间使用占比可以使用如下语句(如果要查看某台机器可以带上条件where ipaddress=’xxxx’ and instance=’xxxxx’ and to_char(time,’yyyy-mm-dd’)=’xxxx-xx-xx’)
| 1 2 3 4 5 6 7 8 9 10 11 | SELECT IPADDRESS , Instancename , tablespace_name , datafile_count , size _mb “表空间大小(M)” , used _mb “已使用空间(M)” , TO_CHAR ( ROUND ( ( used_mb ) / size_mb * 100 , 2 ) , ‘990.99’ ) “使用比” , free _mb “空闲空间(M)” FROM tbsmonitor . tbsmonitor order by “使用比” desc |
13.查看每日增量可以使用如下脚本。（下面显示的是4-8日10.1.21.2表空间增长的情况）
| 1 2 3 4 5 | select a . tablespace_name , ( b . used_mb – a . used_mb ) increase , a . ipaddress from ( select * from tsmonitor . tbs_usage where to_char ( time , ‘yyyy-mm-dd’ ) = ‘2016-01-04’ ) a , ( select * from tsmonitor . tbs_usage where to_char ( time , ‘yyyy-mm-dd’ ) = ‘2016-01-08’ ) b where a . tablespace_name = b . tablespace_name and a . IPADDRESS = b . IPADDRESS order by increase desc select * from tbsmonitor . tbsmonitor where ipaddress = ‘10.1.21.2’ and to_char ( time , ‘yyyy-mm-dd’ ) = ‘2016-01-08’ |
文章转载来自：trustauth.cn