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.To change data a session will generate some redo change vectors and record them in the redo log buffer.
As it records the redo change vectors it applies the changes to the data blocks (including the undo blocks)Changes are saved to redo by the log writer process:
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 according to system activity but two or three per hour is typical. To insure a reasonable frequency of log switches set ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 2400 SCOPE=BOTH;
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.
After a commit, the log writer process (LGWR) will write redo log entries from the SGA (Log Buffer) into the online redo log file.
On the commit, your session posts a message to LGWR to write the log buffer to disc up to the point that includes the last redo change vector created by the session.
The session waits for the log writer to confirm that it has written the log before continuing.The Database Writer Process (DBWR) writes modified blocks from the database buffer cache to the data files. At no point does the commit FORCE the database writer to write the changed blocks to disc - a moment will come when DBWR just happens to wake up and decide that it’s time they were written to disk.
If a user disconnects from Oracle the current transaction is committed.
Applications should always explicitly commit or rollback transactions before program termination.
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. UNDO_MANAGEMENT=MANUALAutomatic 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. UNDO_MANAGEMENT=AUTO
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;An important caveat with this is that Oracle will not reuse space (wrap the rollback segment back around over itself) while there is an active transaction. So if you run a one hour long transaction and have a 20 minute retention - then the undo tablespace will still continue to grow for the whole hour.
If the undo space is less than the longest running transaction, you will get a 'failure to extend' error and the transaction will fail and roll back.
Even when the retention period has passed Oracle does not actively delete data from undo, it will be overwritten by the next transaction.
Typical values for an undo tablespace range from 1 GB to 20 GB. A 20% buffer of undo space is recommended to avoid excessive movement of space between undo segments.
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.
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, 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.
“To see and to be seen, in heaps they run;
Some to undo, and some to be undone” ~ John Dryden
Related
Architecture diagram (11g)
Automated Storage Management (ASM)
COMMIT
ROLLBACK