|
|
Redo
In addition to datafiles for storing database data,
every Oracle database has a set of (two or more) redo log files.
The redo log records all changes made to data, including both uncommitted and
committed changes.
Changes are saved to redo (by the log writer process) before being permanently
written to the datafiles.
The redo log can consist of two parts: the online redo log and the archived
redo log.
To avoid losing the database due to a single point of failure, Oracle
can maintain multiple sets of online redo log files.
Assuming the database operates in ARCHIVELOG mode, Oracle will create an
archived
redo log at every checkpoint - these can be used to
recover from a disk failure.
Archived
redo logs should be backed up and deleted regularly.
Online redo logs should not be backed up.
The size of a redo log file directly influences checkpoint frequency and performance. Checkpoint frequency can vary widely but two or three per hour is typical.
Rollback
Rolling back means undoing changes to data that have been performed within
an uncommitted SQL transaction.
If at any time an SQL statement causes an error, all effects of the statement
are rolled back.
The user can also request a statement-level rollback by issuing a ROLLBACK statement.
Rollback segments store the data as it was before changes were made. This is in contrast to the redo log which is a record of the insert/update/deletes.
Commit
After a commit, the log writer process (LGWR) will write redo log entries from
the SGA (Log
Buffer) into the online redo log file. The background process DBWr will then
copy the 'new' data from the redo log file into the datafile.
If a user disconnects from Oracle the current transaction is committed.
Applications should always explicitly commit or rollback transactions before
program termination.
Rollback and Undo Records
Undo records
can be stored in either rollback segments or undo tablespaces.
Rollback Segment Undo
Rollback segments have traditionally stored undo information
used by several functions of Oracle. During database recovery, after all changes
recorded in the redo log have been applied, Oracle uses rollback segment information
to undo any uncommitted transactions. Because rollback segments are stored
in the database buffers, this important recovery information is automatically
protected by the redo log.
Automatic Undo
Automatic undo management
enables
a DBA to exert control over how long undo records are retained before being
overwritten. Automatic undo management mode is more efficient, and less complex
to implement and manage than rollback segment undo.
The UNDO_POOL directive enables DBAs to control runaway transactions by grouping users into consumer groups, with each group assigned a maximum undo space limit.
Whenever the total undo space consumed by a group exceeds the limit, its users are not allowed to make any further updates, until undo space is freed up by other members (after their transactions commit or abort).
In addition to space management, a DBA can specify an UNDO_RETENTION period to minimise occurences of the "snapshot too old" error. You can set this at startup or with the ALTER SYSTEM statement.
e.g.
set retention to 20 minutes (1200 seconds):
ALTER SYSTEM SET UNDO_RETENTION =
1200;
The default value of
UNDO_POOL
is UNLIMITED.
The default value of
UNDO_RETENTION is a small
value that should be adequate for most OLTP systems.
A 20%
buffer
of undo space is recommended to avoid excessive movement of space between
undo segments.
Two-Phase Commit
In a distributed environment
Oracle ensures data consistency using the transaction model and
a two-phase commit mechanism. As in nondistributed
systems, transactions should be carefully planned to include a logical
set of SQL statements that should all succeed or fail as a unit. Oracle's two-phase
commit mechanism guarantees that, a distributed transaction will either
commit on all involved nodes or roll back on all involved nodes across
the
global distributed database.
Flashback Query
Flashback Query, lets you view and repair historical data i.e perform queries
on the database as of a certain wall clock time (or SCN)
This capability uses Oracle's multiversion read-consistency to restore data by
applying undo as needed. Administrators can configure undo retention by specifying
how long undo should be kept in the database.
The speed of this operation depends only on the amount of data being queried
and the number of changes to the data that need to be backed out.
You set the date and time you want to view. Then any SQL query you execute operates
on data as it existed at that time. You can correct errors and back out the restored
data.
Flashback Query does NOT undo anything. It is only a query mechanism. You
can take the output from a flashback query and perform an Undo yourself in
many circumstances.
- Flashback Query does NOT tell you what changed. LogMiner does that.
- Flashback Query can be used to undo changes and can be very efficient if
you know the rows that need to be moved back in time. You can in theory use
it to move a full table back in time but this is very expensive if the table
is large since it involves a full table copy.
"The difference between eggs and bacon: the chicken
is involved, the pig is committed." - Anon
See also
COMMIT
ROLLBACK
