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


segunda-feira, 11 de julho de 2016

Performance em operações de INSERT: Quando a ordem dos fatores altera o produto

Por Eduardo Legatti

Olá,

No artigo de Dezembro/2013 eu abordei alguns conceitos sobre algumas operações no Oracle que podem ser aplicadas a este artigo. Irei demonstrar e compartilhar uma experiência na qual tive que fazer uma carga de dados (Direct INSERT) em uma tabela particionada, onde a fonte dos dados era uma uma view de banco de dados (140 milhões de linhas). Sabemos que a performance de um INSERT em uma tabela que possui índices é mais lento do que se a mesma não tivesse índices. O que me chamou a atenção além do tempo para realização do INSERT, foi o uso considerável das tablespaces de UNDO e TEMP. Abaixo irei mostrar um cenário onde irei criar uma tabela (NOLOGGING) particionada com os respectivos índices (NOLOGGING) e fazer uma operação de INSERT a partir de uma view.

SQL> set timing on
SQL> create table t1
  2  (
  3     code01  number (10) not null,
  4     code02  number (15) not null,
  5     code03  number (10) not null,
  6     code04  number (10) not null
  7  )
  8  tablespace tbs_data
  9  partition by range (code03)
 10     interval (1)
 11     (partition p1 values less than (1))
 12  nologging;

Tabela criada.

Decorrido: 00:00:00.11

SQL> create index idx_code01 on t1 (code01) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.73
SQL> create index idx_code01code03 on t1 (code01,code03) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code02 on t1 (code02) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code03 on t1 (code03) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code03_code02_code01 on t1 (code03,code02,code01) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01

Após a criação da tabela e dos respectivos índices, irei fazer o INSERT conforme demonstrado abaixo. Pode-se perceber que após 10 horas e 41 minutos da execução, o INSERT foi abortado por falta de espaço na tablespace de UNDO.

SQL> insert /*+ APPEND */ into t1 select * from vw1;
insert /*+ append */ into t1 select * from vw1
*
ERRO na linha 1:
ORA-30036: não foi possível estender o segmento em 8 no tablespace de undo 'UNDOTBS1'

Decorrido: 10:41:45.31

Vale a pena salientar que tanto a tablespace de UNDO quanto a tablespace TEMP foram definidas inicialmente com tamanho de 1 GB e AUTOEXTED ON. Ao final, é possível perceber que a tablespace de UNDO se estendeu até o máximo de 32 GB e que a tablespace TEMP se estendeu até 24 GB, conforme demonstrado abaixo.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
UNDOTBS1                       31,9999847

1 linha selecionada.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_temp_files;

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
TEMP                                   24

1 linha selecionada.

Enfim, percebendo que esta não seria a melhor opção para carregar uma tabela em um ambiente de produção, a alternativa abaixo foi a que melhor se encaixou, ou seja, primeiro realiza a criação da tabela particionada(NOLOGGING), depois a execução do INSERT utilizando a carga direta, e por fim a criação dos índices (NOLOGGING), conforme demonstrado abaixo.

SQL> set timing on
SQL> create table t1
  2  (
  3     code01   number (10) not null,
  4     code02   number (15) not null,
  5     code03   number (10) not null,
  6     code04   number (10) not null
  7  )
  8  tablespace tbs_data
  9  partition by range (code03)
 10     interval (1)
 11     (partition p1 values less than (1))
 12  nologging;

Tabela criada.

Decorrido: 00:00:01.10

SQL> insert /*+ APPEND */ into t1 select * from vw1;

140000000 linhas criadas.

Decorrido: 00:04:35.04

SQL> create index idx_code01 on t1 (code01) nologging noparallel local;

Índice criado.

Decorrido: 00:10:25.73
SQL> create index idx_code01code03 on t1 (code01, code03) nologging noparallel local;

Índice criado.

Decorrido: 00:09:56.37
SQL> create index idx_code02 on t1 (code02) nologging noparallel local;

Índice criado.

Decorrido: 00:09:16.56
SQL> create index idx_code03 on t1 (code03) nologging noparallel local;

Índice criado.

Decorrido: 00:08:59.35
SQL> create index idx_code03_code02_code01 on t1 (code03, code02, code01) nologging noparallel local;

Índice criado.

Decorrido: 00:11:38.65

No mais é possível perceber que no total foram gastos cerca de 54 minutos para realizar toda operação, que a tablespace de UNDO não chegou a ser estendida acima de 1 GB e que a tablespace TEMP se estendeu no máximo até 6 GB.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
UNDOTBS1                                1

1 linha selecionada.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_temp_files;

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
TEMP                                    6

1 linha selecionada.

quarta-feira, 1 de junho de 2016

Um pouco sobre o Database Resource Manager (Instance Caging) do Oracle 11g R2

Por Eduardo Legatti

Olá,

Em um servidor onde existem várias instâncias de bancos de dados Oracle, em alguns momentos pode acontecer do mesmo ficar muito sobrecarregado e com o "load" alto pelo fato de uma ou mais instâncias do Oracle estarem consumindo muitos recursos de CPU. Neste caso, uma instância pode comprometer a performance geral do servidor e impactar o funcionamento das demais instâncias. Então, como fazer para que possamos restringir quanto uma instância poderá consumir de CPU nesse servidor? A partir do Oracle 11g R2 é possível limitar o uso de CPU em um servidor com múltiplos núcleos (CPU core). Para isso faremos uso de uma feature chamada Instance Caging do Database Resource Manager. Segue um exemplo prático.

Abaixo está sendo mostrado a saída do comando TOP do Linux de um servidor que possui 8 núcleos de CPU e cerca de 15 instâncias Oracle.

top - 10:00:05 up 526 days, 18 min,  1 user,  load average: 7.28, 4.59, 4.78
Tasks: 1125 total,   6 running, 1102 sleeping,  16 stopped,   1 zombie
Cpu0  : 47.6%us,  3.9%sy,  0.0%ni, 44.7%id,  3.9%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  : 85.4%us,  0.0%sy,  0.0%ni, 13.6%id,  0.0%wa,  0.0%hi,  1.0%si,  0.0%st
Cpu2  : 34.3%us,  2.0%sy,  0.0%ni, 63.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  : 48.1%us,  1.0%sy,  0.0%ni, 49.0%id,  0.0%wa,  1.0%hi,  1.0%si,  0.0%st
Cpu4  : 47.1%us,  3.8%sy,  0.0%ni, 45.2%id,  2.9%wa,  0.0%hi,  1.0%si,  0.0%st
Cpu5  : 33.7%us,  5.9%sy,  0.0%ni, 59.4%id,  0.0%wa,  0.0%hi,  1.0%si,  0.0%st
Cpu6  : 43.3%us,  5.8%sy,  0.0%ni, 50.0%id,  1.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :100.0%us,  0.0%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32876004k total, 32439108k used,   436896k free,   102568k buffers
Swap: 16777208k total,  6779068k used,  9998140k free, 23822240k cached

Agora irei realizar um teste na qual habilitarei o Instance Caging na instância BD01 de forma a limitar o consumo de CPU pela mesma. Para isso farei alteraçao em 2 parâmetros dinâmicos no Oracle (CPU_COUNT e RESOURCE_MANAGER_PLAN). O propósito será limitar a instância BD01 de consumir no máximo 4 núcleos de CPU conforme demonstrado abaixo.

$ export ORACLE_SID=BD01
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Qua Jun 1 09:58:32 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set CPU_COUNT = 4;

Sistema alterado.


Após limitar para 4 o número de núcleos de CPU que a instância BD01 poderá usar, irei habilitar de fato o Instance Caging setando o parâmetro RESOURCE_MANAGER_PLAN com o valor DEFAULT_PLAN conforme demonstrado abaixo.

SQL> alter system set RESOURCE_MANAGER_PLAN = DEFAULT_PLAN;

Sistema alterado.
 
Pronto. A partir de agora o Instance Caging está habilitado para a instância BD01 de forma que a mesma se limite ao consumo de apenas 4 núcleos do total de 8 existentes no servidor.

SQL> show parameter CPU_COUNT;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4

SQL> show parameter RESOURCE_MANAGER_PLAN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      DEFAULT_PLAN
 
Abaixo podemos ver que o plano DEFAULT_PLAN está habilitado.

SQL> select id,name,is_top_plan,cpu_managed,instance_caging from v$rsrc_plan;

        ID NAME                         IS_TOP_PLAN CPU_MANAGED INSTANCE_CAGING
---------- ---------------------------- ----------- ----------- ---------------
    125274 DEFAULT_PLAN                 TRUE        ON          ON
    125271 ORA$AUTOTASK_SUB_PLAN        FALSE       ON          ON
    125272 ORA$AUTOTASK_HIGH_SUB_PLAN   FALSE       ON          ON

3 linhas selecionadas.

Realizando um pequeno teste e abrindo 4 sessões no Oracle através do SQL*Plus, executei algumas instruções SQL pesadas na instância BD01. Pela saída do comando TOP abaixo podemos perceber que apenas 4 núcleos de CPU estão em uso com quase 100% cada, ou seja, dando um total de 400% de uso.

top - 10:03:05 up 526 days, 20 min,  1 user,  load average: 4.32, 6.88, 5.21
Tasks:   4 total,   4 running,   0 sleeping,   0 stopped,   0 zombie
Cpu(s): 73.6%us,  2.1%sy,  0.0%ni, 24.2%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32876004k total, 32369496k used,   506508k free,    97944k buffers
Swap: 16777208k total,  8340916k used,  8436292k free, 23379872k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 5025 oracle    20   0  932m  24m  20m R 98.9  0.1   4:21.60 oracleBD01 (LOCAL=NO)
 5041 oracle    20   0  932m  24m  20m R 98.9  0.1   4:17.72 oracleBD01 (LOCAL=NO)
 7737 oracle    20   0  932m  22m  20m R 98.9  0.1   3:45.09 oracleBD01 (LOCAL=NO)
 5001 oracle    20   0  933m  28m  25m R 98.9  0.1   4:24.05 oracleBD01 (LOCAL=NO)

Agora irei limitar ainda mais o consumo de CPU para a instância BD01 de forma que a mesma utilize apenas 1 núcleo de CPU.


SQL> alter system set CPU_COUNT = 1;

top - 10:06:17 up 504 days, 22 min,  1 user,  load average: 3.13, 5.38, 3.85
Tasks:   4 total,   1 running,   3 sleeping,   0 stopped,   0 zombie
Cpu(s): 54.4%us,  3.3%sy,  0.0%ni, 35.9%id,  5.7%wa,  0.0%hi,  0.8%si,  0.0%st
Mem:  32876004k total, 32543176k used,   332828k free,   109064k buffers
Swap: 16777208k total,  8330556k used,  8446652k free, 23567276k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 7737 oracle    20   0  932m  24m  20m R 35.9  0.1  10:40.08 oracleBD01 (LOCAL=NO)
 5041 oracle    20   0  932m  24m  20m S 25.4  0.1  10:42.45 oracleBD01 (LOCAL=NO)
 5001 oracle    20   0  933m  28m  25m S 19.6  0.1  10:47.46 oracleBD01 (LOCAL=NO)
 5025 oracle    20   0  932m  24m  20m S 18.5  0.1  10:44.54 oracleBD01 (LOCAL=NO) 
 
Podemos perceber pela saída do comando TOP acima que ocorreu um balanceamento de carga de CPU entre as sessões de forma que no máximo apenas 100% , ou seja, como se apenas 1 núcleo fosse utilizado pelas 4 sessões. Pode-se notar também que após essa limitação é possível perceber que o "load" do servidor diminuiu de 4.32 para 3.13.

No mais, vale a pena salientar que a limitação de CPU ocorre para processos de primeiro plano (foreground processes) e alguns processos de segundo plano não críticos (background processes).

segunda-feira, 2 de maio de 2016

Abordando a técnica de tablespaces transportáveis no Oracle (Transportable Tablespaces)

Por Eduardo Legatti

Olá,

No artigo de Agosto/2013, eu mostrei como mover ou renomear arquivos de bancos de dados (control files, datafiles, redo log files). Neste artigo irei demonstrar através de um exemplo prático, como podemos plugar tablespaces de um banco de dados em outro banco de dados através do recurso Transportable Tablespaces (TTS) disponível desde a versão do Oracle 8i. Este recurso pode vir a ser útil quando precisamos transferir um grande volume de dados de um banco de dados para outro banco de dados. Ao invés de usar métodos de exportação/importação através dos utilitários exp/imp ou expdp/impdp que poderiam consumir um tempo considerável, usando o método TTS, o tempo para transferir os dados seria praticamente o tempo de realização da cópia dos datafiles do banco de dados de origem para o banco de dados de destino. A figura abaixo ilustra o que será abordado a seguir.



As tablespaces DATA_TTS, INDX_TTS e LOB_TTS pertencentes ao banco de dados BD01 demonstrados abaixo serão alvos da migração para o banco de dados BD02.
 
SQL> select tablespace_name,file_name
  2    from dba_data_files
  3   where tablespace_name in ('DATA_TTS','INDX_TTS','LOB_TTS');

TABLESPACE_NAME          FILE_NAME
------------------------ -----------------------------------------
DATA_TTS                 /oradata/BD01/DATA_TTS_01_001.dbf
INDX_TTS                 /oradata/BD01/INDX_TTS_01_001.dbf
LOB_TTS                  /oradata/BD01/LOB_TTS_01_001.dbf

3 linhas selecionadas.

Segue abaixo os objetos de propriedade do usuário SCOTT que possuem segmentos nessas tablespaces.
 
SQL> select owner,segment_name,segment_type,tablespace_name
  2    from dba_segments
  3   where owner='SCOTT';

OWNER        SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------ ------------------------------ ------------------ ----------------
SCOTT        T1                             TABLE              DATA_TTS
SCOTT        PK_T1                          INDEX              INDX_TTS
SCOTT        IDX2                           INDEX              INDX_TTS
SCOTT        SYS_IL0000225643C00003$$       LOBINDEX           LOB_TTS
SCOTT        SYS_LOB0000225643C00003$$      LOBSEGMENT         LOB_TTS

5 linhas selecionadas.


Para que o transporte de tablespaces seja possível, os bancos de dados de origem e destino precisam ter o mesmo CHARACTERSET. Isto é necessário para evitar um erro durante o processo de plugar as tablespaces na qual uma mensagem ORA-29345 é emitida dizendo que não é possível conectar uma tablespace em um banco de dados usando um conjunto de caracteres incompatível.
 
SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_LANGUAGE                   BRAZILIAN PORTUGUESE
NLS_TERRITORY                  BRAZIL
NLS_CURRENCY                   R$
NLS_ISO_CURRENCY               BRAZIL
NLS_NUMERIC_CHARACTERS         ,.
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              BRAZILIAN PORTUGUESE
NLS_SORT                       WEST_EUROPEAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Cr$
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_RDBMS_VERSION              11.2.0.3.0

20 linhas selecionadas.

O primeiro passo é verificar se as tablespaces são passíveis para utilização do método TTS.
 
SQL> exec SYS.DBMS_TTS.TRANSPORT_SET_CHECK
  2  (ts_list => 'DATA_TTS', incl_constraints => TRUE);

Procedimento PL/SQL concluído com sucesso.
  
SQL> select * from SYS.transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: O índice SCOTT.PK_T1 do tablespace INDX_TTS valida as restrições principais  da tabela SCOTT.T1 no tablespace DATA_TTS.
ORA-39905: A tabela SCOTT.SYS_LOB0000225643C00003$$ do tablespace LOB_TTS aponta para o segmento da LOB SCOTT.T1 no tablespace DATA_TTS.

2 linhas selecionadas.

Pelo resultado acima, é possível perceber que a tablespace DATA_TTS sozinha não pode ser transportada pelo fato de violar algumas restrições de dependência com as outras tablespaces. Neste caso, iremos verificar todas as tablespaces ao mesmo tempo conforme demonstrado abaixo.

SQL> exec SYS.DBMS_TTS.TRANSPORT_SET_CHECK
  2  (ts_list => 'DATA_TTS,INDX_TTS,LOB_TTS', incl_constraints => TRUE);

Procedimento PL/SQL concluído com sucesso.

SQL> select * from SYS.transport_set_violations;

não há linhas selecionadas


=====================================

Banco de dados BD01 (Source Database)
=====================================


O primeiro passo a ser realizado é colocar as tablespaces no modo READ ONLY no banco de dados de origem conforme demonstrado abaixo.

SQL> alter tablespace DATA_TTS read only;

Tablespace alterado.

SQL> alter tablespace INDX_TTS read only;

Tablespace alterado.

SQL> alter tablespace LOB_TTS read only;

Tablespace alterado.

A seguir, será necessário exportar os metadados utilizando o Datapump Export (expdp) utilizando a cláusula TRANSPORT_TABLESPACES e indicando as tablespaces que serão transportadas.

export ORACLE_SID=BD01
expdp system/#5ydl3db# dumpfile=BD01_transport_tablespaces.dmp
transport_tablespaces=DATA_TTS,INDX_TTS,LOB_TTS
nologfile=y

Export: Release 11.2.0.3.0 - Production on Mon Mai 2 10:02:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=BD01_transport_tablespaces.dmp transport_tablespaces=DATA_TTS,INDX_TTS,LOB_TTS nologfile=y
Processando o tipo de objeto TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processando o tipo de objeto TRANSPORTABLE_EXPORT/TABLE
Processando o tipo de objeto TRANSPORTABLE_EXPORT/INDEX/INDEX
Processando o tipo de objeto TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processando o tipo de objeto TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Tabela-mestre "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 e:
  /tmp/BD01_transport_tablespaces.dmp
******************************************************************************
Os arquivos de dados necessarios para o tablespace transportavel DATA_TTS:
  /oradata/BD01/DATA_TTS_01_001.dbf
Os arquivos de dados necessarios para o tablespace transportavel INDX_TTS:
  /oradata/BD01/INDX_TTS_01_001.dbf
Os arquivos de dados necessarios para o tablespace transportavel LOB_TTS:
  /oradata/BD01/LOB_TTS_01_001.dbf
O job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" foi concluido com sucesso em 10:04:57

Após a geração do arquivo de dump com os metadados das tablespaces, poderemos copiar os arquivos de dados para o destino conforme demonstrado abaixo.

cp -a /oradata/BD01/DATA_TTS_01_001.dbf /oradata/BD02
cp -a /oradata/BD01/INDX_TTS_01_001.dbf /oradata/BD02
cp -a /oradata/BD01/LOB_TTS_01_001.dbf /oradata/BD02

Após a realização da cópia, as tablespaces no banco de dados de origem já podem voltar a estado READ WRITE.

SQL> alter tablespace DATA_TTS read write;

Tablespace alterado.

SQL> alter tablespace INDX_TTS read write;

Tablespace alterado.

SQL> alter tablespace LOB_TTS read write;

Tablespace alterado.


=====================================

Banco de dados BD02 (Target Database)
=====================================


No banco de dados de destino, o primeiro passo é criar os usuários que possuem objetos nas tablespaces que serão importadas. No meu caso, apenas o usuário SCOTT será criado. Caso o usuário não seja criado, o erro ORA-29342 será emitido com a mensagem que algum usuário não existe no banco de dados.

SQL> create user SCOTT identified by tiger;

Usuário criado.

SQL> grant connect,resource to SCOTT;

Concessão bem-sucedida.

Após a criação do usuário SCOTT no banco de dados BD02, poderemos proceder com a importação do dump utilizando o Datapump Import (impdp) utilizando a cláusula TRANSPORT_DATAFILES e indicando os caminho completo dos datafiles que serão transportados conforme demonstrado abaixo.

export ORACLE_SID=BD02
impdp system/#5ydl3db# dumpfile=BD01_transport_tablespaces.dmp
transport_datafiles='/oradata/BD02/DATA_TTS_01_001.dbf',
                    '/oradata/BD02/INDX_TTS_01_001.dbf',
                    '/oradata/BD02/LOB_TTS_01_001.dbf'
nologfile=y


[oracle@beast TTS2]$ export ORACLE_SID=BD02
[oracle@beast TTS2]$ impdp system/#5ydl3db# dumpfile=BD01_transport_tablespaces.dmp transport_datafiles='/oradata/BD02/DATA_TTS_01_001.dbf','/oradata/BD02/INDX_TTS_01_001.dbf','/oradata/BD02/LOB_TTS_01_001.dbf' nologfile=y

Import: Release 11.2.0.3.0 - Production on Mon Mai 2 10:27:24 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Tabela-mestre "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" carregada/descarregada com sucesso
Iniciando "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=BD01_transport_tablespaces.dmp transport_datafiles=/oradata/BD02/DATA_TTS_01_001.dbf,/oradata/BD02/INDX_TTS_01_001.dbf,/oradata/BD02/LOB_TTS_01_001.dbf nologfile=y
Processando o tipo de objeto TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processando o tipo de objeto TRANSPORTABLE_EXPORT/TABLE
Processando o tipo de objeto TRANSPORTABLE_EXPORT/INDEX/INDEX
Processando o tipo de objeto TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processando o tipo de objeto TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
O job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" foi concluido com sucesso em 10:28:49

Pronto. O último passo é colocar as tablespaces no modo READ WRITE.

SQL> alter tablespace DATA_TTS read write;

Tablespace alterado.

SQL> alter tablespace INDX_TTS read write;

Tablespace alterado.

SQL> alter tablespace LOB_TTS read write;

Tablespace alterado.

Após a importação, podemos verificar abaixo que as tablespaces foram transportadas com sucesso no banco de dados BD02.

SQL> select tablespace_name,file_name from dba_data_files
  2   where tablespace_name in ('DATA_TTS','INDX_TTS','LOB_TTS');

TABLESPACE_NAME          FILE_NAME
------------------------ -----------------------------------------
DATA_TTS                 /oradata/BD02/DATA_TTS_01_001.dbf
INDX_TTS                 /oradata/BD02/INDX_TTS_01_001.dbf
LOB_TTS                  /oradata/BD02/LOB_TTS_01_001.dbf

3 linhas selecionadas.

SQL> select owner,segment_name,segment_type,tablespace_name
  2    from dba_segments
  3   where owner='SCOTT';

OWNER        SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------ ------------------------------ ------------------ ----------------
SCOTT        T1                             TABLE              DATA_TTS
SCOTT        PK_T1                          INDEX              INDX_TTS
SCOTT        IDX2                           INDEX              INDX_TTS
SCOTT        SYS_IL0000225643C00003$$       LOBINDEX           LOB_TTS
SCOTT        SYS_LOB0000225643C00003$$      LOBSEGMENT         LOB_TTS

5 linhas selecionadas.

Por fim, poderemos revogar o privilégio UNLIMITED TABLESPACE e conceder os privilégios de cota para cada tablespace conforme a seguir.

SQL> revoke unlimited tablespace from SCOTT;

Revogação bem-sucedida.

SQL> alter user SCOTT quota unlimited on DATA_TTS;

Usuário alterado.

SQL> alter user SCOTT quota unlimited on INDX_TTS;

Usuário alterado.

SQL> alter user SCOTT quota unlimited on LOB_TTS;

Usuário alterado.

sexta-feira, 1 de abril de 2016

Conceito de transação em blocos PL/SQL no Oracle

Por Eduardo Legatti

Olá,

Não é raro vermos iniciantes em Oracle, principalmente desenvolvedores, tendo algumas dúvidas em relação ao conceito de transações no banco de dados. Sabemos que o Oracle abre uma transação implicitamente quando executamos a primeira instrução SQL (DML) e que, para finalizar esta transação, deveremos emitir o comando COMMIT ou ROLLBACK. Vale a pena salientar que qualquer comando DDL (CREATE, ALTER, DROP, etc.) irá finalizar a transação com COMMIT mesmo que a instrução SQL execute com falha. Mas como fica a transação quando as instruções SQL estão dentro de um bloco PL/SQL ou de uma stored procedure? Neste artigo irei demonstrar exemplos da execução de blocos PL/SQL para demonstrar como as instruções SQL são afetadas dentro do mesmo.


Conceito de transação em blocos PL/SQL

Veremos abaixo como as instruções SQL irão se comportar dentro de um bloco PL/SQL padrão.
 
SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11  END;
 12  /
*
ERRO na linha 1:
ORA-01722: número inválido
ORA-06512: em line 8


O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 
SQL> select * from T1;

não há linhas selecionadas

SQL> select * from T2;

não há linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas


Pelo resultado demonstrado acima, podemos perceber que por padrão, um procedimento PL/SQL fará rollback de toda a transação caso aconteça algum erro, independente se alguma instrução SQL foi executada com sucesso.


Conceito de transação em blocos PL/SQL (EXCEPTION) 

Veremos abaixo como as instruções SQL irão se comportar caso usemos a cláusula EXCEPTION dentro do bloco PL/SQL.
 
SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11   exception
 12    when others then
 13     dbms_output.put_line('Error msg: '||sqlerrm);
 15  END;
 16  /
Error msg: ORA-01722: número inválido

Procedimento PL/SQL concluído com sucesso.


O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 
SQL> select * from T1;

3 linhas selecionadas

SQL> select * from T2;

3 linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas
 
Pelo resultado demonstrado acima, podemos perceber que criando uma EXCEPTION, as instruções SQL serão executadas até dar algum erro. Após o erro, a execução irá parar e a transação ficará aberta até a execução da instrução COMMIT ou ROLLBACK.


Conceito de transação em blocos PL/SQL (RAISE)

Veremos abaixo como as instruções SQL irão se comportar caso usemos a cláusula RAISE_APPLICATION_ERROR dentro do bloco PL/SQL.

SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11   exception
 12    when others then
 13     raise_application_error(-20000, 'Ocorreu um erro');
 14  END;
 15  /
*
ERRO na linha 1:
ORA-20000: Ocorreu um erro
ORA-06512: em line 13

O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 
SQL> select * from T1;

não há linhas selecionadas

SQL> select * from T2;

não há linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas
 
Pelo resultado demonstrado acima, podemos perceber que criando uma EXCEPTION e usando o comando RAISE_APPLICATION_ERROR, será feito rollback de toda transação da mesma forma que se não tivesse sendo utilizado a cláusula EXCEPTION.

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

Postagens populares