Alerta Snapshot Too Old

[dejambo]

Ola galera, hoje ao logar no Oracle Enterprise Manager 10g me deparei com o seguinte alerta:

Selecionar tudo

Snapshot Too Old com o erro: Erro de Snapshot Muito Antigo detectado: SQL ID dzrm9y5u18xb6, Snapshot SCN 0x0000.0be5f207, SCN Recente 0x0000.0bf5e8e4, Tablespace de Undo UNDOTBS1, Retenção de Undo Atual 2129. 

O que devo fazer?

Obrigado.

Estagiário em BD Oracle.

[stcoutinho]

Dejambo,

A mensagem de erro SNAPSHOT TOO OLD é um erro clássico que eventualmente pode acontecer em um banco de dados.

Em linhas gerais, ele pode ocorrer em uma seguinte situação:

- Você tem um banco de dados com diversas operações em andamento (ex: UPDATE);
- A foto das informações originais são armazenadas na área de UNDO. Na shared pool ficam as informações
alteradas. Estas informações não se encontram comitadas;
- Você então abre um cursor, que eventualmente irá ler os dados que sofreram atualização;
- Só que o ORACLE precisa fazer uma leitura consistente dos dados. Ele não pode fazer com que você leia
informações atualizadas que ainda não foram comitadas pelas sessões originais;
- Desta forma, entre os registros a serem recuperados pelo seu cursor, o ORACLE aponta para os registros
originais (antes do update não commitado), e que se encontram na UNDO;
- Só que a UNDO não pode indefinidamente manter esta foto originais dos registros. Periodicamente, ela
vai liberando os registros que já foram fisicamente atualizados no banco. Um parâmetro que controla isso
é o UNDO_RETENTION;
- Eventualmente, pode ser que seu cursor demore muito para processar os registros, e quando ele tentar
localizar os registros originais na UNDO, pode ser que eles não se encontrem mais por lá. E é aí que você
acaba recebendo o seu SNAPSHOT TOO OLD;

Bem, este exemplo acima seria uma das situações onde ocorrem o SNAPSHOT. Para tentar resolver o problema, você pode verificar se o parâmetro de UNDO_RETENTION (o tempo em milisegundos que as informações "desnecessárias" são mantidas na UNDO).

O script abaixo (desculpe, peguei ele faz muito tempo em um forum e não disponho mais da URL) pode lhe dar uma dica. Salve ele em um arquivo SQL e execute em seu banco de dados (pelo SQL*PLUS somente) através de um usuário que tenha permissão de acessar o dicionario de dados (ex: com GRANT de DBA);

Selecionar tudo

ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/RRRR HH24:MI:SS'

set serverout on size 1000000
set feedback off
set heading off
set linesize 300;
set trimspool on;

select '--------------------------------------------------------------------' FROM dual;
select 'INFORME DE ESTIMATIVA DE UNDO_RETENTION                             ' FROM dual;
select '--------------------------------------------------------------------' FROM dual;

declare
 cursor get_undo_stat is
        select d.undo_size/(1024*1024) "C1",
               substr(e.value,1,25)    "C2",
               (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "C3",
               round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))             "C4"
          from (select sum(a.bytes) undo_size
                  from v$datafile      a,
                       v$tablespace    b,
                       dba_tablespaces c
                 where c.contents = 'UNDO' 
                   and c.status = 'ONLINE'
                   and b.name = c.tablespace_name
                   and a.ts# = b.ts#)  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';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'To optimize UNDO you have two choices :'); 
dbms_output.put_line('====================================================' || chr(10));
 for rec1 in get_undo_stat loop
     dbms_output.put_line('A) Adjust UNDO tablespace size according to UNDO_RETENTION :' || chr(10));
     dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||TO_CHAR(rec1.c1,'999999') || ' MB');
     dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2/1,'999999')) || ' MSEGS)',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MB' );
     dbms_output.put_line(chr(10));
     dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :' || chr(10));
     dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') || ' : ' || TO_CHAR(rec1.c2/1,'999999') || ' MSEGS');
     dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1/1,'999999')) || ' MSEGS) ',65,'.') || ' : '|| TO_CHAR(rec1.c4/1,'999999')|| ' MSEGS');
 end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/

BEGIN
  dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'Estadisticas de session oracle        :'); 
  dbms_output.put_line('====================================================' || chr(10));
END;
/  
select 'Number of "ORA-01555 (Snapshot too old)" encountered since the last startup of the instance : ' || sum(ssolderrcnt)
 from v$undostat;

select '' AS "Estadisticas errores ORA-01555" 
  From dual
UNION ALL
select TO_CHAR(TRUNC(BEGIN_TIME),'DD/MM/RRRR')||' -> [' || sum(ssolderrcnt) ||'] ocurrencias'
 from v$undostat
 GROUP BY TRUNC(BEGIN_TIME); 

select 'OBSERVACIONES:'||CHR(10)||CHR(13)||' - Instancia  ' || instance_name || ' en ' || host_name|| ' fue iniciada en  '||to_char(startup_time,'DD/MM/RRRR HH24:MI:SS')|| ' ... '
 from v$instance;

select '- UNDO_RETENTION esta configurado en : [' || value || '] MILSECS, lo que correspunde a ['||value/60|| '] SEGUNDOS o, ['||value/60/60|| '] MINUTOS'
 from v$parameter
 where name = 'undo_retention';

select '--------------------------------------------------------------------' FROM dual;
select 'FINAL INFORME - FECHA ... '||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS') FROM dual;
select '--------------------------------------------------------------------' FROM dual;

SET FEEDBACK OFF;
SET ECHO OFF;  

A saída deste script deve dar uma informação parecida com o texto abaixo:

Selecionar tudo

--------------------------------------------------------------------
INFORME DE ESTIMATIVA DE UNDO_RETENTION
--------------------------------------------------------------------

To optimize UNDO you have two choices :
====================================================

A) Adjust UNDO tablespace size according to UNDO_RETENTION :

ACTUAL UNDO SIZE ................................................ :   12000 MB
OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (72000 MSEGS)....... :    9588 MB


B) Adjust UNDO_RETENTION according to UNDO tablespace size :

ACTUAL UNDO RETENTION ........................................... :   72000 MSEGS
OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (12000 MSEGS) ...... :   90114 MSEGS

Estadisticas de session oracle        :
====================================================
Number of "ORA-01555 (Snapshot too old)" encountered since the last startup of the instance : 0
13/05/2010 -> [0] ocurrencias

OBSERVACIONES:
  - Instancia  PATIX en XPTO fue iniciada en  13/05/2010 05:35:06 ...
  - UNDO_RETENTION esta configurado en : [72000] MILSECS, lo que correspunde a [1200] SEGUNDOS o, [20] MINUTOS

--------------------------------------------------------------------
FINAL INFORME - FECHA ... 13/05/2012 14:32:08
--------------------------------------------------------------------

Ele explica as opções que você dispõe: você pode ter que aumentar a sua UNDO para permitir um UNDO_RETENTION maior, ou então, aumentar o parâmetro de UNDO_RETENTION, permitindo que a tablespace de UNDO seja 100% utilizada.

Creio que os foristas do GLUFKE poderão lhe dar mais dicas sobre este problema e de como resolver. Talvez também seja necessária alguma revisão das suas rotinas, caso este problema nunca tenha ocorrido antes.

Abraços,

Sergio Coutinho

[dejambo]

Vlw Sergio pela explicação..irei pesquisar mais sobre o assunto aqui.

[fbifabio]

dejambo,

Este erro normalmente acontece quando seu tablespace de UNDO realmente é pequeno para comportar as transações que estão ocorrendo. O caminho mais fácil e rápido para resolvê-lo é aumentar o tamanho do tablespace de UNDO. Se você aumentar o valor do parametro UNDO_RETENTION a situação vai piorar ainda mais!

[]s

Fábio Prado
www.fabioprado.net

[dejambo]

Vlw fábio...sua solução resolveu meu problema.

[fbifabio]

Pessoal, escrevi um artigo sobre o assunto: http://www.fabioprado.net/2012/11/confi ... space.html

[]s

Fábio Prado
www.fabioprado.net