Oracle Architecture

ORACLE ARCHITECTURE

 

 

Oracle Architecture-

Data-

,Oracle server-we can say it as SGA+DATABASE

 

Sga-memory components (dbc,library cache, data dictionary cache--------mandatory---and optional

are----large pool ,java pool,stream pool)+background process(smon,pmon,chkpt,lgwriter,dbwriter)

 

Database-logical(tables,views,synomns, )+physical(control files ,redo log files,datafiles-(system,sysaux,undo,temp,user tablespace) and spfile and pfile)

 

Make clear definition with tutor-swapping concept(double or more size of ram)—swap space will be created on hard disk when user is idle then from memory we get in to swap as sga gets full

 

Rac means database having two or more instances

 

Oracle Architecture mainly Divided into two Structures:

=>Logical Structure:

=>Physical Structure:

 

Logical Structure:Is  Memory area of oracle  Know as INSTANCE

=>INSTANCE=SGA+PGA(9i)+BACKGROUND PROCESS

SGA

The System Global Area (SGA):- SGA is a shared memory region that Oracle uses to store data and control information for one Oracle instance.

=>The SGA is allocated when the Oracle instance starts and deallocated when the Oracle instance shuts down.

=>Each Oracle instance that starts has its own SGA. The information in the SGA consists of Components, which has a fixed size and is created at instance startup:

 

Database buffer cache--

=>This stores the most recently used data blocks. These blocks can contain modified data that has not yet been written to disk ( known as dirty blocks).

 

=>When user performs Select statement for first time data will be retrieved from database which leads to PHYSICAL READS,Second if same identical statement is fired by same user or different user the data will retrieved from Database buffer (know as logical reads)(sometimes known as clean blocks). Because the buffer cache keeps blocks based on a most recently used algorithm, the most active buffers stay in memory to reduce I/O and improve performance.

Types Buffer Blocks

From Oracle8 release onwards the buffer cache contains three buffer pools for different type of data usage. They are DEFAULT, KEEP, and RECYCLE. These three buffer pools have separate allocations of buffers and LRU lists that manage buffers.

=>RECYCLE buffer pool is used to store blocks that are virtually never used after the initial read. This pool eliminates the data blocks from the memory when no longer needed. This is more like a work area for the blocks.

=>KEEP pool is for the allocation of buffers for the objects that are accessed with medium frequency or those for which a consistent response time is desirable. This buffer pool retains the schema objects data block in memory.

=>DEFAULT buffer pool contains data blocks from schema objects that are not assigned to any buffer pool as well as for the schema objects that are explicitly assigned to the DEFAULT pool.

 

Redo log buffer--This stores redo entries. the changes made to the database(DDL & DML).

 

Java Pool:-Services parsing requirements for Java commands. Required if installing and using Java

 

 

Shared pool--This is the area of the SGA that stores shared memory structures such as shared SQL areas in the library cache and internal information in the data dictionary.

 

=> The shared pool is important because an insufficient amount of memory allocated to the shared pool can cause performance degradation.

 

Library Cache:The library cache is used to store shared SQL. Here the parse tree and the execution plan for every unique SQL statement are cached. If multiple applications issue the same SQL statement, the shared SQL area can be accessed by each to reduce the amount of memory needed and to reduce the processing time used for parsing and execution planning.

 

=> If the parse and the execution plan is prepared for every unique SQL statement

(Know as HARD PARSE).If the sql reuse the existing parse in library cache (know as SOFT PARSE)

 

Data-Dictionary Cache:The data dictionary contains a set of tables and views that Oracle uses as a reference to the database. Oracle stores information here about the logical and physical structure of the database. The data dictionary contains information such as :-

 

=>User information, such as user privileges

=>Integrity constraints defined for tables in the database

=>Names and data types of all columns in database tables

=>Information on space allocated and used for schema objects

 

Large pool:--An optional area of memory in the SGA

=> Relieves the burden placed on the Shared Pool

=> Session memory (UGA) for the Shared Server

=>I/O server processes

=>Backup and restore operations or RMAN

=>Parallel execution message buffers

 

Stream pool(10g) :- is used to provide buffer areas for the streams components of Oracle.  To configure the Streams pool explicitly,If the size of the Streams Pool is zero or not specified, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.

PGA

 

The Program Global Area (PGA):Is a memory area that contains data and control information for the Oracle server processes. The size and content of the PGA depends on the Oracle server options you have installed. This area consists of the following components:

 

=>Stack space--This is the memory that holds the session's variables, arrays, and so on.

=>Session information--If you are not running the multithreaded server, the session information is stored in the PGA. If you are running the multithreaded server, the session information is stored in the SGA.

=>Private SQL area--This is an area in the PGA where information such as binding variables and runtime buffers is kept.

 

BACKGROUND PROCESS

The Oracle RDBMS uses two types of processes: user processes and Oracle processes (also known as background processes). In some operating systems (such as Windows NT), these processes are actually threads;

 

User Processes:User, or client, processes are the user's connections to the Database.The user process manipulates the user's input and communicates with the Oracle server process through the Oracle program interface. The user process is also used to display the information requested by the user and, if necessary, can process this information into a more useful form.

Oracle Processes:Oracle processes perform functions for users. Oracle processes can be split into two groups: server processes (which perform functions for the invoking process) and background processes (which perform functions on behalf of the entire ORACLE).

 

Server Processes:Also known as shadow processes, communicate with the user and interact with Oracle to carry out the user's requests. For example, if the user process requests a piece of data not already in the SGA, the shadow process is responsible for reading the data blocks from the datafiles into the SGA. There can be a one-to-one correlation between user processes and shadow processes (as in a Dedicated server configuration); although one shadow process can connect to multiple user processes (in Shared server configuration), doing so reduces the utilization of system resources.

 

Background Processes:Used to perform various tasks within the Database. These tasks vary from communicating with other Oracle instances and performing system maintenance and cleanup to writing dirty blocks to disk. Following are brief descriptions of the nine Oracle background processes:

 

DBWR (Database Writer):DBWR is responsible for writing Dirty data blocks from the database block buffers to disk.

 

=>DBWR Writes the Data from Redologfiles to Datafiles

.

LGWR (Log Writer):LGWR responsible for writing data from the log buffer to the redo log files.

 

CKPT (Checkpoint)

 

=>CKPT process is responsible for signaling the DBWR process and Archive writes process  to  the data into respective files.

 

=>It perform a checkpoint and to update all the datafiles and control files for the database to indicate the most recent checkpoint.

 

=>A checkpoint is an event in which all modified database buffers are written to the datafiles by the DBWR.

 

=>CKPT process is optional. If the CKPT process is not present, the LGWR assumes these responsibilities.(8i)

 

 

 

PMON (Process Monitor):

Process Recovery:-PMON is responsible for keeping track of Database processes and re-establish the process if  process is terminated unexpectedly

 

Resource Release:PMON cleans up the cache and frees resources that might still be allocated.

 

Dead Detection and sloving:-A deadlock occurs when two or more session are waiting for data locked by each other, resulting in all the sessions being blocked. pmon automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are caused by poorly implemented locking in application code.

 

SMON (System Monitor)

Instance recovery:SMON performs instance recovery at instance startup.if database brougt down abnormally.

 

Rollback/Roll-forward:Smon will Recover the  transactions that have terminated because of a system crash.commited data will roll-forwarded and un commited data will be rolled back.

 

Cleaning Temporary Tablespace

=>Smon will clean the temporary segments in  Temporary Tablespace

 

Tablespace  coalescing: The SMON also defragments the database by coalescing adjacent

Free extents.

 

RECO (Recovery)--RECO is used to clean transactions that were pending in a distributed database.  committing or rolling back the data of the disputed transactions.

 

ARCH (Archiver)--ARCH is responsible for copying the online redo log files to archival storage when they become full. ARCH is active only when the RDBMS is operated in archivelog mode.

 

Optional Oracle Background Processes

Job Queue Process (CJQ) - Used for the job scheduler. The job scheduler includes a main program (the coordinator) and slave programs that the coordinator executes.

 

CQJ0 - Job queue controller process wakes up periodically and checks the job log. If a job is due, it spawns Jnnnn processes to handle jobs.

 

LMON - Lock Manager process

 

MMON - The Oracle 10g background process to collect statistics for the Automatic Workload Repository (AWR).

 

MMNL - This process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation.

 

MMAN - is used for internal database tasks that manage the automatic shared memory. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components.

PHYSICAL STRUCTURE

 

Datafiles – Permanent storage area.

 

Controlfiles:-Every Oracle database has a control file.

 

=>The database name

 

=>Names and locations of associated datafiles and online redo log files

 

=>The current log sequence number

 

=>Checkpoint information

 

Redo logfiles:-From log buffer the data (DDL&DML) comes to redolofiles.it will help oracle  in   database Recovery.

 

Password file :- it will help the user to connect database as sysdba.

 

Trace files:-Contains detailed information of ORA errors.1.User trace files 2.Process trace files and

3)system trace files.

 

Alert logfiles:-it will record ORA errors and shutdown and startup information of database.

 

Network files:-Helps the user in connecting database remotly 1.Listener file 2.tnsnames 3.sqlnet file

 

Parameter file(pfile) or Initialization Parameter file(inti.ora) and Server parameter file(9i)

(spfile.ora):- Contains prameters related to database  helps in starting the instance.

 

 

Oracle Environmental veriables

 

ORACLE_SID :-Similary to instance name.

 

ORACLE_HOME :- Loation where the oracle binaries  are installed.

 

ORACLE_BASE:-it is default location for trace file,datafiles and rman backups.this repalced with FLASH RECOVRY AREA in 10g

 

 

 

 

4) See the sga components

 

sql> sho parameter sga_target

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                           big integer 0

sql>sho  parameter db_cache_size

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 4384M

sql> sho parameter shared_pool

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

shared_pool_reserved_size            big integer 60M

shared_pool_size                     big integer 1200M

 

sql> sho parameter pga

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

pga_aggregate_target                 big integer 6G

 

 TO CHECK THE SESSIONS ON PRESENT INSTANCE

 

select inst_id, count(*) from gv$session group by inst_id;

 

 select username,machine,program from v$session group by username;

select inst_id,machine,program  from gv$session group by inst_id;