一，設計背景
由於所在公司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表空間
|
|
create tablespace tbsmonitor datafile '/opt/u01/app/oradata/tbsmonitor/tsmonitor.dbf' size 50M autoextend on;
|
3.在tbsmonitor和database1/database2/database3上建立tbsmonitor使用者用來做表空間監控。
|
|
create user tsmonitor identified by I11m8cb default tablespace tsmonitor;
|
4.為了tbsmonitor使用者賦權用來查詢表空間使用情況。
|
|
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伺服器的話可以略過
|
|
10.1.21.2 database1
10.1.21.3 database2
10.1.21.4 database3
|
7.在tbsmonitor主機設定dblink，這樣就能透過dblink從被監控伺服器遠端抽取表空間資訊。
|
|
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 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舉例，以此類推）
|
|
/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’)
|
|
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表空間增長的情況）
|
|
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-
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15498/viewspace-1978486/，如需轉載，請註明出處，否則將追究法律責任。