Wednesday, March 28, 2007

Oracle Server Architecture in Simple Words

In order to efficiently and intelligently manage an Oracle database, you have to have a sound knowledge of its underlying architectural details. This knowledge pays off because it is directly linked with DBA's job. The real job of DBA starts after the installation of database, when he embarks on to setting-up a database and then maintaining it. Naturally, the configuration and maintenance of database cannot be done by a DBA unless he doesn't know about the ins and outs of its architecture. The following section provides a short overview of Oracle architecture.

The Oracle Server can be divided into a physical and logical portion. The physical portion is Oracle database, which resides on disks and logical portion is Instance, which resides in memory. Instance is basically a mean to access the database.

Oracle Instance: In order to access the database Oracle Server initiates some background processes and allocates some shared memory. These background processes and shared memory structures made up of Oracle Instance. The properties of instance (i.e. the properties of background processes and memory structures) are present in the parameter file in the form of parameters having some values. Oracle Server needs these parameters to create an instance while in startup stage.

Shared Memory Structures: The shared memory structure of instance is called as Shared Global Area (SGA). Basically, it contains the most frequently data and code, along with some control information. SGA divides its work into subcomponents.

Database Buffer Cache: It is commonly called as 'Buffer Cache'. Buffer Cache stores the most recent and frequent data. Any data read from the database, first comes to buffer cache and from it, multiple user can access it.

Redo Log Buffer: It stores the changes, which are made to the data and is only used for recovery purposes.

Shared Pool: It is that part of SGA, which stores most frequent and recent SQL, PL/SQL code along with data dictionary information. The code part (SQL, PL/SQL) is stored library cache of shared pool and data about schema objects is stored in data dictionary cache of shared pool.

Large Pool: It is an optional area and used to facilitate large jobs.

Java Pool: It is also an optional area used to facilitate Java code.

Stream Pool: It is also an optional area, used in case of Oracle Streams.

Background Processes: The Background processes are used to facilitate the working of instance with respect to database. Background processes are responsible for the efficient interaction of memory structures of instance and database. Some background processes are always there and some are added up as you configure more features of database. Some common background processes are:

Database Writer: It takes data from buffer cache and writes it to data files.

Log Writer: It takes information from redo log buffer and writes it to online redo logs on disk.

Checkpoint: It ensures that at continuous intervals, all modified data from buffer cache is written to data files.

System Monitor: It performs crash recovery and some space management.

Process Monitor: It performs process recovery, in case of failure of user process.

Archiver: It makes offline copies of online redo logs and helps to recover from media failure.

Private Memory Area: Besides of SGA, there is a private memory structure called Program Global Area (PGA). It contains session information for a single server process. A server process is a foreground or shadow process which acts on behalf of one or more users. If a database is in dedicated mode, then for each user there would be a separate server process, and if a database is in shared mode, then there would be some server process which would be servicing all user processes between them.

3 comments:

Jeremy Schneider said...

"Checkpoint: It ensures that at continuous intervals, all modified data from buffer cache is written to data files."

Just an additional note... there are two kinds of checkpoints in Oracle - soft and hard. There is a CKPT (checkpoint) background process but it never writes dirty blocks to datafiles. CKPT only performs soft checkpoints, meaning that it writes the SCN of the oldest block in memory to the controlfile and datafile headers.

There is also what's called a hard checkpoint but these are becoming less and less common in Oracle. This is where all dirty blocks are flushed to disk as you describe. You can trigger it manually with ALTER SYSTEM CHECKPOINT...

But anyway, great post about general Oracle concepts! It's awesome to see more people adding to the knowledge and resources available online. I think blogs and posts like this benefit the whole Oracle community in the long run - keep the good posts coming!

Fahd Mirza said...

Thanks Jeremy, this blog needs this kind of encouragment by the Oracle gurus like you.

Aman.... said...

@Jeremy

I am not really sure that the hard/soft terms are used with the checkpointing. There is surely different behaviors when it comes to checkpointing, like Thread, Global, Partial, Incremental and so on( list is long) but I haven't come across this terminology any where. Can you point me to a link where it is( or was) mentioned?

@Fahd,

Good attempt!

Aman....