Oracle Architecture

• SGA is an area in memory that stores information shared by all database processes and by all users of the database
• This information includes both organizational data and conrol information used by the Oracle Server
• The SGA is allocated in memory and virtual memory
• The size of the SGA can be established by a DBAby assigning a value to the parameter SGA_MAX_SIZE in the parameter file(optional parameter)

SGA and PGA (Program Global Area)

• Shared Pool
• Library Cache
• Data Dictionary Cache
• Database Buffer Cache
• Redo Log Buffer

• optional memory structures:
• Large Pool
• Java Pool
• Streams Pool

fairly large memory object that stores the actual data blocks that are retrieved from datafiles by system queries and other data manipulation language commands

• "Brain" of the software,
• pool retains blocks in memory that are likely to be reused throughout daily processing

This pool is used to store table data that is unlikely to be reused throughout daily processing - thus the data will be quickly recycled

• an optional memory structure that primarily relieves the memory burden placed on the Shared Pool. The large pool is used for the following tasks if allocated:
• Allocating space for session memory requirements from the User Global Area where a Shared Server is in use
• Transactions that interact with more than one database
• Backup and restore operations byt he Recovery Manager(RMAN) process
• Parallel excution message buffers for parallel server operations

• optional memory object, but required if the database has Oracle Java installed and in use for Oracle JVM
• Used for memory allocation to parse Java commands

• new to Oracle 10g
• This pool stores data and control structures to support the Oracle Streams feature of Oracle Enterprise Edition
• manages sharing of data and events in a distributed environment

stores images of all changes made to database blocks. Changes include: INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP

• contains the information on the system and the pieces thereof that are part of the software's functioning
• memory structure that chaches data dictionary information that has been recently used, such as user account information, datafile names, table descriptions, user privileges and other information
• If too small will lead to more disk queries, which slows down performance

memory is allocated whenever an SQL statement is parsed or a program unit is called. this enables storage of the most recently used SQL and PL/SQL statements

• Has two subcomponents:
• Shared SQL
• Shared PL/SQL Procedures and Packages

This stores/shares the execution plan and parse tree for SQL statements. If a system user executes an identical statement, then the statement does not have to be parsed again in order to execute the statement

• the procedures, processes, and rules for data manipulation are stored here
• This stores/shares the most recently used PL/SQL statements such as functions, packages, and triggers

underpining of the software

the reserved space for processing, each system would need a slightly different amount

log writer - writes the contents from the Redo Log buffer to the Redo Log File that is in use. the writes are sequential, and records database modifications in real time rather than after they are committed to.

• Database writer - creates the data files
• writes modified blocks from the database buffer cache to the datafiles. It improves system performance by caching writes of database blocks
• It writes when: checkpoints occurs, dirty buffers reach threshold, there are no free buffers, timeout occurs, RAC ping request is made, Tablespacc is OFFLINE, Tablespace READ ONLY, Table DROP or TRUNCATE, Tablespace BEGIN BACKUP

• responsible for instance recoery by applying entries in the online redo log files to the ddatafiles
• It rolls forward changes in redo logs
• rolls back uncommitted transactions
• coalesces free space
• deallocates temporary segments

the physical location on the hard drive where data is stored

resides in hard drive, storage space for all data logged by LGWR

• archiver process - takes log files and stores them safely
• it is optional, but is almost always used for production systems. The process must be used to recover from loss of a physical disk drive for systems athat are 'busy' with lots of transactions being completed
• it automatically archives online redo logs when ARCHIVELOG mode is set
• preserves the record of all changes made to the database

reside on hard drive, storage location of all redo log giles after they have been archived

• Checkpoint process, maintains data accuracy by copying datafiles to the control files, unless it finds an issue in data consistency which it will notify you of
• Responsible for signaling DBWn at checkpoints
• updating datafile headers with checkpoint information
• updating control files with checkpoint information

completed database files, considered the resource material, holds checked copies of datafiles and archived redo log files

Establishes the Connection to an Oracle Instance when a User Process requests connection - makes the connection for the user process

Starts when a database user requests to connect to an Oracle Server

• Maintains and enforces relationships between physical and memory structures
• Mandatory background processes:
• DBWn
• LGWR
• PMON
• SMON
• CKPT

• Optional background processes:
• ARCn
• CJQ0
• Dnnn
• LCKn
• LMDn
• LMON
• Pnnn
• QMNn
• REC0
• Snnn

coordinator job que - coordinator of job queue processes for an instance. It monitors the JOB$ table and starts job queue processes(Jnnn) as needed to execute jobs

Dispatcher number, for example, D000 would be the first dispatcher process - Dispatchers are optional background processes, present only whenthe shared server coniguration is used

recoverer - the recoverer process is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions

• Oracle Instance (or multiple instances)
• Oracle Database
• Server needs to manage concurrent access to the same data

• background processes
• memory structures- SGA allocated
• Provides access to one and only one Oracle database

• collection of files
• database files that store the database information that an organization needds to operate (.dbf)
• the redo log files (.log)
• archived redo log files
• parameter file, password file, alert and trace log files are also contained in the database

file that is used to specify parameters for configuring an Oracle instance when it starts up

optional file to authenticate special users of the database - privildged users, which includes database aministrators

store information about errors and actions taken that affect the configuration of the database

one to one correspondence betwen the user and server processes

more than one user process shares a server process

• contains three types of files
• Control files
• Datafiles
• Redo log files

init.ora file (PFILE) - a static parameter file. It contains parameters that specify how the database instance is to start up

spfile.ora is a dynamic prameter file. It also stores parameters to speify how to startup a database; but can be modified while the database is running

• Program Global Area - this is allocated when a Server Process starts up
• Memory is reservedd for each user process connecting to an Oracle database
• Alocated when a process is created
• deallocated when the process if terminated
• used by only one process
• Allocated outside outside of the Oracle Instance. It stores data and control information for a single server process or a single background process - it is not a shared part of memory - only one PGA to each process

memory structure that is shared by all system users. It consists of both fixed andd variable structures, stores the most recently excuted SQL statements and used data definitions.

least recently used algorithm - the process by which data is aged out of memory. Every time a unique query is created, all parsed requests are moved down the list by one. Once the list is full, each unique query is added to the top of the list, and the least recently used query is lost.

part of the database buffer cache that holds dirty buffers

buffers that hold data that has been modified, but the blocks have not been written back to disk

part of the database buffer cache that holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list

buffers that do not contain any useful data and are available for use within the database buffer cache

buffers in the database buffer cache that are currently being accessed and are therefore unabled to be used at the time

most recently used- the top of the list on the LRU list

data for binding variables and runtime memory allocations

the Private SQL Area is located in the Program Global Area

The Private SQL Area is located in the System Global Area

meomory that holds session variables and other session informtion

Memory allocated for sort, hash-join, bitmap merge, and bitmap create types of operations

store Oracle executable files running as part of the Oracle instance