ORACLE 11G DATABASE ARCHITECTURE
ORACLE SERVER:It is simply a machine where oracle server software is installed and an oracle database is residing.
PURPOSE OF ORACLE SERVER:
- It manages large amount of data without affecting performance of the database.
- It provides concurrent access in a multi user environment.
- It prevents unauthorized access by providing basic security of password.
- It provides bundle of recovery solutions in case of failure.
ORACLE SERVER CONSISTS OF:
- ORACLE INSTANCE
- ORACLE DATABASE
ORACLE INSTANCE:
- It is gateway to access any of the database.
- It opens one and only one database at a time
- SGA (Memory Structure of oracle) is allocated memory from physical memory(RAM) of server.
- Background processes are started in background.
ORACLE DATABASE:
It is collection of data which is treated as a single unit.
It consists of:
- Physical components : made up of operating system's physical files.
- Logical components
PHYSICAL COMPONENTS:
They are made up of operating system's physical files like
- Data Files (1) (.dbf)
- Online Redolog Files (2) (.log)
- Control Files (1) (.ctl)
- Password File
- Parameter File (.ora)
- Archive Files (.arc)
- Trace Files (.trc)
- Backup Files
Out of all the files above first 3 files (DFs, ORDLFs,CFs) are mandatory files. That is loss of any of this file results into database crash. Hence this files are known as mandatory DATABASE FILES.
Where as rest of all the files are not mandatory but required for better functionality of the database, that is loss of any of this file doesn't result into database crash .Hence this files are known as NON-DATABASE FILES.
DATA FILES:
- This files contains actual data of the database. This files are in binary format.
ONLINE REDO LOG FILES:
- It records changes done to the database by means of DML and DDL statements.
- Purpose of this file is INSTANCE RECOVERY.
CONTROL FILES:
- It records physical structure of database. (for eg: database name,timestamp of database creation, names & location of data files and redolog files, etc....)
- Purpose of this file is to maintain integrity and consistency of the database.
Parameter file:
- It contains list of non default initialization parameters. This is first file to be read while startup.
- Default location of this file is : $ORACLE_HOME/dbs
Password file:
- It contains password of user having SYSDBA or SYSOPER privileges. (not of scott,hr,etc...)
- Default location of this file is : $ORACLE_HOME/dbs
Archive files:
- This are offline copies of online redolog files and are created only if database is in archivelog mode.
- By default database is always is noarchivelog mode.
- Purpose of this files is MEDIA RECOVERY.
- Default location of this file is FLASH or FAST_RECOVERY_AREA
Trace files:
- This files are diagnostic files which records errors diagnosed by user process or background process.
- Three types of trace files are there :
- user trace file
- background trace files
- alert_SID.log file
Default location of this file is specified by DIAGONISTIC_DEST parameter($ORACLE_BASE/diag/rdbms/'dbname'/'SID'/trace)
Backup files:
- This are physical files holding of the data.
LOGICAL COMPONENTS:
- The first logical division of the database is called tablespace. Each and every database is made up of one or more than one tablespace. From oracle 10g 2 tablespaces: SYSTEM and SYSAUX are mandatory.
- Each and every tablespace is made up of one or more than one segments. Segments are nothing but database objects like tables,views,Packages,Procedures, etc...
- Each and every segment is made up of one or more than one extents. Extents are set of contiguous oracle blocks.
- Oracle Block is multiple of operating system block.
- It is defined by parameter DB_BLOCK_SIZE and its possible values are 2k or 4k or 8k or 16k or 32k
- Its default value is 8k
- This parameter is defined while database creation. Once it is configured and database is created then this parameter cannot be altered. Hence it is known as STANDARD DATABASE BLOCK SIZE.
- Oracle block is defined at finest level of granularity and it is minimum unit of I/O.
ORACLE INSTANCE:
MEMORY STRUCTURES (SGA) SYSTEM GLOBAL AREA:
- SGA is allocated memory at instance startup and deallocated at instance shutdown.
- It is Sized by parameter SGA_MAX_SIZE which is a static parameter
- But components of SGA are dynamic.
Components are:
- STREAMS POOL - sized by parameter STREAMS_POOL_SIZE
- JAVA POOL - sized by parameter JAVA_POOL_SIZE
- LARGE POOL - sized by LARGE_POOL_SIZE
- DATABASE BUFFER CACHE - sized by DB_CACHE_SIZE
- REDO LOG BUFFER - sized by LOG_BUFFER
- SHARED POOL - sized by SHARED_POOL_SIZE
- Sum of memory allocated to all this components should be less than or equal to SGA_MAX_SIZE
- Out of all this components last three i.e DBBC,RDLB,SP are mandatory whereas first three are optional memory structures to be configured only if required.
- Upto 9i all above components where to be configured manually by their parameters.
- From 10g one new parameter called SGA_TARGET is introduced. If this parameter is configured then no need to configure all above components separately, they all(except log buffer) will be allocated memory from SGA_TARGET. That means if SGA_TARGET is set to a non-zero value then AUTOMATIC SHARED MEMORY MANAGEMENT (ASMM) feature is enabled.
- SGA_TARGET is dynamic and can have maximum value upto SGA_MAX_SIZE parameter.
- DBBC : It records data blocks read from data files because of any SQL statement
- RDLB : It records changes done to database by means of DML and DDL statements. (i.e. DML/DDL statement, SCN,timestamp etc.)
- SP : It records most recently executed SQL/PLSQL statements and Most recently used data definitions.
- SP is further classified into : LIBRARY CACHE and DATA DICTIONARY CACHE
- LC : Records SQL/PLSQL statments
- DDC : Records Data definitions
LRU ALGORITHM:
DBBC,DDC,LC are regulated by LRU algorithm. According to this algorithm memory structure has two ends:
MRU END (Most Recently Used) and LRU END (Least Recently Used). All data blocks or statements enters from middle of the memory , If that data blocks or statements are repeatedly reused it moves towards MRU END and if not repeatedly reused it moves towards LRU END. Once the memroy ids full then last statement at LRU END ages out making free space for new coming entries.
USER PROCESS,SERVER PROCESS,CONNECTION,SESSION:
First of all user starts client machine then starts an aplication for eg: SQL*PLUS, then writes down connection request(i.e username/password) , at that time USER PROCESS is created on client machine.
Now this user process along with connection request will go to server via network, at server end a process called SERVER PROCESS is created for this user process. Now this server process will work on behalf of user process, so as soon as user is validated by oracle server CONNECTION will be established between USER PROCESS and ORACLE SERVER and SESSION will be started. Session is completed only when user logs out normally or abnormally.
DEDICATED AND SHARED MODE:
When per user process there is one dedicated server process it is said that database is in DEDICATED SERVER MODE. By default database is in DEDICATED MODE. Through out the session user process remains connected to same server process.
In Shared Mode, all user processes are served by few server processes.
DRL DML DDL statements Processing:
DRL : When ever a DRL (SELECT) statement is executed for first time it under goes four phases:
- Parse
- Bind
- Execute
- Fetch
In PARSE phase first of all it checks for syntax, if syntax is correct it checks for semantics( Object validation,user validation,table,column,etc) if this is done it generates a compiled code for the statement known as PARSE CODE for this statement.
In BIND phase it obtains values of bind variables if referred in the statement.
In EXECUTE phase , oracle optimizer(inbuilt function) invokes which generates best execution plan for the statement.
In FETCH stage it fetches the rows and returns to user process.
DML: It under goes only three phases:
- Parse
- Bind
- Execute
DDL : It under goes two phases:
- Parse
- Execute
CHECKPOINT EVENT:
Say suppose any of the user fires a DML statement (for eg: update emp set sal=10000 where deptno=10; ) then
pre + post images are generated and written to DBBC as well as RDLB.
After sometime say suppose user fires COMMIT at that time FAST COMMIT MECHANISM takes place under which 3 things are happening :
- POST SCN (system change or commit number) is generated and written to RDLB
- PRE Image is removed from DBBC only.
- LGWR (Logwriter) background process is invoked and starts writing all redo entries from RDLB to RDLF's
Now LGWR writes sequentially so as soon as RDLF-1 gets full it switches to RDLF-2, this event is called LOG SWITCH under which 2 things are happening simultaneously:
- LSN (Log Sequence Number) is generated.
- Checkpoint event is fired in background.
Under checkpoint Event 2 things are happening:
- Ckpt (checkpoint) background process invokes which generates ckpt no.
- Updates checkpoint information in Control Files and header of all Datafiles.
As soon as this task is over it is said that Checkpoint of RDLF-1 is over and hence it signals DBWn(Database Writer) background process to flush DIRTY BUFFERS( Buffers holding post images) from DBBC to Data Files.
Finally committed data is saved to datafiles.
While checkpoint of RDLF-1 was going on, LGWR was writing to RDLF-2 so as soon as RDLF-2 gets full it switches back to RDLF-1 but starts over writing only when checkpoint of RDLF-1 is over.
BACKGROUND PROCESSES:
There are bundle of background processes out of which five background processes are mandatory.
- LGWR (Log Writer)
- CKPT (Checkpoint)
- DBWn (Database Writer)
- PMON (Process Monitor)
- SMON (Sytem Monitor)
LGWR:
Task of this process is to write down all redo entries from RDLB to RDLF
Situations under which this process invokes:
- At commit.
- Every 3 Seconds.
- 1Mb of redo data generated
- one-third of RDLB full
- Atleast before DBWn writes.
CKPT:
Task of this process is to perform checkpoint( i.e. to generate ckptno and update checkpoint info in CFs and header of all DFs)
Situations under which this process invokes:
- At every Log Switch automatically.
- As requested by DBA manually:
- Alter system switch logfile;
- Alter system checkpoint;
- Whenever database is shutdown with normal,immediate or transactional mode.
- Whenever tablespace is taken offline,read only or in begin backup mode.
DBWn:
n stands for 0 to 9 ,by default only one process DBW0 is running.
Task of this process is to flush dirty buffers from DBBC to datafiles.
Situations under which this process invokes:
- At completion of checkpoint as signalled by ckpt process.
- Time out occurs (Fast_start_mttr_target parameter)
- Buffers reached threshold or No free buffers left
- RAC ping request made
- Whenever database is shutdown with normal,immediate or transactional mode.
- Whenever tablespace is taken offline,read only or in begin backup mode.
PMON:
Task of this process is to cleanup after USER PROCESS failure.
- It dynamically registers database services to listner.
- It releases all the resources which were in use.
- It releases all the DML locks which were held.
- It restarts dead dispatchers.
SMON:
Task of this process is to perform INSTANCE RECOVERY
Phases of instance recovery are:
- It Rolls forward all data from RDLF's to Datafiles.
- It opens the database.
- Rollback uncommitted changes from datafile by reading undo segments.
ARCHIVER BACKGROUND PROCESS (ARCn):
This is an optional background process but very important background process when database is in
ARCHIVELOG MODE.
Task of this process is to generate offline copies of online redo log files.
- It invokes at every log switch and generates an archive file
- Its default destination is $ORACLE_BASE/fast_recovery_area
- Upto 10 destinations can be defined for this files with parameter log_archive_dest_*
PROGRAM GLOBAL AREA (PGA):
This is another memory area of oracle which is allocated memory whenever a server process is created or background process is started. And deallocation whenever server process or background process is terminated.
As PGA is private per server process per background process it is also known as PRIVATE GLOBAL AREA.
It consists of UGA( Users Global area) and Stack Space.
And UGA consists of SORT AREA,CURSOR STATE,SESSION INFORMATION.
SORT AREA: This memory area is used whenever query contains any of sorting keywords like ORDER BY,GROUP BY etc..
CURSOR STATE: This memory area is run time area for DRL statements.
SESSION INFORMATION: This memory area holds information about session like session id,values of bind variables,.
STACK SPACE: This memory area is run time area for DDL/DML statements.
Whenever database is in dedicated mode UGA and stack space both are allocated memory from PGA
Whenever database is in shared mode UGA is allocated memory from SGA (SP or LP) and stack space is allocate from PGA.
The parameter to size pga is PGA_AGREGGATE_TARGET .
