Flowchart:

┌──────────────────────────────────────────────────────────────┐
│             User Session (Update, Delete, Insert)            │     
│                    Generate Redo log + undo                  │     
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌───────────────────────────────────────────┐             ──────────▶  Rollback
│  Undo Segment (store before image data    │           /
│         used by transactions)             │ ─────────● 
└───────────────────────────────────────────┘           \
                               │                          ──────────▶  Read consistancy
                               ▼
┌──────────────────────────────────────────────────────────────┐
│             Undo Tablespace (Contains undo segments          │   
│                  Datafiles store undo blocks                 │ ───────────▶ ORA-01555 risk
└──────────────────────────────────────────────────────────────┘

Core Concepts:

Undo Record:

The original data of the data block that has changed due to a transcation, the original data is copied and keep in a undo record in undotbs data file, inside undo tablespace. If there is any rollback this is used, and to maintain read consistency (until the change is commited a lock is applied to the changed data block, in that time if anyone wants to read that data block, it will give the original data from the undo record. When there is commit the changed data block will be unlocked and the previous data in the undo record is deleted and readers see the new data. This is read consistency), recovery (?).

Undo Segment:

The segment containing undo records. Oracle automatically assigns undo segments during transaction for operation.

Read Consistency:

During transaction a lock is applied to the changed data block, in that time if anyone wants to read that data block, server process will give the original data from the undo record. When there is commit the changed data block will be unlocked, and readers see the new data. This is read consistency. So that readers and writters don't block each others. The previous data in the undo record will be deleted when the retention period of undo is expired after the commit.

Undo Retention:

Target retention time in seconds.

Tuned Retention:

Oracle adjust the retention based on workload and available space. Can be seen in dynamic view v$undostat.

Active Extent:

Contains undo records for active transaction. This cannot be reused.

Unexpired Extent:

Contains undo records of completed transactions whose retention period has not expired. May be needed for consistent reads (?) and flashback (?).

Expired Extent (?):

Contains undo records of completed transactions whose retention period has expired.

Retention garuntee:

Prevets reuse of enexpired undo extent, and don't let those data be deleted to give space for new undo records. Protects consistent reads (?) and flashback (?) but may cause DML failure when space is exhausted as it doesn't let the process delete the data in unexpired extent to get new space.

Associated Views and Parameter:

v$undostat: (?) Please explain this strange view and its columns.

10 minute undo workload statistics, retention, errors and query duration.

v$transaction:

Active transaction, undo blocks, undo records, start time.

v$rollstat:

Runtime statistics for rollback/undo segments.

v$rollname:

Maps rollback segment numbers to names.

dba_undo_extents:

Undo extent status, active, unexpired, expired

dba_rollback_segs:

Undo segement name, status and tablespace.

dba_tablespaces:

Undo tablespace status and retention mode.

dba_data_files:

Undo datafile size, autoextend and maximum size.

undo_tablespace:

Active undo tablespace parameter.

undo_retention:

Retention target in seconds.

undo_management:

Auto or Manual; modern systems use Auto.

Baseline Health check:

Parameters:

show parameter undo_management;

show parameter undo_tablespace;

show parameter undo_retention;

Undo Tablespace and files:

select tablespace_name, status, contents, retention, bigfile, extent_management, segment_space_management from dba_tablespaces where contents='UNDO';

select tablespace_name, file_id, file_name, bytes/1024/1024 as size_md, autoextensible, maxbytes/1024/1024 as maxsize_mb, status, online_status from dba_data_files where tablespace_name=(select upper(value) from v$parameter where name='undo_tablespace') order by file_id;

Undo extent status:

Select status, count(*) as extent_count, round(sum(bytes)/1024/1024, 2) as size_mb from dba_undo_extents where tablespace_name = (select upper(value) from v$parameter where name = 'undo_tablespace') group by status order by status;

v$undostat summary:

Select begin_time, end_time, undoblks, txncount, maxquerylen, maxqueryid, tuned_undoretention, ssolderrcnt, nospaceerrcnt, unxpstealcnt, expstealcnt from v$undostat order by begin_time desc fetch first 24 rows only; (?)

Current Undo segment status:

Select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name = (select upper(value) from v$parameter where name='undo_tablespace') order by segment_name;

Transaction and Rollback practical lab:

Scenaro 1: Update rows and rollback:

Step 1: Create a lab table and insert data.

insert into undo_lab select level, 'original'||level, level * 100 from dual connect by level <=10000; (?)

Step 2: Start a transaction and don't commit.

Step 3: Find active transaction.

select s.sid, s.serial#, s.username, s.status, s.sql_id, t.start_time, t.used_ublk, t.used_urec, t.log_io, t.phy_io from v$transaction t inner join v$session s on s.taddr = t.addr order by t.used_ublk desc;

Step 4: Roll Back.

select count(*) from undo_lab where value like 'UPDATED%';

Expected result: Zero rows remain with updated values.

Undo retention and tuned retention:

Check retention settings:

Show parameter undo_retention;

Select tablespace_name, retention from dba_tablespaces where contents = 'UNDO';

Tuned retention trend:

Select begin_time, end_time, tuned_undoretention, maxquerylen, undoblks, txncount , ssolderrcnt, nospaceerrcnt from v$undostat where begin_time >= sysdate-1 order by begin_time;

Retention Garuntee:

Alter tablespace undotbs1 retention guarantee;

Select tablespace_name, retention from dba_tablespaces where tablespace_name = 'UNDOTBS1';

Alter tablespace undotbs1 retention noguarantee;

Expired vs unexpired spaces:

Select status, round(sum(bytes)/1024/1024/1024, 2) as size_gb from dba_undo_extents where tablespace_name = 'UNDOTBS1' group by status order by status;

ORA-01555: Snapshot too old

Reason:

A query needs an older version of a block but that undo record has been overwritten so oracle can't find that data. This raises the error ORA-01555. (?)

Common Causes:

Long running query.

High DML workload generating undo rapidly.

Undo tablespace too long.

Undo retention too low for the query duration.

Frequent commits inside batch loops causing block cleanouts or reuse patterns.

Retention is not garunteed and space pressure forces reuse.

Identify ORA-01555 history:

Select begin_time, end_time, undoblks, txncount, maxquerylen, maxqueryid, tuned_undoretention, ssolderrcnt, nospaceerrcnt, unxpstealcnt, unxpstealcnt, expstealcnt from v$undostat where ssolderrcnt > 0 order by begin_time desc;

Find the longest query recorded:

Select begin_time, end_time, maxquerylen, tuned_undoretention from v$undostat order by maxquerylen desc;

Retrieve sql text:

select sql_id, sql_fulltext from v$sql where sql_id='&MAXQUERYID';

Alert Log search:

adrci show homes set home diag/rdbms/prod/prod show alert -p "message_text like '%ORA-01555%'" -term exit

Simulate this error in lap:

Step 1: Create a small undo tablespace.

Step 2: Then tried to do some big transcation with no commit with an undotbs datafile of 5 MB.

Tried with another small undo tablespace datafile of 1 MB with no autoextend, same result.

Corrective Action:

Increase undo tablespoon size.

Increase undo retention based on maximum query duration. (?)

Enable autoextend with a required maxsize.

Tune the long running query. (?)

Reduce unnecessary commit frequency in batch jobs.

Schedule reporting away from heavy DML window.

Use retention guarantee only when justified and sized appropriately.

Undo sizing:

Undo generation rate:

Select begin_time, end_time, undoblks, txncount, maxquerylen, tuned_undoretention from v$undostat where begin_time >= sysdate-1 order by begin_time;

Calculate Approximate Undo MB per 10 minutes:

Select u.begin_time, u.end_time, round(u.undoblks * to_number(p.value)/1024/1024, 2) as undo_mb from v$undostat u, v$parameter p where p.name = 'db_block_size' and begin_time >= sysdate-1 order by begin_time;

Estimate Required Undo size:

General sizing method:

Required Undo = Peak undo blocks per second * Required retention seconds * Database block size

Example query:

With u as ( Select Max(undoblks/600) as peak_undo_blocks_per_sec From v$undostat where begin_time >= sysdate-7), p as ( Select to_number(value) as block_size from v$parameter where name='db_block_size') Select round( u.peak_undo_blocks_per_sec * &required_retention_seconds * p.block_size/1024/1024/1024, 2) as estimated_undo_gb from u cross join p;

Please explain this query and this output ?

Compare maximum query length to retention:

Select max(maxquerylen) as maximum_query_seconds, max(tuned_undoretention) as maximum_undo_retention from v$undostat where begin_time >= sysdate-7;

Create and switch undo tablespace lab:

Step 1: Create a new undo tablespece, start a transaction, don't commit and switch active tablespace;

Step 2: Validate:

show parameter undo_tablespace; select tablespace_name, status, contents, retention from dba_tablespaces where contents = 'UNDO'; select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name in ('UNDOTBS4', 'UNDOTBS5') order by tablespace_name, segment_name;

Do with previous and new one.

Step 4: Check transaction using old undo.

select s.sid, s.serial#, s.username, r.name as rollback_segment, t.start_time, t.used_ublk, t.used_urec from v$transaction t inner join v$session s on s.taddr = t.addr inner join v$rollname r on r.usn=t.xidusn order by t.used_ublk desc;

Step 5: Drop old undo only after it is safe.

select segment_name, status from dba_rollback_segs where tablespace_name = 'UNDOTBS4' order by segment_name;

Active transaction monitoring:

Top undo consumers:

select s.sid, s.serial#, s.username, s.program, s.module, s.sql_id, t.start_time, t.used_ublk, t.used_urec, round(t.used_ublk * p.value/1024/1024, 2) as undo_mb from v$transaction t inner join v$session s on s.taddr = t.addr cross join ( select to_number(value) value from v$parameter where name = 'db_block_size') p order by t.used_ublk desc;

With an active transaction:

Transaction Age:

select s.sid, s.serial#, s.username, round((sysdate-to_date(t.start_time, 'MM/DD/YY HH24:MI:SS'))2460,2) as age_minutes, t.used_ublk, t.used_urec from v$transaction t inner join v$session s on s.taddr = t.addr order by age_minutes desc;

with an active transaction:

Session sql:

Select s.sid, s.serial#, s.sql_id, q.sql_text from v$session s left outer join v$sql q on q.sql_id = s.sql_id where s.taddr is not null; (?)

Flashback and undo dependency:

Flashback is as its sounds, a Flashback or any image of what it was before. There is a way to display the previous data of a table which has been changed provided that data is still in undo records. For that it needs to be inside the retention period. But still it can be deleted before the retention period ends due to not having retention guarantee and space constraints in undo tablespace.

Flashback query:

Select * from undo_lab as of timestamp (systimestamp - interval '5' minute) where id = 100;

Versions query:

Select versions_starttime, versions_endtime, versions_operation, id, value, amount from undo_lab versions between timestamp (systimestamp - interval '8' minute) and systimestamp where id=100;

Limitation:

Flashback depends on undo tablespace storage being available and is not a replacement of physical and logical backup or Flashback database. (?) Maybe a database specially for holding flashback information. Need more details

Production safe undo checklist:

Before change;

Check database name, role (?), host (?), instance (?) and active undo tablespace.
Review undostat for at least 7 days including peak load.
Check maximum query duration, tuned_retention, ORA-01555 count and no space error.
Check active transaction and active undo consumers.
Check filesystem/ Automatic storage management (ASM) and autoextend limits.
Confirm backup, data gaurd impac

t and rollback plan.

Do not drop old undo until now transaction references it.

Pre change scripts:

Select name, open_mode, database_role from v$database;

show parameter undo_management; Show parameter undo_tablespace; show parameter undo_retention;

Select tablespace_name, status, retention from dba_tablespaces where contents = 'UNDO';

Select file_name, bytes/1024/1024 as size_mb, autoextensible, maxbytes/1024/1024 as max_mb from dba_data_files where tablespace_name = (select upper(value) from v$parameter where name = 'undo_tablespace');

Select count(*) as active_transactions from v$transaction;

Post change validation:

Show parameter undo_tablespace;