When is the logbuffer refreshed?
The log buffer is 1/3rd full
Log buffer has 1mb of redo data
Three seconds since last flush
Commit has been performed
What can end a change?
COMMIT or ROLLBACK
What is the process for instance recovery?
Recover data files
Apply redo logs files (roll forward)
Apply undo data from undo tbspc (roll back uncommitted changes).
What is the process for a transaction?
- Statement is issued
- Optimizer determines best way to retrieve data and pushes it to buffer cache
- Change is made to buffer cache data (uncommitted yet)
- Old data is written to undo tbspc and to redo log buffer.
- Any other users see the DB as DB+Undo (old state before commit)
- Redo log buffer gets flushed at least every 3 seconds to redo log files by LGWR.
- Upon commit, committed change is written to redo log buffer.
- Upon checkpoint, changes (dirty blocks in buffer cache) are written to data files by DBWn.
MANUAL or AUTO
When in automatic undo mgmt mode, this specifies the undo tbspc to use for read consistency.
If this initparam is not specified, the first available undo tbspc is assigned at startup. Use SHOW PARAMETER to see which one it chose.
Specifies, in seconds, how long committed undo info should be retained until it is overwritten.
Setting to 0 turns on automatic undo retention tuning, tuned to satisfy the longest running query to date.
Query data as it existed in the past.
Flashback version query
Query versions of rows that existed for a specified window of time
Backs out a transaction and its dependent transactions
Point in time recovery of an individual table
Not an initparam. Used in ALTER like this:
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE
Prioritizes undo retention over DML failure.
Collects 10-minute snapshots of the undo tbspc consumption.
OEM uses it in conjunction with UNDO_RETENTION parameter and DB block size to provide an optimal undo tbspc size.
SELECT … FOR UPDATE
Select that locks corresponding rows.
Table lock mode: Row Share
Permits concurrent access to locked table but prohibits exclusive table locks from other users.
Table lock mode: Row Exclusive
Permits concurrent access to locked table but prohibits exclusive or share mode locks from other users. Default for UPDATE, INSERT, DELETE.
Table lock mode: Share
Permits concurrent queries but prohibits updates to the table. Required and used by default on CREATE INDEX.
Table lock mode: Share Row Exclusive
Permits queries but prohibits Share mode locks from other users or row updates.
Table lock mode: Exclusive
Permits queries but prohibits DML from other users. Required and default for DROP TABLE.
LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT
Works for any mode. Returns control to user if locks exist rather than waiting through the lock queue.
LOCK TABLE employees IN EXCLUSIVE MODE WAIT 60
Works for any mode. If locks exist, it enters the lock queue but exits and returns control after the specified number of seconds.