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


terça-feira, 6 de setembro de 2016

RMAN - Analisando a mensagem "Bad check value found during backing up datafile" no arquivo de alerta do Oracle

Por Eduardo Legatti

Olá,

No artigo de Fevereiro/2016 foi abordado como realizar a recuperação física de um bloco corrompido em um arquivo de dados utilizando a técnica "Block Media Recovery" do RMAN. Recentemente, fazendo a análise do arquivo de alerta de um banco de dados como demonstrado abaixo, percebi uma mensagem informando que o bloco 334002 do datafile 70 estaria corrompido durante a tentativa de realização de um backup físico pelo RMAN. Se realmente o bloco estiver corrompido, poderemos recuperá-lo utilizando esta técnica.

Sat Sep 3 10:10:01 2016
Hex dump of (file 70, block 334002) in trace file /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc
Corrupt block relative dba: 0x118518b2 (file 70, block 334002)
Bad check value found during backing up datafile
Data in bad block:
 type: 40 format: 2 rdba: 0x118518b2
 last change scn: 0x0c69.bc39d7ea seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7ea2802
 check value in block header: 0x3253
 computed block checksum: 0x0
Reread of blocknum=334002, file=/oradata/BD01/LOB_01_015.dbf. found valid data

Ao verificar o trecho acima no arquivo de alerta acima, realizei a validação do arquivo de dados pelo RMAN conforme a seguir, mas nenhuma mensagem de bloco corrompido foi emitida.

$ export ORACLE_SID=BD01
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 3 10:19:35 2016

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

connected to target database: BD01 (DBID=1637785486)

RMAN> validate datafile 70;

Starting validate at 03/09/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=485 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00070 name=/oradata/BD01/LOB_01_015.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:07:46

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- --------------
70   OK     0              124289       3171840         13656377431238

  File Name: /oradata/BD01/LOB_01_015.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              2418
  Other      0              3045133

Finished validate at 03/09/2016

Ao analisar o arquivo de trace, foi identificado que uma operação de backup incremental estava em execução durante o suposto problema de corrupção de bloco corrompido. No entanto, é possível perceber que logo após o bloco ser marcado como corrompido, existe uma mensagem indicando que o bloco foi novamente lido e que o seu estado estava válido. Vale a pena salientar que esta informação também está contida no arquivo de log de alerta.

$ cat /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc

Trace file /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:    server01
Release:    2.6.32-431.el6.x86_64
Version:    #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine:    x86_64
VM name:    Xen Version: 4.2 (HVM)
Instance name: BD01
Redo thread mounted by this instance: 1
Oracle process number: 136
Unix process pid: 26379, image: oracle@server01 (TNS V1-V3)


*** 2016-09-03 10:10:01.872
*** SESSION ID:(87.57015) 2016-09-03 10:10:01.872
*** CLIENT ID:() 2016-09-03 10:10:01.872
*** SERVICE NAME:(SYS$USERS) 2016-09-03 10:10:01.872
*** MODULE NAME:(backup incr datafile) 2016-09-03 10:10:01.872
*** ACTION NAME:(0002354 STARTED16) 2016-09-03 10:10:01.872

Hex dump of (file 70, block 334002)
Dump of memory from 0x00007F4158268000 to 0x00007F415826A000
7F4158268000 0000A228 118518B2 BC39D7EA 04020C69  [(.........9.i...]
7F4158269FB0 ACF6C7BB 701DB8EC B5F46AB4 E30FC56E  [.......p.j..n...]
7F4158269FC0 22758DDB B9452750 605107CF 39ECD3AD  [..u"P'E...Q`...9]
7F4158269FD0 387CC51C 9660182B 23606732 F1478AB3  [..|8+.`.2g`#..G.]
7F4158269FE0 1C17E72F C6082CF2 009DE777 6EABC849  [/....,..w...I..n]
7F4158269FF0 98D57134 E3D4B9E8 B511A853 D7EA2802  [4q......S....(..]
Corrupt block relative dba: 0x118518b2 (file 70, block 334002)
Bad check value found during backing up datafile
Data in bad block:
 type: 40 format: 2 rdba: 0x118518b2
 last change scn: 0x0c69.bc39d7ea seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7ea2802
 check value in block header: 0x3253
 computed block checksum: 0x0
Reread of blocknum=334002, file=/oradata/BD01/LOB_01_015.dbf. found valid data

Conclusão: Inicialmente eu achei muito estranho um erro no arquivo de alerta sem associação a um erro ORA-. Durante uma operação de backup usando o RMAN, o mesmo tenta obter uma imagem consistente do bloco de dados, e caso esse bloco seja alterado durante essa operação, o RMAN tentará obter novamente uma imagem consistente do mesmo bloco. Possivelmente esta foi a causa da mensagem no arquivo de alerta. Outro ponto importante a salientar é que toda essa operação é logada no arquivo de alerta (alert log file).

segunda-feira, 8 de agosto de 2016

Abordando a package DBMS_SQLTUNE para recomendar melhorias em SQL IDs específicos (SQL Tuning Advisor)

Por Eduardo Legatti

Olá,

Desde o Oracle 10g, os supervisores (advisors) são ferramentas especializadas que ajudam o DBA a analisar o desempenho do banco de dados, identificar possíveis problemas e gargalos e ajustar os vários componentes do banco de dados. No artigo de Abril/2008 eu demonstrei através de exemplos práticos como fazer para o Oracle analisar instruções SQL específicas e sugerir recomendações para melhorar seu desempenho. Foi usado o Supervisor de Ajuste SQL (SQL Tuning Advisor) através da package DBMS_SQLTUNE. Agora irei abordar novamente o uso do DBMS_SQLTUNE só que, em vez de passar como parâmetro uma instrução SQL para ser analisada, irei passar como parâmetro o SQL_ID de uma instrução SQL que já tinha sido executada anteriormente.

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Seg Ago 8 09:01:22 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> var id number;
SQL> exec :id := 2500;

Procedimento PL/SQL concluído com sucesso.

SQL> select count(*) from t1 where id=:id;

  COUNT(*)
----------
         8

1 linha selecionada.

Após a execução da instrução SQL acima, irei procurar através da view dinâmica de desempenho V$SQLAREA qual o SQL_ID associado à instrução SQL executada. 
  
SQL> select sql_id,parsing_schema_name
  2    from v$sqlarea
  3   where sql_fulltext like 'select count(*) from t1 where id=:id%'; 
SQL_ID        PARSING_SCHEMA_NAME ------------- ------------------------- 1x6vk95kg44wb SCOTT 1 linha selecionada.

Uma vez encontrada o SQL_ID, irei criar uma tarefa de SQL Tuning passando o SQL_ID 1x6vk95kg44wb e executar a mesma conforme demonstrado a seguir. Para essa tarefa irei limitar a sua execução até o máximo de 5 minutos (300 seg).

SQL> DECLARE
  2    l_sql_tune_task_id  VARCHAR2(100);
  3  BEGIN
  4    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  5                            sql_id      => '1x6vk95kg44wb',
  6                            scope       => DBMS_SQLTUNE.scope_comprehensive,
  7                            time_limit  => 300,
  8                            task_name   => 'SCOTT_1x6vk95kg44wb',
  9                            description => 'SCOTT Tuning task for statement 1x6vk95kg44wb.');
 10    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 11  END;
 12  /
l_sql_tune_task_id: SCOTT_1x6vk95kg44wb

Procedimento PL/SQL concluído com sucesso.

SQL> select task_id,task_name,status from user_advisor_log;

   TASK_ID TASK_NAME                      STATUS
---------- ------------------------------ -----------
     33150 SCOTT_1x6vk95kg44wb            INITIAL

1 linha selecionada.

SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'SCOTT_1x6vk95kg44wb');

Procedimento PL/SQL concluído com sucesso.


Pronto. Após a execução será necessário apenas analisar e verificar as recomendações que foram encontradas pelo otimizador. Vale a pena salientar que além de ser mostrado a instrução SQL, também será mostrado o valor da bind variable usada na instrução SQL.

SQL> set long 1000000;
SQL> set longchunksize 100000
SQL> set pagesize 10000
SQL> set linesize 1000
SQL> select dbms_sqltune.report_tuning_task('SCOTT_1x6vk95kg44wb') as recomendacoes from dual;

RECOMENDACOES
--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SCOTT_1x6vk95kg44wb
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 08/08/2016 09:13:45
Completed at       : 08/08/2016 09:13:56

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 1x6vk95kg44wb
SQL Text   : select count(*) from t1 where id=:id
Bind Variables :
 1 -  (NUMBER):2500

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  A tabela "SCOTT"."T1" não foi analisada.

  Recommendation
  --------------
  - Considere a coleta de estatísticas do otimizador para esta tabela
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'T1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
            => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    O otimizador requer estatísticas atualizadas para a tabela ao selecionar
    um plano de execução adequado.

2- Index Finding (see explain plans section below)
--------------------------------------------------
  O plano de execução dessa instrução pode ser melhorado com a criação de um
  ou mais índices.

  Recommendation (estimated benefit: 99.93%)
  ------------------------------------------
  - Considere a execução do Supervisor de Acesso para aumentar o projeto de
    esquema físico ou a criação do índice recomendado.
    create index SCOTT.IDX$$_817E0001 on SCOTT.T1("ID");

  Rationale
  ---------
    Criar os índices recomendados melhora significativamente o plano de
    execução dessa instrução. No entanto, talvez seja preferível executar o
    "Supervisor de Acesso" utilizando uma carga de trabalho SQL representativa
    em oposição a uma única instrução. Isso permitirá a obtenção de
    recomendações de índice abrangentes que levam em consideração o overhead
    de manutenção de índice e um consumo de espaço adicional.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |  1503   (3)| 00:00:19 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    73 |   949 |  1503   (3)| 00:00:19 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=:ID)

2- Using New Indices
--------------------
Plan hash value: 809122696

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX$$_817E0001 |     8 |   104 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=:ID)

-------------------------------------------------------------------------------

1 linha selecionada.

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