The two main components of Oracle Server are:
Oracle Instance comprises of
SGA is the Oracle Server?s main memory structure. When a user enters Oracle, SGA is created with six components - Three Essential and Three Optional.
SGA Space Management: SGA overall size and its components individual size can be managed in two ways -
In both the cases, allocation of space is done by Oracle dynamicallyby dividing the allocated memory into chunks called granules.
The size of the granules can be 4MB, 8MB, or 16MB depending on the Operating System and the size of SGA.
SGA space management specification (Manual or Automatic) is made through use of parameter initialization file.
Parameter Initialization file is a file that contains configuration settings to be used by the Instance during creation as well as while running. The parameter initialization files are of two types. PFILE and SPFILE.
Pfile is a text file which can be edited and SPFILE is a binary which is created and maintained by Oracle and cannot be edited.>
Automatic SGA management requires the use of the SPFILE.
Differences of PFILE and SPFILE:
There are certain differences in PFILE and SPFILE which are shown in the table below.
|Text file that can be edited using a text editor.||Binary file that cannot be edited directly.|
|When changes are made to the PFILE, the instance must be shut down and restarted before it takes effect.||Most changes to the SPFILE can be made dynamically, while the instance is open and running.|
|Is called initinstance name.ora.||Is called spfileinstance name.ora.|
|Can be created from an SPFILE using |
CREATE PFILE FROM SPFILEcommand.
|Can be created from a PFILE using |
CREATE SPFILE FROM PFILEcommand.
More than 250 configuration parameters can be specified in the PFILE or SPFILE. The parameters can be categorised into two types: Basic and Advanced. Only 30 basic initialization parameters are set manually. Remaining 220 parameters must not be set unless Oracle Support recommends to do so.
|CLUSTER_DATABASE||Tells the instance whether it is part of a clustered environment.|
|COMPATIBLE||Specifies the release level and feature set that you want to be active in the instance.|
|CONTROL_FILES||Designates the physical location of the database control files.|
|DB_BLOCK_SIZE||Specifies the default database block size|
|DB_CREATE_FILE_DEST||Specifies the directory location where database datafiles will be created if the Oracle Managed Files feature is used.|
|DB_CREATE_ONLINE_LOG_DEST_n||Specifies the directory location where database datafiles will be created if the Oracle Managed Files feature is used.|
|DB_DOMAIN||Specifies the logical location of the database on the network.|
|DB_NAME||Specifies the name of the database that is mounted by the instance.|
|DB_RECOVERY_FILE_DEST||Specifies the location where recovery files will be written if the Flash Recovery feature is used.|
|DB_RECOVERY_FILE_DEST_SIZE||Specifies the amount of disk space available for storing Flash Recovery files.|
|DB_UNIQUE_NAME||Specifies a globally unique name for the database within the enterprise.|
|INSTANCE_NUMBER||Identifies the instance in a Real Application Clusters (RAC) environment.|
|JOB_QUEUE_PROCESSES||Specifies the number of background processes to start for handling jobs submitted via Enterprise Manager or DBMS_JOBS.|
|LOG_ARCHIVE_DEST_n||Specifies as many as nine locations where archived redo log files are to be written.|
|LOG_ARCHIVE_DEST_STATE_n||Indicates how the specified locations should be used for log archiving.|
|NLS_LANGUAGE||Specifies the default language of the database.|
|NLS_TERRITORY||Specifies the default region or territory of the database.|
|OPEN_CURSORS||Sets the maximum number of cursors that an individual session can have open at one time.|
|PGA_AGGREGATE_TARGET||Establishes the overall amount of memory that all PGA processes are allowed to consume.|
|PROCESSES||Specifies the maximum number of operating system processes that can connect to the instance.|
|REMOTE_LISTENER||Specifies a network name that points to the address or list of addresses of remote Oracle Net listeners.|
|REMOTE_LOGIN_PASSWORDFILE||Determines whether the instance uses a password file and what type.|
|ROLLBACK_SEGMENTS||Specifies only if Automatic Undo Management is not being used.|
|SESSIONS||Determines the maximum number of sessions that can connect to the database.|
|SGA_TARGET||Establishes the maximum size of the SGA, within which space is automatically allocated to each SGA component when automatic memory management is used.|
|SHARED_SERVERS||Specifies the number of Shared Server processes to start when the instance is started.|
|STAR_TRANSFORMATION_ENABLED||Determines whether the optimizer will consider star transformations when queries are executed.|
|UNDO_MANAGEMENT||Establishes whether system undo is automatically or manually managed.|
|UNDO_TABLESPACE||Specifies which tablespace stores undo segments if the Automatic Undo Management option is used.|
Values for many initialization parameters are used to specify the size of the SGA and its components. The values for parameters that are not specified in the PFILE or SPFILE will use default values. These values can be viewed using EM or DDVs.
In addition to 30 basic and 220 advanced parameters, more than 1000 parameters are available undocumented in Oracle 10g. (They precede underscore in their names). It is not advisable to change values these undocumented parameters, without being directed by directed by Oracle Support.
Oracle Background Processes
There are many types of Oracle background processes. Each performing a specific job for the Instance Management. Five are required and most others are optional. The required background processes are found in all Instances and Optional background processes are used depending on the optional Oracle features being used.
Required background processes:
|Process Name||Operating System Process||Description|
|System Monitor||SMON||the database, and manages space used for sorting|
|Process Monitor||PMON||Writes modified database blocks from the SGA's Database Buffer Cache to the datafiles on disk|
|Database Writer||DBWn*||Writes transaction recovery information from the SGA?s|
|Log Writer||LGWR||Redo Log Buffer to the online Redo Log files on disk|
|Checkpoint||CKPT||Updates the database files following a Checkpoint Event|
* n=more than one process
Example: DBW0, DBW1, DBW2, and DBW3.
Optional background processes:
|Process Name||Operating System Process||Description|
|Archiver||ARCn||Copies the transaction recovery information written to disk by secondary location in case it is for recovery. Nearly all production databases use this optional process.|
|Recoverer||RECO||Recovers failed transactions that are distributed across multiple databases when using Oracle?s distributed Assigns jobs to the Job Queue processes when using Oracle?s job scheduling feature.|
|Job Queue Monitor||CJQn||Assigns jobs to the Job Queue processes when using Oracle's scheduling feature.|
|Job Queue||Jnnn||Executes database jobs that have been scheduled using Oracle's job scheduling feature.|
|Queue Monitor||QMNn||Monitors the messages in the message queue when Oracle?s Advanced Queuing feature is used.|
|Parallel Query Slave||Qnnn||Used to carry out portions of a larger overall query when Oracle?s Parallel Query feature is used.|
|Dispatcher||Dnnn||Assigns user?s database requests to a queue where they are then serviced by Shared Server processes when Oracle?s Shared Server feature is used.|
|Shared Server||Snnn||Server Processes that are shared among several users when Oracle?s Shared Server feature is used.|
|Memory Manager||MMAN||Manages the size of each individual SGA component when Oracle?s Automatic Shared Memory Management feature is used.|
|Memory Monitor||MMON||Gathers and analyzes statistics used by the Automatic Workload Repository feature.|
|Memory Monitor Light||MMNL||Gathers and analyzes statistics used by the Automatic Workload Repository feature.|
|Recovery Writer||RVWR||Writes recovery information to disk when Oracle?s Flashback Database Recovery feature is used.|
|Change Tracking Writer||CTWR||Keeps track of which database blocks have changed when Oracle?s incremental Recovery Manager feature is used.|
User Server processes are not considered part of the instance. In Windows environments, a Windows service called OracleServiceInstance-Name is also associated with each instance. This service must be started in order to start up the instance in Windows environments.
Oracla Database also consists of some other files that are technically part of the Database.
Following table describes above file types and the information being stored in each type:
|File Type||Description / Content|
|Control File|| Locations of other physical files, database name, block size, character set, and recovery information.
These files are required to open the database.
|Data Files|| They are the physical files that actually store the data in tables (the
actual database). They are the largest size files in the database
ranging from MB, GB or Terabytes.
Datafiles are a subordinate structures of TABLESPACEs. Essential tablespaces are SYSTEM, SYSAUX, TEMP Other tablespaces are USERS, TOOLS, UNDOTBS1
|Redolog Files||History of all changes made to the database Each action of the user is recorded (first being written to redolog buffer, and then transferred through LGWR process) in these files.|
|Archived Log files||Backups of previous redo log files|
|Password File||Stores names of users allowed to connect to the database using SYSDBA and SYSOPER privileges.|
|Parameter Files||Configuration settings for SGA, optional features, and background processes|
Loss of Control file causes loss to database. Hence they are multiplexed. To reduce chances for loss of control files, CKPT background process updates each of these files automatically, with synchronising all control files.
By using V$CONTROLFILE data dictionary view control file information can viewed.
They are the smallest files in the database with size between 1 MB and 5 MB. CONTROLFILE_RECORD_KEEP_TIME PARAMETER used when RMAN feature is used, will determine their size whether to be more than 5 MB. V$CONTROLFILE data dictionary view an be used to view the the names and locations of all the database?s control files.