Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Administering Oracle9i

Overview

Environment Variables

Oracle9i Environment Variables

UNIX Environment Variables

Setting a Common Environment

Setting and Exporting the Value of a Variable in a Current Session

Setting the System Time

Relinking Executables

System Global Area

Determining the Size of the SGA

Intimate Shared Memory (Solaris Only)

Oracle9i Memory Requirements

Database Limits

Operating System Accounts and Groups

Oracle Software Owner Account

OSDBA, OSOPER, and ORAINVENTORY Groups

Groups and Security

Security for Database Files

External Authentication

Running the orapwd Utility

Password Management

Customizing the Initialization File

Embedded PL/SQL Gateway

Overview

Installing the Embedded PL/SQL Gateway

Oracle HTTP Server powered by Apache

Oracle HTTP Server Status

Oracle HTTP Server Log Files

Demonstration Files

SQL*Loader Demonstrations

Administering SQL*Loader

PL/SQL Demonstrations

2 Tuning for Oracle9i on UNIX

Importance of Tuning

Types of Performance Bottlenecks

Operating System Tools

Common Tools

vmstat

sar

iostat

swap, swapinfo, swapon, and lsps

Linux Tools

Solaris Tools

AIX Tools

HP Tools

Oracle SQL Tuning Tools

Tuning Memory Management

Allocate Sufficient Swap Space

Control Paging

Adjust Oracle Block Size

Tuning Disk I/O

Choose the Appropriate File System Type

Monitoring Disk Performance

Tuning CPU Usage

Keep All Oracle Users/Processes at the Same Priority

Tuning Oracle Resource Contention and UNIX Kernel Parameters

Tuning the Operating System Buffer Cache

Using Raw Devices/Volumes

Guidelines for Using Raw Devices/Volumes

Raw Device Setup

Using Trace and Alert Files

Trace Files

Alert Files

3 Administering SQL*Plus

Administering SQL*Plus

Using Setup Files

Using the PRODUCT_USER_PROFILE Table

Using Demonstration Tables

SQL*Plus Online Help

Installing the SQL*Plus Online Help

Removing the SQL*Plus Online Help

Using SQL*Plus

Using a System Editor from SQL*Plus

Running Operating System Commands from SQL*Plus

Interrupting SQL*Plus

Using the SPOOL Command

SQL*Plus Restrictions

Resizing Windows

Return Codes

4 Using Oracle Precompilers and the Oracle Call Interface

Overview of Oracle Precompilers

Precompiler Configuration Files

Relinking Precompiler Executables

Precompiler README Files

Issues Common to All Precompilers

Static and Dynamic Linking

Client Shared Library

Pro*C/C++ Precompiler

Pro*C/C++ Demonstration Programs

Pro*C/C++ User Program

Pro*COBOL Precompiler

Pro*COBOL Environment Variables

Pro*COBOL Oracle Runtime System

Pro*COBOL Demonstration Programs

Pro*COBOL User Programs

FORMAT Precompiler Option

Pro*FORTRAN Precompiler

Pro*FORTRAN Demonstration Programs

Pro*FORTRAN User Programs

SQL*Module for Ada (Solaris and AIX Only)

SQL*Module for Ada Demonstration Programs

SQL*Module for Ada User Programs

Oracle Call Interface

OCI Demonstration Programs

OCI User Programs

Custom Make Files

Correcting Undefined Symbols (Solaris Only)

Multi-threaded Applications

Using Signal Handlers

XA Functionality

5 Configuring Oracle Net Services

Core Oracle Net Services Products and Features

Oracle Net Services and Utilities

Oracle Net Services Protocol Support

ADDRESS Specification

Bequeath (BEQ) Protocol Support

IPC Protocol Support

Specifying an IPC ADDRESS

RAW Protocol Support

TCP/IP Protocol Support

Specifying a TCP/IP ADDRESS

Oracle Enterprise Manager

Configuring Oracle Intelligent Agent for Oracle SNMP

Oracle Advanced Security

A Tuning for Oracle9i on AIX

Memory and Paging

Controlling Buffer-Cache Paging Activity

Tuning the AIX File Buffer Cache

Allocating Sufficient Paging Space

Controlling Paging

Setting the Database Block Size

Tuning the Log Archive Buffers

Tuning the SGA Size

I/O Buffers and SQL*Loader

Disk I/O Issues

AIX Logical Volume Manager

Using Journaled File Systems Compared to Using Raw Partitions

Using Asynchronous I/O

I/O Slaves

Using the DB_FILE_MULTIBLOCK_READ_COUNT Parameter

Using RAID Capabilities

Using Write Behind

Tuning Sequential Read Ahead

Tuning Disk I/O Pacing

Disk Geometry Considerations

Minimizing Remote I/O Operations

Do Not Use VSD Cache Buffers

CPU Scheduling and Process Priorities

Changing Process Running Time Slice

Using Processor Binding on SMP Systems

UDP Tuning

Backing Up Raw Devices

B Tuning for Oracle9i on HP

HP SCHED_NOAGE Scheduling Policy

Enabling SCHED_NOAGE for Oracle9i

Lightweight Timer Implementation

Asynchronous I/O

MLOCK Privilege

Implementing Asynchronous I/O

Verifying Asynchronous I/O

Asynchronous Flag in SGA

Disabling Data Prefetch on the HP Superdome for Oracle

C Tuning for Oracle9i on Tru64

Gathering Database Statistics on Tru64

Oracle9i Real Application Clusters on Tru64

Reliable Data Gram

TRU64_IPC_NET Initialization Parameter

Tuning Asynchronous I/O

Operating System Parameters

Direct I/O Support and Concurrent Direct I/O Support Enabled in Oracle9i for Tru64

Enabling Access to the Real Time Clock

Setting Up Raw Devices

Spike Optimization Tool

Using Spike

Enabling Oracle9i Directed Placement Optimizations

Requirements to Run the Directed Placement Optimizations

Enabling Oracle Directed Placement Optimizations

Disabling Oracle Directed Placement Optimizations

Using Oracle Directed Placement Optimizations

Oracle Initialization Parameters

Tru64 UNIX System Parameters

Process Affinity to RADs

D Running Oracle interMedia, Oracle Text, and Oracle Spatial Demonstrations

Oracle9i interMedia

interMedia Annotator

Locator

Clipboard

Oracle Text

Oracle9i Spatial

E Oracle Cluster Management Software for Linux

Overview

Watchdog Daemon

Node Monitor

Cluster Manager

Starting OCMS

Starting the Watchdog Daemon

Configuring the Node Monitor

Starting the Node Monitor

Starting the Cluster Manager

Configuring Timing for Cluster Reconfiguration

Watchdog Daemon and Cluster Manager Starting Options

Known Issues and Restrictions

F Optimal Flexible Architecture

Optimal Flexible Architecture

Characteristics of an OFA-Compliant Database

Optimal Flexible Architecture Implemented on UNIX

Mount Points

Naming Directories

Naming Database Files

Separate Segments With Different Requirements

Naming Tablespaces

Exploiting the OFA Structure for Oracle Files

OFA File Mapping

File Mapping for a Multiple-Instance OFA Database

Directory Structure

Index