Wednesday, November 5, 2008

Undo Management

My Understanding on undo Tablespace and Rollback segments.

Undo table space concept released with Oracle 9 Version , prior to 9 DBA used to manage undo management through rollback segments. It was a cumbersome process for DBAs to manage rollback segments especially sizing rollback segments.

From Oracle 9i onwards Oracle came up with a good solution called undo tablespace where DBA does not require to worry  Undo management.

But Oracle still have the rollback segment in 9i as well as in 10g and 11g  for backward compatibility. In 10g and 11g still oracle use rollback segment concept but this exclusively used by Oracle at the time of database creation. In 10g or 111g rollback segments are created and managed by oracle software and they are stored in SYSTEM tablespace, this is because at the time of database creation they may not be a undo tablespace.

Init.ORA Undo parameters .

The following undo parameter controls undo management .

UNDO_MANAGEMENT :  Possible values are 'AUTO' or 'MANUAL' , you basically speicy which undo management mode oracle database should use . If you specify AUTO database will be starting in automatic undo management mode.

UNDO_TABLESPACE  : You specify the tablespace name as a parameter value if your  undo management mode is automatic. It is not a mandatory parameter though, if you forget to specify undo tablespace name , database still start with out any startup errors but you still see errors/Warnings in the Alert log. If instance starts without undo tablespace user transaction will be using rollback segment created in system to manage transaction undo data. Oracle strongly recommends not use Rollback segments created in System tablespace. Undo tableapce parameter can be modified using Alter system table space command.

UNDO_RETENTION : Undo retention specified in seconds , Number of seonds to keep expired transaction data( undo data). default value is 900 seconds, this can be changed using alter system command.

Will write more undo in the next posting.