Table Of Content

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 - (650) 506-7365 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