To clear this question we have this table:
|
|
UNDO |
REDO |
| Record of |
How to undo a change |
How to reproduce a change |
| Used for |
Rollback, Read-Consistency |
Rolling forward DB Changes |
| Stored in |
Undo segments |
Redo log files |
| Protect Against |
Inconsistent reads in multiuser systems |
Data loss |
UNDO
Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.
Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.
Undo records are used to:
- Roll back transactions when a ROLLBACK statement is issued
- Recover the database
- Provide read consistency
- Analyze data as of an earlier point in time by using Oracle Flashback Query
- Recover from logical corruptions using Oracle Flashback features
When a ROLLBACK statement is issued, undo records are used to undo changes that was made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.
REDO
Redo log files record changes to the database as a result of transactions and internal Oracle server actions. (A transaction is a logical unit of work, consisting of one or more SQL statements run by a user.)
Redo log files protect the database from the loss of integrity because of system failures caused by power outages, disk failures, and so on.
Redo log files must be multiplexed to ensure that the information stored in them is not lost in the event of a disk failure.
The redo log consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of that group, and each group is identified by a number. The LogWriter (LGWR) process writes redo records from the redo log buffer to all members of a redo log group until the file is filled or a log switch operation is requested. Then, it switches and writes to the files in the next group. Redo log groups are used in a circular fashion.
HOW TO DETECT UNDO:
There are some views that show information related to undo activity:
- V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
- V$TRANSACTION: present time view providing information on current transactions.
- V$SESSTAT: individual session statistics, which includes one for undo usage.
- V$UNDOSTAT will provide who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.
- V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:
SELECT a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC
- V$SESSTAT provides another view, which uses the undo kind of view, but we must avoid getting lost in the maze of Oracle statistics and focusing on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.
SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176<– Which stands for undo change vector size
ORDER BY a.value DESC
HOW TO DETECT REDO:
To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well. The methods are: 1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.
The query you can use is:
SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;
- Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.
- Query V$TRANSACTION. These view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).
The query you can use is:
SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session. You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.
RESUMEN
UNDO
- Record of how to undo a change.
- Used for Rollback, read-consistency
- Stored in Undo Segments
- Protect against inconsistent reads in multiuser systems
REDO
- Record of how to reproduce a change
- Used for Rolling forward database changes
- Stored in redo log files
- Protect against data loss.
An undo segment is just a segment, like a table or an index or a hash cluster or a materialized view is a segment. The clue is in the name. And the rule is that if you modify part of a segment, any segment, regardless of its type, you must generate redo so that the modification can be recovered in the event of media or instance failure. Therefore, you modify EMP; the changes to the EMP blocks are recorded in redo. The modification to EMP also has to be recorded in UNDO, because you might change your mind and want to reverse the transaction before you commit. Therefore, the modification to EMP causes entries to be made in an undo segment. But that’s a modification to a segment -this time, an undo segment. Therefore, the changes to the undo segment also have to be recorded in redo, in case you suffer a media or instance failure.
If your database now crashed and you had to restore a set of datafiles, including those for the undo tablespace, from 10 days ago, you would of course do what Oracle always does: start reading from your archived redo, rolling the 10 day old files forward in time until they were 9, then 8, then 7 then 6 and so on days old, until you get to the time where the only record of the changes to segments (any segment) was contained in the current online redo log, and then you’d use that redo log to roll the files forward until they were 8 minutes old, 7 minutes, 6, 5, 4,… and so on, right up until all changes to all segments that had ever been recorded in the redo had been applied. At which point, your undo segments have been re-populated. So now you can start rolling back those transactions which were recorded in the redo logs, but which weren’t committed at the time of the database failure.
I can’t emphasize enough, really, that undo segments are just slightly special tables. They’re fundamentally not very different from EMP or DEPT, except that new inserts into them can over-write a previous record, which never happens to EMP, of course. If you generate change vectors when you update EMP, you generate change vectors when you generate undo. Why do we store the before and after image in redo and then duplicate half of that by repeating the store of the before image in undo? Because redo is written and generated sequentially and isn’t cached for long in memory (most log buffers are a few megas in size, tops).
Therefore, using redo to rollback a mere mistake or as a result of a change of mind, whilst theoretically do-able, would involve wading through huge amounts of redo sequentially, looking for one little before image in a sea of changes made by lots of people and all of that wading would be done by reading stuff off disk (like it is in a recovery scenario). Undo, on the other hand, is stored in the buffer cache (just as EMP is stored in the buffer cache), so there’s a good chance that reading that will only require logical I/O, not physical. And your transaction is dynamically linked to where it’s written its undo, so you and your transaction can jump straight to where your undo is, without having to wade through the entire undo generated by other transactions. In performance terms, there is no comparison. Splitting ‘you need this for recovery’ from ‘you need this for changes of mind’ was a stroke of genius on the part of Oracle: other databases merely have ‘transaction logs’ which serve both purposes, and suffer in performance and flexibility terms accordingly.
USEFUL SCRIPTS:
To see the redo generated since instance started:
col name format a30 heading ‘Statistic|Name’
col value heading ‘Statistic|Value’
start title80 “Redo Log Statistics”
spool rep_out\&db\red_stat
SELECT name, value
FROM v$sysstat
WHERE name like ‘%redo%’
order by name
/
spool off
pause Press enter to continue
ttitle off
The redo generated during my session since the session started:
select value redo_size
from v$mystat, v$statname
where v$mystat.STATISTIC# = v$statname.STATISTIC#
and name = ‘redo size’
/
The redo generated by current user sessions:
select v$session.sid, username, value redo_size
from v$sesstat, v$statname, v$session
where v$sesstat.STATISTIC# = v$statname.STATISTIC#
and v$session.sid = v$sesstat.sid
and name = ‘redo size’
and value > 0
and username is not null
order by value
/
Provide a current status for redo logs:
column first_change# format 999,999,999 heading Change#
column group# format 9,999 heading Grp#
column thread# format 999 heading Th#
column sequence# format 999,999 heading Seq#
column members format 999 heading Mem
column archived format a4 heading Arc?
column first_time format a25 heading First|Time
break on thread#
set pages 60 lines 132 feedback off
start title132 ‘Current Redo Log Status’
spool rep_out\&db\log_stat
select thread#, group#, sequence#,bytes, members,archived,status,first_change#,to_char(first_time,’dd-mon-yyyy hh24:mi’) first_time
from sys.v_$log
order by thread#, group#;
spool off
pause Press Enter to continue
set pages 22 lines 80 feedback on
clear breaks
clear columns
ttitle off
/
Best Regards,
Francisco Munoz Alvarez
www.oraclenz.com
