Páginas

quinta-feira, 24 de maio de 2012

Índices e Chaves Estrangeiras


Vamos a mais um post sobre uma falha de arquitetura que muitas vezes não é percebida pelo desenvolvedor ou DBAs envolvidos no desenvolvimento de uma nova aplicação, ou mesmo de uma aplicação já operacional.

A algumas semanas estava dando manutenção em um sistema e me chamou a atenção o fato de todas as chaves estrangeiras estarem desabilitadas, com a justificativa de que elas tornavam o sistema muito mais lento.

Não vou nem entrar na discução a respeito da questão sobre performance (ficará para um próximo post, mas caso não se queira fazer uso dos recursos de integridade de dados de um banco de dados Relacional, então não use um banco de dados Relacional, existem diversas opções de bancos de dados NoSQL disponíveis no mercado e eles também implementam mecanismos de controle de concorrência e integridade de dados).

Bom, voltando ao assunto, no caso em particular percebi que ao habilitar as constraints que a lentidão ocorria durante o delete de registros em algumas tabelas, onde ocorriam diversos "table locks" nas tabelas filhas, e já me ocorreu, "estão faltando índices".

A documentação do Oracle Database, já fala sobre índices e chaves estrangeiras:
  1. Evitar contenção completa da tabela (lock table) filha em caso de "delete" na tabela pai; 
    • O índice permite o lock na tabela filha como "row lock";
    • Updates na chave primária da tabela pai também caem nesta mesma situação, mas alertando que alterações de chave primária serem consideradas uma má prática;
  2. Evita "full table scan" desnecessários na tabela filha; 
Os índices apenas podem ser ignorados caso a tabela pai não sofra operações de "update", e nem mesmo de "delete" (caso raro).

A algum tempo atrás eu já havia construido uma consulta para exibir todos os índices não existentes para as FKs criadas no esquema do usuário corrente, já gerando os comandos de criação dos índices. Segue:

set lines 180
set pages 10000
col command for a180

alter session set optimizer_features_enable='10.2.0.1';

SELECT 'CREATE INDEX ' || cons.constraint_name || ' ON ' || cons.table_name || '(' || cons.columns || ') TABLESPACE &tablespace;' command
FROM (SELECT constraint_name, table_name,
           LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
      FROM (SELECT dcc.constraint_name,
                   dcc.table_name,
                   dcc.column_name,
                   ROW_NUMBER() OVER (PARTITION BY dcc.constraint_name ORDER BY dcc.position) AS curr,
                   ROW_NUMBER() OVER (PARTITION BY dcc.constraint_name ORDER BY dcc.position) -1 AS prev
            FROM  user_cons_columns dcc
            INNER JOIN user_constraints dc
                    ON dc.owner = dcc.owner
                   AND dc.constraint_name = dcc.constraint_name
                   AND dcc.table_name = dc.table_name
            WHERE dc.constraint_type = 'R')
    GROUP BY constraint_name, table_name
    CONNECT BY prev = PRIOR curr AND constraint_name = PRIOR constraint_name
    START WITH curr = 1) cons
WHERE NOT EXISTS (SELECT 1 
        FROM 
          (SELECT index_name, table_name,
                  LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
             FROM (SELECT icc.index_name,
                          icc.table_name,
                          icc.column_name,
                          ROW_NUMBER() OVER (PARTITION BY icc.index_name ORDER BY icc.column_position) AS curr,
                          ROW_NUMBER() OVER (PARTITION BY icc.index_name ORDER BY icc.column_position) -1 AS prev
                    FROM  user_ind_columns icc)
             GROUP BY index_name, table_name
             CONNECT BY prev = PRIOR curr AND index_name = PRIOR index_name
             START WITH curr = 1) indx
        WHERE cons.table_name = indx.table_name
          AND cons.columns = indx.columns)
ORDER BY cons.table_name;

Prova de conceito

Caso algum developer teimoso ainda queira argumentar a respeito, bom eis então uma prova de conceito sobre o uso de índices em chaves estrangeiras.

Primeiro devemos criar 2 tabelas grandes, onde a tabela BIGTAB2 (com 1 milhão de registros) fará referencia à tabela BIGTAB1 com a clausula "on delete cascade" (para remover automaticamente registros da tabela BIGTAB2 caso o registro pai tenha sido removido na tabela BIGTAB1).

-- Cria tabela PAI
 
create table bigtab1
as
select rownum id, a.*
  from all_objects a
where 1=0;
 
alter table bigtab1 add  primary key (id) using index tablespace USERS;
 
insert into bigtab1
select rownum,
       owner, object_name, subobject_name,
       object_id, data_object_id,
       object_type, created, last_ddl_time,
       timestamp, status, temporary,
       generated, secondary
  from all_objects;
commit;
 
execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB1', cascade=>TRUE);
 
-- Cria tabela FILHA
 
create table bigtab2
as
select a.*,
       rownum parent_id
  from bigtab1 a
where 1=0;
 
alter table bigtab2 add foreign key (parent_id) references bigtab1(id) on delete cascade;
 
declare
    l_cnt  number := 0;
    l_rows number := 1000000;
begin
    -- Generate Rows
    while (l_cnt < l_rows)
    loop
        insert into bigtab2
        select ID+l_cnt, -- Nova chave unica
               owner, object_name, subobject_name,
               object_id, data_object_id,
               object_type, created, last_ddl_time,
               timestamp, status, temporary,
               generated, secondary,
               ID   -- Mantem referencia ao pai na tabela bigtab1
          from bigtab1
         where id <= l_rows-l_cnt;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
end;
/

execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB2', cascade=>TRUE);

Veja que apenas a chave primária, coluna ID, da tabela BIGTAB1 esta indexada.

Delete sem índice na FK


Agora façamos um teste de deleção na tabela BIGTAB1 gerando trace da execução.

ALTER SESSION SET TRACEFILE_IDENTIFIER = "DBA_RAFAEL_1";
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
 
delete from bigtab1 where id between 1 and 100;
 
rollback;


Na execução do TKPROF usamos o parâmetro "sys=yes" para que a deleção dos registros na tabela filha seja exibido no arquivo de saída, assim como o "aggregate=yes" para agregar os deletes.


tkprof rddbdev_ora_22603_DBA_RAFAEL_1.trc trace_delete_1.txt explain=DBA_RAFAEL sys=YES aggregate=yes waits=yes sort=prsela,exeela,fchela


Como podemos ver abaixo a deleção da tabela pai possui o tempo total de execução de 0.04 segundos e plano de execução usando INDEX RANGE SCAN da chave primária, tal como já era de se esperar:


delete from bigtab1
where
id between :"SYS_B_0" and :"SYS_B_1"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.04          0          2        517         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.04          0          2        517         100

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67  (DBA_RAFAEL)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  BIGTAB1 (cr=1520802 pr=0 pw=0 time=7588432 us)
    100   FILTER  (cr=2 pr=0 pw=0 time=829 us)
    100    INDEX RANGE SCAN SYS_C00167097 (cr=2 pr=0 pw=0 time=32 us)(object id 220499)

Se pode perceber o alto custo (CR) e tempo (TIME) associados a operação de DELETE no plano de execução, isto se deve devido ao cascade, uma vez que a deleção dos registros na tabela filha é feito pelo próprio oracle, pois a constraint existe com a clausula "on delete cascade".

Como podemos ver abaixo são feitas mais de 100 execuções (uma para cada chave na tabela pai) do delete, com tempo total de execução de 7.54 segundos, e também podemos observar no plano de execução que é feito um FULL TABLE SCAN na tabel BIGTAB2 a cada execução.


delete from "DBA_RAFAEL"."BIGTAB2"
where
"PARENT_ID" = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      7.72       7.54          0    1520800       1688        1500
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      7.72       7.54          0    1520800       1688        1500

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  BIGTAB2 (cr=1520800 pr=0 pw=0 time=7538904 us)
   1500   TABLE ACCESS FULL BIGTAB2 (cr=1520800 pr=0 pw=0 time=7149249 us)





Delete com índice na FK


Com a execução da consulta para criar os índices de acordo com as chaves estrangeiras foi recomendada a criação do seguinte índice:


COMMAND
-------------------------------------------------------------------
CREATE INDEX SYS_C00167103 ON BIGTAB2(PARENT_ID) TABLESPACE USERS;


Após criado o índices executamos novamente a mesma operação de delete.

CREATE INDEX SYS_C00167103 ON BIGTAB2(PARENT_ID) TABLESPACE USERS; 
execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB2', cascade=>TRUE);

ALTER SESSION SET TRACEFILE_IDENTIFIER = "DBA_RAFAEL_2";
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

delete from bigtab1 where id between 1 and 100;
rollback;

Agora, com tempo de execução menor, a saída do TKPROF nos aponta um custo e tempo muito menor em ambas as operações de delete.

delete from bigtab1
where
 id between :"SYS_B_0" and :"SYS_B_1"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          2        615         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          2        615         100


Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67  (DBA_RAFAEL)


Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  BIGTAB1 (cr=304 pr=0 pw=0 time=50849 us)
    100   FILTER  (cr=2 pr=0 pw=0 time=229 us)
    100    INDEX RANGE SCAN SYS_C00167097 (cr=2 pr=0 pw=0 time=25 us)(object id 220499)


E para o "delete cascade" temos a mesma deleção usando PARENT_ID só que desta vez com uso do índice recem criado, e com uma quantidade muito inferior de LIOs (302) e por consequencia um custo de execução muito menor em relação ao plano de execução do teste anterior com mais 1.5 milhões de LIOs. Diminuindo de 7.54 para 0.04 o tempo de execução do delete na tabela filha.


delete from "DBA_RAFAEL"."BIGTAB2"
where
 "PARENT_ID" = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.04       0.04          0        302       6226        1500
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.04       0.04          0        302       6226        1500


Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)


Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  BIGTAB2 (cr=302 pr=0 pw=0 time=42640 us)
   1500   INDEX RANGE SCAN SYS_C00167103 (cr=302 pr=0 pw=0 time=3648 us)(object id 220502)



Neste exemplo temos então as evidências da melhoria de performance para menos de 1% do tempo de execução anterior.

quarta-feira, 18 de abril de 2012

DBAs vs Developers vs Clients


Esta semana vou comentar sobre 2 artigos que eu recomendo. Quando li eles tive aquela sensação de "eu sempre quis dizer isto".

Os artigos não são muito técnicos, aconselho tanto para DBAs, quanto para desenvolvedores, programadores, arquitetos de sistemas, gerentes/coordenadores de TI, etc.

Trata-se do artigo "Crossing the Chasm Between Developers and DBAs", escrito por Mike Cuppett, (em português: Cruzando o abismo entre Desenvolvedores e DBAs).

Ele aborda o quão problemática a visão estreita do problema e a guerra de egos pode se tornar do ponto de vista de quem realmente importa, que é o cliente.

E o fundador do Pythian Group, Paul Vallée, cita esta questão de egos em seu artigo "The seven deadly habits of a DBA... and how to cure them" (em português: Os sete hábitos mortais de um DBA... e como curá-los). Que é uma leitura recomendadíssima para DBAs e gerentes de times de DBAs. Ele explica muito bem a questão do artigo anterior e como o ponto de vista mais aberto é importante neste caso. Faço aqui uma tradução do hábito 5:

"Habit #5. O JOGO DA CULPA: Não olhe para mim, é culpa do desenvolvedor que este SQL esteja em produção
Alguns DBAs tem uma real mentalidade de 'nós contra eles' quando se trata de desenvolvedores e suas organizações. Eles não se veem como facilitadores ajudando os desenvolvedores a desenvolverem código de qualidade, do ponto de vista de banco de dados, mas se veem como guardiões que previnem que código de baixa qualidade entre em produção. Isto pode parecer como uma questão de semântica, mas um relacionamento conflituoso entre desenvolvedores e DBAs resulta em uma falta de iniciativa do desenvolvedor e uma lentidão significativa nos ciclos de release"

Já presenciei colegas DBAs sendo solicitados para atividades de análise de performance e para que ajudassem na melhoria do tempo de resposta de algumas consultas, e estes prontamente responderam "nós não fazemos isto, esta fora do nosso escopo". Deixando as questões de 'custo vs escopo' de lado, eu até acho que em muitos casos este tipo de atitude seja devido a falta de conhecimento sobre performance e até mesmo o medo de aprender, mas estas também são barreiras que devem ser vencidas por qualquer técnico.

E eu diria que este hábito de "nós contra eles" é terrível para qualquer profissional, seja DBA ou análista de sistemas, ou storage, ou backup, ou network, etc.

E para casos de empresas que trabalham com "Outsourcing Services"? As coisas se complicam ainda mais quando temos diversos fornecedores. A muito tempo já venho me perguntando como resolver a situação neste ultimo caso? Como coordenar os trabalhos, e conflitos, das diferentes empresas suportando um mesmo ambiente? 


É claro que é fator chave todos os envolvidos serem dotados de muita paciência e habilitade de comunicação. Mas nada supera a necessidade de haver uma curiosidade natural por resolução de problemas, que é o que nos motiva e cria esta ânsia de não descansar enquanto o problema estiver resolvido.



Concluíndo, acho que estes dois artigos são do tipo que deveriamos ler periódicamente, para nos políciar contra os vícios sociais no trabalho.

quarta-feira, 4 de abril de 2012

JPA/Hibernate e Sequences: script para associar sequences e colunas

Você sabe como os sitemas de bancos de dados Oracle fazem para associar sequences e colunas?
Bom, infelizmente ele não faz.

O Oracle Database ainda não possui a propriedade de coluna "AUTO_INCREMENT" tal como do MySQL ou SERIAL do PostreSQL.

Uma opção seria fazer uso de triggers, para colocar o valor crescente nas tabelas de identificadores tal como exemplificado pelo Tim Hall em seu blog.

Mas e se o projeto já esta em andamento a alguns anos?
E se ele for construido em Java com uso de algum framework de persistência, tais como JPA e Hibernate (xodó dos programadores)?

Bom, depois de me debater durante umas 2 a 3 horas procurando por um parser de anotações Java/JPA especifico para sequences resolvi escrever meu próprio parser para tornar a minha vida mais fácil, e talvez a sua também.

O script "HbtParse_SeqToDB.pl" foi escrito em Perl e esta disponível no Google code, no projeto que criei recentemente chamado DB-Handy-Code.


Descrição: O script primeiramente se conecta ao banco de dados e cria no esquema do usuário fornecido uma tabela auxiliar chamada AUX_SEQUENCE_TAB_COL (caso ela não exista). Esta tabela possui 3 colunas (sequence_name, table_name e column_name) onde o nome da sequencia é uma chave primária e não pode ser repetida.
Após isto ela efetua uma varredura (do tipo 'find') de toda a árvore de diretórios a partir do <source directory> especificado buscando por arquivos ".java". Em cada arquivo ele efetua um parse por anotações específicas e caso cosiga efetuar a associação entre "Sequencia, Tabela e Coluna" ele insere esta associação na tabela auxiliar AUX_SEQUENCE_TAB_COL. Caso a sequence já exista na tabela auxiliar ele apenas imprime um "warning" na saída padrão, informando o nome do arquivo.

Após ter os dados das associações na tabela auxiliar é possível efetuar atividades de manutenção, tais como o reset dos valores das sequences para o valor máximo das colunas. Escrevi a algum tempo o código de uma stored procedure para tal, disponível aqui.


Limitações da versão 0.1a do HbtParse_SeqToDB.pl:

  1. Ela atualmente só reconhece anotações JPA em código fonte ".java", mas tenho intenção de extende-lo para elementos de XML no futuro
  2. É necessário que a anotação  @Column  esteja após a anotação @Id  e antes da declaração do atributo/método da entidade, para que seja possível fazer a associação
  3. Atualmente  só reconhece 1 sequence em cada arquivo fonte, e este arquivo pode conter apenas 1 entidade e 1 atributo identificador (o que não deixa de ser uma boa prática de programação Java)
  4. Atualmente suporta apenas banco de dados Oracle
  5. É necessário Perl v5.10 ou superior
  6. São necessários os seguintes módulos Perl
    • File::Find::Rule
    • Getopt::Long
    • DBI
    • DBD::Oracle
Como usar:
HbtParse_SeqToDB.pl -d <src dir> -B <src db> -U <db user> [-P <db pwd> -t <parser type> -h]
O script possui uma opção "-h" que exibe um help com a sintaxe.
Exemplo de uso:
[henchen@obelix ~]$ perl HbtParse_SeqToDB.pl -d /src-java -B dbi:Oracle://localhost:1521/ORCL -U scott -P tiger 



sábado, 24 de março de 2012

Guia rápido para uso do RMAN - Don't Panic!

Você continua usando exp e expdp como ferramentas de backup? Porque?

Toda vez que assumo um ambiente de banco legado e me deparo com um backup sendo feito via Export (exp) ou Data Pump Export (expdp) ou ainda pior, "cold backup via shell script".
E toda vez eu sou obrigado a perguntar "porque vocês estão fazendo seus backups com export"? As respostas são das mais variadas possíveis, entre "não sei" e entre "não fomos nós que desenvolvemos a rotina", mas fico atônito quanto ouço respostas do tipo "é mais fácil do que o RMAN".

Esta semana isto ocorreu novamente. Bom, sinto em lhes informar, mas o uso do RMAN (Recovery Manager) é tão fácil e prático que, na minha opinião, a unica resposta válida para não usa-lo seria "eu não sei como se usa, por isto tenho medo de usar".

Além disto tal como consta na documentação do Oracle Database, o export (logical backup) pode ser utilizado como estratégia complementar para proteção dos dados, mas jamais como sendo a principal estratégia.

Admitir que não sabemos algo não é uma deficiência, afial de contas ninguém nasce sabendo tudo, mas continuar em uma zona de conforto e não querer aprender a usar uma ferramenta tão importante, neste caso em particular eu considero muito arriscado.

Por isto como eu disse no começo "DON'T PANIC", vamos desmistificar este dogma.

Para começar é preciso ter em mente que a principal função de uma "ferramenta de backup" não é apenas garantir um backup fácil e rápido, mas principalmente é garantir uma restauração rápida, eficiente e consistente dos dados perdidos.

Como é que você tem garantido a consistência dos seus dados usando "expdp"? Em 99% dos casos eu vejo scripts/rotinas de backup com "expdp" que não usam os parâmetros corretos para garantir a consistência da exportação de dados.

Então eis os 5 passos para começar a usar o Oracle Recovery Manager o quanto antes, afinal de contas, antes tarde do que nunca.

Prefácio

Para que seja possível efetuar o backup do banco de forma "online", primeiramente é necessário ativar o "modo archivelog" para que os redologs sejam armazenados e nenhuma transação seja perdida durante a execução do backup. Caso você ainda não tenha feito isto deve se reiniciar a instância de banco de dados da seguinte forma:

sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;


Caso contrário será apenas possível efetuar backups com o banco desligado, veja a documentação a respeito.

Quanto ao destino padrão dos Archivelogs e Backups eu costumo sempre que possível utilizar a Flash Recovery Area (FRA). Para isto deve se configurar os parâmetros "db_recovery_file_dest" (local de destino) e "db_recovery_file_dest_size" (tamanho máximo da área).
Quando o uso da FRA atingir o valor do tamanho máximo da área definido o Oracle irá se encarregar de eliminar backupsets e archivelogs obsoletos automaticamente, veremos como fazer isto manualmente no parte 3 deste artigo.

Como destino da FRA use sempre um disco (ou grupo de discos LUN, LVM, ASM, etc) que de preferencia não seja físicamente o mesmo já utilizado por outro banco de dados vivo, a fim de evitar concorrência durante o backup.

Eu considero como uma política muito simples e eficiente manter de 1 a 15 dias de backup em disco (para acesso mais rápido) e os demais em unidades externas, preferencialmente unidades de fita. Lembre-se que a política de retenção de dados deve ser definida junto ao seu cliente ou gestores de segurança de dados, etc. não tome decisões sem antes consultar e discuti-las com os demais responsáveis pela segurança dos dados.

1) Bem vindo ao maravilhoso mundo novo, entre nele sem medo 

Para se conectar no Recovery  Manager como "sysdba" basta usar o comando "rman target /", mais fácil impossível:

sh> rman target /

Ao se conectar deve aparecer uma mensagem informando "connected to target database: <nomedobanco> (DBID=<numero>)". É importante anotar o número do DBID em um local seguro, pois ele pode ser necessário no futuro.

Existem diversas opções para o comando "rman" caso você use uma opção inválida, tal como "rman help" serão exibidas as opções válidas. Leia a documentação.

Uma vez dentro da ferramenta é possível se fazer tudo que se deseja com relação a backup e recuperação de dados. E o Guia de Referencia possui uma relação de todos os comandos da ferramenta.

2) Configure, se quiser, apenas uma vez

O mais importante que deve ser lembrado é que estas configurações são persistentes, ou seja, uma vez atribuído um determinado valor ele não precisa ser reconfigurado a cada execução.

Para exibir a configuração atual da ferramenta execute:

RMAN> SHOW ALL;

Todos os comandos listados podem ser reexecutados com outros valores para que a alteração seja feita. A princípio basta que sejam reconfigurados os seguintes valores:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Este comando informa o RMAN que ele sempre que um backup for feito ele também deve extrair um backup dos arquivos "controlfile" e "spfile" automaticamente ao fim do backup.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

Este comando define a política de retenção de backups para uma janela de 2 dias. Veremos que esta configuração torna muito fácil a remoção dos backups antigos.
Ao definir o tamanho da janela deve se levar em conta o espaço disponível para armazenamento dos backups, portanto ajuste de acordo com o espaço que houver disponível no seu ambiente. 
Caso você não queira utilizar a Flash Recovery Area para armazenar seus backups é possível se alterar o destino padrão dos backups através do comando:

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/adirectory/anotherdirectory/%U'

Acho que não preciso dizer que deve se ler a documentação para um entendimento completo dos parâmetros e opções de configuração.

3) Executando um backup simples em apenas 1 linha, ou talvez 2

Agora, toda vez que se desejar fazer um backup, basta executar um simples comando:

RMAN> BACKUP DATABASE;

Sinceramente me diga, quando isto é mais complicado do que um expdp e todos aqueles parametros: FULL, SCHEMAS, FLASHBACK_SCN ou FLASHBACK_TIME (muitos esquecem destes 2 para manter os dados consistentes, deixando o "backup lógico" Completamente inútil), DUMP_FILE, LOG_FILE, etc?

Este backup será dividido em um ou mais "backup sets" (guarde bem este nome), que são conjuntos de datafiles, archivelogs, etc., por padrão cada "backupset" é formado por 1 "backup piece" que consiste em um formato proprietário de arquivo. A grosso modo, tendo o(s) backupset(s) e os archivelogs é possível restaurar o banco para qualquer "ponto no tempo" da vida do banco de dados.

Tudo bem, vou ser sincero, eu menti um pouco, é que minhas rotinas de backup normalmente consistem em 3 linhas:

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
RMAN> DELETE NOPROMPT OBSOLETE;
RMAN> SQL "alter database backup controlfile to trace as /adirectory/controlfile.txt"

A opção "incremental level 0" eu costumo usar sempre, o que me permite decidir se quero tornar os backups parte de uma política de backup incremental do dia para a noite. Não vou me aprofundar neste assunto no momento, caso não esteja familiarizado busque na documentação.

Já o comando "delete noprompt obsolete" é para remover qualquer backup e archivelog que não se enquadre na política de retenção. Como falei na etapa 2, a configuração de uma janela de retenção torna muito fácil a limpeza dos backups antigos, ou seja, todos os backups e archivelogs que já estejam "obsoletos" (fora da janela de retenção) são apagados com 1 simples comando.

E o SQL "alter database backup controlfile to trace" gera uma versão em texto, caso o controlfile precise ser recriado manualmente. Confie em mim, isto pode ocorrer.

Uma regra que é valida sempre em qualquer situação tanto nas áreas de TI quanto na vida real, quanto mais simples e claro for um sistema mais fácil de entende-lo, mante-lo e menos sujeita a falhas ele se torna. Tente manter suas rotinas de backup sempre o mais simples e diretas possíveis configurações de destinos, formatação, parallelismo, etc pode ser feita com o comando "CONFIGURE" e persistidas evitando scripts imensos para backups padronizados.

Outra recomendação importante, não adianta fazer backup dos archivelogs para dentro da própria Flash Recovery Area. Se ela já é o destino dos archivelogs, para que copia-los para onde eles já estão?
Faz sentido sim fazer backup dos archivelogs para um outro destino em disco, ou servidor de arquivos remoto, ou unidade de fita, etc. Os archivelogs já fazem parte da política de backup, por tanto não faça "backups de backups" sem a devida necessidade.

Duas recomendação simples de performance para o backup:
  • Evitar que o destino dos backup seja o mesmo disco onde estão os dados, evitando um "gargalo" tal como ja foi dito;
  • Evitar que o destino inicial do backup seja uma unidade remota (ex. NFS) em uma rede de banda estreita e/ou tráfego intenso; 
A ideia é sempre fazer o backup do banco de forma rápida, para evitar um longo tempo de contenção de leitura dos datafiles. Eu costumo fazer um primeiro backup do database (ou seja, datafiles, controlfile e spfile) em disco, tal como ilustrei acima, e efetuar backup dos backupsets e archivelogs para unidades externas (fita ou NFS) usando os comandos "backup backupset" e "backup archivelog" (não vou abordar este script aqui pois ele depende da mídia de destino do backup), sendo que se for fazer via NFS recomendo que seja através de uma rede de banda Gigabit (ou superior) dedicada exclusivamente para tráfego de backup.

E por fim, para verificar se temos backup de todos os arquivos do database podemos utilizar o comando:

RMAN> REPORT NEED BACKUP;


4) Encontre e valide backups sem complicações

Para listar seus backups você basicamente irá utilizar o comando "LIST" em suas diversas variações. Um resumo geral dos seus backups pode ser visto com o comando:

RMAN> LIST BACKUP SUMMARY;

Já para obter mais detalhes sobre o conteúdo e local dos seus backups execute o comando:

RMAN> LIST BACKUP;
Este comando lhe trará o valor descrito como "Piece Name", que indica o nome do arquivo onde se encontra aquele "pedaço" do backup. Outro dado importante nesta relação é o valor "Tag", pois varias operações podem ser feitas utilizando-se este valor, tais como validação, remoção e restore.

O RMAN também é uma excelente ferramenta para encontrar possíveis corruções do banco de dados, veja a documentação e procure a referencia dos comandos "VALIDATE DATABASE" e "BACKUP DATABASE ... VALIDATE".

Mas tendo em mente que a função principal é garantir um restore de dados é considerada uma boa prática testar periódicamten se os backups não estão corrompidos.
Assim usamos a opção VALIDATE tal como no comando abaixo:

RMAN> RESTORE DATABASE VALIDATE;

Este comando irá efetuar uma leitura completa dos backupsets necessários para restaurar todos os datafiles, controlfiles e spfiles do banco, sem escreve-los em disco.
Mas também é uma boa prática de tempos em tempos se efetuar restores e recover completo do banco de dados em um servidor/host, assim testando a validade do seu backup e deixando você mais preparado caso o piór venha a acontecer. Para isto recomendo a leitura do seguinte documento.

5) RMAN na hora H - Restore e recover

Ninguém deseja perder seus dados, mas é um fato inegável que mais cedo ou mais tarde isto pode acontecer.

O Recovery Manager possui diversas opções de restore/recover de falhas de mídia, com ele é possível restaurar desde 1 unico bloco de dados corrompido, um datafile perdido, até o banco de dados inteiro. É importante ter em mente que se houve apenas a corrupção de 1 unico bloco, não existe necessidade de se restaurar todo o banco de dados. Por isto é importante que se leia atentamente a documentação sobre "Preparação e Planejamento da Estratégia de Restore e Recover" e também as informações a respeito de BLOCKRECOVER.

Para demonstrar o processo de restauração ilustraremos um caso muito ruim, não o pior, mas um caso de perda total do banco de dados. Lembramos aqui que existen dezenas se não centenas ou milhares de cenários possíveis para um restore de banco de dados, por isto é importante estudar e conhecer todas as funcionalidades para que as decisões corretas sejam tomadas no momento de se efetuar a restauração.

Cenário: digamos que tenha sido perdido o storage, ou os discos, onde estavam todos os arquivos que compõe o database (datafiles, controlfiles e redologs). Apenas se salvando o ORACLE_HOME, o SPFILE (que estava no ORACLE_HOME) e a Flash Recovery Area

Supondo que o destino original dos arquivos já esteja pronto para recebe-lo novamente (filesystems formatados e montados, ou grupo ASM criado e montado, etc.)

Então para restaurarmos todo o banco de dados ao seu destino original, devemos abrir uma sessão do RMAN e podemos executar os seguintes comandos, aqui explicados passo a passo:

RMAN> SET DBID <número do DBID>;
RMAN> STARTUP FORCE NOMOUNT;
Uma vez que a instância esteja inicializada o RMAN saberá onde encontrar a Flash Recovery Area pelo parametro "db_recovery_file_dest".

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; 
Este comando efetua o restore do controlfile, pois é dentro dele que esta o repositório do RMAN que contém o catalogo de todos os backups, e sem ele a ferramenta não saberia  quais backupsets precisam ser restaurados, ou onde eles se encontram.
RMAN> ALTER DATABASE MOUNT; 
Com a montagem do banco (leitura do controlfile) permitimos que o RMAN tome conhecimento do repositório de catalogo dos backups. 
RMAN> RESTORE DATABASE PREVIEW;
Este comando lista todos os backupsets que serão utilizados para restaurar e recuperar o banco de dados por completo. Pode ser útil checar se todos os backup pieces e archivelogs estão disponíveis antes de iniciar o processo de recuperação.

RMAN> RESTORE DATABASE;
Efetua o restore completo dos datafiles que não estejam em seu destino e com o SCN apropriado. 

RMAN> RECOVER DATABASE;
É a ação que atualiza/sincroniza os datafiles para manter a integridade dos dados. Podemos dizer a grosso modo que com a execução do recover as transações são "refeitas"  em todo o banco, aplicando os arquivelogs necessários (e redologs se ainda existirem ao menos 1 cópia disponível) até o ultimo estado válido conhecido, fazendo com que o banco de dados esteja apto a ser aberto. 
Caso estejam faltando archivelogs e/ou apenas os redologs dizemos que este é um "recover incompleto", pois algumas transações serão perdidas. Neste caso é importante informar aos responsáveis pelas aplicações até que momento foi possível se recuperar os dados, pois talvez algumas atividades tenham que ser refeitas por parte dos usuários.

RMAN> ALTER DATABASE OPEN RESETLOGS; 
Caso os redologs tenham sido perdidos (que na minha experiência é o caso mais comum) o banco de dados é aberto novamente com o uso do "OPENRESETLOGS".
Após isto o procedimento de Restauração e Recuperação completa do banco é finalizado. E os usuários poderão voltar a utilizar as aplicações normalmente.


Epílogo

O repositório principal do catalogo de recuperação (RMAN REPOSITORY) fica dentro do próprio "control file" do database, e por tanto não é necessário ficar inventando formas de catalogar e indexar seus arquivos de dump ou cópias de datafiles dos cold backups com scripts mirabolantes.

Mas de qualquer forma é aconselhável criar um repositório externo, em uma base de dados Oracle que de preferencia esteja em outro servidor, para isto leia a documentação.


Para se aprofundar no assunto, arregace as mangas e leia os manuais:


PS.: Este uso básico é válido para diferentes versões do Oracle Database, mas recomendo que se leia sempre a documentação específica da versão em uso. Neste artigo nos baseamos na versão 10gR2.

terça-feira, 10 de janeiro de 2012

Removendo chek constraints duplicadas em bases Oracle

Talvez você também esteja com problemas devido a check constraints do tipo "<coluna> not null" estarem duplicadas no seu banco.

No meu caso específico eu descobri que este problema ocorreu pois o time de "análise" utiliza uma ferramenta de UML (Enterprise Architect) para modelar um banco relacional quando deveria estar utilizando uma ferramenta de ER. A ferramenta definiu as colunas como não nulas, mas também criou check constraints para a forçar a restrição. Acontece que o Oracle (ao menos até a versão 10.2) não valida se constraints do tipo "check" possuem as mesmas clausulas de restrição.

Enfim vamos ao que interessa...

Para solucionar o problema escrevi o código de uma procedure que efetua a remoção das 2, ou mais, constraints do tipo "not null" na mesma coluna e por fim altera a tabela para redefinir a coluna, esta redefinição precisa ser feita pois com a remoção das contraints a coluna passaria a ser "nullable", por isto não basta só remover as constraints.

Antes da procedure de remoção das duplicações em si, é necessário também criar a procedure abaixo, para converter a coluna "search_condition" da view "all_constraints" de LONG para VARCHAR2, para que seja possível efetuar as comparações dos strings.


CREATE OR REPLACE FUNCTION  f_get_cons_check( p_owner VARCHAR2,p_constraint VARCHAR2)
    RETURN VARCHAR2
AS
   l_cursor    INTEGER DEFAULT dbms_sql.open_cursor;
   l_n         NUMBER;
   l_long_val  VARCHAR2(4000);
   l_long_len  NUMBER;
   l_buflen    NUMBER := 4000;
   l_curpos    NUMBER := 0;

BEGIN
   dbms_sql.parse( l_cursor,
        'SELECT search_condition 
           FROM all_constraints
          WHERE owner = :x
            AND constraint_name = :y',
        dbms_sql.native );
   dbms_sql.bind_variable( l_cursor, ':x', p_owner );
   dbms_sql.bind_variable( l_cursor, ':y', p_constraint );
   dbms_sql.define_column_long(l_cursor, 1);
   l_n := dbms_sql.execute(l_cursor);
   IF (dbms_sql.fetch_rows(l_cursor)>0)
   THEN
      dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , l_long_val, l_long_len );
   END IF;
   dbms_sql.close_cursor(l_cursor);
   RETURN l_long_val;
END f_get_cons_check;

Esta função é baseada no código do Thomas Kyte publicada na issue "Long to Varchar2 conversion" do "Ask Tom"


E então temos a procedure para remoção das constraints duplicadas. Observe que estou apenas imprimindo os comandos de DML para remoção das constraints e alteração da tabela, mas nada impede que se você se sentir seguro altere estas chamadas para um "execute immediate".



PROCEDURE p_remove_dup_notnull_cons(p_schema VARCHAR2)
AS
  TYPE dup_constraints_type  IS RECORD
    ( owner            all_constraints.owner%TYPE,
      table_name       all_constraints.table_name%TYPE,
      column_name      all_cons_columns.column_name%TYPE,
      constraint_name  all_constraints.constraint_name%TYPE,
      cnt_check        NUMBER,
      ord              NUMBER);
      
  TYPE ref_cursor IS REF CURSOR RETURN dup_constraints_type;
  
  c_dup_constraints ref_cursor;
  
  TYPE t_dup_constraints IS TABLE OF dup_constraints_type
      INDEX BY PLS_INTEGER;
  
  l_dup_constraints t_dup_constraints;
  
  l_row             PLS_INTEGER;
  v_cnt             INTEGER;
  v_ddl_modify_coln VARCHAR2(4000);
  
  
BEGIN

  OPEN  c_dup_constraints FOR 
        SELECT owner, table_name, column_name, constraint_name, cnt_check, ord
          FROM( SELECT owner, table_name, column_name, constraint_name, 
                       count(*) over (partition by owner, table_name, column_name) as cnt_check,
                       row_number() over (partition by owner, table_name, column_name order by last_change) as ord
                  FROM (SELECT owner, table_name, constraint_name, last_change, trim('"' FROM substr(cons_check,1,instr(cons_check,' ')-1)) as column_name
                          FROM (SELECT owner, table_name, constraint_name, last_change, f_get_cons_check( owner, constraint_name) as cons_check
                                  FROM all_constraints
                                 WHERE owner = p_schema
                                   AND constraint_type IN ('C','?'))
                          WHERE cons_check LIKE '%IS NOT NULL'))
              --WHERE cnt_check > 1
            ORDER BY table_name, column_name, ord;

  LOOP
    FETCH c_dup_constraints BULK COLLECT INTO l_dup_constraints LIMIT 1000;
    DBMS_OUTPUT.PUT_LINE('-- ' || l_dup_constraints.COUNT || ' DUPLICATED CONSTRAINTS FOUND');
    EXIT WHEN l_dup_constraints.COUNT = 0;
    l_row := l_dup_constraints.FIRST;
    WHILE l_row IS NOT NULL
    LOOP
       IF l_dup_constraints(l_row).ord = 1
       THEN
         -- GERA DDL PARA REDEFINIR A COLUNA COMO "NOT NULL" APÓS A REMOÇÃO DAS CONSTRAINTS
         v_ddl_modify_coln := f_ddl_mod_tab_column(l_dup_constraints(l_row).owner,
                                                   l_dup_constraints(l_row).table_name,
                                                   l_dup_constraints(l_row).column_name,
                                                   ' NOT NULL ENABLE');
       END IF;
       
       -- DROPA CONSTRAINT DUPLICADA
       DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || l_dup_constraints(l_row).owner || 
                          '.' || l_dup_constraints(l_row).table_name ||
                          ' DROP CONSTRAINT ' || l_dup_constraints(l_row).constraint_name || ';');
       
       IF l_dup_constraints(l_row).ord = l_dup_constraints(l_row).cnt_check
       THEN
         -- EXECUTA DDL MODIFY PARA REDEFINIR A COLUNA COMO "NOT NULL"
         DBMS_OUTPUT.PUT_LINE(v_ddl_modify_coln || ';');
       END IF;
       l_row := l_dup_constraints.NEXT(l_row);
    END LOOP;
  
  END LOOP;

  IF c_dup_constraints%ISOPEN
  THEN
    CLOSE c_dup_constraints;
  END IF;

EXCEPTION 
WHEN OTHERS THEN
    IF c_dup_constraints%ISOPEN
    THEN
      CLOSE c_dup_constraints;
    END IF;
    RAISE;
END p_remove_dup_notnull_cons;




Sinta-se livre para utilizar e modificar estas rotinas como bem entender, por sua conta e risco. Mas se for publica-la por favor referencie minha autoria.