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=

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

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

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

=Common Non-OFA Environment Variables=

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

$ORACLE_SID - Defines which instance a Unix user session should be connecting to on the server.

%ORACLE_SID% - Defines which instance a Windows user session should connect to on the server.

$TNS_ADMIN - Specifies where the Oracle Net configuration files are stored on Unix systems—if they are to be stored outside their default location of $ORACLE_HOME\network\admin.

%TNS_ADMIN% - Specifies where the Oracle Net configuration files are stored on Windows systems—if they are to be stored outside their default location of %ORACLE_HOME%/network/admin.

$TWO_TASK - Establishes a default Oracle Net connection string that will be used on Unix systems if none is specified by the user.

%LOCAL% - Establishes a default Oracle Net connection string that will be used on Windows systems if none is specified by the user.

$LD_LIBRARY_PATH - Specifies the locations of the Oracle shared object libraries. This variable usually points to $ORACLE_HOME/lib or $ORACLE_HOME/ lib32 on Unix systems.

$PATH - Tells the operating system in which directories to look for executable files on Unix systems.

%PATH% - Tells the operating system in which directories to look for executable files on Windows systems.

Initialization Parameters.

You can specify more than 250 documented configuration parameters in the PFILE or SPFILE. Oracle 10g divides these parameters into two categories: basic and advanced. Oracle recommends that you set only about 30 basic initialization parameters manually. Oracle also recommends that you do not modify the remaining 220 or so parameters unless directed to do so by Oracle Support or to meet the specific needs of your application.

Oracle 10g Basic Initialization Parameters:

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 location(s) where the database redo log files 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. See Chapter 5 for details.

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.

PL/SQL: Procedural Database Language

SQL is a powerful language for interacting with databases, but it does have some imitations.

For example, SQL does not have very good mechanisms for condition testing, which would allow a SQL statement to execute if a given condition is true, but not execute if the condition is false. SQL also lacks looping capabilities, the ability to perform a specific SQL action for a specified number of times before stopping. Finally, SQL does not offer any exception-handling capabilities; all errors raised by SQL statements are returned directly to the user.

Oracle Procedural Language for SQL (PL/SQL) is the solution for all these limitations.

PL/SQL is a powerful extension to SQL that not only adds condition testing, looping, and exception handling, but also allows developers to write application-specific functions, procedures, packages, and triggers. Table 1.8 describes each of these types of PL/SQL objects.

Types of PL/SQL Objects

Anonymous Block - A block of PL/SQL code that is not stored in the database, but instead is embedded in a form, web page, or SQL script.

Procedure - A block of PL/SQL code that is stored in the database and performs a specific action.

Function - A block of PL/SQL code that is stored in the database and returns a value when called in a SQL statement.

Package - A collection of related procedures and/or functions that perform related functions.

Trigger - A block of PL/SQL code that runs whenever an INSERT, UPDATE, or DELETE activity occurs on a table. Can also be defined to run when certain database events occur.

Data Dictionary Views

Data dictionary views have names that begin with DBA_, ALL_, and USER_.

DBA_TABLES Shows the names and physical storage information about all the tables

in the database.

DBA_USERS Shows information about all the users in the database.

DBA_VIEWS Shows information about all the views in the database.

DBA_TAB_COLUMNS Shows all the names and datatypes of the table columns in the database.

A complete list of the Oracle 10g data dictionary views can be found in Chapters

2 and 3 of the Oracle Database Reference 10g Release 1 (10.1) Part Number

===

Dynamic Performance Views

Most of these views have names that begin with V$.

V$DATABASE Contains information about the database itself, such as the database name and when the database was created.

V$VERSION Shows which software version the database is using.

V$OPTION Displays which optional components are installed in the database.

V$SQL Displays information about the SQL statements that database users have been issuing.

A complete list of the Oracle 10g data dictionary views can be found in

Chapter 4 of the Oracle Database Reference 10g Release 1 (10.1) Part Number

===

A Comparison of Data Dictionary and Dynamic Performance Views:

1) Dictionary Views

2)Dynamic Performance Views

1) The DBA_ views usually have plural names (for example, DBA_DATA_FILES).

2) The names of the V$ views are generally singular (for example, V$DATAFILE).

1) The DBA_ views are available only when the database is open and running.

2) Some V$ views are available even when the database is not fully open and running.

1) The data contained in the DBA_ views is generally uppercase.

2) The data contained in the V$ views is usually lowercase.

1) The data contained in the DBA_ views is static and is not cleared when the database is shut down.

2) The V$ views contain dynamic statistical data that is lost each time the database is shut down.

As an alternative to querying data dictionary and dynamic performance views directly, you can use the web-based Oracle Enterprise Manager Database Control tools to graphically display metadata information.

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

=Deleting an Oracle Database Manually=

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

Some DBAs prefer to use a command-line interface to perform their tasks. You can delete a database using the command-line tool SQL*Plus.

To do so, first connect to SQL*Plus as an administrator that has the ability to start up the database, that is, an administrator with either SYSOPER or SYSDBA privileges.

For example:

/u01/app/oracle>sqlplus sys/oracle as sysdba

Once you are connected, you need to put the database in MOUNT RESTRICT mode. Issue the following command if the database is not running:

Startup mount restrict;

Next, issue the following command:

Drop database;

This command deletes all the files associated with the database. If you are using raw disk devices, the special files created for these devices are not deleted. Also, you may have to remove any archived logs from the database archive area using the appropriate operating system command.

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

=SQL*Plus Startup Command Examples=

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

STARTUP NOMOUNT pfile=/u01/oracle/init.ora - Start up Oracle in NOMOUNT mode using a nondefault parameter file

STARTUP MOUNT - Start up Oracle in MOUNT mode using a default SPFILE or PFILE

STARTUP OPEN - Start up Oracle in OPEN mode using a default SPFILE or PFILE

STARTUP RESTRICT - Start up Oracle in OPEN mode and allow only users with restricted session privileges to connect to the database

STARTUP FORCE - Force database startup using the default PFILE or SPFILE

STARTUP OPEN PFILE=/u01/sp01.ora - Start up Oracle in OPEN mode using a nondefault parameter file

iSQL*Plus

http://servername:5560/isqlplus

The iSQL*Plus listener process must be running on the host server before you can connect using your web browser. You can use the operating system command isqlplusctl start to start the iSQL*Plus listener on the host server.

Usage:

isqlplusctl start

isqlplusctl stop

Enterprise manager.

The EM Database Control process must be running on the host server before you can connect using your web browser. You can use the operating system command emctl start dbconsole to start the EM process on the host server. The default URL for accessing EM Database Control is http://hostname:5550/em.

The issue - can't start EM from CMD.

Solution (set ORACLE_SID=TST /*without blanks, etc*/)

C:\Documents and Settings\disa>emctl status dbconsole

Environment variable ORACLE_SID not defined. Please define it.

C:\Documents and Settings\disa>set ORACLE_SID=TST

C:\Documents and Settings\disa>emctl status dbconsole

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

Oracle Enterprise Manager 10g is not running.

------------------------------------------------------------------

Logs are generated in directory D:\oracle\product\10.2.0\db_1/philka_TST/sysman/

log

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

=Obtaining a List of Ports Used by the Oracle Components=

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

To view a summary of which ports are in use by the various Oracle components, you can view the portlist.ini file. This file is normally located in the $ORACLE_HOME/install directory on Unix or %ORACLE_HOME/install directory on Windows. Here is an example of what you might see in the portlist.ini file:

Ultra Search HTTP port number =5620

Enterprise Manager Agent Port =

iSQL*Plus HTTP port number =5560

Enterprise Manager Console HTTP Port (orcl) = 5500

Enterprise Manager Agent Port (orcl) = 1830

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

=The DBCA Database Management Options=

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

Create A Database - Allows for the step-by-step creation of a database. The database can be created based on an existing template or customized for the specific needs of the organization.

Configure Database Options - Performs the necessary changes to move from a dedicated server to a Shared Server. You can also add database options that have not been previously configured for use with your database.

Delete A Database - Completely removes a database and all associated files.

Manage Templates - Manages database templates. The database templates are definitions of your database configuration saved in an XML file format on your local hard disk. You can choose from several predefined templates, or you can create customized templates.