UNDO MANAGEMENT

Every oracle database must have a method to maintain information that is used to rollback, or undo changes to the database such information consists of records of actions of transactions, primarily before they are committed.

 undo records are used to

• roll back transations when a roll back statement is used

• recover the database.

till 8i the undo that used to generated, used to be handled rollback tablespace, which was directly managed. In case we have choose to first create a rollback tablespace, then create rollback segments and assign it to roll back tablespace.

 now oracle 9i ,the new concept of undo tablespace is introduces,whioch helps in below ways:

• it is logically managed.

• The undo segments are created by oracle itself.

• The number of undo segments are generated by oracle itself.

• The purpose of undo management and rollback segment is same The purpose of undo segments amd rollback segment is same except the creation and maintaince past.

it is not possible to use both methods in a single instance. However we can migrate for example to created undo tablespace in database that is using rollback segments and assign undo to db.

And to create rollback segs in database that using undo ts (or) commented it

However in both cases we must shut down and restart out database in order to effect the switch to another.

 mode of undo space management : Manual:

If we use the rollback segments method of managing undo space you are said to be operating in the manual undo management mode.

Auto:

If we use undo tablespace method, you are operating in automatic undo management mode.

We usually determine this mode at instance startup using the undo-management parameter in the init file

An undo tablepsace must available into which oracle will store undorecors. The default undo tablespace is created at database creation (or) an undo tablepspace can be created explicitly

The parameter to be specified to create and assign an undo tablespace is undo_tablespace.

 when instance startup,oracle automatically selects for use the first available undo tablespae if there is no undo tablespace available the instance starts,but uses system rollback segmet. This is not recommended. And an alert message is written to alert file.

 undo_retention:

Retention is period of time. it is specified in units of seconds. it cam survive system crashes ie, undo genated before an instance is crash ,is retained until its retention time has expired even across restarting the machine.

When the instance is recovered undo info is returned based on current setting if undo_retention parameter.

Default is undo_retention=900 default

We can change this value dynamically by using below statement.

Sql> alter system set undo_retention=200;

It effects immediately.

 oracle 10g guarantee undo retention

When we enable this option the database never overwrite unexpired undo data ie,undo data whose age is less than undo retention period this option is disabled by default .

 create a undo tablespace

Sql>create undo tablespace undotbs Datafile ‘/oraAPP/undo.dbf’ size 50m;

Create a undo with retention guarantee

:-Sql>create undo tablespace undotbs datafile ‘oraAPP/undotbs’ size 100m

Retention guarantee;

 alter tablespace retention guarantee or no;

Sql> alter tablespace undotbs retention guarantee/noguarente

 changing undo tablespace dynamicallyto db;

Sql>alter system set undo_tablespace=’TS_NAME’;

droping undots:

Sql>drop tablespace undotbs;

table to get information about undo data:

V$unditat V$rollstat V$undostat

Dba_undo_extents Dba_tablespaces Dba_rollback_segs

Difference between undo and roll back segments:

 rollback segments are overwritten ie, when the last extent rollback segment gets filled it enters the later uncommitted to first extent of that signet only it over writes the data in those extents.

We had to create rollback segments manually it used upto 8i.

undo segments maintain the uncommitted data till the retention period is reached. Even though it fills all the extents also, it maintains the data till it reaches retention period.at that time it throws an error.

Ora:30036 unable to extent the segment

Oracle usually takes care of creating undo segments it is introduced in oracle 9i.

To build demo tables using sql Alomg with scott user :

@?/rdbms/admin/utlsampl.sql

select undtsn, undblock from v$undostat;

Temporary tablespaces V$sort_usage

V$temstat V$tempfile V_$sort_usage Dba_temp_file Database_properties Package:

Utl_recomp_stored

Sql>select file_name,tablespace_name,bytes,status from dba_temp_files;

To know which tablespace is assigned to database:

Sql>select property_name,property_value from database_properties;

Sql>select name from v$tempfile;

Database creation

we can create db without mentioning the below parameter in initfile.

Db_cache_size,shared_pool_size,log_buffer and control_files;

The sizes of the above parameter are

db_cache)size =48m

shared_pool_size=32m

log_buffer=7057408 Controlfile=control<sid>.ora Loc ‘$oracle_home/dbs/

Total sga size = 112m.

How can we trace a session (user)?

We want to get information that what the user is doin. For this we had to follow below process.

Open 2 sessions 1) as sysdba 2) scott/tiger solutions;

1) we had to get the sid. Serial# for that session.

Sql> select sid, serial#,username from v$sessions where username is not null;

Sid serial# username

27 1632 scott

2) now excute the below package to enable tracing for that session.

Sql> exec dbms_system set sql_trace in session (‘27’,’1632’,true);

3) perform some activities in that session

4) now server process id for that session using below query.

Sql> select p.sid from v$session s, v$process p where s.paddr=p.addr and s.sid=/s;

Spid 3683

With this spid a trace file is generated for this session in udump:-

5) go to udump location now convert this trace file to user unserst and able format and also eliminate sys related data.

[ kittu@linux1 n]$ tkprof ram_ora_3683.trc sys=no]

Open this file and view what are the activities being done on that session and also performance,

How can we disable tracing on a session:

Sql> exec dbms_system set_sql_trace_in_session (sid,serial#,false);

How to kill a session

Identify the pid,serial# of that session from v$session.

Sql> select sid,serial#,username from v$session;

27 1749 rama

Now find the server processid for this session.

Sql> select p.sid from v$session s, v$process p where s.paddr=p.addr and s.sid=27;

Sid 9082

First kill this session using the below query in sql level;

Sql> alter system kill session ’27,1749’;

Now kill this session in o/s level first find out the process for this session we already found the server process id for this session with this id, kill the session.

[kithu@linux2 ~] ps –ef|grep pracle ram [kithu @linux2 ~] kill -9 9082