Oracle Database Administrator’s Reference for …

1 Oracle DatabaseAdministrator s Reference 11g Release 2 ( ) for linux and UNIX-Based Operating Sys-temsE10839-07 June 2010 Oracle Database administrator 's Reference , 11g Release 2 ( ) for linux and UNIX-Based Operating Systems E10839-07 Copyright 2006, 2010, Oracle and/or its affiliates. All rights Author: Namrata Bhakthavatsalam, Reema KhoslaContributing Authors: Kevin Flood, Pat Huey, Clara Jaeckel, Emily Murphy, Terri Winters, Prakash JashnaniContributors: Subhranshu Banerjee, Mark Bauer, Robert Chang, Jonathan Creighton, Sudip Datta, Thirumaleshwara Hasandka, Joel Kallman, George Kotsovolos, Richard Long, Rolly Lv, Padmanabhan Manavazhi, Matthew Mckerley, Krishna Mohan, Rajendra Pingte, Hanlin Qian, Janelle Simmons, Roy Swonger, Douglas Williams, Joseph Therrattil Koonen, Binoy Sukumaran, and Sumanta software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws.

2 Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in this software or related documentation is delivered to the Government or anyone licensing it on behalf of the Government, the following notice is GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations.

3 As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR , Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous is a registered trademark of Oracle Corporation and/or its affiliates.

4 Other names may be trademarks of their respective software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or ixDocumentation Accessibility .. ixRelated Documentation .. xConventions .. xiiCommand Syntax .. xiiTerminology .. xiiAccessing xiii1 Administering Oracle Database 1-1 Environment 1-1 Oracle Database Environment Variables .. 1-2 UNIX Environment Variables .. 1-4 Setting a Common Environment .. 1-5 Setting the System Time Zone .. 1-6 Initialization 1-6DB_BLOCK_SIZE Initialization Parameter .. 1-7 ASM_DISKSTRING Initialization Parameter.

5 1-7 LOG_ARCHIVE_DEST_n Initialization Parameter .. 1-7 DISK_ASYNCH_IO Initialization Parameter (HP-UX) .. 1-7 Operating System Accounts and 1-8 Creating Additional Operating System 1-8 Configuring the Accounts of Oracle 1-8 Using Trace 1-82 Stopping and Starting Oracle SoftwareStopping and Starting Oracle 2-1 Stopping and Starting Oracle Database and Automatic Storage Management Instances .. 2-1 Stopping an Oracle Database or Automatic Storage Management Instance .. 2-2 Restarting an Oracle Database or Automatic Storage Management Instance .. 2-3 Stopping and Starting Oracle 2-3 Stopping and Starting Oracle Enterprise Manager Database Control .. 2-3 Stopping and Starting Oracle Management Agent .. 2-4 Automating Shutdown and 2-5ivAutomating Database Startup and Shutdown on Other Operating Systems .. 2-53 Configuring Oracle DatabaseUsing Configuration Assistants as Standalone 3-1 Using Oracle Net Configuration 3-1 Using Oracle Database Upgrade Assistant.

6 3-2 Using Oracle Database Configuration Assistant .. 3-2 Configuring New or Upgraded 3-2 Relinking 3-34 Administering SQL*PlusAdministering Command-Line SQL* 4-1 Using Setup Files .. 4-1 Using the PRODUCT_USER_PROFILE Table .. 4-2 Using Oracle Database Sample Schemas .. 4-2 Installing and Removing SQL*Plus Command-Line Help .. 4-2 Installing SQL*Plus Command-Line Help .. 4-2 Removing SQL*Plus Command-Line Help .. 4-3 Using Command-Line SQL* 4-3 Using a System Editor from SQL* 4-3 Running Operating System Commands from SQL* 4-4 Interrupting SQL*Plus .. 4-4 Using the SPOOL 4-4 SQL*Plus 4-4 Resizing Windows .. 4-5 Return Codes .. 4-5 Hiding the Password .. 4-55 Configuring Oracle Net ServicesLocating Oracle Net Services Configuration 5-1 Adapters 5-2 Oracle Protocol 5-3 IPC Protocol Support .. 5-3 TCP/IP Protocol 5-3 TCP/IP with Secure Sockets Layer Protocol Support .. 5-4 Setting Up the Listener for TCP/IP or TCP/IP with Secure Sockets 5-4 Oracle Advanced 5-56 Using Oracle Precompilers and the Oracle Call InterfaceOverview of Oracle 6-1 Precompiler Configuration Files.

7 6-2 Relinking Precompiler Executables .. 6-2 Precompiler README Files .. 6-3 Issues Common to All Precompilers .. 6-3 Static and Dynamic Linking .. 6-3 Client Shared and Static Libraries .. 6-3vBit-Length Support for Client 6-4 Pro*C/C++ 6-6 Pro*C/C++ Demonstration Programs .. 6-6 Pro*C/C++ User Programs .. 6-7 Pro*COBOL 6-8 Pro*COBOL Environment Variables .. 6-8 Micro Focus Server Express COBOL 6-9 Acucorp ACUCOBOL-GT COBOL 6-9 Pro*COBOL Oracle Runtime System .. 6-10 Pro*COBOL Demonstration Programs .. 6-11 Pro*COBOL User Programs .. 6-11 FORMAT Precompiler Option .. 6-12 Pro*FORTRAN 6-12 Pro*FORTRAN Demonstration 6-13 Pro*FORTRAN User Programs .. 6-13 SQL*Module for 6-14 SQL*Module for Ada Demonstration Programs .. 6-14 SQL*Module for Ada User Programs .. 6-15 OCI and 6-16 OCI and OCCI Demonstration 6-16 OCI and OCCI User Programs .. 6-16 Oracle JDBC/OCI Programs with a 64-Bit 6-17 Custom Make 6-17 Correcting Undefined 6-18 Multithreaded 6-19 Using Signal 6-19XA 6-207 SQL*Loader and PL/SQL DemonstrationsSQL*Loader 7-1PL/SQL 7-1 Calling 32-Bit External Procedures from 64-Bit Oracle Database 7-48 Tuning Oracle DatabaseImportance of 8-1 Operating System 8-2sar.

8 8-2iostat .. 8-3swap, swapinfo, swapon, or lsps .. 8-3 AIX Tools .. 8-4 Base Operation System Tools .. 8-4 Performance Toolbox .. 8-4 System Management Interface Tool .. 8-5HP-UX Tools .. 8-5 linux 8-6viSolaris Tools .. 8-6 Tuning Memory 8-7 Allocating Sufficient Swap Space .. 8-7 Controlling Paging .. 8-8 Adjusting Oracle Block 8-8 Allocating Memory Resource .. 8-9 Tuning Disk 8-9 Using Automatic Storage 8-9 Choosing the Appropriate File System Type .. 8-10 Monitoring Disk 8-10 Monitoring Disk Performance on Other Operating Systems .. 8-10 Using Disk Resync to Monitor Automatic Storage Management Disk Group .. 8-11 System Global 8-11 Determining the Size of the SGA .. 8-12 Shared Memory on AIX .. 8-13 Tuning the Operating System Buffer 8-14A Administering Oracle Database on AIXM emory and A-1 Controlling Buffer-Cache Paging Activity .. A-1 Tuning the AIX File Buffer A-2 Allocating Sufficient Paging Space.

9 A-3 Controlling Paging .. A-3 Setting the Database Block A-4 Tuning the Log Archive A-4 Input-Output Buffers and SQL*Loader .. A-5 Disk Input-Output A-5 AIX Logical Volume A-5 Using Journaled File Systems Compared to Raw Logical A-6 Using Asynchronous Input-Output .. A-9 Input-Output Slaves .. A-10 Using the DB_FILE_MULTIBLOCK_READ_COUNT Parameter .. A-11 Using Write A-11 Tuning Sequential Read Ahead .. A-12 Tuning Disk Input-Output A-12 Resilvering with Oracle A-13 CPU Scheduling and Process A-13 Changing Process Running Time Slice .. A-14 Using Processor Binding on SMP A-14 Setting the AIXTHREAD_SCOPE Environment A-14 Network Information Service external naming A-15 Simultaneous Multithreading on AIX A-15 Configuring IBM JSSE Provider with Oracle JDBC Thin A-15B Administering Oracle Database on HP-UXHP-UX Shared Memory Segments for an Oracle B-1HP-UX SCHED_NOAGE Scheduling B-2viiEnabling SCHED_NOAGE for Oracle Database .

10 B-2 Lightweight Timer B-3 Asynchronous B-3 MLOCK B-4 Implementing Asynchronous B-4 Verifying Asynchronous Input-Output .. B-6 Verifying That HP-UX Asynchronous Driver is Configured for Oracle Database .. B-6 Verifying that Oracle Database is Using Asynchronous B-7 Asynchronous Flag in SGA .. B-7 Large Memory Allocations and Oracle Database B-8 Default Large Virtual Memory Page B-8 Tuning B-9 Tunable Base Page Size .. B-9 CPU_COUNT Initialization Parameter and HP-UX Dynamic Processor B-9 Network Information Service external naming B-10 Activating and Setting Expanded Host Names and Node B-10C Administering Oracle Database on LinuxExtended Buffer Cache C-1 Using hugetlbfs on SUSE linux Enterprise Server 10 or Red Hat Enterprise linux C-3 Increasing SGA Address C-3 Asynchronous Input-Output C-4 Simultaneous C-5 Allocating Shared C-5 Database Migration from 32-Bit linux to 64-Bit C-6 Online Backup of Database With RMAN.