Automatic Undo Management in ORACLE

AUTOMATIC UNDO MANAGEMENT in ORACLE 11g

Undo:

When we make some changes to the data of the database, then existing data are created and maintained by oracle, which are used for rollback or undo the changes, before they are committed. These records are collectively called as UNDO.

Uses:

At the time of database recovery, undo information is used to undo any data which are not committed.

Undo Features:

Rollback

Rollback is very easy to understand. Rollback is to make undo the data modification. It means if we make some changes to the data and we are not happy with that modification, then we can undo the changes by “rollback” command.

Read consistency

Undo information provide read consistency by maintaining old data to the user who are accessing the data same time while another user is changing it.

Ex: the user A make some changes in a table at 1:00PM. and the query will run about to 10mins. Another user B run a select statement at 1:05PM. Then user B will see the old data, not the new data. After user A commit the data, user B can see the new data. It is read consistency.

Flashback:

We can see the old data using undo information using “flashback”. It means old data are stored in rollback segments (undo retention period), so we can look the old data back by this rollback segments using “flashback” statement.

Undo management:

Undo management is to manage the undo information and space. From the oracle 9i, undo management is automatic by default. Undo information are stored in undo segments of undo tablespace. Undo tablespace is created automatically at the time of database creation. When the database starts, it searches for the available undo tablespace. If the undo tablespace is missing, then undo information are stored in system tablespace which is not recommended by oracle.

Undo Life-cycle:

Undo information has three states, depending on running transactions and retention settings.

Active:

Active undo extents are used by active transactions and these are active because of they are needed for “rollback”.We can’t say that we cannot rollback the transaction, if the undo retention period is passed.

If there is no space for active undo extents, then it will show “ora-30036: unable to extend segment in undo tablespace”.

Expired:

The data in the inactive undo extents are committed and the undo retention period has passed. So these extent are not used for read consistency.

Unexpired:

The data in the unexpired undo extents are already committed, but the undo retention period has not passed. So these extents can be use for read consistency.

When the active transaction needs  more extents for active extents, it takes from expired extents and if it does not get any expired, it takes from unexpired extents.

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 and it cannot allocate more free extents ( autoextend maxsize reached or fixed tablespace size ). One can check the steal-count in UNXPSTEALCNT in V$UNDOSTAT.

You will get “ORA-01555 snapshot too old” errors if no Read Consistency information for a query is available. The SSOLDERRCNT in V$UNDOSTAT will show a count of these errors.

Undo Initialization Parameters:

UNDO_MANAGEMENT : this parameter specifies the mode of undo management, the system should use. When it set to AUTO, it uses automatic undo space management. When it set to MANUAL, then undo space management is allocated to rollback segments by externally.

UNDO_TABLESPACE: this parameter specifies which is the default undo tablespace. When the instance starts, it searches for undo tablespace for storing the undo data.

UNDO_RETENTION: this parameter specifies that how much time, the committed undo data will remain in the database (In seconds). It is very useful for long running queries.

Undo_retention has two values.

a.RETENTION GUARANTEE – It specifies the guarantee for the success of long running queries and oracle flashback operations. If it is enable, then the database never overwrites the unexpired undo extents and it remains for read consistency, even if the database hanged due to the lack of space in undo tablespace.

b.RETENTION NOGUARANTEE – It specifies the database can overwrite the unexpired undo extents, if the database needs more space for storing the undo information. Due to this, ora-01555 error can be occur.

Dictionary views for UNDO MANAGEMENT:

DBA_UNDO_EXTENTS

DBA_HIST_UNDOSTAT

V$UNDOSTAT

DBA_ROLLBACK_SEGS

V$ROLLSTAT

SOME EXAMPLES

Note : In these examples, i am using ASM. So it is dhowing like "+DATA". If you have Non-ASM file system, you have to specify name and location of the datafile.

Check the default undo tablespace

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS1

Check the location of undo tablespace

SQL> select FILE_NAME, TABLESPACE_NAME, BYTES, STATUS, ONLINE_STATUS from dba_data_files;

SQL> select FILE_NAME, TABLESPACE_NAME, BYTES, STATUS, ONLINE_STATUS from dba_data_files where TABLESPACE_NAME = 'UNDOTBS1';

FILE_NAME                                          TABLESPACE_NAME                     BYTES STATUS    ONLINE_

-------------------------------------------------- ------------------------------ ---------- --------- -------

+DATA/orcl/datafile/undotbs1.264.888195483         UNDOTBS1                         41943040 AVAILABLE ONLINE

Check the size of the undo tablespace

SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from dba_data_files where TABLESPACE_NAME = 'UNDOTBS1';

TABLESPACE_NAME                        MB

------------------------------ ----------

UNDOTBS1                               40

Drop undo tablespace

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

Resize the undo tablespace

Note : You should not resize/shrink the datafile of the undo tablespace. It’s better to add datafile to the undo tablespace.

Add datafile to a undo tablespace

SQL> alter tablespace UNDOTBS2 add datafile size 5M;

Tablespace altered.

Drop a datafile of a undo tablespace

SQL> alter tablespace UNDOTBS2 drop datafile 3;

Tablespace altered.

Note : You can get the datafile id in column "FILE_ID" of table "dba_data_files". You should not drop undo tablespace without assign another undo tablespace for the database in automatic undo management.

Make another undo tablespace as default for the database

Create a undo tablespace

SQL> create undo tablespace UNDOTBS2 datafile size 25M autoextend on;

Tablespace created.

Check the tablespace created or not

SQL> select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, STATUS, ONLINE_STATUS from dba_data_files where TABLESPACE_NAME like '%UNDO%';

FILE_NAME                                          TABLESPACE_NAME                        MB STATUS    ONLINE_

-------------------------------------------------- ------------------------------ ---------- --------- -------

+DATA/orcl/datafile/undotbs1.264.888195483         UNDOTBS1                               40 AVAILABLE ONLINE

+DATA/orcl/datafile/undotbs2.267.888851189         UNDOTBS2                               25 AVAILABLE ONLINE

Switching the undo tablespace as default

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS1

SQL> alter system set undo_tablespace = 'UNDOTBS2';

System altered.

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS2

UNDO­_RETENTION

Check the retention of undo tablespace

SQL> select TABLESPACE_NAME, STATUS, RETENTION from dba_tablespaces;

TABLESPACE_NAME                STATUS    RETENTION

------------------------------ --------- -----------

SYSTEM                         ONLINE    NOT APPLY

SYSAUX                         ONLINE    NOT APPLY

UNDOTBS1                       ONLINE    NOGUARANTEE

TEMP                           ONLINE    NOT APPLY

USERS                          ONLINE    NOT APPLY

EXAMPLE                        ONLINE    NOT APPLY

6 rows selected.

Switching the Undo retention to guarantee

SQL> select TABLESPACE_NAME, STATUS, RETENTION from dba_tablespaces;

TABLESPACE_NAME                STATUS    RETENTION

------------------------------ --------- -----------

SYSTEM                         ONLINE    NOT APPLY

SYSAUX                         ONLINE    NOT APPLY

TEMP                           ONLINE    NOT APPLY

USERS                          ONLINE    NOT APPLY

UNDOTBS2                       ONLINE    GUARANTEE

EXAMPLE                        ONLINE    NOT APPLY

7 rows selected.

Check the retention period

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_retention                       integer     900

Change retention period

SQL> alter system set undo_retention = 300;

System altered.

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_retention                       integer     300

PENDING OFFLINE UNDO SEGMENTS

A database can have many undo tablespaces, but only one undo tablespace can be default for the database. The segments of default undo tablespace will be online and all segments of non-default undo tablespaces will be offline. If any online segments of undo tablespace contain some data of active transaction, which can be use for rollback and at the same time, dba make another undo tablespace as default for the database, then all segments of new undo tablespace will be online and the segments of old undo tablespace which contains the data of the transaction will show “PENDING OFFLINE”..

Example:

As sys user

SQL> select USN, STATUS, EXTENTS, CUREXT, CURBLK from v$rollstat;

       USN STATUS             EXTENTS     CUREXT     CURBLK

---------- --------------- ---------- ---------- ----------

         0 ONLINE                   6          4          1

        11 ONLINE                   4          1          3

        12 ONLINE                   4          3          1

        13 ONLINE                   6          3          3

        14 ONLINE                   2          1          1

        15 ONLINE                   7          0          2

        16 ONLINE                   5          3          0

        17 ONLINE                   5          4          2

        18 ONLINE                   7          4          0

        19 ONLINE                   4          3          5

        20 ONLINE                   7          3          5

11 rows selected.

As scott user

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80       8000                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81      16000        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81      12500        500         30

      7566 JONES      MANAGER         7839 02-APR-81      29750                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81      12500       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81      28500                    30

      7782 CLARK      MANAGER         7839 09-JUN-81      24500                    10

      7788 SCOTT      ANALYST         7566 19-APR-87      30000                    20

      7839 KING       PRESIDENT            17-NOV-81      50000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81      15000          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87      11000                    20

      7900 JAMES      CLERK           7698 03-DEC-81       9500                    30

      7902 FORD       ANALYST         7566 03-DEC-81      30000                    20

      7934 MILLER     CLERK           7782 23-JAN-82      13000                    10

14 rows selected.

SQL> update emp set sal=sal/10;

14 rows updated.

As sys user

SQL> alter system set undo_tablespace = UNDOTBS1;

System altered.

SQL> select USN, STATUS, EXTENTS, CUREXT, CURBLK from v$rollstat;

       USN STATUS             EXTENTS     CUREXT     CURBLK

---------- --------------- ---------- ---------- ----------

         0 ONLINE                   6          4          4

         1 ONLINE                   2          0          0

         2 ONLINE                   2          0          0

         3 ONLINE                   2          0          0

         4 ONLINE                   2          0          0

         5 ONLINE                   2          0          0

         6 ONLINE                   2          0          0

         7 ONLINE                   2          0          0

         8 ONLINE                   2          0          0

         9 ONLINE                   2          0          0

        10 ONLINE                   2          0          0

        15 PENDING OFFLINE          7          0          2

12 rows selected.

To know the undo extent status

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'

);

Undo parameters

select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and (nam.ksppinm like '%undo%' or nam.ksppinm in ('_first_spare_parameter','_smu_debug_mode')) order by 1;

What are the various statuses for Undo Extents?

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

Tuned Retention

SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT) FROM V$UNDOSTAT;

SELECT BEGIN_TIME, END_TIME, TUNED_UNDORETENTION, MAXQUERYLEN, MAXQUERYID, NOSPACEERRCNT, EXPSTEALCNT, UNDOBLKS, TXNCOUNT FROM V$UNDOSTAT;

The size details and auto-extend setting for the UNDO Tablespace

COL AUTOEXTENSIBLE FORMAT A14
SELECT FILE_ID, BYTES/1024/1024 AS "BYTES (MB)", MAXBYTES/1024/1024 AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='&UNDOTBS';