Oracle Architecture
In Every Oracle Server consists of an Oracle Instance and Database .
An Oracle Instance is the combination of System Global Area(SGA) and the Background Process.
Background Process is two types
1) Mandatory
2) Optional
Mandatory are
1) Process Monitor (PMON)
2) System Monitor (SMON)
3) Database Writer (DBWR)
4) Log Writer (LGWR)
5) Checkpoint (CKPT)
Optional are
1) Oracle Parallel Query Option (OPQO)
2) Multi DBWR
3) Archiver (ARCH)
4) Recoverer (Reco)
Oracle Database consists mainly these files
1) Redo Log File
2) Control File
3) Data File
4) Parameter File
5) Initialisation File (init.ora)
Redo Log file:
It stores the changed information made by the Redolog buffer Cache by the help of Log Writer (LGWR).
-It stores the redo entries
-Atleast two files is required and the size should be in KB’s.
-It contains File Id- Block Id –New Contents
Data file:
- The actual data stores in the data file. The file size should be in MBs or GBs or may be TBs.
- One data file belongs only one Tablespace.
Control File:
It Stores the crushial information about the database like
- No of data files
- Name of each data files
- Location of each data files
- Database original creation date
Parameter File:
While datatabase is starting, first it reads from the parameter file, what parameter u have set in the database. There must be atleast some parameter require to start.
Such as :
Database Name
Control File Location
Whether Rollback Segmnet or Undo Management
Background/User/Core Dump Location etc..
Password File:
Password file is required to connect database remotely from one serever to another server.
Program Global Area (PGA)
When single-threaded servers are used, the Program Global Area (PGA) contains data (such as user’s session variables and arrays) for use only by a single process. Its contents is not visible to other processes (unless Oracle multithreaded server (MTS) is used).
System Global Area (SGA)
The Shared System Global Area (SGA) contains user data and control information for a single Oracle Instance. Its contents is visible/accessible to several user applications and system processes.
Shared SQL Pool
When an SQL statement is issued by a user process, it goes through a parse tree in the shared pool.
The shared pool contains the library cache, data dictionary cache, execution plan and parse tree for SQL statements. contains library cache performance information for each type of object in the library cache.
Data Dictionary Cache
This area saves the re-reading of data from the Dictionary Table on a hard disk referenced while parsing SQL statements. It contains information such as segment information, security and access privileges, and available free storage space. It contains information such as user account data, datafile names, segment names, extent locations, table descriptions, and Privileges.
Library Cache
The Library Cache contains both shared and private SQL areas. The v$librarycache view lists performance information for each type of object in the library cache.
A private SQL area is created for each transaction initiated. It contains session-specific information, such as bind variables, environment and session parameters, runtime stacks and buffers, and so on. Each private area is deallocated after the cursor to the session is closed. The number of private SQL areas a user session can have open at one time is limited by the value of the OPEN_CURSORS init.ora parameter.
Within the private SQL area of the library cache, the persistent area contains information that is valid and applicable through multiple executions of the SQL statement. The runtime area contains data that is used only while the SQL statement is being executed.
To improver Oracle performance, Oracle uses a Shared SQL area to save in cache the intermediate results from SQL commands previously returned. So before performing a hard disk operation, Oracle can service identical requests simply by retrieving them from its cached memory for reuse. Specifically, the shared area contains the parse tree and execution path for SQL statements. It also contains headers of PL/SQL packages and procedures that have been executed.
Database Buffer Cache
The data block buffer stores the most recently queried data from the database. This is done for efficiency -- to avoid time-consuming hard disk operations. Its size is controlled by the variable DB_BLOCK_BUFFERS parameter, which has a value calculated from the size of about 1 to 2 percent of the database. This space is managed by latching and writing the Least Recently Used (LRU) block to hard disk.
The v$sqlarea view displays the text of SQL statements in the shared SQL area, plus the number of users accessing the statements, disk blocks and memory blocks accessed while executing the statement, and other information.
Redo Log Buffer Cache
The redo log buffer is used to store redo information in memory before it is flushed to the Online Redo Log Files. Its size is initialized according to the LOG_BUFFER (bytes) parameter. The number of redo log space requests can be monitored using the v$sysstat view.
Large Pool
Introduced with Oracle 8 to store user session global area infor and for parallel processing/recovery.
System Background Processes
The PROCESSES parameter in init.ora specifies the maximum number of processes accessing the database. The v$process view displays all processes that are connected to the database, including background and user processes.
The V$bgprocess view lists all background processes.
User and Server Processes (Snnn)
Applications and utilities access the RDBMS through a user process. The user process connects to a server process, which can be dedicated to one user process or shared by many user processes. The server process parses and executes SQL statements that are submitted to it and returns the result sets back to the user process. It is also the process that reads data blocks from the data files into the database buffer cache.
Four background processes are mandatory -- required for startup of each Oracle instance:
1. System Monitor (SMON)
The System monitor performs automatic crash recovery on the Oracle instance in the event it fails. If the command shutdown abort is issued by the user, Oracle doesn't have time to write modified database changes from the System Global Area (SGA) down to disk. It wakes itself up routinely to clean up free space in datafiles (much like a defragmenter for hard drives). It looks for free spaces and organizes them into contiguous areas.
2. Process Monitor (PMON)
The Process monitor performs recovery (such as releasing locks failed processes held on resources). PMON analyzes user processes that access the database, performs "garbage collection", and restarts failed processes in the database. It releases locks held by processes which failed before they released memory allocations.
3. Database Writer (DBWn)
The Database Writer (DBWR) writes and retrieves blocks from datafiles through the buffer cache. Some suggest using the DB_WRITERS parameter to define as many DBWR processes as there are physical disks used to store data files.
4. Log Writer (LGWR)
The Log Writer writes and retrieves its blocks from datafiles through the Redo Log Buffer.
5. Checkpoint Process (CKPT)
The checkpoint (CKPT) process frees up limited memory space and ensures instance recovery. It is a background process that monitors the number of dirty buffers that have not been written to disk. Depending on the LOG_CHECKPOINT_INTERVAL parameter, it also gives the DBWR a wakeup call to write dirty buffer to disk. At a specified time, it updates all the data and control files with the new log file control number. This process is optionally enabled if parameter CHECKPOINT_PROCESS contains a TRUE value.
Other background system processes ...
Redo log files are achived to tape or other media by the ARCH background process. In Oracle 8i, the DBWR_IO_SLAVES parameter defines the number of I/O slaves to improve performance. The v$archive view provides information on the archived logs written by ARCn (ARCH pre Oracle8i).
Alert Logs
Database start-ups and other commands and responses to commands to a database are stored in an Alert Log.
Trace Writer (TRWR)
The operating system Process ID for each running process are maintained in a trace file located in the file name specified in parameter BACKGROUND_DUMP_DEST. By default, that's folder /admin/INSTANCE_NAME/bdump under the ORACLE_BASE folder.
User trace Database trace start-up and other commands and responses to commands to a database are stored in an Alert Log.
Archival Process (ARCn)
The full online redo logs are copied to the archived redo log files by this process. If the database is operating in archivelog mode AND the ARCHIVE_LOG_START parameter in the init.ora file is not set to TRUE, the database will hang.
In Every Oracle Server consists of an Oracle Instance and Database .
An Oracle Instance is the combination of System Global Area(SGA) and the Background Process.
Background Process is two types
1) Mandatory
2) Optional
Mandatory are
1) Process Monitor (PMON)
2) System Monitor (SMON)
3) Database Writer (DBWR)
4) Log Writer (LGWR)
5) Checkpoint (CKPT)
Optional are
1) Oracle Parallel Query Option (OPQO)
2) Multi DBWR
3) Archiver (ARCH)
4) Recoverer (Reco)
Oracle Database consists mainly these files
1) Redo Log File
2) Control File
3) Data File
4) Parameter File
5) Initialisation File (init.ora)
Redo Log file:
It stores the changed information made by the Redolog buffer Cache by the help of Log Writer (LGWR).
-It stores the redo entries
-Atleast two files is required and the size should be in KB’s.
-It contains File Id- Block Id –New Contents
Data file:
- The actual data stores in the data file. The file size should be in MBs or GBs or may be TBs.
- One data file belongs only one Tablespace.
Control File:
It Stores the crushial information about the database like
- No of data files
- Name of each data files
- Location of each data files
- Database original creation date
Parameter File:
While datatabase is starting, first it reads from the parameter file, what parameter u have set in the database. There must be atleast some parameter require to start.
Such as :
Database Name
Control File Location
Whether Rollback Segmnet or Undo Management
Background/User/Core Dump Location etc..
Password File:
Password file is required to connect database remotely from one serever to another server.
Program Global Area (PGA)
When single-threaded servers are used, the Program Global Area (PGA) contains data (such as user’s session variables and arrays) for use only by a single process. Its contents is not visible to other processes (unless Oracle multithreaded server (MTS) is used).
System Global Area (SGA)
The Shared System Global Area (SGA) contains user data and control information for a single Oracle Instance. Its contents is visible/accessible to several user applications and system processes.
Shared SQL Pool
When an SQL statement is issued by a user process, it goes through a parse tree in the shared pool.
The shared pool contains the library cache, data dictionary cache, execution plan and parse tree for SQL statements. contains library cache performance information for each type of object in the library cache.
Data Dictionary Cache
This area saves the re-reading of data from the Dictionary Table on a hard disk referenced while parsing SQL statements. It contains information such as segment information, security and access privileges, and available free storage space. It contains information such as user account data, datafile names, segment names, extent locations, table descriptions, and Privileges.
Library Cache
The Library Cache contains both shared and private SQL areas. The v$librarycache view lists performance information for each type of object in the library cache.
A private SQL area is created for each transaction initiated. It contains session-specific information, such as bind variables, environment and session parameters, runtime stacks and buffers, and so on. Each private area is deallocated after the cursor to the session is closed. The number of private SQL areas a user session can have open at one time is limited by the value of the OPEN_CURSORS init.ora parameter.
Within the private SQL area of the library cache, the persistent area contains information that is valid and applicable through multiple executions of the SQL statement. The runtime area contains data that is used only while the SQL statement is being executed.
To improver Oracle performance, Oracle uses a Shared SQL area to save in cache the intermediate results from SQL commands previously returned. So before performing a hard disk operation, Oracle can service identical requests simply by retrieving them from its cached memory for reuse. Specifically, the shared area contains the parse tree and execution path for SQL statements. It also contains headers of PL/SQL packages and procedures that have been executed.
Database Buffer Cache
The data block buffer stores the most recently queried data from the database. This is done for efficiency -- to avoid time-consuming hard disk operations. Its size is controlled by the variable DB_BLOCK_BUFFERS parameter, which has a value calculated from the size of about 1 to 2 percent of the database. This space is managed by latching and writing the Least Recently Used (LRU) block to hard disk.
The v$sqlarea view displays the text of SQL statements in the shared SQL area, plus the number of users accessing the statements, disk blocks and memory blocks accessed while executing the statement, and other information.
Redo Log Buffer Cache
The redo log buffer is used to store redo information in memory before it is flushed to the Online Redo Log Files. Its size is initialized according to the LOG_BUFFER (bytes) parameter. The number of redo log space requests can be monitored using the v$sysstat view.
Large Pool
Introduced with Oracle 8 to store user session global area infor and for parallel processing/recovery.
System Background Processes
The PROCESSES parameter in init.ora specifies the maximum number of processes accessing the database. The v$process view displays all processes that are connected to the database, including background and user processes.
The V$bgprocess view lists all background processes.
User and Server Processes (Snnn)
Applications and utilities access the RDBMS through a user process. The user process connects to a server process, which can be dedicated to one user process or shared by many user processes. The server process parses and executes SQL statements that are submitted to it and returns the result sets back to the user process. It is also the process that reads data blocks from the data files into the database buffer cache.
Four background processes are mandatory -- required for startup of each Oracle instance:
1. System Monitor (SMON)
The System monitor performs automatic crash recovery on the Oracle instance in the event it fails. If the command shutdown abort is issued by the user, Oracle doesn't have time to write modified database changes from the System Global Area (SGA) down to disk. It wakes itself up routinely to clean up free space in datafiles (much like a defragmenter for hard drives). It looks for free spaces and organizes them into contiguous areas.
2. Process Monitor (PMON)
The Process monitor performs recovery (such as releasing locks failed processes held on resources). PMON analyzes user processes that access the database, performs "garbage collection", and restarts failed processes in the database. It releases locks held by processes which failed before they released memory allocations.
3. Database Writer (DBWn)
The Database Writer (DBWR) writes and retrieves blocks from datafiles through the buffer cache. Some suggest using the DB_WRITERS parameter to define as many DBWR processes as there are physical disks used to store data files.
4. Log Writer (LGWR)
The Log Writer writes and retrieves its blocks from datafiles through the Redo Log Buffer.
5. Checkpoint Process (CKPT)
The checkpoint (CKPT) process frees up limited memory space and ensures instance recovery. It is a background process that monitors the number of dirty buffers that have not been written to disk. Depending on the LOG_CHECKPOINT_INTERVAL parameter, it also gives the DBWR a wakeup call to write dirty buffer to disk. At a specified time, it updates all the data and control files with the new log file control number. This process is optionally enabled if parameter CHECKPOINT_PROCESS contains a TRUE value.
Other background system processes ...
Redo log files are achived to tape or other media by the ARCH background process. In Oracle 8i, the DBWR_IO_SLAVES parameter defines the number of I/O slaves to improve performance. The v$archive view provides information on the archived logs written by ARCn (ARCH pre Oracle8i).
Alert Logs
Database start-ups and other commands and responses to commands to a database are stored in an Alert Log.
Trace Writer (TRWR)
The operating system Process ID for each running process are maintained in a trace file located in the file name specified in parameter BACKGROUND_DUMP_DEST. By default, that's folder /admin/INSTANCE_NAME/bdump under the ORACLE_BASE folder.
User trace Database trace start-up and other commands and responses to commands to a database are stored in an Alert Log.
Archival Process (ARCn)
The full online redo logs are copied to the archived redo log files by this process. If the database is operating in archivelog mode AND the ARCHIVE_LOG_START parameter in the init.ora file is not set to TRUE, the database will hang.
