Oracle Architecture and Administration

The Oracle Architecture
The Oracle server consists of physical files and memory components. The Oracle 10g Database product is made up three main components namely:
• The Oracle Server – This is the Oracle database management system that is able to store, manage and manipulate data. It consists of all the files, structures, processes that form Oracle Database 10g. The Oracle server is made up of an Oracle instance and an Oracle database.
• The Oracle Instance –Consists of the memory components of Oracle and various background processes.
• The Oracle database – This is the centralized repository where the data is stored. It has a physical structure that is visible to the Operating system made up of operating system files and a logical structure that is recognized only the Oracle Server.

The following figure displays the architecture of the Oracle Database 10g. It is broadly divided into the memory components which form the Oracle instance and the physical database components where different kinds of data are stored.

The Oracle Instance
The Oracle instance is made up of a number of memory-related components and background processes. The instance is the created in memory every-time the database is started. The instance is associated to only a single database at any time. The memory components of the Oracle Database 10g are also known as the System Global Area (SGA).

The instance and its components are configured using a file known as the Parameter file.
In Oracle 10g there are two types of initialization files namely the 'Server Parameter file' (spfile) and 'The Parameter file' (pfile). The parameter files hold parameters that can set the sizes of the various buffers and pools of the SGA. You can also specify the name of the instance, the name of the database and other size-related parameters needed by the Oracle instance.

A typical parameter setting would be parameter_name = parameter_value
For e.g. DB_NAME=moviedb
Here the name of the database is moviedb.

Memory Components of the SGA
The Oracle Instance is made up of the System Global area (SGA) and the background
processes. The SGA is made up of the
• Redo Log Buffer
• The Database Buffer cache
• The Shared Pool
• The Java Pool
• The Large Pool
• The Streams Pool

Redo Log Buffer – A circular buffer that stores all changes made in the database. It contains are transferred periodically from memory to the online redo log files on disk by the Log Writer (LGWR) background process. The contents of the redo log buffer are essential for instance recovery purposes. The size of the redo log buffer is determined by the LOG_BUFFER initialization parameter.

The Database Buffer Cache – An area in memory that holds all the blocks read in from disk for query or modification. Blocks that need to be modified, are modified in memory and are written back to disk periodically. Modified blocks that have not yet been written to disk are known as dirty blocks. This buffer is managed in a manner that free blocks are always made available for new blocks being read into memory. The contents of the database buffer cache are shared by multiple user processes. The contents of the database buffer cache are written to datafiles on disk by the Database Writer (DBWR) background process. The size of the default database buffer cache is sized by the DB_CACHE_SIZE initialization parameter. The blocks that are part of the Database buffer cache are sized by the DB_BLOCK_SIZE initialization parameter. This is the default buffer cache.

Other caches can be created in addition to the default database buffer cache: they are the Keep and Recycle cycle buffers. Oracle uses the technique known as LRU algorithm, to create space for new in-coming blocks that need room in buffer cache. The oldest blocks are the first ones to leave. There maybe certain blocks that should be retained in memory for a longer period of time, since they are frequently accessed. The Keep cache is used to hold such. Though they are also subject to the LRU algorithm, blocks are not released as quickly as they would have in the default cache. Blocks from standard and frequently accessed packages should be placed in the keep cache. The size of the Keep cache can be specified by the DB_KEEP_CACHE_SIZE initialization parameter. The Recycle cache on the otherhand is used to hold blocks of objects that should be released as soon as they are used if it is unlikely that they will be reused. You can specify which cache will be used by an object when the object is created.

The Shared Pool – The contents of this memory area are shared by multiple users and hence the name shared pool. There are two specific caches form the shared pool. The sizes of the two caches are not set individually and are automatically determined by the Oracle Server. However it is possible to set the overall size of the Shared pool by using the SHARED_POOL_SIZE initialization parameter.
The Large Pool – This is an optional pool. The size of this memory area is determined by the LARGE_POOL_SIZE initialization parameter. It is used for handling large I/O requests of server processes. Its main functions are to provide memory for session memory (UGA) for the shared server environment, parallel execution message buffers (when PARALLEL_AUTOMATIC_TUNING is set to TRUE). It is also used by the Recovery Manager (RMAN) for its functioning when the BACKUP_DISK_IO =n and BACKUP_TAPE_IO_SLAVE=TRUE initialization parameters have been set. This pool does not use the LRU algorithm.

The Java Pool – This memory area is used by all session-specific Java code and data within the Java Virtual Machine (JVM). Its size is determined by the JAVA_POOL_SIZE initialization parameter.

The Streams Pool – This memory area is used by the Oracle Streams Product for its functioning.

Automatic Shared Memory Management
In Oracle 10g, a new feature known as Automatic Shared Memory Management has been introduced. Using this feature all you need to specify is the total amount of memory that will be used by all the SGA components. The database then uses this value to redistribute memory between the various memory components based on the current workload. The parameter that needs to be configured for automatic shared memory management is the SGA_TARGET initialization parameter. In Oracle 10g, the SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE and DB_CACHE_SIZE are known as the auto-tuned SGA parameters. If you set the value for SGA_TARGET to a value greater than zero then the total amount of memory to be allocated for the auto-tuned parameters is obtained by subtracting the SGA_TARGET value from the total amount of memory for manual SGA parameters.

Setting the SGA_TARGET to a value of zero will result in disabling automatic shared memory management. The ASSM feature uses the MMAN background process, which is responsible for coordinating the sizes of the various pools dynamically based on the current workload in the database.

Background Processes
The background processes of the Oracle instance are responsible for performing asynchronous I/O functions between the Oracle Instance and the physical files of the Oracle database that exist on disk. There are 5 mandatory background processes in Oracle 10g. These are the Database Writer (DBWR), the Log Writer (LGWR), the Checkpoint process (CKPT), the System Monitor (SMON) and the Process Monitor (PMON). Other background processes can be started based on certain additional functionality required in the database. Given below is a list of the most commonly started background process and the actions they are responsible for in the database.

Background Process Description and Function
Database Writer
This background process is mandatory. Its function is to transfer modified blocks from the database buffer cache to the datafiles.
The DBWR writes to the datafiles when one of the following events occur in the database:
• Normal or incremental checkpoint
• The number of dirty buffers reaches a pre-defined threshold value
• The server process scans a pre-defined number of blocks looking for free space needed by new blocks being read in.
• Timeout occurs
• Tablespace is taken offline in normal or immediate mode.
• Tablespace is made read-only
• Dropping or truncating a table
• Online backup is done on a tablespace using ALTER TABLESPACE... BEGIN BACKUP command.
Log Writer (LGWR) This background process is mandatory. It transfers redo entries that have been written to the redo log buffer to the online redo log files present on disk.
The LGWR writes to the online redo log files when one of the following events occur in the database:
• A commit is issued.
• The Redo Log Buffer is one-third full.
• More than 1MB of changes (updations) has been made in the database.
• Every three seconds
• Just before the DBWR writes from the database buffer cache to the datafiles.
Checkpoint (CKPT) This background process is mandatory.
When the contents of the database buffer cache are transferred to the datafiles by the DBWR background process, the event is referred to as a CHECKPOINT. When a checkpoint occurs the Checkpoint (CKPT) background process updates the headers of the datafiles and control files to indicate the most recent checkpoint.
This process is therefore responsible for synchronizing the database.
System Monitor
This background process is mandatory. It is responsible for performing recovery in the event of an instance failure.
Instance failure occurs during an abnormal shutdown of the database, such as if a shutdown is performed using the ABORT option. During instance recovery the SMON process performs a roll forward operation. During this step all committed transactions that were not written out to the datafiles at the time of failure were be completed. This step maybe followed by a rollback during which time either the SMON or individual user processes undo all the changes that were uncommitted during the time of failure. Certain additional functions performed by the System Monitor involve coalescing of free space in dictionary-managed datafiles and releasing temporary segments that are created in the datafiles.
Process Monitor
This background process is mandatory. Users connect to the database to access data.
When a user connects to the database, a process known as a User process is started on behalf of the user on the client machine.
If the user terminates his/her session abnormally, resources that were being used by the user session continue to remain attached until the PMON background process cleans it up.
The PMON background process is responsible for cleaning the resources such locks on tables being held by failure user processes.
The process also restarts dead dispatcher processes.
Archiver (ARCH)
This is an optional background process. It can be started when you wish to operate the database in an archivelog mode.
This mode of database operation allows for complete recovery of the database in an event of a failure.
The ARCH background process transfers the contents of the online redo log files to archival media which could be disk or tape
The other optional processes in Oracle 10g are the :
CJQ0 – Coordinator Job Queue background process
Dnnn – Dispatcher process
Pnnn – Parallel Query Slaves
QMNn – Advanced Queuing
RECO – Recoverer
Snnn – Shared Server Processes
MMON – Memory Monitor

User Processes
A user trying to access data in the database needs to make a connection with the database using an appropriate tool such as SQL*Plus, iSQL*Plus and a front-end database interface tool. A connection is a communication pathway created between a user process and the Oracle Server. The user has to first supply proper authentication credentials.
Once the user's credentials are validated a user session is established and a user process is created on the client-side to act on behalf of the user. This process remains active till the user completes the session. In the event that a user process is terminated abnormally the PMON background process cleans up the resources that were held by the user process.

Server Processes
Server processes are created in Oracle to handle requests made by user processes. Server processes are always started on the Server side. They are responsible for responding to the user process with appropriate results. Based on the mode in which a database operates either a 1:1 ratio or m:n exists between user and server processes. In Oracle, the instance can operate in one of two modes:

The Program Global Area (PGA)
This is memory area that is associated with a server process. It contains data and control information held by a single server process or background process. It is created when a user process is created and is released when the user process is terminated. This memory area is not shared by server processes. The contents of the PGA however vary in dedicated and shared server  environments. The PGA is used to process SQL statements and to hold logon and other session information. The contents of the PGA include:

The Physical Files of the Database
Recalling an earlier discussion, the Oracle Server is made up of the Oracle Database and Oracle Instance. The Oracle database has a physical and a logical structure. The physical structure of the database is made up of the operating system files that comprise the database. All the data of the database is stored in the physical files. Storage management is a one of the primary functions of the database administrator. In Oracle Database 10g, there are three types of files namely:

Parameter Files in Oracle
The Oracle 10g server is made up of the Oracle Instance and the Oracle database. The Oracle instance is created in real memory. Most actions are first performed in memory and then transferred to disk. It is therefore very important the Oracle instance be configured properly. When a database administrator starts the Oracle database, the first step involves creating the instance in memory. Various memory structures and background processes are started.

To create the instance the Oracle server uses a file known as the Initialization Parameter File. There are two types of initialization parameter files,
• The Static parameter file, PFILE, known as the initSID.ora file
• Persistent parameter file, SPFILE, known as the spfileSID.ora file

The initialization parameter file consists of a list of parameters. Each parameter has a name and a value. Most parameters have default values. Explicit values can also be assigned to the parameters. The initialization parameter file specifies information such as the values for various memory structures of the instance, the database name, the number of processes the can concurrently access the instance, parameters associated with archiving and so on. A comprehensive list of initialization parameters has been displayed along with a brief description.

In Oracle 10g, the number of initialization parameters to be configured has been greatly reduced compared to previous versions of Oracle. Parameters have been categorized into basic and advanced parameters. The basic parameters are a list of parameters that are sufficient for day-to-day interaction. The most important ones being:

Initialization Parameter Description
Compatible Refers to the version of the server with which the instance should be compatible.
The maximum number of operating system processes that can connect simultaneously to the instance.





















It is possible to dynamically change certain initialization parameter. The change will however only affect the current instance. Dynamic changes are done by using the ALTER SYSTEM command. An example of a dynamic change that will affect the current instance could be:
This change will affect only the current instance. When the database is restarted, the previous value present in the parameter file will be used.

Any permanent change you make to a parameter requires that the database be restarted. By default the parameter file is located in the %ORACLE_HOME%\dbs directory. It has a name like initSID.ora where SID is the system identifier of the database. It can be quite difficult to remember the names of all the parameters and set them properly. There is a sample file created in the Oracle database, which can be copied and modified based on your requirements. It is possible to create multiple initialization parameters files however only one will be active at any time.

Persistent Parameter File (spfile)
The persistent parameter file often called the spfile was introduced in Oracle 9i. It is located at the server-side. The spfile cannot be modified by the user and any manual change to the file will render it useless. It is also located in the %ORACLE_HOME%\dbs directory. When using the Database Configuration Assistant, you are given the option of creating an SPFILE for the database. This option is not available when you create a database manually using the CREATE DATABASE command. If you are using the SPFILE file, it can be backed up using Recovery Manager, since it is located on the server side.

In the case of the static parameter file any permanent change to a parameter would require a bounce of the database. Using the spfile you can modify parameters such that the change will not only affect the current instance but also affect future instances of the database. Hence the term persistent.

When changing a parameter dynamically, you can set the SCOPE option. The values taken by the SCOPE option are MEMORY, SPFILE or BOTH. The value BOTH is the default. When you set SCOPE=MEMORY, the change you are making will only affect the current instance. If you set SCOPE=SPFILE the change will be made in the parameter file and will come into effect when the database is restarted. If you set SCOPE=BOTH then the change will affect both the current instance and will affect future instances for the database. An example of a dynamic change only affecting the spfile is displayed below.

The ALTER SYSTEM command and the SPFILE file
The ALTER SYTEM SET command is used to modify the values of instance parameters after the instance has been created. The complete syntax of its usage is displayed below:
ALTER SYSTEM [SET|RESET] parameter_name=parameter_value [COMMENT 'text'] [SCOPE = MEMORY|SPFILE|BOTH] [SID='sid' | '*']

Parameter_name : the name of the parameter to be changed
Parameter_value : the new value to be assigned to the parameter
COMMENT: A comment to be added to the SPFILE next to the parameter that is being altered.
SCOPE: Determines if the change should be made in MEMORY, SPFILE or both areas
MEMORY: The change affects only the current running instance
SPFILE: The change affects only the SPFILE file
BOTH: The change affects both the current instance and the SPFILE.
SID : Identifies the system identifier (ORACLE_SID) for the SPFILE being used currently.
'sid': Specific SID to be used in altering the SPFILE
'*': Uses the default SPFILE
RESET: This causes the parameter to revert to its default value

Example of dynamic changes the may or may not affect the changes:
1. Modify the size of the default database buffer cache to 20M, both for the current and future instances.

As mentioned earlier it the DBCA tool gives us the option of creating an SPFILE, for the database, during database creation. However when a database is created manually, only the PFILE can be created. If you wish to create an SPFILE using a PFILE you can do so by issuing the command:
CREATE SPFILE [= 'location of the spfile'] FROM PFILE [= 'location of the pfile'] ;

If you do not specify the location, which is optional it will be created in the default location %ORACLE_HOME%\dbs directory.
It is possible to create the PFILE from the SPFILE by using the command:

An editable parameter file will be created in its default location if a path is not specified.
Retrieving the initialization parameter values using the data dictionary Parameter values stored in the Static parameter file can be viewed using the V$PARAMETER view. The parameter values from the Persistent parameter file can be viewed by issuing a query against V$SPPARAMETER data dictionary view.

Starting and Stopping the Oracle Database

A database needs to be started before it can be accessed by users. Starting up a database should be done by a privileged user. Users who have been granted the SYSDBA or SYSOPER roles can perform a startup or a shutdown of the database. When a database is started three important steps are executed. The first involves creation of the Oracle instance in memory, followed by mounting the database where the control files are read and finally opening the database where all the data stored in the database is accessible to the users of the database. To get a complete understanding let us deal with each stage individually.
1) Instance Creation – This is the first step in starting the database. The initialization parameter file is read, (spfile or pfile depending on the options used in the STARTUP command) and the System Global Area (SGA) is configured. The parameter file identifies the name of the database, and various sizes for the memory pools, the optional background processes and so on. During instance creation, the mandatory and optional background processes are started. The alert log file and other trace files are also started.

2) Mounting the Database – After instance creation, the database is mounted. When a database is mounted a database administrator can perform certain maintenance or administrative tasks. For e.g. putting a database in archivelog mode, renaming datafiles or performing a full database recovery requires the database to be mounted. During mounting, a database is associated with its previously started instance. The control files of the database are opened and read. The control file contains information about the other files of the database, their status, location and synchronizing information. This information is needed for the next stage of startup which is opening of the database.

3) Opening the database – This is the last stage in starting a database. This phase has to be performed so that users of the database can access the data in the database. Once the control file has been read and the location of the physical files of the database identified after mounting, the files are opened and made available to the users. The files that are opened are the online datafiles and the online redo log files. If any of the files are unavailable, an error will be reported and the database will not be opened. It is at this point that the Oracle server verifies the consistency of the database. In case the database was shutdown improperly the last time, it will be detected at this point and the SMON background process will perform instance recovery.

STARTUP command
The syntax of the STARTUP command with its important options has been displayed:

FORCE: Used when a startup does not occur as a result of some kind of problem, you can use this option to abort the running instance and perform a normal startup.
RESTRICT: This option is used when you want only users who possess the RESTRICTED SESSION privilege to access the database. This option can be used is you wish to perform some kind of maintenance on the database such as an export and import.
PFILE=filename: this option starts up the database using the static non-default parameter file to startup the database.
OPEN [database_name]: The instance will be created; database will be mounted and then opened for all the users. You can optionally specify the database name. This is the default option.
MOUNT: The instance will be created and the database mounted. The option can be used for performing certain maintenance tasks.
NOMOUNT: Only the instance is created. The SGA will be created in memory and the background processes will be started. You may have to use this option for certain operations such as when creating a database manually.
RECOVER: used to perform media recovery when starting the database.
READ ONLY: used to open the database for queries only. DML statements are not allowed in the database.

You can also start a database using the EM Console.

If you have started the database using the NOMOUNT option, only the instance is created. In order to then mount / open the database you can use the following ALTER DATABASE command. It is not possible to re-issue the STARTUP command with the MOUNT or OPEN options.

Current state of the database: NOMOUNT.
    To change it to the MOUNT state, issue:
    ALTER DATABASE [database_name] MOUNT;
    To change it to the OPEN state directly, issue:
    ALTER DATABASE [database_name] OPEN;

Current state of the database: MOUNT
    To change it to the OPEN state, issue:
    ALTER DATABASE [database_name] OPEN;

It is possible to open a database in a read only or read write mode using the ALTER DATABASE command. To startup the database in a read only or read write mode from a NOMOUNT or MOUNT stage you can issue;
READ ONLY – only queries are allowed on the database.
READ WRITE – the database is opened for normal read and writes.

A database may be shutdown to make it unavailable for use. You can either perform a proper or improper shutdown. During a proper shutdown, three phases complementary to the startup are performed in the reverse order. First the database is closed; this involves performing a checkpoint on all available datafiles and closing the files. Next the database is dismounted. At this time, the control file is synchronized and closed. Finally the instance that was created in memory is released. The SGA no longer exists in memory and all background processes are stopped.

The SHUTDOWN command
The syntax of the SHUTDOWN command
NORMAL: This is the default mode for shutting down the database. The Oracle server waits for all currently connected users to disconnect their sessions. No new connections are permitted. A checkpoint is performed on all the databases and the files are closed. When a database is shutdown is this mode, an instance recovery will not need to be done during the subsequent startup.
IMMEDIATE: When a database is shutdown in this mode, the Oracle server
automatically rolls back all currently active transactions. After the transactions have been rolled back the user sessions are terminated. No new connections are allowed. The database is then closed, dismounted and the instance released. No instance recovery will be performed during subsequent startup.
TRANSACTIONAL – When the database is shutdown using this option, all currently active transactions will be allowed to complete. As soon as a user's transaction completes the user is automatically disconnected. No new user connections will be allowed. The database is then closed, dismounted and the instance released. No instance recovery will be performed during subsequent startup.
ABORT – When the database is shutdown using this option, the instance is shutdown down. This is a case of an improper shutdown. No checkpointing is done. All user connections are abnormally terminated. The database is not closed or dismounted. However, the next startup will require an instance recovery to be performed by the SMON background process.

Startup Command and Parameter files
The command that is used to startup a database is:

If the database has an spfile, by default the spfileSID.ora will be used to configure the instance. If the spfileSID.ora file does not exist, Oracle will look for a default SPFILE on the server side. If one cannot be found the initSID.ora file on the server side will be used. There may be times when you wish to startup the database using the PFILE. To do so, you issue the STARTUP command with the PFILE='filename' option as shown:
SQL> STARTUP PFILE='location of the parameter_file\init<SID>.ora

If the SPFILE for a database is not located in its default location %ORACLE_HOME%\dbs, then an entry in the PFILE pointing to it location can be specified.
Example: SPFILE=<location of the SPFILE>