Undo Tablespace & ORA-01555 snapshot too old error..!!

by

As an “Oracle DBA ” normally interviewer ask about ORA-01555 snapshot too old error.  

What is Undo?

Oracle Database maintain information that is used to roll back, or undo. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

1. Roll back transactions when a ROLLBACK statement is issued

2. Recover the database

3. Provide read consistency

4. Analyze data as of an earlier point in time by using Flashback Query

Statement-Level Read Consistency:

Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent for a single point in time. The point in time to which a single SQL statement is consistent depends on the transaction isolation level and the nature of the query.
Undo information is used for is Read Consistency, which means if you run a query at 8:00 AM for 10 minutes, therefore one can ask for all the data from 8:00AM . You don’t want it to read data that has been modified at 8:05 or data that hasn’t been committed yet.

So, to support Read Consistency, Oracle must keep the original data (committed or not) for these 10 minutes until the query is finished.

The following initialization parameter setting causes the STARTUP command to start an instance in automatic undo management mode:

UNDO_MANAGEMENT = AUTO
If the database contains multiple undo tablespaces, you can optionally specify at startup that you want an Oracle Database instance to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initialization parameter. For example:

UNDO_TABLESPACE = undotbs_01
Initialization ParameterDescription
UNDO_MANAGEMENTIf AUTO, use automatic undo management mode. If MANUAL, use manual undo management mode. The default is MANUAL.
UNDO_TABLESPACEAn optional dynamic parameter specifying the name of an undo tablespace to use. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.
UNDO_RETENTIONA dynamic parameter specifying the minimum length of time to retain undo. The default is 900 seconds. The setting of this parameter should take into account any flashback requirements of the system.

Undo Retention

Committed undo information normally is lost when its undo space is overwritten by a newer transaction. However, for consistent read purposes, long-running queries sometimes require old undo information for undoing changes and producing older images of data blocks. The success of several Flashback features can also depend upon older undo information.

Setting the UNDO_RETENTION Initialization Parameter

The default value for the UNDO_RETENTION parameter is 900.

You can set the UNDO_RETENTION parameter initially in the initialization parameter file that is used by the STARTUP process or at any time using the ALTER SYSTEM statement:

1
ALTER SYSTEM SET UNDO_RETENTION = 86400;

The ORA-01555 snapshot too old error can be addressed by several remedies:-

1. Re-schedule long-running queries when the system has less DML load.

2. Increasing the size of rollback segments(undo)size.

3.The ORA-01555 snapshot too old also relates to your setting for automatically undo retention. We have to increase undo retention parameter.

4. Don’t fetch record between commits.

UNDO segments

ACTIVE: Currently in Used. (You will get ‘ORA-30036 unable to extend segment in Undo tablespace‘ errors when no more space is left to store ACTIVE Undo.)

EXPIRED: Extend cannot be Used by transactions

UNEXPIRED: When the Undo mechanism requires more extents for ACTIVE extents, it is allowed to steal UNEXPIRED extents when there are no EXPIRED extents left for reuse.

(You will get ‘ORA-01555 snapshot too old‘ errors if no Read Consistency information for a query is available.)

Check details of Undo Tablespace:

1
select tablespace_name, file_name,autoextensible, bytes/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';
1
select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select status,
  round(sum_bytes / (1024*1024), 0) as MB,
  round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
  select status, sum(bytes) sum_bytes
  from dba_undo_extents
  group by status
),
(
  select sum(a.bytes) undo_size
  from dba_tablespaces c
    join v$tablespace b on b.name = c.tablespace_name
    join v$datafile a on a.ts# = b.ts#
  where c.contents = 'UNDO'
    and c.status = 'ONLINE'
);

Creating Undo Tablespace

1
CREATE UNDO TABLESPACE undotbs_02 DATAFILE '+DATA' SIZE 2M REUSE AUTOEXTEND ON;

You can create more than one undo tablespace, but only one of them can be active at any one time.

Altering an Undo Tablespace

1
ALTER TABLESPACE undotbs_03 ADD DATAFILE '+DATA' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Dropping an Undo Tablespace

1
DROP TABLESPACE undotbs_01;

Switching Undo Tablespaces

1
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

Identify active transactions in undo and rollback segments

1
2
3
4
5
6
7
8
9
10
11
12
13
col o format a10
col u format a10
select osuser o, username u, sid,
segment_name s, substr(sa.sql_text,1,200) txt
from v$session s,
v$transaction t,
dba_rollback_segs r,
v$sqlarea sa
where s.taddr=t.addr
and t.xidusn=r.segment_id(+)
and s.sql_address=sa.address(+)
And substr(sa.sql_text,1,200) is not null
order by 3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
col name format a8
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select username, osuser,
t.start_time, r.name, t.used_ublk "ROLLB BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/

Determine the optimal UNDO tablespace size

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]“,
       SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]“,
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      “NEEDED UNDO SIZE [MByte]“
  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'
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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;
/