Undo retention no Oracle: como dimensionar antes do incidente

O erro ORA-01555 snapshot too old aparece com frequência logo depois que alguém tenta usar Flashback Query para recuperar dados apagados por engano. Quando isso acontece, o instinto de quem está de plantão é achar que a ferramenta falhou ou que faltou algum parâmetro. Na prática, o problema quase nunca é o Flashback. É o dimensionamento do undo que sustenta ele.

Se você chegou aqui depois de ler sobre o DELETE sem WHERE e a corrida contra o tempo até acionar o DBA, este artigo é a continuação natural: como configurar o ambiente para que, quando o incidente acontecer, o undo ainda esteja lá esperando por você.

O que é undo e por que ele não é redo

Todo banco Oracle mantém duas estruturas que, apesar do nome parecido, resolvem problemas completamente diferentes. O redo log registra as mudanças físicas nos blocos para garantir que, se o banco cair, as transações committadas não se percam. É o mecanismo de durabilidade.

O undo é outra coisa. Ele guarda a imagem anterior dos dados alterados, e serve para finalidades que costumam se confundir na cabeça de quem está começando.

A primeira é rollback de transação: se você faz um UPDATE e decide desfazer com ROLLBACK, é o undo que devolve o valor antigo. A segunda é consistência de leitura: enquanto sua transação roda um SELECT longo, outras sessões continuam alterando os dados, e o Oracle usa o undo para te mostrar a foto do momento em que a consulta começou, não o estado atual da tabela. A terceira é a recuperação via Flashback Query, que na essência é pedir ao Oracle para mostrar a tabela como ela estava há vinte minutos, reconstruindo o passado a partir das imagens guardadas.

Repare que os três usos dependem do undo continuar disponível pelo tempo necessário. Se ele for sobrescrito antes da hora, o rollback ainda funciona, porque é da transação ativa, mas a consistência de leitura de consultas longas e o Flashback quebram.

A tablespace de undo: onde tudo isso mora

Cada instância Oracle usa uma tablespace de undo, e você descobre qual está ativa com:

show parameter undo_tablespace;

Isso retorna o nome da tablespace corrente, normalmente algo como UNDOTBS1. Para ver o tamanho atual e se ela tem autoextend habilitado, a consulta vai direto no dicionário:

select tablespace_name,
       file_name,
       round(bytes/1024/1024/1024, 2) as tamanho_gb,
       autoextensible,
       round(maxbytes/1024/1024/1024, 2) as max_gb
  from dba_data_files
 where tablespace_name = (select value from v$parameter where name = 'undo_tablespace');

Vale conferir isso antes de qualquer promessa de retenção. Prometer oito horas de undo retention numa tablespace pequena com autoextend desligado é a receita mais comum para o incidente que estamos tentando evitar.

Undo retention é um alvo, não uma garantia

O parâmetro undo_retention define, em segundos, por quanto tempo o Oracle tenta preservar as imagens de undo depois que a transação que as gerou terminou.

Só que por padrão isso é um alvo. Se a tablespace de undo estiver sob pressão de espaço, o Oracle vai sobrescrever undo não expirado para dar lugar a transações ativas novas, mesmo que o tempo configurado ainda não tenha se esgotado. O sistema prioriza manter as transações correntes rodando acima de preservar histórico para consulta ou Flashback.

Isso explica boa parte dos casos de ORA-01555. O DBA configurou 3600 segundos de retenção, achou que estava protegido, e na hora do incidente descobriu que o espaço acabou e o Oracle reciclou o undo em bem menos tempo que isso.

Retention guarantee: transformando o alvo em garantia

Existe um jeito de forçar o Oracle a respeitar a retenção configurada, mesmo que isso signifique recusar espaço para transações novas:

alter tablespace undotbs1 retention guarantee;

Com isso ativado, o Oracle não sobrescreve undo dentro da janela de retenção. E o custo é real: se a tablespace de undo ficar sem espaço livre, transações novas começam a falhar com ORA-30036, unable to extend segment in undo tablespace.

Eu prefiro pensar nisso como uma troca explícita. Você está dizendo ao banco para proteger o passado mesmo que isso ameace o presente. É uma decisão de negócio, não só técnica, e precisa vir acompanhada de uma tablespace dimensionada para sustentar a garantia, não só do comando executado e esquecido.

Para reverter:

alter tablespace undotbs1 retention noguarantee;

Como dimensionar undo retention na prática

Número mágico de blog não serve para o seu ambiente. O jeito certo de dimensionar é perguntar ao próprio banco o que ele está entregando hoje, usando v$undostat. Essa view guarda estatísticas em janelas de dez minutos, incluindo a retenção que o Oracle calculou como necessária, a consulta mais longa observada e quantos blocos de undo foram consumidos em cada janela.

select begin_time,
       end_time,
       undoblks,
       maxquerylen,
       tuned_undoretention
  from v$undostat
 order by begin_time desc;

Rodar essa consulta cobrindo pelo menos um ciclo completo de pico (fechamento de mês, horário de maior movimento, batch noturno) mostra qual é a retenção real que o sistema está sustentando e qual foi o consumo de blocos nos períodos de maior geração.

A partir daí, o cálculo de espaço segue uma lógica simples: espaço necessário é a taxa de geração de undo multiplicada pela retenção desejada. Uma forma prática de estimar a taxa é pegar o pico de undoblks das janelas mais carregadas e multiplicar pelo tamanho do bloco:

select max(undoblks) as pico_blocos_10min,
       (select value from v$parameter where name = 'db_block_size') as tamanho_bloco,
       max(undoblks) * (select value from v$parameter where name = 'db_block_size')
         / 1024 / 1024 as pico_mb_10min
  from v$undostat;

Isso dá o volume gerado no pior período de dez minutos observado. Multiplique pelo número de janelas de dez minutos que cabem na retenção que você quer garantir e some uma margem. Se o pico acontece durante um processo específico, como fechamento fiscal ou carga em lote, vale medir esse período separadamente em vez de confiar na média do dia.

Monitorando o undo com dba_undo_extents

Para acompanhar o que está acontecendo com os extents em tempo real:

select status,
       count(*) as qtd_extents,
       round(sum(bytes)/1024/1024, 2) as mb
  from dba_undo_extents
 group by status;

Três status aparecem aqui, e a diferença entre eles importa. ACTIVE é undo de transações ainda em andamento e não pode ser tocado. UNEXPIRED é undo de transações já commitadas, mas ainda dentro da janela de retenção, e é ele que sustenta consistência de leitura e Flashback. EXPIRED já passou do tempo de retenção e está disponível para reuso.

Quando a proporção de UNEXPIRED cai de forma consistente sob pressão de espaço, é sinal de que a retenção real está bem abaixo do que você configurou.

ORA-01555: duas causas diferentes com o mesmo erro

O erro tem duas origens bem distintas, e misturar as duas no diagnóstico atrasa a solução.

No caso de uma consulta longa rodando (um relatório, uma carga, um batch), o erro acontece porque o undo necessário para reconstruir a foto do início da consulta foi sobrescrito antes dela terminar. A correção passa por aumentar a retenção, garantir espaço na tablespace, ou revisar por que a consulta demora tanto.

No caso do Flashback Query, o gatilho é outro: você está pedindo explicitamente para reconstruir um ponto no passado, e se o undo daquele intervalo já foi reciclado, não tem mais como voltar. Não é falha do comando, é o dado que deixou de existir.

Qual número usar no seu ambiente

Esqueça o padrão de 900 segundos como se fosse suficiente para qualquer ambiente. Ele raramente é.

O dimensionamento correto de undo retention não vem de tabela de boas práticas genérica. Vem de uma pergunta operacional: quanto tempo leva, no seu ambiente, entre um erro humano acontecer e alguém com acesso e autoridade para agir ficar sabendo? Se essa janela é de duas horas, a retenção precisa cobrir duas horas com folga, e a tablespace precisa ter espaço dimensionado para isso, medido com v$undostat e não estimado de cabeça.

Undo, redo e consistência de leitura são o tipo de base que sustenta decisões como essa, e é o que trabalho no curso Oracle Fundamentals.

Marcio Mandarino é DBA Oracle e SQL Server há mais de 20 anos, com passagens por ambientes críticos de varejo, saúde, educação e locação de equipamentos.