Implement Automatic Undo Management(rather than Manual)
Undo = rollback
Undo segment ->save old value(undo data)&relevant location
Undo segment header ->contains a transaction table->stores the information about the current transactions using the undo segement
A serial transaction(concurrent transactions included) uses only one undo segment to store all of its undo data.
purpose ->transaction rollback,transaction recovery, read consistency
transaction rollback: restores the original values back to the modifiled row.
transaction recovery: instance fails while transactions are in progress--> undo any uncommitted changes(when DB is opened again) <-- changes made to the undo segment are also protected by the online redo log files.
read consistency:a.transactions in progress, other users should not see any uncommitted changes made by these transactions.
b. a statement should not see any changes committed in execution
Read Consistency
Order: executing a SELECT statement
determines current SCN& ensures any changes not committed before the SCN are not processed by the statement.
whether a data block is old or not depends one the SCN(compare)
The Oracle server automatically manages the creation, allocation, and tuning of undo segments.
Types of Undo Segments
SYSTEM Undo Segment -> created in the SYSTEM tablespace when a DB is created.
-> used only for changes made to objects in the SYSTEMTBS
Non-SYSTEM Undo Segments -> A DB that has multiple tablespaces needs at least one non-SYSTEM segment for manual mode or one UNDO tablespace for auto mode.
Deferred: Used when tablespaces are taken offline immediate, temporary, or for recovery
Automatic Undo Management: Concepts
* Undo data is managed using an UNDO tablespace.
* You allocate one UNDO tablespace per instance with enough space for the workload of the instance.
*Undo segments are created with the naming convention: _SYSSMUn$
Automatic Undo Management: Configuration
* Configure two parameters in the initialization file(must&only):
– UNDO_MANAGEMENT:AUTO/MANUAL(not recommended), can not be changed after DB starts.
– UNDO_TABLESPACE:specifies a particular UNDO TBS to be used.
can be dynamically altered.
--SQL> ALTER SYSTEM SET undo_tablespace = UNDOTBS;
create at least one UNDO tablespace(auto,one active).
>= 1 UNDO tablespace may exist, only one can be active.
spfilewade.ora ---refer to manually create a DB
Automatic Undo Management: UNDO Tablespace creation
1.created with a DB by adding a clause in the CREATE DATABASE command
CREATE DATABASE db01
. . .
UNDO TABLESPACE undo1
DATAFILE '/u0