Ora-01555 while performing FLASHBACK Query

[engineer2100]

I get the ORA-01555 Snapshot too old error when I am trying to get the historical data using the flashback query

My UNDO_RETENTION is set at 900

Thanks
Engi

 

[DrD123]

Hi engineer2100,

You could put the following inside an SQL file and run it and it'll give you an optimal UNDO for your setting or vice-versa, then adjust your UNDO size or retention accordingly:

Here's example output:

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

A) Adjust UNDO tablespace size according to UNDO_RETENTION :

ACTUAL UNDO SIZE ................................................ : 2000 MEGS
OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (15 MINUTES)
....... : 26 MEGS


B) Adjust UNDO_RETENTION according to UNDO tablespace size :

ACTUAL UNDO RETENTION ........................................... : 15 MINUTES
OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (2000 MEGS) ........ : 1159 MINUTES

====================================================

set serverout on size 1000000
set feedback off
set heading off
set lines 132
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') || ' MEGS');
      dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2/60,'999999')) || ' MINUTES)
',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');
      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/60,'999999') || ' MINUTES');
      dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS) ',65,'.') || ' : ' || TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
  end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/

Good luck,
DrD

 

[engineer2100]

Thanks DRD; i will check this out.

So does this mean, its more to do with the settings that's causing this issue?

-Engi

 

[DrD123]

Hi Engi,

Yes, it's the settings. Even though you may have a high retention time (UNDO_RETENTION), you need to adjust the size (UNDO_SIZE) to match that. The size will be the amount of space to hold the historical transactions to bring back in your flashback query.

There are other parameters that effect it as well (SESSIONS & TRANSACTIONS_PER_ROLLBACK_SEGMENT), but lets see what you have so far.

What results did you get with the query?

Good luck
DrD

 

[engineer2100]

Well I can see that the DB we have has very less value for UNDO_RETENTION

To optimize UNDO you have two choices :

==================================================

==

A) Adjust UNDO tablespace size according to UNDO_RETENTION :

ACTUAL UNDO SIZE ................................................ : 17485 MEGS

OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (15 MINUTES)

....... : 42 MEGS

 

B) Adjust UNDO_RETENTION according to UNDO tablespace size :

ACTUAL UNDO RETENTION ........................................... : 15 MINUTES

OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (17485 MEGS) ....... : 6238 MINUTES