Oracle database Architecture

Hey Folks,

Here we will learn about oracle database architecture and its Function.

Oracle Server: An Oracle server is a Database management system that provides an OPEN, comprehensive, integrated approach to information management.

In General, an oracle server must reliably manage a large amount of data in a multi-user environment so that many users can concurrently access the same Data.

There are several files, Processes, and memory structure 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, to ensure that the database can be recovered in the event of a software or hardware error, or to perform other tasks necessary to maintain the Database.

An Oracle server must also prevent unauthorized access and provide efficient solutions for failure recovery.

Oracle Server Consists of an  Oracle Instance and Oracle Database. 

Oracle Instance:  An Oracle Instance is the combination of the background Process and the Memory structure.  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 Process is started. Background Process performs functions on behalf of the invoking process. The Background Process Perform input/output (I/O) and monitor other Oracle Processes to provide increased parallelism for better performance and Reliability.

Oracle Database: An Oracle Database consist 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.

An Oracle Database has a Logical and a Physical Structure. The physical structure of the database for the database is the set of Operation System files in the database.

An Oracle Database :

• Is a collection of data that is treated as a Unit.
• Consist if Three File Types. 

1. Data Files: Data Files Containing the actual data in the database.
2. Online Redo Log files: Online Redo Log Files containing a record of changes made to the database to enable recovery of data in case failures.
3. Control Files: Control Files containing Information necessary to Maintain and Verify database  

Other Key File Structure :

The Oracle server also uses other files that are not part of the Database :

• The Parameter File: The Parameter File defines the characteristics of an Oracle Instance. For Example, It contains parameters that size, Some of the memory structure in the SGA.
• The Password File: The password file authenticates users privileged to startup and shut down an Oracle Instance.
• Archive redo log files: Archive redo log files are offline copies of the online redo log files that may be recover from Media Failure.

        ORACLE DATABASE ARCHITECTURE DIAGRAM 

Now here we see standard oracle database architecture via Diagram. Further, we will see the one by one all the Oracle architecture components in Details in the coming few subsequent posts.

Here we see the only overview:

Virtual Structure OR memory Structure: Virtual Structure is volatile in nature as when your database instance is up, memory structure will exist and when your database instance is down, memory structure does not exist.

Oracle's memory structure consists of two memory is known as,

• System Global Area (SGA): Allocated at instance startup, and it is the fundamental component of an oracle instance.
• Program Global Area (PGA): Allocated when the server process is started.

SGA consists of many components OR divided in-memory structures components and as we can see in the above Architecture diagram. Now Let see here the little description of all memory components. I will share descriptive details for all each and every component in a few next posts.

Shared Pool: The Shared pool is an area that is Exists on RAM heap of the system when the Oracle Instance starts Up. This is one of the most important storage structure in an Oracle Database system. The Oracle shared pool contains  Oracle library cache, Data dictionary cache and In 11g Onward It contains the Result Cache also. 

Database Buffer cache:  The oracle database buffer cache is also known as buffer cache. The database buffer cache is typically the largest area if SGA holds the copy of data blocks read from the data files. The data block copies are stored in the buffer and these buffers are equals in the data block size. The database buffer cache is shared among all the sessions that are connected to the instance. 

The goal is to keep frequently used data blocks in the buffer cache in order to improve I/O by having fewer physical reads or writes. When a dirty buffer is not used anymore, it is written to the disk by the background process database writer (DBWR).

This area of the SGA contains only the buffers themselves and not their control structures. For each buffer, there is a corresponding buffer header in the variable area of the SGA.

Redo Log Buffer: The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or Redo, the changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations.

Redo entries are primarily used for database recovery as necessary.

Large Pool: The large pool is an optional memory area in the SGA. It relieves the burden placed on the shared pool by allocating session memory from the large pool for a shared server or parallel query buffers. Oracle can use the shared pool primarily for caching shared SQL and avoid the performance caused by shrinking the shared SQL cache in Shared Pool. 

Large pool provides large memory allocation for :

• Session memory for the shared server and It is also used where transaction interact with more than one database
• I/O server process
• Oracle Backup and restore operations
• Parallel execution message buffer if the initialization parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE. (Otherwise, these buffers are allocated to the shared pool)

In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for parallel buffers are allocated in buffers of a few hundred kilobytes. The large pool is better able to satisfy such large memory requests than the shared pool.

JAVA Pool: The java pool is a Memory region within the Oracle SGA and the java pool Memory is used to provide:

• Parsing if the JAVA code and Scripts
• Installation Tasks related to JAVA applications with Oracle

The JAVA pool is an optional Setting but it is required if you are installing and using JAVA. its size is set in bytes, using the JAVA_POOL_SIZE parameter. Default Size of the JAVA Pool  is  24 MB (Oracle 9i)

Streams Pool: The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes. The Streams pool is used exclusively by Oracle Streams.

Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as required by Oracle Streams.

Oracle Background Processes:  Oracle Mandatory background processes are started when oracle instance is started. These Processes are maintaining and enforces the relationships between Physical and Memory Structures.

We will see here Only ORACLE Mandatory background process.

Mandatory background Processes is:

• DBWn (Database Writer Process)
• LGWR (Log Writer Process)
• PMON (Process monitor process)
• SMON (System Monitor process)
• CKPT  ( Checkpoint Process) 
  

Physical Structure: Physical Structure is consists of oracle physical files.

• Data file: Data files Containing the actual data in the database.

• Online redo log files: Online Redo Log Files containing a record of changes made to the database to enable recovery of data in case failures.

• Control Files containing Information necessary to Maintain and Verify database  

• Parameter file

• Password File

• Archive redo log files.

Thank you !!.

See you in the next post with some more Architecture details.