2. ORACLE DATABASE ARCHITECTURE - sayanbhattacharya1234/Oracle_DBA GitHub Wiki
Oracle Database Architecture:
It is divided into 3 parts:
1. Logical Part (System Global Area or SGA)
2. Background Process or BG
3. Physical Part (Where the data are stored physically)
Execution Process of DQL Queries:
1. Instance of the database :: When a user gets connected to a database they connects to the instance of the database. The instance is a combination of System Global Area and Background Process.
2.User and Server Process :: a user execute a dql quary [ select employee_id from employees;]
When the user connects to the instance of database there is a process called User Process who is responsible to carry all instructions from user n to instance.
In the server means oracle database side there is also a process generated called Server Process who checks the quary from user process and proceed for execution. Server Process (Does all the work to execute the query) For every User there is a unique and dedicated User Process and Server Process. The query written by the user will be carried by the user process to the instance. The server process receives the query. Then it performs certain tasks.
3. First Task: Parse the query: It breaks the query into pieces and rearrenge as per requirment (depends on the server process whether it is into individual characters or individual words) and generate a hash code. This hash code generated will be stored into Library Cache of Shared Pool of System Global Area.
4. Then the server process do a syntax check on the Library Cache. If there is a syntax error then server process returns the query and the syntax error to user process. If there is no error the query moves to data dictionary.
5. Server process do a semantic check on this data dictionary. Like the query has entered a table or view which doesn't exist on the database, or a field requested by user is not present in the requested tale or view. That's not a syntax error. So in this semantic this will caught. Then the server process will return this error to the user process.
6. If all the above checks passes the server process then checks whether the user has permission to execute this query or not. All this checks are done in this data dictionary layer.
7. Once all the checking is done then the server process process comes to Optimizer.
it create multiple execution plans
optimizer have 2 factors [a] time factor and [b] resource factor.
8. From Optimizer server process chooses the one which will use the lowest resources and will also take lowest time for execution. Then it goes to the data file in the physical area.
9. Server Process comes to data File and all physical datas are stored in that data files and inside it the oracle data block created, where the data of the user query is stored and takes the copy of that data block and takes it to Database Buffer Area of System Global Area.
[ Clean block : the copied block is called clean block ]
[ Dirty block : any changes made in tht copied data blocks inside database buffer cache is call dirty block ]
10. Server Process takes the data block of user query and takes it to this area. Currently that block is called clean block. From here the Server Process returns the output to the user.
System Global Area: Basically the RAM of the database where the logical part of the database is stored. When the database is started the system global area is generated and stored in our RAM. There are different components under System Global Area.
Share Pool:
Library Cache: This takes the hashcode generated by the server process. There is one component called sql area. The sql queries of every user who are connected into the database and their hashcodes and all are stored into this sql area. THere the server process do a syntax check on the Library Cache. If there is a syntax error then server process returns the query and the syntax error to user process. If there is no error the query moves to data dictionary.
Data Dictionary: This contains the metadata (information about data) of all objects of the database. Like the data about the objects stored in the database (tables, views), like who owns the objects, where is the physical address of the objects, what are the roles and responsibilities of the user who has connected, like what are the columns of the objects etc. All of this stored in data dictionary.
Server process do a semantic check on this data dictionary. Like the query has entered a table or view which doesn't exist on the database, or a field requested by user is not present in the requested tale or view. That's not a syntax error, so it will come to this layer. So in this semantic this will caught. Then the server process will return this error to the user process.
If all the above checks passes the server process then checks whether the user has permission to execute this query or not. All this checks are done in this data dictionary layer.
Once all the checking is done then the server process process comes to Optimizer.
Optimizer: Generates multiple plan for the query execution. Like when we plan our travel accordingly to the resource available at the moment and our priority we create multiple plans and choose the optimal plan. the server process chooses the one which will use the lowest resources and will also take lowest time for execution.
Resources are things that are needed to execute a query like RAM and processor available. Server process consider this and chooses the optimum execution plan
With that plan server process go to Data File in the physical layer location
Database Buffer Cache: Server Process takes the data block of user query and takes it to this area. Currently that block is called clean block. From here the Server Process returns the output.
the entire quary is written redolog buffer cache and from redolog buffer cache its written into redo log file - this LGWR process is reponsible for write all redo log buffer cache into redo log file.
Soft passing :: if the hashcode of the quary is already available or stored in the library cache - > SQL area .
Hard passing :: Newly hashcode of the quary is generated ,means all the process are executes first time and store the hashcode in sql area.
cache hit :: If the datablock is availble already in database buffer cache - sql area.
cache miss :: server process newly take a datablock into database buffer cache - sql area.
When LGWR write redolog buffer cache into redolog file
1. every 3 second the lgwr process will write all the statements to redolog buffer cache into redolog file.
2. if 1/3 of redolog buffer cache is full then the lgwr process will write all the statements to redolog buffer cache into redolog file.
3. when the n user exxecutes the commit statement the lgwr process will write all the statements to redolog buffer cache into redolog file.
4. if 1 mb full the lgwr process will write all the statements to redolog buffer cache into redolog file.
Background Process: When we start our database this processes get started. These processes manage the entire database. They are:
SMON: System Monitor: Helps for instance recovery.
PMON: Process Monitor
DBWn: Database Writter (Lazy Writter): When the process CKPT instructs it, it will write all the dirty blocks from the database buffer cache to the data file. Its called lazy writter as it never writes on its own. It only writes when CKPT instructs it to write.
CKPT: Checkpoint: Executes every 3 seconds. It job that the 3 files in the physical area the data file, the control file, the redo log file all are in sink if not sink db will not started .
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.
2 types check point: a. Incremental CKPT
: Executes every 3 seconds. When the user commits the SCN generates in that nano second that SCN number is added to the dam statements as suffix and that is put in to the redo log file by Log Writter. The function of CKPT is to find if there is any new SCN in the suffix if there is any log with new SCN number then that SCN number IS written in the control file. Also it tells the DBWr to write the dirty blocks into data file.
Full Check Point
: Executes when the database is shutdown or there is any log switches. Same function as Incremental CKPT. Just the execution time is different.
Lets think that i used a dml query and another user also do some dml. The dmls of both of us are moved to redo log files from redo log cache by LGWR. Then I commit my chnages but the other user didn't commit. Now as both of our statements are in redo log file how will the system know which statements are commited and which statements are non commited.
Somehow we have to identify which statements are commited and when. We will need to know the time later during instance Recovery. Now from the moment the database is started every nano second a number is generated by itself. It is called SCN. System change number. Now when we commit a statement that SCN number gets attached as a preffix to all and only my DML statements. Thats the way system recognise that this statement is commited by this user.
The job of incremental CKPT it will activate in every 3 seconds and search for new SCN number and write it in control file and update the header of data file with the SCN number so that all the data file, control file and redo log file will remain in sync.
LGWR: Log Writter: This process writes the contents of the Redo log Buffer Cache into Redo Log File. The writing is done on 4 scenarios:
1. every 3 second the lgwr process will write all the statements to redolog buffer cache into redolog file.
2. if 1/3 of redolog buffer cache is full then the lgwr process will write all the statements to redolog buffer cache into redolog file.
3. when the n user exxecutes the commit statement the lgwr process will write all the statements to redolog buffer cache into redolog file.
4. if 1 mb full the lgwr process will write all the statements to redolog buffer cache into redolog file.
This statements are written because this will help database into instance recovery.
Physical Part: The data gets stored here. These are covered into 3 parts:
Component of Physical Area
1. Data File: In Data file all our data (tables and views) are stored in form of data block. The size of Oracle Data Blocks can be 2 KB, 4 KB, 8 KB, 32 KB. In industry standard practice is 8 KB default block size.
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 of System Global Area. Data Block: A table is not stored like what we see together in the screen in physical memory. They are stored randomly where they could find the space to store them. Like: Name Id Cobb 10 Din 12 These values 12, Cobb etc will be stored randomly. They will only have a address of the other records with the table address to connect them.
4 types of Data Block:
Clean Block Clear Block or New Block Pinned Block Dirty Block
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.
A. Major data files: This got created by default whenever we create a database.
1.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.
2. 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.
3. 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.
4. undotbs01.dbf: Most important. its a rollback comes from undotbs01.dbf
Like we rollback. We changed the data using this: Update employees set salary = 15000 where employee_id=102;. First the data was 10000 before. Because of the query the server process dedicated to that user will change the data in the database buffer cache and create a dirty block with the new data. That user still didn't commit.
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.dbf 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.
5. 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 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.
B. Control File:
control file is the heart of the database.
2 default control files when the database is created. control01.ctl, control02.ctl. They both have same content. If they become different then the database will not start. ### It contains the name of database, database creation date and time, meta data of all the data files and checkpoint information. Also the physical repository of backup like when the backup done, how much backup is done, which backup got obsolete, etc all this stored in control files.
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.
C .Redo Log File: The contents of Redo Log file are all the DDL and DML statements. redo01.log, redo02.log, redo03.log default redo log files.
Execution process of DML Queries: For example user has run this "Update employees set salary = 15000 where employee_id=102;" The same process at first like this:
Summary
Creation of User Process and Server Processes , Server Process will take the query from the User Process, parses it, generates hashcode
Do a syntax check in Library Cache, a semantic check in Data Dictionary.
Optimizer generates multiple execution plan and from there the server process take the optimum one and takes it to data file
Then it copies the data block or oracle data blocks related to the query and takes it to Database Buffer Cache as a clean Data Block.
Lets say the salary of the 102's was 10000. The server process changes it to 15000 according to the query. The moment the data got changed the clean data block got transformed into dirty data block. The update will be only present in the Database Buffer Cache..
In case of DML and DDL those statements are written into Redolog Buffer Cache of System Global Area. Once they are written these Redolog Buffer Cache into Redo Log file. The written is done by LGWR (Log Writter) Background Process.
Now all the dml statements of multiple users are in redo log file. When an user commits then the System change number (SCN) is attached with that users DML statement. The background process CKPT searches for these new SCN numbers and put then into control file and instruct DBWn to write the data blocks related to those dml statements and put them into data file.
Now when an user writes a dml statement afrer all the steps of copying those data blocks from data file to database buffer area the orginal data blocks in the data file a lock is applied on it so that in that time no other user can run a dml statement on those records. In that time a copy of that record are stored in undotbs01.db data file and its address are given in the data file. In that moments is some other user wants to see thoat records the server process will pick up that address and take the value from the undotbs01.db data file. When the original user commits or rollbacks the lock gets removed.
Process of Commit and Rollback:
When the commit is done the the background process CKPT tell the background process DBWn to write all the dirty blocks from the database buffer cache to the data file.
If the user rollbacks the record from undotbs01.db gets erased and the lock gets removed from the original records in the data file and the dirty block gets removed from database buffer cache.
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
Inside this location all the files will located. This is oracle base location.
1. Location of the datafiles in database architechture
Lets see where these files (datafiles, control files, rede log files ,parameter and password files) are physically:-
/u01/app/oracle/oradata/PROD
All the data files : system01.dbf sysaux01.dbf users01.dbf undotbs01.dbf temp01.dbf
All the control files : control01.ctl comtrol02.ctl
All the redo log files: redo01.log redo02.log redo03.log
All these files are binary files.