没有合适的资源?快使用搜索试试~ 我知道了~
首页Oracle数据库管理员指南11g Release 2
Oracle数据库管理员指南11g Release 2
需积分: 9 1 下载量 158 浏览量
更新于2024-07-21
收藏 16.55MB PDF 举报
"Oracle管理员指南,11g Release 2 (11.2)"
在Oracle数据库管理系统中,管理员的角色至关重要,他们负责确保系统的稳定运行、性能优化以及数据的安全性。本指南针对Oracle管理员,旨在提供全面的管理和维护知识,帮助他们有效地管理Oracle数据库。
一、Oracle概念
1、Oracle的文件组成
Oracle数据库主要由三种文件类型构成:
- 数据文件(Data Files):存储数据库的实际数据,是构成表空间的基础。每个表空间由一个或多个数据文件组成,它们共同承载数据库对象如表、索引等。
- 日志文件(Redo Logs):记录对数据库的所有更改,用于数据库恢复。日志文件分为归档模式和非归档模式,前者更安全,便于长时间保留历史更改记录。
- 控制文件(Control Files):包含关于数据库结构和状态的关键信息,用于数据库启动和恢复过程。控制文件记录了数据库的创建时间、表空间信息、数据文件位置等。
二、Oracle数据库管理
1. 实例与数据库的关系
Oracle实例是内存结构和后台进程的集合,它与物理磁盘上的数据库相连接。实例提供了访问数据库的接口,而数据库则存储实际的数据。
2. 表空间与数据文件
表空间是逻辑存储单位,将数据库空间划分为不同的区域,用于存储不同类型的数据库对象。例如,系统表空间(SYSTEM)存储系统数据,用户表空间则用于存放用户创建的对象。
3. 系统全局区(SGA)
SGA是Oracle实例中的一块共享内存区域,包含了数据库缓存、重做日志缓冲区、数据字典缓存等重要组件。
4. 进程架构
Oracle数据库运行时涉及多种进程,包括服务器进程、后台进程等。服务器进程处理客户端请求,后台进程如DBWR(数据库写入进程)负责将缓存中的更改写入数据文件,LGWR(日志写入进程)负责将重做日志缓冲区的内容写入日志文件。
三、备份与恢复
Oracle提供了多种备份策略,如完整备份、增量备份和导出导入备份。恢复技术包括还原和恢复,有时还需要使用闪回技术来撤销错误操作。
四、性能监控与优化
管理员需要通过工具如SQL*Plus、Enterprise Manager或性能视图来监控数据库性能,识别瓶颈并进行调整。优化包括SQL查询优化、索引管理、分区技术和资源管理等。
五、安全性管理
Oracle的安全特性包括用户权限管理、角色分配、审计、网络加密等,管理员需设置合适的权限,防止未授权访问,并定期审计以确保数据安全。
六、故障排查与维护
Oracle提供了一系列故障排查工具,如 trace 文件、alert 日志和事件跟踪。定期维护包括数据库的检查点、空间管理、统计信息更新等。
七、高可用性与灾难恢复
Oracle支持RAC(Real Application Clusters)实现多节点集群,提供高可用性。另外,通过 standby 数据库、数据守护等技术可实现灾难恢复。
Oracle管理员需要掌握数据库的结构、管理工具、备份恢复策略、性能调优、安全性和高可用性等方面的知识,以确保Oracle数据库的高效、安全运行。本指南将详细阐述这些主题,为管理员提供实用的指导。
xvi
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
Capacity Planning for Database Objects ....................................................................................... 19-32
Estimating the Space Use of a Table .......................................................................................... 19-33
Estimating the Space Use of an Index ....................................................................................... 19-33
Obtaining Object Growth Trends .............................................................................................. 19-34
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-17
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-32
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-36
Placing a Table in Read-Only Mode........................................................................................... 20-37
Redefining Tables Online ................................................................................................................. 20-38
Features of Online Table Redefinition .......................................................................................
20-39
xvii
Performing Online Redefinition with DBMS_REDEFINITION............................................. 20-40
Results of the Redefinition Process............................................................................................. 20-44
Performing Intermediate Synchronization................................................................................ 20-44
Aborting Online Table Redefinition and Cleaning Up After Errors ..................................... 20-45
Restrictions for Online Redefinition of Tables.......................................................................... 20-45
Online Redefinition of a Single Partition................................................................................... 20-46
Online Table Redefinition Examples.......................................................................................... 20-48
Privileges Required for the DBMS_REDEFINITION Package............................................... 20-54
Researching and Reversing Erroneous Table Changes ............................................................... 20-54
Recovering Tables Using Oracle Flashback Table ....................................................................... 20-55
Dropping Tables.................................................................................................................................. 20-56
Using Flashback Drop and Managing the Recycle Bin ............................................................... 20-56
What Is the Recycle Bin? .............................................................................................................. 20-57
Enabling and Disabling the Recycle Bin.................................................................................... 20-58
Viewing and Querying Objects in the Recycle Bin .................................................................. 20-58
Purging Objects in the Recycle Bin............................................................................................. 20-59
Restoring Tables from the Recycle Bin....................................................................................... 20-60
Managing Index-Organized Tables ................................................................................................ 20-61
What Are Index-Organized Tables?........................................................................................... 20-61
Creating Index-Organized Tables............................................................................................... 20-62
Maintaining Index-Organized Tables ........................................................................................ 20-66
Creating Secondary Indexes on Index-Organized Tables....................................................... 20-68
Analyzing Index-Organized Tables ........................................................................................... 20-69
Using the ORDER BY Clause with Index-Organized Tables.................................................. 20-70
Converting Index-Organized Tables to Regular Tables.......................................................... 20-70
Managing External Tables................................................................................................................. 20-70
About External Tables .................................................................................................................. 20-71
Creating External Tables.............................................................................................................. 20-72
Altering External Tables............................................................................................................... 20-75
Preprocessing External Tables..................................................................................................... 20-75
Dropping External Tables............................................................................................................ 20-76
System and Object Privileges for External Tables.................................................................... 20-76
Tables Data Dictionary Views ......................................................................................................... 20-77
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-4
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
xviii
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
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
xix
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
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-18
25 Repairing Corrupted Data
Options for Repairing Data Block Corruption................................................................................ 25-1
About the DBMS_REPAIR Package .................................................................................................. 25-2
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
xx
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-18
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-24
Execution Time Limit ................................................................................................................... 27-24
Undo Pool....................................................................................................................................... 27-24
Idle Time Limit .............................................................................................................................. 27-24
Creating a Simple Resource Plan .................................................................................................... 27-24
剩余1081页未读,继续阅读
2011-05-11 上传
2023-10-23 上传
2023-05-14 上传
2023-07-27 上传
2023-07-29 上传
2023-07-27 上传
2023-05-11 上传
2023-06-28 上传
洛水妖孽
- 粉丝: 0
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- 李兴华Java基础教程:从入门到精通
- U盘与硬盘启动安装教程:从菜鸟到专家
- C++面试宝典:动态内存管理与继承解析
- C++ STL源码深度解析:专家级剖析与关键技术
- C/C++调用DOS命令实战指南
- 神经网络补偿的多传感器航迹融合技术
- GIS中的大地坐标系与椭球体解析
- 海思Hi3515 H.264编解码处理器用户手册
- Oracle基础练习题与解答
- 谷歌地球3D建筑筛选新流程详解
- CFO与CIO携手:数据管理与企业增值的战略
- Eclipse IDE基础教程:从入门到精通
- Shell脚本专家宝典:全面学习与资源指南
- Tomcat安装指南:附带JDK配置步骤
- NA3003A电子水准仪数据格式解析与转换研究
- 自动化专业英语词汇精华:必备术语集锦
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功