Monitorar o crescimento dos bancos de dados de uma organização é uma das tarefas de um DBA. Embora existam várias formas de monitorar o tamanho dos bancos de dados individualmente, eu não abro mão de um email que seja enviado todo mês para a minha caixa de entrada com informações de tamanho e crescimento dos principais bancos de produção Oracle das quais eu monitoro. As informações contidas nesse nesse email nada mais são do que uma página HTML com os nomes dos bancos de dados, tamanhos e taxa de crescimento em relação ao mês anterior. Em relação ao tamanho dos bancos de dados, estão incluídos o tamanho atual dos bancos de dados (físico e lógico), o tamanho dos bancos de dados nos últimos 30 dias (físico e lógico) e o crescimento (em MB e %) nesses últimos 30 dias. Com essas informações em mão, é possível tomas algumas atitudes e fazer uma previsão em relação à infraestrutura caso seja necessário. Vale a pena salientar que o que eu entendo por tamanho lógico de um bancos de dados é a soma em bytes de todos os segmentos (tabelas, índices, lobs, etc.) do mesmo. O tamanho físico se refere à soma do tamanho de todos os datafiles, dos redo log files e dos control files.
Para que esse relatório seja gerado, eu utilizarei um script shell no Linux que irá acessar os bancos de dados diariamente, coletar as informações e gravar em uma tabela. Para centralizar essas informações, irei utilizar o banco de dados de um servidor que será o próprio banco de catálogo de recuperação do RMAN. Recomendo criar um usuário específico no banco de dados para a tabela que irá armazenar as informações dos bancos de dados. O meu caso, irei criar o usuário MONITOR.
Ok, mas como será feita a conexão em cada banco de dados? Nesse caso, irei criar vários Database Links (dblinks) no usuário MONITOR. Cada dblink irá apontar para um banco de dados específico e a rotina shell será responsável em fazer um loop nesses dblinks e coletar a informações de cada banco de dados. Para demonstrar o funcionamento desta rotina, segue abaixo alguns scripts.
Após criar o usuário MONITOR no banco de dados escolhido para ser o centralizador das informações, será necessário criar a tabela que armazenará as informações dos banco de dados como demonstrado abaixo.
SQL> create table rpt_database_size
2 (
3 date_time date,
4 db_name varchar2(9),
5 host_name varchar2(64),
6 database_used_size_mb number,
7 database_free_size_mb number,
8 file_system_size_disk_mb number,
9 log_mode varchar2(12),
10 flashback_on varchar2(18),
11 restore_point number,
12 block_change_tracking_status varchar2(10),
13 block_change_tracking_file varchar2(513)
14 );
Tabela criada.
SQL> alter table rpt_database_size
2 add constraint
3 pk_rpt_database_size primary key (date_time, db_name);
Tabela alterada.
Após criação da tabela acima, o próximo passo será criar os database links (dblinks) para os bancos de dados que serão monitorados. Segue um exemplo.
SQL> select db_link,username,host from user_db_links order by 1;
DB_LINK USERNAME HOST
---------- ------------- ---------------------------------------------------------------------------------------------------------
PRD01 SYSTEM (description = (address = (protocol = tcp) (host = server01)(port = 1521))(connect_data = (sid = PRD01)))
PRD02 SYSTEM (description = (address = (protocol = tcp) (host = server01)(port = 1521))(connect_data = (sid = PRD02)))
PRD03 SYSTEM (description = (address = (protocol = tcp) (host = server02)(port = 1521))(connect_data = (sid = PRD03)))
PRD04 SYSTEM (description = (address = (protocol = tcp) (host = server03)(port = 1521))(connect_data = (sid = PRD04)))
PRD05 SYSTEM (description = (address = (protocol = tcp) (host = server03)(port = 1521))(connect_data = (sid = PRD05)))
PRD06 SYSTEM (description = (address = (protocol = tcp) (host = server03)(port = 1521))(connect_data = (sid = PRD06)))
PRD07 SYSTEM (description = (address = (protocol = tcp) (host = server04)(port = 1521))(connect_data = (sid = PRD07)))
PRD08 SYSTEM (description = (address = (protocol = tcp) (host = server04)(port = 1521))(connect_data = (sid = PRD08)))
PRD09 SYSTEM (description = (address = (protocol = tcp) (host = server05)(port = 1521))(connect_data = (sid = PRD09)))
PRD10 SYSTEM (description = (address = (protocol = tcp) (host = server06)(port = 1521))(connect_data = (sid = PRD10)))
10 linhas selecionadas.Em seguida, segue abaixo o script SQL que será usado no script shell para fazer a coleta diária do tamanho dos bancos de dados listados acima.
$ cat monitor_tamanho_bd.sql
declare
cursor c1 is select db_link from user_db_links order by 1;
v_string_sql_1 varchar2(4000);
v_date_time date := trunc(sysdate);
begin
/* ORACLE */
FOR c1rec in c1 LOOP
v_string_sql_1 := 'INSERT INTO RPT_DATABASE_SIZE
SELECT '''||v_date_time||''' DATE_TIME,
(SELECT name FROM v$database@'||c1rec.db_link||') "DB_NAME",
(SELECT host_name FROM v$instance@'||c1rec.db_link||') "HOST_NAME",
(SELECT ROUND (SUM (bytes) / 1024 / 1024, 2)
AS database_used_size
FROM dba_segments@'||c1rec.db_link||')
"DATABASE_USED_SIZE_MB",
(SELECT ROUND (SUM (bytes) / 1024 / 1024, 2)
AS database_free_size
FROM dba_free_space@'||c1rec.db_link||')
"DATABASE_FREE_SIZE_MB",
(SELECT SUM (total_size) "FILE_SYSTEM_SIZE_DISK"
FROM (SELECT ROUND (SUM (bytes) / 1024 / 1024, 2) AS total_size FROM dba_data_files@'||c1rec.db_link||'
UNION ALL
SELECT ROUND (SUM (bytes) / 1024 / 1024, 2) AS total_size FROM dba_temp_files@'||c1rec.db_link||'
UNION ALL
SELECT ROUND (SUM (block_size * file_size_blks) / 1024 / 1024, 2) AS total_size FROM v$controlfile@'||c1rec.db_link||'
UNION ALL
SELECT ROUND (SUM (bytes / 1024 / 1024), 2) AS total_size FROM v$log@'||c1rec.db_link||' l, v$logfile@'||c1rec.db_link||' f WHERE l.group# = f.group#
UNION ALL
SELECT NVL (ROUND (SUM (bytes / 1024 / 1024), 2), 0) AS total_size FROM v$standby_log@'||c1rec.db_link||' l, v$logfile@'||c1rec.db_link||' f WHERE l.group# = f.group#))
"FILE_SYSTEM_SIZE_DISK_MB",
(SELECT LOG_MODE FROM V$DATABASE@'||c1rec.db_link||') "LOG_MODE",
(SELECT FLASHBACK_ON FROM V$DATABASE@'||c1rec.db_link||') "FLASHBACK_ON",
(SELECT COUNT(*) RESTORE_POINT FROM V$RESTORE_POINT@'||c1rec.db_link||') "RESTORE_POINT",
(SELECT STATUS FROM V$BLOCK_CHANGE_TRACKING@'||c1rec.db_link||') "BLOCK_CHANGE_TRACKING_STATUS",
(SELECT NVL(FILENAME,''NONE'') FROM V$BLOCK_CHANGE_TRACKING@'||c1rec.db_link||') "BLOCK_CHANGE_TRACKING_FILE"
FROM DUAL';
execute immediate v_string_sql_1;
commit;
end loop;
end;
/Segue abaixo o script shell no Linux que será usado para executar o script acima.
$ cat monitor_tamanho_bd.sh
#!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_SID=RCVCAT
export NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'
export NLS_LANG=AMERICAN_AMERICA
sqlplus /nolog <conn monitor/pwdmonitor
@/home/oracle/monitor_tamanho_bd/monitor_tamanho_bd.sql
quit
EOF
O ideal é que esse script shell seja agendado na crontab para ser executado diariamente. No meu caso, eu agendei o script shell acima para ser executado ao final de cada dia às 23:00. Apenas para demonstração, segue abaixo um exemplo dos dados inseridos na tabela RPT_DATABASE_SIZE após a execução do script shell monitor_tamanho_bd.sh.
SQL> select * from rpt_database_size where date_time <= '01/02/2016' order by 1,2;
DATE_TIM DB_NAME HOST_NAME DATABASE_USED_SIZE_MB DATABASE_FREE_SIZE_MB FILE_SYSTEM_SIZE_DISK_MB LOG_MODE FLASHBACK_ON RESTORE_POINT BLOCK_CHAN BLOCK_CHANGE_TRACKING_FILE
-------- --------- ----------------- --------------------- --------------------- ------------------------ ------------ ------------------ ------------- ---------- ----------------------------
28/01/16 PRD01 server01 6296,5 2991,75 9995,63 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD02 server01 16907,81 5404,25 27823,6 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD03 server02 212235,75 34844,44 250624,5 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD04 server03 64790,19 11062,81 78113,78 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD05 server03 3057,94 3007,06 7100,69 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD06 server03 1746,44 2483 5253,6 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD07 server03 1756,44 2898,56 5693,59 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD08 server04 12189,75 18094,13 30990,84 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD09 server05 150922,25 11498,75 165198,44 ARCHIVELOG NO 0 DISABLED NONE
28/01/16 PRD10 server06 1665,63 2532,56 5798,2 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD01 server01 6312,38 2985,5 10005,63 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD02 server01 16885,38 5426,69 27823,6 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD03 server02 213433,56 33796,63 250774,5 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD04 server03 64880,44 11182,56 78323,78 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD05 server03 3035,31 3029,69 7100,69 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD06 server03 1735,88 2503,56 5263,6 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD07 server03 1742,38 2912,63 5693,59 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD08 server04 12175,31 18108,56 30990,84 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD09 server05 151041,5 14199,5 168018,44 ARCHIVELOG NO 0 DISABLED NONE
29/01/16 PRD10 server06 1656,75 2551,44 5808,2 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD01 server01 6287,19 3011,06 10005,63 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD02 server01 16907,06 5454,94 27873,6 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD03 server02 215329,06 37193,13 256066,5 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD04 server03 64893,88 11169,13 78323,78 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD05 server03 3077,88 2987,13 7100,69 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD06 server03 1723,31 2516,13 5263,6 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD07 server03 1747,31 2907,69 5693,59 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD08 server04 12181,13 18102,75 30990,84 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD09 server05 151275,44 14115,56 168168,44 ARCHIVELOG NO 0 DISABLED NONE
30/01/16 PRD10 server06 1680,81 2547,38 5828,2 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD01 server01 6321,25 2977 10005,63 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD02 server01 16972,75 5439,31 27923,6 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD03 server02 218533,94 35008,25 257086,5 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD04 server03 64903,44 11159,56 78323,78 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD05 server03 3088,56 2976,44 7100,69 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD06 server03 1725 2514,44 5263,6 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD07 server03 1754,06 2900,94 5693,59 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD08 server04 12192,5 18091,38 30990,84 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD09 server05 151604,44 13936,56 168318,44 ARCHIVELOG NO 0 DISABLED NONE
31/01/16 PRD10 server06 1691,94 2546,25 5838,2 ARCHIVELOG NO 0 DISABLED NONE
40 linhas selecionadas.Vale a pena salientar que algumas informações gravadas nessa tabela não tem relação com o tamanho do banco, mas que poderá ser utilizada em algum momento no futuro. No mais, o próximo passo é criar o script SQL que será responsável por agrupar as informações dos bancos de dados no formato HTML de forma a mostrar as informações de tamanho e taxa de crescimento dos bancos de dados monitorados, como demonstrado a seguir. Para evitar problemas de visualização do código abaixo, eu troquei os sinais de maior/maior <> por colchetes [].
$ cat rel_crescimento_bd.sql
set echo off
set feedback off
set linesize 500
set trimspool on
set pagesize 10000
alter session set nls_territory='BRAZIL';
alter session set nls_date_format='dd/mm/yyyy';
column title heading ""
column db_name heading "Banco|de Dados" format a8
column used_seg0 heading "Tam. Logico (MB)" format a16
column used_seg30 heading "Tam. Logico|30 dias atras (MB)" format a30
column used_phys0 heading "Tam. Fisico (MB)" format a16
column used_phys30 heading "Tam. Fisico|30 dias atras (MB)" format a30
column mb_month heading "(MB) crescimento|nos ultimos 30 dias" format a30
column grow_month heading "(%) crescimento|nos ultimos 30 dias" format a30
select '[h1][center]RELATORIO DE CRESCIMENTO DE BANCO DE DADOS - '||host_name||' ('||sysdate||')[/center][/h1]' title from v$instance;
select
db_name,
used_seg0,
used_seg30,
used_phys0,
used_phys30,
mb_month,
decode(grow_month,'%','N/A',grow_month) grow_month
from
(
select
x.db_name,
nvl(to_char(x.used_seg0,'FM9G999G990D0099'),'N/A') used_seg0,
nvl(to_char(z.used_seg30,'FM9G999G990D0099'),'N/A') used_seg30,
nvl(to_char(x.used_phys0,'FM9G999G990D0099'),'N/A') used_phys0,
nvl(to_char(z.used_phys30,'FM9G999G990D0099'),'N/A') used_phys30,
nvl(to_char(round(((x.used_seg0-decode(z.used_seg30,0,NULL,z.used_seg30)))),'FM9G999G990D0099'),'N/A') mb_month,
to_char(round(((x.used_seg0/decode(z.used_seg30,0,NULL,z.used_seg30))-1)*100,2),'FM9G999G990D0099')||'%' grow_month
from
(select b.db_name,database_used_size_mb used_seg0,file_system_size_disk_mb used_phys0 from rpt_database_size a, (select db_name,max(date_time) date_time from RPT_DATABASE_SIZE group by db_name) b where a.db_name(+)=b.db_name and a.date_time(+)=b.date_time) x,
(select b.db_name,database_used_size_mb used_seg30,file_system_size_disk_mb used_phys30 from rpt_database_size a, (select db_name,max(date_time)-30 date_time from RPT_DATABASE_SIZE group by db_name) b where a.db_name(+)=b.db_name and a.date_time(+)=b.date_time) z
where
x.db_name=z.db_name
order by 1
);
quitAgora resta apenas criar o script shell que será responsável por executar o script SQL rel_crescimento_bd.sql e enviar o email para o destinatário. Segue abaixo o script shell que fará esse trabalho. No meu caso, ele está configurado para se conectar no banco de dados do catálogo de recuperação do RMAN (RCVCAT). O ideal é que esse script shell seja agendado na crontab para ser executado uma vez por mês. No meu caso, eu prefiro executar no primeiro dia de cada mês pela manhã.
$ cat rel_crescimento_bd.sh
#!/bin/bash
. /home/oracle/.bash_profile
HOST=`hostname -s`
CURRENTPATH=`dirname $0`
SQLQUERYFILE=$CURRENTPATH/rel_crescimento_bd.sql
RESULTADO=$CURRENTPATH/rel_crescimento_bd.html
MSG=$CURRENTPATH/rel_crescimento_bd.msg
echo "From: "$HOST"@server.com
To: dba@intranet.enterprise.com
Subject: Relatorio de acompanhamento de servidor de banco de dados - "$HOST"
Mime-Version: 1.0
Content-Type: text/html" > $MSG
export ORACLE_SID=RCVCAT
sqlplus -s -m "HTML ON ENTMAP OFF" monitor/pwdmonitor @$CURRENTPATH/rel_crescimento_bd.sql > $CURRENTPATH/rel_crescimento_bd.html
if [ -s $RESULTADO ]
then
cat $RESULTADO >> $MSG
sendmail -t < $MSG
fi
exit 0
Após a execução do script shell rel_crescimento_bd.sh, segue abaixo o resultado que estará no email enviado.
Para finalizar, segue o agendamento na crontab que eu aconselho que seja usado para cada script shell.
#Cron para execução da rotina de coleta de tamanhos dos bancos de dados
00 23 * * * /home/oracle/rotinas/monitor_tamanho_bd/monitor_tamanho_bd.sh
#Cron para execução do relatório de crescimento dos bancos de dados
00 08 1 * * /home/oracle/rotinas/relatorios/rel_crescimento_bd.sh