Database Architecture:
A database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost-effective way to manage information and applications.
The database has Physical Components , Background Process and Logical Compoents. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting access to logical storage structures.
A. Physical Components:
a. Data File
b. Redo Log Files
c. Control Files
d. Init Parameter
e. Archive
f. Password File
g. Listener & TNS Files
B. Logical Components
a. SGA
b. PGA
C. Background Process
a. SMON
b. PMON
c. DataBase Writer
d. Log Writer
e. Archival Process
f. Checkpoint
Below Diagram will give you more idea on the Structure part of the Database.
I will take all the components description one by one:
Types of Processes
A database instance contains or interacts with the following types of processes:
• Client processes run the application or Oracle tool code.
• Oracle processes run the Oracle database code. Oracle processes including the following
Subtypes:
o Background processes start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
o Server processes perform work based on a client request.
Overview of Server Processes:
Oracle Database creates server processes to handle the requests of client processes connected to the instance. A client process always communicates with a database through a separate server process.
Mandatory Background Processes
The mandatory background processes are present in all typical database configurations. These processes run by default in a database instance started with a minimally configured initialization parameter file
This section describes the following mandatory background processes:
• Process Monitor Process (PMON)
The process monitor (PMON) monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally. PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. For example, PMON resets the status of the active transaction table, releases locks that are no longer required, and removes the process ID from the list of active processes.
• System Monitor Process (SMON)
The system monitor process (SMON) is in charge of a variety of system-level cleanup duties. The duties assigned to SMON include:
• Performing instance recovery, if necessary, at instance startup. In an Oracle RAC database, the SMON process of one database instance can perform instance recovery for a failed instance.
• Recovering terminated transactions that were skipped during instance recovery because of file-read or tablespace offline errors. SMON recovers the transactions when the tablespace or file is brought back online.
• Database Writer Process (DBWn)
The database writer process (DBWn) writes the contents of database buffers to data files. DBWn processes write modified buffers in the database buffer cache to disk (see "Database Buffer Cache").
• Log Writer Process (LGWR)
The log writer process (LGWR) manages the redo log buffer
• Checkpoint Process (CKPT)
The checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk.
Archiver Processes (ARCn)
The archiver processes (ARCn) copy online redo log files to offline storage after a redo log switch occurs
Basic Memory Structures
The basic memory structures associated with Oracle Database include:
• System global area (SGA)
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
• Program global area (PGA)
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.
One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.
• User Global Area (UGA)
The UGA is memory associated with a user session.
Database Buffer Cache
The database buffer cache, also called the buffer cache, is the memory area that stores copies of data blocks read from data files. A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block. All users concurrently connected to a database instance share access to the buffer cache.
Buffer States
The database uses internal algorithms to manage buffers in the cache. A buffer can be in any of the following mutually exclusive states:
• Unused
The buffer is available for use because it has never been used or is currently unused. This type of buffer is the easiest for the database to use.
• Clean
This buffer was used earlier and now contains a read-consistent version of a block as of a point in time. The block contains data but is "clean" so it does not need to be checkpointed. The database can pin the block and reuse it.
• Dirty
The buffer contain modified data that has not yet been written to disk. The database must checkpoint the block before reusing it.
Every buffer has an access mode: pinned or free (unpinned). A buffer is "pinned" in the cache so that it does not age out of memory while a user session accesses it. Multiple sessions cannot modify a pinned buffer at the same time.
Redo Log Buffer
The redo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. Database recovery applies redo entries to data files to reconstruct lost changes.
Oracle Database processes copy redo entries from the user memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process log writer (LGWR) writes the redo log buffer to the active online redo log group on disk
Shared Pool
The shared pool caches various types of program data. For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool.
The shared pool is divided into several subcomponents
Library Cache
The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles. In a shared server architecture, the library cache also contains private SQL areas.
When a SQL statement is executed, the database attempts to reuse previously executed code. If a parsed representation of a SQL statement exists in the library cache and can be shared, then the database reuses the code, known as a soft parse or a library cache hit. Otherwise, the database must build a new executable version of the application code, known as a hard parse or alibrary cache miss.
Shared SQL Areas
The database represents each SQL statement that it runs in the following SQL areas:
• Shared SQL area
The database uses the shared SQL area to process the first occurrence of a SQL statement. This area is accessible to all users and contains the statement parse tree and execution plan. Only one shared SQL area exists for a unique statement.
• Private SQL area
Data Dictionary Cache
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle Database accesses the data dictionary frequently during SQL statement parsing.
The data dictionary is accessed so often by Oracle Database that the following special memory locations are designated to hold dictionary data:
• Data dictionary cache
This cache holds information about database objects. The cache is also known as the row cache because it holds data as rows instead of buffers.
• Library cache
All server processes share these caches for access to data dictionary information.
A database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost-effective way to manage information and applications.
The database has Physical Components , Background Process and Logical Compoents. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting access to logical storage structures.
A. Physical Components:
a. Data File
b. Redo Log Files
c. Control Files
d. Init Parameter
e. Archive
f. Password File
g. Listener & TNS Files
B. Logical Components
a. SGA
b. PGA
C. Background Process
a. SMON
b. PMON
c. DataBase Writer
d. Log Writer
e. Archival Process
f. Checkpoint
Below Diagram will give you more idea on the Structure part of the Database.
I will take all the components description one by one:
Types of Processes
A database instance contains or interacts with the following types of processes:
• Client processes run the application or Oracle tool code.
• Oracle processes run the Oracle database code. Oracle processes including the following
Subtypes:
o Background processes start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
o Server processes perform work based on a client request.
Overview of Server Processes:
Oracle Database creates server processes to handle the requests of client processes connected to the instance. A client process always communicates with a database through a separate server process.
Mandatory Background Processes
The mandatory background processes are present in all typical database configurations. These processes run by default in a database instance started with a minimally configured initialization parameter file
This section describes the following mandatory background processes:
• Process Monitor Process (PMON)
The process monitor (PMON) monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally. PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. For example, PMON resets the status of the active transaction table, releases locks that are no longer required, and removes the process ID from the list of active processes.
• System Monitor Process (SMON)
The system monitor process (SMON) is in charge of a variety of system-level cleanup duties. The duties assigned to SMON include:
• Performing instance recovery, if necessary, at instance startup. In an Oracle RAC database, the SMON process of one database instance can perform instance recovery for a failed instance.
• Recovering terminated transactions that were skipped during instance recovery because of file-read or tablespace offline errors. SMON recovers the transactions when the tablespace or file is brought back online.
• Database Writer Process (DBWn)
The database writer process (DBWn) writes the contents of database buffers to data files. DBWn processes write modified buffers in the database buffer cache to disk (see "Database Buffer Cache").
• Log Writer Process (LGWR)
The log writer process (LGWR) manages the redo log buffer
• Checkpoint Process (CKPT)
The checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk.
Archiver Processes (ARCn)
The archiver processes (ARCn) copy online redo log files to offline storage after a redo log switch occurs
Basic Memory Structures
The basic memory structures associated with Oracle Database include:
• System global area (SGA)
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
• Program global area (PGA)
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.
One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.
• User Global Area (UGA)
The UGA is memory associated with a user session.
Database Buffer Cache
The database buffer cache, also called the buffer cache, is the memory area that stores copies of data blocks read from data files. A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block. All users concurrently connected to a database instance share access to the buffer cache.
Buffer States
The database uses internal algorithms to manage buffers in the cache. A buffer can be in any of the following mutually exclusive states:
• Unused
The buffer is available for use because it has never been used or is currently unused. This type of buffer is the easiest for the database to use.
• Clean
This buffer was used earlier and now contains a read-consistent version of a block as of a point in time. The block contains data but is "clean" so it does not need to be checkpointed. The database can pin the block and reuse it.
• Dirty
The buffer contain modified data that has not yet been written to disk. The database must checkpoint the block before reusing it.
Every buffer has an access mode: pinned or free (unpinned). A buffer is "pinned" in the cache so that it does not age out of memory while a user session accesses it. Multiple sessions cannot modify a pinned buffer at the same time.
Redo Log Buffer
The redo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. Database recovery applies redo entries to data files to reconstruct lost changes.
Oracle Database processes copy redo entries from the user memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process log writer (LGWR) writes the redo log buffer to the active online redo log group on disk
Shared Pool
The shared pool caches various types of program data. For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool.
The shared pool is divided into several subcomponents
Library Cache
The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles. In a shared server architecture, the library cache also contains private SQL areas.
When a SQL statement is executed, the database attempts to reuse previously executed code. If a parsed representation of a SQL statement exists in the library cache and can be shared, then the database reuses the code, known as a soft parse or a library cache hit. Otherwise, the database must build a new executable version of the application code, known as a hard parse or alibrary cache miss.
Shared SQL Areas
The database represents each SQL statement that it runs in the following SQL areas:
• Shared SQL area
The database uses the shared SQL area to process the first occurrence of a SQL statement. This area is accessible to all users and contains the statement parse tree and execution plan. Only one shared SQL area exists for a unique statement.
• Private SQL area
Data Dictionary Cache
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle Database accesses the data dictionary frequently during SQL statement parsing.
The data dictionary is accessed so often by Oracle Database that the following special memory locations are designated to hold dictionary data:
• Data dictionary cache
This cache holds information about database objects. The cache is also known as the row cache because it holds data as rows instead of buffers.
• Library cache
All server processes share these caches for access to data dictionary information.
No comments:
Post a Comment