Oracle 11gR2 数据库管理员实战指南

oracle

5星 · 超过95%的资源需积分: 11 174 浏览量更新于2024-07-24 收藏 14.73MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"Oracle® Database Administrator's Guide 11g Release 2 (11.2) E10595-06" Oracle 11g R2数据库管理员官方指南是Oracle公司发布的一本详尽的技术参考资料，专为管理Oracle 11g第二版数据库系统设计。这本书由Steve Fogel作为主要作者，以及一众贡献者共同编写，包含了2001年至2009年间的知识更新。它涵盖了数据库管理的各种核心主题，旨在帮助DBA（数据库管理员）有效管理和优化Oracle数据库环境。本书的关键知识点包括但不限于以下部分： 1. **安装与配置**：介绍如何在不同的操作系统平台上安装Oracle数据库软件，以及配置数据库实例、网络和服务。这涉及到Oracle Database Configuration Assistant (DBCA) 和其他工具的使用。 2. **数据库创建与管理**：详述如何创建、启动、关闭和迁移数据库，以及管理数据文件、控制文件、重做日志文件和系统表空间等。 3. **性能监控与调优**：讲解了使用SQL*Plus、Enterprise Manager (EM) 和其他工具进行性能分析和调优的方法，包括SQL查询优化、数据库缓存管理、索引策略等。 4. **安全性**：讨论了用户管理和权限控制，如角色、权限、加密、审计和访问控制列表（ACLs）的设置。 5. **备份与恢复**：介绍了Oracle的RMAN（Recovery Manager）工具，用于执行数据库备份、增量备份和恢复操作，以及数据保护策略如闪回技术和归档日志模式。 6. **高可用性与故障恢复**：涉及RAC（Real Application Clusters）集群技术，使数据库能在多台服务器上运行，提供故障切换和负载均衡功能。 7. **存储管理**：讨论了ASM（Automatic Storage Management）自动存储管理，简化存储设备的管理和数据分布。 8. **数据仓库与OLAP**：简述如何利用Oracle的数据仓库特性支持决策支持系统，如星型和雪花模型的构建，以及OLAP（在线分析处理）操作。 9. **数据库维护**：包括日常的数据库维护任务，如空间管理、数据库维护计划的设定、数据库的升级和迁移。 10. **故障诊断与问题解决**：提供了识别和解决数据库问题的策略，包括日志分析、错误代码解析和使用跟踪文件。 11. **网格计算与Oracle Grid Infrastructure**：介绍了Oracle的网格计算概念，以及Grid Infrastructure组件的管理，如OCR（Oracle Cluster Registry）和 Voting Disks。 12. **自动化管理**：阐述了通过EM Cloud Control实现的数据库自动化管理，包括自动备份、性能监控和警报设置。 13. **Oracle数据库服务**：讲解了如何配置和管理数据库服务，以提供特定的应用程序需求。通过阅读这本官方指南，Oracle数据库管理员可以获取全面的理论知识和实践经验，以便更好地管理和维护Oracle 11g R2数据库环境，确保其高效、稳定和安全运行。

资源详情

资源推荐

xvi

Placing a Table in Read-Only Mode........................................................................................... 19-28

Redefining Tables Online.................................................................................................................. 19-29

Features of Online Table Redefinition ....................................................................................... 19-30

Performing Online Redefinition with DBMS_REDEFINITION............................................. 19-31

Results of the Redefinition Process............................................................................................. 19-35

Performing Intermediate Synchronization................................................................................ 19-36

Aborting Online Table Redefinition and Cleaning Up After Errors ..................................... 19-36

Restrictions for Online Redefinition of Tables.......................................................................... 19-36

Online Redefinition of a Single Partition................................................................................... 19-37

Online Table Redefinition Examples.......................................................................................... 19-39

Privileges Required for the DBMS_REDEFINITION Package............................................... 19-45

Researching and Reversing Erroneous Table Changes................................................................ 19-45

Recovering Tables Using Oracle Flashback Table ........................................................................ 19-46

Dropping Tables .................................................................................................................................. 19-46

Using Flashback Drop and Managing the Recycle Bin ............................................................... 19-47

What Is the Recycle Bin? .............................................................................................................. 19-48

Enabling and Disabling the Recycle Bin.................................................................................... 19-49

Viewing and Querying Objects in the Recycle Bin .................................................................. 19-49

Purging Objects in the Recycle Bin............................................................................................. 19-50

Restoring Tables from the Recycle Bin....................................................................................... 19-50

Managing Index-Organized Tables ................................................................................................ 19-52

What Are Index-Organized Tables?........................................................................................... 19-52

Creating Index-Organized Tables............................................................................................... 19-53

Maintaining Index-Organized Tables ........................................................................................ 19-57

Creating Secondary Indexes on Index-Organized Tables....................................................... 19-59

Analyzing Index-Organized Tables ........................................................................................... 19-60

Using the ORDER BY Clause with Index-Organized Tables.................................................. 19-61

Converting Index-Organized Tables to Regular Tables.......................................................... 19-61

Managing External Tables ................................................................................................................. 19-61

About External Tables .................................................................................................................. 19-62

Creating External Tables.............................................................................................................. 19-63

Altering External Tables............................................................................................................... 19-65

Preprocessing External Tables..................................................................................................... 19-66

Dropping External Tables............................................................................................................ 19-67

System and Object Privileges for External Tables.................................................................... 19-68

Tables Data Dictionary Views .......................................................................................................... 19-68

20 Managing Indexes

About Indexes ........................................................................................................................................ 20-1

Guidelines for Managing Indexes ..................................................................................................... 20-2

Create Indexes After Inserting Table Data .................................................................................. 20-2

Index the Correct Tables and Columns ....................................................................................... 20-3

Order Index Columns for Performance....................................................................................... 20-3

Limit the Number of Indexes for Each Table.............................................................................. 20-4

Drop Indexes That Are No Longer Required ............................................................................ 20-4

Estimate Index Size and Set Storage Parameters........................................................................ 20-4

Specify the Tablespace for Each Index......................................................................................... 20-5

xvii

Consider Parallelizing Index Creation......................................................................................... 20-5

Consider Creating Indexes with NOLOGGING ........................................................................ 20-5

Understand When to Use Unusable or Invisible Indexes ......................................................... 20-5

Consider Costs and Benefits of Coalescing or Rebuilding Indexes......................................... 20-7

Consider Cost Before Disabling or Dropping Constraints........................................................ 20-8

Creating Indexes.................................................................................................................................... 20-8

Creating an Index Explicitly.......................................................................................................... 20-8

Creating a Unique Index Explicitly .............................................................................................. 20-9

Creating an Index Associated with a Constraint........................................................................ 20-9

Collecting Incidental Statistics when Creating an Index......................................................... 20-10

Creating a Large Index................................................................................................................. 20-11

Creating an Index Online............................................................................................................. 20-11

Creating a Function-Based Index................................................................................................ 20-11

Creating a Key-Compressed Index............................................................................................. 20-12

Creating an Unusable Index........................................................................................................ 20-13

Creating an Invisible Index.......................................................................................................... 20-14

Altering Indexes .................................................................................................................................. 20-14

Altering Storage Characteristics of an Index............................................................................. 20-15

Rebuilding an Existing Index ...................................................................................................... 20-15

Making an Index Unusable.......................................................................................................... 20-16

Making an Index Invisible ........................................................................................................... 20-17

Renaming an Index....................................................................................................................... 20-18

Monitoring Index Usage .............................................................................................................. 20-18

Monitoring Space Use of Indexes .................................................................................................... 20-18

Dropping Indexes................................................................................................................................ 20-19

Indexes Data Dictionary Views ........................................................................................................ 20-19

21 Managing Clusters

About Clusters ....................................................................................................................................... 21-1

Guidelines for Managing Clusters .................................................................................................... 21-2

Choose Appropriate Tables for the Cluster ................................................................................ 21-3

Choose Appropriate Columns for the Cluster Key.................................................................... 21-3

Specify the Space Required by an Average Cluster Key and Its Associated Rows .............. 21-3

Specify the Location of Each Cluster and Cluster Index Rows ................................................ 21-4

Estimate Cluster Size and Set Storage Parameters..................................................................... 21-4

Creating Clusters ................................................................................................................................... 21-4

Creating Clustered Tables.............................................................................................................. 21-5

Creating Cluster Indexes................................................................................................................ 21-5

Altering Clusters ................................................................................................................................... 21-6

Altering Clustered Tables .............................................................................................................. 21-6

Altering Cluster Indexes ................................................................................................................ 21-7

Dropping Clusters................................................................................................................................. 21-7

Dropping Clustered Tables............................................................................................................ 21-8

Dropping Cluster Indexes.............................................................................................................. 21-8

Clusters Data Dictionary Views ......................................................................................................... 21-8

xviii

22 Managing Hash Clusters

About Hash Clusters............................................................................................................................. 22-1

When to Use Hash Clusters................................................................................................................. 22-2

Situations Where Hashing Is Useful............................................................................................. 22-2

Situations Where Hashing Is Not Advantageous ...................................................................... 22-2

Creating Hash Clusters ........................................................................................................................ 22-2

Creating a Sorted Hash Cluster..................................................................................................... 22-3

Creating Single-Table Hash Clusters ........................................................................................... 22-4

Controlling Space Use Within a Hash Cluster............................................................................ 22-4

Estimating Size Required by Hash Clusters................................................................................ 22-7

Altering Hash Clusters......................................................................................................................... 22-7

Dropping Hash Clusters ...................................................................................................................... 22-7

Hash Clusters Data Dictionary Views............................................................................................... 22-8

23 Managing Views, Sequences, and Synonyms

Managing Views.................................................................................................................................... 23-1

About Views .................................................................................................................................... 23-1

Creating Views ................................................................................................................................ 23-2

Replacing Views.............................................................................................................................. 23-4

Using Views in Queries ................................................................................................................. 23-4

Updating a Join View ..................................................................................................................... 23-6

Altering Views............................................................................................................................... 23-12

Dropping Views ............................................................................................................................ 23-12

Managing Sequences.......................................................................................................................... 23-12

About Sequences ........................................................................................................................... 23-12

Creating Sequences....................................................................................................................... 23-13

Altering Sequences........................................................................................................................ 23-13

Using Sequences............................................................................................................................ 23-14

Dropping Sequences..................................................................................................................... 23-16

Managing Synonyms.......................................................................................................................... 23-17

About Synonyms........................................................................................................................... 23-17

Creating Synonyms....................................................................................................................... 23-17

Using Synonyms in DML Statements ....................................................................................... 23-18

Dropping Synonyms..................................................................................................................... 23-18

Views, Synonyms, and Sequences Data Dictionary Views ........................................................ 23-19

24 Repairing Corrupted Data

Options for Repairing Data Block Corruption................................................................................ 24-1

About the DBMS_REPAIR Package .................................................................................................. 24-1

DBMS_REPAIR Procedures........................................................................................................... 24-2

Limitations and Restrictions.......................................................................................................... 24-2

Using the DBMS_REPAIR Package................................................................................................... 24-2

Task 1: Detect and Report Corruptions ....................................................................................... 24-3

Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR.......................................... 24-4

Task 3: Make Objects Usable ......................................................................................................... 24-5

Task 4: Repair Corruptions and Rebuild Lost Data................................................................... 24-5

xix

DBMS_REPAIR Examples................................................................................................................... 24-5

Examples: Building a Repair Table or Orphan Key Table ........................................................ 24-6

Example: Detecting Corruption .................................................................................................... 24-7

Example: Fixing Corrupt Blocks ................................................................................................... 24-8

Example: Finding Index Entries Pointing to Corrupt Data Blocks.......................................... 24-9

Example: Skipping Corrupt Blocks .............................................................................................. 24-9

Part IV Database Resource Management and Task Scheduling

25 Managing Automated Database Maintenance Tasks

About Automated Maintenance Tasks.............................................................................................. 25-1

About Maintenance Windows ........................................................................................................... 25-2

Configuring Automated Maintenance Tasks................................................................................... 25-3

Enabling and Disabling Maintenance Tasks for all Maintenance Windows.......................... 25-3

Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows ................ 25-4

Configuring Maintenance Windows ................................................................................................. 25-4

Modifying a Maintenance Window.............................................................................................. 25-4

Creating a New Maintenance Window ....................................................................................... 25-4

Removing a Maintenance Window.............................................................................................. 25-5

Configuring Resource Allocations for Automated Maintenance Tasks..................................... 25-5

About Resource Allocations for Automated Maintenance Tasks............................................ 25-6

Changing Resource Allocations for Automated Maintenance Tasks...................................... 25-6

Automated Maintenance Tasks Reference ....................................................................................... 25-7

Predefined Maintenance Windows .............................................................................................. 25-7

Automated Maintenance Tasks Database Dictionary Views.................................................... 25-7

26 Managing Resource Allocation with Oracle Database Resource Manager

About Oracle Database Resource Manager...................................................................................... 26-1

What Problems Does the Resource Manager Address? ............................................................ 26-2

How Does the Resource Manager Address These Problems?.................................................. 26-2

Elements of the Resource Manager .............................................................................................. 26-3

About Resource Allocation Methods ........................................................................................... 26-6

About Resource Manager Administration Privileges.............................................................. 26-10

Creating a Simple Resource Plan .................................................................................................... 26-10

Creating a Complex Resource Plan.................................................................................................. 26-12

About the Pending Area .............................................................................................................. 26-13

Creating a Pending Area.............................................................................................................. 26-13

Creating Resource Consumer Groups ...................................................................................... 26-13

Creating a Resource Plan ............................................................................................................. 26-14

Creating Resource Plan Directives ............................................................................................ 26-15

Validating the Pending Area....................................................................................................... 26-19

Submitting the Pending Area...................................................................................................... 26-21

Clearing the Pending Area .......................................................................................................... 26-21

Assigning Sessions to Resource Consumer Groups..................................................................... 26-21

Overview of Assigning Sessions to Resource Consumer Groups ......................................... 26-22

Assigning an Initial Resource Consumer Group...................................................................... 26-22

Manually Switching Resource Consumer Groups................................................................... 26-22

Specifying Automatic Resource Consumer Group Switching ............................................... 26-23

Specifying Session-to–Consumer Group Mapping Rules....................................................... 26-25

Enabling Users or Applications to Manually Switch Consumer Groups............................. 26-29

Granting and Revoking the Switch Privilege............................................................................ 26-30

Enabling Oracle Database Resource Manager and Switching Plans........................................ 26-32

Putting It All Together: Oracle Database Resource Manager Examples .................................. 26-33

Multilevel Plan Example.............................................................................................................. 26-34

Examples of Using the Maximum Utilization Limit Attribute............................................... 26-36

Example of Using Several Resource Allocation Methods....................................................... 26-39

An Oracle-Supplied Mixed Workload Plan .............................................................................. 26-39

Managing Multiple Database Instances on a Single Server....................................................... 26-40

About Instance Caging................................................................................................................. 26-40

Enabling Instance Caging ............................................................................................................ 26-41

Maintaining Consumer Groups, Plans, and Directives............................................................... 26-42

Updating a Consumer Group...................................................................................................... 26-42

Deleting a Consumer Group ....................................................................................................... 26-42

Updating a Plan............................................................................................................................. 26-42

Deleting a Plan............................................................................................................................... 26-43

Updating a Resource Plan Directive .......................................................................................... 26-43

Deleting a Resource Plan Directive ............................................................................................ 26-44

Viewing Database Resource Manager Configuration and Status ............................................. 26-44

Viewing Consumer Groups Granted to Users or Roles .......................................................... 26-44

Viewing Plan Information ........................................................................................................... 26-44

Viewing Current Consumer Groups for Sessions.................................................................... 26-45

Viewing the Currently Active Plans........................................................................................... 26-45

Monitoring Oracle Database Resource Manager.......................................................................... 26-45

Interacting with Operating-System Resource Control ................................................................ 26-48

Guidelines for Using Operating-System Resource Control.................................................... 26-48

Oracle Database Resource Manager Reference............................................................................. 26-49

Predefined Resource Plans and Consumer Groups................................................................. 26-49

Predefined Consumer Group Mapping Rules.......................................................................... 26-51

Resource Manager Data Dictionary Views ............................................................................... 26-52

27 Oracle Scheduler Concepts

Overview of Oracle Scheduler............................................................................................................ 27-1

About Jobs and Supporting Scheduler Objects .............................................................................. 27-3

Programs .......................................................................................................................................... 27-4

Schedules.......................................................................................................................................... 27-4

Jobs .................................................................................................................................................... 27-4

Destinations ..................................................................................................................................... 27-6

File Watchers.................................................................................................................................... 27-7

Credentials ....................................................................................................................................... 27-7

Chains ............................................................................................................................................... 27-7

Job Classes........................................................................................................................................ 27-9

Windows......................................................................................................................................... 27-10

Groups ............................................................................................................................................ 27-14