Páginas

29 de nov. de 2012

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 tabelaFazer 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
  

20 comentários:

  1. Fábio, primeiramente, parabéns pelo post. Segundo, como faço pra saber o valor atual do undo_retention?

    ResponderExcluir
    Respostas
    1. Márcio, obrigado pelo comentário. Para saber o valor atual do UNDO_RETENTION, digite no sqlplus ou sqldeveloper, o comando abaixo:
      SHOW PARAMETER UNDO_RETENTION

      Excluir
  2. Ola Fábio, a minha dúvida é como saber quanto de espaço adicional é necessário para implementar esta configuração? Obrigada, Camilla

    ResponderExcluir
    Respostas
    1. Camila, ótima pergunta! Acabei de incluir no artigo um SELECT que poderá ser executado para calcular o espaço que o UNDO Tablespace deverá ocupar.

      Excluir
  3. Excelente postagem !

    Parabéns mais uma vez.

    ResponderExcluir
  4. Olá Fabio,

    Tenho um banco oracle 11g em linux, em produção na empresa que trabalho e aconteceu um imprevisto....

    Eu precisei acertar o horário do servidor e cometi um erro.... eu troquei o mês pelo ano... ficando com a data adiantada... exemplo:

    era pra ser 12/08/2013 e ficou 12/08/2013

    Notei rapidamente meu erro... e corrigi a data ....

    Porem... apos isso.... notei que a tablespace UNDOTBS01, começou a crescer sem parar.....

    ela tinha 900 mb e hoje esta em 03Gb....

    Eu acredito, diante de muita leitura, googlada que dei... que o problema pode estar relacionado à view V$UNDOSTAT ...

    fiz o select :

    SELECT TO_CHAR (begin_time, 'mm/dd/yyyy hh24:mi:ss') BEGIN,
    TO_CHAR (end_time, 'mm/dd/yyyy hh24:mi:ss') END, undoblks, unxpstealcnt,
    unxpblkrelcnt, unxpblkreucnt, ssolderrcnt
    FROM v$undostat

    e o resultado foi:

    1 12/08/2013 15:41:56 12/08/2013 15:48:43 404447 0 0 0 0
    2 12/04/2013 15:51:56 12/08/2013 15:41:56 0 0 0 0 0

    Eu criei outra tablespace, a UNDOTBS02, com 01Gb e coloquei ela como default do banco.... e aparentemente ela esta sendo populada, não chegou ainda a extender ... esta uns 40% ocupada.... mas tbm esta crescendo aos poucos...

    Eu acredito que o meu problema esta ligado a isso....

    A minha pergunta, seria:

    Se eu baixar o banco.... ao subir novamente... ele vai recriar a view e atualizar com a informação correta, resolvendo assim o meu problema !! ???

    Pelo que li... essas informações ficam em memoria... e qdo baixa o banco e sobe ele, essas informações são recriadas...

    A minha janela de manutenção eh mto apertada .. e só terei amanha de manha (25/08/2013) para fazer isso..

    Eu agradeço imensamente se responder me der essa força ..

    Abraço, e desde ja mto obrigado...


    //:o)

    Carlos Noris

    ResponderExcluir
    Respostas
    1. Carlos, a visão não tem problema algum, ela apenas mostra estatísticas dos seus tablespaces de UNDO. Eu nunca passei por situação semelhante, mas sei que nestes erros data você pode até mesmo corromper o BD e vc teve sorte disso não ter acontecido.

      Antes de fazer qq coisa, eu sugiro que você faça uma pesquisa no MOS ou continue pesquisando na internet para que vc encontre a solução de alguém que já teve o mesmo problema! O que você poderá fazer depois é excluir a UNDOTBS01, mas vc só conseguirá fazer isso no momento em que nada estiver sendo usado lá (provavelmente após o shutdown vc já conseguirá apagá-la).

      Excluir
    2. Fabio, já procurei um monte,.. e eu tentei colocar a UNDOTBS01 offline mas o sistema xiou...., deveria ter algum processo ainda nesta tablespace UNDO..., recoloquei ela em online e boa...!! Eu estou com receio, por que como a tablespace que nao esta mais como default ainda esta lotada, eu fico com medo de perder alguma informação do banco gerando inconsistencias..., mas acredito que nao... eh so um receio...!!! após o shutdown e o startup, vou dar um drop nela.. e ver o que acontece kkkk... mas morrendo de medo heheeh !!!!

      Excluir
    3. CCNoris, nao acredito que vc vá perder informações, os problemas são outros (veja os artigos http://certificacaobd.com.br/2012/10/19/oracle-alterando-para-horario-de-verao/ e http://profissionaloracle.com.br/blogs/rodrigoalmeida/2009/02/14/horario-de-verao-os-impactos-no-banco-de-dados-oracle/).

      Eu tentei simular em um lab adiantando a data e depois retrocendo a data e não tive problema algum, mas isso foi em uma máquina de laboratório!!!!

      []s

      Excluir
  5. Boa noite Fábio, uma tablespace UNDO criada não como bigfile e com 5 datafiles adicionais em relação ao post sobre tablespaces certamente nao é a melhor pratica, vendo isso e outros notes sobre bigfile tablespace e undo tablespace a melhor forma de gerencia-la seria criar outra mas como bigfile em um tamanho que atende a necessidade do banco e eliminando essa e seus datafiles adicionais ?

    ResponderExcluir
    Respostas
    1. Paulo, se a versão do BD for 11G, eu aconselharia sim a criar um novo tablespace de UNDO como bigfile tablespace por causa das vantagens que menciono no artigo http://www.fabioprado.net/2013/11/boas-praticas-para-gerenciar-tablespaces.html.

      []s

      Excluir
  6. Fabio, em seu artigo (http://www.fabioprado.net/2012/11/configurando-o-undo-tablespace.html ) voce diz que "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". Que fatores identificam o valor desse uso medio? Obrigada!.

    ResponderExcluir
    Respostas
    1. Ótima pergunta! Não conheço algo que te mostre isso já calculado, mas você pode obter isso executando múltiplas vezes o SQL que mostro no final deste artigo (criando, por exemplo, um job periódico para guardar estas informações em uma tabela), e depois calculando o valor médio do retorno da coluna "ACTUAL UNDO SIZE [MByte]", ok?

      Excluir
  7. Fabio. estou tendo o erro de Snapshot Muito Antigo por Causa de Limite de Tablespace
    Erro de Snapshot Muito Antigo detectado: SQL ID bv4s5sa2zdavv, Snapshot SCN 0x0000.1ff2f6e6, SCN Recente 0x0000.1ff34af2, Tablespace de Undo UNDOTBS1, Retenção de Undo Atual 1244.
    Minha tablespace UNDO tem 10g de tamanho. Desses tenho usado 4,4%.
    O parametro de undo_retention está configurado com 1200. Noguarantee.
    O recurso de FlashBack está OFF.
    Meu datafile de Undo tem tamanho de 10240,00MB. Desses tenho usado 2,31%. Podendo extender até os 32G. Valor do auto incremento 5120MB.
    De acordo com a coluna NEEDED UNDO SIZE[Mbyte] da instrução SQL acima , devo aumentar para 59,671875Mbyte.
    Porque preciso crescer tanto essa tablespace se tenho tanto espaco livre. Como posso estar tendo espaço insuficiente de armazenamento para causar esse erro?

    Obrigada pela atenção!

    ResponderExcluir
    Respostas
    1. Nayana, o cálculo verifica a qtde. de blocos de undo gerados no momento da execução do SQL (qtde de blocos gerados neste momento * tempo de retenção configurado * tamanho do bloco).

      O resultado considera que no período total de retenção, em cada segundo, você terá a mesma qtde. de blocos sendo gerados, o que pode não ser verdade, se por exemplo você configurar retenção de 1 semana em um BD que só trabalha no período comercial e dias úteis, ok? Neste caso o UNDO necessário calculado é muito maior do que realmente você precisa.

      Para evitar o erro "snapshot muito antigo...", de acordo com o que você me descreveu, acho que só falta configurar o tablespace de UNDO para RETENTION GUARANTEE, ok?

      Excluir
  8. Boa tarde Fábio.

    O que você acha de configurar undo tablespace com blocos de 16k ou 32k?

    ResponderExcluir
    Respostas
    1. UNDO Tablespace tem muita gravação, eu jamais configuraria ele com um tamanho de bloco maior (16 ou 32). Também não sei se é possível fazer isso nele, preciso pesquisar e/ou testar.

      []s

      Excluir
    2. Fiz um teste, é possível sim criar também Undo Tablespaces com tamanhos de blocos diferentes do default do BD.

      Excluir
  9. Olá mestre,

    Eu gostei bastante da query apresentada, mas no meus testes eu reparei que a query leva em conta o tamanho de todas as tablespaces de undo presentes no banco e não apenas a tablespace de undo em uso. Se alguém for utilizar recomendo colocar o nome da tablespace de undo que está sendo utilizada no momento caso o seu banco tenha mais de uma tbs de undo
    Depois do WHERE c.contents = 'UNDO' coloque
    AND C.TABLESPACE_NAME = 'nome_da_tbsundo_aqui'.

    ResponderExcluir
    Respostas
    1. Danilo, obrigado pela informação adicional!

      Qdo montei o SQL eu tinha 1 só tablespace de UNDO, e neste caso o SQL funcionava bem, mas vc tem razão, para quem possui mais de 1, é necessário adicionar o filtro pelo nome do tablespace.

      []s

      Excluir