Oracle11g数据库管理员指南

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

"Oracle Database Administrator's Guide 是Oracle11g版本的一本官方管理员指南，适合对学习Oracle数据库管理有兴趣的人阅读。该文档由Steve Fogel作为主要作者，并有多位贡献者共同编撰，旨在帮助读者深入理解Oracle数据库的管理和维护。" 在Oracle Database Administrator's Guide中，你可以找到关于以下关键知识点的详细信息： 1. **Oracle数据库安装与配置**：指南将详细介绍如何安装和配置Oracle 11g数据库，包括选择合适的安装选项、设置系统参数和初始化参数文件。 2. **数据库创建与管理**：学习如何创建新数据库、添加表空间、管理数据文件以及设置数据库的高可用性选项，如归档模式和闪回功能。 3. **用户管理与安全性**：了解如何创建和管理数据库用户，设置权限和角色，以及实施Oracle的安全特性，如审计、加密和身份验证策略。 4. **性能优化**：包含有关监控和调整数据库性能的内容，如使用SQL*Plus、企业管理器或性能视图来分析和优化查询，以及使用数据库调优顾问（DTA）进行性能改进。 5. **备份与恢复**：讲解Oracle的备份和恢复策略，包括使用RMAN（恢复管理器）进行备份、增量备份、归档日志管理，以及如何执行灾难恢复操作。 6. **存储管理**：介绍如何有效地管理数据库的物理存储结构，如表空间、段、区和块，以及如何利用Oracle的自动存储管理（ASM）功能。 7. **网络配置**：涉及Oracle Net Services的配置，包括监听器设置、网络连接的故障排查，以及使用SQL*Net实现客户端和服务器之间的通信。 8. **数据库实例与服务**：解释数据库实例的概念，如何启动、关闭和管理数据库实例，以及配置数据库服务以满足不同应用需求。 9. **故障诊断与问题解决**：提供一系列工具和方法，帮助管理员识别和解决Oracle数据库中的常见问题，如使用trace文件和alert log进行故障排查。 10. **集群与高可用性**：涵盖Oracle Real Application Clusters (RAC)的相关知识，包括RAC的安装、配置、故障切换和负载均衡。 11. **数据库升级与迁移**：介绍如何从旧版本的Oracle数据库升级到11g，以及数据迁移的方法和策略。 12. **数据泵与导入导出**：讲解Oracle的数据泵工具（ExpDP/ImpDP）和传统的导入导出工具，用于数据迁移和备份。这本指南是Oracle数据库管理员不可或缺的参考书籍，它不仅包含了理论知识，还提供了大量的实际操作步骤和示例，有助于读者在实践中掌握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-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

xvii

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

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

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

剩余1067页未读，继续阅读

小南家的青蛙

粉丝: 3140
资源: 2

Oracle11g数据库管理员指南

Oracle Database Client Installation Guide 11g Release 2 (11.2) f

Oracle Database Lite 评估人员指南

Oracle 11g Study Guide Admin I + II

oracle database 12c 安装

Oracle TimesTen 如何与oracle database数据同步

oracle database 19c for microsoft windows x64下载

oracle database instant client 11g 11.2.0.3.0 x64

如何启动 oracle database console

oracle database11g安装教程

oracle database10g下载安装

最新资源