[Laskey99] Section 3.4. Configuring Oracle
来源:百度文库 编辑:神马文学网 时间:2024/03/28 21:41:10
3.4.
Oracle's overallperformance is affected by the components that are installed, as wellas by how those components are configured. A high-performance Oracledatabase is essential to obtaining maximum performance fromtransactions run against that database. This section provides generalconfiguration guidelines and some specific recommendations forconfiguring SQL*Net/Net8, MTS, Parallel Query, and Parallel Server.
3.4.1. Configuration Guidelines
While every installation is different, there are some generalconfiguration guidelines that can be applied to most databases,regardless of the components installed or the use of the particulardatabase. These general guidelines are described in the followingsections.
3.4.1.1. Check the documentation
Thisone may seem obvious, but it needs to be said: read thedocumentation. Even experienced DBAs will benefit from a quick readof the pertinent documentation before beginning an Oracleinstallation. We recommend that you look at the following (at least):
-
Hardware-specific Installation and User's Guide (IUG)
-
Server Administrator's Guide
-
Release Notes (usually packed with the media)
-
The README file, which is usually found on the installation media and contains last-minute information that may not be in the printed documentation.
3.4.1.2. Check resource requirements
Before beginning an installation,be certain that sufficient system resources are available. TheIUG for your platform contains comprehensiveinformation about disk storage and memory requirements. Remember thatthese requirements are minimums, and that the resources required mayactually be higher, depending on other configuration decisions youmake. For example, more memory will be required if you specify alarger SGA.
In particular, make sure there is enough disk space available on thedevice where you place the Oracle software (typically calledORACLE_HOME ) to load all software and ancillaryfiles.
3.4.1.3. Check system privileges
Most operatingsystems require the account that is performing the Oracleinstallation to have certain privileges. Be sure to check theIUG for these, and make sure the systemadministrator has set them properly. Note that these privileges mayinclude the right to create directories and files on specificdevices.
3.4.1.4. Determine control file locations
Oracle requires at least one control file. You shouldrequire at least two, and usually more, control files. This iscritical because if all copies of the control file are lost, you willbe unable to mount your database. Plan to place control files ondifferent disk devices and, where possible, on different diskcontrollers.
3.4.2. SQL*Net Configuration
SQL*Net (Oracle7) and Net8(Oracle8) must be configured, usually using Oracle Network Manager orthe Net8 Assistant. This is typically done after the databasesoftware is installed and after at least one Oracle instance is upand running, but the configuration should be planned in advance.Before beginning a SQL*Net/Net8 configuration, you must know:
-
The types of network protocols that will be used to access Oracle in your environment
-
The naming scheme you will use to identify Oracle network nodes
-
The names and network locations of all servers, gateways, and MultiProtocol Interchanges in your environment
Once SQL*Net/Net8 is configured, the following files (at least) mustbe placed on each server:
listener.ora
Controls the operation of the SQL*Net listener process
tnsnames.ora
Maintains the relationship between logical node names (aliases) andphysical locations in the network when the Oracle Names software isnot used
sqlnet.ora
Controls logging of Oracle network operations (not required buthighly desirable)
If you are using the Multi-Threaded Server, this fact must also beconfigured in the INIT.ORA file, as shown in thenext section.
3.4.3. Multi-Threaded Server Configuration
The Multi-Threaded Server(MTS) is configured in the INIT.ORA file, asshown in the following sample INIT.ORA parametersettings:
Code View:Scroll/Show Allmts_dispatchers="ipc,1"
mts_dispatchers="tcp,1"
mts_max_dispatchers=10
mts_servers=1
mts_max_servers=10
mts_service=TEST
mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=TEST))"
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(HOST=10.74.72.42)(PORT=1526))"
This example will configure a Multi-Threaded Server that will handleTCP/IP connections to the TEST database. A maximum of 10 dispatcherswill be started, and up to 10 server processes will be created.
Remember that each MTS process counts against the total count specified in the INIT.ORA parameter PROCESSES, as well as against the maximum processes allowed for the Oracle user at the operating system level.
3.4.4. Parallel Query Configuration
ParallelQuery Option (PQO) is a powerful feature of Oracle, but in order touse it properly, the database must be configured properly. ParallelQuery allows multiple CPU systems to divide certain database tasks(usually full table scans) into several pieces that can be executedat the same time (in parallel). In order to perform this task, thefollowing are required:
-
Multiple parallel processes must be permitted by setting the INIT.ORA parameter PARALLEL_MAX_SERVERS to a value greater than 0.
-
Tablespaces must be created using multiple datafiles, which should be allocated to separate devices. Ideally, there will be as many devices allocated to each tablespace as there are CPUs in the system.
-
Tables taking advantage of Parallel Query should have their degree of parallelism set (using the PARALLEL clause in the CREATE TABLE statement) to the number of datafiles comprising the tablespace in which the table is created.
3.4.5. Parallel Server Configuration
In order to utilize OracleParallel Server (OPS), which allows a single Oracle database to beshared by multiple Oracle instances, you must carefully specify theParallel Server characteristics usingINIT.ORA parameters on eachparticipating instance, including:
PARALLEL_SERVER
Must be set to TRUE to enable the Oracle Parallel Server (Oracle8only).
INSTANCE_NUMBER
Identifies the instance to the database.
ROLLBACK_SEGMENTS
Specifies the private rollback segments to be used by each instance.Public rollback segments can also be specified, but this is notnecessary.
THREAD
Identifies the redo log thread to be associated with the instance.
GC_DB_LOCKS
The total number of instance locks (Oracle7 only).
GC_FILES_TO_LOCKS
The number of database file locks.
GC_LCK_PROCS
The total number of distributed locks.
GC_ROLLBACK_LOCKS
The total number of rollback locks.
GC_SAVE_ROLLBACK_LOCKS
The number of rollback save locks (Oracle7 only).
GC_SEGMENTS
The maximum number of segments that may have activities impactingspace management performed on them simultaneously (Oracle7 only).
INSTANCE_GROUPS
Assigns the instance to one or more specified groups (Oracle8 only).
LM_LOCKS
The number of locks that will be configured for the lock manager(Oracle8 only).
LM_PROCS
The number of processes for the lock manager (Oracle8 only).
LM_RESS
The number of resources that can be locked by each lock managerinstance (Oracle8 only).
OPS_ADMIN_GROUP
Assigns the instance to a group for monitoring (Oracle8 only).
PARALLEL_INSTANCE_GROUP
Identifies the parallel instance group to be used for spawningparallel query slaves (Oracle8 only).
ROW_LOCKING
Should be set to ALWAYS.
SERIALIZABLE
Should be set to FALSE (Oracle7 only).
SINGLE_PROCESS
Should be set to FALSE (Oracle7 only).
Additional information on these parameters can be found in Chapter 12. Because Oracle Parallel Server is a verycomplex product, you should consult the Oracle ParallelServer Concepts and Administration Guide before attemptingto configure a Parallel Server environment. Here are a few points tokeep in mind when doing this configuration:
-
On Unix platforms, all datafiles must be created in raw partitions.
-
When creating a database, only redo thread 1 is created automatically; additional threads must be explicitly created, and you must specify which thread a redo log belongs to.
-
Although not required, ensuring that the instance number and thread number are the same will avoid confusion.
The terms "Parallel Query" and "Parallel Server" are often confused. Parallel Query refers to the ability of a single Oracle instance to divide an operation (for example, a full table scan) across multiple CPUs on the same host computer and merge the completed results. Parallel Server, on the other hand, is a feature whereby multiple Oracle instances on different host machines share a single physical database. In this case, work is divided across Oracle instances either by distributing users across multiple instances, or by spawning parallel query processes across instances.
Related Content
Configuring Oracle Net
From: Oracle Essentials, 4th Edition
System Requirements
From: Migrating to Oracle8i
Installing and Configuring SQL*Net
From: Oracle Security
Oracle Networking Fundamentals
From: Oracle in a Nutshell
The Oracle Security Model
From: Oracle Security
The Instance and the Database: Starting an Oracle Database
From: Oracle Security
An Overview of Databases and Instances
From: Oracle Database 10g DBA Handbook
Major Parts of the Installation
From: Guerrilla Oracle®: The Succinct Windows Perspective
Overview of Oracle Net
From: Oracle Database 11g DBA Handbook: Administer a Scalable, Secure Oracle Enterprise Database
An Overview of Databases and Instances
From: Oracle Database 11g DBA Handbook: Administer a Scalable, Secure Oracle Enterprise Database