Alert Snapshot Too Old

[dejambo]

Ola Galera, today when logging in Oracle Enterprise Manager 10g I came across the following alert:

Select all

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. 

What should I do?

Thanks.

Trainee in BD Oracle.

[stcoutinho]

Dejjo,

The Snapshot Too Old error message is a classic error that may eventually happen in a database.

On general lines, it may occur in a situation:

- You have a database with several operations in progress (eg update);
- The photo of the original information is stored in the Undo area. In Shared Pool, the information
changed. This information is not commissioned;
- You then open a cursor, which will eventually read the data that suffered update;
- Only Oracle needs to make a consistent reading of the data. It can not cause you to read
Updated information that have not yet been committed by the original sessions;
- In this way, among the records to be recovered by its cursor, Oracle points to the original
records (before the unitual update), and which are in the UNDO;
- Only UNDO can not indefinitely keep this photo original from records. Periodically, it
is releasing the records that have already been physically updated in the bank. A parameter that controls this
is the UNDO_RETENTION;
- Eventually, your cursor may take a lot to process the records, and when it tries
locate the original records in the undo, it may be that they are no longer there. And that's where you
ends up receiving your Snapshot Too Old;

Well, this example above would be one of the situations where Snapshot occurs. To try to resolve the problem, you can check if the UNDO_RETENTION parameter (the time in milliseconds that the "unnecessary" information is maintained in the UNDO).

The script below (sorry, I got it a long time in a forum and I do not have any more of the URL) can give you a tip. Save it to an SQL file and run on your database (by SQL * Plus only) through a user who is allowed to access the data dictionary (eg with DBA Grant);

Select all

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;   

The output of this script shall give information similar to the text below:

Select all

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

He explains the options you have: You may have to increase your undo to allow a larger Undo_Retention, or then increase the Undo_Retention parameter, allowing UNDO tablespace to be 100% used.

I believe Glufke's forists can give you more tips on this problem and how to solve. It may also be necessary any revision of your routines if this problem has never occurred before.

Hugs,

Sergio Coutinho

[dejambo]

Vlw sergio for the explanation .. I would search more on the subject here.

[fbifabio]

Dejjo,

This error usually happens when your Undo tablespace really is small to behave the transactions that are occurring. The easy and fastest way to solve it is to increase the size of the UNDO tablespace. If you increase the value of the parameter Undo_Retention the situation will get worse even more!

[] S

Fábio Prado www.fabioprado.net

[dejambo]

Vlw Fábio ... Your solution solved my problem. : D

[fbifabio]

Personnel, I wrote an article on the subject: http://www.fabioprado.net/2012/11/confi ... space.html
[] S

Fábio Prado www.fabioprado.net