Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


terça-feira, 1 de março de 2016

Monitoramento do tamanho e da taxa de crescimento dos bancos de dados Oracle

Por Eduardo Legatti

Olá,

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

quit


Agora 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

Google+

4 comentários:

Diego Fernandes disse...

Bom dia Eduardo!

Gostaria de lhe agradecer pelos conhecimento que você vem passando,
eu sou novo em administração de BD, meu mundo ainda e cheio de duvidas, mas muitos deles
são esclarecido pelo seu blog que acompanho já tem algum tempo. Parabéns pelo seu trabalho
a forma como você tem dedicação em passar seus conhecimento. Espero por em pratica tudo que
você tem passado para poder ter um melhor aprendizado e uma carreira mais promissora.

Eduardo Legatti disse...

Olá Diego,

Obrigado pela visita e pelo comentário! Tenho certeza que você vai se sair bem nessa nova jornada. Sucesso pra você!!

Abraços,

Legatti

Airton Roos disse...

Boa tarde,

Muito legal as informações que você passou, com certeza vai ajudar bastante no dia a dia, parabéns pela iniciativa e por compartilhar informações.

No exemplo que você passou tem um detalhe, o campo "file_system_size_disk_mb" esta com o nome "file_system_size_disk_gb" na tabela.

Abraço,

Airton

Eduardo Legatti disse...

Olá Airton,

Item corrigido.

obrigado pela visita!

Abraços,

Legatti

Postagens populares