Oracle数据库11g Release2管理员指南

需积分: 10 181 浏览量更新于2024-07-26 收藏 16.27MB PDF 举报

"Oracle Administrator Guide 11g Release 2 (11.2) E25494-03" Oracle数据库管理员指南是针对Oracle 11g Release 2 (11.2)版本的一份详尽的技术文档，旨在帮助数据库管理员（DBA）有效地管理和维护Oracle数据库系统。这份指南由Steve Fogel为主要作者，并有多个贡献者参与编写，涵盖了从基础到高级的多种Oracle数据库管理主题。在Oracle数据库管理员指南中，你将学习到以下关键知识点： 1. **安装与配置**: 包括如何安装Oracle数据库软件、创建数据库实例、配置网络服务以及设置安全选项。这一步骤对于初次接触Oracle DBA工作的人员尤为重要。 2. **数据库管理**: 深入了解如何创建、扩展和调整数据库的物理结构，如数据文件、控制文件、重做日志文件和表空间。此外，还涵盖了数据库备份与恢复策略。 3. **性能优化**: 学习监控和分析数据库性能，使用SQL Tuning Advisor和Automatic Workload Repository（AWR）进行性能问题诊断。还包括使用PGA和SGA内存结构的优化。 4. **安全性管理**: 掌握用户权限和角色的管理，以及数据库加密技术，例如 Transparent Data Encryption (TDE)。理解Oracle的审计功能，确保合规性和数据保护。 5. **备份与恢复**: 了解RMAN（ Recovery Manager）的使用，实现数据库的完整备份、增量备份和逻辑备份。学习如何制定恢复计划，应对不同级别的故障。 6. **高可用性**: 研究Real Application Clusters (RAC) 和其他高可用性解决方案，如Data Guard，以确保在硬件故障或灾难情况下保持服务连续性。 7. **存储管理**: 学习Oracle的存储选项，包括ASM（Automatic Storage Management）和表空间管理，以及如何优化I/O性能。 8. **数据库升级与迁移**: 了解如何从旧版本的Oracle数据库升级到11g Release 2，以及数据迁移策略。 9. **故障诊断与排错**: 掌握如何利用日志文件、警报日志和跟踪文件来识别和解决数据库中的问题。 10. **自动化管理**: 使用Oracle Enterprise Manager等工具实现数据库的自动化管理，减少手动干预并提高效率。这份指南不仅适合新手DBA，也对有经验的数据库管理员有价值，因为它提供了关于Oracle 11g Release 2的最新特性和最佳实践。通过深入阅读和实践，你可以提升在Oracle数据库管理方面的专业技能。

xvi

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-13

Understand Deferred Segment Creation ................................................................................... 20-14

Materializing Segments................................................................................................................ 20-16

Estimate Table Size and Plan Accordingly................................................................................ 20-16

Restrictions to Consider When Creating Tables....................................................................... 20-17

Creating Tables.................................................................................................................................... 20-17

Example: Creating a Table........................................................................................................... 20-18

Creating a Temporary Table........................................................................................................ 20-19

Parallelizing Table Creation ........................................................................................................ 20-20

Loading Tables..................................................................................................................................... 20-21

Methods for Loading Tables........................................................................................................ 20-21

Improving INSERT Performance with Direct-Path INSERT.................................................. 20-22

Using Conventional Inserts to Load Tables .............................................................................. 20-27

Avoiding Bulk INSERT Failures with DML Error Logging ................................................... 20-27

Automatically Collecting Statistics on Tables............................................................................... 20-31

Altering Tables .................................................................................................................................... 20-31

Reasons for Using the ALTER TABLE Statement .................................................................... 20-32

Altering Physical Attributes of a Table...................................................................................... 20-33

Moving a Table to a New Segment or Tablespace ................................................................... 20-33

Manually Allocating Storage for a Table................................................................................... 20-34

Modifying an Existing Column Definition................................................................................ 20-34

Adding Table Columns................................................................................................................ 20-34

Renaming Table Columns............................................................................................................ 20-35

Dropping Table Columns ........................................................................................................... 20-35

Placing a Table in Read-Only Mode........................................................................................... 20-37

Redefining Tables Online ................................................................................................................. 20-38

Features of Online Table Redefinition .......................................................................................

20-39

Performing Online Redefinition with DBMS_REDEFINITION............................................. 20-39

Results of the Redefinition Process............................................................................................. 20-43

Performing Intermediate Synchronization................................................................................ 20-44

Aborting Online Table Redefinition and Cleaning Up After Errors ..................................... 20-44

Restrictions for Online Redefinition of Tables.......................................................................... 20-44

Online Redefinition of a Single Partition................................................................................... 20-46

xvii

Online Table Redefinition Examples.......................................................................................... 20-47

Privileges Required for the DBMS_REDEFINITION Package............................................... 20-53

Researching and Reversing Erroneous Table Changes ............................................................... 20-53

Recovering Tables Using Oracle Flashback Table ....................................................................... 20-54

Dropping Tables.................................................................................................................................. 20-55

Using Flashback Drop and Managing the Recycle Bin ............................................................... 20-55

What Is the Recycle Bin? .............................................................................................................. 20-56

Enabling and Disabling the Recycle Bin.................................................................................... 20-57

Viewing and Querying Objects in the Recycle Bin .................................................................. 20-57

Purging Objects in the Recycle Bin............................................................................................. 20-58

Restoring Tables from the Recycle Bin....................................................................................... 20-59

Managing Index-Organized Tables ................................................................................................ 20-60

What Are Index-Organized Tables?........................................................................................... 20-60

Creating Index-Organized Tables............................................................................................... 20-61

Maintaining Index-Organized Tables ........................................................................................ 20-65

Creating Secondary Indexes on Index-Organized Tables....................................................... 20-67

Analyzing Index-Organized Tables ........................................................................................... 20-68

Using the ORDER BY Clause with Index-Organized Tables.................................................. 20-69

Converting Index-Organized Tables to Regular Tables.......................................................... 20-69

Managing External Tables................................................................................................................. 20-69

About External Tables .................................................................................................................. 20-70

Creating External Tables.............................................................................................................. 20-71

Altering External Tables............................................................................................................... 20-73

Preprocessing External Tables..................................................................................................... 20-74

Dropping External Tables............................................................................................................ 20-75

System and Object Privileges for External Tables.................................................................... 20-75

Tables Data Dictionary Views ......................................................................................................... 20-76

21 Managing 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

xviii

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-15

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-19

Dropping Indexes................................................................................................................................ 21-20

Indexes Data Dictionary Views........................................................................................................ 21-21

22 Managing 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

xix

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

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

27 Managing 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-8

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-21

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-24

Undo Pool....................................................................................................................................... 27-24

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-27

Creating Resource Consumer Groups ...................................................................................... 27-27

Creating a Resource Plan ............................................................................................................. 27-28

Creating Resource Plan Directives ............................................................................................ 27-29

剩余1071页未读，继续阅读

ysmgigi

粉丝: 8

Oracle数据库11g Release2管理员指南

oracle database administrator guide 中文版第一章

Oracle Balanced Scorecard Administrator Guide Release 11i

b14231(Oracle Administrator's Guide)

Veritas NetBackup for Oracle Administrator’s Guide for Windows

Veritas NetBackup for Oracle Administrator’s Guide for UNIX and

Oracle Coherence administrator's guide

121wfag Oracle workflow Administrator's Guide

Oracle Workflow Administrator’s Guide Release 2.6.4

Oracle Administrator Tools Techonology

GoldenGate Administrator Guide

最新资源