Oracle Fundamentals

ORACLE ARCHITECTURE

Oracle Server: There are several files, processes, and memory structures in an Oracle server; however, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, ensure that the database can be recovered in the event of a software or hardware error, or perform other tasks necessary to maintain the database. The Oracle server consists of an Oracle Instance and an Oracle Database.

Oracle Instance: An Oracle instance is the combination of the background processes and memory structures. The instance must be started to access the data in the database. Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started.


Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

Oracle Instance Startup: has 3 distinct steps:

1. 1.Starting the instance process.
2. 2.Mounting the database, which consists of opening the control files for the instance
3. 3.Opening the database, which makes the database available for user requests.

üOracle Database: An Oracle database consists of operating system files, also known as database files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance.

Consists of 3 types of Files:

1. Data Files: containing the actual data in the database

2.Redo Log Files: containing a record of changes made to the

database to enable recovery of the data in case of failures

3.Control Files: containing information necessary to maintain

and verify database integrity.

Other key files: Non-database files are used to configure the instance, authenticate privileged users, and recover the database in the event of a disk failure.

User Processes: Started at the time a database user requests connection to the Oracle Server.

Server Processes: Connects to Oracle Instance and is started when a user establishes a session.

Background Processes: Available when an Oracle Instance is started.

Other processes: Many other processes exist that are used by other options within Oracle, such as Advanced Queuing, Real Application Clusters, Shared Server, Advanced Replication, and so on.

Memory Structure: Consists of two memory areas-
1.System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance. Shared by several processes.

SGA Consists of several memory structures:
-Shared Pool: Used to store most recently used SQL Statements and data definitions

1. Library Cache: Stores SQL Statements

2. Data Dictionary Cache: Stores Data Definition

“ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;”

-Database Buffer Cache: Stores copies of data blocks that have been retrieved from the data files.

“ALTER SYSTEM SET DB_CACHE_SIZE = 96M;”

-Redo Log Buffer Cache: Stores changes made in Data Blocks.

“LOG_BUFFER”

-Other Structures:

There are 2 other structures that can be configured in SGA:-

I.Large Pool: Configured only in Shared server environment, used for session memory, I/O slaves and backup and restore operation.

“ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;”

II.Java Pool: Requires if installing and using JAVA. Stored much the same way as PL/SQL in db.

“JAVA_POOL_SIZE”

2.Program Global Area (PGA): PGA is memory reserved for each user process that connects to an Oracle Database. Allocated when server process is started and de-allocated when process is terminated. PGA is used by One process only.

Logical Structure:
Dictates how the physical space of database is to be used. Hierarchy- Tablespaces, Segments, Extent & Blocks. Individual objects in database are stored in segments, which are collection of extents.
Block is smallest unit for read and write operations.