Oracle: Flashback Query

The Oracle Flashback Query allows users to look backwards in time and see a consistent view of the database as it was sometime in the past. This facility is only relevant if your server is configured for Automatic Undo Management. How far backwards you can see is determined by the UNDO_RETENTION parameter (in seconds) which can be configured in the init.ora file or alternatively like this:

ALTER SYSTEM SET UNDO_RETENTION = NNNN

This facility is mainly used for recovering from accidental updates and deletes as it is not practical to set the UNDO_RETENTION to an extended period. Nevertheless it is a potentially very powerful facility.

"True humor is fun - it does not put down, kid, or mock. It makes people feel wonderful, not separate, different, and cut off. True humor has beneath it the understanding that we are all in this together." - Hugh Prather

Oracle: Flash Recovery

A new feature available in Oracle 10g is Oracle Flash Recovery. Flash Recovery is designed to make it easier and more straightforward to perform Oracle Backups and Recovery by providing a Centralised Location for all the files related to your database backup.

The Flash recovery Area is an area of a disk, or a separate disk, that is dedicated to the storage of database backup files and recovery information. It is held completely separate to all other areas of the Oracle database.

The Flash Recovery Area is used in the following ways:

Enterprise Manager can stores its backups here
Oracle can store the Archived Redo Logs here
RMAN can store and maintain details of it’s files here

In short: All files necessary to recover the database in the unlikely event of a media failure are stored as part of the Flash Recovery Area.

The default location for the Flash Recovery Area is ORACLE_BASE/flash_recovery_area. The size of this area defaults to 2GB but can be altered as necessary.

I see the Flash Recovery Area as a simple but extremely powerful and useful facility within Oracle 10g. Only the naming is unfortunate, this is not to be confused with another new feature that came out with 9i, the Flashback Query or Flashback Table facility.

"Everyone is necessarily the hero of his own life story." - John Barth

Oracle: Startup Procedure

The Oracle startup procedure follows these stages:

NOMOUNT: The Instance Starts and the Initialisation Parameter File is read and interpreted by Oracle.

MOUNT: The First Control File is read by the Oracle Instance.

OPEN: The Redo Log Files and the Datafiles are accessed by the Oracle Instance.

"Concentration comes out of a combination of confidence and hunger." - Arnold Palmer

Oracle: What Happens When ...

... An UPDATE statement is performed.

The following is an accurate (if fairly simplistic) description of what happens when an UPDATE statement is executed against an Oracle database.


The data set for the update is taken from the DB CACHE.

The ‘Pre Change’ data is written to the UNDO CACHE.

The ‘Post Update’ data is rewritten to the REDO CACHE as a delta (a piece of data that contains enough information to reconstruct the original data). The ‘Post Update’ data from the UNDO CACHE is also written to the REDO CACHE (alternatively known as the REDO LOG BUFFER).

After a COMMIT is issued the LOG WRITER Process (LGWR) writes the REDO CACHE to the REDO LOG FILES.

If ARCHIVELOG is enabled then the ARCHIVER Process (ARCH) writes a copy of the REDO LOG FILES to an alternative destination(s) (Up to 10) before the REDO LOGS are reused.

The CHECKPOINT Process (CKPT) is called regularly and this ensures that changed data (dirty blocks) are written from the DB CACHE and back to the datafiles. In addition this process updates the CONTROL FILES.

“Weaseling out of things is important to learn. It’s what separates us from the animals — except the weasel.” - Homer Simpson