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

User Management in ORACLE

USER MANAGEMENT in ORACLE 11g

User management is a common activity for a ORACLE DBAs. Followings are real time & day-to-day activity examples for a DBA .

A DBA should create a user, after understanding from the application team that, what the user needs.

While creating a user, we can assign the default tablespace and default temporary tablespace for that user.

Privileges

It is a rights for a user to create, modify and delete on object-level and schema-level.

It is two types.

·         System level privilege - eg: create table, create view etc

·         Object level privilege - eg: select on A, update on B etc

Role

A role is a set of privileges which will reduce the risk of issuing many commands

To findout roles and privileges assigned to a user, use following views
a. DBA_SYS_PRIVS
b. DBA_TAB_PRIVS

c. DBA_ROLE_PRIVS
d. ROLE_SYS_PRIVS
e. ROLE_TAB_PRIVS
f. ROLE_ROLE_PRIVS

g. USER_SYS_PRIVS

h. USER_TAB_PRIVS

i. DBA_ROLES

Privileges with admin option

System level privileges can be granted with admin option so that the grantee can grant the same
privilege to other users. If we revoke the privilege from first grantee, still others will have that
privilege.

Privileges with grant option

Object level privileges can be granted with grant option so that grantee can grant the same
privilege to other users. If we revoke the privilege from first grantee, it will revoked from all the
users.

EXAMPLES

# To create a user
SQL> create user dr identified by dr
default tablespace kalamtbs
temporary tablespace temp;

# Create user with demand for a new password at the time of login

SYS@ORCL>alter user dr identified by dr password expire;

# To grant permissions to user
SQL> grant create session, create table to dr;

# To grant permissions to user with admin option
SQL> grant create table to scott with admin option;

# To grant permissions to user with grant option
SQL> grant update on scott.salary to dr with grant option;

# To revoke any permissions from user
SQL> revoke create table from scott;

# To change password of user

SQL> alter user dr identified by oracle;

# To allocate quota on tablespace
SQL> alter user dr quota 10m on mydata;

Note: Allocating quota doesn't represent reserving the space. If 2 or more users are sharing a
tablespace, quota will filled up in first come first serve basis

# To change default tablespace or temporary tablespace
SQL> alter user dr default tablespace test;
SQL> alter user dr default temporary tablespace mytemp;

Note: The objects created in the old tablespace remain unchanged even after changing a default
tablespace for a user

# To check default permanent & temporary tablespace for a user (for user level)
SQL> select default_tablespace,temporary_tablespace from dba_users where username='SCOTT';

# To lock or unlock a user
SQL> alter user scott account lock;
SQL> alter user scott account unlock;

# To check default permanent tablespace and temporary tablespace (in database level)

SQL> select property_name,property_value from database_properties where property_name like 'DEFAULT%';

# To change default permanent tablespace

SQL> alter database default tablespace mydata;

# To change default temporary tablespace

SQL> alter database default temporary tablespace mytemp;

# To check system privileges for a user
SQL> select privilege from dba_sys_privs where grantee='SCOTT';

# To check object level privileges
SQL> select owner,table_name,privilege from dba_tab_privs where grantee='SCOTT';

# To check roles assigned to a user
SQL> select granted_role from dba_role_privs where grantee='SCOTT';

# To check permissions assigned to role
SQL> select privilege from role_sys_privs where role='MYROLE';
SQL> select owner,table_name,privilege from role_tab_privs where role='MYROLE';
SQL> select granted_role from role_role_privs where role='MYROLE';

# Create a role

SYS@ORCL>create role drrole1 identified by dr;

# Assign some privileges to a role

SYS@ORCL>grant create table to drrole1;

SYS@ORCL>grant select on hr.employees to drrole1;

# Assign a role to user

SYS@ORCL>grant drrole1 to dr;

# Revoke a role from a user

SYS@ORCL>revoke drrole1 from dr;

# To drop a user
SQL> drop user user1;
Or
SQL> drop user user1 cascade;

Note :

a)       After creating user, don't grant “connect” and “resource” roles . We can grant “connect” role as it contains only create session privilege.

b)      Resource role internally contains “unlimited tablespace” privilege and because of this, the user can create table in any tablespace. So, it should not be granted in real time until it is required.