This topic introduces the Oracle server architecture by examining the physical, memory, process, and logical structures involved in establishing a database connection, creating a session, and executing SQL commands.
Here we use Oracle 10g R2 (10.2.0.1)
10.2.0.1 --> means Patchset
10-->Major Version
2 -->Maintainance Release number
0 -->Application Server Release number
1 -->Component specific number
Overview of Primary Components:
What is Oracle Server ?
There are several files, processes, and memory structures 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, ensure that the database can be recovered in the event of a software or hardware error, or perform other tasks necessary to maintain the database. The Oracle server consists of an Oracle instance and an Oracle Database
What is Oracle Instance ?
An Oracle instance is the combination of the background processes and memory structures. 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 processes are started. Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability.
What is Oracle Database ?
An Oracle database consists 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 consists of three file types
- Data files containing the actual data in the database
- Redo logs containing a record of changes made to the database to enable recovery of the data in case of failures
- Control files containing information necessary to maintain and verify database integrity
Other Key File Structures
The Oracle server also uses other files that are not part of the database:• The parameter file defines the characteristics of an Oracle instance. For example, it contains parameters that size some of the memory structures in the SGA.
• The password file authenticates users privileged to start up and shut down an Oracle instance.
• Archived redo log files are offline copies of the redo log files that may be necessary to recover from media failures.
System Global Area (SGA)
The SGA is also called the shared global area. It is used to store database information that is shared by database processes. It contains data and control information for the Oracle server and is allocated in the virtual memory of the computer where Oracle resides. The following statement can be used to view SGA memory allocations:SHOW SGA;
Total System Global Area 36437964 bytes
Fixed Size 6543794 bytes
Variable Size 19521536 bytes
Database Buffers 16777216 bytes
Redo Buffers 73728 bytes
• The SGA consists of several memory structures:
– Shared pool
– Database buffer cache
– Redo log buffer
– Other structures (e.g. lock and latch management, statistical data)
• There are two optional memory structures that can be configured within the SGA:
– Large pool
– Java pool
Shared pool
The shared pool environment contains both fixed and variable structures. The fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements. The actual sizing for the fixed and variable structures is based on an initialization parameter and the work of an Oracle internal algorithm.It consists of two key performance-related memory structures:
– Library cache
– Data dictionary cache
Library cache
The library cache stores information about the most recently used SQL and PL/SQL statements. The library cache:• Enables the sharing of commonly used statements
• Is managed by a least recently used (LRU) algorithm
• Consists of two structures:
– Shared SQL area
– Shared PL/SQL area
• Has its size determined by the shared pool sizing
Data Dictionary Cache
The data dictionary cache is a collection of the most recently used definitions in the database.• It includes information about database files, tables, indexes, columns, users, privileges, and
other database objects.
• During the parse phase, the server process looks at the data dictionary for information to resolve
object names and validate access.
• Caching the data dictionary information into memory improves response time on queries.
• Size is determined by the shared pool sizing.
Database Buffer Cache
The database buffer cache stores copies of data blocks that have been retrieved from the data files.• It enables great performance gains when you obtain and update data.
• It is managed through a least recently used (LRU) algorithm.
• DB_BLOCK_SIZE determines the primary block size
Redo Log Buffer Cache
The redo log buffer cache records all changes made to the database data blocks.• Its primary purpose is recovery.
• Changes recorded within are called redo entries.
• Redo entries contain information to reconstruct or redo changes.
• Size is defined by LOG_BUFFER.
Large Pool
The large pool is an optional area of memory in the SGA configured only in a shared server environment.• It relieves the burden placed on the shared pool.
• This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore operations.
• Unlike the shared pool, the large pool does not use an LRU list.
• Sized by LARGE_POOL_SIZE.
ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;
Java Pool
The Java pool services the parsing requirements for Java commands.• Required if installing and using Java.
• It is stored much the same way as PL/SQL in database tables.
• It is sized by the JAVA_POOL_SIZE parameter.
Program Global Area(PGA)
The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process.Background Processes
The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes.• Mandatory background processes
DBWn
PMON
CKPT
LGWR
LGWR
SMON
RECO
• Optional background processes
ARCn
• Optional background processes
ARCn
LMON
Snnn
QMNn
QMNn
LMDn
CJQ0
CJQ0
Pnnn
LCKn
LCKn
Dnnn
Database Writer (DBWn)
The server process records changes to rollback and data blocks in the buffer cache. Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It ensures that a sufficient number of free buffers—buffers that can be overwritten when server processes need to read in blocks from the data files—are available in the database buffer cache. Database performance is improved because server processes make changes only in the buffer cache.DBWn defers writing to the data files until one of the following events occurs:
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
• ALTER TABLESPACE tablespace name BEGIN BACKUP
Log Writer (LGWR)
LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations:• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is
written to disk.
LGWR can also call on DBWn to write to the data files.
Note: DBWn does not write to the online redo logs.
System Monitor (SMON)
If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery consists of the following steps:1. Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because all committed transactions have been written to the redo logs, this process completely recovers these transactions.
2. Opening the database so that users can log on. Any data that is not locked by unrecovered transactions is immediately available.
3. Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server processes as they access locked data. SMON also performs some space maintenance functions:
• It combines, or coalesces, adjacent areas of free space in the data files.
• It deallocates temporary segments to return them as free space in data files. Temporary segments are used to store data during SQL statement processing.
Process Monitor (PMON)
The background process PMON cleans up after failed processes by:• Rolling back the user’s current transaction
• Releasing all currently held table or row locks
• Freeing other resources currently reserved by the user
Archiver (ARCn)
All other background processes are optional, depending on the configuration of the database; however, one of them, ARCn, is crucial to recovering a database after the loss of a disk. As online redo log files fill, the Oracle server begins writing to the next online redo log file. The process of switching from one redo log to another is called a log switch. The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log before the log can be reused, so that all of the changes made to the database are preserved. This enables the DBA to recover the database to the point of failure, even if a disk drive is damaged.Archiving Redo Log Files
One of the important decisions that a DBA has to make is whether to configure the database to operate in ARCHIVELOG or in NOARCHIVELOG mode.
NOARCHIVELOG Mode: In NOARCHIVELOG mode, the online redo log files are overwritten each time a log switch occurs. LGWR does not overwrite a redo log group until the checkpoint for that group is complete. This ensures that committed data can be recovered if there is an instance crash. During the instance crash, only the SGA is lost. There is no loss of disks, only memory. For example, an operating system crash causes an instance crash.
ARCHIVELOG Mode: If the database is configured to run in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived before they can be used again. Since changes made to the database are recorded in the online redo log files, the database administrator can use the physical backup of the data files and the archived online redo log files to recover the database without losing any committed data because of any single point of failure, including the loss of a disk. Usually, a production database is configured to run in ARCHIVELOG mode.
Logical Structure
A logical structure hierarchy exists as follows:• An Oracle database is a group of tablespaces.
• A tablespace may consist of one or more segments.
• A segment is made up of extents.
• An extent is made up of logical blocks.
• A block is the smallest unit for read and write operations.
Thanks for sharing the information
ReplyDeleteOracle Certification Courses in Noida