Oracle 8i Admin Guide

630 Pages·2016·4.09 MB·English

Oracle8i
Administrator’s Guide
Release 2 (8.1.6)
December 1999
Part No. A76956-01
Administrator’s Guide, Release 2 (8.1.6)
Part No. A76956-01
Copyright © 1996, 1999, Oracle Corporation. All rights reserved.
Primary Authors: Ruth Baylis, Joyce Fee
Contributors: Alex Tsukerman, Andre Kruglikov, Ann Rhee, Ashwini Surpur, Bhaskar Himatsingka,
Harvey Eneman, Jags Srinivasan, Lois Price, Robert Jenkins, Sophia Yeung, Vinay Srihari, Wei Huang,
Jonathan Klein, Mike Hartstein, Bill Lee, Diana Lorentz, Lance Ashdown, Phil Locke, Ekrem Soylemez,
Connie Dialaris, Steven Wertheimer, Val Kane, Mary Rhodes, Archna Kalra, Nina Lewis, Mary Ann
Davidson, Sujatha Muthulingam, Carolyn Gray
Graphic Designer: Valarie Moore
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, 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 on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer 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 computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous 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 for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Net8, Oracle Call Interface, Oracle7, Oracle8, Oracle8i, Oracle
Designer, Oracle Enterprise Manager, Oracle Forms, Oracle Parallel Server, Oracle Server Manager,
Oracle SQL*Loader, LogMiner, PL/SQL, Pro*C, SQL*Net and SQL*Plus, and Trusted Oracle are
trademarks or registered trademarks of Oracle Corporation. All other company or product names
mentioned are used for identification purposes only and may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................. xxi
Preface........................................................................................................................................................ xxiii
Part I Basic Database Administration
1 The Oracle Database Administrator
Types of Oracle Users......................................................................................................................... 1-2
Database Administrators............................................................................................................. 1-2
Security Officers............................................................................................................................ 1-3
Application Developers............................................................................................................... 1-3
Application Administrators........................................................................................................ 1-3
Database Users.............................................................................................................................. 1-3
Network Administrators............................................................................................................. 1-4
Database Administrator Security and Privileges......................................................................... 1-4
The Database Administrator’s Operating System Account................................................... 1-4
Database Administrator Usernames.......................................................................................... 1-4
The DBA Role................................................................................................................................ 1-6
Database Administrator Authentication........................................................................................ 1-6
Selecting an Authentication Method......................................................................................... 1-6
Using Operating System Authentication.................................................................................. 1-7
OSOPER and OSDBA................................................................................................................... 1-8
Using an Authentication Password File.................................................................................... 1-9
Password File Administration.......................................................................................................... 1-9
iii
Using ORAPWD......................................................................................................................... 1-10
Setting REMOTE_LOGIN_ PASSWORDFILE........................................................................ 1-11
Adding Users to a Password File............................................................................................. 1-12
Connecting with Administrator Privileges............................................................................. 1-14
Maintaining a Password File..................................................................................................... 1-15
Database Administrator Utilities................................................................................................... 1-17
SQL*Loader................................................................................................................................. 1-17
Export and Import...................................................................................................................... 1-17
Priorities of a Database Administrator......................................................................................... 1-17
Step 1: Install the Oracle Software............................................................................................ 1-18
Step 2: Evaluate the Database Server Hardware.................................................................... 1-18
Step 3: Plan the Database........................................................................................................... 1-19
Step 4: Create and Open the Database..................................................................................... 1-20
Step 5: Implement the Database Design.................................................................................. 1-20
Step 6: Back Up the Database.................................................................................................... 1-20
Step 7: Enroll System Users....................................................................................................... 1-20
Step 8: Tune Database Performance......................................................................................... 1-21
Identifying Your Oracle Database Software Release................................................................. 1-21
Release Number Format............................................................................................................ 1-21
Checking Your Current Release Number............................................................................... 1-22
2 Creating an Oracle Database
Considerations Before Creating a Database.................................................................................. 2-2
Planning for Database Creation.................................................................................................. 2-2
Creation Prerequisites.................................................................................................................. 2-3
Deciding How to Create an Oracle Database........................................................................... 2-3
The Oracle Database Configuration Assistant (DBCA).............................................................. 2-4
Advantages of Using DBCA....................................................................................................... 2-5
DBCA Modes for Database Creation......................................................................................... 2-5
Identifying Your Database Environment.................................................................................. 2-6
Selecting the Database Creation Method.................................................................................. 2-6
Manually Creating an Oracle Database.......................................................................................... 2-9
Steps for Creating an Oracle Database...................................................................................... 2-9
Examining a Database Creation Script.................................................................................... 2-11
Troubleshooting Database Creation........................................................................................ 2-16
iv
Dropping a Database................................................................................................................. 2-16
Installation Parameters.................................................................................................................... 2-16
A Sample Initialization File....................................................................................................... 2-17
Editing the Initialization Parameter File................................................................................. 2-19
Considerations After Creating a Database.................................................................................. 2-24
Initial Tuning Guidelines............................................................................................................... 2-25
Allocating Rollback Segments.................................................................................................. 2-25
Choosing the Number of DB_BLOCK_LRU_LATCHES...................................................... 2-26
Distributing I/O.......................................................................................................................... 2-26
3 Starting Up and Shutting Down
Starting Up a Database...................................................................................................................... 3-2
Preparing to Start an Instance..................................................................................................... 3-2
Options for Starting Up a Database........................................................................................... 3-2
Starting an Instance: Scenarios................................................................................................... 3-4
Altering Database Availability........................................................................................................ 3-8
Mounting a Database to an Instance.......................................................................................... 3-8
Opening a Closed Database........................................................................................................ 3-8
Opening a Database in Read-Only Mode................................................................................. 3-9
Restricting Access to an Open Database................................................................................... 3-9
Shutting Down a Database............................................................................................................. 3-10
Shutting Down with the NORMAL Option........................................................................... 3-11
Shutting Down with the IMMEDIATE Option...................................................................... 3-12
Shutting Down with the TRANSACTIONAL Option.......................................................... 3-12
Shutting Down with the ABORT Option................................................................................ 3-13
Suspending and Resuming a Database........................................................................................ 3-13
Using Initialization Parameter Files............................................................................................. 3-15
The Sample Initialization Parameter File................................................................................ 3-15
The Number of Initialization Parameter Files........................................................................ 3-16
The Location of the Initialization Parameter File in Distributed Environments............... 3-16
Part II Oracle Server Configuration
v
4 Managing Oracle Processes
Server Processes.................................................................................................................................. 4-2
Dedicated Server Processes......................................................................................................... 4-2
Multi-Threaded Server Processes............................................................................................... 4-3
Configuring Oracle for the Multi-Threaded Server..................................................................... 4-5
Initialization Parameters for MTS.............................................................................................. 4-5
MTS_DISPATCHERS: Setting the Initial Number of Dispatchers........................................ 4-6
MTS_SERVERS: Setting the Initial Number of Shared Servers............................................. 4-7
Modifying Dispatcher and Server Processes............................................................................ 4-8
Monitoring MTS.......................................................................................................................... 4-10
Tracking Oracle Background Processes........................................................................................ 4-11
What are the Oracle Background Processes............................................................................ 4-11
Monitoring the Processes of an Oracle Instance..................................................................... 4-14
Trace Files, the Alert Log, and Background Processes.......................................................... 4-15
Managing Processes for the Parallel Query Option................................................................... 4-17
Managing the Query Servers.................................................................................................... 4-17
Variations in the Number of Query Server Processes........................................................... 4-18
Managing Processes for External Procedures.............................................................................. 4-18
Setting up an Environment for Calling External Routines................................................... 4-19
Sample Entry intnsnames.ora.................................................................................................. 4-19
Sample Entry inlistener.ora..................................................................................................... 4-20
Terminating Sessions....................................................................................................................... 4-20
Identifying Which Session to Terminate................................................................................. 4-21
Terminating an Active Session................................................................................................. 4-22
Terminating an Inactive Session............................................................................................... 4-22
5 Managing Control Files
What is a Control File?....................................................................................................................... 5-2
Guidelines for Control Files............................................................................................................. 5-2
Name Control Files....................................................................................................................... 5-2
Multiplex Control Files on Different Disks............................................................................... 5-3
Place Control Files Appropriately.............................................................................................. 5-3
Manage the Size of Control Files................................................................................................ 5-3
Creating Control Files........................................................................................................................ 5-4
Creating Initial Control Files....................................................................................................... 5-4
vi
Creating Additional Control File Copies, and Renaming and Relocating Control Files... 5-5
New Control Files......................................................................................................................... 5-5
Creating New Control Files........................................................................................................ 5-6
Troubleshooting After Creating Control Files.............................................................................. 5-8
Checking for Missing or Extra Files........................................................................................... 5-8
Handling Errors During CREATE CONTROLFILE................................................................ 5-9
Dropping Control Files...................................................................................................................... 5-9
6 Managing the Online Redo Log
What Is the Online Redo Log?......................................................................................................... 6-2
Redo Threads................................................................................................................................ 6-2
Online Redo Log Contents.......................................................................................................... 6-2
How Oracle Writes to the Online Redo Log............................................................................. 6-3
Planning the Online Redo Log......................................................................................................... 6-5
Multiplexing Online Redo Log Files.......................................................................................... 6-5
Placing Online Redo Log Members on Different Disks.......................................................... 6-9
Setting the Size of Online Redo Log Members......................................................................... 6-9
Choosing the Number of Online Redo Log Files..................................................................... 6-9
Creating Online Redo Log Groups and Members..................................................................... 6-11
Creating Online Redo Log Groups.......................................................................................... 6-11
Creating Online Redo Log Members....................................................................................... 6-11
Renaming and Relocating Online Redo Log Members............................................................ 6-12
Dropping Online Redo Log Groups and Members................................................................... 6-14
Dropping Log Groups................................................................................................................ 6-14
Dropping Online Redo Log Members..................................................................................... 6-15
Forcing Log Switches....................................................................................................................... 6-16
Verifying Blocks in Redo Log Files............................................................................................... 6-17
Clearing an Online Redo Log File................................................................................................. 6-17
Restrictions.................................................................................................................................. 6-17
Listing Information about the Online Redo Log........................................................................ 6-18
7 Managing Archived Redo Logs
What Is the Archived Redo Log?..................................................................................................... 7-2
Choosing Between NOARCHIVELOG and ARCHIVELOG Mode......................................... 7-3
Running a Database in NOARCHIVELOG Mode................................................................... 7-3
vii
Running a Database in ARCHIVELOG Mode.......................................................................... 7-4
Controlling the Archiving Mode..................................................................................................... 7-6
Setting the Initial Database Archiving Mode........................................................................... 7-6
Changing the Database Archiving Mode.................................................................................. 7-6
Enabling Automatic Archiving................................................................................................... 7-7
Disabling Automatic Archiving.................................................................................................. 7-8
Performing Manual Archiving................................................................................................... 7-9
Specifying the Archive Destination.............................................................................................. 7-10
Specifying Archive Destinations.............................................................................................. 7-10
Understanding Archive Destination States............................................................................ 7-12
Specifying the Mode of Log Transmission.................................................................................. 7-14
Normal Transmission Mode..................................................................................................... 7-14
Standby Transmission Mode..................................................................................................... 7-14
Managing Archive Destination Failure........................................................................................ 7-16
Specifying the Minimum Number of Successful Destinations............................................ 7-16
Re-Archiving to a Failed Destination....................................................................................... 7-19
Tuning Archive Performance.......................................................................................................... 7-19
Specifying Multiple ARCn Processes....................................................................................... 7-20
Adjusting Archive Buffer Parameters...................................................................................... 7-21
Displaying Archived Redo Log Information............................................................................... 7-22
Fixed Views................................................................................................................................. 7-23
The ARCHIVE LOG LIST SQL Statement............................................................................... 7-24
Controlling Trace Output Generated by the Archivelog Process............................................ 7-25
Using LogMiner to Analyze Online and Archived Redo Logs................................................ 7-26
How Can You Use LogMiner?.................................................................................................. 7-26
Restrictions................................................................................................................................... 7-27
Creating a Dictionary File.......................................................................................................... 7-28
Specifying Redo Logs for Analysis.......................................................................................... 7-30
Using LogMiner.......................................................................................................................... 7-30
Using LogMiner: Scenarios....................................................................................................... 7-32
8 Managing Job Queues
SNP Background Processes............................................................................................................... 8-2
Multiple SNP processes............................................................................................................... 8-2
Starting up SNP processes........................................................................................................... 8-3
viii
Managing Job Queues....................................................................................................................... 8-3
The DBMS_JOB Package.............................................................................................................. 8-3
Submitting a Job to the Job Queue............................................................................................. 8-4
How Jobs Execute......................................................................................................................... 8-9
Removing a Job from the Job Queue....................................................................................... 8-10
Altering a Job............................................................................................................................... 8-11
Broken Jobs.................................................................................................................................. 8-12
Forcing a Job to Execute............................................................................................................. 8-13
Terminating a Job....................................................................................................................... 8-14
Viewing Job Queue Information................................................................................................... 8-14
Part III Database Storage
9 Managing Tablespaces
Guidelines for Managing Tablespaces........................................................................................... 9-2
Use Multiple Tablespaces............................................................................................................ 9-2
Specify Tablespace Storage Parameters.................................................................................... 9-3
Assign Tablespace Quotas to Users........................................................................................... 9-3
Creating Tablespaces.......................................................................................................................... 9-4
Dictionary-Managed Tablespaces.............................................................................................. 9-5
Locally Managed Tablespaces.................................................................................................... 9-6
Temporary Tablespaces............................................................................................................... 9-8
Managing Tablespace Allocation................................................................................................... 9-10
Storage Parameters in Locally Managed Tablespaces.......................................................... 9-11
Storage Parameters for Dictionary-Managed Tablespaces................................................... 9-11
Coalescing Free Space in Dictionary-Managed Tablespaces............................................... 9-12
Altering Tablespace Availability................................................................................................... 9-15
Taking Tablespaces Offline....................................................................................................... 9-15
Bringing Tablespaces Online.................................................................................................... 9-17
Read-Only Tablespaces.................................................................................................................... 9-17
Making a Tablespace Read-Only.............................................................................................. 9-18
Making a Read-Only Tablespace Writable............................................................................. 9-20
Creating a Read-Only Tablespace on a WORM Device........................................................ 9-21
Delaying the Opening of Datafiles in Read Only Tablespaces............................................ 9-21
Dropping Tablespaces...................................................................................................................... 9-22
ix
Using the DBMS_SPACE_ADMIN Package............................................................................... 9-23
Scenario 1..................................................................................................................................... 9-25
Scenario 2..................................................................................................................................... 9-25
Scenario 3..................................................................................................................................... 9-25
Scenario 4..................................................................................................................................... 9-26
Scenario 5..................................................................................................................................... 9-26
Transporting Tablespaces Between Databases............................................................................ 9-26
Introduction to Transportable Tablespaces............................................................................ 9-27
Limitations................................................................................................................................... 9-27
Procedure for Transporting Tablespaces Between Databases............................................. 9-28
Object Behaviors......................................................................................................................... 9-33
Using Transportable Tablespaces............................................................................................. 9-35
Viewing Information About Tablespaces.................................................................................... 9-39
10 Managing Datafiles
Guidelines for Managing Datafiles............................................................................................... 10-2
Determine the Number of Datafiles......................................................................................... 10-2
Set the Size of Datafiles.............................................................................................................. 10-4
Place Datafiles Appropriately................................................................................................... 10-4
Store Datafiles Separate From Redo Log Files........................................................................ 10-4
Creating and Adding Datafiles to a Tablespace.......................................................................... 10-5
Changing a Datafile’s Size............................................................................................................... 10-5
Enabling and Disabling Automatic Extension for a Datafile............................................... 10-5
Manually Resizing a Datafile.................................................................................................... 10-6
Altering Datafile Availability......................................................................................................... 10-7
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode............................... 10-8
Taking Datafiles Offline in NOARCHIVELOG Mode.......................................................... 10-8
Renaming and Relocating Datafiles.............................................................................................. 10-9
Renaming and Relocating Datafiles for a Single Tablespace............................................... 10-9
Renaming and Relocating Datafiles for Multiple Tablespaces.......................................... 10-11
Verifying Data Blocks in Datafiles.............................................................................................. 10-12
Viewing Information About Datafiles........................................................................................ 10-13
11 Managing Rollback Segments
Guidelines for Managing Rollback Segments............................................................................ 11-2
x