administration guide 11g

oracle 11g管理篇

Oracle® Database Administrator's Guide 11g Release 2 (11.2) E25494-01

September 2011

oracle 11g管理篇

Oracle Database Administrator's Guide, 11g Release 2 (11.2)

E25494-01

Copyright © 2001, 2011, Oracle and/or its affiliates. All rights reserved.

Primary Author: Steve Fogel

Contributing Author: Caroline Johnston, Sheila Moore, Tony Morales, Padmaja Potineni, Randy Urbano, Lance Ashdown, Janis Greenberg

Contributors: D. Austin, B. Baddepudi, H. Baer, C. Baird, M. Bauer, E. Belden, A. Chaudhry, J. Creighton, S. Datta, M. Dilman, J. Draaijer, M. Fallen, A. Ganesh, GP Gongloor, V. Goorah, S. Gupta, W. Hodak, P. Huey, C. Iyer, B. Khaladkar, B. Krishnan, V. Krishnaswamy, B. Kuchibhotla, S. Kumar, V. Kumar, P. Lane, A. Lee, B. Lee, S. K. Lee, C. Lei, Y. Li, I. Listvinsky, B. Llewellyn, B. Lundhild, S. Lynn, R. Mani, V. Marwah, C. McGregor, M. Minhas, K. Mohan, V. Moore, N. Mukherjee, S. Muthulingam, G. Ngai, A. Raghavan, M. Ramacher, R. Ramkissoon, Y. Sarig, S. Shankar, B. Sinha, V. Srihari, D. Steiner, J. Stern, M. Stewart, M. Subramaniam, A. Tran, A. Tsukerman, K. Umamageswaran, E. Voss, D. M. Wong, P. Youn, W. 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 is software or related documentation that 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 America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software or hardware 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 that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware 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.

oracle 11g管理篇

Contents

Preface (xxxv)

Audience (xxxv)

Documentation Accessibility (xxxv)

Related Documents (xxxvi)

Conventions (xxxvi)

What's New in Oracle Database Administrator's Guide? (xxxvii)

Oracle Database 11g Release 2 (11.2.0.3) New Features in the Administrator's Guide (xxxvii)

Oracle Database 11g Release 2 (11.2.0.2) New Features in the Administrator's Guide (xxxvii)

Oracle Database 11g Release 2 (11.2.0.1) New Features in the Administrator's Guide (xxxix)

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

iii

oracle 11g管理篇

Connecting to the Database with SQL*Plus................................................................................... 1-7 Identifying Your Oracle Database Software Release.....................................................................1-12 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-24 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: (Optional) Run Scripts to Install Additional Options.................................................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-15 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-18 Creating a Default Permanent Tablespace..................................................................................2-18 Creating a Default Temporary Tablespace..................................................................................2-18 iv

oracle 11g管理篇

Specifying Oracle Managed Files at Database Creation............................................................2-19 Supporting Bigfile Tablespaces During Database Creation......................................................2-20 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-24 Determining the Global Database Name.....................................................................................2-26 Specifying a Fast Recovery Area...................................................................................................2-27 Specifying Control Files ................................................................................................................2-28 Specifying Database Block Sizes...................................................................................................2-28 Specifying the Maximum Number of Processes.........................................................................2-29 Specifying the DDL Lock Timeout...............................................................................................2-30 Specifying the Method of Undo Space Management................................................................2-30 About The COMPATIBLE Initialization Parameter..................................................................2-31 Setting the License Parameter.......................................................................................................2-31 Managing Initialization Parameters Using a Server Parameter File...........................................2-32 What Is a Server Parameter File?..................................................................................................2-32 Migrating to a Server Parameter File...........................................................................................2-33 Creating a Server Parameter File..................................................................................................2-33 The SPFILE Initialization Parameter............................................................................................2-35 Changing Initialization Parameter Values..................................................................................2-35 Clearing Initialization Parameter Values.....................................................................................2-37 Exporting the Server Parameter File............................................................................................2-37 Backing Up the Server Parameter File.........................................................................................2-37 Recovering a Lost or Damaged Server Parameter File..............................................................2-38 Viewing Parameter Settings..........................................................................................................2-38 Managing Application Workloads with Database Services ........................................................2-39 About Database Services ...............................................................................................................2-39 Creating Database Services............................................................................................................2-41 Database Service Data Dictionary Views.....................................................................................2-42 Considerations After Creating a Database.......................................................................................2-42 Some Security Considerations.......................................................................................................2-42 Enabling Transparent Data Encryption.......................................................................................2-43 Creating a Secure External Password Store................................................................................2-44 Installing the Oracle Database Sample Schemas........................................................................2-44 Dropping a Database............................................................................................................................2-44 Database Data Dictionary Views.......................................................................................................2-45 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

v

oracle 11g管理篇

Opening a Closed Database...........................................................................................................3-10 Opening a Database in Read-Only Mode....................................................................................3-10 Restricting Access to an Open Database......................................................................................3-11 Shutting Down a Database..................................................................................................................3-11 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-9 Preparing to Run SRVCTL.............................................................................................................4-10 Obtaining Help for SRVCTL..........................................................................................................4-11 Adding Components to the Oracle Restart Configuration.......................................................4-11 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-16 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-19 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-26 Stopping and Restarting Oracle Restart for Maintenance Operations.......................................4-27 SRVCTL Command Reference for Oracle Restart..........................................................................4-30 add.....................................................................................................................................................4-32 config.................................................................................................................................................4-38 disable...............................................................................................................................................4-41 enable................................................................................................................................................4-44 vi

oracle 11g管理篇

getenv................................................................................................................................................4-47 modify...............................................................................................................................................4-49 remove..............................................................................................................................................4-53 setenv................................................................................................................................................4-56 start....................................................................................................................................................4-58 status.................................................................................................................................................4-62 stop....................................................................................................................................................4-65 unsetenv............................................................................................................................................4-69 CRSCTL Command Reference...........................................................................................................4-71 check..................................................................................................................................................4-72 config.................................................................................................................................................4-73 disable...............................................................................................................................................4-74 enable................................................................................................................................................4-75 start....................................................................................................................................................4-76 stop....................................................................................................................................................4-77 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 Disabling Shared Server.................................................................................................................5-14 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

oracle 11g管理篇

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-19 Using Manual PGA Memory Management................................................................................6-20 Configuring Database Smart Flash Cache........................................................................................6-21 When to Configure the Flash Cache.............................................................................................6-21 Sizing the Flash Cache....................................................................................................................6-21 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-11 Roadmap—Investigating, Reporting, and Resolving a Problem.............................................9-11 viii

oracle 11g管理篇

Task 1 – View Critical Error Alerts in Enterprise Manager......................................................9-13 Task 2 –View Problem Details.......................................................................................................9-14 Task 3 – (Optional) Gather Additional Diagnostic Information..............................................9-14 Task 4 – (Optional) Create a Service Request..............................................................................9-14 Task 5 – Package and Upload Diagnostic Data to Oracle Support..........................................9-15 Task 6 – Track the Service Request and Implement Any Repairs............................................9-16 Task 7 – Close Incidents.................................................................................................................9-17 Viewing Problems with the Enterprise Manager Support Workbench......................................9-18 Creating a User-Reported Problem....................................................................................................9-19 Viewing the Alert Log..........................................................................................................................9-20 Finding Trace Files................................................................................................................................9-21 Running Health Checks with Health Monitor................................................................................9-21 About Health Monitor....................................................................................................................9-22 Running Health Checks Manually...............................................................................................9-23 Viewing Checker Reports..............................................................................................................9-24 Health Monitor Views....................................................................................................................9-27 Health Check Parameters Reference............................................................................................9-28 Repairing SQL Failures with the SQL Repair Advisor.................................................................9-28 About the SQL Repair Advisor.....................................................................................................9-29 Running the SQL Repair Advisor.................................................................................................9-29 Viewing, Disabling, or Removing a SQL Patch..........................................................................9-30 Repairing Data Corruptions with the Data Recovery Advisor....................................................9-31 Creating, Editing, and Uploading Custom Incident Packages.....................................................9-32 About Incident Packages................................................................................................................9-33 Packaging and Uploading Problems with Custom Packaging................................................9-35 Viewing and Modifying Incident Packages................................................................................9-39 Creating, Editing, and Uploading Correlated Packages...........................................................9-45 Deleting Correlated Packages.......................................................................................................9-45 Setting Incident Packaging Preferences.......................................................................................9-46 10Managing Oracle Enterprise Manager Database Control

Database Control Overview................................................................................................................10-1 Understanding the Enterprise Manager Directories..................................................................10-1 Configuring Database Control During and After Installation....................................................10-3 Overview of Configuration Options............................................................................................10-3 Configuring Database Control During Installation...................................................................10-4 Configuring Database Control with DBCA................................................................................10-4 Configuring Database Control Using EMCA.............................................................................10-5 Using an Input File for EMCA Parameters...............................................................................10-10 Specifying the Ports Used by Database Control.......................................................................10-11 Using EMCA With Oracle RAC..................................................................................................10-12 Administering Database Control.....................................................................................................10-15 Starting and Stopping the Database Control Process..............................................................10-15 Accessing the Database Home Page...........................................................................................10-16 Creating Database Control Administrative Users....................................................................10-17 Granting Access to Database Control for Non-Administrative Users..................................10-18 Setting Database Control Preferences........................................................................................10-18

ix

oracle 11g管理篇

Managing Database Control in an Oracle RAC Environment...................................................10-21 Verifying that Database Control is Running in an Oracle RAC Environment....................10-21 Backing Up the emkey.ora File...................................................................................................10-22 Deleting and Creating Database Control Objects........................................................................10-22 Deleting Database Control Objects.............................................................................................10-22 Creating Database Control Objects.............................................................................................10-24 Re-Creating Or Reconfiguring Database Control.....................................................................10-25 EMCA Troubleshooting Tips............................................................................................................10-25 Using EMCA After Changing the Database Listener Port......................................................10-25 Upgrading 11g Release 2 Grid Control Agents.........................................................................10-26 Using EMCA When the Database Host Name or IP Address Changes................................10-26 Using EMCA When the TNS Configuration Is Changed........................................................10-26 Using EMCA for Desktop Class Installations...........................................................................10-26 Handling EMCA Error Messages...............................................................................................10-27

Part II Oracle Database Structure and Storage

11Managing Control Files

What Is a Control File?.........................................................................................................................11-1 Guidelines for Control Files...............................................................................................................11-2 Provide Filenames for the Control Files......................................................................................11-2 Multiplex Control Files on Different Disks.................................................................................11-2 Back Up Control Files.....................................................................................................................11-3 Manage the Size of Control Files..................................................................................................11-3 Creating Control Files..........................................................................................................................11-3 Creating Initial Control Files.........................................................................................................11-3 Creating Additional Copies, Renaming, and Relocating Control Files .................................11-4 Creating New Control Files...........................................................................................................11-4 Troubleshooting After Creating Control Files.................................................................................11-7 Checking for Missing or Extra Files.............................................................................................11-7 Handling Errors During CREATE CONTROLFILE..................................................................11-7 Backing Up Control Files.....................................................................................................................11-8 Recovering a Control File Using a Current Copy...........................................................................11-8 Recovering from Control File Corruption Using a Control File Copy....................................11-8 Recovering from Permanent Media Failure Using a Control File Copy.................................11-8 Dropping Control Files........................................................................................................................11-9 Control Files Data Dictionary Views.................................................................................................11-9 12 Managing the Redo Log

What Is the Redo Log?..........................................................................................................................12-1 Redo Threads...................................................................................................................................12-1 Redo Log Contents..........................................................................................................................12-2 How Oracle Database Writes to the Redo Log...........................................................................12-2 Planning the Redo Log.........................................................................................................................12-4 Multiplexing Redo Log Files.........................................................................................................12-4 Placing Redo Log Members on Different Disks..........................................................................12-6 x

oracle 11g管理篇

Planning the Size of Redo Log Files.............................................................................................12-7 Planning the Block Size of Redo Log Files..................................................................................12-7 Choosing the Number of Redo Log Files....................................................................................12-8 Controlling Archive Lag ...............................................................................................................12-9 Creating Redo Log Groups and Members......................................................................................12-10 Creating Redo Log Groups..........................................................................................................12-10 Creating Redo Log Members.......................................................................................................12-11 Relocating and Renaming Redo Log Members ............................................................................12-11 Dropping Redo Log Groups and Members...................................................................................12-12 Dropping Log Groups..................................................................................................................12-13 Dropping Redo Log Members.....................................................................................................12-13 Forcing Log Switches..........................................................................................................................12-14 Verifying Blocks in Redo Log Files.................................................................................................12-14 Clearing a Redo Log File....................................................................................................................12-15 Redo Log Data Dictionary Views ....................................................................................................12-16 13 Managing Archived Redo Logs

What Is the Archived Redo Log?........................................................................................................13-1 Choosing Between NOARCHIVELOG and ARCHIVELOG Mode...........................................13-2 Running a Database in NOARCHIVELOG Mode.....................................................................13-2 Running a Database in ARCHIVELOG Mode............................................................................13-3 Controlling Archiving..........................................................................................................................13-4 Setting the Initial Database Archiving Mode..............................................................................13-4 Changing the Database Archiving Mode ...................................................................................13-4 Performing Manual Archiving......................................................................................................13-5 Adjusting the Number of Archiver Processes............................................................................13-5 Specifying Archive Destinations.......................................................................................................13-6 Setting Initialization Parameters for Archive Destinations......................................................13-6 Understanding Archive Destination Status................................................................................13-9 Specifying Alternate Destinations..............................................................................................13-10 About Log Transmission Modes.......................................................................................................13-10 Normal Transmission Mode........................................................................................................13-10 Standby Transmission Mode ......................................................................................................13-10 Managing Archive Destination Failure..........................................................................................13-11 Specifying the Minimum Number of Successful Destinations...............................................13-11 Rearchiving to a Failed Destination...........................................................................................13-12 Controlling Trace Output Generated by the Archivelog Process..............................................13-13 Viewing Information About the Archived Redo Log..................................................................13-14 Archived Redo Logs Views.........................................................................................................13-14 The ARCHIVE LOG LIST Command.........................................................................................13-15 14 Managing Tablespaces

Guidelines for Managing Tablespaces..............................................................................................14-1 Using Multiple Tablespaces...........................................................................................................14-2 Assigning Tablespace Quotas to Users........................................................................................14-2 Creating Tablespaces............................................................................................................................14-2

xi

oracle 11g管理篇

Locally Managed Tablespaces.......................................................................................................14-3 Bigfile Tablespaces..........................................................................................................................14-6 Compressed Tablespaces...............................................................................................................14-8 Encrypted Tablespaces...................................................................................................................14-8 Temporary Tablespaces................................................................................................................14-10 Multiple Temporary Tablespaces: Using Tablespace Groups................................................14-13 Specifying Nonstandard Block Sizes for Tablespaces.................................................................14-14 Controlling the Writing of Redo Records.......................................................................................14-15 Altering Tablespace Availability .....................................................................................................14-15 Taking Tablespaces Offline..........................................................................................................14-15 Bringing Tablespaces Online.......................................................................................................14-17 Using Read-Only Tablespaces..........................................................................................................14-17 Making a Tablespace Read-Only................................................................................................14-18 Making a Read-Only Tablespace Writable ...............................................................................14-19 Creating a Read-Only Tablespace on a WORM Device..........................................................14-20 Delaying the Opening of Datafiles in Read-Only Tablespaces..............................................14-20 Altering and Maintaining Tablespaces...........................................................................................14-21 Increasing the Size of a Tablespace.............................................................................................14-21 Altering a Locally Managed Tablespace....................................................................................14-21 Altering a Bigfile Tablespace.......................................................................................................14-21 Altering a Locally Managed Temporary Tablespace...............................................................14-22 Shrinking a Locally Managed Temporary Tablespace............................................................14-23 Renaming Tablespaces.......................................................................................................................14-23 Dropping Tablespaces .......................................................................................................................14-24 Managing the SYSAUX Tablespace.................................................................................................14-25 Monitoring Occupants of the SYSAUX Tablespace.................................................................14-25 Moving Occupants Out Of or Into the SYSAUX Tablespace..................................................14-25 Controlling the Size of the SYSAUX Tablespace......................................................................14-25 Diagnosing and Repairing Locally Managed Tablespace Problems.........................................14-26 Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)............14-28 Scenario 2: Dropping a Corrupted Segment.............................................................................14-28 Scenario 3: Fixing Bitmap Where Overlap is Reported...........................................................14-28 Scenario 4: Correcting Media Corruption of Bitmap Blocks...................................................14-28 Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace......14-29 Migrating the SYSTEM Tablespace to a Locally Managed Tablespace....................................14-29 Transporting Tablespaces Between Databases..............................................................................14-30 Introduction to Transportable Tablespaces...............................................................................14-30 About Transporting Tablespaces Across Platforms.................................................................14-31 Limitations on Transportable Tablespace Use..........................................................................14-32 Compatibility Considerations for Transportable Tablespaces...............................................14-34 Transporting Tablespaces Between Databases: A Procedure and Example........................14-35 Using Transportable Tablespaces: Scenarios............................................................................14-44 Moving Databases Across Platforms Using Transportable Tablespaces..............................14-47 Tablespace Data Dictionary Views..................................................................................................14-47 Example 1: Listing Tablespaces and Default Storage Parameters.........................................14-48 Example 2: Listing the Datafiles and Associated Tablespaces of a Database.......................14-48 Example 3: Displaying Statistics for Free Space (Extents) of Each Tablespace....................14-49 xii

oracle 11g管理篇

15Managing Datafiles and Tempfiles

Guidelines for Managing Datafiles...................................................................................................15-1 Determine the Number of Datafiles.............................................................................................15-2 Determine the Size of Datafiles.....................................................................................................15-3 Place Datafiles Appropriately.......................................................................................................15-4 Store Datafiles Separate from Redo Log Files.............................................................................15-4 Creating Datafiles and Adding Datafiles to a Tablespace ...........................................................15-4 Changing Datafile Size........................................................................................................................15-5 Enabling and Disabling Automatic Extension for a Datafile....................................................15-5 Manually Resizing a Datafile........................................................................................................15-6 Altering Datafile Availability.............................................................................................................15-6 Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode...................................15-7 Taking Datafiles Offline in NOARCHIVELOG Mode...............................................................15-7 Altering the Availability of All Datafiles or Tempfiles in a Tablespace.................................15-8 Renaming and Relocating Datafiles..................................................................................................15-8 Procedures for Renaming and Relocating Datafiles in a Single Tablespace..........................15-9 Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces.......................15-10 Dropping Datafiles.............................................................................................................................15-11 Verifying Data Blocks in Datafiles..................................................................................................15-12 Copying Files Using the Database Server......................................................................................15-12 Copying a File on a Local File System........................................................................................15-13 Third-Party File Transfer.............................................................................................................15-14 File Transfer and the DBMS_SCHEDULER Package...............................................................15-15 Advanced File Transfer Mechanisms.........................................................................................15-15 Mapping Files to Physical Devices..................................................................................................15-15 Overview of Oracle Database File Mapping Interface............................................................15-16 How the Oracle Database File Mapping Interface Works.......................................................15-16 Using the Oracle Database File Mapping Interface..................................................................15-20 File Mapping Examples................................................................................................................15-23 Datafiles Data Dictionary Views......................................................................................................15-25 16 Managing Undo

What Is Undo?........................................................................................................................................16-1 Introduction to Automatic Undo Management ..............................................................................16-2 Overview of Automatic Undo Management..............................................................................16-2 About the Undo Retention Period................................................................................................16-3 Setting the Minimum Undo Retention Period................................................................................16-5 Sizing a Fixed-Size Undo Tablespace................................................................................................16-6 The Undo Advisor PL/SQL Interface..........................................................................................16-7 Managing Undo Tablespaces..............................................................................................................16-7 Creating an Undo Tablespace.......................................................................................................16-8 Altering an Undo Tablespace........................................................................................................16-9 Dropping an Undo Tablespace.....................................................................................................16-9 Switching Undo Tablespaces.......................................................................................................16-10 Establishing User Quotas for Undo Space.................................................................................16-10 Managing Space Threshold Alerts for the Undo Tablespace.................................................16-11

xiii

oracle 11g管理篇

Migrating to Automatic Undo Management..................................................................................16-11 Undo Space Data Dictionary Views................................................................................................16-11

17 Using Oracle Managed Files

What Are Oracle Managed Files?.......................................................................................................17-1 Who Can Use Oracle Managed Files?..........................................................................................17-2 Benefits of Using Oracle Managed Files......................................................................................17-3 Oracle Managed Files and Existing Functionality......................................................................17-3 Enabling the Creation and Use of Oracle Managed Files.............................................................17-3 Setting the DB_CREATE_FILE_DEST Initialization Parameter...............................................17-4 Setting the DB_RECOVERY_FILE_DEST Parameter.................................................................17-5 Setting the DB_CREATE_ONLINE_LOG_DEST_n Initialization Parameters.......................17-5 Creating Oracle Managed Files..........................................................................................................17-5 How Oracle Managed Files Are Named......................................................................................17-6 Creating Oracle Managed Files at Database Creation...............................................................17-7 Creating Datafiles for Tablespaces Using Oracle Managed Files..........................................17-12 Creating Tempfiles for Temporary Tablespaces Using Oracle Managed Files....................17-14 Creating Control Files Using Oracle Managed Files................................................................17-15 Creating Redo Log Files Using Oracle Managed Files............................................................17-16 Creating Archived Logs Using Oracle Managed Files............................................................17-17 Operation of Oracle Managed Files.................................................................................................17-17 Dropping Datafiles and Tempfiles.............................................................................................17-18 Dropping Redo Log Files.............................................................................................................17-18 Renaming Files..............................................................................................................................17-18 Managing Standby Databases ....................................................................................................17-19 Scenarios for Using Oracle Managed Files....................................................................................17-19 Scenario 1: Create and Manage a Database with Multiplexed Redo Logs...........................17-19 Scenario 2: Create and Manage a Database with Database and Fast Recovery Areas........17-22 Scenario 3: Adding Oracle Managed Files to an Existing Database......................................17-23

Part III Schema Objects

18 Managing Schema Objects

Creating Multiple Tables and Views in a Single Operation.........................................................18-1 Analyzing Tables, Indexes, and Clusters..........................................................................................18-2 Using DBMS_STATS to Collect Table and Index Statistics.......................................................18-2 Validating Tables, Indexes, Clusters, and Materialized Views................................................18-3 Listing Chained Rows of Tables and Clusters............................................................................18-4 Truncating Tables and Clusters..........................................................................................................18-5 Using DELETE.................................................................................................................................18-6 Using DROP and CREATE............................................................................................................18-6 Using TRUNCATE..........................................................................................................................18-6 Enabling and Disabling Triggers.......................................................................................................18-7 Enabling Triggers............................................................................................................................18-9 Disabling Triggers...........................................................................................................................18-9 Managing Integrity Constraints.........................................................................................................18-9 xiv

oracle 11g管理篇

Integrity Constraint States ..........................................................................................................18-10 Setting Integrity Constraints Upon Definition..........................................................................18-11 Modifying, Renaming, or Dropping Existing Integrity Constraints.....................................18-12 Deferring Constraint Checks.......................................................................................................18-14 Reporting Constraint Exceptions................................................................................................18-14 Viewing Constraint Information.................................................................................................18-16 Renaming Schema Objects................................................................................................................18-16 Managing Object Dependencies......................................................................................................18-17 About Object Dependencies and Object Invalidation..............................................................18-17 Manually Recompiling Invalid Objects with DDL...................................................................18-18 Manually Recompiling Invalid Objects with PL/SQL Package Procedures........................18-19 Managing Object Name Resolution................................................................................................18-19 Switching to a Different Schema.....................................................................................................18-21 Managing Editions..............................................................................................................................18-21 About Editions and Edition-Based Redefinition......................................................................18-21 DBA Tasks for Edition-Based Redefinition...............................................................................18-22 Setting the Database Default Edition.........................................................................................18-22 Querying the Database Default Edition.....................................................................................18-22 Setting the Edition Attribute of a Database Service.................................................................18-23 Using an Edition............................................................................................................................18-24 Editions Data Dictionary Views..................................................................................................18-24 Displaying Information About Schema Objects..........................................................................18-24 Using a PL/SQL Package to Display Information About Schema Objects..........................18-25 Schema Objects Data Dictionary Views.....................................................................................18-25 19 Managing Space for Schema Objects

Managing Tablespace Alerts...............................................................................................................19-1 Setting Alert Thresholds................................................................................................................19-2 Viewing Alerts.................................................................................................................................19-3 Limitations.......................................................................................................................................19-3 Managing Resumable Space Allocation...........................................................................................19-4 Resumable Space Allocation Overview.......................................................................................19-4 Enabling and Disabling Resumable Space Allocation...............................................................19-7 Using a LOGON Trigger to Set Default Resumable Mode.......................................................19-8 Detecting Suspended Statements..................................................................................................19-8 Operation-Suspended Alert.........................................................................................................19-10 Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger............19-10 Reclaiming Wasted Space..................................................................................................................19-12 Understanding Reclaimable Unused Space..............................................................................19-12 Using the Segment Advisor.........................................................................................................19-12 Shrinking Database Segments Online........................................................................................19-26 Deallocating Unused Space.........................................................................................................19-28 Dropping Unused Object Storage....................................................................................................19-28 Understanding Space Usage of Data Types...................................................................................19-29 Displaying Information About Space Usage for Schema Objects............................................19-29 Using PL/SQL Packages to Display Information About Schema Object Space Usage......19-29 Schema Objects Space Usage Data Dictionary Views..............................................................19-30

xv

oracle 11g管理篇

Capacity Planning for Database Objects .......................................................................................19-32 Estimating the Space Use of a Table ..........................................................................................19-32 Estimating the Space Use of an Index .......................................................................................19-33 Obtaining Object Growth Trends ..............................................................................................19-33 20 Managing Tables

About Tables...........................................................................................................................................20-1 Guidelines for Managing Tables........................................................................................................20-2 Design Tables Before Creating Them...........................................................................................20-2 Specify the Type of Table to Create..............................................................................................20-3 Specify the Location of Each Table...............................................................................................20-4 Consider Parallelizing Table Creation.........................................................................................20-4 Consider Using NOLOGGING When Creating Tables ............................................................20-4 Consider Using Table Compression.............................................................................................20-5 Consider Encrypting Columns That Contain Sensitive Data.................................................20-12 Understand Deferred Segment Creation...................................................................................20-13 Materializing Segments................................................................................................................20-15 Estimate Table Size and Plan Accordingly................................................................................20-16 Restrictions to Consider When Creating Tables.......................................................................20-16 Creating Tables....................................................................................................................................20-17 Example: Creating a Table...........................................................................................................20-17 Creating a Temporary Table........................................................................................................20-18 Parallelizing Table Creation........................................................................................................20-19 Loading Tables.....................................................................................................................................20-20 Methods for Loading Tables........................................................................................................20-20 Improving INSERT Performance with Direct-Path INSERT..................................................20-21 Using Conventional Inserts to Load Tables..............................................................................20-26 Avoiding Bulk INSERT Failures with DML Error Logging...................................................20-26 Automatically Collecting Statistics on Tables...............................................................................20-30 Altering Tables.....................................................................................................................................20-30 Reasons for Using the ALTER TABLE Statement....................................................................20-31 Altering Physical Attributes of a Table......................................................................................20-32 Moving a Table to a New Segment or Tablespace...................................................................20-32 Manually Allocating Storage for a Table...................................................................................20-33 Modifying an Existing Column Definition................................................................................20-33 Adding Table Columns................................................................................................................20-33 Renaming Table Columns............................................................................................................20-34 Dropping Table Columns ...........................................................................................................20-34 Placing a Table in Read-Only Mode...........................................................................................20-36 Redefining Tables Online..................................................................................................................20-37 Features of Online Table Redefinition.......................................................................................20-38 Performing Online Redefinition with DBMS_REDEFINITION.............................................20-38 Results of the Redefinition Process.............................................................................................20-42 Performing Intermediate Synchronization................................................................................20-43 Aborting Online Table Redefinition and Cleaning Up After Errors.....................................20-43 Restrictions for Online Redefinition of Tables..........................................................................20-43 Online Redefinition of a Single Partition...................................................................................20-45 xvi

oracle 11g管理篇

Online Table Redefinition Examples..........................................................................................20-46 Privileges Required for the DBMS_REDEFINITION Package...............................................20-52 Researching and Reversing Erroneous Table Changes................................................................20-52 Recovering Tables Using Oracle Flashback Table........................................................................20-53 Dropping Tables..................................................................................................................................20-54 Using Flashback Drop and Managing the Recycle Bin...............................................................20-54 What Is the Recycle Bin?..............................................................................................................20-55 Enabling and Disabling the Recycle Bin....................................................................................20-56 Viewing and Querying Objects in the Recycle Bin..................................................................20-56 Purging Objects in the Recycle Bin.............................................................................................20-57 Restoring Tables from the Recycle Bin.......................................................................................20-58 Managing Index-Organized Tables ................................................................................................20-59 What Are Index-Organized Tables?...........................................................................................20-59 Creating Index-Organized Tables...............................................................................................20-60 Maintaining Index-Organized Tables........................................................................................20-64 Creating Secondary Indexes on Index-Organized Tables.......................................................20-66 Analyzing Index-Organized Tables...........................................................................................20-67 Using the ORDER BY Clause with Index-Organized Tables..................................................20-68 Converting Index-Organized Tables to Regular Tables..........................................................20-68 Managing External Tables.................................................................................................................20-68 About External Tables..................................................................................................................20-69 Creating External Tables..............................................................................................................20-70 Altering External Tables...............................................................................................................20-72 Preprocessing External Tables.....................................................................................................20-73 Dropping External Tables............................................................................................................20-74 System and Object Privileges for External Tables....................................................................20-74 Tables Data Dictionary Views ..........................................................................................................20-75 21Managing Indexes

About Indexes........................................................................................................................................21-1 Guidelines for Managing Indexes.....................................................................................................21-2 Create Indexes After Inserting Table Data..................................................................................21-2 Index the Correct Tables and Columns.......................................................................................21-3 Order Index Columns for Performance.......................................................................................21-3 Limit the Number of Indexes for Each Table..............................................................................21-4 Drop Indexes That Are No Longer Required ............................................................................21-4 Indexes and Deferred Segment Creation.....................................................................................21-4 Estimate Index Size and Set Storage Parameters........................................................................21-4 Specify the Tablespace for Each Index.........................................................................................21-5 Consider Parallelizing Index Creation.........................................................................................21-5 Consider Creating Indexes with NOLOGGING........................................................................21-5 Understand When to Use Unusable or Invisible Indexes.........................................................21-6 Consider Costs and Benefits of Coalescing or Rebuilding Indexes.........................................21-7 Consider Cost Before Disabling or Dropping Constraints........................................................21-8 Creating Indexes....................................................................................................................................21-8 Creating an Index Explicitly..........................................................................................................21-9 Creating a Unique Index Explicitly..............................................................................................21-9

xvii

oracle 11g管理篇

Creating an Index Associated with a Constraint........................................................................21-9 Creating a Large Index.................................................................................................................21-11 Creating an Index Online.............................................................................................................21-11 Creating a Function-Based Index................................................................................................21-11 Creating a Key-Compressed Index.............................................................................................21-12 Creating an Unusable Index........................................................................................................21-13 Creating an Invisible Index..........................................................................................................21-14 Altering Indexes..................................................................................................................................21-15 Altering Storage Characteristics of an Index.............................................................................21-16 Rebuilding an Existing Index......................................................................................................21-16 Making an Index Unusable..........................................................................................................21-17 Making an Index Invisible...........................................................................................................21-18 Renaming an Index.......................................................................................................................21-19 Monitoring Index Usage..............................................................................................................21-19 Monitoring Space Use of Indexes....................................................................................................21-20 Dropping Indexes................................................................................................................................21-20 Indexes Data Dictionary Views........................................................................................................21-21 22Managing Clusters

About Clusters.......................................................................................................................................22-1 Guidelines for Managing Clusters....................................................................................................22-2 Choose Appropriate Tables for the Cluster................................................................................22-3 Choose Appropriate Columns for the Cluster Key....................................................................22-3 Specify the Space Required by an Average Cluster Key and Its Associated Rows ..............22-3 Specify the Location of Each Cluster and Cluster Index Rows................................................22-4 Estimate Cluster Size and Set Storage Parameters.....................................................................22-4 Creating Clusters...................................................................................................................................22-4 Creating Clustered Tables..............................................................................................................22-5 Creating Cluster Indexes................................................................................................................22-5 Altering Clusters...................................................................................................................................22-6 Altering Clustered Tables..............................................................................................................22-6 Altering Cluster Indexes................................................................................................................22-7 Dropping Clusters.................................................................................................................................22-7 Dropping Clustered Tables............................................................................................................22-8 Dropping Cluster Indexes..............................................................................................................22-8 Clusters Data Dictionary Views.........................................................................................................22-8 23 Managing Hash Clusters

About Hash Clusters.............................................................................................................................23-1 When to Use Hash Clusters.................................................................................................................23-2 Situations Where Hashing Is Useful.............................................................................................23-2 Situations Where Hashing Is Not Advantageous......................................................................23-2 Creating Hash Clusters........................................................................................................................23-2 Creating a Sorted Hash Cluster.....................................................................................................23-3 Creating Single-Table Hash Clusters...........................................................................................23-6 Controlling Space Use Within a Hash Cluster............................................................................23-6 Estimating Size Required by Hash Clusters................................................................................23-8 xviii

oracle 11g管理篇

Altering Hash Clusters.........................................................................................................................23-9 Dropping Hash Clusters......................................................................................................................23-9 Hash Clusters Data Dictionary Views...............................................................................................23-9 24 Managing Views, Sequences, and Synonyms

Managing Views....................................................................................................................................24-1 About Views....................................................................................................................................24-1 Creating Views................................................................................................................................24-2 Replacing Views..............................................................................................................................24-4 Using Views in Queries .................................................................................................................24-4 Updating a Join View.....................................................................................................................24-6 Altering Views...............................................................................................................................24-12 Dropping Views............................................................................................................................24-12 Managing Sequences..........................................................................................................................24-12 About Sequences...........................................................................................................................24-12 Creating Sequences.......................................................................................................................24-13 Altering Sequences........................................................................................................................24-13 Using Sequences............................................................................................................................24-14 Dropping Sequences.....................................................................................................................24-16 Managing Synonyms..........................................................................................................................24-17 About Synonyms...........................................................................................................................24-17 Creating Synonyms.......................................................................................................................24-17 Using Synonyms in DML Statements .......................................................................................24-18 Dropping Synonyms.....................................................................................................................24-18 Views, Synonyms, and Sequences Data Dictionary Views........................................................24-19 25 Repairing Corrupted Data

Options for Repairing Data Block Corruption................................................................................25-1 About the DBMS_REPAIR Package..................................................................................................25-1 DBMS_REPAIR Procedures...........................................................................................................25-2 Limitations and Restrictions..........................................................................................................25-2 Using the DBMS_REPAIR Package...................................................................................................25-2 Task 1: Detect and Report Corruptions.......................................................................................25-3 Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR..........................................25-4 Task 3: Make Objects Usable.........................................................................................................25-5 Task 4: Repair Corruptions and Rebuild Lost Data...................................................................25-5 DBMS_REPAIR Examples...................................................................................................................25-5 Examples: Building a Repair Table or Orphan Key Table........................................................25-6 Example: Detecting Corruption....................................................................................................25-7 Example: Fixing Corrupt Blocks...................................................................................................25-8 Example: Finding Index Entries Pointing to Corrupt Data Blocks..........................................25-9 Example: Skipping Corrupt Blocks..............................................................................................25-9 Part IV Database Resource Management and Task Scheduling

xix

oracle 11g管理篇

26 Managing Automated Database Maintenance Tasks

About Automated Maintenance Tasks..............................................................................................26-1 About Maintenance Windows ...........................................................................................................26-2 Configuring Automated Maintenance Tasks...................................................................................26-3 Enabling and Disabling Maintenance Tasks for all Maintenance Windows..........................26-3 Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows................26-4 Configuring Maintenance Windows.................................................................................................26-4 Modifying a Maintenance Window..............................................................................................26-4 Creating a New Maintenance Window.......................................................................................26-4 Removing a Maintenance Window..............................................................................................26-5 Configuring Resource Allocations for Automated Maintenance Tasks.....................................26-5 About Resource Allocations for Automated Maintenance Tasks............................................26-6 Changing Resource Allocations for Automated Maintenance Tasks......................................26-6 Automated Maintenance Tasks Reference.......................................................................................26-7 Predefined Maintenance Windows..............................................................................................26-7 Automated Maintenance Tasks Database Dictionary Views....................................................26-7 27Managing Resources with Oracle Database Resource Manager

About Oracle Database Resource Manager......................................................................................27-1 What Solutions Does the Resource Manager Provide for Workload Management?............27-2 Elements of the Resource Manager..............................................................................................27-3 About Resource Manager Administration Privileges................................................................27-6 Assigning Sessions to Resource Consumer Groups.......................................................................27-7 Overview of Assigning Sessions to Resource Consumer Groups...........................................27-7 Assigning an Initial Resource Consumer Group........................................................................27-8 Specifying Session-to–Consumer Group Mapping Rules.........................................................27-8 Switching Resource Consumer Groups.....................................................................................27-12 Specifying Automatic Resource Consumer Group Switching...............................................27-14 Granting and Revoking the Switch Privilege............................................................................27-16 The Types of Resources Managed by the Resource Manager....................................................27-17 CPU.................................................................................................................................................27-18 Degree of Parallelism Limit.........................................................................................................27-20 Parallel Target Percentage...........................................................................................................27-20 Parallel Queue Timeout................................................................................................................27-22 Active Session Pool with Queuing.............................................................................................27-23 Automatic Consumer Group Switching....................................................................................27-23 Canceling SQL and Terminating Sessions.................................................................................27-23 Execution Time Limit...................................................................................................................27-23 Undo Pool.......................................................................................................................................27-23 Idle Time Limit..............................................................................................................................27-24 Creating a Simple Resource Plan ....................................................................................................27-24 Creating a Complex Resource Plan..................................................................................................27-25 About the Pending Area..............................................................................................................27-26 Creating a Pending Area..............................................................................................................27-26 Creating Resource Consumer Groups ......................................................................................27-27 Creating a Resource Plan.............................................................................................................27-27 Creating Resource Plan Directives ............................................................................................27-28 xx

oracle 11g管理篇

Validating the Pending Area.......................................................................................................27-33 Submitting the Pending Area......................................................................................................27-34 Clearing the Pending Area..........................................................................................................27-35 Enabling Oracle Database Resource Manager and Switching Plans........................................27-35 Putting It All Together: Oracle Database Resource Manager Examples..................................27-37 Multilevel Plan Example..............................................................................................................27-37 Examples of Using the Maximum Utilization Limit Attribute...............................................27-39 Example of Using Several Resource Allocation Methods.......................................................27-45 Example of Managing Parallel Statements Using Directive Attributes................................27-45 An Oracle-Supplied Mixed Workload Plan..............................................................................27-47 Managing Multiple Database Instances on a Single Server.......................................................27-48 About Instance Caging.................................................................................................................27-48 Enabling Instance Caging............................................................................................................27-49 Maintaining Consumer Groups, Plans, and Directives...............................................................27-49 Updating a Consumer Group......................................................................................................27-50 Deleting a Consumer Group.......................................................................................................27-50 Updating a Plan.............................................................................................................................27-50 Deleting a Plan...............................................................................................................................27-50 Updating a Resource Plan Directive..........................................................................................27-51 Deleting a Resource Plan Directive............................................................................................27-51 Viewing Database Resource Manager Configuration and Status.............................................27-51 Viewing Consumer Groups Granted to Users or Roles..........................................................27-52 Viewing Plan Information...........................................................................................................27-52 Viewing Current Consumer Groups for Sessions....................................................................27-52 Viewing the Currently Active Plans...........................................................................................27-53 Monitoring Oracle Database Resource Manager..........................................................................27-53 Interacting with Operating-System Resource Control................................................................27-57 Guidelines for Using Operating-System Resource Control....................................................27-57 Oracle Database Resource Manager Reference.............................................................................27-58 Predefined Resource Plans and Consumer Groups.................................................................27-58 Predefined Consumer Group Mapping Rules..........................................................................27-60 Resource Manager Data Dictionary Views...............................................................................27-60 28 Oracle Scheduler Concepts

Overview of Oracle Scheduler............................................................................................................28-1 About Jobs and Supporting Scheduler Objects..............................................................................28-3 Programs..........................................................................................................................................28-4 Schedules..........................................................................................................................................28-4 Jobs....................................................................................................................................................28-5 Destinations.....................................................................................................................................28-6 File Watchers....................................................................................................................................28-7 Credentials.......................................................................................................................................28-7 Chains...............................................................................................................................................28-8 Job Classes......................................................................................................................................28-10 Windows.........................................................................................................................................28-11 Groups............................................................................................................................................28-14 More About Jobs..................................................................................................................................28-15

xxi

本文来源：https://www.bwwdw.com/article/fgl1.html