oracle Server Architecture

============================

=Oracle Server architecture=

============================

The Oracle Server architecture can be described in three categories:

I. User-related processes

II. Logical memory structures that are collectively called an Oracle instance

III. Physical file structures that are collectively called a Oracle database

===================

=I. User Processes=

===================

At the user level, two processes allow a user to interact with the instance and, ultimately, with the database: the User Process and the Server Process.

Whenever a user runs an application, such as a human resources or order-taking application, Oracle starts a User Process to support the user’s connection to the instance. Depending on the technical architecture of the application, the User Process exists either on the user’s own PC or on the middle-tier application server. The User Process then initiates a connection to the instance. Oracle calls the process of initiating and maintaining communication between the User Process and the instance a connection. Once the connection is made, the user establishes a session in the instance.

After establishing a session, each user then starts a Server Process on the host server itself. It is this Server Process that is responsible for performing the tasks that actually allow the user to interact with the database.

Server Processes generally have a one-to-one relationship with User Processes—each User Process connects to one and only one Server Process. However, in some Oracle configurations, multiple User Processes can share Server Processes.

In addition to the User and Server processes that are associated with each user connection, an additional memory structure called the Program Global Area (PGA) is also created for each user. The PGA stores user-specific session information such as bind variables and session variables. Every Server Process on the server has a PGA memory area.

The Server Process communicates with the Oracle instance on behalf of the user.

=====================

=II. Oracle instance=

=====================

An Oracle Server instance is made up of Oracle’s main memory structure, called the System Global Area (SGA), and several Oracle background processes.

==========================

=System Global Area (SGA)=

==========================

select * from V$SGA

select * from V$SGA_DYNAMIC_COMPONENTS

Required SGA Components

Shared Pool - Caches the most recently used SQL statements that have been issued by database users

Database Buffer Cache - Caches the data that has been most recently accessed by database users

Redo Log Buffer - Stores transaction information for recovery purposes

Optional SGA Components

Java Pool - Caches the most recently used Java objects and application code when Oracle’s JVM option is used

Large Pool - Caches data for large operations such as Recovery Manager (RMAN) backup and restore activities and Shared Server components

Streams Pool - Caches the data associated with queued message requests when Oracle’s Advanced Queuing option is used

======================

=Background processes=

======================

select * from v$bgprocess

Required Oracle Background Processes

1) System Monitor (SMON) - Performs instance recovery following an instance crash, coalesces free space in the database, and manages space used for sorting

2) Process Monitor (PMON) - Cleans up failed user database connections

3) Database Writer (DBWn*) - Writes modified database blocks from the SGA’s Database Buffer Cache to the datafiles on disk

4) Log Writer (LGWR) - Writes transaction recovery information from the SGA’s Redo Log Buffer to the online Redo Log files on disk

5) Checkpoint (CKPT) - Updates the database files following a Checkpoint Event

*The n in any operating system process name signifies that more than one of these processes may be running. In

these cases, the n is replaced with a numeric value. For example, if four database writer processes are running, their

process names at the operating system level are DBW0, DBW1, DBW2, and DBW3.

====================================================================

Optional Oracle Background Processes

1) Archiver (ARCn) - Copies the transaction recovery information written to disk by LGWR (log writer) to the online Redo Log files and to a secondary location in case it is needed for recovery. Nearly all production databases use this optional process.

2) Recoverer (RECO) - Recovers failed transactions that are distributed across multiple databases when using Oracle’s distributed database feature.

3) Job Queue Monitor (CJQn) - Assigns jobs to the Job Queue processes when using Oracle’s job scheduling feature.

4) Job Queue (Jnnn) - Executes database jobs that have been scheduled using Oracle’s job scheduling feature.

5) Queue Monitor (QMNn) - Monitors the messages in the message queue when Oracle’s Advanced Queuing feature is used.

6) Parallel Query Slave (Qnnn) - Used to carry out portions of a larger overall query when Oracle’s Parallel Query feature is used.

7) 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.

8) Shared Server (Snnn) - Server Processes that are shared among several users when Oracle’s Shared Server feature is used.

9) Memory Manager (MMAN) - Manages the size of each individual SGA component when Oracle’s Automatic Shared Memory Management feature is used.

10) Memory Monitor (MMON) - Gathers and analyzes statistics used by the Automatic Workload Repository feature.

11) Memory Monitor Light (MMNL) - Gathers and analyzes statistics used by the Automatic Workload Repository feature.

12) Recovery Writer (RVWR) - Writes recovery information to disk when Oracle’s Flashback Database Recovery feature is

used.

13) Change Tracking Writer (CTWR) - Keeps track of which database blocks have changed when Oracle’s incremental Recovery Manager feature is used.

================================

=Database Writer process (DBWn)=

================================

When a user performs a SQL operation on a table, the user’s Server Process copies the affected data from the datafiles into the Database Buffer Cache in the SGA. If the user has performed a committed transaction that modifies that data, the Database Writer process (DBWn) ultimately writes the modified data back to the datafiles.

When Does Database Writer Write?

The DBWn background process writes to the datafiles whenever one of the following events occurs:

- A user’s Server Process has searched too long for a free buffer when reading a buffer into the Buffer Cache.

- The number of modified and committed, but unwritten, buffers in the Database Buffer Cache is too large.

- At a database Checkpoint event. See Chapters 10 and 11 for information on checkpoints.

- The instance is shut down using any method other than a shutdown abort.

- A tablespace is placed into backup mode.

- A tablespace is taken offline to make it unavailable or changed to READ ONLY.

- A segment is dropped.

===================

=Log Writer (LGWR)=

===================

When Does Log Writer Write?

The LGWR background process writes to the current redo log group whenever one of the following database events occurs:

- Every three seconds.

- A user commits a transaction.

- The Redo Log Buffer is one-third full.

- The Redo Log Buffer contains 1MB worth of redo information.

- Before the DBWn process whenever a database checkpoint occurs. See Chapter 10 for more information on checkpoints.

==========================

=III. The Oracle Database=

==========================

select * from v$datafile

select * from dba_data_files

An instance is a temporary memory structure, but the Oracle database is made up of a set of physical files that reside on the host server’s disk drives. These files are called control files, datafiles, and redo logs. Additional physical files that are associated with the Oracle database, but are not technically part of the database, are the password file, the PFILE and SPFILE described previously, and any archived redo log files.

Control file - Locations of other physical files, database name, database block size, database character set, and recovery information. These files are required to open the database.

Datafile - All application data and internal metadata.

Redo log files - Record of all changes made to the database; used for recovery.

Parameter (PFILE or SPFILE) file - Configuration parameters for the SGA, optional Oracle features, and background processes.

Archived log - Copy of the contents of previous online redo logs, used for recovery.

Password - Optional file used to store names of users who have been granted the SYSDBA and SYSOPER privileges.

Oracle Net - Entries that configure the database listener and client-todatabase connectivity.

===============

=Control Files=

===============

select * from v$controlfile

Control files are critical components of the database because they store important information that is not available anywhere else. This information includes the following:

- The name of the database

- The names, locations, and sizes of the datafiles and redo log files

- Information used to recover the database in the case of a disk failure or user error

The control files are created when the database is created in the locations specified in the control_files parameter in the parameter file. Because loss of the control files negatively impacts the ability to recover the database, most production databases multiplex their control files to multiple locations. Oracle uses the CKPT background process to automatically update each of these files as needed, keeping the contents of all copies of the control synchronized. You can use the dynamic performance view V$CONTROLFILE to display the names and locations of all the database’s control files.

===========

=Datafiles=

===========

Datafiles are the physical files that actually store the data that has been inserted into each table in the database. The size of the datafiles is directly related to the amount of table data that they store. Datafiles are the physical structure behind another database storage area called a tablespace. A tablespace is a logical storage area within the database. Tablespaces group logically related segments. For example, all the tables for the Accounts Receivable application might be stored together in a tablespace called AR_TAB, and the indexes on these tables might be stored in a tablespace called AR_IDX.

By default, every Oracle 10g database must have at least three tablespaces.

SYSTEM - Stores the data dictionary tables and PL/SQL code.

SYSAUX - Stores segments used for database options such as the Automatic Workload Repository, Online Analytical Processing (OLAP), and Spatial.

TEMP - Used for performing large sort operations. TEMP is required when the SYSTEM tablespace is created as a locally managed tablespace; otherwise, it is optional. See Chapter 3 for details.

In addition to these three required tablespaces, most databases have tablespaces for storing other database segments similar to those

TOOLS - Used to store segments for nonapplication management tools.

USERS - Used as the default tablespace for database users.

UNDOTBS1 - Used to store transaction information for read consistency and recovery purposes. See Chapter 8 for details.

================

=Redo Log Files=

================

select * from v$logfile

select * from v$log

Whenever a user performs a transaction in the database, the information needed to reproduce this transaction in the event of a database failure is automatically recorded in the Redo Log Buffer. The contents of the Redo Log Buffer are ultimately written to the redo logs by the LGWR background process.

Because of the important role that redo logs play in Oracle’s recovery mechanism, they are usually multiplexed, or copied. This means that each redo log contains one or more copies of itself in case one of the copies becomes corrupt or is lost due to a hardware failure. Collectively, these sets of redo logs

are referred to as redo log groups. Each multiplexed file within the group is called a redo log group member. Oracle automatically writes to all members of the redo log group to keep the files in sync. Each redo log group must be composed of one or more members. Each database must have a minimum of two redo log groups because redo logs are used in a circular fashion.

You can use the V$LOGFILE dynamic performance view to view the names of the redo log groups and the names and locations of their members.

When a user performs a DML activity on the database, the recovery information for this transaction is written to the redo log buffer by the user’s Server Process. LGWR eventually writes this recovery information to the active redo log group until that log group is filled.

Once the current log fills with transaction information, LGWR switches to the next redo log until that log group fills with transaction information, and so on, until all available redo logs are used. When the last redo log is used, LGWR wraps around and starts using the first redo log again. You can use the V$LOG dynamic performance view to display which redo log group is currently active and being written to by LGWR.

Once redo log group 1 is full, LGWR switches to redo log group 2.

When LGWR wraps around from the last redo log group back to the first redo log group, any recovery information previously stored in the first redo log group is overwritten and therefore no longer available for recovery purposes. However, if the database is operating in archive log mode, the contents of these previously used logs are copied to a secondary location before the log is reused by LGWR. If this archiving feature is enabled, it is the job of the ARCn background process described in the previous section to copy the contents of the redo log to the archive location. These copies of old redo log entries are called archive logs.

Nearly all production databases run in archive log mode because they need to be able to redo all transactions since the last backup in the event of a hardware failure or user error that damages the database.

!!!If LGWR needs to write to the redo log group that ARCn is trying to copy but cannot because the destination is full, the database hangs until space is cleared on the drive!!!

Web-Based Management and Development Tools

In addition to iSQL*Plus, Oracle provides several other web-based tools for accessing and manipulating data in databases. Most of these tools do not require that the user construct their own SQL statements the way SQL*Plus or iSQL*Plus do. Instead, these tools either dynamically generate SQL code or use SQL code stored in the database to interact with the database.

One example of this type of query tool is Oracle Discoverer. Discoverer is an end-user query tool that allows users to run predefined and ad hoc reports from their web browser simply by clicking the tables that they want to query. Oracle Forms and Reports also allows users to access databases using web-based forms and reports.

Another tool, Enterprise Manager (EM) Database Control, is Oracle’s web-based database administration tool. EM Database Control dynamically produces SQL commands that are sent to the database based on the navigational choices that are made within EM Database Control.

In addition to EM Database Control, you can install EM client software on your computer so that you can manage database tasks without using EM Database Control, if needed.

=====================================

=Least recently used (LRU) algorithm=

=====================================

Oracle uses a least recently used (LRU) algorithm to manage the contents of the Shared Pool and Database Buffer Cache. When a user’s Server Process needs to put a SQL statement into the Shared Pool or copy a database block into the Buffer Cache, Oracle uses the space in memory that is occupied by the least recently accessed SQL statement or buffer to hold the requested

SQL or block copy. Using this technique, Oracle keeps frequently accessed SQL statements and database buffers in memory longer, improving the overall performance of the server by minimizing parsing and physical disk I/O.

Comparison of PFILES and SPFILES

1) PFILE

2) SPFILE

1) Text file that can be edited using a text editor.

2) Binary file that cannot be edited directly.

1) When changes are made to the PFILE, the instance must be shut down and restarted before it takes effect.

2) Most changes to the SPFILE can be made dynamically, while the instance is open and running.

1) Is called initinstance_name.ora.

2) Is called spfileinstance_name.ora.

1) Can be created from an SPFILE using the create pfile from spfile command.

2) Can be created from a PFILE using the create spfile from pfile command.

in sqlplus (or console) - show parameter spfile /*if null - then pfile is in use*/

=========================================

=The Optimal Flexible Architecture (OFA)=

=========================================

The OFA model addresses four areas:

- Naming conventions for Unix file systems and mount points

- Naming conventions for directory paths

- Naming conventions for database files

- Standardized locations for Oracle-related files

In addition to using the OFA model, planning your install also means answering the following questions:

- Which operating system user will own the installed Oracle software?

- On which disk drive and directory will the Oracle software be installed?

- What directory structure will be used to store the Oracle software, its related configuration files, and the database itself?

- How should the database files be laid out so that the maximum performance benefits will be realized?

- How should the database files be laid out so that the maximum recoverability benefits will be realized?

==================================

=Creating the Oracle User Account=

==================================

On UNIX systems, every file is owned by an operating system user account. Therefore, before you can install the Oracle software, you must create a UNIX user account that will own the Oracle binaries. The user name for this account can be anything, but common Oracle user names include oracle, ora10g, and ora101. Each Unix user is also in one or more operating system groups. Create a new operating system group for the Oracle Unix user. This group is usually called dba, and you will be prompted for it later during the installation.

=================================

=Naming Volumes and Mount Points=

=================================

Unless Oracle’s automatic storage management feature or raw devices are used, almost all files on a Unix server are stored on logical storage areas called volumes, which are attached, or mounted, to directories, or mount points, by the Unix system administrator. The OFA model suggests that these mount points be given a name that consists of a combination of a character and numeric values. Examples of common OFA mount points for Unix systems include the following:

- /u01

- /mnt01

- /du01

- /d01

Notice that the naming convention for these mount points is generic. The mount point’s name has no relationship to what type of file it will ultimately hold. The OFA model recommends this generic naming convention because it provides the greatest flexibility for future management of the server’s file systems.

==============================

=Creating OFA Directory Paths=

==============================

The OFA model prescribes that the directory structures under the mount points use a consistent and meaningful naming convention. In addition to this naming convention, the OFA models also assigns standard operating system environment variable names to some of these directory paths as “nicknames” to aid in navigation and ensure portability of the directory structures in the event that they need to be moved to new file systems.

==================================================

=Comparison of Unix Directory Paths and Variables=

==================================================

$ORACLE_BASE - /u01/app/oracle - Top-level directory for Oracle software on the host server

$ORACLE_HOME - /u01/app/oracle/product/10.1.0 - Directory into which the Oracle 10g software will be installed

=====================================================

=Comparison of Windows Directory Paths and Variables=

=====================================================

%ORACLE_BASE% - D:\ORACLE - Top-level directory for Oracle software on the host server

%ORACLE_HOME% - D:\ORACLE\ORA101 - Directory into which the Oracle 10g software will be installed

These environment variables are used extensively when installing, patching, upgrading, and managing Oracle systems.

$ORACLE_HOME/dbs - Default location for PFILES and SPFILES on Unix systems

%ORACLE_HOME%\database - Default location for PFILES and SPFILES on Windows systems

$ORACLE_BASE/admin/PROD/pfile - Location of the PFILE for a database called PROD on Unix systems

%ORACLE_BASE%\admin\PROD\pfile - Location of the PFILE for a database called PROD on Windows systems

$ORACLE_HOME/network/admin - Default location for Oracle Net configuration files on Unix systems

%ORACLE_HOME%\network\admin - Default location for Oracle Net configuration files on Windows systems

$ORACLE_HOME/rdbms/admin - Location of many Oracle database configuration scripts on Unix systems

%ORACLE_HOME%\rdbms\admin - Location of many database configuration scripts on Windows systems

For Unix systems, $ORACLE_HOME/dbs is the default location for the PFILE and SPFILE, but then says that PFILES should be stored in $ORACLE_BASE/admin/PROD/pfile. Windows systems are similar. This implies that the same file needs to be in two locations at the same time. You can accomplish this using two tricks. Which you use depends on your operating system.

On Unix systems, you can create the PFILE in the $ORACLE_BASE/admin/PROD/pfile directory and then create a symbolic link in $ORACLE_HOME/dbs that points to the file in $ORACLE_BASE/admin/PROD/pfile using this syntax:

ln -s $ORACLE_BASE/admin/PROD/pfile/initPROD.ora $ORACLE_HOME/dbs/initPROD.ora

On Windows systems, you can create the PFILE in the %ORACLE_BASE%\admin\PROD\pfile directory and then put another PFILE in %ORACLE_HOME%\dbs that contains a single entry that points to the other PFILE in %ORACLE_BASE%\admin\PROD\pfile like this:

ifile=D:\oracle\admin\PROD\pfile\initPROD.ora Using these techniques allows you to put the initialization parameter files in their default locations under $ORACLE_HOME, but also in their desired location under $ORACLE_BASE.

==================================

=Other Administrative Directories=

==================================

According to the OFA model, for a database called PROD, the initialization parameter file should be located in $ORACLE_BASE/admin/PROD/pfile. However, the OFA model also recommends that several other directories be built under this location for other management purposes.

These directories are located under $ORACLE_HOME/admin/PROD:

adhoc This directory is designed to store ad hoc SQL scripts for the PROD database.

arch If the database is in archive log mode, this directory is specified as the location where the archived redo logs are to be written by LGWR. The PFILE/SPFILE parameter called LOG_ARCHIVE_DEST specifies the location of the arch directory.

adump If database auditing is turned on, this directory is specified as the location where the audit trail information is to be written. The PFILE/SPFILE parameter AUDIT_FILE_DEST specifies the location of the adump directory.

bdump This directory is the location of the database Alert log file and any trace files generated by background processes. The PFILE/SPFILE parameter BACKGROUND_DUMP_DEST specifies the location of the bdump directory.

cdump This directory is the location where core dump files will be written by operating system processes that crash. The PFILE/SPFILE parameter CORE_DUMP_DEST specifies the location of the cdump directory. create This directory stores the SQL scripts that were used to initially create the database. These scripts may have been manually created or created using the Oracle Database Configuration Assistant described in Chapter 2.

exp This directory stores files that have been created using the Oracle export utility.

logbook This directory stores files that document the activities you performed on the database.

pfile This directory stores the parameter initialization file, or PFILE, for the database.

udump This directory is the location where any trace files generated by user processes will be written. The PFILE/SPFILE parameter USER_DUMP_DEST specifies the location of the udump directory.

=====================================

=Creating OFA Directories Structures=

=====================================

The OFA directory structure and its associated operating system environment variables are primarily used to manage the Oracle 10g software binaries and the supporting files for an instance such as the PFILE and SPFILES. However, the OFA model also prescribes a directory naming convention and structure for the physical database files. In general, the OFA model recommends

that the physical database file be located in a directory structure like the one shown here for a database called PROD with five mount points:

- /u01/oradata/PROD

- /u02/oradata/PROD

- /u03/oradata/PROD

- /u04/oradata/PROD

- /u05/oradata/PROD

Notice that the directory naming convention incorporates the database name in the path. This makes it easier to have multiple files from multiple databases on the same mount points, on the same server. In addition to these directory paths, the names of the physical database files themselves also have an OFA naming convention, which is discussed in the following sections.

===============

=Control Files=

===============

Control files use names such as controln.ctl, in which n is the number of the copy of the multiplexed control file, for example, control01.ctl, control02.ctl, and so on.

===========

=Datafiles=

===========

Datafiles use names such as filenamen.dbf, in which n is the number of the datafile of a tablespace that is composed of multiple datafiles. The datafile names should also describe the tablespace to which they belong. For example, if a tablespace called TOOLS comprises four datafiles, those datafiles might be called tools01.dbf, tools02.dbf, tools03.dbf, and tools04.dbf.

Databases that use the Oracle Managed Files (OMF) feature will use systemgenerated filenames.

================

=Redo Log Files=

================

Redo logs use names such as redogm.log, in which g is the group number of the redo log and m is the member number. For example, if a database has three redo log groups, and each redo log group is multiplexed with two members, the first redo log group’s files might be called redo1a.log and redo1b.log.

Many of these environment variables and OFA file locations are used by the Oracle Universal Installer during the installation process.

============================

=Oracle Universal Installer=

============================

The OUI process consists of six primary operations:

- Mounting the CD and starting the OUI

- Performing preinstallation checks

- Responding to server-specific prompts for file locations, names, and so on

- Selecting the products that you want to install

- Copying the files from the install media to $ORACLE_HOME

- Compiling the Oracle binaries

- Performing post-install operations using Configuration Assistants

============

=Enviroment=

============

==================================================

=Comparison of Unix Directory Paths and Variables=

==================================================

$ORACLE_BASE - /u01/app/oracle - Top-level directory for Oracle software on the host server

$ORACLE_HOME - /u01/app/oracle/product/10.1.0 - Directory into which the Oracle 10g software will be installed

=====================================================

=Comparison of Windows Directory Paths and Variables=

=====================================================

%ORACLE_BASE% - D:\ORACLE - Top-level directory for Oracle software on the host server

%ORACLE_HOME% - D:\ORACLE\ORA101 - Directory into which the Oracle 10g software will be installed

============================================

=Common Uses of ORACLE_BASE and ORACLE_HOME=