Oracle® Database Administrator's Guide 11g Release 2 (11.2) E10595-07 February 2010 Oracle Database Administrator's Guide, 11g Release 2 (11.2) E10595-07 Copyright © 2001, 2010, Oracle and/or its affiliates. All rights reserved. Primary Author: Steve Fogel Contributing Authors: Caroline Johnston, Sheila Moore, Tony Morales, Padmaja Potineni, Randy Urbano, Lance Ashdown, Janis Greenberg Contributors: David Austin, Bharat Baddepudi, Cathy Baird, Mark Bauer, Eric Belden, Atif Chaudhry, Jonathan Creighton, Sudip Datta, Mark Dilman, Jacco Draaijer, Marcus Fallen, Amit Ganesh, GP Gongloor, Vira Goorah, Janis Greenberg, Shivani Gupta, Bill Hodak, Pat Huey, Chandrasekharan Iyer, Bhushan Khaladkar, Balaji Krishnan, Vasudha Krishnaswamy, Bala Kuchibhotla, Sushil Kumar, Vikram Kumar, Paul Lane, Adam Lee, Bill Lee, Sue K. Lee, Chon Lei, Yunrui Li, Ilya Listvinsky, Bryn Llewellyn, Barb Lundhild, Scott Lynn, Raghu Mani, Vineet Marwah, Colin McGregor, Mughees Minhas, Krishna Mohan, Sheila Moore, Valarie Moore, Niloy Mukherjee, Sujatha Muthulingam, Gary Ngai Ananth Raghavan, Mark Ramacher, Ravi Ramkissoon, Yair Sarig, Shrikanth Shankar, Bipul Sinha, Deborah Steiner, Janet Stern, Michael Stewart, Mahesh Subramaniam, Anh-Tuan Tran, Alex Tsukerman, Kothanda Umamageswaran, Eric Voss, Daniel M. Wong, Paul Youn, Wei Zhang This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. 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 prohibited. The 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 writing. If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. 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 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065. This 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 applications. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. This 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 services. Contents Preface............................................................................................................................................................ xxvii Audience.................................................................................................................................................. xxvii Documentation Accessibility................................................................................................................ xxvii Related Documents............................................................................................................................... xxviii Conventions........................................................................................................................................... xxviii What's New in Oracle Database Administrator's Guide?................................................. xxix Oracle Database 11g Release 2 (11.2) New Features in the Administrator's Guide....................... xxix Part I Basic Database Administration 1 Getting Started with Database Administration Types of Oracle Database Users............................................................................................................ 1-1 Database Administrators.................................................................................................................. 1-1 Security Officers................................................................................................................................. 1-2 Network Administrators................................................................................................................... 1-2 Application Developers..................................................................................................................... 1-2 Application Administrators.............................................................................................................. 1-3 Database Users................................................................................................................................... 1-3 Tasks of a Database Administrator....................................................................................................... 1-3 Task 1: Evaluate the Database Server Hardware.......................................................................... 1-3 Task 2: Install the Oracle Database Software................................................................................. 1-4 Task 3: Plan the Database.................................................................................................................. 1-4 Task 4: Create and Open the Database........................................................................................... 1-5 Task 5: Back Up the Database........................................................................................................... 1-5 Task 6: Enroll System Users.............................................................................................................. 1-5 Task 7: Implement the Database Design......................................................................................... 1-5 Task 8: Back Up the Fully Functional Database............................................................................ 1-5 Task 9: Tune Database Performance............................................................................................... 1-5 Task 10: Download and Install Patches.......................................................................................... 1-6 Task 11: Roll Out to Additional Hosts............................................................................................ 1-6 Submitting Commands and SQL to the Database............................................................................. 1-6 About SQL*Plus.................................................................................................................................. 1-7 Connecting to the Database with SQL*Plus................................................................................... 1-7 Identifying Your Oracle Database Software Release..................................................................... 1-12 iii Release Number Format................................................................................................................. 1-13 Checking Your Current Release Number.................................................................................... 1-13 About Database Administrator Security and Privileges............................................................... 1-14 The Database Administrator's Operating System Account...................................................... 1-14 Administrative User Accounts...................................................................................................... 1-14 Database Administrator Authentication.......................................................................................... 1-16 Administrative Privileges.............................................................................................................. 1-16 Selecting an Authentication Method for Database Administrators........................................ 1-18 Using Operating System Authentication..................................................................................... 1-20 Using Password File Authentication............................................................................................ 1-21 Creating and Maintaining a Password File...................................................................................... 1-22 Creating a Password File with ORAPWD................................................................................... 1-23 Sharing and Disabling the Password File.................................................................................... 1-25 Adding Users to a Password File.................................................................................................. 1-26 Maintaining a Password File......................................................................................................... 1-27 Data Utilities.......................................................................................................................................... 1-28 2 Creating and Configuring an Oracle Database About Creating an Oracle Database..................................................................................................... 2-1 Considerations Before Creating the Database............................................................................... 2-2 Creating a Database with DBCA........................................................................................................... 2-5 Creating a Database with Interactive DBCA.................................................................................. 2-5 Creating a Database with Noninteractive/Silent DBCA.............................................................. 2-5 Creating a Database with the CREATE DATABASE Statement..................................................... 2-6 Step 1: Specify an Instance Identifier (SID).................................................................................... 2-7 Step 2: Ensure That the Required Environment Variables Are Set............................................. 2-7 Step 3: Choose a Database Administrator Authentication Method............................................ 2-7 Step 4: Create the Initialization Parameter File.............................................................................. 2-8 Step 5: (Windows Only) Create an Instance................................................................................... 2-9 Step 6: Connect to the Instance......................................................................................................... 2-9 Step 7: Create a Server Parameter File......................................................................................... 2-10 Step 8: Start the Instance ............................................................................................................... 2-10 Step 9: Issue the CREATE DATABASE Statement..................................................................... 2-11 Step 10: Create Additional Tablespaces....................................................................................... 2-14 Step 11: Run Scripts to Build Data Dictionary Views ............................................................... 2-14 Step 12: Run Scripts to Install Additional Options (Optional)................................................. 2-15 Step 13: Back Up the Database...................................................................................................... 2-15 Step 14: (Optional) Enable Automatic Instance Startup............................................................ 2-15 Specifying CREATE DATABASE Statement Clauses.................................................................... 2-16 Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM.................... 2-16 Creating a Locally Managed SYSTEM Tablespace..................................................................... 2-17 About the SYSAUX Tablespace..................................................................................................... 2-17 Using Automatic Undo Management: Creating an Undo Tablespace.................................... 2-19 Creating a Default Permanent Tablespace.................................................................................. 2-19 Creating a Default Temporary Tablespace.................................................................................. 2-19 Specifying Oracle-Managed Files at Database Creation........................................................... 2-20 Supporting Bigfile Tablespaces During Database Creation...................................................... 2-21 iv Specifying the Database Time Zone and Time Zone File.......................................................... 2-22 Specifying FORCE LOGGING Mode........................................................................................... 2-23 Specifying Initialization Parameters................................................................................................. 2-24 About Initialization Parameters and Initialization Parameter Files........................................ 2-25 Determining the Global Database Name..................................................................................... 2-27 Specifying a Fast Recovery Area................................................................................................... 2-28 Specifying Control Files ................................................................................................................ 2-29 Specifying Database Block Sizes................................................................................................... 2-29 Specifying the Maximum Number of Processes......................................................................... 2-30 Specifying the DDL Lock Timeout............................................................................................... 2-30 Specifying the Method of Undo Space Management................................................................ 2-31 About The COMPATIBLE Initialization Parameter.................................................................. 2-31 Setting the License Parameter....................................................................................................... 2-32 Managing Initialization Parameters Using a Server Parameter File........................................... 2-32 What Is a Server Parameter File?.................................................................................................. 2-33 Migrating to a Server Parameter File........................................................................................... 2-33 Creating a Server Parameter File.................................................................................................. 2-34 Storing the Server Parameter File on HARD-Enabled Storage................................................ 2-35 The SPFILE Initialization Parameter............................................................................................ 2-37 Changing Initialization Parameter Values.................................................................................. 2-38 Clearing Initialization Parameter Values..................................................................................... 2-39 Exporting the Server Parameter File............................................................................................ 2-39 Backing Up the Server Parameter File......................................................................................... 2-40 Recovering a Lost or Damaged Server Parameter File.............................................................. 2-40 Viewing Parameter Settings.......................................................................................................... 2-41 Managing Application Workloads with Database Services ........................................................ 2-42 About Database Services ............................................................................................................... 2-42 Creating Database Services............................................................................................................ 2-43 Database Service Data Dictionary Views..................................................................................... 2-44 Considerations After Creating a Database....................................................................................... 2-45 Some Security Considerations....................................................................................................... 2-45 Enabling Transparent Data Encryption....................................................................................... 2-45 Creating a Secure External Password Store................................................................................ 2-46 Installing the Oracle Database Sample Schemas........................................................................ 2-46 Dropping a Database............................................................................................................................ 2-47 Database Data Dictionary Views....................................................................................................... 2-47 3 Starting Up and Shutting Down Starting Up a Database............................................................................................................................ 3-1 About Database Startup Options..................................................................................................... 3-1 Specifying Initialization Parameters at Startup............................................................................. 3-2 About Automatic Startup of Database Services............................................................................ 3-5 Preparing to Start Up an Instance.................................................................................................... 3-5 Starting Up an Instance..................................................................................................................... 3-6 Altering Database Availability.............................................................................................................. 3-9 Mounting a Database to an Instance............................................................................................... 3-9 Opening a Closed Database........................................................................................................... 3-10 v Opening a Database in Read-Only Mode.................................................................................... 3-10 Restricting Access to an Open Database...................................................................................... 3-11 Shutting Down a Database.................................................................................................................. 3-12 Shutting Down with the Normal Mode....................................................................................... 3-12 Shutting Down with the Immediate Mode................................................................................. 3-12 Shutting Down with the Transactional Mode............................................................................. 3-13 Shutting Down with the Abort Mode.......................................................................................... 3-13 Shutdown Timeout......................................................................................................................... 3-14 Quiescing a Database........................................................................................................................... 3-14 Placing a Database into a Quiesced State.................................................................................... 3-15 Restoring the System to Normal Operation................................................................................ 3-16 Viewing the Quiesce State of an Instance.................................................................................... 3-16 Suspending and Resuming a Database............................................................................................ 3-16 4 Configuring Automatic Restart of an Oracle Database About Oracle Restart............................................................................................................................... 4-1 Oracle Restart Overview................................................................................................................... 4-1 About Startup Dependencies........................................................................................................... 4-2 About Starting and Stopping Components with Oracle Restart................................................. 4-3 About Starting and Stopping Oracle Restart.................................................................................. 4-3 Oracle Restart Configuration............................................................................................................ 4-4 Oracle Restart Integration with Oracle Data Guard..................................................................... 4-5 Fast Application Notification with Oracle Restart........................................................................ 4-6 Configuring Oracle Restart................................................................................................................. 4-10 Preparing to Run SRVCTL............................................................................................................. 4-10 Obtaining Help for SRVCTL.......................................................................................................... 4-11 Adding Components to the Oracle Restart Configuration....................................................... 4-12 Removing Components from the Oracle Restart Configuration............................................. 4-14 Disabling and Enabling Oracle Restart Management for a Component................................ 4-14 Viewing Component Status........................................................................................................... 4-15 Viewing the Oracle Restart Configuration for a Component................................................... 4-16 Modifying the Oracle Restart Configuration for a Component............................................... 4-17 Managing Environment Variables in the Oracle Restart Configuration................................. 4-17 Creating and Deleting Database Services with SRVCTL.......................................................... 4-19 Enabling FAN Events in an Oracle Restart Environment......................................................... 4-20 Automating the Failover of Connections Between Primary and Standby Databases........... 4-20 Enabling Clients for Fast Connection Failover........................................................................... 4-21 Starting and Stopping Components Managed by Oracle Restart............................................... 4-25 Starting and Stopping Components Managed by Oracle Restart with SRVCTL.................. 4-25 Starting a Database Managed by Oracle Restart with Oracle Enterprise Manager.............. 4-27 Stopping and Restarting Oracle Restart for Maintenance Operations....................................... 4-28 SRVCTL Command Reference for Oracle Restart.......................................................................... 4-30 add..................................................................................................................................................... 4-32 config................................................................................................................................................. 4-38 disable............................................................................................................................................... 4-42 enable................................................................................................................................................ 4-45 getenv................................................................................................................................................ 4-48 vi modify............................................................................................................................................... 4-50 remove.............................................................................................................................................. 4-54 setenv................................................................................................................................................ 4-58 start.................................................................................................................................................... 4-60 status................................................................................................................................................. 4-64 stop.................................................................................................................................................... 4-68 unsetenv............................................................................................................................................ 4-72 CRSCTL Command Reference........................................................................................................... 4-74 check.................................................................................................................................................. 4-75 config................................................................................................................................................. 4-75 disable............................................................................................................................................... 4-75 enable................................................................................................................................................ 4-75 start.................................................................................................................................................... 4-75 stop.................................................................................................................................................... 4-75 5 Managing Processes About Dedicated and Shared Server Processes.................................................................................. 5-1 Dedicated Server Processes.............................................................................................................. 5-1 Shared Server Processes.................................................................................................................... 5-2 About Database Resident Connection Pooling.................................................................................. 5-4 Comparing DRCP to Dedicated Server and Shared Server......................................................... 5-5 Restrictions on Using Database Resident Connection Pooling................................................... 5-6 Configuring Oracle Database for Shared Server............................................................................... 5-6 Initialization Parameters for Shared Server................................................................................... 5-6 Memory Management for Shared Server....................................................................................... 5-7 Enabling Shared Server..................................................................................................................... 5-7 Configuring Dispatchers................................................................................................................ 5-10 Shared Server Data Dictionary Views.......................................................................................... 5-15 Configuring Database Resident Connection Pooling.................................................................... 5-15 Enabling Database Resident Connection Pooling...................................................................... 5-16 Configuring the Connection Pool for Database Resident Connection Pooling..................... 5-17 Data Dictionary Views for Database Resident Connection Pooling........................................ 5-19 About Oracle Database Background Processes............................................................................... 5-19 Managing Processes for Parallel SQL Execution............................................................................ 5-20 About Parallel Execution Servers................................................................................................. 5-21 Altering Parallel Execution for a Session..................................................................................... 5-21 Managing Processes for External Procedures.................................................................................. 5-22 About External Procedures............................................................................................................ 5-22 DBA Tasks to Enable External Procedure Calls.......................................................................... 5-23 Terminating Sessions............................................................................................................................ 5-23 Identifying Which Session to Terminate...................................................................................... 5-24 Terminating an Active Session...................................................................................................... 5-24 Terminating an Inactive Session................................................................................................... 5-25 Process and Session Data Dictionary Views.................................................................................... 5-25 vii 6 Managing Memory About Memory Management................................................................................................................. 6-1 Memory Architecture Overview............................................................................................................ 6-2 Using Automatic Memory Management............................................................................................. 6-3 About Automatic Memory Management....................................................................................... 6-4 Enabling Automatic Memory Management.................................................................................. 6-4 Monitoring and Tuning Automatic Memory Management........................................................ 6-6 Configuring Memory Manually............................................................................................................ 6-7 Using Automatic Shared Memory Management.......................................................................... 6-8 Using Manual Shared Memory Management............................................................................. 6-14 Using Automatic PGA Memory Management........................................................................... 6-20 Using Manual PGA Memory Management................................................................................ 6-21 Configuring Database Smart Flash Cache........................................................................................ 6-21 When to Configure the Flash Cache............................................................................................. 6-21 Sizing the Flash Cache.................................................................................................................... 6-22 Tuning Memory for the Flash Cache............................................................................................ 6-22 Flash Cache Initialization Parameters.......................................................................................... 6-22 Flash Cache in an Oracle Real Applications Clusters Environment........................................ 6-23 Memory Management Reference....................................................................................................... 6-23 Platforms That Support Automatic Memory Management...................................................... 6-23 Memory Management Data Dictionary Views........................................................................... 6-23 7 Managing Users and Securing the Database The Importance of Establishing a Security Policy for Your Database........................................... 7-1 Managing Users and Resources............................................................................................................. 7-1 Managing User Privileges and Roles................................................................................................... 7-2 Auditing Database Use........................................................................................................................... 7-2 Predefined User Accounts...................................................................................................................... 7-2 8 Monitoring Database Operations Monitoring Errors and Alerts................................................................................................................. 8-1 Monitoring Errors with Trace Files and the Alert Log................................................................. 8-1 Monitoring Database Operations with Server-Generated Alerts............................................... 8-4 Monitoring Performance......................................................................................................................... 8-6 Monitoring Locks............................................................................................................................... 8-7 Monitoring Wait Events.................................................................................................................... 8-7 Performance Monitoring Data Dictionary Views.......................................................................... 8-7 9 Managing Diagnostic Data About the Oracle Database Fault Diagnosability Infrastructure.................................................... 9-1 Fault Diagnosability Infrastructure Overview............................................................................... 9-1 About Incidents and Problems......................................................................................................... 9-3 Fault Diagnosability Infrastructure Components......................................................................... 9-5 Structure, Contents, and Location of the Automatic Diagnostic Repository............................ 9-7 Investigating, Reporting, and Resolving a Problem...................................................................... 9-10 Roadmap—Investigating, Reporting, and Resolving a Problem............................................. 9-10 viii Task 1 – View Critical Error Alerts in Enterprise Manager...................................................... 9-12 Task 2 –View Problem Details....................................................................................................... 9-13 Task 3 – (Optional) Gather Additional Diagnostic Information.............................................. 9-13 Task 4 – (Optional) Create a Service Request.............................................................................. 9-13 Task 5 – Package and Upload Diagnostic Data to Oracle Support.......................................... 9-14 Task 6 – Track the Service Request and Implement Any Repairs............................................ 9-15 Task 7 – Close Incidents................................................................................................................. 9-16 Viewing Problems with the Enterprise Manager Support Workbench...................................... 9-17 Creating a User-Reported Problem.................................................................................................... 9-18 Viewing the Alert Log.......................................................................................................................... 9-19 Finding Trace Files................................................................................................................................ 9-20 Running Health Checks with Health Monitor................................................................................ 9-20 About Health Monitor.................................................................................................................... 9-21 Running Health Checks Manually............................................................................................... 9-22 Viewing Checker Reports.............................................................................................................. 9-23 Health Monitor Views.................................................................................................................... 9-26 Health Check Parameters Reference............................................................................................ 9-27 Repairing SQL Failures with the SQL Repair Advisor................................................................. 9-27 About the SQL Repair Advisor..................................................................................................... 9-28 Running the SQL Repair Advisor................................................................................................. 9-28 Viewing, Disabling, or Removing a SQL Patch.......................................................................... 9-29 Repairing Data Corruptions with the Data Recovery Advisor.................................................... 9-30 Creating, Editing, and Uploading Custom Incident Packages..................................................... 9-31 About Incident Packages................................................................................................................ 9-32 Packaging and Uploading Problems with Custom Packaging................................................ 9-34 Viewing and Modifying Incident Packages................................................................................ 9-38 Creating, Editing, and Uploading Correlated Packages........................................................... 9-44 Deleting Correlated Packages....................................................................................................... 9-44 Setting Incident Packaging Preferences....................................................................................... 9-45 Part II Oracle Database Structure and Storage 10 Managing Control Files What Is a Control File?......................................................................................................................... 10-1 Guidelines for Control Files............................................................................................................... 10-2 Provide Filenames for the Control Files...................................................................................... 10-2 Multiplex Control Files on Different Disks................................................................................. 10-2 Back Up Control Files..................................................................................................................... 10-3 Manage the Size of Control Files.................................................................................................. 10-3 Creating Control Files.......................................................................................................................... 10-3 Creating Initial Control Files......................................................................................................... 10-3 Creating Additional Copies, Renaming, and Relocating Control Files ................................. 10-4 Creating New Control Files........................................................................................................... 10-4 Troubleshooting After Creating Control Files................................................................................. 10-7 Checking for Missing or Extra Files............................................................................................. 10-7 Handling Errors During CREATE CONTROLFILE.................................................................. 10-7 ix Backing Up Control Files..................................................................................................................... 10-8 Recovering a Control File Using a Current Copy........................................................................... 10-8 Recovering from Control File Corruption Using a Control File Copy.................................... 10-8 Recovering from Permanent Media Failure Using a Control File Copy................................. 10-8 Dropping Control Files........................................................................................................................ 10-9 Control Files Data Dictionary Views................................................................................................. 10-9 11 Managing the Redo Log What Is the Redo Log?.......................................................................................................................... 11-1 Redo Threads................................................................................................................................... 11-1 Redo Log Contents.......................................................................................................................... 11-2 How Oracle Database Writes to the Redo Log........................................................................... 11-2 Planning the Redo Log......................................................................................................................... 11-4 Multiplexing Redo Log Files......................................................................................................... 11-4 Placing Redo Log Members on Different Disks.......................................................................... 11-6 Planning the Size of Redo Log Files............................................................................................. 11-7 Planning the Block Size of Redo Log Files.................................................................................. 11-7 Choosing the Number of Redo Log Files.................................................................................... 11-8 Controlling Archive Lag ............................................................................................................... 11-9 Creating Redo Log Groups and Members...................................................................................... 11-10 Creating Redo Log Groups.......................................................................................................... 11-10 Creating Redo Log Members....................................................................................................... 11-11 Relocating and Renaming Redo Log Members ............................................................................ 11-11 Dropping Redo Log Groups and Members................................................................................... 11-13 Dropping Log Groups.................................................................................................................. 11-13 Dropping Redo Log Members..................................................................................................... 11-13 Forcing Log Switches.......................................................................................................................... 11-14 Verifying Blocks in Redo Log Files................................................................................................. 11-14 Clearing a Redo Log File.................................................................................................................... 11-15 Redo Log Data Dictionary Views .................................................................................................... 11-16 12 Managing Archived Redo Logs What Is the Archived Redo Log?........................................................................................................ 12-1 Choosing Between NOARCHIVELOG and ARCHIVELOG Mode........................................... 12-2 Running a Database in NOARCHIVELOG Mode..................................................................... 12-2 Running a Database in ARCHIVELOG Mode............................................................................ 12-3 Controlling Archiving.......................................................................................................................... 12-4 Setting the Initial Database Archiving Mode.............................................................................. 12-4 Changing the Database Archiving Mode ................................................................................... 12-4 Performing Manual Archiving...................................................................................................... 12-5 Adjusting the Number of Archiver Processes............................................................................ 12-6 Specifying Archive Destinations....................................................................................................... 12-6 Setting Initialization Parameters for Archive Destinations...................................................... 12-6 Understanding Archive Destination Status................................................................................ 12-9 Specifying Alternate Destinations.............................................................................................. 12-10 About Log Transmission Modes....................................................................................................... 12-10 Normal Transmission Mode........................................................................................................ 12-10 x
Description: