DBA > Articles

Oracle DBA Exam ( the Oracle Architecture )

To read more DBA articles, visit http://dba.fyicenter.com/article/

Part:   1  2 

The Oracle Architecture The Diagram below gives a clear idea of the background processes, memory structures, and disk resources that comprise the Oracle instance, and also of the methods in which they act together to allow users to access information.

Several memory structures exist on the Oracle database to improve performance on various areas of the database. The memory structures of an Oracle instance include the System Global Area (SGA) and the Program Global Area (PGA).

The SGA, in turn, consists of a minimum of three components: the data block buffer cache, the shared pool, and the redo log buffer. Corresponding to several of these memory areas are certain disk resources. These disk resources are divided into two categories: physical resources and logical resources.

Oracle Disk Utilization Structures
The physical disk resources on the Oracle database are datafiles, redo log files, control files, password files, and parameter files. The logical resources are tablespaces, segments, and extents. Tying memory structures and disk resources together are several memory processes that move data between disk and memory, or handle activities in the background on Oracle's behalf.

The Oracle PGA
The PGA is an area in memory that helps user processes execute, such as bind variable information, sort areas, and other aspects of cursor handling. From the prior discussion of the shared pool, the DBA should know that the database already stores parse trees for recently executed SQL statements in a shared area called the library cache. So, why do the users need their own area to execute? The reason users need their own area in memory to execute is that, even though the parse information for SQL or PL/SQL may already be available, the values that the user wants to execute the search or update upon cannot be shared. The PGA is used to store real values in place of bind variables for executing SQL statements.

Oracle Background Processes
In any Oracle instance, there will be user processes accessing information. . Likewise, the Oracle instance will be doing some things behind the scenes, using background processes. There are several background processes in the Oracle instance. It was mentioned in the discussion of the SGA that no user process ever interfaces directly with I/O. This setup is allowed because the Oracle instance has its own background processes that handle everything from writing changed data blocks onto disk to securing locks on remote databases for record changes in situations where the Oracle instance is set up to run in a distributed environment. The following list presents each background process and its role in the Oracle instance.

DBWR The database writer process. This background process handles all data block writes to disk. It works in conjunction with the Oracle database buffer cache memory structure. It prevents users from ever accessing a disk to perform a data change such as update, insert, or delete.
LGWR The log writer process. This background process handles the writing of redo log entries from the redo log buffer to online redo log files on disk. This process also writes the log sequence number of the current online redo log to the datafile headers and to the control file. Finally, LGWR handles initiating the process of clearing the dirty buffer write queue. At various times, depending on database configuration, those updated blocks are written to disk by DBWR. These events are called checkpoints. LGWR handles telling DBWR to write the changes.
SMON The system monitor process. The usage and function of this Oracle background process is twofold. First, in the event of an instance failure-when the memory structures and processes that comprise the Oracle instance cannot continue to run-the SMON process handles recovery from that instance failure. Second, the SMON process handles disk space management issues on the database by taking smaller fragments of space and "coalescing" them, or piecing them together.
PMON The process monitor process. PMON watches the user processes on the database to make sure that they work correctly. If for any reason a user process fails during its connection to Oracle, PMON will clean up the remnants of its activities and make sure that any changes it may have made to the system are "rolled back," or backed out of the database and reverted to their original form.
RECO (optional) The recoverer process. In Oracle databases using the distributed option, this background process handles the resolution of distributed transactions against the database.
ARCH (optional) The archiver process. In Oracle databases that archive their online redo logs, the ARCH process handles automatically moving a copy of the online redo log to a log archive destination.
CKPT (optional) The checkpoint process. In high-activity databases, CKPT can be used to handle writing log sequence numbers to the datafile headers and control file, alleviating LGWR of that responsibility.
LCK0..LCK9 (optional) The lock processes, of which there can be as many as ten. In databases that use the Parallel Server option, this background process handles acquiring locks on remote tables for data changes.
S000..S999 The server process. Executes data reads from disk on behalf of user processes. Access to Server processes can either be shared or dedicated, depending on whether the DBA uses MTS or not. In the MTS architecture, when users connect to the database, they must obtain access to a shared server process via a dispatcher process, described below.
D001..D999 (optional) The dispatcher process. This process acts as part of the Oracle MTS architecture to connect user processes to shared server processes that will handle their SQL processing needs. The user process comes into the database via a SQL*Net listener, which connects the process to a dispatcher. From there, the dispatcher finds the user process a shared server that will handle interacting with the database to obtain data on behalf of the user process.

Starting and Stopping the Oracle Instance

Selecting an Authentication Method
Before starting the instance, the DBA must figure out what sort of database authentication to use both for users and administrators. The options available are operating system authentication and Oracle authentication. The factors that weigh on that choice are whether the DBA wants to use remote administration via network or local administration directly on the machine running Oracle. If the DBA chooses to use Oracle authentication, then the DBA must create a password file using the ORAPWD utility. The password file itself is protected by a password, and this password is the same as the one used for authentication as user SYS and when connecting as internal. To have database administrator privileges on the database, a DBA must be granted certain privileges. They are called sysdba and sysoper in environments where Oracle authentication is used, and osdba or osoper where operating system authentication is used.

Starting the Oracle Instance and Opening the Database
In order to start a database instance, the DBA must run Server Manager and connect to the database as internal. The command to start the instance from Server Manager is called startup. There are several different options for starting the instance. They are nomount, mount, open, restrict, recover, and force. The nomount option starts the instance without mounting a corresponding database. The mount option starts the instance and mounts but does not open the database. The open option starts the instance, mounts the database, and opens it for general user access. The restrict option starts the instance, mounts the database, and opens it for users who have been granted a special access privilege called restricted access. The recover option starts the instance, but leaves the database closed and starts the database recovery procedures associated with disk failure. The force option gives the database startup procedure some extra pressure to assist in starting an instance that either has trouble opening or trouble closing normally. There are two alter database statements that can be used to change database accessibility once the instance is started as well.

Shutting Down the Oracle Database
Several options exist for shutting down the database as well. The DBA must again connect to the database as internal using the Server Manager tool. The three options for shutting down the Oracle database are normal, immediate, and abort. When the DBA shuts down the database with the normal option, the database refuses new connections to the database by users and waits for existing connections to terminate. Once the last user has logged off the system, then the shutdown normal will complete. The DBA issuing a shutdown immediate causes Oracle to prevent new connections while also terminating current ones, rolling back whatever transactions were taking place in the sessions just terminated. The final option for shutting down a database is shutdown abort, which disconnects current sessions without rolling back their transactions and prevents new connections to the database as well.

Creating an Oracle Database
After developing a model of the process to be turned into a database application, the designer of the application must then give a row count forecast for the application's tables. This row count forecast allows the DBA to size the amount of space in bytes that each table and index needs in order to store data in the database. Once this sizing is complete, the DBA can then begin the work of creating the database. First, the DBA should back up existing databases associated with the instance, if any, in order to prevent data loss or accidental deletion of a disk file resource. The next thing that should happen is the DBA should create a parameter file that is unique to the database being created. Several initialization parameters were identified as needing to be set to create a database. The following list describes each parameter:

DB_NAME The local name of the database on the machine hosting Oracle, and one component of a database's unique name within the network. If this is not changed, permanent damage may result in the event a database is created.
DB_DOMAIN Identifies the domain location of the database name within a network. It is the second component of a database's unique name within the network.
CONTROL_FILES A name or list of names for the control files of the database. The control files document the physical layout of the database for Oracle. If the name specified for this parameter do not match filenames that exist currently, then Oracle will create a new control file for the database at startup. If the file does exist, Oracle will overwrite the contents of that file with the physical layout of the database being created.
DB_BLOCK_SIZE The size in bytes of data blocks within the system. Data blocks are unit components of datafiles into which Oracle places the row data from indexes and tables. This parameter cannot be changed for the life of the database.
DB_BLOCK_BUFFERS The maximum number of data blocks that will be stored in the database buffer cache of the Oracle SGA.
PROCESSES The number of processes that can connect to Oracle at any given time. This value includes background processes (of which there are at least five) and user processes.
ROLLBACK_SEGMENTS A list of named rollback segments that the Oracle instance will have to acquire at database startup. If there are particular segments the DBA wants Oracle to acquire, he/she can name them here.
LICENSE_MAX_SESSIONS Used for license management. This number determines the number of sessions that users can establish with the Oracle database at any given time.
LICENSE_MAX_WARNING Used for license management. Set to less than LICENSE_MAX_SESSIONS, Oracle will issue warnings to users as they connect if the number of users connecting has exceeded LICENCE_MAX_WARNING.
LICENSE_MAX_USERS Used for license management. As an alternative to licensing by concurrent sessions, the DBA can limit the number of usernames created on the database by setting a numeric value for this parameter.

After the parameter file is created, the DBA can execute the create database command, which creates all physical disk resources for the Oracle database. The physical resources are datafiles, control files, and redo log files, the SYS and SYSTEM users, the SYSTEM tablespace, one rollback segment in the SYSTEM tablespace, and the Oracle data dictionary for that database. After creating the database, it is recommended that the DBA back up the new database in order to avoid having to re-create the database from scratch in the event of a system failure.

Creating the Oracle Data Dictionary
Of particular importance in the database creation process is the process by which the data dictionary is created. The data dictionary must be created first in a database because all other database structure changes will be recorded in the data dictionary. This creation process happens automatically by Oracle. Several scripts are run in order to create the tables and views that comprise the data dictionary. There are two "master" scripts that everything else seems to hang off of. The first is catalog.sql. This script creates all the data dictionary tables that document the various objects on the database. The second is called catproc.sql. This script runs several other scripts that create everything required in the data dictionary to allow procedural blocks of code in the Oracle database, namely packages, procedures, functions, triggers, snapshots, and certain packages for PL/SQL such as pipes and alerts.

Accessing and Updating Data
Oracle allows users to access and change data via the SQL language. SQL is a unique language in that it allows users to define the data they want in terms of what they are looking for, not in terms of a procedure to obtain the data. Oracle manages the obtaining of data by translating SQL into a series of procedures Oracle will execute to fetch the data the user requested. The steps Oracle uses in SQL statement processing are opening the statement cursor, which is a memory address Oracle will use for storing the statement operation; parsing the statement into a series of data operations; binding variables in place of hard coded values to allow for parse tree sharing; executing the statement; and fetching the results (query only). After the statement is executed, the parse information is left behind in the library cache of the shared pool in order to reduce the amount of memory required to handle user processes and also to boost performance of SQL statement processing.

The Function and Contents of the Buffer Cache
In order to further boost performance, Oracle maintains an area of the SGA called the buffer cache, which is used to store data blocks containing rows from recently executed SQL statements. Part of this buffer cache contains an area called the dirty buffer write queue, which is a list of blocks containing row data that has been changed and needs to be written to disk. When users issue statements that require Oracle to retrieve data from disk, obtaining that data is handled by the server process.
Another database process, DBWR, eliminates I/O contention on the database by freeing user processes from having to perform disk writes associated with the changes they make. Since users only deal directly with blocks that are in the buffer cache, they experience good performance while the server and DBWR processes handle all disk utilization behind the scenes.

Role of the Server Process
The Server process does its job whenever user processes need more blocks brought into the cache. In order to make room for the incoming data, the server process eliminates blocks from the buffer cache according to which ones were used least recently. One exception to this rule is made for blocks that were brought into the buffer cache to support full table scans. These buffers are eliminated almost immediately after they are scanned.

Role of the DBWR Process
The DBWR process will write buffers back to the database when triggered to do so by another process, called LGWR, during a special database event called a checkpoint. DBWR also writes data to the database every three seconds in a timeout.

Online Redo Log
Oracle handles the tracking of changes in the database through the use of the online redo log. There are several components to the online redo log. The first is an area in memory where user processes place the redo log entries they have to make when they write a change to the database. This area is called the redo log buffer. Another component of the online redo log is a set of files on disk that store the redo log entries. This is the actual "online redo log" portion of the architecture. There is a minimum of two online redo logs in the Oracle database. They consist of one or more files, called "members," that contain the entire contents of the redo log. For safety purposes, it is best to put each redo log member on a separate disk so as to avoid the failure of one disk causing the failure of an entire Oracle instance. The final component of the online redo log is the log writer process (LGWR), a background process mechanism that writes redo entries from the memory buffer to the online redo log.

The Purpose of Checkpoints
A checkpoint is performed every time LGWR fills an online redo log with redo entries and has to switch to writing entries to another redo log. A checkpoint is when LGWR sends a signal to DBWR to write all changed data blocks in the dirty buffer write queue out to their respective datafiles on disk. By default, checkpoints happen once every log switch, but can happen more often, depending on the values set for LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT. These two parameters allow for transaction volume-based or time-based checkpoint intervals.

Data Concurrency and Statement-level Read Consistency
In multiple-user environments, it must be remembered that there are special considerations required to ensure that users don't overwrite others' changes on the database. In addition, users must also be able to have read-consistent views of the data, both for individual statements and for collections of statements treated as one operation. The key to transaction concurrency without overwriting another user's changes is the concept of transaction processing. Transactions are made possible in the Oracle database with the use of mechanisms that allow one and only one user at a time to make a change to a database table. These mechanisms are called locks. In addition, when the user makes a change to the database, that change isn't recorded on disk right away. Instead, the change is noted in a database object called a rollback segment. This mechanism allows the user to make a series of changes to the database and save or commit them once as one unit of work. Another feature this architecture allows for is the ability to discard the changes made in favor of the way the data used to look. This act is called a rollback. The rollback segment allows for read-consistent views of the data on the database at the transaction level.

Managing the Database Structure
There is a physical and a logical view of the database. The physical structure permits the database to grow to a certain size, while the logical structure regulates its setup. Storage is governed by parameters set at object creation. These parameters can be changed at various points in the maintenance of the object. Storage allocation should work around the reality of the physical database design in that the DBA should attempt to place objects over several disks to better utilize the physical resources available to Oracle.

Preparing Necessary Tablespaces
At database creation, Oracle creates a special tablespace called SYSTEM to hold the data dictionary and the initial rollback segment of the database. There are several different types of segments on the database that correspond to the various types of database objects. Some examples are tables, indexes, rollback segments, clusters, and temporary segments. For the most part, these objects have different storage needs, and as such it is usually best for them to be in separate tablespaces.

Managing Storage Allocation
When the data in a database object grows too large for the segment to store all the data, Oracle must acquire another extent for the object. The size of the initial extent, the acquired extent, the number of extents allowed, and possible percentage increases for each extent of an object are all governed by the use of storage parameters. Another aspect of database usage that is governed by storage parameters is how the data in each data block owned by the object will be stored. In order to find out the storage parameters and the overall space usage for a database object, the DBA can utilize several views in the data dictionary. List of database storage allocation parameters:

initial Segment storage clause that determines the size (either in kilobytes or megabytes as determined by specifying K or M) of the initial extent comprising the database object.
Next Segment storage clause that determines the size (either in kilobytes or megabytes as determined by specifying K or M) of the second extent comprising the database object.
Minextents Segment storage clause that determines the minimum number of extents a database object may have.
Maxextents Segment storage clause that determines the maximum number of extents a database object may have. A special keyword unlimited can be used to allow unlimited number of extents on the object.
pctincrease Segment storage clause that specifies the permitted percentage of growth of each subsequent extent allocated to the database object. For example, if a table is created with initial 1M, next 1M, minextents 2 and pctincrease 20 storage clauses, the third extent created for this object will be 1.2M in size, the fourth will be 1.44M in size, etc. Oracle rounds up to the nearest block when use of this option identifies an extent size that is not measurable in whole blocks. The default value for this option is 50 percent. Note: This option is NOT available for rollback segment creation.
optimal Segment storage clause that specifies the optimal number of extents that should be available to the rollback segment. Note: This is ONLY available for rollback segment creation.
freelists Database object storage clause that specifies the number of lists in each freelist group that Oracle will maintain of blocks considered "free" for the table. A free block is one that either has not reached its pctfree threshold or fallen below its pctused threshold.
freelist groups Database object storage clause that specifies the number of groups of freelists for database objects that Oracle will maintain in order to know which blocks have space available for row storage. Note: this is available for Parallel Server Option usage.

(Continued on next part...)

Part:   1  2 

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/