Oracle9i Database Administrator's Guide

168 Pages·2001·1.668 MB·English

by  Kelly H., Kennedy M., Slatlery H.

Oracle9i
Database Administrator’s Guide
Release 1 (9.0.1) for Windows
June 2001
Part No. A90164-01
Oracle9i Database Administrator’s Guide, Release 1 (9.0.1) for Windows
Part No. A90164-01
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Author: Herbert Kelly III, Mark Kennedy, and Helen Slattery.
Contributors: Server Technologies Division - NT Development and Robert Farrington of Oracle
Support Services.
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.
The information contained in this document is subject to change without notice. If you find any problems
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 OracleMetaLink, Oracle Store, Oracle7, Oracle8, Oracle8i, Oracle9i,
PL/SQL, Pro*C/C++, Pro*COBOL, and SQL*Plus are trademarks or registered trademarks of Oracle
Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................... ix
Preface............................................................................................................................................................ xi
Audience................................................................................................................................................ xii
Organization.......................................................................................................................................... xii
Related Documentation....................................................................................................................... xiii
Conventions.......................................................................................................................................... xiv
Documentation Accessibility............................................................................................................. xix
What’s New in Oracle9i for Windows........................................................................................ xxi
Windows Integration......................................................................................................................... xxii
Oracle Database Configuration Assistant Improvements............................................................ xxii
Oracle Internet Directory Administration Improvements............................................................ xxii
Using Oracle9i on Windows 2000.................................................................................................... xxii
Oracle9i Desupported and Deprecated Features........................................................................... xxiii
1 Postinstallation Database Creation
Before You Create a Database........................................................................................................... 1-2
Naming Conventions for Oracle Databases............................................................................. 1-2
Accessing Data Files and Log Files on Remote Computers................................................... 1-3
Creating a Database Using Tools..................................................................................................... 1-4
Using Oracle Database Configuration Assistant.......................................................................... 1-4
Create a Database......................................................................................................................... 1-5
Importing Sample Schemas................................................................................................. 1-8
iii
Delete a Database........................................................................................................................ 1-10
Creating a Database Manually....................................................................................................... 1-10
How to Create a Database......................................................................................................... 1-11
Creating Directories.................................................................................................................... 1-11
Exporting an Existing Database................................................................................................ 1-12
Deleting Database Files.............................................................................................................. 1-13
Modifying the init.ora file.......................................................................................................... 1-14
Creating and Starting an Oracle Service.................................................................................. 1-16
Putting the CREATE DATABASE Statement in a Script...................................................... 1-16
Creating a Database.................................................................................................................... 1-17
Importing a Database................................................................................................................. 1-18
Updating the ORACLE_SID in the Registry........................................................................... 1-19
Backing Up the New Database................................................................................................. 1-21
Using the ORADIM Utility to Administer an Oracle Instance............................................... 1-22
Creating an Instance................................................................................................................... 1-23
Starting an Instance.................................................................................................................... 1-25
Stopping an Instance.................................................................................................................. 1-25
Modifying an Instance............................................................................................................... 1-26
Deleting an Instance................................................................................................................... 1-27
2 Postinstallation Configuration Tasks
Oracle interMedia............................................................................................................................... 2-2
Audio.............................................................................................................................................. 2-2
Video............................................................................................................................................... 2-2
Image.............................................................................................................................................. 2-2
Locator............................................................................................................................................ 2-2
Oracle Text............................................................................................................................................ 2-2
Oracle interMedia Audio, Video, Image, and Locator Configuration Responsibilities...... 2-3
Configuring Oracle interMedia Audio, Video, Image, and Locator...................................... 2-3
Configuring Oracle interMedia, Audio, Video, Image, and Locator Demos....................... 2-4
Oracle Text Configuration Responsibilities.............................................................................. 2-4
Oracle Spatial....................................................................................................................................... 2-7
Configuring Oracle Spatial.......................................................................................................... 2-8
Shared Server Support....................................................................................................................... 2-9
Enabling Shared Server Support for IIOP Clients.................................................................. 2-11
iv
Enabling Shared Server Support for Two-Task Oracle Net Clients.................................... 2-11
Disabling Shared Server Support for Two-Task Oracle Net Clients................................... 2-12
Advanced Replication...................................................................................................................... 2-13
Task 1: Checking Tablespace and Rollback Segment Requirements.................................. 2-13
Task 2: Checking Initialization Parameters............................................................................ 2-14
Task 3: Monitoring Data Dictionary Tables............................................................................ 2-14
3 Administering a Database
Managing Oracle Services................................................................................................................. 3-2
Oracle Service Naming Conventions for Multiple Oracle Homes........................................ 3-2
Available Oracle Services............................................................................................................ 3-4
Starting Oracle Services............................................................................................................... 3-5
Stopping Oracle Services............................................................................................................. 3-6
Auto-starting Oracle Services..................................................................................................... 3-8
Starting and Shutting Down a Database with SQL*Plus........................................................... 3-9
Starting and Shutting Down a Database Using Services.......................................................... 3-11
Running Multiple Instances........................................................................................................... 3-14
Creating Password Files.................................................................................................................. 3-15
Viewing Password Files............................................................................................................. 3-17
Deleting Password Files.................................................................................................................. 3-18
Connecting Remotely to the Database as SYS............................................................................ 3-18
Encrypting Database Passwords.................................................................................................... 3-18
Creating Control, Data, and Log Files on Remote Computers................................................. 3-19
Archiving Redo Log Files................................................................................................................ 3-20
Task 1: Change the Archive Mode to ARCHIVELOG.......................................................... 3-20
Task 2: Enable Automatic Archiving....................................................................................... 3-21
Using the ORADEBUG Utility...................................................................................................... 3-23
4 Monitoring a Database
Database Monitoring Overview...................................................................................................... 4-2
Using Oracle for Windows NT Performance Monitor ............................................................... 4-2
Registry Information.................................................................................................................... 4-3
Accessing Oracle for Windows NT Performance Monitor..................................................... 4-4
Monitoring Oracle9i Objects....................................................................................................... 4-5
Understanding Oracle Performance Objects............................................................................ 4-6
v
Oracle for Windows NT Performance Monitor Troubleshooting Information................... 4-8
Using the Event Viewer..................................................................................................................... 4-9
Accessing the Event Viewer........................................................................................................ 4-9
Reading the Event Viewer......................................................................................................... 4-10
Using the Event Viewer............................................................................................................. 4-11
Managing the Event Viewer...................................................................................................... 4-12
Using Trace and Alert Files............................................................................................................. 4-13
Viewing Threads Using the Oracle Administration Assistant for Windows NT................. 4-14
5 Tuning Windows NT to Optimize Oracle9i
Overview.............................................................................................................................................. 5-2
Reduce Priority of Foreground Applications on the Server Console....................................... 5-3
Configure Windows NT Server to Be an Application Server.................................................... 5-4
Disable Unnecessary Services.......................................................................................................... 5-6
Remove Unused Network Protocols .............................................................................................. 5-7
Reset the Network Protocol Bind Order......................................................................................... 5-8
Apply Latest Reliable Windows NT Server Service Pack......................................................... 5-10
Use Hardware or Operating System Striping............................................................................. 5-11
Multiple Striped Volumes for Sequential and Random Access........................................... 5-16
Span Windows NT Server Virtual Memory Paging File Across Physical Volumes............ 5-16
General Page File Sizing Tip..................................................................................................... 5-16
Close All Unnecessary Foreground Applications...................................................................... 5-17
Startup Folder.............................................................................................................................. 5-17
Virtual DOS Machines............................................................................................................... 5-17
Screen Savers............................................................................................................................... 5-17
6 Backing Up and Recovering Database Files
Selecting a Backup and Recovery Tool........................................................................................... 6-2
Integrating RMAN with a Media Manager on Windows NT.................................................... 6-3
Backing Up Files with the OCOPY Utility..................................................................................... 6-4
Recovering Files with the OCOPY Utility..................................................................................... 6-6
Using Legato Storage Manager........................................................................................................ 6-7
Settings for Multiple Tape Devices............................................................................................ 6-7
Japanese Version of Legato Storage Manager.......................................................................... 6-7
vi
7 Oracle9i Database Specifications for Windows
Initialization Parameter File (init.ora) Overview......................................................................... 7-2
Location of the Initialization Parameter File............................................................................ 7-2
Editing the Initialization Parameter File................................................................................... 7-2
Sample File.................................................................................................................................... 7-3
SGA_MAX_SIZE parameter....................................................................................................... 7-4
Initialization Parameters Without Windows NT-Specific Values............................................. 7-4
Displaying Initialization Parameter Values.............................................................................. 7-5
Database Initialization Parameters............................................................................................ 7-6
Calculating Database Limits............................................................................................................. 7-6
A Storing Tablespaces on Raw Partitions
Raw Partition Overview.................................................................................................................... A-2
Disk Definition.............................................................................................................................. A-2
Raw Partition Definition.............................................................................................................. A-3
Physical Disk.......................................................................................................................... A-3
Logical Partition.................................................................................................................... A-4
Physical Disk and Logical Partition Considerations........................................................ A-4
Frequently Asked Questions............................................................................................... A-5
Compatibility Issues............................................................................................................. A-6
Creating an Extended Partition........................................................................................................ A-7
Creating Logical Partitions in an Extended Partition.............................................................. A-8
Creating a Tablespace in a Raw Partition...................................................................................... A-9
Glossary
Index
vii
viii
Send Us Your Comments
Oracle9i Database Administrator’s Guide, Release 1 (9.0.1) for Windows
Part No. A90164-01
Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this
document. Your input is an important part of the information used for revision.
n Did you find any errors?
n Is the information clearly presented?
n Do you need more information? If so, where?
n Are the examples correct? Do you need more examples?
n What features did you like most?
If you find any errors or have any other suggestions for improvement, please indicate the document
title and part number, and the chapter, section, and page number (if available). You can send
comments to us in the following ways:
n E-mail: [email protected]
n FAX - [phone] Attn: Oracle Database for Windows Documentation
n Postal service:
Oracle Corporation
Oracle Database for Windows Documentation Manager
500 Oracle Parkway, Mailstop 1op6
Redwood Shores, CA 94065
USA
If you would like a reply, please give your name, address, telephone number, and (optionally)
electronic mail address.
If you have proble ms with the software, please contact your local Oracle Support Services. Contact
information for Oracle Support Services is available at this Web site:
http://www.oracle.com/support/
ix
xOracle9i Database Administrator’s Guide