Oracle Database 10g Architecture on Windows

Any application whenever needs to be run first needs to be loaded in the memory and then only it is processed and exceuted. In oracle database software the Database which forms my physical layer also need to be accessed by the users has to be loaded in the memory first and then only its is accessible by users. This reduces the I/O contention. SO the logical layer of Oracle can be defined as to what we most commonly known as “ORCALE INSTANCE”

Description of ntqrf003.gif follows
10g Architecture
ORACLE SERVER can be on a whole divided in two broad layers:
10g arch.jpg

ORACLE INSTANCE: Instance can be defined as the gateway to the Oracle Database present on my harddisk. My instance comprises of the memory structures and background process.
Thus my INSTANCE= memory structures + background process
Lets study the memory structures in detail;
SHARED POOL: The shared pool comprises of two other components likely the library cache and the data dictionary cache. the library cahe stores the recently used sql and pl/sql statements, the hash value of the statements, and the execution plan of the statements.the data dictionary cache stores the data dictionary tables required by the sql and pl/sql statements during the parsing stage.the sizing of the shared pool is done by the parameter shared_pool_size=__
DATABASE BUFFER CACHE: The result set of the sql or pl/sql statement is stored in the database buffer cache.the sizing of the database buffer cache is sized by the parameter db_cache_size=___
REDO LOG BUFFER: Any transaction which is causing any change to my database structure is generation a redo entry which gets stored in my redo log buffer.REDO ENTRY= sql statement+reverse sql statement + transaction id (where sql+reverse sql = group of change vectors)sizing of the redo log buffer is done by the parameter log_buffer=___
LARGE POOL: Is configured to reduce the burden on my shared pool. used in case of shared server configuration. also used for storing rman’s backupand recovery operations and parallel quuery message buffering.
JAVA POOL: Stores any codes and procedures of java applications running on my oracle database software.
STREAMS POOL: Used in case of data replication for standby database present in different geographical location.
The summation of all my memory components is called as SGA (System Global Area) where then size of my SGA is defined maximum by the parameter sga_max_size.
From 10g onwards the need to size each and every component was reduced because of the automatic shared memory management feature of oracle becuase of which when we provide one parameter value that is sga_target= some value all my memory components other than the redo buffer is automatically sized.
BACKGROUND PROCESSES: Orcale background process are the process which also partly perform the job of database administration
LGWR: Writes the redo entries present in the memory from the redo buffer to the redo files. there are certain events at which the LGWR writes;at commit
every 3 secs
when 1/3rd full
when there are 1mb of changes
DBWR: Writes the modified data blocks from the dbcache to the datafiles. there are also certain events at which the dbwr writesafter LGWR writes
after CKPT signals DBWR to write
when the database buffer is full
CKPT: The checkpointing process marks the latest data modified as the checkpointing position and then signals the dbwr to write till the latest checkpoint position.
SMON: Responsible for performing instance recovery. Instance recovery is involving two stages:Roll forward where all the changes which have been lost during instance crash are all applied from the redo files to datafiles. all the commited as well as uncommited data both are applied.
Then the database is opened then comes the roll back phase where all the uncommited transaction are rolled back and then the files are brought in a consistent mode.
PMON: It is responsible in releasing the resources and locks in case of abnormal termination of user processes.
These were the five mandatory background processes which are taken up in the architecture of Oracle 10g.
the physical layer of my database consists of the actual database present on my hard drive which mainly consistes of three types of database files; namel control files ,datafiles and redo log files.
Control file: Its is the brain of my database where it stores the structural and status information of my database like the name and location of my datafiles and redo log files. it also stores the checkpoint number and the latest scn.
Datafile: It is the biggest file of the database where the actual data of database is stored.
Redo log file: The redo log file contains the redo entries generated in the memory in the redo log buffer. Works in circular fashion.
Shajib Mahmud

Leave a Reply

Your email address will not be published. Required fields are marked *