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.
Obrigado.
Estagiário em BD Oracle.
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.
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;
--------------------------------------------------------------------
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
--------------------------------------------------------------------
Usuários navegando neste fórum: Nenhum usuário registrado e 14 visitantes