Oracle Database Architecture
In this post, you will learn about the ORACLE DATABASE Architecture components such as Log Writer, DB Writer,etc (Background Processes), SGA, Buffer Cache, Shared Pool, etc (Memory Layout – Memory Buffer),Datafiles, Controlfiles, etc (Physical Oracle Layout). All these components, running together play an important part in the Oracle Architecture.
Oracle Memory Components
All components such as Shared Pool (Library Cache, Dictionary Cache), Buffer Cache, Online Redo Log file, Large Pool, Java Pool as well as a few other items are referred to as the System Global Area (SGA). And the place stores information like bind variable values, sort areas, cursor handling, etc for a specific user is called Program Global Area (PGA). The PGA is used to store only real values in place of bind variables for executing SQL statements. The combination of these two memories structure while they are running is called Oracle Instance.
All components such as Log Writer (LGWR), DB Writer (DBWR), Checkpoint (CKPT), Recovery Process (RECO), Lock Process (LCKn), Archive Process (ARCH), System Monitor (SMON), and Program Monitor (PMON) are referred to as an Oracle Background processes.
All components such as Server Parameter File (SPFILE), Parameter File (PFILE the INIT.ORA file), and Controlfiles, Datafiles, Password File, Archives and Online Redo Log files are referred to as an Oracle Database Components.
All these Oracle components running together allow users to read, write, and modify data in an Oracle database.
Now, the following are brief job descriptions for above components.
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 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 Buffer Cache holds users’ data. Users query their data while they are in the Buffer Cache. If user’s request is not in the Buffer Cache then server process has to bring it from disk. The smallest unit in the buffer cache is an Oracle block. The buffer cache can be increased or decreased by granule unit. The smallest Granule Unit is 4Meg if the SGA size is less than 128Meg and the smallest Granule Unit become 16Meg is the SGA size is more than 128Meg.
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 holds information about the Recovery Manager (RMAN) utility when RMAN is running. If you use the Multi-threaded Server (MTS) process, you may allocate the Oracle Memory structure such that you can get advantage of using Large Pool instead of the Shared Pool. Notice that when you use dedicated servers, user session information is housed in the PGA.
The Multi-threaded Server process will be used when a user send his/her request by using a shared server. A user’s request will be assigned to a dispatcher based on the availability of dispatchers. Then the dispatcher will send or receive request from an assigned shared server.
When you are running dedicated servers then the session information can be stored inside the process global area (PGA). The UGA is the user global area, which holds session-based information. When you are running shared servers then the session information can be stored inside the user global area (UGA) and when your session does some sorting, some of the memory allocated for sorting – specifically the amount defined by parameter sort_area_retained_size – comes from the SGA and the rest (up to sort_area_size) comes from the PGA (Snnn). This is because the sort_area_retained_size may have to be held open as the pipeline to return results to the front-end, so it has to be located where the session can find it again as the session migrate from server to server. On the other hand, the sort_area_size is a complete throwaway, and by locating it in the PGA, Oracle can make best use of available memory without soaking the SGA. To avoid sessions grabbing too much memory in the SGA when running MTS/shared server, you can set the private_sga value in the resource_limit for the user. This ensures that any particularly greedy SQL that (for example) demands multiple allocations of sort_area_retained_size will crash rather than
flushing and exhausting the SGA.
Log Writer Background Process (LGWR):
The LGWR’s job is to write the redo user’s entries from the Redo Log Buffer when the buffer exceeds one third of the Redo Log Buffer, every 3 seconds, or when a user executes the commit SQL statement.
DB Writer Background Process (DBWR):
The DBWR’s job is to write all the blocks that were marked as dirty block to the Oracle database on disks (datafiles) whenever the checkpoint process signals it. Notice that when the Online Redo Log files are filled the checkpoint process will signals a DBWR to write the entire dirty block into the Oracle database.
CheckPoint Background Process (CKPT):
The Checkpoint signals DB writers to write all dirty blocks into the disk. The Checkpoint will occurred either by a specific defined time, size of the Online Redo Log file used by DBA, or when an Online Redo log file will be switched from on log file to another. The following are the parameters that will be used by a DBA to adjust time or interval of how frequently its checkpoint should occur on its database.
LOG_CHECKPOINT_TIMEOUT = 3600 # every one hour
LOG_CHECKPOINT_INTERVAL=1000 # number of Operating System blocks
Recovery Background Process (RECO):
The RECO will be used only if you have a distributed database. You use this process to recover a database if a failure occurs due to physical server problem or communication problem.
LOCK Background Process (LCKN):
The LCKn background process will be used if you have multiple instances accessing to only one database. An example of that is a Parallel Server or a Real Application Clusters.
ARCHIVE Background Process (ARCH):
This background process archives the Online Redo Log file when you are manually or automatically switching an Online Redo Log file. An example of manually switching is:
ALTER SYSTEM SWITCH OGFILE or ALTER SYSTEM ARCHIVE LOG CURRENT.
System Monitor Background Process (SMON):
When you start your database, the SMON will make sure that all datafiles, controlfiles, and log files are synchronized before opening a database. If they are no, it will perform an instance recovery. It will check the last SCN that was performed against the datafiles. If it finds that there are transactions that were not applied against the datafile, then it will recover or synchronize the datafile from either the Online Redo Log files or the Archive Log files. The smaller Online Redo log files will bring a faster database recovery.
Program Monitor Background Process (PMON):
A user may be disconnected either by cancelling its session or by communication link. In either act, the PMON will start and perform an action to clean the reminding memory allocation that was assigned to the user.
Physical Database Components
Parameter File (PFILE – INIT.ORA)
You can read or change this file. The file contains all Oracle parameters file to configure a database instance. In this file, you can reset and change the Buffer Cache size, Shared Pool size, Redo Log Buffer size, etc. You use this file to increase or decrease the size of System Global Area (SGA). You also can change the location of your control files, mode of a database such as archivelog mode or noarchivelog mode, and many other parameter options that you will learn them in the course of this book.
Server Parameter File (SPFILE)
This file is in binary format and you cannot read this file. You should create the Server Parameter file (CREATE SPFILE FROM PFILE) and startup your database using the spfile file, if you want to change database parameters dynamically. There are some few parameters that you still need to shutdown and startup the database, if you want to make the parameter in effect. You will learn all about these parameters in the course of this book.
You cannot read this file and it is in a binary format. If you want to see the content of control file or the layout of your database, you should use the ALTER DATABASE BACKUP
CONTROLFILE TO TRACE statement. It writes a trace file into the ORACLE_BASEdirectory. It contains information the structure of your database layout, database name, last System Change Number (SCN) number, your database mode (archivelog mode or noarchivelog mode), maximum number of log files, maximum number of log members, maximum number of instances, maximum of number of datafiles, the location of the database Online Redo Log files, and backup information.
All the Oracle data information will be stored in the Oracle datafiles. A datafile is one of the physical layout components of a database. A tablespace (logical database layout) contains one or more datafiles (physical database layout). You may have one or more extents in a datafile. An extent is a collection of blocks. A block is a smallest unit in an Oracle. A tablespace is a collection of segments. Think of a segment like an object in an Oracle database. A Segment is a collection of Oracle blocks.
To use the password file you should set the REMOTE_LOGIN_PASSWORD parameter to exclusive or shared mode in the Parameter File (Example: REMOTE_LOGIN_PASSWORD=EXCLUSIVE). A password file is an external Oracle file and to create it you should run the ORAPWD utility from operating system.
$ORAPWD FILE=%ORACLE_HOMEb0 orapw.pwd par PASSWORD=mypass
The ENTRIES parameter specifying the number of user entries allowed for the password file. Now, the DBA can be connected to the database as a user with sysdba privileges as shown here:
SQL> connect sys as sysdba
Online Redo Log Files:
The Online Redo Log files hold the Redo Entries. You should have at least two or more Redo Log Groups. Each group may have more than one member. It is a good practice to multiplex Online Redo Log members. 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.
Archive Online Redo Log Files:
When an Online Redo Log File fills out, the checkpoint will force DBWR to write into the Oracle datafiles and also the archive process copies the log file to an archive destination directory. That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.
Assuming that you have a user is updating a record from her SQLPLUS. The following SQL statement is her
SQL> UPDATE emp SET sal = 1000 WHERE empno = 100;
Let us see what would be happen when oracle process it.
Step 1: The user will type the above SQL statement and press enter key. This user either is connect to the databaseby dedicated server or shared server (MTS). If the user is using multi-threaded servers then her request will be given to a dispatcher and the dispatcher will give the request to shared server. If the user is using dedicated server then the dedicated server will be all hers. Now, her user process is talking to shared or dedicated server.
Step 2: Now, the user’s SQL statement will be parsed and assigned an executed plan to be compiled in the Library Cache in the Shared Pool. In order the SQL statement be compiled, Oracle need to make sure its table and columns are valid and the user did not violated any security information. It goes to the Dictionary Cache known as Raw Cache to get all necessary information about the table. If there was no syntax problem and its table and columns were valid, then the SQL statement will be parsed successfully and the execution plan will be perform.
Step 3: Now, there is no problem. The Server process fetches the record. If the data or record is in the Buffer Cache then an update process will be applied to it and the block will be marked as dirty block. Notice that before the user save the update, the before block images are in the UNDO segment. When the user executes commit statement or more than one third of the Redo Log buffer have filled out, then LGWR writes the user’s entries from the redo log buffer to the Online Redo Log files. Still the block may not been stored in the database. In the case that the record is not in the buffer cache, the server process read the block containing the record from the datafile (disk) and places it into the buffer cache.
Step 4: Now, the checkpoint process will be activated based on the LOG_CHECKPOINT_INTERVAL,
LOG_CHECKPOINT_TIMEOUT parameters, or may be due to a log switch. This action will force DBWR or CKPT to write all dirty block in the database (data file).
Logical Database Structure
An ORACLE database’s logical structure is determined by:
The logical storage structures, including tablespaces, segments, and extents, dictate how the physical space of a database is used. The schema objects and the relationships among them form the relational design of a database.
TableSpaces and Data Files:
Tablespaces are the primary logical storage structures of any ORACLE database. The usable data of an ORACLE database is logically stored in the tablespaces and physically stored in the data files associated with the corresponding tablespace. Figure 2 illustrates this relationship. Although databases, tablespaces, data files, and segments are closely related, they have important differences:
Each tablespace in an ORACLE database is comprised of one or more operating system files called data files. A tablespace’s data files physically store the associated database data on disk.
Databases and TableSpaces:
An ORACLE database is comprised of one or more logical storage units called tablespaces. The database’s data is collectively stored in the database’s tablespaces.
A database’s data is collectively stored in the data files that constitute each tablespace of the database. For example, the simplest ORACLE database would have one tablespace, with one data file. A more complicated database might have three tablespaces, each comprised of two data files (for a total of six data files).
Schema Objects, Segments and TableSpaces:
When a schema object such as a table or index is created, its segment is created within a designated tablespace in the database. For example, suppose a table is created in a specific tablespace using the CREATE TABLE command with the TABLESPACE option. The space for this table’s data segment is allocated in one or more of the data files that constitute the specified tablespace. An object’s segment allocates space in only one tablespace of a database.
Figure 2. Data files and Table Spaces
A database is divided into one or more logical storage units called tablespaces. A database administrator can use tablespaces to do the following:
Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database. You can query these data dictionary tables to obtain pertinent information about the database; for example, the names of the tables that are owned by you or ones to which you have access. See Chapter 3 for more information on how to access data dictionary tables.
Data files associated with a tablespace store all the database data in that tablespace. One or more datafiles form a logical unit of database storage called a tablespace. A data file can be associated with only one tablespace, and only one database.
After a data file is initially created, the allocated disk space does not contain any data; however, the space is reserved to hold only the data for future segments of the associated tablespace – it cannot store any other program’s data. As a segment (such as the data segment for a table) is created and grows in a tablespace, ORACLE uses the free space in the associated data files to allocate extents for the segment.
The data in the segments of objects (data segments, index segments, rollback segments, and so on) in a tablespace are physically stored in one or more of the data files that constitute the tablespace. Note that a schema object does not correspond to a specific data file; rather, a data file is a repository for the data of any object within a specific tablespace. The extents of a single segment can be allocated in one or more data files of a tablespace (see Figure 3); therefore, an object can “span” one or more data files. The database administrator and end-users cannot control which data file stores an object.
Data Blocks, Extents and Segments
ORACLE allocates database space for all data in a database. The units of logical database allocations are data blocks, extents, and segments. Figure 3 illustrates the relationships between these data structures.
Data Blocks: At the finest level of granularity, an ORACLE database’s data is stored in data blocks (also called logical blocks, ORACLE blocks, or pages). An ORACLE database uses and allocates free database space in ORACLE data blocks. Figure 4 illustrates a typical ORACLE data block.
Extents: The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks that are allocated for storing a specific type of information.
Segments: The level of logical database storage above an extent is called a segment. A segment is a set of extents which have been allocated for a specific type of data structure, and all are stored in the same tablespace. For example, each table’s data is stored in its own data segment, while each index’s data is stored in its own index segment. ORACLE allocates space for segments in extents. Therefore, when the existing extents of a segment are full, ORACLE allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk, and may or may not span files. An extent cannot span files, though.
Figure 3. The Relationship Among Segments, Extents and Data Blocks
ORACLE manages the storage space in the data files of a database in units called data blocks. A data block is the smallest unit of I/O used by a database. A data block corresponds to a block of physical bytes on disk, equal to the ORACLE data block size (specifically set when the database is created – 2048). This block size can differ from the standard I/O block size of the operating system that executes ORACLE.
The ORACLE block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 4 shows the format of a data block.
Figure 4. Data Block Format
Header (Common and Variable): The header contains general block information, such as block address, segment type, such as data, index, or rollback. While some block overhead is fixed in size (about 107 bytes), the total block overhead size is variable.
Table Directory: The table directory portion of the block contains information about the tables having rows in this block.
Row Directory: This portion of the block contains row information about the actual rows in the block (including addresses for each row piece in the row data area). Once the space has been allocated in the row directory of a block’s header, this space is not reclaimed when the row is deleted.
Row Data: This portion of the block contains table or index data. Rows can span blocks.
Free Space: Free space is used to insert new rows and for updates to rows that require additional space (e.g., when a trailing null is updated to a non-null value). Whether issued insertions actually occur in a given data block is a function of the value for the space management parameter PCTFREE and the amount of current free space in that data block.
Space Used for Transaction Entries Data blocks allocated for the data segment of a table, cluster, or the index segment of an index can also use free space for transaction entries. Two space management parameters, PCTFREE and PCTUSED, allow a developer to control the use of free space for inserts of and updates to the rows in data blocks. Both of these parameters can only be specified when creating or altering tables and clusters (data segments). In addition, the storage parameter PCTFREE can also be specified when creating or altering indicies (index segments).
The PCTFREE parameter is used to set the percentage of a block to be reserved (kept free) for possible updates to rows that already are contained in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
PCTFREE 20: This states that 20% of each data block used for this table’s data segment will be kept free and available for possible updates to the existing rows already within each block.
After a data block becomes full, as determined by PCTFREE, the block is not considered for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED. Before this value is achieved, the free space of the data block can only be used for updates to rows already contained in the data block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
PCTUSED 40: In this case, a data block used for this table’s data segment is not considered for the insertion of any new rows until the amount of used space in the blocks falls to 39% or less (assuming that the block’s used space has previously reached PCTFREE).
No matter what type, each segment in a database is created with at least one extent to hold its data. This extent is called the segment’s initial extent.
If the data blocks of a segment’s initial extent become full and more space is required to hold new data, ORACLE automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or incremented size of the previous extent in that segment.
Every non-clustered table in an ORACLE database has a single data segment to hold all of its data. The data segment for a table is indirectly created via the CREATE TABLE/SNAPSHOT command.
Storage parameters for a table, snapshot, or cluster control the way that a data segment’s extents are allocated. Setting these storage parameters directly via the CREATE TABLE/SNAPSHOT/CLUSTER or ALTER TABLE/SNAPSHOT/CLUSTER commands affects the efficiency of data retrieval and storage for that data segment.
Get Updates on Tech posts, Interview & Certification questions and training schedules