14 Manageability

001 Oracle Database 10g represents a major milestone in Oracle's drive toward self-managing databases. It automates many routine administrative tasks, and considerably simplifies key DBA functions, such as performance diagnostics, SQL tuning, and space and memory management. It also provides several advisors that guide DBAs in managing key components of the database by giving specific recommendations along with potential benefit. Furthermore, Oracle Database 10g proactively sends alerts when a problem is anticipated, thus facilitating proactive rather than reactive database management.
在 Oracle 逐渐发展为具备自我管理能力的(self-managing)数据库的过程中,Oracle 数据库 10g 是一个重要的里程碑。Oracle 数据库 10g 能够自动执行许多常规的数据库管理任务,同时能够显著地简化某些关键的 DBA 工作,例如性能诊断(performance diagnostic),SQL 调优(tuning),空间(space)管理及内存管理等。Oracle 10g 还提供了多个顾问工具(advisor),为数据库参数的设置提供建议并预测修改参数对数据库的影响,为 DBA 管理关键数据库组件提供了参考依据。此外,Oracle 数据库 10g 能够预测系统中可能出现的问题并提前向用户发出告警,做到了防患于未然的数据库管理。
002 This chapter contains the following topics: 本章包含以下主题:

Installing Oracle and Getting Started

14.1 安装数据库并使系统开始运转

004 The Oracle Universal Installer (OUI) is a GUI tool for installing Oracle software. It automates all installation tasks, performs comprehensive prerequisite checks (such as operating system version, software patches, and capacity), installs selected software components, and performs all postinstall configuration.
Oracle 通用安装程序(Oracle Universal Installer,OUI)是用于安装 Oracle 软件的图形化工具。她能自动地执行各项安装任务,进行安装先决条件检查(例如操作系统版本,软件补丁,磁盘容量等),安装用户选择的软件组件,并执行安装后的配置 工作。
005 The installation process is self-contained to automatically set up the required infrastructure for routine monitoring and administration. The Enterprise Manager Database Management Console is automatically configured to let you to get started with database administrative tasks without any manual configuration. The Enterprise Manager Database Console provides all essential functionality for managing a single database, including alert notification, job scheduling, and software management. In addition, all Oracle server components such as the database, listener, management framework, and so on, are configured for automated startup and shutdown.
安装过程将自动地配置监控及管理工作所需的基础结构。安装结束后,企业管理器数据库管理控制台(Enterprise Manager Database Management Console)已经被自动地配置了,管理员无需手工配置就可以开始进行数据库管理工作了。企业管理器数据库管理控制台包含了管理一个单实例数据库(single database)的主要功能,包括告警提示(alert notification),作业调度(job scheduling),及软件管理。此外,所有的 Oracle 服务器组件,例如数据库,监听器,管理框架(management framework)等,都已被配置为能够自动地启动与关闭。

See Also:

"Configuration Management" for more information on Enterprise Manager


配置管理” 了解关于企业管理器的更多信息

Simplified Database Creation

14.1.1 简化的数据库创建工作

008 The Database Creation Assistant (DBCA) is a GUI tool for database creation. It lets you create all possible configurations of the database, be it a standalone database, a Real Application Cluster database, or a standby database. During the database creation process, the DBCA guides you in setting up an automated disk-based backup and registering the database with a LDAP server, if available. A database created using the DBCA is fully setup and ready to use in all respects.
数据库创建助手(Database Creation Assistant,DBCA)是一个图形化的数据库创建工具。用户可以使用此工具创建各种不同配置的数据库,例如单实例数据库(standalone database),RAC 数据库,或备用数据库(standby database)。在数据库创建过程中,用户可以在 DBCA 的指引下创建一个基于磁盘(disk-based)的自动备份机制,或者将数据库注册到 LDAP 服务器中。通过 DBCA 创建的数据库已经进行了全面的设置,可以马上投入使用。

Instant Client

14.1.2 即时客户端

010 The Instant Client is the simplest way to deploy a full Oracle Client application built with OCI, OCCI, JDBC-OCI, or ODBC drivers. It provides the necessary Oracle Client libraries in a small set of files. Installation is as easy as copying a few shared libraries to a directory on the client computer. If this directory is accessible through the operating system library path variable (for instance, LD_LIBRARY_PATH or PATH) then the application will operate in the Instant Client mode. Instant Client deployment does not require the ORACLE_HOME environment, nor does it require the large number of code and data files provided in a full Oracle Client install, thereby significantly reducing the client application disk space needs. There is no loss in functionality or performance for an application deployed using Instant Client when compared to the same application running in a full ORACLE_HOME environment.
部署建立在 OCI,OCCI,JDBC-OCI,或 ODBC 驱动上的 Oracle 客户端应用程序时,采用即时客户端(Instant Client)是最简单的方法。即时客户端使用最少的文件包含了所有必要的 Oracle 客户端函数库。即时客户端的安装过程极为简单,只需要将客户端函数库文件复制到客户端计算机的某个目录。如果这个目录已经包含在操作系统的路径环境变量(例如 LD_LIBRARY_PATH PATH)中,那么应用程序就能够利用即时客户端开始运行。即时客户端不需要 ORACLE_HOME 环境变量,也不需要完全安装的 Oracle 客户端中包含的大量程序及数据文件,因此显著地减少了客户端应用程序所需的磁盘空间。应用程序无论利用即时客户端部署,还是利用完全安装的 Oracle 客户端部署,都拥有完全相同的功能及运行性能。

See Also:



Automated Upgrades

14.1.3 自动升级

013 With the Database Upgrade Assistant (DBUA), you can upgrade any database configuration, including RAC and standby, just by answering a few simple questions. It automatically checks that adequate resources are available, ensures adherence to the best practices – such as backing up the database before beginning the upgrade process, replacing the obsolete and deprecate initialization parameters, and so on – and, verifies the successful completion of the operation.
用户可以使用数据库升级助理(Database Upgrade Assistant,DBUA)来升级各种配置的数据库,包括 RAC 数据库及备用数据库(standby database),升级过程只需用户回答一些简单的问题。数据库升级助理能够自动地检查是否具备所需的资源,能够保证升级过程尽量完善--例如在升级前备份数据库,能够 清除废弃的初始化参数(initialization parameter),最后还能够检验数据库升级是否成功。
014 The upgrade process is restartable, allowing it to automatically resume from the point of interruption. You can also get a time estimation of how long the upgrade process is likely to take.
升级过程可以从中断点重新开始。DBUA 可以事先告知用户升级过程预计所需的时间。

Basic Initialization Parameters

14.1.4 基本初始化参数

016 The Oracle Database provides a number of initialization parameters to optimize its operation in diverse environments. Only a few of these parameters need to be explicitly set, because the default values are adequate in the majority of cases.
Oracle 数据库使用了大量的初始化参数(initialization paramete),以便用户根据不同的系统需求对数据库进行调优。但只有少量参数需要显示地设置,因为她们的默认值能够满足大多数情况的要求。
017 There are approximately 30 basic parameters. The remainder of the parameters are preserved to allow expert DBAs to adapt the behavior of the Oracle Database to meet unique requirements without overwhelming those who have no such requirements.
Oracle 数据库有 30 个左右基本初始化参数。而基本初始化参数之外的参数供经验丰富的 DBA 使用,使数据库经过调整后能够满足独特的需求。没有特殊需求时不必改变这些参数。

See Also:

Oracle Database Administrator's Guide


Oracle Database Administrator's Guide

Data Loading, Transfer, and Archiving

14.1.5 数据加载,传输,及归档

020 Data Pump enables very high-speed data and metadata loading and unloading to and from the Oracle Database. It automatically manages and schedules multiple, parallel streams of load or unload for maximum throughput.
数据泵(Data Pump)工具能够在 Oracle 数据库间高速地导出或加载数据及元数据。数据泵工具调度并管理多个并行地进程进行导出及加载,从而获得最大的数据吞吐量。
021 The transportable tablespace feature lets you quickly move a tablespace across Oracle databases. This can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.
用户可以利用可移动表空间(transportable tablespace)功能在数据库间快速地移动表空间。这比执行导入导出要快得多,因为在数据库间移动表空间只需复制数据文件,再导入表空间结构信息即可。用户使用可移动表空间时还能够移动索引数据, 而导入导出数据后还需要进行索引重建工作。
022 Data Pump functionality together with cross-platform transportable tablespace feature provides powerful, easy to use, and high performance tools for moving data in and out of the database.

See Also:



Intelligent Infrastructure

14.2 智能的基础结构

025 Oracle Database has a sophisticated self-management infrastructure that allows the database to learn about itself and use this information to adapt to workload variations or to automatically remedy any potential problem. The self-management infrastructure includes the following: Oracle 数据库拥有一套用于自我管理(self-management)的完善的基础结构,数据库能够利用这套基础结构了解自身的运行信息,并利用这些信息对自身进行调整以适应工作负载的变化,或自动地修正系统中的潜在问题。用于自我管理的基础结构包括以下内容:

Automatic Workload Repository

14.2.1 自动负载资料库

027 Automatic Workload Repository (AWR) is a built-in repository in every Oracle Database. At regular intervals, the Oracle Database makes a snapshot of all its vital statistics and workload information and stores them in AWR. By default, the snapshots are made every 60 minutes, but you can change this frequency. The snapshots are stored in the AWR for a certain period of time (seven days by default) after which they are automatically purged.
自动负载资料库(Automatic Workload Repository,AWR)是 Oracle 数据库中内置的一个资料库。Oracle 会按照固定的周期对其运行信息及负载信息进行快照(snapshot),并存储在 AWR 中。快照的默认的周期为 60 分钟,用户也可以调整这个频率。快照将在 AWR 中存储一段时间(默认为 7 天),过期的快照会被自动清除。
028 The captured data allows both system level and user level analysis to be performed, again reducing the requirement to repeat the workload in order to diagnose problems.
AWR 中的数据既可以供系统使用,也可以供用户使用,这避免了为诊断系统问题而重复地收集负载信息。
029 Optimizations have been performed to ensure that the capture of data is performed efficiently to minimize overhead. One example of these optimizations is in the SQL statement capture. It maintains deltas of the data for SQL statements between snapshots. These let the Oracle Database capture only statements that have significantly impacted the load of the system since the previous snapshot in an efficient manner, rather than having to capture all statements that had performed above a threshold level of work since they first appeared in the system.
Oracle 已经对捕获信息的工作进行了优化,从而保证其高效执行并使系统开销最小化。以 SQL 语句捕获的优化为例,Oracle 能够记录 SQL 语句不同快照间的增量信息(delta)。因此,Oracle 不会捕获系统中超过负载阈值但只是第一次出现的 SQL 语句,而只需捕获与上一次快照相比显著影响系统负载的语句。对比这两种捕获机制,后者较前者效率更高。
030 AWR forms the foundation for all self-management functionality of Oracle Database. It is the source of information that gives the Oracle Database an historical perspective on how it is being used and enables it to make decisions that are accurate and specifically tailored for each environment.
AWR 是 Oracle 数据库所有自我管理(self-management)功能的基础。Oracle 利用 AWR 作为信息源得以从历史的角度观察数据库是如何被使用的,从而使数据库的自动调整更精确且更符合系统的实际要求。

Automatic Maintenance Tasks

14.2.2 自动维护任务

032 By analyzing the information stored in AWR, the database can identify the need to perform routine maintenance tasks, such as optimizer statistics refresh. The automated maintenance tasks infrastructure enables the Oracle Database to automatically perform such operations. It uses the Scheduler to run such tasks in a pre-defined "maintenance window".
通过分析 AWR 中的数据,Oracle 能够决定是否需要执行常规的维护任务(maintenance task),例如刷新优化器(optimizer)的统计信息。Oracle 使用自动维护任务基础结构(automated maintenance tasks infrastructure)来自动地执行此类维护操作。Oracle 使用调度器(scheduler)在预定义的维护窗口(maintenance window)内运行维护任务。
033 By default, the maintenance window starts at 10 PM every night and lasts until 6 AM next morning and throughout the weekend. All attributes of the maintenance window are customizable, including start and end time, frequency, days of the week, and so on. Also, the impact of automated maintenance tasks on normal database operations can be limited by associating a Database Resource Manager resource plan to the maintenance window.
Oracle 中默认的维护窗口为每晚 22 点至第二天早晨 6 点及整个周末。维护窗口的属性是能够自定义的,这包括窗口的开始及结束时间,频率,一周内的哪几天等等。此外,用户还可以将数据库资源管理器(Database Resource Manager)中的资源计划应用到维护窗口,从而避免自动护任务对数据库常规操作造成影响。
034 Optimizer statistics are automatically refreshed using the automatic maintenance task infrastructure.

See Also:



Server-Generated Alerts

14.2.3 服务器告警

037 For problems that cannot be resolved automatically and require administrators to be notified, such as running out of space, the Oracle Database provides server-generated alerts. The Oracle Database can monitor itself and send out alerts to notify you of any problem in an efficient and timely manner.
对于不能自动解决而必须通知管理员的问题(例如存储空间不足),Oracle 数据库将生成服务器告警(server-generated alert)。Oracle 数据库能够进行自我监控,在出现问题时及时向用户发出告警。
038 Monitoring activities take place as the database performs its regular operation. This ensures that the database is aware of problems the moment they arise. The alerts produced by the Oracle Database not only notify the problem, they also provide recommendations on how the reported problem can be resolved. This ensures quick problem resolution and helps prevent potential failures.
在数据库进行常规操作的时,监控活动也在同时进行。因此发生问题后 Oracle 能够马上获知。由 Oracle 数据库产生的服务器告警不仅能将问题通知给用户,还能提供如何解决问题的建议。这有助于迅速解决问题,并能预防潜在的系统故障。

Advisor Framework

14.2.4 顾问工具框架

040 The Oracle Database includes a number of advisors for different sub-systems in the database to automatically determine how the operation of the corresponding subcomponents could be further optimized. The SQL Tuning Advisor and the SQL Access Advisor, for example, provide recommendations for running SQL statements faster. Memory advisors help size the various memory components without resorting to trial-and-error techniques. The Segment Advisor handles space-related issues, such as recommending wasted-space reclamation and analyzing growth trends, while the Undo Advisor guides you in sizing the undo tablespace correctly. The various advisors are discussed more throughout this chapter.
Oracle 数据库中包含了多个针对不同数据库子系统的顾问工具(advisor),用于决定如何进一步优化各个子系统。举例来说,SQL 调优顾问工具(SQL Tuning Advisor)及 SQL 数据存取顾问工具(SQL Access Advisor)能够为更快地执行 SQL 语句提供建议。用户可以根据内存顾问工具(Memory advisor)的建议调整各个内存组件的容量,而不必反复尝试调整值是否正确(trial-and-error technique)。数据段顾问工具(Segment Advisor)用于解决和空间相关的问题,例如进行空间回收(wasted-space reclamation)及分析空间增长趋势。还原管理顾问工具(Undo Advisor)能够指导用户正确地设置还原表空间的容量。本章还将详细论述各种顾问工具。
041 To ensure the consistency and uniformity in the way advisors function and allow them to interact with each other seamlessly, the Oracle Database includes an advisor framework. The advisor framework provides a consistent manner in which advisors are invoked and results are reported. Although these advisors are primarily used by the database to optimize its own performance, they can be invoked by administrators to get more insight into the functioning of a particular subcomponent.
为了使各个顾问工具能够以一致且统一的方式工作,并使不同顾问工具间能够无缝地交互,Oracle 数据库中定义了顾问工具框架(advisor framework)。在顾问工具框架下,不同顾问工具的调用方式及结果返回方式具有一致性。数据库中的顾问工具主要供数据库优化其性能时使用,但管理员也能够调用顾问工具,从而深入了解各个子系统的运行情况。

See Also:

Oracle Database 2 Day DBA for more information on using advisors


Oracle Database 2 Day DBA 了解更多关于使用顾问工具的信息

Performance Diagnostic and Troubleshooting

14.3 性能诊断及故障处理

044 Building upon the data captured in AWR, the Automatic Database Diagnostic Monitor (ADDM) lets the Oracle Database diagnose its own performance and determine how identified problems could be resolved. ADDM runs automatically after each AWR statistics capture, making the performance diagnostic data readily available.
Oracle 数据库中的自动数据库诊断监视器(Automatic Database Diagnostic Monitor,ADDM),能够利用 AWR 中捕获的数据对数据库性能进行分析,并决定如何解决发现的问题。AWR 每次捕获统计信息后 ADDM 将自动运行,迅速生成性能诊断信息。
045 ADDM examines data captured in AWR and performs analysis to determine the major issues on the system on a proactive basis. In many cases, it recommends solutions and quantifies expected benefits. ADDM takes a holistic approach to the performance of the system, using time as a common currency between components. ADDM identifies those areas of the system that are consuming the most time. ADDM drills down to identify the root cause of problems, rather than just the symptoms, and reports the impact that the problem is having on the system overall. If a recommendation is made, it reports the benefits that can be expected in terms of time. The use of time throughout allows the impact of several problems or recommendations to be compared.
ADDM 能够分析 AWR 中捕获的信息,并确定系统中存在的潜在问题。对于大多数问题,ADDM 能够提出建议的解决方案并给出量化的预期效果。ADDM 总是从整体上来评估系统的性能,她使用时间作为评估系统各个组件的通用标准。ADDM 能够确定系统中哪个组件消耗的时间最多。确定了问题的表象后,ADDM 还能深入研究以确定问题的根源,并显示此问题对系统整体的影响。如果 ADDM 能够提出调优意见,她还会以时间为单位计算调整后的预期效果。统一以时间为单位使 ADDM 能够对不同的性能问题及解决方案进行比较。
046 ADDM focuses on activities that the database is spending most time on and then drills down through a sophisticated problem classification tree. Some common problems detected by ADDM include the following:
  • CPU bottlenecks
  • Poor connection management
  • Excessive parsing
  • Lock contention
  • I/O capacity
  • Undersizing of Oracle memory structures; for example, PGA, buffer cache, log buffer
  • High load SQL statements
  • High PL/SQL and Java time
  • High checkpoint load and cause; for example, small log files, aggressive MTTR setting
  • RAC-specific issues
ADDM 集中分析数据库中消耗时间最多的操作,并依照一个完善的问题分类树(problem classification tree)进行深入分析。ADDM 能够检测出的通用问题包括:
  • CPU 瓶颈
  • 连接管理(connection management)问题
  • 过量的解析操作(excessive parsing)
  • 锁竞争(lock contention)
  • I/O 性能问题
  • Oracle 内存结构过小;例如 PGA,数据库缓存,重做日志缓存等
  • 执行 SQL 语句造成的负载过高
  • 执行 PL/SQL 及 Java 程序时间过长
  • 检查点(checkpoint)负载过高及原因;例如重做日志文件(log file)过小,或 MTTR 设置不适当
  • 与 RAC 相关的问题
047 Besides reporting potential performance issues, ADDM also documents non-problem areas of the system. The subcomponents, such as I/O and memory, that are not significantly impacting system performance are pruned from the classification tree at an early stage and are listed so that you can quickly see that there is little to be gained by performing actions in those areas.
除了报告潜在的性能问题,ADDM 还能记录系统中没有问题的子系统。没有显著影响系统性能的子系统在诊断初期就被从问题分类树中移出,并被列在诊断结果中。用户能够清楚地了解对系统中哪些组件进行调优的效果不会太明显。
048 You no longer need to first collect huge volumes of diagnostic data and spend hours analyzing them in order to find out answers to performance issues. You can simply follow the recommendation made by ADDM with just a few mouse clicks.
使用了 ADDM 后,用户不必手工收集大量诊断数据,并花费大量时间对数据进行分析,再设法找到解决系统性能问题的办法。用户只需轻点鼠标,参考 ADDM 的建议即可。

Application and SQL Tuning

14.4 应用调优及 SQL 调优

050 The Oracle Database completely automates the SQL tuning process. ADDM identifies SQL statements consuming unusually high system resources and therefore causing performance problems. In addition, the top SQL statements in terms of CPU and shared memory consumption are automatically captured in AWR. Thus, the identification of high load SQL statements happens automatically in the Oracle Database and requires no intervention.
Oracle 数据库中的 SQL 语句调优过程是完全自动化的。ADDM 能够识别出消耗系统资源异常高并导致系统性能问题的 SQL 语句。此外 AWR 也能自动地捕获系统中消耗 CPU 或共享内存较高的语句。因此在 Oracle 数据库中,对造成高负载的 SQL 语句的识别是完全自动的,无须用户干预。
051 After identifying the top resource-consuming SQL statements, the Oracle Database can automatically analyze them and recommend solutions using the Automatic Tuning Optimizer. Automatic SQL Tuning is exposed with an advisor, called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as input and produces well-tuned plans along with tuning advice. You do not need to do anything other than invoke the SQL Tuning Advisor.
当识别出消耗资源较高的 SQL 后,Oracle 数据库能自动地使用自动调优优化器(Automatic Tuning Optimizer)对其进行分析并给出建议的执行计划。而用户可以通过 SQL 调优顾问工具(SQL Tuning Advisor)来使用 Oracle 的自动 SQL 调优(Automatic SQL Tuning)功能。SQL 调优顾问工具接收一个或多个 SQL 语句作为输入,输出经过调优的 SQL 执行计划及调优建议。用户需要做的工作只是调用 SQL 调优顾问工具。
052 The solution comes right from the optimizer and not from external tools using pre-defined heuristics. This provides several advantages: a) the tuning is done by the system component that is ultimately responsible for the execution plans and SQL performance, b) the tuning process is fully cost-based, and it naturally accounts for any changes and enhancements done to the query optimizer, c) the tuning process considers the past execution statistics of a SQL statement and customizes the optimizer settings for that statement, and d) it collects auxiliary information in conjunction with the regular statistics based on what is considered useful by the query optimizer.
在 Oracle 中 SQL 语句的执行计划是由查询优化器(query optimizer)给出的,而非其它使用预定义的探索方式(pre-defined heuristics)来优化 SQL 的外部工具。这种模式以下几个优势:a)调优工作是由最终负责生成执行计划(execution plan)及保证 SQL 执行性能的系统组件完成的,b)调优过程是完全基于成本的(cost-based),且修改了查询优化器的任何参数后都会直接影响调优结果,c)调优过程能够 参考 SQL 语句以往执行所产生的统计信息,并根据每个 SQL 语句的特性设置查询优化器,d)调优过程能够根据查询优化器的需要收集系统中其他有意义的统计信息。
053 The recommendation of the Automatic Tuning Optimizer can fall into one of the following categories
  • Statistics Analysis: The Automatic Tuning Optimizer checks each query object for missing or stale statistics and makes recommendations to gather relevant statistics. It also collects auxiliary information to supply missing statistics or correct stale statistics in case recommendations are not implemented. Because the Oracle Database automatically gathers optimizer statistics, this should not be the problem unless the automatic statistics gathering functionality has been disabled.
  • SQL Profiling: The Automatic Tuning Optimizer verifies its own estimates and collects auxiliary information to remove estimation errors. It also collects auxiliary information in the form of customized optimizer settings (for example, first rows or all rows) based on past execution history of the SQL statement. It builds a SQL profile using the auxiliary information and makes a recommendation to create it. It then enables the query optimizer (under normal mode) to generate a well-tuned plan. The most powerful aspect of SQL profiles is that they enable tuning of queries without requiring any syntactical changes and thereby proving a unique database –resident solution to tune the SQL statements embedded in packaged applications.
  • Access Path Analysis: The Automatic Tuning Optimizer considers whether a new index can be used to significantly improve access to each table in the query and when appropriate makes recommendations to create such indexes.
  • SQL Structure Analysis: The Automatic Tuning Optimizer tries to identify SQL statements that lend themselves to bad plans and makes relevant suggestions to restructure them. The suggested restructuring can be syntactic as well as semantic changes to the SQL code.
  • 统计信息分析:自动调优优化器将检查查询中使用的每个对象,如发现没有统计信息或信息过于陈旧,优化器将建议重新收集相关的统计信息。如果优化器的建议没有被执行,她还能自动地收集信息以补充缺失的统计信息或修正陈旧的统计信息。由于 Oracle 数据库能够自动地收集优化器所需的统计信息,通常此类问题不会出现,除非自动收集统计信息的功能被用户禁止。
  • SQL 档案(SQL Profiling):自动调优优化器能够验证其调优结果,并收集额外信息修正调优结果中的错误。自动调优优化器还能依据 SQL 语句执行的历史情况,收集依据为一个 SQL 而定制的优化器设置信息(customized optimizer setting)(例如,首行(first rows)模式或所有行(all rows)模式)。自动调优优化器利用其收集的额外信息为 SQL 语句建立 SQL 档案。此时查询优化器(如运行在普通模式下)就可以使用 SQL 档案来生成执行计划了。用户使用 SQL 档案功能后,无需修改 SQL 语句也能对查询进行调优,这相当于将 SQL 语句的调优方案固化在数据库中,此功能有助于对预制的应用程序(packaged application)中的 SQL 语句进行调优。
  • 数据存取路径(Access Path)分析:自动调优优化器能够预测新的索引是否能显著提高查询中某个表的存取效率,如有可能将建议用户创建索引。
  • SQL 结构分析:自动调优优化器能够识别执行计划较差的 SQL 语句,并提出重构 SQL 语句的建议。自动调优优化器的建议既可能针对语法(syntactic),也可能针对语义(semantic)。
054 Both access path and SQL structure analysis can be useful in tuning the performance of an application under development or a homegrown production application where the administrators and developers have access to application code.
如果应用程序正在开发,或者应用程序是自制的,管理员或开发者能够修改程序代码,那么可以采用数据存取路径分析或 SQL 结构分析对 SQL 语句进行调优。
055 The SQL Access Advisor can automatically analyze the schema design for a given workload and recommend indexes, function-based indexes, and materialized views to create, retain, or drop as appropriate for the workload. For single statement scenarios, the advisor only recommends adjustments that affect the current statement. For complete business workloads, the advisor makes recommendations after considering the impact on the entire workload.
SQL 数据存取顾问工具(SQL Access Advisor)能够分析方案对象在某种工作负载下的数据访问情况,并在需要时向用户建议创建,保留,或移除索引,函数索引,物化视图等对象,以满足 此工作负载的需求。如果用户对一个单独语句进行调优,顾问工具在提出调优建议时只考虑对当前语句的影响。如果用户需要对整个系统的负载进行调优,顾问工具在提出调优建议时将考虑对整个系统的影响。
056 While generating recommendations, the SQL Access Advisor considers the impact of adding new indexes and materialized views on data manipulation activities, such as insert, update, and delete, in addition to the performance improvement they are likely to provide for queries. After the SQL Access Advisor has filtered the workload, but while it is still identifying all possible solutions, you can asynchronously interrupt the process to get the best solution up to that point in time.
SQL 数据存取顾问工具在生成调优建议时,除了考虑调优对查询性能的提高之外,还会考虑添加了新数据库对象(例如索引或物化视图)后对插入,更新及删除等数据操作活动的影响。如果 SQL 数据存取顾问工具已经挑选出了数个执行计划,但仍在寻找其他可行的方案,用户可以异步地终止这个过程,并使用当前发现的最好的方案。
057 The SQL Access Advisor provides an easy to use interface and requires very little system knowledge. It can be run without affecting production systems, because the data can be gathered from the production system and taken to another computer where the SQL Access Advisor can be run.
SQL 数据存取顾问工具拥有易用的用户接口,无需用户掌握大量的系统知识。SQL 数据存取顾问工具不会对生产系统产生很大的影响,因为用户可以将在生产系统中收集的数据迁移到其他安装了SQL 数据存取顾问工具的计算机上进行分析。

See Also:

Oracle Database Performance Tuning Guide for more information on the SQL Tuning Advisor and the SQL Access Advisor


Oracle Database Performance Tuning Guide 了解关于 SQL 调优顾问工具及 SQL 数据存取顾问工具的详细信息

Memory Management

14.5 内存管理

060 The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. Automatic Shared Memory Management automates the management of SGA used by an Oracle Database instance. Simply specify the total amount of SGA memory available to an instance with the parameter SGA_TARGET. The Oracle Database then automatically distributes the available memory among various components as required.
系统全局区(System Global Area,SGA)是一个共享的内存区域,其中包含了供 Oracle 实例使用的数据及控制信息。自动共享内存管理(Automatic Shared Memory Management)能使 Oracle 数据库实例 SGA 的管理自动化。用户只需通过 SGA_TARGET 参数为一个实例设定可供其 SGA 使用的总内存容量。Oracle 数据库能根据各个内存组件的需求自动地分配可用内存。
061 Oracle provides dynamic memory management that allows for resizing of the Oracle shared memory components dynamically. It also provides for transparent management of working memory for SQL execution by self-tuning the initialization runtime parameters controlling allocation of private memory. This helps users on systems with a low number of users to reduce the time and effort required to tune memory parameters for their applications, such as data warehouse and reporting applications. On systems with a higher number of users, this also allows them to avoid memory tuning for individual workloads.
Oracle 具备动态内存管理功能,因此各个共享内存组件(shared memory component)的容量能够动态地调节。Oracle 还能自动调整用于控制私有内存(private memory)分配的运行时初始化参数(initialization runtime parameter),因此能够(对用户)透明地管理 SQL 语句执行时所需的工作内存区(working memory)。对于数据仓库或报表应用等用户较少的系统,上述功能可以大量减少用户手工调整应用程序内存参数的工作。对于用户数较多的系统,上述功能同样可以减少为每个用户连接调整内存的工作。
062 Oracle provides the following advisors to help size the memory allocation for optimal database performance.
Oracle 提供了以下顾问工具来协助调整内存分配,以优化数据库性能。
063 The Shared Pool Advisor determines the optimal shared pool size by tracking its use by the library cache. The amount of memory available for the library cache can drastically affect the parse rate of an Oracle instance. The shared pool advisor statistics provide information about library cache memory, letting you predict how changes in the size of the shared pool can affect aging out of objects in the shared pool.
共享池顾问工具(Shared Pool Advisor)通过跟踪库缓存(library cache)使用共享池的情况来决定最优的共享池容量。库缓存可用的内存容量决定了 Oracle 实例的解析(parse)效率。共享池顾问工具收集了关于库缓存的统计信息,供用户预测共享池的容量改变对共享池内对象保存时间的影响。
064 The Buffer Cache Advisor determines the optimal size of the buffer cache. When configuring a new instance, it is difficult to know the correct size for the buffer cache. Typically, you make a first estimate for the cache size, then run a representative workload on the instance and examines the relevant statistics to see whether the cache is under or over configured. A number of statistics can be used to examine buffer cache activity. These include the V$DB_CACHE_ADVICE view and the buffer cache hit ratio.
数据库缓存顾问工具(Buffer Cache Advisor)的作用是决定数据库缓存的最优容量。当用户配置一个新的实例时,一般难以确定数据库缓存的最佳容量。通常,用户需要首先设置一个预估的容量,然后通过观察实例在预期负载下的统计信息来决定之前预估的容量是否适当。用户观察数据库缓存活动的统计指标有多个。例如 V$DB_CACHE_ADVICE 视图及数据库缓存命中率(buffer cache hit ratio)。
065 The Java Pool Advisor provides information about library cache memory used for Java and predicts how changes in the size of the Java pool can affect the parse rate.
Java 池顾问工具(Java Pool Advisor)能够记录 Java 程序使用库缓存的情况,并预测改变 Java 池容量对解析效率的影响。
066 The Streams Pool Advisor determines the optimal size of the Streams pool. The view V$STREAMS_POOL_ADVICE gives estimates of the amount of bytes spilled and unspilled for the different values of the STREAMS_POOL_SIZE parameter. You can use this to tune the STREAMS_POOL_SIZE parameter for Streams and for logical standby. Automatic Workload Repository reports on the V$STREAMS_POOL_ADVICE view and CPU usage help you tune Streams performance.
数据流池(Streams Pool Advisor)顾问工具能够决定数据流池的最佳容量。用户可以通过对 V$STREAMS_POOL_ADVICE 视图进行查询,了解 Oracle 预测的在各种 STREAMS_POOL_SIZE 参数值下数据流池的使用情况。用户依据视图中的信息为数据流及逻辑备用数据库(logical standby)设定 STREAMS_POOL_SIZE 参数。自动负载资料库(Automatic Workload Repository)能够提供基于数据流 CPU 使用情况和 V$STREAMS_POOL_ADVICE 视图的报表,协助用户调整数据流性能。
067 The Program Global Area (PGA) Advisor tunes PGA memory allocated to individual server processes. Under automatic PGA memory management mode, Oracle honors the PGA_AGGREGATE_TARGET limit by controlling dynamically the amount of PGA memory allotted to SQL database areas. At the same time, Oracle maximizes the performance of all the memory-intensive SQL operators by maximizing the number of database areas that are using an optimal amount of PGA memory (cache memory). The rest of the database areas are executed in one-pass mode, unless the PGA memory limit set by PGA_AGGREGATE_TARGET is so low that multipass execution is required to reduce even more the consumption of PGA memory and honor the PGA target limit.
程序全局区顾问工具(Program Global Area Advisor)能够对每个服务进程(server process)的内存分配进行调整。在自动 PGA 内存管理模式下,Oracle 能够动态地控制为各个 SQL 工作区(database area)所分配的 PGA 内存容量,使其总 PGA 容量满足 PGA_AGGREGATE_TARGET 参数的限制。对于需要大量内存的 SQL 操作,Oracle 优先保证其工作区能够从 PGA 中分配足够的内存,从而保证这类操作的性能。Oracle 还将保证其余的工作区尽量工作在一次交换(one-pass)模式下。如果 PGA_AGGREGATE_TARGET 参数设置的过低,Oracle 将使某些工作区采取多次交换(multipass)的方式执行,从而减少 PGA 的消耗,确保 PGA 容量满足限制。
068 When configuring a new instance, it is difficult to know an appropriate setting for PGA_AGGREGATE_TARGET. You can determine this setting in three stages:
  1. Make a first estimate for PGA_AGGREGATE_TARGET.
  2. Run a representative workload on the instance and monitor performance using PGA statistics collected by Oracle to see whether the maximum PGA size is under configured or over configured.
  3. Tune PGA_AGGREGATE_TARGET using Oracle's PGA advice statistics.
当用户配置新实例时,很难确定恰当的 PGA_AGGREGATE_TARGET 参数值。用户可以通过以下步骤使此参数更为合理:
  1. 首先估计一个 PGA_AGGREGATE_TARGET 参数值。
  2. 使实例运行在预期的负载下,通过 Oracle 收集的 PGA 统计信息来监控系统性能,从而决定当前参数值是否适当。
  3. 根据 PGA 顾问工具的统计数据来调整 PGA_AGGREGATE_TARGET 参数的值。
069 When the Automatic Shared Memory Management is enabled, the most commonly configured components are sized automatically. These include the following:
  • Shared pool (for SQL and PL/SQL execution)
  • Java pool for (Java execution state)
  • Large pool (for large allocations such as RMAN backup buffers)
  • Buffer cache
  • Streams pool
当用户启用了自动共享内存管理后,需要用户经常调整容量的内存组件将被 Oracle 自动地调整。这样的内存组件包括:
  • 共享池(供 SQL 及 PL/SQL 执行适用)
  • Java 池(供 Java 程序执行适用)
  • 大型池(用于大量的内存分配,例如 RMAN 的备份缓冲区)
  • 数据库缓存
  • 数据流池
070 There is no need to set the of size any of these components explicitly, and by default the parameters for these components appear to have values of zero. Whenever a component needs memory, it can request that it be transferred from another component with the internal auto-tuning mechanism. This happens transparently without user-intervention.
用户无需显式地为这些内存组件设定容量,默认情况下这些组件的容量参数均显示为 0。当某个内存组件需要扩展容量时,她将向系统发出请求,Oracle 则通过内部的自动调节机制将其他组件的内存转移给她。上述过程对用户是透明的。
071 The performance of each component is monitored by the Oracle instance. The instance uses internal views and statistics to determine how to optimally distribute memory among the automatically-sized components. Thus, as the workload changes, memory is redistributed to ensure optimal performance with the new workload. This algorithm tries to find the optimal distribution by taking into consideration long term and short terms trends.
Oracle 组件负责监控各个内存组件的性能。实例根据内部视图的信息及其他统计信息来决定如何在各个自动管理的组件间分配内存以达到最优。这样,当工作负载改变时内存就能够被重新分配,从而确保系统在新负载下也能获得最优性能。内存分配调整算法会同时考虑系统运行的短期与长期趋势。
072 You can exercise some control over the size of the auto-tuned components by specifying minimum values for each component. This can be useful in cases where you know that an application needs a minimum amount of memory in certain components to function properly.
073 The sizes of the automatically-tuned components are remembered across shutdowns if a server parameter file (SPFILE) is used. This means that the system picks up where it left off from the last shutdown.
如果数据库使用了服务器参数文件(server parameter file,SPFILE),那么自动调整内存组件的参数在实例关闭后依然能被保留。系统下次启动时可以使用上次关闭时保留的参数。
074 The most significant benefit of using automatic SGA memory management is that the sizes of the different SGA components are flexible and adapt to the needs of a workload without requiring user intervention. Besides maximizing the use of available memory, Automatic Shared Memory Management can enhance workload performance. With manual configuration, it is possible that the compiled SQL statements will frequently age out of the shared pool because of its inadequate size. This manifests into frequent hard parses and reduced performance. However, when automatic management is enabled, the internal tuning algorithm monitors the performance of the workload and grows the shared pool if it determines that doing so will reduce the number of parses required. This provides enhanced performance, without requiring any additional resources or manual tuning effort.
使用自动 SGA 内存管理的最大好处是,各个 SGA 组件的容量是灵活可变的,能够适应不同的工作负载而无需用户干预。除了是可用内存最大化之外,自动共享内存管理还有助于提高系统性能。采用手工内存管理时,已编译的 SQL 语句可能会因为共享池容量不足而被清除出共享池。这将导致频繁的硬解析(hard parse)进而影响系统性能。而启用了自动内存管理后,内部调节算法将监控系统性能,如认为增大共享池有助于减少硬解析的话就会对内存组件做出相应调整。此功能提高了系统性能,且无需向系统添加资源,也无需任何手工 调整操作。

See Also:



Space Management

14.6 空间管理

077 The Oracle Database automatically manages its space consumption, sends alerts on potential space problems, and recommends possible solutions. Oracle features that help you to easily manage space include the following: Oracle 数据库能够自动地对其空间使用进行管理,能够发现潜在的空间问题,及时向管理员发出告警并给出可行的解决方案。Oracle 提供的协助用户对空间进行管理的功能有:

Automatic Undo Management

14.6.1 自动还原管理

079 Earlier releases of Oracle used rollback segments to store undo. Space management for these rollback segments was complex. Automatic undo management eliminates the complexities of managing rollback segments and lets you exert control over how long undo is retained before being overwritten. Oracle strongly recommends that you use undo tablespace to manage undo rather than rollback segments.
之前版本的 Oracle 使用回滚段(rollback segment)来存储还原信息(undo)。回滚段的空间管理十分复杂。而采用自动还原管理(automatic undo management)将大大降低管理回滚段的复杂性,管理员只需专注于控制还原信息经过多长时间才能被覆盖即可。Oracle 强烈建议用户使用还原表空间(undo tablespace)来管理还原信息,而不要继续使用回滚段。
080 The Undo Advisor improves manageability of transaction management, especially for automatic undo management. The Undo Advisor presents the best retention possible for the given undo tablespace. It also advises a size for the undo tablespace when you want to set undo retention to a particular value.
还原管理顾问工具(Undo Advisor)能够提高事物的可管理性,尤其是系统采用了自动还原管理时。还原管理顾问工具能够根据可用的还原表空间容量确定最佳的还原信息保存周期(undo retention)。还原管理顾问工具也能根据用户所需的还原信息保存周期建议最佳的还原表空间容量。
081 The Undo Advisor is based on system activity statistics, including the longest running query and undo generation rate. Advisor information includes the following:
  • Current undo retention
  • Current undo tablespace size
  • Longest query duration
  • Best undo retention possible
  • Undo tablespace size necessary for current undo retention
  • 当前的还原信息保存周期
  • 当前的还原表空间容量
  • 系统中最长的查询执行时间
  • 当前还原表空间容量所能提供的最大还原信息保存周期
  • 给定的还原信息保存周期所需的还原表空间容量

See Also:



Oracle-Managed Files

14.6.2 自动文件管理

084 With Oracle-managed files, you do not need to directly manage the files comprising an Oracle database. Oracle uses standard file system interfaces to create and delete files as needed. This automates the routine task of creation and deletion of database files.
采用了自动文件管理(Oracle-managed file)后,用户无需直接管理组成 Oracle 数据库的各个文件。Oracle 能够使用标准的文件系统接口自动地创建或删除文件。这使数据库文件创建删除之类的常规管理工作能够自动地执行。

Free Space Management

14.6.3 可用空间管理

086 Oracle allows for managing free space within a table with bitmaps, as well as traditional dictionary based space management. The bitmapped implementation eliminates much space-related tuning of tables, while providing improved performance during peak loads. Additionally, Oracle provides automatic extension of data files, so the files can grow automatically based on the amount of data in the files. Database administrators do not need to manually track and reorganize the space usage in all the database files.
Oracle 既可以使用位图(bitmap)来管理表的可用空间(free space),也可以使用传统的数据字典方式进行管理。采用位图方式对表的可用空间进行管理能够消除大量的空间调整工作,同时还能提高表在高负载下的工作性能。Oracle 还能够自动地扩展数据文件(data file),即数据文件的容量能够根据其中所存储的数据量而自动地增长。因此数据库管理员无需手工地监控所有数据文件的空间使用情况。

Proactive Space Management

14.6.4 主动空间管理

088 Oracle Database introduces a non-intrusive and timely check for space utilization monitoring. It automatically monitors space utilization during normal space allocation and de-allocation operations and alerts you if the free space availability falls below the pre-defined thresholds. Space monitoring functionality is set up out of box, causes no performance impact, and is uniformly available across all tablespace types. Also, the same functionality is available both through Enterprise Manager as well as SQL. Because the monitoring is performed at the same time as space is allocated and freed up in the database, this guarantees immediate availability of space usage information whenever you need it.
Oracle 数据库能够定期地进行检查,从而实现对空间使用情况的监控,这种检查不会中断正常的数据库操作。Oracle 在空间分配及回收操作期间对系统的空间使用情况进行监控,如果发现可用空间低于预设的阈值将向用户发出告警。空间监控是 Oracle 的内置功能,因此不会对系统性能产生影响,且各种类型的表空间均可使用。用户通过企业管理器(Enterprise Manager)或 SQL 语句均可使用系统的空间监控功能。由于监控是与数据库空间分配及释放同时进行的,用户可以随时得到及时准确的空间使用情况。
089 Notification is performed using server-generated alerts. The alerts are triggered when certain space-related events occur in the database. For example, when the space usage threshold of a tablespace is crossed or when a resumable session encounters an out of space situation, then an alert is raised. An alert is sent instantaneously to take corrective measures. You may choose to get paged with the alert information and add space to the tablespace to allow the suspended operation to continue from where it left off.
关于空间使用的信息是通过服务器告警(server-generated alert)通知给用户的。当数据库中发生的某些与空间使用相关的事件时,告警将被触发。例如,当某个表空间的已用空间超过了阈值,或一个可恢复会话(resumable session)遇到空间不足的状况,都会产生告警。告警信息能够被及时地传递给用户,以便用户采取适当的措施。例如,用户可以通过传呼(page)接受告警信息,再为表空间增加存储空间,使挂起的操作得以继续执行。
090 The database comes with a default set of alert thresholds. You can override the default for a given tablespace or set a new default for the entire database through Enterprise Manager.

Intelligent Capacity Planning

14.6.5 智能存储空间规划

092 Space may get overallocated because of the difficulty to predict the space requirement of an object or the inability to predict the growth trend of an object. On tables that are heavily updated, the resulting segment may have a lot of internal fragmentation and maybe even row chaining. These issues can result in a wide variety of problems from poor performance to space wastage. The Oracle Database offers several features to address these challenges.
如果数据库对象所需的存储空间难以预测,或者对象容量的增长趋势难以预测,为某个数据库对象分配的空间很可能会过大。而频繁地进行更新操作的表,其数据段(segment) 中可能存在大量碎片(internal fragmentation)或行迁移(row chaining)。这些情况可能导致各种问题,例如存储性能下降或空间浪费。Oracle 数据库提供了多种解决此类问题的方法。
093 The Oracle Database can predict the size of a given table based on its structure and estimated number of rows. This is a powerful "what if" tool that allows estimation of the size of an object before it is created or rebuilt. If tablespaces have different extent management policies, then the tool will help decide the tablespace that will cause least internal fragmentation.
Oracle 能够依据表结构及预计行数预测表的容量。用户可以在创建或重建对象之前使用这个强大的“what if”工具预测对象的容量。如果表空间可以使用不同的数据扩展(extent)管理策略,这个工具还能帮助用户决定采用哪种策略能够使数据段内的碎片最少。
094 The growth trend report takes you to the next step of capacity planning – planning for growth. Most database systems grow over time. Planning for growth is an important aspect of provisioning resources. To aid this process, the Oracle Database tracks historical space utilization in the AWR and uses this information to predict the future resource requirements.
用户可以使用空间增长趋势报告(growth trend report)进一步地对空间进行规划——空间增长规划(planning for growth)。绝大多数数据库系统的容量都会随时间而增长。空间增长规划是资源供给(provisioning resource)的一个重要方面。Oracle 数据库在 AWR 中记录空间使用的历史信息,并以此为依据预测未来的资源需求,从而实现对空间增长的规划。

Space Reclamation

14.6.6 空间回收

096 The Oracle Database provides in-place reorganization of data for optimal space utilization by shrinking it. Shrinking of a segment makes unused space available to other segments in the tablespace and may improve the performance of queries and DML operations.
Oracle 数据库能够对数据段(segment)进行收缩(shrinking)从而优化空间利用率,且执行收缩操作时无需额外空间(in-place)来重组数据。收缩操作可以释放数据段中的未用空间以供其他数据段使用,且能提高查询及 DML 操作的性能。
097 The segment shrink functionality both compacts the space used in a segment and then deallocates it from the segment. The deallocated space is returned to the tablespace and is available to other objects in the tablespace. Sparsely populated tables may cause a performance problem for full table scans. By performing shrink, data in the table is compacted and the high water mark of the segment is pushed down. This makes full table scans read less blocks run faster.
执行数据段收缩(segment shrink)时,首先需要集中(compact)数据段内的数据,之后再释放段内的未用空间。从数据段中释放的空间将被返还给表空间,供表空间内的其他对象使用。如果表内的数据存储 的较为分散,将会影响全表扫描(full table scan)的性能。执行了收缩操作后,表内的数据将被集中,且数据段的高水位线(high water mark)将被降低。这将使全表扫描需要访问的数据块(block)更少,扫描更迅速。
098 Segment shrink is an online operation – the table being shrunk is open to queries and DML while the segment is being shrunk. Additionally, segment shrink is performed in place. This is an advantage over online table redefinition for compaction and reclaiming space. You can schedule segment shrink for one or all the objects in the database as nightly jobs without requiring any additional space to be provided to the database.
数据段收缩属于联机操作,当表的数据段被收缩时,此表依然可以进行查询或 DML 操作。此外,数据段收缩无需额外的存储空间。本地收缩(in-place)与通过联机重定义(online table redefinition)的方式收缩相比更有优势。用户可以在夜间定期调度作业来执行数据库对象的收缩操作,而无需为数据库提供额外的存储空间。
099 Segment shrink works on heaps, IOTs, IOT overflow segments, LOBs, LOB segments, materialized views, and indexes with row movement enabled in tablespaces with automatic segment space management. When segment shrink is performed on tables with indexes on them, the indexes are automatically maintained when rows are moved around for compaction. User-defined triggers are not fired, however, because compaction is a purely physical operation and does not impact the application.
在采用了自动段空间管理(automatic segment space management)的表空间中,数据段收缩可以作用于堆表(heap-organized table),索引表(index-organized table,IOT),索引表的行溢出段(Row Overflow Area),LOB 对象,LOB 段,物化视图,及允许行移动(row movement enabled)的索引。如果对带有索引的表进行数据段收缩操作,当数据行发生移动时 Oracle 能够自动地维护索引。但用户自定义的触发器不会被触发,因为数据段收缩属于物理操作,对应用程序没有影响。


Segment shrink can be performed only on tables with row movement enabled. Applications that explicitly track rowids of objects cannot be shrunk, because the application tracks the physical location of rows in the objects.


只有允许行移动的表才能够进行数据段收缩操作。如果应用程序中显示地使用了对象的 ROWID,那么这个对象就不能进行收缩,因为应用程序需要通过物理位置来定位对象内的数据行。
101 To easily identify candidate segments for shrinking, the Oracle Database automatically runs the Segment Advisor to evaluate the entire database. The Segment Advisor performs growth trend analysis on individual objects to determine if there will be any additional space left in the object in seven days. It then uses the reclaim space target to select candidate objects to shrink.
Oracle 数据库能够自动地运行数据段顾问工具(Segment Advisor)对数据库进行评估,确定哪些数据段应该进行收缩操作。数据段顾问工具能够针对每个数据库对象进行容量增长趋势分析,预测数据库对象在七天之后是否依然存在可用空间。之后就可以对满足条件的对象进行数据段收缩以回收空间。


The Segment Advisor does not evaluate undo and temporary tablespaces.


103 In addition to using the pre-computed statistics in the workload repository, the Segment Advisor performs sampling of the objects under consideration to refine the statistics for the objects. Although this operation is more resource intensive, it can be used to perform a more accurate analysis.
数据段顾问工具除了可以使用 AWR 中已有的统计信息,也可以直接对对象进行采样使统计信息更为准确。这种操作将消耗大量系统资源,但能提供更为精确的分析结果。
104 Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold. For example, if a row size increases during an update such that it not longer fits into the block, then the Segment Advisor recommends that the segment be reorganized to improve I/O performance.
数据段收缩有助于减少行链接(row chaining),但 Oracle 建议采用对象联机重定义(online redefinition)来消除行链接。数据段顾问工具能够检测出某些超过阈值的行链接情况。例如,如果一个更新操作导致某行的容量超过了一个数据块的最大容量,数据段顾问工具将建议对数据段进行重组以提高 I/O 性能。


The Segment Advisor does not detect chained rows created by inserts.



See Also:



Storage Management

14.7 存储管理

108 Automatic Storage Management provides a vertical integration of the file system and volume manager specifically built for the Oracle database files. ASM distributes I/O load across all available resource to optimize performance while removing the need for manual I/O tuning (spreading out the database files avoids hotspots). ASM helps you manage a dynamic database environment by letting you grow the database size without having to shutdown the database to adjust the storage allocation.
自动存储管理(Automatic Storage Management)将底层的文件系统(file system)及其上的卷管理器(volume manager)集成,用于管理 Oracle 的数据库文件。ASM 能够将 I/O 负载分布到所有可用的存储资源上,从而提高了系统性能且无需大量的手工 I/O 调整(即自动地将数据库文件分散从而避免出现热点(hotspot))。ASM 使用户能够增加数据库容量而无需关闭数据库,从而使数据库存储分配动态化。
109 Automatic Storage Management lets you define a pool of storage (called a disk group) and then the Oracle kernel manages the file naming and placement of the database files on that pool of storage. You can change the storage allocation (adding or removing disks) with SQL statements (CREATE DISKGROUP, ALTER DISKGROUP, and DROP DISKGROUP). You can also manage the disk groups with Enterprise Manager and the Database Configuration Assistant (DBCA).
用户可以通过自动存储管理定义一个存储池(称为磁盘组(disk group)),Oracle 能够自动地基于存储池管理文件命名及文件存储位置。用户可以使用 SQL 语句(CREATE DISKGROUPALTER DISKGROUP,及 DROP DISKGROUP)改变存储分配(添加或移除磁盘)。用户也可以使用企业管理器(Enterprise Manager)或数据库配置助理(Database Configuration Assistant,DBCA)管理磁盘组。
110 The Oracle Database provides a simplified management interface for storage resources. Automatic Storage Management eliminates the need for manual I/O performance tuning. It virtualizes storage to a set of disk groups and provides redundancy options to enable a high level of protection. ASM facilitates non-intrusive storage configuration changes with automatic rebalancing. It spreads database files across all available storage to optimize performance and resource utilization. It is a capability that saves time by automating manual storage and thereby increasing the ability to manage larger databases and more of them with increased efficiency.
ASM 是 Oracle 数据库提供的一个简化的存储资源管理接口。使用 ASM 能够避免手工地调整 I/O 性能。ASM 将存储资源虚拟为一组磁盘组,并提供了冗余选项以实现高度的数据保护能力。ASM 实现了联机的(non-intrusive)存储配置修改及自动负载均衡(automatic rebalancing)功能。ASM 能够将数据库文件分散在可用的存储资源上,从而优化存储性能及资源利用效率。ASM 使手工的存储资源调整工作自动化,从而缩短了工作时间并提高了工作效率,使用户有能力管理更大规模的数据库。

Backup and Recovery

14.8 备份与恢复

112 Oracle provides several features that help you to easily manage backup and recovery. These include the following: Oracle 提供了多个功能帮助用户管理数据库的备份(backup)与恢复(recovery)工作。这些功能包括:

Recovery Manager

14.8.1 系统恢复管理器

114 Oracle Recovery Manager (RMAN) is a powerful tool that simplifies, automates, and improves the performance of backup and recovery operations. RMAN enables one time backup configuration, automatic management of backups and archived logs based on a user-specified recovery window, restartable backups and restores, and test restore/recovery. RMAN implements a recovery window to control when backups expire. This lets you establish a period of time during which it is possible to discover logical errors and fix the affected objects by doing a database or tablespace point-in-time recovery. RMAN also automatically expires backups that are no longer required to restore the database to a point-in-time within the recovery window. Control file autobackup also allows for restoring or recovering a database, even when a RMAN repository is not available.
Oracle 系统恢复管理器(Recovery Manager,RMAN)能够简化备份(backup)与系统恢复(recovery)操作,使之自动化,并提升其性能。RMAN 能够实现完全备份(one time backup configuration),能够根据用户定义的系统恢复窗口(recovery window)管理备份数据及归档日志(archived log),能够实现可重启的(restartable)备份(backup)与数据库恢复(restore),并可对系统恢复及数据库恢复进行测试。RMAN 根据系统恢复窗口来决定备份数据何时过期。在备份数据过期前,用户可以使用这些数据将数据库恢复到系统恢复窗口内的某一时间点时的状态,从而修复受逻辑错误影响的数据库对象。RMAN 还可以自动地令系统恢复窗口之外的备份数据过期。即使 RMAN 资料库(RMAN repository)不可用,用户还可以使用系统自动备份的控制文件数据进行数据库恢复或系统恢复。
115 DBCA can automatically schedule an on disk backup procedure. All you do is specify the time window for the automatic backups to run. A unified storage location for all recovery related files and activities in an Oracle database, called the flash recovery area, can be defined with the initialization parameter DB_RECOVERY_FILE_DEST. All files needed to completely recover a database from a media failure, such as control files, archived log files, Flashback logs, RMAN backups, and so on, are part of the flash recovery area.
DBCA 能够自动地调度磁盘备份程序(on disk backup procedure)。用户只需设定备份程序运行的时间窗口即可。用户可以使用初始化参数 DB_RECOVERY_FILE_DEST 设定一个统一的位置,存储和 Oracle 数据库恢复相关的文件,这个位置被称为快速恢复区(flash recovery area)。发生介质故障(media failure)时对数据库进行完全恢复所需的文件,例如控制文件(control file),归档重做日志文件(archived log file),闪回日志(Flashback log),RMAN 备份数据等,都保存在快速恢复区。
116 Allocating sufficient space to the flash recovery area ensures faster, simpler, and automatic recovery of the Oracle database. Flash recovery actually manages the files stored in this location in an intelligent manner to maximize the space utilization and avoid out of space situations to the extent possible. Based on the specified RMAN retention policy, the flash recovery area automatically deletes obsolete backups and archive logs that are no longer required based on that configuration.
为快速恢复区分配足够的空间,才能保证 Oracle 数据库恢复工作更迅速,更简单,且自动化。快速恢复功能(flash recovery)以智能化的方式管理着其所需的文件,从而使备份空间利用率最高,并能避免扩展备份空间时出现空间不足的问题。快速恢复功能可以根据用户设定的 RMAN 保留周期策略(RMAN retention policy)自动地将快速恢复区中失效的备份文件及归档重做日志文件删除。
117 Incremental backups let you back up only the changed blocks since the previous backup. When the block change tracking feature is enabled, Oracle tracks the physical location of all database changes. RMAN automatically uses the change tracking file to determine which blocks need to be read during an incremental backup and directly accesses that block to back it up. It reduces the amount of time needed for daily backups, saves network bandwidth when backing up over a network, and reduces the backup file storage.
用户可以采用增量备份(incremental backup)方式,只对上次备份后修改过的数据块进行备份。当启用了数据块修改跟踪(block change tracking)功能后,Oracle 将记录所有经过修改的数据块的物理位置。RMAN 能够自动地使用修改记录文件,以确定进行增量备份时需要读取哪些数据块,并能够直接访问数据块来进行备份。这减少了周期性备份所需的时间,节约了网络备份时所需的网络带宽,并缩小了备份文件所占用的存储空间。
118 Incremental backups can be used for updating a previously made backup. With incrementally updated backups, you can merge the image copy of a datafile with a RMAN incremental backup, resulting in an updated backup that contains the changes captured by the incremental backup. This eliminates the requirement to make a whole database backup repeatedly. You can make a full database backup once for a given database and use incremental backups subsequently to keep the full back up updated. A backup strategy based on incrementally updated backups can help keep the time required for media recovery of your database to a minimum.
用户可以使用增量备份的结果更新旧的备份数据。Oracle 具有增量地更新备份数据的功能,用户可以将 RMAN 增量备份数据融入备份的数据文件中,从而得到经过增量更新的备份数据文件。此功能使用户免于重复地执行全库备份操作。用户只需对数据库进行一次全库备份,之后不断地用增量备份数对原备份进行更新即可。采用增量更新备份的策略有助于缩短数据库介质恢复的时间。

See Also:



Mean Time to Recovery

14.8.2 限时恢复

121 Oracle allows for better control over database downtime by letting you specify the mean time to recover (MTTR) from system failures in number of seconds. This, coupled with dynamic initialization parameters, helps improve database availability. After you set a time limit for how long a system failure recovery can take, Oracle automatically and transparently makes sure that the system can restart in that time frame, regardless of the application activity running on the system at the time of the failure. This provides the fastest possible up time after a system failure.
用户可以使用限时恢复(mean time to recover,MTTR)功能设定数据库实例错误(system failure)恢复所允许的最大时间(以秒为单位),从而更好地控制数据库停止服务的时间。限时恢复功能与动态初始化参数(dynamic initialization parameter)相结合,能够帮助用户提高数据库的可用性。当用户设定了最大的实例故障恢复时限后,Oracle 将自动且透明地确保系统在此时限内恢复正常,恢复所需时间与前台的应用程序无关。限时恢复功能可以使出现实例故障的系统以最快的速度恢复。
122 The smaller the online logfiles are, the more aggressively DBWRs do incremental checkpoints, which means more physical writes. This may adversely affect the runtime performance of the database. Furthermore, if you set FAST_START_MTTR_TARGET, then the smallest logfile size may drive incremental checkpointing more aggressively than needed by the MTTR.
联机的重做日志文件(logfile)越小,DBRWs 进程执行增量检查点操作就越频繁,系统中的物理写入操作也越多。这将影响数据库的运行性能。如果用户设置了 FAST_START_MTTR_TARGET 参数,那么较小的重做日志文件会导致增量检查点操作更频繁,甚至超出 MTTR 的需要。
123 The Logfile Size Advisor determines the optimal smallest logfile size from the current FAST_START_MTTR_TARGET setting and the MTTR statistics. A smallest logfile size is considered optimal if it does not drive incremental checkpointing more aggressively than needed by FAST_START_MTTR_TARGET.
重做日志文件容量顾问工具(Logfile Size Advisor)能够根据当前的 FAST_START_MTTR_TARGET 参数设置及 MTTR 统计信息来确定重做日志文件的最优容量。如果重做日志文件的容量不会导致增量检查点操作的执行频率超出 MTTR 的需要,那么这个容量就是最优容量。
124 The MTTR Advisor helps you evaluate the effect of different MTTR settings on system performance in terms of extra physical writes. When MTTR advisor is enabled, after the system runs a typical workload, you can query V$MTTR_TARGET_ADVICE to see the ratio of the estimated number of cache writes under other MTTR settings to the number of cache writes under the current MTTR. For instance, a ratio of 1.2 indicates 20% more cache writes.
MTTR 顾问工具能够统计 MTTR 造成的额外的物理写入操作,从而协助用户评估不同 MTTR 设置对系统性能的影响。MTTR 顾问工具启用,且系统在正常负载下运行一段时间后,用户可以查询 V$MTTR_TARGET_ADVICE 视图,获得其他 MTTR 设置与当前 MTTR 设置下数据库缓存写入操作的比率。例如,比率值为 1.2 表示某一 MTTR 设置将比当前设置增加 20% 的数据库缓存写入操作。
125 By looking at the different MTTR settings and their corresponding cache write ratio, you can decide which MTTR value fits your recovery and performance needs. V$MTTR_TARGET_ADVICE also gives the ratio on total physical writes, including direct writes, and the ratio on total I/O, including reads.
通过查看不同 MTTR 设置及对应的数据库缓存写入操作比率,用户可以确定哪个设置能够同时满足数据库恢复及系统性能的需求。V$MTTR_TARGET_ADVICE 视图中还有不同 MTTR 设置下总物理写入操作的比率(包括直接写入(direct write)),及总体 I/O 操作的比率(包括读取操作)。

See Also:

Oracle Database Backup and Recovery Advanced User's Guide for information on using the MTTR Advisor


Oracle Database Backup and Recovery Advanced User's Guide 了解更多关于使用 MTTR 顾问工具的信息

Self Service Error Correction

14.8.3 自助错误修正

128 Oracle Flashback technology lets you view and rewind data back and forth in time. You can query past versions of schema objects, query historical data, perform change analysis, or perform self-service repair to recover from logical corruptions while the database is online.
用户可以使用 Oracle 回闪查询技术查看数据在不同时间点(向前或向后)的状态,或将当前数据更新为某个时间点时状态。用户可以查询方案对象的历史版本,查询历史数据,或联机执行自助错误修正来修复数据库中的逻辑错误。
129 This revolutionizes recovery by just operating on the changed data. The time it takes to recover the error is equal to the amount of time it took to make the mistake.
用户使用 Oracle 回闪查询技术恢复数据时,只需对被修改过的数据进行操作。因此对数据进行恢复所需的时间大致等于导致错误的操作的执行时间。

See Also:



Configuration Management

14.9 配置管理

132 Enterprise Manager has several powerful configuration management facilities that help detect configuration changes and differences and enforce best practice configuration parameter settings. These capabilities also encompass the underlying hosts and operating systems.
企业管理器(Enterprise Manager)种包含多项配置管理(configuration management)功能,能够监测配置变化及变化前后的差异,并确保系统使用了最优的配置参数。Oracle 的配置管理功能除了针对数据库,还涵盖了底层的硬件及操作系统。
133 Enterprise Manager continuously monitors the configuration of all Oracle systems for such things as best practice parameter settings, security set-up, storage and file space conditions, and recommended feature usage. Non-conforming systems are automatically flagged with a detailed explanation of the specific-system configuration issue. For example, Enterprise Manager advises you to use new functionality such as automatic undo management or locally managed tablespaces if they are not being used. This automatic monitoring of system configurations promotes best practices configuration management, reduces administrator workload and the risk of availability, performance, or security compromises.
企业管理器能够持续地监控其所管理的 Oracle 系统的配置情况,例如配置参数设置,安全设置,存储及空间使用情况,以及推荐功能的使用情况(recommended feature usage)。Oracle 能够自动地标识存在配置问题的系统,并给出详细的问题描述。例如,如果企业管理器发现某个数据库没有使用自动还原管理(automatic undo management)或本地管理的表空间(locally managed tablespace)等新功能,她将会提醒用户及时启用。自动系统配置监控能够确保各个系统配置最优,减轻数据库管理员的工作负担,保证了数据库的可用性,安全性,即高性能。
134 Enterprise Manager also automatically alerts you to new critical patches – such as important security patches – and flags all systems that require that patch. In addition, you can invoke the Enterprise Manager patch wizard to find out what interim patches are available for that installation.
Oracle 发布了新的关键补丁(critical patch)后 —— 例如重要的安全补丁 —— 企业管理器会自动地提醒用户,并标识出所有需要打补丁的系统。此外,用户也可以调用企业管理器的补丁向导(patch wizard)来查找当前系统可用的补丁。

See Also:

Oracle Enterprise Manager Concepts


Oracle Enterprise Manager Concepts

Workload Management

14.10 负载管理

137 Oracle provides the following resource management features: Oracle 提供了以下资源管理功能:

Overview of the Database Resource Manager

14.10.1 数据库资源管理器概述

139 The Database Resource Manager provides the ability to prioritize work within the Oracle system. High priority users get resources, so as to minimize response time for online workers, for example, while lower priority users, such as batch jobs or reports, could take longer. This allows for more granular control over resources and provides features such as automatic consumer group switching, maximum active sessions control, query execution time estimation and undo pool quotas for consumer groups.
数据库资源管理器(Database Resource Manager)能够安排 Oracle 系统内各项操作的优先级。例如,联机用户的优先级较高,因此能够及时获得系统资源从而缩短响应时间,而批处理作业或报表之类的用户优先级较低,因此可能适当等待。数据库资源管理器能够更精细地对 资源进行控制,且其中还提供了自动消费者组切换(automatic consumer group switching),最大活动会话控制(maximum active sessions control),查询执行时间估计,及消费者组还原池容量限额(undo pool quotas for consumer group)等功能。
140 You can specify the maximum number of concurrently active sessions for each consumer group. When this limit is reached, the Database Resource Manager queues all subsequent requests and runs them only after existing active sessions complete.
141 The Database Resource Manager solves many resource allocation problems that an operating system does not manage so well:
  • Excessive overhead. This results from operating system context switching between Oracle database server processes when the number of server processes is high.
  • Inefficient scheduling. The operating system deschedules Oracle database servers while they hold latches, which is inefficient.
  • Inappropriate allocation of resources. The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.
  • Inability to manage database-specific resources.
  • 过度的系统开销(overhead)。当系统内 Oracle 服务进程(server process)数量过多时,服务进程间的操作系统上下文切换(operating system context switching)将导致系统开销过度。
  • 低效率的调度(Inefficient scheduling)。操作系统不会调度拥有闩锁(hold latch)Oracle 进程,这样效率较低。
  • 不恰当的资源分配(inappropriate allocation of resource)。在操作系统中,对所有活动进程的资源分配是均等的,无法调整不同任务的优先级。
  • 无法管理数据库特有的资源(database-specific resource)。
142 With Oracle's Database Resource Manager, you can do the following:
  • Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users
  • Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP (relational on-line analytical processing) applications than to batch jobs.
  • Limit the degree of parallelism of any operation performed by members of a group of users
  • Create an active session pool. This pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs terminate.
  • Allow automatic switching of users from one group to another group based on administrator-defined criteria. If a member of a particular group of users creates a session that runs for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.
  • Prevent the execution of operations that are estimated to run for a longer time than a predefined limit
  • Create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.
  • Configure an instance to use a particular method of allocating resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.
  • Identify sessions that would block a quiesce from completing.
用户可以使用 Oracle 的数据库资源管理器完成以下工作:
  • 无论系统的负载情况如何或用户数量多少,都能保证特定用户至少拥有一定的处理资源(processing resource)。
  • 通过分配 CPU 时间的百分比(percentages of CPU time)将处理资源在不同用户及应用间分配。例如,在数据仓库系统中,应将更多的 CPU 时间分配给 ROLAP(关系型联机分析处理(relational on-line analytical processing))应用,而不是批处理作业(batch job)。
  • 限制某用户组内各成员执行操作时的并行度(degree of parallelism)。
  • 创建活动会话池(active session pool)。活动会话池中的会话个数 是由某用户组被允许的最大用户会话数决定的。超出最大会话数量限制的会话将入队等待执行,用户也可以设定一个超时值,超时的入队作业将被终止(terminate)。
  • 根据管理员定义的条件,自动地将某个用户从一个用户组切换到另一组。例如,如果某一用户组成员所创建的会话的执行时间超过了设定值,此用户将被自动地切换到具备不同资源需求的用户组中。
  • 阻止执行时间可能会超过预设限制的操作执行。
  • 创建还原池(undo pool)。池内的还原空间(undo space)可供某一用户组的成员使用。
  • 使一实例按照某种预定的策略分配资源。用户可以动态地切换资源分配策略 —— 例如从日间策略切换到夜间策略 —— 且无需关闭再重起实例。
  • 找出导致数据库无法进入静默状态(quiesce)的会话。
143 It is thus possible to balance one user's resource consumption against that of other users and to partition system resources among tasks of varying importance, to achieve overall enterprise goals.

Database Resource Manager Concepts 数据库资源管理器概念

145 Resources are allocated to users according to a resource plan specified by the database administrator. The following terms are used in specifying a resource plan:
  • A resource plan specifies how the resources are to be distributed among various users (resource consumer groups).
  • Resource consumer groups let you group user sessions together by resource requirements. Resource consumer groups are different from user roles; one database user can have different sessions assigned to different resource consumer groups.
  • Resource allocation methods determine what policy to use when allocating for any particular resource. Resource allocation methods are used by resource plans and resource consumer groups.
  • Resource plan directives are a means of assigning consumer groups to particular plans and partitioning resources among consumer groups by specifying parameters for each resource allocation method.
系统资源将依据数据库管理员定义的资源计划(resource plan)分配给用户。
  • 资源计划(resource plan)指定了资源如何分配给不同的用户(或资源消费者组)。
  • 管理员使用资源消费者组(resource consumer group)将资源需求类似的用户会话归类。资源消费者组与用户角色(user role)不同,同一数据库用户的不同会话可以属于不同的资源消费者组。
  • 资源分配策略(resource allocation method)是分配特定资源时使用的策略。资源分配策略供资源计划及资源消费者组使用。
  • 管理员使用资源计划指令(resource plan directive)为资源消费者组设定资源计划,或为资源分配策略设定参数从而在资源消费者组间分配资源。
146 The Database Resource Manager also allows for creation of plans within plans, called subplans. Subplans allow further subdivision of resources among different users of an application.
147 Levels provide a mechanism to specify distribution of unused resources among available users. Up to eight levels of resource allocation can be specified.

See Also:



Overview of Services

14.10.2 服务概述

150 Services represent groups of applications with common attributes, service level thresholds, and priorities. Application functions can be divided into workloads identified by services. For example, the Oracle E*Business suite can define a service for each responsibility, such as general ledger, accounts receivable, order entry, and so on. Oracle Email Server can define services for iMAP processes, postman, garbage collector, monitors, and so on. A service can span one or more instances of an Oracle database or multiple databases in a global cluster, and a single instance can support multiple services.
服务(service)表示一组具备相同属性,相同服务级阈值(service level threshold),及相同优先级的应用程序。一个应用系统的功能可以被划分为多组负载节点(workload),并以服务来进行标识。例如,在 Oracle E*Business 套件中可以按功能划分服务,例如总帐功能,应收帐款功能,订单功能等。Oracle Email Server 可以分别将 iMAP 进程,postman,垃圾收集,监视器等功能定义为服务。一个服务可以跨一个数据库的多个实例,还可以跨一个集群的多个数据库,而一个实例也能够支持多个服务。
151 The number of instances offering the service is transparent to the application. Services provide a single system image to manage competing applications, and they allow each workload to be managed as a single unit.
152 Middle tier applications and clients select a service by specifying the service name as part of the connection in the TNS connect data. For example, data sources for the Web server or the application server are set to route to a service. Using Net Easy*Connection, this connection includes the service name and network address. For example, service:IP.
中间层应用程序(middle tier application)及客户端程序通过设定 TNS 连接参数(connect data)中的服务名(service name)来选择一个服务。例如,用户可以为 Web 服务器或应用服务器设定一个服务作为其数据源。在 Net Easy*Connection 工具中,连接参数包括服务名及网络地址。例如,服务名:IP。
153 Server side work, such as the Scheduler, parallel query, and Oracle Streams Advanced Queuing set the service name as part of the workload definition. For the Scheduler, jobs are assigned to job classes, and job classes run within services. For parallel query and parallel DML, the query coordinator connects to a service, and the parallel query slaves inherit the service for the duration of the query. For Oracle Streams Advanced Queuing, streams queues are accessed using services. Work running under a service inherits the thresholds and attributes for the service and is measured as part of the service.
在数据库服务器端的应用,例如调度器(Scheduler),并行查询(parallel query),及 Oracle 数据流高级队列(Oracle Streams Advanced Queuing)均使用服务名来定义其负载节点属性。对于调度器来说,每个作业(job)都属于一个作业类(job class),而每个作业类都利用一个服务来运行。对于并行查询及并行 DML 操作来说,查询协调器(query coordinator)首先连接到一个服务,所有并行查询子进程将继承使用查询协调器所连接的服务。对于 Oracle 数据流高级队列来说,数据流队列需要通过服务来访问。运行于一个服务内的应用,将继承此服务的阈值及属性,也可以被看作是此服务的组成部分。
154 The Database Resource Manager binds services to consumer groups and priorities. This lets services be managed in the database in the order of their importance. For example, you can define separate services for high priority online users and lower priority internal reporting applications. Likewise, you can define gold, silver, and bronze services to prioritize the order in which requests are serviced for the same application.
数据库资源管理器(Database Resource Manager)将服务分别与资源消费者组(resource consumer group)及优先级相绑定。这样,数据库的服务可以按照其重要程度的顺序进行管理。例如,管理员可以为优先级较高的联机用户应用和优先级较低的内部报表应用分别指定不同的服务。类似地,管理员还可以定义多个具有不同优先级的服务,依次接收来自同一应用的请求。
155 When planning the services for a system, include the priority of each service relative to the other services. In this way, the Database Resource Manager can satisfy the highest priority services first, followed by the next priority services, and so on.

Workload Management with Services 通过服务进行工作负载管理

157 The Automatic Workload Repository lets you analyze the performance of workloads using the aggregation dimension for service. The Automatic Workload Repository automatically maintains response time and CPU consumption metrics, performance and resource statistics wait events, threshold-based alerts, and performance indexes for all services.
用户可以通过自动负载资料库(Automatic Workload Repository)对系统负载性能(performance of workload)进行多角度的分析。自动负载资料库能够自动地记录服务的响应时间及CPU 消耗矩阵,与性能统计及资源统计有关的等待事件,基于阈值的告警,以及各项性能指标。
158 Service, module, and action tags identify operations within a service at the server. (MODULE and ACTION are set by the application) End to end monitoring enables aggregation and tracing at service, module, and action levels to identify high load operations. Oracle Enterprise Manager administers the service quality thresholds for response time and CPU consumption, monitors the top services, and provides drill down to the top modules and top actions for each service.
在数据库服务端,服务(service)内的某个操作(operation)可以用服务(service),模块(module),及动作(action)这三种标签(tag)来标识。(模块及动作标签需要由应用程序来设定)通过对客户端的监控,就能够获得服务,模块,及动作级的汇总信息及跟踪信息,从而确定系统内高负载的操作。在 Oracle 企业管理器中可以设置用于服务质量控制的阈值(响应时间及 CPU 消耗),还能够对高负载服务进行监控,并可下钻(drill down)查看每个服务中高负载的模块及动作。
159 With the Automatic Workload Repository, performance management by the service aggregation makes sense when monitoring by sessions may not. For example, in systems using connection pools or transaction processing monitors, the sessions are shared, making accountability difficult.
有时通过监控会话获取的信息对于性能管理没有意义,管理员应该使用自动负载资料库中的服务汇总信息(service aggregation)。例如,如果系统使用了连接池(connection pool)或事务处理监视器(transaction processing monitor,TP monitor),会话是被所有用户共享的,依据会话无法进行准确的统计。
160 The service, module, and action tags provide major and minor boundaries to discriminate the work and the processing flow. This aggregation level lets you tune groups of SQL that run together (at service, module, and action levels). These statistics can be used to manage service quality, to assess resource consumption, to adjust priorities of services relative to other services, and to point to places where tuning is required. With Real Application Clusters (RAC), services can be provisioned on different instances based on their current performance.
用户可以使用服务,模块,及动作这三种标签为业务活动及处理流程划定边界。用户可以根据上述汇总级别对运行于同一级别的一组 SQL 语句进行整体调优(例如服务级,模块级,或动作级)。用户可以使用收集的服务汇总信息管理服务质量,评估资源消耗,调整服务间的优先级,并找出系统中需要进一步调优的位置。在 RAC 系统中,多个实例的处理能力可以作为一个服务供用户使用。
161 Connect time routing and runtime routing algorithms balance the workload across the instances offering a service. The metrics for server-side connection load balancing are extended to include service performance. Connections are shared across instances according to the current service performance. Using service performance for load balancing accommodates nodes of different sizes and workloads with competing priorities. It also prevents sending work to nodes that are hung or failed.
在一个服务的多个实例间进行负载平衡,可以采用连接时(connect time routing)或运行时(runtime routing)平衡算法。用于管理服务端连接负载(server-side connection load)的数据结构中还含了服务性能信息。连接可以依据服务当前的性能而分布到多个实例上。在进行负载均衡时加入服务性能作为判断依据,就能依据各节点的处理能力及当前负载合理分配 新负载,同时还能避免将负载分配给停机或故障的节点。
162 The Automatic Workload Repository maintains metrics for service performance continuously. These metrics are available when routing runtime requests from mid-tier servers and TP monitors to RAC. For example, Oracle JDBC connection pools use the service data when routing the runtime requests to instances offering a service.
自动负载资料库中将持续记录服务性能信息。中间层服务器或 RAC 的事务处理监视器在进行运行时平衡时可以利用这些信息。例如,Oracle JDBC 连接池能够依据服务当前性能信息在运行时将服务请求分配到服务可用的实例上。

High Availability with Services 通过服务实现高可用性

164 RAC use services to enable uninterrupted database operations. Services are tightly integrated with the Oracle Clusterware high availability framework that supports RAC. When a failure occurs, the service continues uninterrupted on the nodes and instances unaffected by the failure. Those elements of the services affected by the failure are recovered fast by Oracle Clusterware, and the recovering sessions are balanced across the surviving system automatically.
RAC 通过服务(service)确保了数据库操作不会被异常事件所中断。服务与支撑 RAC 的 Oracle 集群组件高可用性框架(Clusterware high availability framework)是紧密相连的。当发生实例故障(failure)时,服务能够利用未受影响的节点继续工作。而受实例故障影响的节点能够被 Oracle 集群快速地恢复,且进行恢复工作的会话能够被自动地分布到系统的可用节点上。
165 For planned outages, RAC provides interfaces to relocate, disable, and enable services. Relocate migrates the service to another instance, and, as an option, the sessions are disconnected. To prevent the Oracle Clusterware system from responding to an unplanned failure that happens during maintenance or repair, the service is disabled on the node doing maintenance at the beginning of the planned outage. It is then enabled at the end of the outage.
当发生有计划停机(planned outage)时,RAC 提供了重定位(relocate),禁用,及启用服务的接口。重定位指将服务迁移到其他实例上,用户还可以选择是否在重定位时断开现有的会话连接。为了避免对硬件进行维护或修理时 Oracle 集群系统中出现不可预料的错误,在有计划停机开始前,准备进行维护的节点上的服务将被禁用。这些节点上的服务将在维护结束后被重新启用。
166 These service-based operations, in combination with schema pre-compilation (DBMS_SCHEMA_COPY) on a service basis, minimize the downtime for many planned outages. For example, application upgrades, operating system upgrades, hardware upgrades and repairs, Oracle patches approved for rolling upgrade, and parameter changes can be implemented by isolating one or more services at a time.
利用上述基于服务的功能,再结合基于服务的方案预编译功能(schema pre-compilation)(DBMS_SCHEMA_COPY),能够大大缩短有计划停机造成的服务中断时间。例如,在进行应用系统升级,操作系统升级,硬件升级或修理,轮换升级(rolling upgrade)各节点上的 Oracle 补丁,或修改初始化参数时,都可以将一个或多个节点隔离。
167 The continuous service built into RAC is extended to applications and mid-tier servers. When the state of a service changes, (for example, up, down, or not restarting), the new status is notified to interested subscribers through events and callouts. Applications can use this notification to achieve very fast detection of failures, balancing of connection pools following failures, and balancing of connection pools again when the failed components are repaired. For example, when the service at an instance starts, the event and callouts are used to immediately trigger work at the service.
Oracle 通过 RAC 提供的无间断服务能力能够扩展到应用程序及中间层服务器。当服务的状态发生了改变(例如,启动,停止,或重启动),服务的新状态将以事件或调用(callout)的形式通知相关的系统组件。应用程序可以根据通知信息快速检测数据库故障,停止使用连接池中受故障影响的连接,并在故障组件修复后恢复使用。例如, 当某个实例开始提供服务后,应用程序将被事件或调用触发立即开始使用此服务。
168 When the service at an instance stops, the event is used to interrupt applications using the service at that instance. Using the notification eliminates the client waiting on TCP timeouts. The events are integrated with Oracle JDBC connection pools and Transparent Application Failover (TAF).
当某实例上的服务停止时,正在使用此实例上服务的应用程序能够同过事件获知并及时停止操作。这使客户端不必等待 TCP 超时。Oracle JDBC 连接池及透明应用故障恢复(Transparent Application Failover,TAF)中都集成了服务状态事件。
169 With Oracle Data Guard, production services are offered at the production site. Other standby sites can offer reporting services when operating in read only mode. RAC and Data Guard Broker are integrated, so that when running failover, switchover, and protection mode changes, the production services are torn down at the original production site and built up at the new production site. There is a controlled change of command between Oracle Clusterware managing the services locally and Data Guard managing the transition. When the Data Guard transition is complete, Oracle Clusterware resumes management of the high availability operation automatically.
在 Oracle Data Guard 环境中,生产节点(production site)为业务提供生产服务(production service)。而备用节点(standby site)则可以为只读的报表功能提供服务。RAC 与 Data Guard 代理(Data Guard Broker)是集成的,因此当进行故障恢复(failover),结点切换(switchover),或修改保护模式(protection mode change)时,生产服务将从原来的生产节点迁移到新建的生产节点上。在这个过程中,Data Guard 负责管理整个迁移工作,并控制 Oracle 集群组件对各节点的服务进行管理。而当 Data Guard 的迁移工作完成后,Oracle 集群组件将恢复对高可用性管理的控制。

See Also:



Automatic Storage Management

14.11 自动存储管理

172 Today's large databases demand minimal scheduled downtime, and DBAs are often required to manage multiple databases with an increasing number of database files. Automatic Storage Management lets you be more productive by making some manual storage management tasks obsolete.
在现今的大型数据库系统中,要求有计划的停机时间尽可能地短,且 DBA 常常需要管理多个数据库,其中每个库所使用的数据文件都在不断增长。自动存储管理(Automatic Storage Management)能够完成某些手工存储管理任务,从而提高了 DBA 的工作效率。
173 Automatic Storage Management is a vertical integration of both the file system and the volume manager built specifically for Oracle database files. It extends the concept of stripe and mirror everything (SAME) to optimize performance, while removing the need for manual I/O tuning (distributing the datafile layout to avoid hotspots). Automatic Storage Management helps manage a dynamic database environment by letting you grow the database size without shutting down the database to adjust the storage allocation. Automatic Storage Management also enables low cost modular storage to deliver higher performance and greater availability by supporting mirroring as well as striping.
自动存储管理垂直地集成了文件系统(file system)及专为 Oracle 数据文件定制的卷管理器(volume manager)。自动存储管理支持条带化(stripe)存储且能够对所有数据进行镜像(stripe and mirror everything,SAME),从而提高了系统性能,同时减少了手工 I/O 调整工作(即自动将数据文件分布到不同物理磁盘以避免出现热点(hotspot))。用户可以利用自动存储管理功能管理一个动态的数据库系统,当增加数据库容量时无需关闭数据库就能够调整存储分配。自动存储管理支持条带化及镜像,因此能利用低成本的模块化存储(modular storage)实现更高的性能及更好的可靠性。
174 Automatic Storage Management lets you create a pool of storage, and then it manages the file naming and placement of the database files on that storage. You can change the storage allocation (add to or remove disks) with the following SQL statements: CREATE DISKGROUP, ALTER DISKGROUP, and DROP DISKGROUP.
用户可以使用自动存储管理创建一个存储池,并令自动存储管理来管理数据库文件的命名及在存储池中的位置。用户可以使用以下 SQL 语句修改存储分配设置(添加或移除磁盘):CREATE DISKGROUPALTER DISKGROUP,及 DROP DISKGROUP
175 You can also manage the disk groups with Enterprise Manager. For creating and deleting files, the Oracle Database internally allocates storage space from these disk groups. As part of disk group setup, you can define failure groups as an additional level of redundancy to protect against loss of a subset of disks, such as a disk controller or disk array. Failure groups let Automatic Storage Management place a mirrored copy intelligently, by not putting copies of data on disks that would be inaccessible if a single component failed.
用户也可以使用企业管理器(Enterprise Manager)来管理磁盘组(disk group)。Oracle 数据库在创建或删除数据文件时,能够自动地管理磁盘组中的存储空间。用户在设置磁盘组时,可以定义故障恢复组(failure group)实现冗余,防止磁盘组中某些组件(磁盘控制器(disk controller)或磁盘阵列(disk array))损坏造成数据丢失。自动存储管理使用故障恢复组来存储数据镜像,她能够智能地选择存储数据镜像的磁盘,防止单一设备故障造成的数据丢失。
176 The Oracle Database provides a simplified management interface for storage resources. Automatic Storage Management eliminates the need for manual I/O performance tuning. It simplifies storage to a set of disk groups and provides redundancy options to enable a high level of protection. Automatic Storage Management facilitates non-intrusive storage allocations and provides automatic rebalancing. It spreads database files across all available storage to optimize performance and resource utilization. It also saves time by automating manual storage tasks, which thereby increases their ability to manage more and larger databases with increased efficiency. Different versions of the database can interoperate with different versions of Automatic Storage Management. That is, any combination of release 10.1.x.y and 10.2.x.y for either the Automatic Storage Management instance or the database instance interoperate transparently.
Oracle 数据库为存储资源管理提供了一个简化的接口。使用自动存储管理后,用户不再需要手工调整 I/O 性能。自动存储管理使用一组磁盘组作为存储资源,同时支持冗余设置以实现高度的数据保护。自动存储管理无需中断数据库操作就能进行存储分配,还能自动地实现负载均衡。自动存储管理将数据文件分布在所有可用的存储设备上,从而优化系统性能及资源利用效率。自动存储管理能够使大量手工存储管理工作自动化,为数据库管理员节约时间提高效率,使管理员有能力管理更多或规模更大的数据库。不同版本的数据库及自动存储管理服务能够相互操作。具体来说,10.1.x.y 及 10.2.x.y 版本的数据库实例及自动存储管理实例的任意组合都能够透明地相互操作。

Basic Automatic Storage Management Concepts

14.11.1 自动存储管理基本概念

178 This section defines some of the basic concepts with Automatic Storage Management.
本节定义了与自动存储管理(Automatic Storage Management)相关的一些基本概念。

Disk Groups 磁盘组

180 A disk group is one or more Automatic Storage Management disks managed as a logical unit. The data structures in a disk group are self contained and consume some of the disk space in a disk group. Automatic Storage Management disks can be added or dropped from a disk group while the database is running. Automatic Storage Management rebalances the data to ensure an even I/O load to all disks in a disk group even as the disk group configuration changes.
磁盘组(disk group)是由一组自动存储管理磁盘(Automatic Storage Management disk)构成的逻辑管理单位。磁盘组的数据结构信息存储在磁盘组内部,因此将占用一些磁盘组空间。自动存储管理磁盘可以在数据库运行时添加到磁盘组或从磁盘组中移除。自动存储管理能够在磁盘组的磁盘间均衡的分布数据,以使各磁盘的 I/O 负载平衡,此功能在磁盘组配置发生变化时依然有效。
181 Any single Automatic Storage Management file is contained in a single disk group. However, a disk group can contain files belonging to several databases, and a single database can use storage from multiple disk groups. One or more disk groups can be specified as the default disk group for files created in a database.
每个自动存储管理文件(Automatic Storage Management file)只能存在于一个磁盘组内。但一个磁盘组可以存储属于不同数据库的数据文件,一个数据库也能够使用多个磁盘组提供的存储资源。管理员可以为数据库指定一个或多个默认的磁盘组来存储其数据文件。
182 Disk groups can be created when creating a database or when a new application is developed. Disk groups can also change when its database server configuration is altered.
183 Most installations probably have two disk groups. Reasons for having different disk groups include the following:
  • To group disks of different sizes or performance characteristics together
  • To group disks with different external redundancy together; disks that have the same external redundancy could be in the same disk group, but disks that have different external redundancy should be in different disk groups.
  • To separate database areas and flash recovery areas for a database
  • 应将不同容量或不同性能的磁盘划分为不同的磁盘组
  • 应根据外部冗余能力(external redundancy)来划分磁盘组;具备相同外部冗余能力的磁盘应划入同一磁盘组,而外部冗余能力不同的磁盘不应划为同一磁盘组。
  • 将数据库区(database area)与快速恢复区(flash recovery area)放置在不同的磁盘组

Types of Disk Groups 磁盘组的类型

185 There are three types of disk groups: normal redundancy, high redundancy, and external redundancy. With normal and high redundancy, Automatic Storage Management provides redundancy for all files in the disk group according to the attributes specified in the disk group templates. High redundancy provides a greater degree of protection. With external redundancy, Automatic Storage Management does not provide any redundancy for the disk group. The underlying disks in the disk group should provide redundancy (for example, using a storage array), or the user must be willing to tolerate loss of the disk group if a disk fails (for example, in a test environment).
磁盘组有三种类型:正常冗余(normal redundancy),高冗余(high redundancy),及外部冗余(external redundancy)。对于正常冗余和高冗余的磁盘组来说,自动存储管理(Automatic Storage Management)将根据磁盘组模板(disk group template)中设定的属性为磁盘组内的每个数据文件提供冗余支持。与正常冗余相比,高冗余对数据的保护程度更高。对于外部冗余来说,自动存储管理不为磁盘组提供冗余支持。磁盘组内的磁盘应自己实现冗余(例如,使用磁盘阵列(storage array)),否则当磁盘组内的磁盘出现故障时可能出现数据丢失(例如,测试系统可以采用这种配置)。

Automatic Storage Management Files 自动存储管理文件

187 When the database requests it, Automatic Storage Management creates files. Automatic Storage Management assigns each file a fully qualified name ending in a dotted pair of numbers. You can create more user-friendly alias names for the Automatic Storage Management filenames. To see alias names for Automatic Storage Management files, query the V$ASM_ALIAS data dictionary view. V$ASM_ALIAS can only be queried from an ASM instance. Users can find the names for ASM files by querying the appropriate V$ view in the database instance (V$DATAFILE, V$LOGFILE, V$CONTROLFILE, and so on). In general, users need not be aware of file names.
当数据库需要存储空间时,自动存储管理(Automatic Storage Management)将创建文件。自动存储管理创建的文件的名称由前缀和数字后缀构成。用户可以为自动存储管理文件设定易于理解的别名。通过 V$ASM_ALIAS 数据字典视图可以查询自动存储管理文件的别名。只有在 ASM 实例中才能查询 V$ASM_ALIAS 视图。用户可以通过数据库实例中的某些 V$ 视图(V$DATAFILEV$LOGFILEV$CONTROLFILE 等)查询出 ASM 文件名。但通常来说,用户没必要了解 ASM 文件名。
188 All existing situations where a filename is required are augmented with a mechanism for recognizing Automatic Storage Management file naming syntax.
189 When a file is created, certain file attributes are permanently set. Among these are its protection policy (mirroring) and its striping policy. Automatic Storage Management files are not visible from the operating system or its utilities, but they are visible to database instances, RMAN, and other Oracle-supplied tools.
当创建 ASM 文件时,一些属性将被永久地赋予此文件。文件的属性包括其保护策略(protection policy)(即镜像)及条带化策略(striping policy)。自动存储管理文件对于操作系统及外部工具是不可见的,只有数据库实例,RMAN,及其他 Oracle 提供的工具可以访问。

See Also:

Oracle Database Administrator's Guide for information on Automatic Storage Management files and attributes


Oracle Database Administrator's Guide 了解关于 Automatic Storage Management 文件及相关属性的信息

Automatic Storage Management Templates 自动存储管理模板

192 Automatic Storage Management templates are collections of attributes used by Automatic Storage Management during file creation. Templates simplify file creation by mapping complex file attribute specifications into a single name. A default template exists for each Oracle file type. Each disk group contains its own definition of templates. Templates of the same name can exist in different disk groups with each having their own unique properties.
自动存储管理模板(Automatic Storage Management template)即自动存储管理文件创建时使用的一组文件属性集合。模板将大量复杂的文件属性归纳为一个单一的名称,因而简化了文件创建工作。各种 Oracle 文件类型都有一个默认的模板。每个磁盘组(disk group)都能保存其自身使用的模板的定义。不同的磁盘组可以拥有属性不同的同名模板。
193 You can change the attributes of the default templates or add your own unique templates. This lets you specify the appropriate file creation attributes as a template. If you need to change an Automatic Storage Management file attribute after the file has been created, then the file must be copied using RMAN into a new file with the new attributes.
用户可以修改默认模板的属性,也可以创建自定义模板。即将特定的文件创建属性保存为模板。如果用户需要修改一个已有的自动存储管理文件(Automatic Storage Management file)的属性,只能通过 RMAN 将原有文件复制到具备新属性的文件中去。

See Also:

Oracle Database Administrator's Guide for a table of the default templates


Oracle Database Administrator's Guide 查看关于默认模板的相关信息

Automatic Storage Management Disks 自动存储管理磁盘

196 Storage is added and removed from disk groups in units of Automatic Storage Management (ASM) disks. ASM disks can be entire physical disks, LUNs from a storage array, or pre-created files in a NAS filer. ASM disks should be independent of each other to obtain optimal I/O performance. For instance, with a storage array, you might specify a LUN that represents a hardware mirrored pair of physical disks to ASM as a single ASM disk. If you specify two separate LUNs that are striped by a storage array across the same set of physical drives, then this may cause suboptimal performance.
一个磁盘组(disk group)内最小的存储分配单位为自动存储管理磁盘(Automatic Storage Management disk,ASM disk)。一个 ASM 磁盘可以是一个物理磁盘,也可以是磁盘阵列(storage array)内的一个逻辑单元号(Logical Unit Number,LUN),或网络接入存储(Network Attached Storage,NAS)文件服务器(filer)中的一个文件。ASM 磁盘应相互独立,从而获得最佳的 I/O 性能。例如,使用磁盘阵列时,用户可以赋给一对互为镜像的物理磁盘一个逻辑单元号,做为一个 ASM 磁盘。如果用户在磁盘阵列中将一个物理磁盘条带化(striped)并赋予两个逻辑单元号,这有可能影响 I/O 性能。
197 If using an NAS filer, the files specified as ASM disks must be a multiple of 1 megabyte. For optimal performance, if NAS files are in the same disk group, then they should have independent underlying physical drives.
当使用网络接入存储文件服务器时,作为 ASM 磁盘的文件容量必须是 1 MB 的整数倍。为获得最佳性能,属于同一磁盘组的网络接入存储文件应该各自独立地分布在不同的物理磁盘上。
198 You should not specify a device that contains data that should not be overwritten. For instance, on some operating systems, certain partitions contain the partition table at the beginning of the partition. Such partitions should not be specified as ASM disks.
用户指定的设备不应包含不能被覆盖(overwritten)的数据。例如,在某些操作系统中,某些磁盘分区(partition)的头部包含分区表(partition table)。这样的分区不应被指定为 ASM 磁盘。
199 ASM performs I/O to ASM disks through a single logical path. Therefore, if you are using a multi-pathing driver with ASM, then you should specify the logical path to the ASM disk and ensure that the ASM discovery string (ASM_DISKSTRING initialization parameter) includes only a single logical path to each ASM disk.
自动存储管理在对 ASM 磁盘执行 I/O 操作时,总是通过唯一的逻辑路径(single logical path)。因此,如果用户在 ASM 中使用了多路径驱动器(multi-pathing driver),那么必须为 ASM 磁盘指定一个唯一的逻辑路径,从而保证 ASM 定位字符串(ASM discovery string)(即 ASM_DISKSTRING 初始化参数)中每个 ASM 磁盘对应唯一的逻辑路径。

See Also:

Oracle Database Administrator's Guide for more information on ASM_DISKSTRING


Oracle Database Administrator's Guide 了解关于 ASM_DISKSTRING 初始化参数的信息
201 In a cluster, ASM disks must be visible to all ASM instances in the cluster, but the path to the ASM disk need not be identical on each node, as long as each instance's ASM_DISKSTRING includes the path to the disk for that instance.
在集群(cluster)内,每个 ASM 磁盘必须对集群内的所有 ASM 实例均可见,但每个节点上保存的 ASM 磁盘路径不必完全相同,只要每个实例上的 ASM_DISKSTRING 初始化参数中包含此实例所用的 ASM 磁盘路径即可。
202 A disk name is common to all nodes of the cluster. The name must be specified by the administrator, or it can be automatically generated by Automatic Storage Management when the disk is added to a disk group. The Automatic Storage Management disk name abstraction is required, because different hosts can use different operating system names to refer to the same disk.
在集群(cluster)的所有节点上,ASM 磁盘的名称都是相同的。ASM 磁盘名称由管理员设定,或在磁盘被添加到磁盘组时由 ASM 自动生成。一个 ASM 磁盘除了磁盘名称之外,还需设定摘要信息,因为不同的主机可能使用不同的操作系统名称表示同一个磁盘。
203 Automatic Storage Management provides mirroring to reduce the chances of losing data due to disk failure. This is necessary, because the loss of the otherwise unduplicated data from a single Automatic Storage Management disk could damage every file in the disk group.
自动存储管理提供了镜像功能,以便减少磁盘故障造成数据丢失的可能性。这十分必要,如果一个 ASM 磁盘的未镜像的数据丢失可能导致磁盘组内的多个文件损坏。

Failure Groups 故障恢复组

205 Failure groups are administratively assigned sets of disks sharing a common resource. Failure groups are used to determine which Automatic Storage Management disks to use for storing redundant copies of data. The use of failure groups ensures that data and the redundant copy of the data do not both reside on disks that are likely to fail together.
故障恢复组(failure group)是由管理员指定的一组磁盘构成的。故障恢复组中的自动存储管理磁盘(Automatic Storage Management disk)用于存储数据的冗余备份。使用了故障恢复组后,数据及数据的冗余备份将分别存储在不同磁盘上,从而避免了磁盘故障造成数据及冗余备份同时丢失。
206 The composition of failure groups is site-specific. Failure group decisions are based on what component failures the system can tolerate. For example, suppose you have five disks and one SCSI controller. The failure of the SCSI controller makes all disks unavailable. In this scenario, you should put each disk in its own failure group. However, if you have two SCSI controllers, each with two disks, and you want to tolerate a SCSI controller failure, then you should create a failure group for each controller.
故障恢复组的构成是与设备相关的。用户要根据系统中各组件的容错要求来设定故障恢复组。例如,系统中有五块磁盘及一个 SCSI 控制器(SCSI controller)。如果 SCSI 控制器发生故障将导致所有磁盘失效。在此种情况下,用户应将每个磁盘放入不同的故障恢复组。再例如,系统中有两个 SCSI 控制器,每个控制器上连接了两块磁盘,如果用户需要对 SCSI 控制器进行容错,就应使用每个 SCSI 控制器下的磁盘分别创建故障恢复组。
207 By default, Automatic Storage Management assigns each disk to its own failure group. When creating a disk group or adding a disk to a disk group, you can specify you own grouping of disks into failure groups. Automatic Storage Management can then optimize file layout to reduce the unavailability of data due to failures.
默认情况下,自动存储管理(Automatic Storage Management)使用一块磁盘构成一个故障恢复组。用户也可以在创建磁盘组(disk group)或向磁盘组中添加磁盘时设定一组磁盘作为故障恢复组。自动存储管理优化文件分布以降低设备故障造成数据丢失的可能性。
208 A failure group is maintained in a disk group, and multiple failure groups can exist within any given disk group. However, changing a disk's failure group requires dropping the disk from the disk group and then adding the disk back to the disk group under the new failure group name.

Automatic Storage Management Instances 自动存储管理实例

210 The Automatic Storage Management instance is a special Oracle instance that manages the disks in disk groups. The Automatic Storage Management instance must be configured and running for the database instance to access Automatic Storage Management files. This configuration is done automatically if the Database Configuration Assistant was used for database creation.
自动存储管理实例(Automatic Storage Management instance)是一种特殊的 Oracle 实例,其功能是管理磁盘组(disk group)内的磁盘。数据库实例必须通过为其配置的自动存储管理实例才能存取自动存储管理文件(Automatic Storage Management file)。如果创建数据库时使用了数据库配置助理(Database Configuration Assistant),此工具能够自动地配置数据库所需自动存储管理实例。
211 Automatic Storage Management instances cannot mount databases. The Automatic Storage Management instances simply coordinate data layout for database instances. Database instances do direct I/O to disks in disk groups without going through an Automatic Storage Management instance.
自动存储管理实例不能用于挂载数据库。自动存储管理实例只负责管理数据库实例的数据存储分布(data layout)。数据库实例能够直接对磁盘组中的磁盘进行 I/O 操作,而无须经过自动存储管理实例。
212 Multiple and separate database instances can share disk groups for their files. On a single node, there is typically a single Automatic Storage Management instance on the node, which manages all disk groups. In a Real Application Clusters environment, there is typically one Automatic Storage Management instance on each node managing all disk groups for its node in a coordinated manner with the rest of the cluster.
多个数据库实例可以共享同一个磁盘组。对于只含有一个节点(single node)的系统来说,通常只使用一个自动存储管理实例来管理此节点所需的所有磁盘组。而在 RAC 系统中,通常每个节点都拥有一个自动存储管理实例,她们相互协调共同管理 RAC 所使用的所有磁盘组。
213 All Automatic Storage Management management commands must be directed to the Automatic Storage Management instance, and not to the Oracle database instance using the Automatic Storage Management files.

Automatic Storage Management Instance Background Processes 自动存储管理实例后台进程

215 An Automatic Storage Management instance contains two background processes. One coordinates rebalance activity for disk groups. It is called RBAL. The second one performs the actual rebalance data extent movements. There can be many of these at a time, and they are called ARB0, ARB1, and so forth. An Automatic Storage Management instance also has most of the same background processes as a database instance (SMON, PMON, LGWR, and so on).
自动存储管理实例(Automatic Storage Management instance)包含两种特殊的后台进程。一个进程负责协调磁盘组间的负载均衡操作,此进程被称为 RBAL。第二种进程在数据扩展时实际执行负载均衡操作。此种进程可以有多个,她们依次被称为 ARB0,ARB1,依此类推。自动存储管理实例也有和数据库实例类似的后台进程(SMON,PMON,LGWR,等)。

Database Instance Background Processes 数据库实例后台进程

217 A database instance using an Automatic Storage Management disk group contains a background process called ASMB that communicates with the Automatic Storage Management instance. Another background process called RBAL performs a global open on Automatic Storage Management disks.
使用自动存储管理文件(Automatic Storage Management file)的数据库实例中包含名为 ASMB 的后台进程,其功能是与自动存储管理实例(Automatic Storage Management instance)进行通信。此外还有名为 RBAL 的进程负责启用实例所使用的所有自动存储管理磁盘(Automatic Storage Management disk)。

Benefits of Using Automatic Storage Management

14.11.2 使用自动存储管理的优势

  • Automatic Storage Management provides easier administration.
  • There is no need to specify and manage filenames. Wherever a file is created, a disk group can be specified instead. Every new file automatically gets a new unique name. This prevents using the same filename in two different databases. Disk group naming avoids using two different names for the same file.
  • For many situations, Automatic Storage Management provides the functions provided by external volume managers and file systems.
  • Automatic Storage Management includes storage reliability features, such as mirroring. The storage reliability policy is applied on a file basis, rather than on a volume basis. Hence, the same disk group can contain a combination of files protected by mirroring, parity, or not protected at all.
  • Automatic Storage Management improves performance.
  • Automatic Storage Management maximizes performance by automatically distributing database files across all disks in a disk group. It has the performance of raw disk I/O without the inconvenience of managing raw disks.
  • Unlike logical volume managers, Automatic Storage Management maintenance operations do not require that the database be shut down. This allows adding or dropping disks while the disks are in use.
  • Automatic Storage Management eliminates the need for manual disk tuning. To help manage performance, file creation attributes are controlled by disk group-specific templates.
  • 自动存储管理(Automatic Storage Management)使数据库管理工作更为简单。
  • 管理员无须设定及管理数据文件名称。管理员在创建数据文件时只需指定磁盘组即可。新创建的数据文件都能自动地获得一个唯一的名称。这避免了使用同一磁盘组的数据库使用相同的文件名。使用磁盘组命名(disk group naming)也避免了用不同名称为同一文件命名。
  • 在很多情况下,自动存储管理能够代替外部卷管理器(external volume manager)及文件系统(file system)的功能。
  • 自动存储管理包含了多种存储可靠性功能(storage reliability feature),例如镜像(mirroring)。存储可靠性策略(storage reliability policy)是针对每个文件的,而非针对整个磁盘组。因此,同一磁盘组内的不同文件可以采取不同的保护方式,例如镜像,奇偶检验(parity),或不采取保护措施。
  • 自动存储管理能够提高系统性能。
  • 自动存储管理能够将数据库文件分布在磁盘组内的所有磁盘中,从而提高系统性能。她拥有裸设备(raw disk)的 I/O 性能,但管理相对裸设备而言更为容易。
  • 与逻辑卷管理器(logical volume manager)不同,自动存储管理的维护操作无须数据库停机即可执行。管理员可以动态地添加磁盘,或将使用中的磁盘移除。
  • 自动存储管理减少了手工设置磁盘属性的工作。文件创建时的属性可以来自磁盘组中的预定义模板,因此提高了管理效率。

See Also:

Oracle Database Administrator's Guide for detailed information on using Automatic Storage Management


Oracle Database Administrator's Guide 了解关于自动存储管理的详细信息

Oracle Scheduler

14.12 Oracle 调度器

222 Oracle Database includes a feature rich job scheduler. You can schedule jobs to run at a designated date and time (such as every weeknight at 11:00pm), or upon the occurrence of a designated event (such as when inventory drops below a certain level). You can define custom calendars such as a fiscal year so you can have a schedule such as the last workday of every fiscal quarter.
Oracle 数据库中包含了一个功能丰富的作业调度器(job scheduler)。用户可以使作业在预定的日期时间(例如周一到周五的 23 点)运行,也可以根据预定的事件(例如库存下降到一定程度)触发作业。用户还可以自定义日历,比如通过自定义财务年度(fiscal year)来实现在每个财务季度的最后一个工作日运行作业。
223 You create and manipulate Scheduler objects such as jobs, programs, and schedules with the DBMS_SCHEDULER package or with Enterprise Manager. Because Scheduler objects are standard database objects, you can control access to them with system and object privileges.
用户需要使用 DBMS_SCHEDULER 包或企业管理器(Enterprise Manager)来创建并管理作业(job),程序(program)及调度周期(schedule)等调度器对象(Scheduler object)。调度器对象是标准的数据库对象,因此管理员能够使用系统权限及对象权限来控制用户对此类对象的访问。
224 Program objects (or programs) contain metadata about the command that the Scheduler will run, including default values for any arguments. Schedule objects (schedules) contain information on run date and time and recurrence patterns. Job objects (jobs) associate a program with a schedule, and are the principal object that you work with in the Scheduler. You can create multiple jobs that refer to the same program but that run at different schedules. A job can override the default values of program arguments, so multiple jobs can refer to the same program but provide different argument values.
程序对象(program object)内包含了调度器将要执行的命令的元数据,还包括命令所有参数的默认值。调度周期对象中含有作业的执行日期时间及作业周期执行的模式(recurrence pattern)。作业对象将程序对象和调度周期对象结合到一起,是用户使用调度器时所操作的主要对象。用户可以创建多个作业,引用相同的程序对象,但按照不同的调度周期执行。在作业对象中可以覆盖(override)程序对象中设定的默认参数值,因此不同的作业可以引用相同的程序对象,但使用不同的参数值执行。
225 The Scheduler provides comprehensive job logging in Enterprise Manager and in a variety of views available from SQL*Plus. You can configure a job to raise an event when a specified state change occurs. Your application can process the event and take appropriate action. For example, the Scheduler can page or send an e-mail to the DBA if a job terminates abnormally.
调度器提供了完善的作业日志功能,用户可以使用企业管理器或通过 SQL*Plus 查询视图来查看这些日志。用户可以对作业进行配置使作业状态改变时抛出事件。用户可以在应用程序中监测事件并作出适当的处理。例如,调度器可以在作业异常终止时向 DBA 发送传呼或 e-mail。
226 The Scheduler also includes chains, which are named groups of steps that work together to accomplish a task. Steps in the chain can be a program, subchain or an event, and you specify rules that determine when each step runs and what the dependencies between steps are. An example of a chain is to run programs A and B, and only run program C if programs A and B complete successfully. Otherwise run program D.
调度器中还能够包含作业流程(chain),作业流程是完成一个任务的一系列步骤(step)。作业流程内的步骤可以是程序对象,子作业流程(subchain)或事件(event),用户可以设定规则来决定每个步骤的运行时机及步骤间的依赖关系。例如,一个作业流程首先运行程序对象 A 和 B,只有在 A,B 都成功执行后才运行程序对象 C,否则运行 D。
227 The Scheduler is integrated with the Database Resource Manager. You can associate Scheduler jobs with resource consumer groups, and you can create Scheduler objects called windows that automatically activate different resource plans at different times. Running jobs can then see a change in the resources that are allocated to them when there is a change in resource plan.
调度器是与数据库资源管理器(Database Resource Manager)集成的。用户可以将调度器作业与资源消费者组关联(resource consumer group)。用户也可以创建被称为窗口(window)的调度器对象,在不同时间自动地激活不同的资源计划。当资源计划发生改变后,所有运行作业能够得到的资源也将随之变化。

See Also:

Oracle Database Administrator's Guide for a detailed overview of the Scheduler and for information on how to use and administer the Scheduler


Oracle Database Administrator's Guide 了解关于调度器的详细介绍,以及如何使用与管理调度器

What Can the Scheduler Do?

14.12.1 调度器能够做什么?

230 The Scheduler provides complex enterprise scheduling functionality. You can use this functionality to do the following: 调度器能够满足复杂的企业级调度功能需求。用户可以使用调度器完成以下工作:

Schedule Job Execution 调度作业执行

232 The most basic capability of a job scheduler is to schedule the execution of a job. The Scheduler supports both time-based and event-based scheduling.

Time-based scheduling 依据时间调度

234 Time-based scheduling enables users to specify a fixed date and time (for example, Jan. 23rd 2006 at 1:00 AM), a repeating schedule (for example, every Monday), or a defined rule (for example the last Sunday of every other month or the fourth Thursday in November which defines Thanksgiving).
依据时间调度的方式包括:用户可以设定一个固定的时间(例如,2006 年 1 月 23 日凌晨 1 点),或进行周期调度(例如,每周一),或定义调度规则(例如,隔月的最后一个星期日,或 11 月的第 4 个星期四,也就是感恩节)。
235 Users can create new composite schedules with minimum effort by combining existing schedules. For example if a HOLIDAY and WEEKDAY schedule were already defined, a WORKDAY schedule can be easily created by excluding the HOLIDAY schedule from the WEEKDAY schedule.
236 Companies often use a fiscal calendar as opposed to a regular calendar and thus have the requirement to schedule jobs on the last workday of their fiscal quarter. The Scheduler supports user-defined frequencies which enables users to define not only the last workday of every month but also the last workday of every fiscal quarter.
企业通常使用财务日历(fiscal calendar)而非常规日历,并有在财务季度的最后一个工作日调度作业之类的需求。调度器支持用户自定义频率,因此用户不仅可以定义类似每月最后一个工作日这样的调度周期,也能够定义类似财务季度最后一个工作日一样的调度周期。

Event-Based Scheduling 依据事件调度

238 Event-based scheduling as the name implies triggers jobs based on real-time events. Events are defined as any state changes or occurrences in the system such as the arrival of a file. Scheduling based on events enables you to handle situations where a precise time is not known in advance for when you would want a job to execute.

Define Multi-Step Jobs 定义多步骤作业

240 The Scheduler has support for single or multi-step jobs. Multi-step jobs are defined using a Chain. A Chain consists of multiple steps combined using dependency rules. Since each step represents a task, Chains enable users to specify dependencies between tasks, for example execute task C one hour after the successful completion of task A and task B.
调度器同时支持单步骤及多步骤作业。多步骤作业是通过定义作业流程(chain)来实现的。作业流程是由多个步骤及步骤间的依赖规则组成的。每个步骤对应一个操作,用户在作业流程中可以设定各操作间的依赖关系,例如在成功执行 A,B 操作一小时后执行操作 C。

Schedule Job Processes that Model Business Requirements 使作业的调度执行符合业务的需求

242 The Scheduler enables job processing in a way that models your business requirements. It enables limited computing resources to be allocated appropriately among competing jobs, thus aligning job processing with your business needs. Jobs that share common characteristic and behavior can be grouped into larger entities called job classes. You can prioritize among the classes by controlling the resources allocated to each class. This lets you ensure that critical jobs have priority and enough resources to complete. Jobs can also be prioritized within a job class.
调度器(scheduler)能够确保作业的调度执行符合业务的要求。调度器能将有限的计算资源在相互竞争的作业间进行适当地分配,从而使作业的调度执行符合业务的要求。属性及行为类似的作业可以被归为一组,这样的组称为作业类(job class)。用户可以为每个作业类设定资源分配策略,以达到为作业类划分优先级的目的。这能保证优先级较高的关键作业获得足够的资源。在同一作业类内的作业也可以设定不同的优先级。
243 The Scheduler also provides the ability to change the prioritization based on a schedule. Because the definition of a critical job can change across time, the Scheduler lets you define different class priorities at different times.

Manage and Monitor Jobs 管理及监控作业

245 There are multiple states that a job undergoes from its creation to its completion. All Scheduler activity is logged, and information, such as the status of the job and the time to completion, can be easily tracked. This information is stored in views. It can be queried with Enterprise Manager or a SQL query. The views provide information about jobs and their execution that can help you schedule and manage your jobs better. For example, you can easily track all jobs that failed for user scott.
一个作业从创建开始到执行结束为止,会拥有多种状态。调度器的所有活动及相关信息(例如作业的状态及结束时间)都会被记录,供管理员使用。这些信息以视图的形式存在。用户可以使用企业管理器(Enterprise Manager)或 SQL 语句进行查询。这些视图提供了作业的基本信息及执行情况,有助于用户更好地调度及管理作业。例如,用户能够便捷地查询出属于用户 scott 的失败的作业。
246 In order to facilitate the monitoring of jobs, users can also flag the Scheduler to raise an event if unexpected behavior occurs and indicate the actions that should be taken if the specified event occurs. For example if a job failed an administrator should be notified.

Execute and Manage Jobs in a Clustered Environment 在集群系统中执行及管理作业

247 A cluster is a set of database instances that cooperates to perform the same task. Oracle Real Application Clusters provides scalability and reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a clustered environment. To balance the load on your system and for better performance, you can also specify the service where you want a job to run.
集群(cluster)是一组协同工作完成同一任务的数据库实例。Oracle RAC 能够在不修改应用程序的情况下提高系统的可扩展性及可靠性。调度器完全支持在集群环境下执行作业。为了保证系统负载均衡并获到更好的性能,用户可以设定作业执行所使用的服务(service)。

See Also:


翻译不确定的词汇(格式:黄色背景 )  

[053] customized 定制的
[053] packaged 预制的
[054] homegrown


[001] advisors
[004] Oracle Universal Installer
[005] Enterprise Manager Database Management Console
[005] single database
[005] alert notification
[005] management framework
[008] Database Creation Assistant
[008] standby database
[010] Instant Client
[013] Database Upgrade Assistant
[020] Data Pump
[021] transportable tablespace
[025] self-management infrastructure
[027] Automatic Workload Repository
[032] maintenance window
[033] Database Resource Manager
[037] server-generated alerts
[040] SQL Tuning Advisor
[040] SQL Access Advisor
[040] Memory advisors
[040] Segment Advisor
[041] advisor framework
[044] Automatic Database Diagnostic Monitor
[046] problem classification tree
[051] Automatic Tuning Optimizer
[051] Automatic SQL Tuning
[053] SQL Profiling
[053] first rows
[053] all rows
[060] Automatic Shared Memory Management
[061] initialization runtime parameters
[063] Shared Pool Advisor
[064] Buffer Cache Advisor
[065] Java Pool Advisor
[066] Streams Pool Advisor
[066] logical standby
[074] hard parses
[077] Proactive Space Management
[079] Automatic undo management
[080] Undo Advisor
[084] Oracle-managed files
[097] high water mark
[114] Oracle Recovery Manager (RMAN)
[114] recovery
[114] recovery window
[114] restores
[115] flash recovery area
[115] media failure
[115] control files
[115] archived log files
[115] Flashback logs
[116] Flash recovery
[121] mean time to recover
[123] Logfile Size Advisor
[124] MTTR Advisor
[125] direct writes
[134] patch wizard
[139] Database Resource Manager
[139] automatic consumer group switching
[139] maximum active sessions control
[139] undo pool quotas for consumer groups
[147] Levels
[150] service level thresholds
[153] query coordinator
[164] Oracle Clusterware high availability framework
[165] planned outages
[167] callouts
[168] Transparent Application Failover
[169] Oracle Data Guard
[169] production service
[169] production site
[169] standby site
[169] failover
[169] switchover
[172] Automatic Storage Management
[173] volume manager
[199] ASM discovery string
[219] parity


[020] It automatically manages and schedules multiple, parallel streams of load or unload for maximum throughput.
[029] It maintains deltas of the data for SQL statements between snapshots.
[052] b) the tuning process is fully cost-based, and it naturally accounts for any changes and enhancements done to the query optimizer
[061] On systems with a higher number of users, this also allows them to avoid memory tuning for individual workloads.
[067] At the same time, Oracle maximizes the performance of all the memory-intensive SQL operators by maximizing the number of database areas that are using an optimal amount of PGA memory (cache memory). The rest of the database areas are executed in one-pass mode, unless the PGA memory limit set by PGA_AGGREGATE_TARGET is so low that multipass execution is required to reduce even more the consumption of PGA memory and honor the PGA target limit.
[104] Although segment shrink reduces row chaining, and the Oracle Database recommends online redefinition to remove chained rows, the Segment Advisor actually detects certain chained rows that are above a threshold.
[108] ASM helps you manage a dynamic database environment by letting you grow the database size without having to shutdown the database to adjust the storage allocation.
[122] Furthermore, if you set FAST_START_MTTR_TARGET, then the smallest logfile size may drive incremental checkpointing more aggressively than needed by the MTTR.
[147] Levels provide a mechanism to specify distribution of unused resources among available users. Up to eight levels of resource allocation can be specified.
[153] Work running under a service inherits the thresholds and attributes for the service and is measured as part of the service.
[187] Automatic Storage Management assigns each file a fully qualified name ending in a dotted pair of numbers.
[188] All existing situations where a filename is required are augmented with a mechanism for recognizing Automatic Storage Management file naming syntax.
[196] For instance, with a storage array, you might specify a LUN that represents a hardware mirrored pair of physical disks to ASM as a single ASM disk. If you specify two separate LUNs that are striped by a storage array across the same set of physical drives, then this may cause suboptimal performance.
[201] In a cluster, ASM disks must be visible to all ASM instances in the cluster, but the path to the ASM disk need not be identical on each node, as long as each instance's ASM_DISKSTRING includes the path to the disk for that instance.
[202] The Automatic Storage Management disk name abstraction is required, because different hosts can use different operating system names to refer to the same disk.
[206] The composition of failure groups is site-specific.
[207] By default, Automatic Storage Management assigns each disk to its own failure group.




[011] SQL, PL/SQL, and Java
[011] Overview of Application Development Languages
[130] Overview of Oracle Flashback Features
[130] Recovery Using Oracle Flashback Technology

1、此处 checkpoint 指的是什么?MTTR 是什么?
[046] High checkpoint load and cause; for example, small log files, aggressive MTTR setting

2、对 SQL profile 的理解是否正确?
[053] The most powerful aspect of SQL profiles is that they enable tuning of queries without requiring any syntactical changes and thereby proving a unique database–resident solution to tune the SQL statements embedded in packaged applications.

[114] Control file autobackup also allows for restoring or recovering a database, even when a RMAN repository is not available.

4、system failures 是不是指实例故障?mean time to recover 如何翻译更准确?
[121] Oracle allows for better control over database downtime by letting you specify the mean time to recover (MTTR) from system failures in number of seconds.

5、什么是 Level?怎么用?怎么翻译?
[147] Levels provide a mechanism to specify distribution of unused resources among available users. Up to eight levels of resource allocation can be specified.

6、不确定 job/job class/service 是不是一对一的关系?
[153] For the Scheduler, jobs are assigned to job classes, and job classes run within services.

7、14.10.2 这节要重新梳理。workload 如何翻译?service 到底指什么?workload 和 service 的关系?
[149] - [170]

8、是隔离 service 还是 node?
[166] For example, application upgrades, operating system upgrades, hardware upgrades and repairs, Oracle patches approved for rolling upgrade, and parameter changes can be implemented by isolating one or more services at a time.

9、什么是 global open?
[217] Another background process called RBAL performs a global open on Automatic Storage Management disks.

10、什么是 fixed user database links?
[248] fixed user database links

translator: zw1840@hotmail.com