Configurando o UNDO Tablespace

ATUALIZADO EM 8/1/19

Olá pessoal,

   No artigo de hoje vou mostrar como configurar o UNDO Tablespace para permitir que ele armazene os dados das transações finalizadas, por mais tempo, permitindo deste modo, aumentar a possibilidade de recuperação de dados, via Flashback.

     O primeiro item que devemos nos preocupar com relação ao UNDO Tablespace é o tamanho dele, pois para garantir leitura consistente nas transações ativas, é necessário que exista espaço suficiente de armazenamento (configurável no nível do tablespace) para evitar o famoso erro ORA-01555 Snapshot Too Old. Outro item que devemos nos preocupar, é que por padrão, ele armazena com segurança os dados das transações já processadas, por até 15 minutos, o que pode não ser suficiente para os casos em que você queira usar Flashback para recuperação de dados..

     Para evitar o erro ORA-01555, o primeiro passo é configurar o UNDO Tablespace com um tamanho que permita comportar os dados de todas as transações em andamento. Uma boa dica, é configurar o tamanho máximo do UNDO tablespace com um valor de no mínimo o dobro do seu uso médio e configurar um valor de auto-incremento (não muito pequeno, para evitar sobrecarga ao estender múltiplas vezes o tablespace, nem muito grande, para não disperdiçar espaço em disco). Em algumas situações usando Flashback Query este erro também poderá ocorrer, de forma inesperada. Neste caso sugiro a leitura do artigo ORA-01555: Snapshot Too Old, When Running Flashback Query para entender melhor a causa e solução do problema.

     O segundo passo, é configurar um valor de retenção (em minutos) de dados em UNDO para um valor maior que 15 minutos, caso exista alguma transação no BD maior que este tempo ou se você precisar efetuar recuperações de dados via Flashback. Se uma transação no BD demorar 30 minutos e se o UNDO Tablespace estiver com o valor de retenção padrão e não houver mais espaço no tablespace, a transação poderá sobrescrever os dados dela própria e neste caso não teremos mais leitura consistente. Para aumentar o tempo de retenção é necessário configurar o parâmetro de instância UNDO_RETENTION.

     Abaixo vou mostrar os passos para configurar um UNDO Tablespace para reter e garantir a retenção dos dados (de transações finalizadas) por 1 semana (configuração normalmente que eu aplico nos BDs de produção que eu administro):

Passo 1: Aumentando o período de retenção

     Para aumentar o período de retenção para guardar os dados pelo período de 1 semana, conecte-se no SQL Plus ou ferramenta de sua preferência, com privilégios de DBA, e altere o valor do parâmetro UNDO_RETENTION para o valor de 604800 (valor em segundos correspondente ao período de 1 semana):

                    ALTER SYSTEM SET UNDO_RETENTION = 604800;

Passo 2: Garantindo o período de retenção

        Só aumentar o período de retenção (realizado no passo anterior) não garante que os dados das transações finalizadas permaneçam no UNDO Tablespace pelo período configurado no parâmetro UNDO_RETENTION. Para garantir a retenção de dados das transações finalizadas, é necessário alterar o tablespace executando o comando abaixo:

                    ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

         Obs.: Substitua undotbs1 pelo nome correspondente do tablespace de UNDO. 

     CUIDADO! Ao implementar as configurações acima, o tamanho do UNDO tablespace aumentará considerávelmente, pois será necessário ter espaço adicional para armazenar mais dados, pelo tempo maior configurado. Quanto maior o tempo de retenção, maior poderá ser o tamanho do UNDO Tablespace. Para calcular o tamanho mínimo necessário para ele, conforme configuração do parâmetro UNDO_RETENTION, execute a instrução SQL abaixo e configure-o de forma que ele possa armazenar o valor da coluna "NEEDED UNDO SIZE [MByte]":


   SELECT     d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
                      SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
                      (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
                     g.undo_block_per_sec) / (1024*1024)
                   "NEEDED UNDO SIZE [MByte]"
  FROM        (SELECT       SUM(a.bytes) undo_size
                       FROM        v$datafile a
                       inner join  v$tablespace b
                              on      a.ts# = b.ts#
                        inner join  dba_tablespaces c
                              on      b.name = c.tablespace_name
                       WHERE       c.contents = 'UNDO'
                      AND         c.status = 'ONLINE') d,
                      v$parameter e,
                      v$parameter f,        
                     ( SELECT   MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
                       FROM    v$undostat) g
  WHERE   e.name = 'undo_retention'
  AND         f.name = 'db_block_size';

CONCLUSÃO

     As dicas deste artigo são simples de aplicar e são valiosas para evitar os erros ORA-01555 Snapshot Too Old, e também, para permitir recuperações lógicas de dados, através de Flashback, caso você tenha, por exemplo, apagado as linhas erradas de uma tabela. Fazer recuperações via Flashback é muito mais fácil e mais rápido do que fazer restore/recover de Backup. 

Por hoje é só! Qualquer dúvida, é só deixar um comentário!

[]s