Oracle Database 12c Architecture

Oracle Database 12c Architecture

Oracle Database 12c Architecture Diagram

Oracle Database Component Explanation.

What is Oracle Instance ?

An Oracle Instance is a combination of  Memory Area + Background Processes.

What Does Oracle Database Memory Area Consists for?

An Oracle Database Memory area consists of 2 parts SGA and PGA

What is SGA ?

SGA is known as System global area. An SGA is a Group of shared Memory Areas that contain data and control information for one Oracle Database instance. All server and background processes share the SGA . did you notice the word Group of Shared Memory.  Yes its Group. Let's explain what all are the component of this Group.

As you can see in the Image above the main Components are

Database Buffer Cache

Shared Pool

Large Pool

Redo Log Buffer

Java Pool

Flashback Buffer etc.

So let us start with the most importent component and discuss each one by one

You can query the V$SGASTAT view for information about SGA components.

Database Buffer Cache

The database buffer cache, also called the buffer cache, is the memory area that stores copies of data blocks read from data files.

A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block.

A buffer could be in one of the following states

Unused
The buffer is available for use because it has never been used or is currently unused. This type of buffer is the easiest for the database to use.
Clean
This buffer was used earlier and now contains a read-consistent version of a block as of a point in time. The block contains data but is "clean" so it does not need to be checkpointed. The database can pin the block and reuse it.
Dirty
The buffer contain modified data that has not yet been written to disk. The database must checkpoint the block before reusing it.

Database buffer cache is futher managed as pools of buffers and these are

default pool

keep pool

recycle pool

for detailed explanation click here

Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database.
A redo record is a data structure that contains the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations.

The database processes copy redo entries from the user memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR (log writer) writes the redo log buffer to the active online redo log group on disk

Shared Pool

Library Cache

The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code.

Data Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users.

Server Result Cache

The server result cache is a memory pool within the shared pool. Unlike the buffer pools, the server result cache holds result sets and not data blocks.

Reserved Pool

The reserved pool is a memory area in the shared pool that Oracle Database can use to allocate large contiguous chunks of memory.

Large Pool

The large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool.

Java Pool

The Java pool is an area of memory that stores all session-specific Java code and data within the Java Virtual Machine (JVM). This memory includes Java objects that are migrated to the Java session space at end-of-call.

Streams Pool

The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes. The Streams pool is used exclusively by Oracle Streams.

Fixed SGA

The fixed SGA is an internal housekeeping area.
For example, the fixed SGA contains:

• General information about the state of the database and the instance, which the background processes need to access
• Information communicated between processes, such as information about locks

The size of the fixed SGA is set by Oracle Database and cannot be altered manually. The fixed SGA size can change from release to release

Program Global Area (PGA)

The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Because the PGA is process-specific, it is never allocated in the SGA.

Contents of the PGA

Over View of Oracle Database Processes

Types of Processes

Processes are divided into the following types:

client process

A client process runs the application or Oracle tool code.

Oracle process

An Oracle process is a unit of execution that runs the Oracle database code.

•             background process

A background process starts with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.

•            server process

A server process performs work based on a client request.

The V$PROCESS view contains one row for each Oracle process connected to a database instance

COL SPID FORMAT a8
COL STID FORMAT a8
SELECT SPID, STID, PROGRAM FROM V$PROCESS ORDER BY SPID;

Importent Background Processes

Process Monitor Process (PMON)

The process monitor (PMON) monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally

Listener Registration Process (LREG)

The listener registration process (LREG) registers information about the database instance and dispatcher processes with the Oracle Net Listener

System Monitor Process (SMON)

• Performing instance recovery, if necessary, at instance startup. In an Oracle RAC database, the SMON process of one database instance can perform instance recovery for a failed instance.
• Recovering terminated transactions that were skipped during instance recovery because of file-read or tablespace offline errors. SMON recovers the transactions when the tablespace or file is brought back online
• Cleaning up unused temporary segments. For example, Oracle Database allocates extents when creating an index. If the operation fails, then SMON cleans up the temporary space
• Coalescing contiguous free extents within dictionary-managed tablespaces.

Database Writer Process (DBW)

The database writer process (DBW) writes the contents of database buffers to data files. DBW processes write modified buffers in the database buffer cache to disk

Log Writer Process (LGWR)

The log writer process (LGWR) manages the online redo log buffer.

In the following circumstances, LGWR writes all redo entries that have been copied into the buffer since the last time it wrote:

• A user commits a transaction.
• An online redo log switch occurs.
• Three seconds have passed since LGWR last wrote.
• The redo log buffer is one-third full or contains 1 MB of buffered data.
• DBW must write modified buffers to disk.

Checkpoint Process (CKPT)

The checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBW to write blocks to disk. Checkpoint information includes the checkpoint position, SCN, and location in online redo log to begin recovery.

Manageability Monitor Processes (MMON and MMNL)

The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR).

Recoverer Process (RECO)

In a distributed database, the recoverer process (RECO) automatically resolves failures in distributed transactions

Archiver Processes (ARCn)

An archiver process (ARCn) copies online redo log files to offline storage after a redo log switch occurs.

Job Queue Processes (CJQ0 and Jnnn)

A queue process runs user jobs, often in batch mode. A job is a user-defined task scheduled to run one or more times.

Flashback Data Archive Process (FBDA)

The flashback data archive process (FBDA) archives historical rows of tracked tables into Flashback Data Archives.

Space Management Coordinator Process (SMCO)

The SMCO process coordinates the execution of various space management related tasks.

I hope you got some idea to start with. If you want to read and understand more in detail just download Database Concepts guide from oracle document library.

Oracle Block Clean Out Select Generates Redo

Oracle Block Clean Out Select Generates Redo

Oracle Block Clean Out

A buffered copy of a block is clean  if and only if the copy in memory is the same as the version on disc that is on datafile. And if the copy in memory and the version on disc differ (because something has been modified the in-memory copy) then the buffer copy is “dirty”.

The dbwr has two main reasons for writing and only one of those two is followed by a call to free the buffer that’s just been written.

One reason for writing is simply to keep the data files reasonably up to date; in this case the buffered copy of the block changes from dirty to clean but is not flushed from memory (and I think This process is known as incremental checkpoint ). The other reason is when a session has been searching for a free buffer, fails to find one soon enough, and calls DBWR to make some free space in the buffer cache – and that’s the case when DBWR may flush blocks from memory or, to be accurate, marks the buffer as free after copying them to disc (and I’m not even sure that I’ve ever proved that that’s really true). There are a couple of fairly well-known special cases where blocks really flushed from disc after the write eg truncating tables, putting tablespaces offline, flush buffer cache etc.

Commit Cleanout:

When you modify some data you will make some in memory copy of  blocks “dirty”. It is quite possible that the database writer will copy those blocks to disc (marking the buffers “clean”) before you issue your commit. When you issue the commit, your session will update its transaction table slot (which is in an undo segment header block), generating a little redo to describe this action, and then call the log writer (lgwr) to write.

Optionally, your session may also revisit a few of the blocks it has modified (nominally up to 10% of the buffer cache) and mark the associated ITL (interested transaction list) entry in those blocks with the “commit SCN”. This activity is called a commit cleanout. The commit cleanout does not do a complete tidy up on the block (it leaves lock bytes in place, for example), it simply ensures that the next process to see the block knows that your transaction has committed when it committed. This feature was introduced some around Oracle version 7.3 to reduce block pinging in OPS  (Oracle Parallel Server)

Note that any of the blocks that had previously been written by dbwr will have been made “dirty” again if your sessions does applies a commit cleanout to them, so dbwr will have to write them to disc again eventually. Although a commit cleanout changes blocks it does not create any redo to describe those changes and, strangely, it doesn’t report db block gets for the block visits that apply those changes.

Block Cleanout / Delayed Block Cleanout:

It’s possible that some of the blocks changed by your transaction were written to disc by dbwr and were even flushed from the buffer cache before you issued the commit. Your session will not re-read these blocks in order to do a commit cleanout on them. In fact, if your transaction was quite large and modified a lot of blocks, it’s quite possible that your session will not even do a commit cleanout on all of the blocks that are still in memory – Oracle doesn’t want to make the user wait for a commit to complete,  so it’s a bit lazy about commit cleanout.

At some later point in time another session may read one of those blocks and discover that the ITL includes a transaction that has committed but not been cleaned out. (It can work this out by cross-checking the ITL entry with the relevant transaction table slot in the undo segment header block).

This session will read the commit SCN from the transaction table slot, tidy up the block’s ITL entry, and clear all the related lock bytes.  (And it will do this for all committed transactions it finds on the block).  This process is known as block cleanout, and because this full cleanout mechanism never takes place on the commit it is more commonly known as delayed block cleanout.

The delayed block cleanout operation changes the block so it generates redo – which is why you may see redo being generated on a select statement, especially after a very big update. (Interestingly, Oracle will report db block changes as this happens – but doesn’t record the block visits as db block gets.)

Delayed Logging Block Cleanout

Finally, let’s go back to a block that was subject to commit cleanout. If you query this block you can see the effects of the committed transaction, but since the commit cleanout wrote the commit SCN into its ITL slot you know when the transaction committed and generally don’t need to do anything to tidy the block up as you read it. However if you want to update the block you become responsible for finishing the cleanout of the block – in fact you may even want to re-use that ITL entry and update some of the rows which are still showing a lock byte. So, at this point, you complete the block cleanout, and generate redo that describes not only the changed you are now making but also the earlier commit cleanout.  This process is known as ‘delayed logging’ block cleanout – because the redo log for the commit cleanout has been delayed until this moment.

References

http://jonathanlewis.wordpress.com/2009/06/16/clean-it-up/

Visit for more detail

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923