ORACLE DATABASE 10g ARCHITECTURE:
In Oracle Database 10g 'g' stands for grid. In Oracle database 10g grid computing technology in introduced where it means that lets say for eg: if i have three servers configured for my organization where one server is for production, one is for online and the another one is for gaming users. If i have to configured a capacity of 10,000 users for production, 20,000 for gaming, 30,000 users capacity for gaming. If this workload is not balanced according to the desired configuration...then with the grid computing technology these resources can be diverted from one server to another server to balance the workload on the respective server.
eg: Reliance energy is responsible for distributing energy in western suburbs but due to resource shortage it is incapable of balancing the load of the energy requirement in such cases it buys energy from the Tata energy source and then diverts that energy to its consumers. This is because Reliance and Tata are in a power grid.
ORACLE SERVER AND DATABASE STRUCTURE:
An oracle database is a collection of data treated as a unit. The purpose of database is to store and retrieve related information.
A Database server is the key to solving problems of information management. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
ORACLE DATABASE SERVER can be on a whole divided in two broad layers:
1> Logical layer
2> Physical layer
Physical layer: the physical layer of my database consists of the actual database present on my hard drive which mainly consists of three types of database files; namel control files, data files and redo log files.
Logical layer: Any application whenever needs to be run first needs to be loaded in the memory and the only its is processed and executed. In oracle database software the Database which forms my physical layer also 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"
ORACLE INSTANCE:
INSTANCE can be defined as the gateway to the Oracle Database present on my hard disk. My instance comprises of the memory structures and background process.
Thus my INSTANCE= memory structures + background process
Let’s study the memory structures in detail;
1. 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=__
2. 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=___
3. 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=___
4. 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 backup and recovery operations and parallel query message buffering.
5. JAVA POOL:
Stores any codes and procedures of java applications running on my oracle database software.
6. 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 because 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.
PHYSICAL LAYER:
1. Control file: it is the brain of my database where it stores the structural 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. Along with that it stores the timestamp at which the database was created.
2. Datafile: It is the biggest file of the database where the actual data of database is stored.
3. Redo log file: the redo log file contains the redo entries generated in the memory in the redo buffer. Works in circular fashion.
BACKGROUND PROCESSES:
1. 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;
a. at commit
b. every 3 secs
c. when 1/3rd full
d. when there are 1mb of changes
2. DBWR: writes the modified data blocks from the dbcache to the data files. There are also certain events at which the dbwr writes
a. after LGWR writes
b. after CKPT signals DBWR to write
c. when the database buffer is full
3. CKPT: the check pointing process marks the latest data modified as the check pointing position and then signals the dbwr to write till the latest checkpoint position.
4. SMON:
Known as the superman of Oracle database.
Responsible for performing instance recovery.
Instance recovery is involving two stages:
1. Roll forward where all the changes which have been lost during instance crash are all applied from the redo files to datafiles. all the committed as well as uncommited data both are applied.
2. 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.
5. PMON:
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.
Other than the 5 mandatory background processes there are other optional background processes also; one of them is
1. ARCn: this background process is initiated when we configure the database in archivelog mode.
To configure the database in archivelog mode:
Startup mount
Alter database archivelog;
Alter database open;
Once the database is configured in archivelog mode my arcn process is initiated.
For that we need to understand the logical working of redo log files.
The redo log files are grouped in logical groups known as redo log groups and the files in them are known as redo log members.
When the lgwr is writing on the first member of the redo log group1 simaltaneusoly the second member is also getting updated with the same information as it is getting written in the first member. Once the first member gets full there is a log switch occurring and after the log switch follows a checkpoint and then the log writer starts writing on the second group.
When the check pointing is done on the first group then the archiver starts writing the redo entries from the first group and prepares an offline copies of the redo log files which are also known as archive log files. It is kept in the flash recovery area in the archivelog folder.