A database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers. With this architecture, each new system can be rapidly provisioned from the pool of components. There is no need for peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.
As the DBA perspective Oracle is a Two tier architecture. As the Applications point of view Oracle is a Three Tier Architecture .
Example: Gmail can not open by sql prompt, we have to open through application .That's why it is 3 -Tier Architecture .
An Oracle Database consists of a Instance and Database.
An instance, or database instance, is the combination of memory and processes that are a part of a running installation and a database is a set of files that store data.
The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.
Oracle database software is designed to support the architecture of instances and databases.
The following pictures illustrates the Oracle Database server architecture.
Overview :
As the DBA perspective Oracle is a Two tier architecture. As the Applications point of view Oracle is a Three Tier Architecture .
Example: Gmail can not open by sql prompt, we have to open through application .That's why it is 3 -Tier Architecture .
An Oracle Database consists of a Instance and Database.
An instance, or database instance, is the combination of memory and processes that are a part of a running installation and a database is a set of files that store data.
The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.
Oracle database software is designed to support the architecture of instances and databases.
The following pictures illustrates the Oracle Database server architecture.
Overview :
Complete Architecture of Oracle Database Server.
As we discussed ,An Oracle Database consists of a Instance and Database.
1. Instance
A Database Instance is an interface between client applications (users) and the database. An Oracle instance consists of Two main parts:
1.1 Oracle Memory Structures.
1.2 Oracle Background Processes.
1.1 Oracle Memory Structure .
We Will Focus first on the memory components of the Oracle instance. This set of memory components represents a "living" version of Oracle that is available only when the instance is running. There are two basic memory structures on the Oracle instance.
- System Global Area or SGA
- Program Global Area or PGA.
This discussion will explain the components of the SGA and the PGA, and also cover the factors that determine the storage of information about users connected to the Oracle instance.
- System Global Area or SGA.
The SGA consists of different items, listed here.
The buffer cache
The shared pool
The redo log buffer
And we have other memory components Like
Large Pool
Streams Poll
Java Pool
The Buffer Cache :
The largest component of the SGA is usually the database buffer cache, which is the part of the SGA that holds copies of blocks of data read from the Oracle datafiles on disk. The size of the database buffer cache is controlled by the INIT.ORA parameter DB_BLOCK_BUFFERS, which specifies the number of database blocks that will be contained in the database buffer cache. Since this is expressed as database blocks, the size of the database buffer cache is the value of DB_BLOCK_BUFFERS multiplied by the DB_BLOCK_SIZE. For example, if a database has been created with a DB_BLOCK_SIZE of 8192 (8K) and DB_BLOCK_BUFFERS is set to 1000, then the database buffer cache component of the SGA would be 8192 × 1000 or 8,192,000 bytes.
When a user process needs data from the database, Oracle first checks to see if the required block is already in the database buffer cache. If it is, it is retrieved from the cache and a disk I/O operation is avoided. Oracle maintains an LRU (least recently used) list of blocks in the cache; when a block is read, its identifier is moved to the end of the list, making it the last block to be purged from the cache. One exception to this rule is that blocks read as the result of a full table scan are placed at the top of the LRU list; the assumption is that they are unlikely to be requested again soon. This behavior may be overridden by using the CACHE clause when creating or updating a table.
The Shared Pool :
The Shared Pool contains the Library Cache and the Dictionary Cache as well as a few other items, which are not in the scope of this section.
The Library Cache holds all users’ SQL statements, Functions, Procedures, and Packages. It stores the parsed SQL statement with its execution plan for reuse.
The Dictionary Cache, sometimes also referred to as the Row Cache, holds the Oracle repository data information such as tables, indexes, and columns definitions, usernames, passwords, synonyms, views, procedures, functions, packages, and privileges information.
The Redo Log Buffer:
The Redo Log Buffer holds users’ entries such as INSERT, UPDATE, DELETE, etc (DML) and CREATE TABLE, DROP TABLE (DDL).
The Redo Entries are information that will be used to reconstruct or redo, changes made to a database. The Log Writer writes the entries into the Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one-third of the Redo Log Buffer is full.
That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.
The Large Pool :
Optional area that provides large memory allocations for certain large processes, such as Oracle backup and recovery operations, and I/O server processes
The Java Pool:
Used for all session-specific Java code and data in the Java Virtual Machine (JVM).
The Streams Pool:
Used by Oracle Streams to store information required by capture and apply.
Program Global Area or PGA.
The PGA is an area in memory that helps user processes execute, such as bind variable information, sort areas, and other aspects of cursor handling. From the prior discussion of the shared pool, the DBA should know that the database already stores parse trees for recently executed SQL statements in a shared area called the library cache. So, why do the users need their own area to execute? The reason users need their own area in memory to execute is that, even though the parse information for SQL or PL/SQL may already be available, the values that the user wants to execute the search or update upon cannot be shared. The PGA is used to store real values in place of bind variables for executing SQL statements.
1.2 Oracle Background Processes.
A good deal of the discussion around users thus far speaks of processes--user processes doing this or that. In any Oracle instance, there will be user processes accessing information. . Likewise, the Oracle instance will be doing some things behind the scenes, using background processes. There are several background processes in the Oracle instance. It was mentioned in the discussion of the SGA that no user process ever interfaces directly with I/O. This setup is allowed because the Oracle instance has its own background processes that handle everything from writing changed data blocks onto disk to securing locks on remote databases for record changes in situations where the Oracle instance is set up to run in a distributed environment. The following list presents each background process and its role in the Oracle instance.
DBWR
The database writer process. This background process handles all data block writes to disk. It works in conjunction with the Oracle database buffer cache memory structure. It prevents users from ever accessing a disk to perform a data change such as update, insert, or delete.
LGWR
The log writer process. This background process handles the writing of redo log entries from the redo log buffer to online redo log files on disk. This process also writes the log sequence number of the current online redo log to the datafile headers and to the control file. Finally, LGWR handles initiating the process of clearing the dirty buffer write queue. At various times, depending on database configuration, those updated blocks are written to disk by DBWR. These events are called checkpoints. LGWR handles telling DBWR to write the changes.
SMON
The system monitor process. The usage and function of this Oracle background process is twofold. First, in the event of an instance failure—when the memory structures and processes that comprise the Oracle instance cannot continue to run—the SMON process handles recovery from that instance failure. Second, the SMON process handles disk space management issues on the database by taking smaller fragments of space and "coalescing" them, or piecing them together.
PMON
The process monitor process. PMON watches the user processes on the database to make sure that they work correctly. If for any reason a user process fails during its connection to Oracle, PMON will clean up the remnants of its activities and make sure that any changes it may have made to the system are "rolled back," or backed out of the database and reverted to their original form.
RECO
(optional) The recoverer process. In Oracle databases using the distributed option, this background process handles the resolution of distributed transactions against the database.
ARCH
(optional) The archiver process. In Oracle databases that archive their online redo logs, the ARCH process handles automatically moving a copy of the online redo log to a log archive destination.
CKPT
(optional) The checkpoint process. In high-activity databases, CKPT can be used to handle writing log sequence numbers to the datafile headers and control file, alleviating LGWR of that responsibility.
2. Database
Database is a combination of Physical and Logical structure .
he following section briefly describes the physical and logical storage structure of an Oracle Database.
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 the following files:
- Data files:
- data files contain real data, e.g., sales order and customer data. The data of logical database structures such as tables and indexes are physically stored in the data files.
- Control files:
- every database has a control file that contains metadata. The metadata describes the physical structure of the database including the database name and the locations of data files.
- Online redo log files:
- every database has an online redo log that 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.
Logical Storage Structures
Oracle Database uses a logical storage structure for fine-grained control of disk space usage. The following are logical storage structures in an Oracle Database:
Oracle Database uses a logical storage structure for fine-grained control of disk space usage. The following are logical storage structures in an Oracle Database:
- Data blocks:
- a data block corresponds to a number of bytes on the disk. Oracle stores data in data blocks. Data blocks are also referred to as logical blocks, Oracle blocks or pages.
- Extents:
- An extent is a specific number of logically contiguous data blocks used to store the particular type of information.
- Segments:
- A segment is a set of extents allocated for storing database objects, e.g., a table or an index.
- Tablespaces:
- A database is divided into logical storage units called tablespaces. A tablespace is a logical container for a segment. Each tablespace consists of at least one data file.
And the next figure shows the relationship between logical and physical storage structures:
Now, you should have a good overview of the Oracle Database architecture and its components.
