What is Oracle
Database?
An Oracle database is a relational database management system (RDBMS)
developed and marketed by Oracle Corporation. It's one of the most widely used
database management systems in the world, known for its robustness,
scalability, and performance.
Oracle databases are used to store and manage large volumes of data
efficiently, offering features such as data integrity, security, and SQL
support. They are commonly used in enterprise environments for various
applications including online transaction processing (OLTP), data warehousing,
and business intelligence.
Oracle databases employ a client-server architecture, where multiple
clients can connect to a central database server to access and manipulate data.
They also support features like high availability, data replication, and backup
and recovery mechanisms to ensure data reliability and availability.
Let’s look at some of the core components in the Oracle architecture, starting with instance and database.
Instance: An instance,
on the other hand, refers to the set of memory structures and background
processes that constitute the Oracle software running on a server. When an
Oracle database is started, Oracle creates an instance. The instance is
responsible for managing the database's operations, including memory
allocation, process management, and communication with client applications. It
also provides a runtime environment for executing SQL statements and
transactions. Each instance is associated with one and only one database, but
multiple instances can access the same database in a clustered or distributed
environment.
A
fundamental difference is that a database may be mounted and opened by numerous
instances through Real Application Clusters (RAC), while an instance may mount
and open only one database (one set of files) at any point in
time.
The core distinctions between a database instance and an entire running database include:
- An instance is shared
memory and a set of background processes. In contrast, a database is a
stored collection of data.
- An instance can only
mount and open one database, ever. In contrast, a database may be mounted
and opened by one or multiple instances using RAC.
Let’s look in to Oracle Instance Architecture
The Oracle
instance architecture is a key component of the Oracle Database system,
responsible for managing the database's operations and providing a runtime
environment for executing SQL statements and transactions. It consists of
several key elements:
Memory
Structure: The instance
allocates memory for various structures to manage database operations
efficiently.
The main
three components of an instance include:
1. System
Global Area (SGA): a group of shared memory structures (available to all processes) that
contain data and control information for one database instance. SGA are
allocated when the instance starts up and released when it shuts down.
2. Program
Global Area (PGA): a private memory area (available to specific processes) allocated to
each session when the session starts and released when the session ends.
3. Background
Processes: manage memory structures, one by one receiving and sending information
(also known as performing input/output (I/O)) to write data to a file on a
disk. They also perform general maintenance tasks and monitor other Oracle
Database processes to provide increased correspondence for more reliable
performance.
System Global Area (SGA): The SGA is a shared memory region that holds data and control
information for the database instance. It includes components such as the
database buffer cache (used for caching data blocks read from disk), the shared
pool (used for caching SQL statements, PL/SQL code, and data dictionary
information), and the redo log buffer (used for storing redo log entries before
they are written to the redo log files).
The SGA is
responsible for:
- Caching data blocks
read from a disk.
- Managing internal data
structures that are accessed by multiple processes and threads
simultaneously.
- Storing SQL
implementation plans.
- Buffering and writing
redo data to the online redo log files.
The Oracle processes share the
SGA, including server processes and background processes running on a single
computer. How Oracle processes are associated with the SGA varies according to
the operating system used.
A database instance includes multiple processes (background
processes, server processes, and process memory). Even when the server
processes cease, the instance will continue to function.
Program Global Area (PGA): The PGA is a
memory region allocated for each database session and contains data and control
information specific to that session, such as sort areas, session variables,
and private SQL areas.
The PGA is:
- Responsible for
processing SQL statements and holding logon and other session data.
- Dedicated to SQL “work
areas.” Work areas are active memory areas for sorts and other SQL
operations.
The sum of PGA memory distributed to all
background and server processes in a database instance is called the total instance PGA memory. The collection of all individual PGAs is called the total instance PGA or instance PGA.
Background Processes:
The Oracle
instance is supported by several background processes that perform various
tasks to manage the database. These processes include:
PMON (Process
Monitor): PMON performs
process recovery when a user process fails and releases resources held by the
failed process.
SMON (System
Monitor): SMON performs instance recovery in case of instance failure
and cleans up temporary segments that are no longer in use.
DBWn (Database
Writer): DBWn processes write modified database buffers to the data
files on disk.
LGWR (Log
Writer): LGWR writes redo log entries to the redo log files on disk to
ensure data integrity and recovery in the event of instance failure.
The LGWR is the key to the recoverability
architecture. It writes and logs every change that occurs in the database out
to a file called “redo log” for recovery purposes. The LGWR process starts with
writing changes to the memory and then to the disk as “redo logs” that can be
used for recovery.
CKPT
(Checkpoint): CKPT signals
the DBWn process to write checkpoint information to the data files and control
file, indicating the most recent changes that have been flushed to disk.
ARCH
(Archiver): ARCH processes
copy redo log files to archival storage once they are no longer needed for
recovery.
LCKn (Lock
Processes): LCKn processes
manage locks acquired by database transactions to control concurrent access to
data.
Manageability Monitor Process (MMON): The MMON gathers
performance metrics.
Memory Manager (MMAN): The MMAN automatically
controls memory inside a database.
Listener Registration Process (LREG): The LREG registers
information on the database instance and dispatcher processes with the Oracle
Net Listener.
Let’s Look into Oracle Database Architecture
Database: A database is a structured collection of data stored in a computer system. It's organized in a way that makes it easy to access, manage, and update. In the context of Oracle, a database typically refers to the physical files on disk that store data, such as tables, indexes, and other database objects. These files are managed by the Oracle Database Management System (DBMS). A database in Oracle usually has a unique name, such as "SalesDB" or "HRDB".
One of the essential tasks of
a database is to store data. There are two Main ways the database stores, and
that is through a physical and logical storage structure.
Physical
Storage Structures
The physical storage structures are simply files that store
data. When you execute a [CREATE DATABASE] statement to create a new database,
Oracle creates three files:
- Data files: contain real data (sales orders and
customer data). The data of logical database structures such as tables and
indexes are physically stored in the data files.
- Control files contain metadata describing the
physical structure (database name and locations of data files). Every
database will have a control file.
- Online redo log files: consists of two or more online
redo log files. An online redo log is made up of redo entries that record
all changes made to the data. Every database will have an online redo log.
A database includes multiple other vital files as well, such as
parameter files, network files, backup files, and archived redo log files that
assist with backup and recovery.
Logical
Storage Structures
Oracle Database uses a logical storage structure for
fine-grained control of disk space usage. The four logical storage structures
in a database are:
- Data blocks: where Oracle stores data. Also known as
logical blocks, Oracle blocks, or pages.
- Extents: a specific number of logically contiguous
data blocks used to store a particular type of information.
- Segments: a set of extents allocated for storing
database objects (a table or an index).
- Tablespaces: a logical container for a segment. Each
tablespace consists of at least one data file.