一，設(shè)計(jì)背景
由于所在公司ORACLE數(shù)據(jù)庫(kù)較多，傳統(tǒng)人工監(jiān)控表空間的方式較耗時(shí)，且無法記錄歷史表空間數(shù)據(jù)，無法判斷每日表空間增長(zhǎng)量，在沒有g(shù)ridcontrol/cloudcontrol軟件的情況下，筆者設(shè)計(jì)如下表空間監(jiān)控方案，大家也可以根據(jù)自己的實(shí)際情況對(duì)下面的方案進(jìn)行修改。
二，設(shè)計(jì)思路
通過dblink將來查詢到的表空間數(shù)據(jù)集中匯總到一張表里通過crontab跑定時(shí)任務(wù)從各臺(tái)服務(wù)器獲取表空間使用情況信息。
三，具體實(shí)施步驟
1.所在oracle數(shù)據(jù)庫(kù)ip地址信息（下面為舉例說明具體情況要根據(jù)所在環(huán)境設(shè)置）
2.在tbsmonitor主機(jī)上創(chuàng)建tbsmonitor表空間
create tablespace tbsmonitor datafile '/opt/u01/app/oradata/tbsmonitor/tsmonitor.dbf' size 50M autoextend on;
3.在tbsmonitor和database1/database2/database3上建立tbsmonitor用戶用來做表空間監(jiān)控。
create user tsmonitor identified by I11m8cb default tablespace tsmonitor;
4.為了tbsmonitor用戶賦權(quán)用來查找表空間使用情況。
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文件中加入
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服務(wù)器的話可以略過
10.1.21.2 database110.1.21.3 database210.1.21.4 database3
7.在tbsmonitor主機(jī)設(shè)置dblink，這樣就能通過dblink從被監(jiān)控服務(wù)器遠(yuǎn)程抽取表空間信息。
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表，表空間統(tǒng)計(jì)數(shù)據(jù)將插入這張表。
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中運(yùn)行每日0點(diǎn)1分更新數(shù)據(jù)庫(kù)表空間信息的腳本tbsmonitor.sh（我根據(jù)業(yè)務(wù)需要每日統(tǒng)計(jì)一次，大家也可以通過業(yè)務(wù)要求修改統(tǒng)計(jì)頻率）
1 0 * * * /opt/u01/app/oracle/tbsmonitor.sh
#!/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.comORACLE_SID= tbsmonitorORACLE_BASE=/opt/u01/appORACLE_HOME=/opt/u01/app/oraclePATH=$ORACLE_HOME/bin:$PATH;export PATHexport ORACLE_SID ORACLE_BASE ORACLE_HOMEdate>>/opt/u01/app/oracle/tbsmonitor.shsqlplus 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;EOFecho >> /opt/u01/app/oracle/ tbsmonitor.log
11.創(chuàng)建插入腳本（拿database1舉例，以此類推）
/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腳本如下
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 timeFROM dba_data_files@TO_DATABASE1 df,(SELECT tablespace_name,file_id,SUM(BYTES) BYTES,MAX(BYTES) maxbytesFROM dba_free_space@TO_DATABASE1GROUP BY tablespace_name, file_id) freeWHERE df.tablespace_name = free.tablespace_name(+)AND df.file_id = free.file_id(+)GROUP BY df.tablespace_nameORDER BY 6; 12.查看表空間使用占比可以使用如下語(yǔ)句(如果要查看某臺(tái)機(jī)器可以帶上條件where ipaddress='xxxx' and instance='xxxxx' and to_char(time,'yyyy-mm-dd')='xxxx-xx-xx')
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表空間增長(zhǎng)的情況）
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') bwhere a.tablespace_name=b.tablespace_name and a.IPADDRESS=b.IPADDRESS order by increase descselect * from tbsmonitor. tbsmonitor where ipaddress='10.1.21.2' and to_char(time,'yyyy-mm-dd')='2016-01-08'
