René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

Oracle basic things

The data (of the database) resides in datafiles. Because these datafiles are visible (as files) they're called physical structures as opposed to logical structures.
One ore more datafiles make up a Tablespace.
Besides of datafiles, there are two other types of physical structures: redo log files and control files
The logical structures are Tablespace, schema objects, data blocks, extents, and segments.

Control Files

An Oracle Database must at least have one control file, but usually (for Backup und Recovery reasons) it has more than one (all of which are exact copies of one control file). The Control File contains a number of important information that the instance needs to operate the database. The following pieces of information are held in a control file: The name (os path) of all datafiles that the database consists of, the name of the database, the timestamp of when the database was created, the checkpoint (all database changes prior to that checkpoint are saved in the datafiles) and information for RMAN.
When a database is mounted, its control file is used to find the datafiles and redo log files for that database. Because the control file is so important, it is imperative to back up the control file whenever a structural change was made in the database.

Redo Log

Whenever something is changed on a datafile, Oracle records it in the redo log. The name redo log indicates its purpose: When the database crashes, oracle can redo all changes on datafiles which will take the database data back to the state it was when the last redo record was written. Use v$log, v$logfile, v$log_history and v$thread to find information about the redo log of your database.
Each redo log file belongs to exactly one group (of which at least two must exist). Exactly one of these groups is the CURRENT group (can be queried using the column status of v$log). Oracle uses that current group to write the redo log entries. When the group is full, a log switch occurs, making another group the current one. Each log switch causes checkpoint, however, the converse is not true: a checkpoint does not cause a redo log switch.
You can also manually cause a redo log switch: alter system switch logfile.
If you want to add a new redo log file, use this command: alter database add logfile member '/u01/adpdb/logs/l_a2' to group 2. Similarly, remove the file with alter database drop logfile member '/u01/adpdb/logs/l_a2'. A new log file group is created like this: alter database add logfile '/u01/adpdb/logs/l_a2'

Starting a database

see alse adminstrator privileges Starting a database and making it available for systemwide use consists of three steps:
  1. Start an instance of the Oracle server.
  2. Mount the database.
  3. Open the database.
When the Oracle server starts up, it uses a parameter file that contains initialization parameters. These parameters specify the name of the database, the amount of memory to allocate, the names of control files


When a user connects to a database service from across the network, a connect descriptor containing network information about the destination service is passed to the listener.

Checkpoint (CKPT): At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn; this event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint
DBWn: A background process of Oracle that writes data from memory to the datafile it belongs.
Rolling forward: the process of applying the online redo log during a recovery.
After roll forward, the datafiles contain all committed changes as well as any uncommitted changes that were recorded in the redo log. A rolling forward is followed (or should be??) by a roll back in which the rollback segments are used to identify and undo transactions that were never committed, yet were recorded in the redo log. This process is called roll back.
Rollback Segments: Rollback segments record rollback 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.
savepoints: By using savepoints, you can arbitrarily mark your work at any point within a long transaction. This allows you the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction. For example, you can use savepoints throughout a long complex series of updates, so if you make an error, you do not need to resubmit every statement.


Thanks to Amey Dhore and Matthew Lange who each corrected a typo on this page.