| Oracle® Database Concepts 11g Release 1 (11.1) Part Number B28318-01 |
|
|
View PDF |
| Oracle® Database Concepts 11g Release 1 (11.1) Part Number B28318-01 |
|
|
View PDF |
Oracle Database 11g 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 11g proactively sends alerts when a problem is anticipated, thus facilitating proactive rather than reactive database management.
This chapter contains the following topics:
The Oracle Universal Installer 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.
The installation process is self-contained to automatically set up the required infrastructure for routine monitoring and administration. The Oracle Enterprise Manager Database Management Console is automatically configured to let you to get started with database administrative tasks without any manual configuration. The Oracle 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 Database server components such as the database, listener, management framework, and so on, are configured for automated startup and shutdown.
See Also:
"Configuration Management" for more information about Oracle Enterprise ManagerThis section includes the following topics:
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, an Oracle Real Application Clusters 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.
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.
See Also:
Chapter 24, "SQL" and Chapter 25, "Supported Application Development Languages" for more information about JDBC, OCI, and OCCI
Oracle Call Interface Programmer's Guide for more information about Instant Client
With the Database Upgrade Assistant (DBUA), you can upgrade any database configuration, including Oracle Real Application Clusters (Oracle 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.
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.
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.
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.
See Also:
Oracle Database Administrator's GuideData 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.
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.
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.
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:
The Automatic Workload Repository (AWR) is a built-in repository that contains performance statistics used by Oracle Database for problem detection and self-tuning purposes. At regular intervals, Oracle Database makes a snapshot of vital statistics and workload information and stores them in the AWR. The data contained in the snapshots is then analyzed by the Automatic Database Diagnostic Monitor (ADDM). The difference between snapshots is compared to determine which SQL statements to capture based on the effect on the system load. This reduces the number of SQL statements that need to be captured over time. By default, the snapshots are taken once every hour and retained in the AWR for 8 days, after which they are automatically purged. You can change both the frequency and the retention period of snapshots.
Snapshots from specific time periods can be preserved in a baseline for comparison with other similar workload periods. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely. There are several types of available baselines in Oracle Database: fixed baselines, moving window baselines, and baseline templates. A fixed baseline corresponds to a fixed, contiguous time period in the past. Fixed baselines captured when the system is operating at an optimal level can be compared with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time. A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the AWR data in the entire AWR retention period can be used to compute metric threshold values. Baseline templates can be used to create baselines for contiguous time periods in the future. There are two types of baseline templates: single and repeating. A single baseline template can be used to create a baseline for a single contiguous time period in the future. This is useful if you know beforehand of a time period that you want to capture in the future. A repeating baseline template can be used to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis.
AWR forms the foundation for all self-management functionality of Oracle Database. It is the source of information that gives Oracle Database a historical perspective on how the database is being used, and enables ADDM to accurately diagnose and resolve potential performance problems.
See Also:
Oracle Database Performance Tuning Guide for information about the Automatic Workload RepositoryBy analyzing the information stored in AWR, the database can identify the need to perform routine maintenance tasks. The automated maintenance tasks infrastructure (known as AutoTask) enables Oracle Database to automatically schedule such operations. AutoTask schedules automatic maintenance tasks to run in a set of Oracle Scheduler windows known as maintenance windows. Maintenance windows are those windows that are members of the Oracle Scheduler window group MAINTENANCE_WINDOW_GROUP.
By default, MAINTENANCE_WINDOW_GROUP contains one window for each day of the week. Weekday windows (Monday through Friday) are configured to be open (active) for 4 hours starting at 10:00 p.m. Weekend windows (Saturday and Sunday) begin at 6:00 a.m. and remain open for 20 hours. You can customize all attributes of these maintenance windows, including start and end time, frequency, days of the week, and so on. You can also add and remove maintenance windows from the group.
The following are the tasks that AutoTask automatically schedules in these maintenance windows:
Optimizer statistics gathering
Automatic Segment Advisor
SQL Tuning Advisor
Using Oracle Enterprise Manager or PL/SQL package procedures, you can adjust which of these tasks run in which maintenance windows.
Limiting Automatic Maintenance Task Resource Allocation
The impact of automated maintenance tasks on normal database operations is limited by the default Database Resource Manager resource plan. You can modify the default plan, or create your own resource plans and activate them either at the system-wide level or at the individual maintenance window level. AutoTask runs all automatic maintenance tasks as Oracle Scheduler jobs that belong to particular resource consumer groups. Resource plans then limit CPU resources that are allocated to these resource consumer groups. Because your user applications can be assigned to resource consumer groups, you can adjust the resource allocation for maintenance tasks not only relative to other maintenance tasks, but also relative to your applications.
See Also:
Oracle Database Administrator's Guide and Oracle Database 2 Day DBA for instructions for managing automatic maintenance tasks
Oracle Database Administrator's Guide for information about Automatic Segment Advisor
Oracle Database Performance Tuning Guide for information about SQL Tuning Advisor
Oracle Database includes an advanced fault diagnosability infrastructure for preventing, detecting, diagnosing, and resolving problems. The problems that are targeted are critical errors such as those caused by database code bugs, metadata corruption, and customer data corruption. The goals of the advanced fault diagnosability infrastructure are the following:
Detecting problems proactively
Limiting damage and interruptions after a problem is detected
Reducing problem diagnostic time
Reducing problem resolution time
Simplifying customer interaction with Oracle Support
The keys to achieving these goals are the following technologies:
The Health Monitor, which performs deeper analysis of a critical error upon detection, creates health check reports and adds these reports to the diagnostic data collected for the error. The DBA can also manually invoke health checks and obtain reports.
First-failure data capture, which captures comprehensive diagnostic data upon the first occurrence of a critical error
Standardized trace and dump formats for easier analysis
Incident packaging service, which enables the DBA to automatically package all diagnostic information surrounding a critical error into an archive suitable for transmission to Oracle Support.
Data Recovery Advisor, which displays data corruption problems, assesses the extent of the problems, and recommends repair options
SQL Test Case Builder, which helps Oracle Support reproduce customer problems that are related to SQL failures
Support Workbench, which is a guided workflow that assists you with capturing critical error diagnostic information, transmitting it to Oracle Support, and filing a service request
See Also:
Oracle Database Administrator's Guide for more information on the fault diagnosability infrastructure and on the Support WorkbenchThis section further discusses two components of this new infrastructure:
The Automatic Diagnostic Repository (ADR) is a file-based repository for database diagnostic data such as traces, the alert log, health monitor reports, and more. It has a unified directory structure across multiple instances and multiple products. Beginning with Oracle Database 11g, the database, Automatic Storage Management (ASM), and other Oracle products or components store all diagnostic data in the ADR. Each instance of each product stores diagnostic data underneath its own ADR home directory. For example, in an Oracle Real Application Clusters environment with shared storage and ASM, each database instance and each ASM instance has a home directory within the ADR. ADR's unified directory structure, consistent diagnostic data formats across products and instances, and a unified set of tools enable customers and Oracle Support to correlate and analyze diagnostic data across multiple instances.
A DBA can automatically and easily gather all diagnostic data (traces, health check reports, SQL test cases, and more) pertaining to a critical error and package the data into a zip file suitable for transmission to Oracle Support. Because all diagnostic data relating to a critical error are tagged with that error's incident number, the DBA does not have to search through trace files and other files to determine the files that are required for analysis; the incident packaging service identifies all required files automatically and adds them to the package.
See Also:
Oracle Database Administrator's Guide for more information about these components
Oracle Database Net Services Administrator's Guide and Oracle Database Net Services Reference for information on ADR usage
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. Oracle Database can monitor itself and send out alerts to notify you of any problem in an efficient and timely manner.
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 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 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.
To ensure the consistency and uniformity in the way advisors function and allow them to interact with each other seamlessly, 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.
See Also:
Oracle Database 2 Day DBA for more information about using advisorsActive entities that attempt to obtain restrictive access to shared resources or request services from other Oracle Database processes, sessions, and transactions are in danger of hanging. A hang chain is a chain of processes with each one waiting on a resource held by the next, with a single process serving as the root of the hang.
Hangs in Oracle Database can cost a great deal in terms of system unavailability. Specifically, hangs lead to the following problems:
Extended system outages. These outages may occur frequently before a fix is found, which adds to the total downtime.
Analyzing the hang to determine where the problem lies can be lengthy, complex, and prone to error.
The Hang Manager is an Oracle Database infrastructure that can detect hangs, analyze them, and then obtain the required diagnostic data from Oracle. The Hang Manager is enabled by default in Oracle RAC databases and Automatic Storage Management (ASM) instances. Hang manager data is output to trace files.
Building upon the data captured in AWR, the Automatic Database Diagnostic Monitor (ADDM) lets 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.
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 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 Database 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
Oracle RAC-specific issues
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.
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.
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 Oracle Database and requires no intervention.
After identifying the top resource-consuming SQL statements, Oracle Database can automatically analyze them and recommend solutions using the Automatic SQL Tuning Advisor. 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.
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.
The recommendation of the Automatic SQL Tuning Advisor can fall into one of the following categories
Statistics Analysis: The Automatic SQL Tuning Advisor 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 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 SQL Tuning Advisor 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 SQL Tuning Advisor 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 SQL Tuning Advisor 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.
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.
The SQL Access Advisor can automatically analyze the schema design for a given workload and recommend indexes, function-based indexes, partitions, 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.
While generating recommendations, the SQL Access Advisor considers the impact of adding new indexes, partitions, 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.
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.
See Also:
Oracle Database Performance Tuning Guide for more information about the SQL Tuning Advisor and the SQL Access AdvisorOracle Database memory management allows for dynamic resizing of system global area (SGA) and program global area (PGA) memory components, either automatically or manually.
Automatic Memory Management
By default, new database installations are configured to automatically tune the various components of the SGA and PGA. You can make simple high-level adjustments to memory allocation by changing one database parameter: MEMORY_TARGET. As you allocate more system memory to the database with this parameter, the database automatically adjusts various component sizes for optimal database performance.
The performance of each component is monitored by the Oracle database 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. The database arrives at optimal distribution by taking into consideration long term and short terms trends.
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.
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.
Manual Memory Management and Memory Advisors
If you want to exercise more precise control over allocation for multiple memory components, you can enable manual memory management. You can then take advantage of a set of memory advisors, which graphically display current component sizes and the estimated affect of changing these sizes.
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 database 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.
The Buffer Cache Advisor determines the optimal size of the buffer cache. When manually configuring memory for 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, run a representative workload on the instance, and then examine 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.
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.
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. AWR reports on the V$STREAMS_POOL_ADVICE view and CPU usage to help you tune Streams performance.
The Program Global Area (PGA) Advisor helps you determine an appropriate setting for PGA_AGGREGATE_TARGET, which is the total amount of memory to allocate for all PGAs for server and background processes.
See Also:
Oracle Database Performance Tuning Guide for more information about memory advisors
Oracle Database Administrator's Guide for information about the various initialization parameters for manual and automatic memory management, and for information about server parameter files
Oracle Database automatically manages its space consumption, sends alerts on potential space problems, and recommends possible solutions. Oracle Database features that help you to easily manage space include the following:
Earlier releases of Oracle Database used rollback segments to store undo. Space management for these rollback segments was complex. Automatic undo management eliminates the complexities of managing rollback segments by automatically managing space in an undo tablespace. Automatic undo management also optimally tunes the length of time that undo is retained before being overwritten. This automatic tuning of undo retention improves the success rate of long running queries and of certain Oracle Flashback features, which may require the presence of old undo information.
Although you can configure the database to use rollback segments, automatic undo management is the default. An autoextending undo tablespace is automatically created upon database installation.
Automatic tuning of undo retention generally achieves better results with a fixed size undo tablespace. If you want to change the undo tablespace to fixed size for this or other reasons, the Undo Advisor can help you determine the proper fixed size to allocate. You provide the desired undo retention period for your long-running queries or Oracle Flashback operations, and the Undo Advisor suggests the required undo tablespace size. The Undo Advisor makes its recommendations 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:
"Introduction to Undo Segments and Automatic Undo Management"
Oracle Database 2 Day DBA for information about managing undo and running the Undo Advisor
Oracle Database Administrator's Guide for more information about the undo tablespace and on undo retention
With Oracle-managed files, you do not need to directly manage the files comprising an Oracle database. Oracle Database 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 Database 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 Database 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 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 Oracle 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.
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.
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 Oracle Enterprise Manager.
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. Oracle Database offers several features to address these challenges.
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.
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, Oracle Database tracks historical space utilization in the AWR and uses this information to predict the future resource requirements.
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.
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 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.
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.
Note:
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.To easily identify candidate segments for shrinking, 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.
Note:
The Segment Advisor does not evaluate undo and temporary tablespaces.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.
Although segment shrink reduces row chaining, and 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.
Note:
The Segment Advisor does not detect chained rows created by inserts.See Also:
"Row Chaining and Migrating" for more information about row chaining
Oracle Database Administrator's Guide and Oracle Database 2 Day DBA for more information about using the Segment Advisor
Automatic Storage Management (ASM) provides a vertical integration of the file system and volume manager specifically built for Oracle database files. ASM distributes I/O load across all available resources 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 enabling you to increase a database's size without having to shutdown the database to adjust the storage allocation.
ASM 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 disk group. You can change the storage allocation, such as by adding or removing disks, by using SQL statements such as CREATE DISKGROUP, ALTER DISKGROUP, and DROP DISKGROUP. You can also manage disk groups with Oracle Enterprise Manager and Database Configuration Assistant (DBCA).
Oracle Database provides a simplified management interface for storage resources. ASM 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. ASM reduces your storage administrative overhead by automating manual storage and thereby increasing your ability to manage larger databases and more of them with increased efficiency.
Oracle Database provides several features that help you to easily manage backup and recovery. These include the following:
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.
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.
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.
Incremental backups let you back up only the changed blocks since the previous backup. When the block change tracking feature is enabled, Oracle Database 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 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 Database 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 Database 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.
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.
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.
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.
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.
See Also:
Oracle Database Backup and Recovery User's Guide for information about using the MTTR AdvisorOracle 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.
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 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.
Oracle 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, Oracle 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 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 Oracle Enterprise Manager patch wizard to find out what interim patches are available for that installation.
See Also:
Oracle Enterprise Manager ConceptsOracle Database provides the following resource management features:
The Database Resource Manager provides the ability to prioritize work within the Oracle database 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.
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.
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.
With the 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
Note:
Switching users or preventing operations could be based on amount of I/O, as well as amount of CPU time.See Also:
Oracle Database Administrator's Guide for more information about automatic switchingCreate 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.
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.
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.
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.
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:
Oracle Database Administrator's Guide for information about using the Database Resource Manager
Oracle Database Performance Tuning Guide for information about how to tune resource plans
Services represent groups of applications or a subset of a large application 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 module, such as general ledger, accounts receivable, order entry, and so on. Oracle Mail 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 cluster, and a single instance can support multiple services.
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.
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.
Server side work, such as the Scheduler, parallel execution, 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 execution and parallel DML, the query coordinator connects to a service, and the parallel execution processes 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.
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.
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.
This section includes the following topics:
AWR lets you analyze the performance of workloads using the aggregation dimension for service. AWR automatically maintains response time and CPU consumption metrics, performance and resource statistics wait events, threshold-based alerts, and performance indexes for all services.
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.
With AWR, 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.
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 Oracle Real Application Clusters (Oracle RAC), services can be provisioned on different instances based on their current performance.
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.
AWR maintains metrics for service performance continuously. These metrics are available when routing runtime requests from mid-tier servers and TP monitors to Oracle RAC. For example, Oracle JDBC connection pools use the service data when routing the runtime requests to instances offering a service.
Oracle RAC use services to enable uninterrupted database operations. Services are tightly integrated with the Oracle Clusterware high availability framework that supports Oracle 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. For planned outages, Oracle 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. 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.The continuous service built into Oracle 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. 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, Oracle Data Provider for .Net Connection Pools, and Oracle Call Interface, including Transparent Application Failover (TAF).
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. Oracle 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.
Today's large databases demand minimal scheduled downtime, and DBAs are often required to manage multiple databases with an increasing number of database files. ASM lets you be more productive by making some manual storage management tasks obsolete.
ASM 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). ASM helps manage a dynamic database environment by letting you increase the database size without shutting down the database to adjust the storage allocation. ASM also enables low cost modular storage to deliver higher performance and greater availability by supporting mirroring as well as striping.
ASM 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.
You can also manage the disk groups with Oracle 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 ASM place a mirrored copy intelligently, by not putting copies of data on disks that would be inaccessible if a single component failed.
Oracle Database provides a simplified management interface for storage resources. ASM 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. ASM 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 ASM. That is, any combination of release 10.1.x.y and 10.2.x.y for either the ASM instance or the database instance interoperate transparently.
This section includes the following topics:
This section defines some of the basic concepts with ASM. This section includes the following topics:
A disk group is one or more ASM 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. ASM disks can be added or dropped from a disk group while the database is running. ASM rebalances the data to ensure an even I/O load to all disks in a disk group even as the disk group configuration changes.
Any single ASM 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.
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.
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
There are three types of disk groups: normal redundancy, high redundancy, and external redundancy. With normal and high redundancy, ASM 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, ASM 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).
When the database requests it, ASM creates files. ASM assigns each file a fully qualified name ending in a dotted pair of numbers. You can create more user-friendly alias names for the ASM filenames. To see alias names for ASM 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.
Note:
All existing situations where a filename is required are augmented with a mechanism for recognizing ASM file naming syntax.When a file is created, certain file attributes are permanently set. Among these are its protection policy (mirroring) and its striping policy. ASM files are not visible from the operating system or its utilities, but they are visible to database instances, RMAN, and other Oracle-supplied tools.
To provide greater scalability and to improve performance, as ASM file sizes increase, the size of each new extent also increases automatically. Because ASM can dynamically resize extents, fewer extent pointers are needed to describe the file. This results in less memory being required to manage the extent maps. This feature improves ASM performance when opening and accessing large files (up to 128 TB) that are required for very large databases (VLDBs).
See Also:
Oracle Database Storage Administrator's Guide for information about ASM files and attributesASM templates are collections of attributes used by ASM 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.
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 ASM file attribute after the file has been created, then the file must be copied using RMAN into a new file with the new attributes.
See Also:
Oracle Database Storage Administrator's Guide for a table describing the ASM default templatesStorage is added and removed from disk groups in units of 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.
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.
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.
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.
See Also:
Oracle Database Storage Administrator's Guide for more information about the
ASM_DISKSTRING parameterIn 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.
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 ASM when the disk is added to a disk group. The ASM disk name abstraction is required, because different hosts can use different operating system names to refer to the same disk.
ASM provides mirroring to avoid data loss due to disk failure. This is necessary, because the loss of unduplicated data from a single ASM disk could damage every file in a disk group.
Failure groups are administratively assigned sets of disks sharing a common resource. Failure groups are used to determine which ASM 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.
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.
By default, ASM 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. ASM can then optimize file layout to reduce the unavailability of data due to failures.
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.
The ASM instance is a special Oracle database instance that manages the disks in disk groups. The ASM instance must be configured and running to enable the database instance to access ASM files. This configuration is done automatically if Database Configuration Assistant was used for database creation.
ASM instances cannot mount databases. The ASM instances simply coordinate data layout for database instances. Database instances direct the I/O to disks in disk groups without going through an ASM instance.
Multiple and separate database instances can share disk groups for their files. On a single node, there is typically a single ASM instance on the node that manages all of the disk groups. In an Oracle RAC environment, there is typically one ASM instance on each node managing all of the disk groups for its node in a coordinated manner with the rest of the cluster.
All ASM management commands must be directed to the ASM instance, and not to the Oracle database instance using the ASM files.
You can perform rolling upgrades of ASM instances clustered environments by using the rolling upgrade mode. This enables the ASM instances to operate with multiple ASM versions. When an environment is in rolling upgrade mode, you can upgrade or patch the ASM instances one at a time without affecting database availability. You can use this feature in environments that run Oracle Database 11g release 1 (11.1) or later. For more information about ASM rolling upgrades, refer to the Oracle Database Storage Administrator's Guide.
An ASM instance contains two background processes. One process, referred to as RBAL, coordinates rebalance activity for disk groups. A second process performs the actual rebalance data extent movements. Multiple ASM rebalancing processes can exist at one time, and they are referred to as ARB0, ARB1, and so on. An ASM instance also has most of the same background processes as a database instance, such as SMON, PMON, LGWR, and so on.
Use the SYSASM privilege to perform ASM administration tasks instead of the SYSDBA role. Using the SYSASM privilege instead of the SYSDBA privilege provides a clearer division of responsibility between ASM administration and database administration. For more information about the SYSASM privilege, refer to the Oracle Database Storage Administrator's Guide.
ASM 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, ASM provides the functions provided by external volume managers and file systems.
ASM 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.
ASM improves performance.
ASM 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, ASM maintenance operations do not require that the database be shut down. This allows adding or dropping disks while the disks are in use.
ASM eliminates the need for manual disk tuning. To help manage performance, file creation attributes are controlled by disk group-specific templates.
See Also:
Oracle Database Storage Administrator's Guide for detailed information about using ASMOracle 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 the last workday of every fiscal quarter.
You create and manipulate Scheduler objects such as jobs, programs, and schedules with the DBMS_SCHEDULER package or with Oracle Enterprise Manager. Because Scheduler objects are standard database objects, you can control access to them with system and object privileges.
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 about 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.
The Scheduler provides comprehensive job logging in Oracle Enterprise Manager and in a variety of views available from SQL*Plus. You can configure a job to raise an event when a specified job 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.
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.
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. A Scheduler job can name a window as its schedule instead of a schedule object. Such a job runs when the named window opens. Additionally, windows can be grouped into window groups, and a job can name a window group as its schedule. Such a job runs whenever any of the windows in the named window group opens.
See Also:
Oracle Database Administrator's Guide for a detailed overview of the Scheduler and for information about how to use and administer the SchedulerThe Scheduler provides complex enterprise scheduling functionality. You can use this functionality to do the following:
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 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).
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.
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.
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.
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.
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.
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.
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 Oracle 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.
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.
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.
See Also:
Oracle Database Administrator's Guide for more information about transferring files with the DBMS_SCHEDULER package and also the DBMS_FILE_TRANSFER package
Oracle Database SQL Language Reference for more information about fixed user database links
