在Linux系统上同时监控多个Oracle数据库表空间的方法_Oracle

一，设计背景

由于所在公司ORACLE数据库较多，传统人工监控表空间的方式较耗时，且无法记录历史表空间数据，无法判断每日表空间增长量，在没有gridcontrol/cloudcontrol软件的情况下，笔者设计如下表空间监控方案，大家也可以根据自己的实际情况对下面的方案进行修改。
二，设计思路

在Linux系统上同时监控多个Oracle数据库表空间的方法

通过dblink将来查询到的表空间数据集中汇总到一张表里通过crontab跑定时任务从各台服务器获取表空间使用情况信息。
三，具体实施步骤

1.所在oracle数据库ip地址信息（下面为举例说明具体情况要根据所在环境设置）

在Linux系统上同时监控多个Oracle数据库表空间的方法

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文件中加入

				?

									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表，表空间统计数据将插入这张表。

				?

									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

				?

									#!/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脚本如下

				?

									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-01-08'