Oracle9iAdministrator’s ReferenceRelease 1 (9.0.1) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000Series HP-UX, Linux Intel and Sun SPARC SolarisMay 2001Part No. A90347-02

Oracle9i Administrator’s Reference Release 1 (9.0.1) for UNIX Systems: AIX-Based Systems, CompaqTru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC SolarisPart No. A90347-02Copyright 1996, 2001, Oracle Corporation. All rights reserved.Primary Author: Emily Murphy. Editor: Kevin Flood.Contributors: Oanh Duong, Kevin Flood, Caroline Johnston, Leslie Marder, Tom Leah-Martin, DaraO’Murchu, Doug Williams, Joycelyn Wee, and Paul Wright.Technical Contributors: Corinne Arne, Harish Akali, Tanya Bagerman, Walter Battistella, Kevin Benson,Jamie Brown, Jack Cai, Dimas Chbane, Chelsea Christenson, Toby Close, Caragh Cooney, JonathanCreighton, Dan Damon, Saheli Dey, Mark Fallon, Nicole Haba, Jackie Gosselin, Richard Gulledge, BryanHiggs, Amit Khare, Sana Karam, Ravi Kuntamukkala, Terri Landry, Stephen Lee, Bennet Leve, Allen Lui,Miranda Nash, Anu Natarajan, Michele Resta, Sanhita Sarkar, Brandie Smith, Ajay Srivastava, DeborahSteiner, Binoy Sukumaran, Karen Thrift, Sandy Vella, Petros Xides, and Huiping Wang.The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherentlydangerous applications. It shall be the licensee’s responsibility to take all appropriate fail-safe, backup,redundancy, and other measures to ensure the safe use of such applications if the Programs are used forsuch purposes, and Oracle Corporation disclaims liability for any damages caused by such use of thePrograms.The Programs (which include both the software and documentation) contain proprietary information ofOracle Corporation; they are provided under a license agreement containing restrictions on use anddisclosure and are also protected by copyright, patent, and other intellectual and industrial propertylaws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.The information contained in this document is subject to change without notice. If you find any problemsin the documentation, please report them to us in writing. Oracle Corporation does not warrant that thisdocument is error free. Except as may be expressly permitted in your license agreement for thesePrograms, no part of these Programs may be reproduced or transmitted in any form or by any means,electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.If the Programs are delivered to the U.S. Government or anyone licensing or using the Programs onbehalf of the U.S. Government, the following notice is applicable:Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercialcomputer software" and use, duplication, and disclosure of the Programs, including documentation,shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.Otherwise, programs delivered subject to the Federal Acquisition Regulations are "restricted computersoftware" and use, duplication, and disclosure of the programs shall be subject to the restrictions in FAR52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500Oracle Parkway, Redwood City, CA 94065.Oracle is a registered trademark, and ConText, Oracle7, Oracle8, Oracle8i, Oracle9i, Oracle ApplicationsInterconnect, Oracle Call Interface, Oracle Data Migration Assistant, Oracle Database ConfigurationAssistant, Oracle Dynamic Services, Oracle Enterprise Manager, Oracle Intelligent Agent, OracleinterMedia, Oracle Internet File System, OracleJSP, Oracle Label Security, Oracle Locale Builder,OracleMetaLink, Oracle Names, Oracle Net, Oracle Objects, Oracle OLAP Services, OraclePersonalization, Oracle Programmer, Oracle Real Application Clusters, Oracle Spatial, Oracle Text,Oracle Wallet Manager, Oracle Workflow, PL/SQL, Pro*C/C , Pro*COBOL, Pro*FORTRAN, andSQL*Plus are trademarks or registered trademarks of Oracle Corporation. Other names may betrademarks of their respective owners.

ContentsSend Us Your Comments . xiPreface. xiii1Administering Oracle9iOverview .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 21-141-151-161-161-161-181-181-18iii

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 .2Tuning for Oracle9i on UNIXImportance 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 2-122-132-142-142-14

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.3Administering SQL*PlusAdministering 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 g Oracle Precompilers and the Oracle Call InterfaceOverview of Oracle Precompilers.Precompiler Configuration Files .Relinking Precompiler Executables .Precompiler README Files.Issues Common to All Precompilers .4-24-24-24-34-4v

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 .5Configuring Oracle Net ServicesCore 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 25-25-45-45-55-65-6

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.5-75-75-85-95-95-11A Tuning for Oracle9i on AIXMemory 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 A-22vii

BTuning for Oracle9i on HPHP 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.B-2B-2B-3B-3B-4B-4B-6B-7B-7C Tuning for Oracle9i on Tru64Gathering Database Statistics on Tru64. C-2Oracle9i Real Application Clusters on Tru64 . C-3Reliable Data Gram. C-3TRU64 IPC NET Initialization Parameter . C-5Tuning Asynchronous I/O . C-5Operating System Parameters. C-6Direct I/O Support and Concurrent Direct I/O Support Enabled in Oracle9i for Tru64. C-7Enabling Access to the Real Time Clock . C-9Setting Up Raw Devices . C-10Spike Optimization Tool . C-12Using Spike . C-13Enabling Oracle9i Directed Placement Optimizations . C-18Requirements to Run the Directed Placement Optimizations . C-19Enabling Oracle Directed Placement Optimizations . C-19Disabling Oracle Directed Placement Optimizations. C-20Using Oracle Directed Placement Optimizations . C-20Oracle Initialization Parameters . C-20Tru64 UNIX System Parameters. C-21Process Affinity to RADs . C-21viii

D Running Oracle interMedia, Oracle Text, and Oracle Spatial DemonstrationsOracle9i interMedia.interMedia Annotator .Locator.Clipboard .Oracle Text.Oracle9i Spatial .ED-2D-3D-3D-3D-4D-4Oracle Cluster Management Software for LinuxOverview .Watchdog Daemon.Node Monitor .Cluster Manager.Starting OCMS .Starting the Watchdog Daemon .Configuring the Node Monitor .Starting the Node Monitor .Starting the Cluster Manager.

Assistant, Oracle Dynamic Services, Oracle Enterprise Manager, Oracle Intelligent Agent, Oracle inter Media, Oracle Internet File System, OracleJSP, Oracle Label Security, Oracle Locale Builder, Oracle MetaLink , Oracle Names, Oracle Net, Oracle Objects,