Methods for simultaneously monitoring multiple Oracle database table spaces on a Linux system _oracle

First, design background

Because of the company's Oracle database, traditional manual monitoring table space is time-consuming, and can not record the historical table space data, can not judge the daily table space growth, in the absence of Gridcontrol/cloudcontrol software, the author designed the following table space monitoring program, You can also according to their own actual situation of the following plan to modify.
Two, design ideas

Collect the table space usage information from each server by dblink the table space data that will be queried in the future through the crontab running scheduled task.
Third, the concrete implementation steps

1. The IP address information of the Oracle database (below for an example to specify the circumstances to be set according to the environment)

2. Create Tbsmonitor tablespace on Tbsmonitor host

Copy Code code as follows:

Create tablespace tbsmonitor datafile '/opt/u01/app/oradata/tbsmonitor/tsmonitor.dbf ' size 50M autoextend on;

3. Establish Tbsmonitor users on Tbsmonitor and database1/database2/database3 to do table space monitoring.

Create user Tsmonitor identified by I11M8CB default Tablespace tsmonitor;

4. In order to Tbsmonitor user empowerment to find table space usage.

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. Establish a DATABASE1/DATABASE2/DATABASE3 Tnsnames.ora connection on the Tbsmonitor and add it to the Tnsnames.ora file

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. Modify the/etc/hosts file, if you have a DNS server, you can skip

10.1.21.2 database1
10.1.21.3 database2
10.1.21.4 database3

7. Set up the Dblink in the Tbsmonitor host, so that the table space information can be extracted remotely from the monitored server via 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. Set up Tbsmonitor table, table space Statistic data will insert this table.

CREATE TABLE Tbsmonitor.tbsmonitor
(
 ipaddress    VARCHAR2),
 instancename  VARCHAR2 (200),
 tablespace_name VARCHAR2, 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. Run daily 0:1 script to update database table space information in crontab (I need to make a daily statistic according to my business needs, you can also change the statistic frequency by business request)

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 in 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. Create Insert Script (take database1 example, etc.)

/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;

The SQL script is as follows

 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 = f
Ree.tablespace_name (+) and df.file_id = free.file_id (+) GROUP by df.tablespace_name order by 6; 

12. View table space usage ratio you can use the following statement (if you want to see a machine can take a condition 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 "tablespace size (M)",
    USED_MB "Used Space (m)",
    To_char (ROUND (USED_MB)/SIZE_MB *
           2),
        ' 990.99 ') "use ratio",
   free_mb "free Space (m)"
From Tbsmonitor. Tbsmonitor ORDER BY "use ratio" desc

13. To view daily increments, you can use the following script. (shown below is the 4-8-day 10.1.21.2 table space growth)

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 * to 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 '