A simple guide to Oracle Database Architecture

If you are having trouble getting your head around the basics of Oracles Architecture, this video does a good job of simplifying things and making it easy to remember by breaking it down and describing it via everyday examples. You can skip to around 2:30 for the real start of the video.


Datafiles – on disk

read to memory
     data – buffer cache
     sql goes to shared pool – library cache
executed sql goes to redo log buffer
Operations on the data always happen in memory. (database buffer)
Queries gets stored in the shared library
Subsequent queries (repeats) can be retrieved directly from the shared library and the buffer cache.
On entering data, process has to wait until data is written to datafile. This is inefficient as writing data out is slow.
Faster way is to use a scrap of paper to quickly jot it down then when have time write it to the ledger
So data goes to memory then the redo buffer then on a checkpoint, gets written to disk.
Once it is in the redo log, it is considered finished.
Before modifying data, the old values are recorded in the rollback segment
When a transaction is canceled, it is rolled back by retrieving the old values from the rollback segment and putting them back.

Oracle Architecture Part 2

[embedplusvideo height=”328″ width=”584″ standard=”http://www.youtube.com/v/wmc9uxNW99E?fs=1″ vars=”ytid=wmc9uxNW99E&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep5503″ /]

Check out part 1 first.


Online Redo Logs

  • Contains Redo and Undo info
  • DML
  • Inserts/Deletes
  • DDL – Create alter drop
  • Commits
  • Need at least 2 files/groups
  • Should be multiplexed each on different devices and controlers
  • When each file fils, next one is started and old one is archived (if in Archivelogmode)

An Oracle database is comprised of datafiles, control files
and redo logs.

The second part is the memory – the SGA (system global area
or shared global memory)

Database Buffer Cache

Comprised of Oracle data blocks

There are 4 main types of data blocks, which are the same as the 4 types of data mentioned earlier:

  • Data
  • Index
  • Rollback
  • Temporary

Part 3…

How to Reduce the Size of the Undo Tablespace in Oracle

There is no function in Oracle (up to 11g currently) that will allow you to reduce the size of the undo tablespace.

Sometimes you will need to free up space and the best way to get it will be to reduce the size of the undo tablespace.

The way to do this is basically to create a new undo tablespace, drop the old one and recreate it with less space.

Here are the exact steps:

1. First, create a script to recreate the undo tablespace exactly as it was (you will need to tweak it to use less space)

In sqlplus:

You can find the location of the undo tablespace datafile with:

select * from dba_data_files;

set long 20000 (to make sure that the output isnt truncated.)

Create the SQL to recreate the tablespace later:


2. Create a second undo tablespace to use temporarily while you remove the first.


3. You need to set the new Undo tablespace as default otherwise you wont be able to delete the first;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

4. Make sure that the default has been set correctly:

SHOW PARAMETER undo_tablespace;

5. Now drop the original tablespace:


6. Use you generated SQL to create the new undo tablespace. Make sure that you change the script to set the size correctly.

7. Next, you need to set the new tablespace as the default:

ALTER SYSTEM SET undo_tablespace=UNDOTBS1;

8. Make sure that the new tablespace is now the default:

SHOW PARAMETER undo_tablespace;

9. Drop the second undo tablespace.


Oracle Undo

When data is changed in the oracle database, the information needed to undo the changes is recorded so that the change can be undone (Rolled back). The record is basically just the original information.

Information is recorded in rollback segments and the undo tablespaces.

This information is also used to provide read consistency which allows long running transactions to always obtain the data as it was at the time that it began.
The recommended method of managing Undo is system managed Undo (SMU)
Set the UNDO_MANAGEMENT initialisation parameter to AUTO

Create an Undo Tablespace


You must also set the UNDO_TABLESPACE initilization parameter

To size the undo tablespace, you need to know how long the data is kept for: UNDO_RETENTION parameter
as well as how much undo is generated per second.

Find the undo rate:

You can use the dynamic performance view v$UNDOSTAT

If you recieve the error: “Snapshot too old”, increasing the retention peroid will help.
Retention is set to 30 seconds by default which is nrmally long enough but you can set this higher if necessary.

Row Level Locking in Oracle

In Oracle, the Undo records allow data that is being modified to be accessed.

Oracle uses row level locking to prevent two processes from trying to update the same data at the same time.

Only the rows being modified are locked.

This means that the rest of the table can still be modified while a row is being changed. It also means that the whole of the table including the data that is currently being modified can still be read.

This is covered elsewhere but the key is that the data will always be consistent with its state at the time that an operation began.