2.1 ORACLE‐ ARCHITECTURE OF DATABASE - sayanbhattacharya1234/Oracle_DBA GitHub Wiki

This is the Architecture of Database

It's divided into 3 parts

1. Logical part [its System Global Area (SGA)]

2. Background process - BG [ SMON, PMON,DBWN,CKTP,LGWR]

3. Physical area [where the data is strored physically in the database]

.

When users connect to the database basically they are connected into Instance of Database

Instance of Database : its nothing but the combination of system global area and background process.

How quaries are executes

suppose an user execute a quary [ select employee_id,first_name from employees;] so there a process called USER PROCESS : this process is responsible to carry all the informations [quaries] from the n user to the instance of Db.

for every individual user individual server process is created. after that -- when the user connects to instance ,ORACLE database creates a SERVER PROCESS:: this process who is going to check the quaries from user process and proceed for execution.

while executing of the quary , Server process perform parse the quary AND generates a #hashcode PARSE - brakedown into pieces and rearrenge them as per requirement.

Logical Part- SGA

SYSTEM GLOBAL AREA : Basically its a RAM of the database where the logical part of the database is stored. When database is started the SGA is generated and stored in RAM.

Components of SGA

I. Shared pool- Components of shared pool

1.library cache: Inside the library cache there is a part called SQL area [that is in also PGA], and in that SQL area it stored all the quaries which are executing along with hashcodes.

it performs syntax check means syntax error or not [ wrong spelling of select, no ","] if there is syntax error server process immediately return the quary to user process. if no error then quaries are fed to data dictionary.

2.Data Dictionary : Meta data of all the objects in the database. metadata is storing all the information about objects that in the database.[ex. table details , owner of the table, what are the rows,columns, who got permission].

it performs the Cementic check : it check table exist or not,field exist or not,table name check etc. if no error in cementic check it fed to optimizer.

3.Optimizer :: It creates multiple execution plans.

why? because server process chooses the optimum plan . there two factors

Time factor[fast quary executes0
Resource factor[how much and what resources are used (CPU ,RAM)] after set the optimum plan quary fed to datafile to store physically.

II.Database Buffer cache- where quaries are executed

III.java pool, IV.large pool, V.stream pool and

VI. Redolog buffer cache

For DQL :: the quary is written in redolog buffer cache and from the redolog buffer cache the quaries are written in redolog file. The log writer[LGWR] process is responsible to write all redolog buffer cache into redolog file. content of redolog buffer cache is all DQL , DML statements quaries.

WHEN WRITES

every 3 seconds this means-- LGWR process will write into redolog buffer cache to redolog file.
if 1/3 of redolog buffer cache is full -- LGWR process will write into redolog buffer cache to redolog file.
if user execute commit statement --LGWR process will write into redolog buffer cache to redolog file.
if 1 mb is full --LGWR process will write into redolog buffer cache to redolog file.

** soft passing : If the hashcode already available in sql area. means the quary execution follows the previous way.

** hard passing: Not availble in the sql area. first time generated the hashcode and executes through all steps.

** Cache Hit : if the datablock is already available in the database buffer cache.

** Cache Miss : if the server procces carry a new datablock come to the database buffer cache.

Component of Background process

1.SMON-System monitor

it helps for instance recovery.

2.PMON-Process monitor.

3.DBWn-Database writer :(Lazy Writter): When the process CKPT instructs it, it will write all the dirty blocks from the database buffer cache to the data file.

4.CKPT-check point:

every 3 second it goes to redo log file and check whether there"s any new SCN[system change number] arrived or not.

a. when the database is started in every nano second the system generates a number called SCN.

b. when n user writes ddl,dml statements and execute the commit statement that time the newly generated SCNs are attached as a prefix to that ddl ,dml statements.

c. Check point checks are redolog files inside it what are the new SCN arrive and it takes those SCN and write into Control files and it will update the header of data file.

this is two types

INCREMENTAL checkpoint

FULL checkpoint

5.LGWR-log writter :-LGWR process will write allredolog buffer cache into redolog file

Component of Physical Area

1. Data file:

In the datafiles there are oracle data blocks are created. server process cames to the datablock and take the appropiate datablock for that quary output location and create the copies the datablock and carry the copied datablocks in the database buffer cache --here output of the quary.then server process return the output to the user. the copied block is called CLEAN BLOCK.

if the copied data block are changes the values are done this momemnt it is a DIRTY BLOCK.

DATA BlOCK size = 2KB, 4KB , 8KB, 32KB is highest

Datafiles

a. system01.dbf: In this data file all the system related information and data dictionary are present. Because when we stop the database the system global area becomes blank. So they have to be in some place physically. The Data Dictionary of system global area it exists physically in system01.db data file.

b. sysaux01.dbf: This file takes a snapshot of the database every 1 hour depending upon the time set by the industry. This is due to checking the health of database. Also when we want to take a report of the database we will use this sysaux01.db data file

c. users01.dbf: This file store all the users we create who can connect to the database. All information related to users are stored in user01.db data file.

d.undotbs01.dbf : this data file is most importent. like its a rollback comes from undotbs01.dbf server process copied the datablock [5000] and in the sql area the copied block value change to 15000 . now old value is stored into undotbs01.dbf in harddrive the location of undo is stored .
Then if another user want to see the data of the salary of employee 102 they will see the old record obviously as the commit didn't occur. This is due to this undotbs.db data file. The old record gets saved in this undo data file. In the main data file the address of this undo data file comes. So when another user executed the query to see the salary of empoyee 102 the server process comes to the data file and picks up the address of the undo file and picks the data there and takes it to the database buffer cache to display.

If the user who run the update query commits then the old record in the undotbs01.dbf data file got erased. If that user rollbacks then the record from the undo data file got written in the main data file.

e. temp01.db: Important. When someone do a order by or group by or some other calculation the temp.db data file is used to do all the calculation. From there that result returns to database buffer cache and is displayed. This is due to when that calculation on an extremely large dataset like millions of records then the database buffer cache size is not enough to all those things. So this file is used for complex calculations.

We can create our own data files after this to store our own data.

2. Control file:

control file is the heart of the database

it keeps the name of database.

date and time of database creation.

metadata of all the datafiles -- where data location, how many files,how many objects all are stored in the control files.

Stored check ponit informations[SCN]

backup repository stored.

2 more physical files are there : Parameter Files

this all are binary file(0 and 1)

system parameter file[sp file]

it is a binary file sp file format is spfile(sid).ora ex- spfileprod.ora

parameter file [p file]

it is a text file p file format is init(sid).ora ex-initprod.ora

Difference of Sp and p file is

Other than the types one is binary the other is text file other than this both are same.

In spfile we cannot change parameters manually while in pfile we can change the parameters manually.

Password file:

This store the passwords of sysdba user. The users with the role sysdba, only the passwords of those users are stored. Not for all the users.

password file naming format:

orapw(sid) ex-orapwprod