Sunday, October 21, 2012

Concepts for Database Administrators part 2

Data Repair

While several problems can halt the normal operation of a database or affect I/O operations, only the following typically require DBA intervention and data repair:
  • Media failures
    A media failure occurs when a problem external to the database prevents it from reading from or writing to a file. Typical media failures include physical failures, such as head crashes, and the overwriting, deletion, or corruption of a database file. Media failures are less common than user or application errors, but a sound recovery strategy must prepare for them.
  • User errors
    A user or application may make unwanted changes to your database, such as erroneous updates, deleting the contents of a table, or dropping database objects (see "Human Errors"). A good backup and recovery strategy enables you to return your database to the desired state, with the minimum possible impact upon database availability, and minimal DBA effort.
Typically, you have multiple ways to solve the preceding problems. This section summarizes some of these solutions.
See Also:
Oracle Database 2 Day DBA and Oracle Database Backup and Recovery User's Guide for data repair concepts
Data Recovery Advisor
The Data Recovery Advisor tool automatically diagnoses persistent data failures, presents appropriate repair options, and executes repairs at the user's request. By providing a centralized tool for automated data repair, Data Recovery Advisor improves the manageability and reliability of an Oracle database and thus helps reduce recovery time.
The database includes a framework called Health Monitor for running diagnostic checks. A checker is a diagnostic operation or procedure registered with Health Monitor to assess the health of the database or its components. The health assessment is known as a data integrity check and can be invoked reactively or proactively.
A failure is a persistent data corruption detected by a data integrity check. Failures are normally detected reactively. A database operation involving corrupted data results in an error, which automatically invokes a data integrity check that searches the database for failures related to the error. If failures are diagnosed, then the database records them in the Automatic Diagnostic Repository (ADR).
After failures have been detected by the database and stored in ADR, Data Recovery Advisor automatically determines the best repair options and their impact on the database. Typically, Data Recovery Advisor generates both manual and automated repair options for each failure or group of failures.
Before presenting an automated repair option, Data Recovery Advisor validates it for the specific environment and for the availability of media components required to complete the proposed repair. If you choose an automatic repair, then Oracle Database executes it for you. The Data Recovery Advisor tool verifies the repair success and closes the appropriate failures.
See Also:
Oracle Database 2 Day DBA and Oracle Database Backup and Recovery User's Guide to learn how to use Data Recovery Advisor
Oracle Flashback Technology
Oracle Database provides a group of features known as Oracle Flashback Technology that support viewing past states of data, and winding data back and forth in time, without needing to restore backups. Depending on the database changes, flashback features can often reverse unwanted changes more quickly and with less impact on availability than media recovery.
The following flashback features are most relevant for backup and recovery:
  • Flashback Database
    You can rewind an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors. Flashback Database can also be used to complement Data Guard, Data Recovery Advisor, and for synchronizing clone databases. Flashback Database does not restore or perform media recovery on files, so you cannot use it to correct media failures such as disk crashes.
  • Flashback Table
    You can rewind tables to a specified point in time with a single SQL statement. You can restore table data along with associated indexes, triggers, and constraints, while the database is online, undoing changes to only the specified tables. Flashback Table does not address physical corruption such as bad disks or data segment and index inconsistencies.
  • Flashback Drop
    You can reverse the effects of a DROP TABLE operation. Flashback Drop is substantially faster than recovery mechanisms such as point-in-time recovery and does not lead to loss of recent transactions or downtime.
See Also:
Block Media Recovery
A block corruption is a data block that is not in a recognized Oracle format, or whose contents are not internally consistent (see "Data Corruption"). Block media recovery is a technique for restoring and recovering corrupt data blocks while data files are online. If only a few blocks are corrupt, then block recovery may be preferable to data file recovery.
See Also:
Oracle Database Backup and Recovery User's Guide to learn how to perform block media recovery
Data File Recovery
Data file recovery repairs a lost or damaged current data file or control file. It can also recover changes lost when a tablespace went offline without the OFFLINE NORMAL option.
Media recovery is necessary if you restore a backup of a data file or control file or a data file is taken offline without the OFFLINE NORMAL option. The database cannot be opened if online data files needs media recovery, nor can a data file that needs media recovery be brought online until media recovery completes.
To restore a physical backup of a data file or control file is to reconstruct it and make it available to Oracle Database. To recover a backup is to apply archived redo log files, thereby reconstructing lost changes. RMAN can also recover data files with incremental backups, which contain only blocks modified after a previous backup.
Unlike instance recovery, which automatically applies changes to online files, media recovery must be invoked by a user and applies archived redo log files to restored backups. Data file media recovery can only operate on offline data files or data files in a database that is not opened by any instance.
Data file media recovery differs depending on whether all changes are applied:
  • Complete recovery
    Complete recovery applies all redo changes contained in the archived and online logs to a backup. Typically, you perform complete media recovery after a media failure damages data files or the control file. You can perform complete recovery on a database, tablespace, or data file.
  • Incomplete recovery
    Incomplete recovery, also called database point-in-time recovery, results in a noncurrent version of the database. In this case, you do not apply all of the redo generated after the restored backup. Typically, you perform point-in-time database recovery to undo a user error when Flashback Database is not possible.
    To perform incomplete recovery, you must restore all data files from backups created before the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. Resetting the logs creates a new stream of log sequence numbers starting with log sequence 1.
    Note:
    If current data files are available, then Flashback Database is an alternative to DBPITR.
    The tablespace point-in-time recovery (TSPITR) feature lets you recover one or more tablespaces to a point in time older than the rest of the database.
See Also:

Memory Management

Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change. Initialization parameter settings determine how SGA and instance PGA memory is managed.
Figure 18-4 shows a decision tree for memory management options. The following sections explain the options in detail.
Figure 18-4 Memory Management Methods
Description of Figure 18-4 follows
Description of "Figure 18-4 Memory Management Methods"
See Also:
Chapter 14, "Memory Architecture" to learn more about the SGA and PGA

Automatic Memory Management

In automatic memory management, Oracle Database manages the SGA and instance PGA memory completely automatically. This method is the simplest and is strongly recommended by Oracle.
The only user-specified controls are the target memory size initialization parameter (MEMORY_TARGET) and optional maximum memory size initialization parameter (MEMORY_MAX_TARGET). Oracle Database tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.
Figure 18-5 shows a database that sometimes processes jobs submitted by online users and sometimes batch jobs. Using automatic memory management, the database automatically adjusts the size of the large pool and database buffer cache depending on which type of jobs are running.
Figure 18-5 Automatic Memory Management
Description of Figure 18-5 follows
Description of "Figure 18-5 Automatic Memory Management"
If you create your database with DBCA and choose the basic installation option, then automatic memory management is enabled by default.
See Also:
Oracle Database 2 Day DBA and Oracle Database Administrator's Guide to learn about automatic memory management

Shared Memory Management of the SGA

If automatic memory management is not enabled, then the system must use shared memory management of the SGA. Shared memory management is possible in either of the following forms:
  • Automatic shared memory management
    This mode enables you to exercise more direct control over the size of the SGA and is the default when automatic memory management is disabled. The database tunes the total SGA to the target size and dynamically tunes the sizes of SGA components. Oracle Database remembers the sizes of the automatically tuned components across instance shutdowns if you are using a server parameter file.
  • Manual shared memory management
    In this mode, you set the sizes of several individual SGA components and manually tune individual SGA components on an ongoing basis. You have complete control of individual SGA component sizes. The database defaults to this mode when both automatic memory management and automatic shared memory management are disabled.
See Also:
Oracle Database 2 Day DBA and Oracle Database Administrator's Guide to learn about shared memory management

Memory Management of the Instance PGA

If automatic memory management is not enabled, then the following modes are possible for management of PGA memory:
  • Automatic PGA memory management
    When automatic memory management is disabled and PGA_AGGREGATE_TARGET is set to a nonzero value, the database uses automatic PGA memory management. In this mode, the PGA_AGGREGATE_TARGET specifies a target size for the instance PGA. The database then tunes the size of the instance PGA to this target and dynamically tunes the sizes of individual PGAs. If you do not explicitly set a target size, then the database automatically configures a reasonable default.
  • Manual PGA memory management
    When automatic memory management is disabled and PGA_AGGREGATE_TARGET is set to 0, the database defaults to manual PGA management. Previous releases of Oracle Database required the DBA to manually specify the maximum work area size for each type of SQL operator (such as a sort or hash join). This technique proved to be very difficult because the workload is always changing. Although Oracle Database supports the manual PGA memory management method, Oracle strongly recommends automatic memory management.
See Also:
Oracle Database Performance Tuning Guide to learn about PGA memory management

Summary of Memory Management Methods

Table 18-1 summarizes the various memory management methods. If you do not enable automatic memory management, then you must separately configure one memory management method for the SGA and one for the PGA.
Note:
When automatic memory management is not enabled, the default method for the instance PGA is automatic PGA memory management.
Table 18-1 Memory Management Methods
Instance SGA PGA Description Initialization Parameters
Auto n/a n/a The database tunes the size of the instance based on a single instance target size. You set:
  • Total memory target size for the database instance (MEMORY_TARGET)
  • Optional maximum memory size for the database instance (MEMORY_MAX_TARGET)
n/a Auto Auto The database automatically tunes the SGA based on an SGA target.
The database automatically tunes the PGA based on a PGA target.		 You set:
  • SGA target size (SGA_TARGET)
  • Optional SGA maximum size (SGA_MAX_SIZE)
  • Instance PGA target size (PGA_AGGREGATE_TARGET)
n/a Auto Manual The database automatically tunes the SGA based on an SGA target.
You control the PGA manually, setting the maximum work area size for each type of SQL operator.		 You set:
  • SGA target size (SGA_TARGET)
  • Optional SGA maximum size (SGA_MAX_SIZE)
  • PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE
n/a Manual Auto You control the SGA manually by setting individual component sizes.
The database automatically tunes the PGA based on a PGA target.		 You set:
  • Shared pool size (SHARED_POOL_SIZE)
  • Buffer cache size (DB_CACHE_SIZE)
  • Large pool size (LARGE_POOL_SIZE)
  • Java pool size (JAVA_POOL_SIZE)
  • Streams pool size (STREAMS_POOL_SIZE)
  • Instance PGA target size (PGA_AGGREGATE_TARGET)
n/a Manual Manual You must manually configure SGA component sizes.
You control the PGA manually, setting the maximum work area size for each type of SQL operator.		 You must manually configure SGA component sizes. You set:
  • Shared pool size (SHARED_POOL_SIZE)
  • Buffer cache size (DB_CACHE_SIZE)
  • Large pool size (LARGE_POOL_SIZE)
  • Java pool size (JAVA_POOL_SIZE)
  • Streams pool size (STREAMS_POOL_SIZE)
  • PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE
See Also:
Oracle Database Administrator's Guide because automatic memory management is not available on all platforms

Resource Management and Task Scheduling

In a database with many active users, resource management is an important part of database administration. Sessions that consume excessive resources can prevent other sessions from doing their work. A related problem is how to schedule tasks so that they run at the best time. Oracle Database provides tools to help solve these problems.

Database Resource Manager

Oracle Database Resource Manager (the Resource Manager) is an infrastructure that provides granular control of database resources allocated to users, applications, and services. The Resource Manager solves many resource allocation problems that an operating system does not manage well, including:
  • Excessive overhead
  • Inefficient scheduling
  • Inappropriate allocation of resources
  • Inability to manage database-specific resources
The Resource Manager helps overcome these problems by giving the database more control over allocation of hardware resources and enabling you to prioritize work within the database. You can classify sessions into groups based on session attributes, and then allocate resources to these groups to optimize hardware utilization.
Resources are allocated to users according to a resource plan specified by the database administrator. The plan specifies how the resources are to be distributed among resource consumer groups, which are user sessions grouped by resource requirements. A resource plan directive associates a resource consumer group with a plan and specifies how resources are to be allocated to the group.
Figure 18-6 shows a simple resource plan for an organization that runs OLTP applications and reporting applications simultaneously during the daytime. The currently active plan, DAYTIME, allocates CPU resources among three resource consumer groups. Specifically, OLTP is allotted 75% of the CPU time, REPORTS is allotted 15%, and OTHER_GROUPS receives the remaining 10%.
Figure 18-6 Simple Resource Plan
Description of Figure 18-6 follows
Description of "Figure 18-6 Simple Resource Plan"
See Also:
Oracle Database Administrator's Guide for information about using the Resource Manager

Oracle Scheduler

Oracle Scheduler (the Scheduler) enables database administrators and application developers to control when and where various tasks take place in the database environment. The Scheduler provides complex enterprise scheduling functionality, which you can use to:
  • Schedule job execution based on time or events
  • Schedule job processing in a way that models your business requirements
  • Manage and monitor jobs
  • Execute and manage jobs in a clustered environment
Program objects (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. To define what is executed and when, you assign relationships among programs, schedules, and jobs.
The Scheduler is implemented as a set of functions and procedures in the DBMS_SCHEDULER PL/SQL package. You create and manipulate Scheduler objects with this package or with Enterprise Manager. Because Scheduler objects are standard database objects, you can control access to them with system and object privileges.
Figure 18-7 shows the basic architecture of the Scheduler. The job table is a container for all the jobs, with one table per database. The job coordinator background process is automatically started and stopped as needed. Job slaves are awakened by the coordinator when a job must be run (see "Job Queue Processes (CJQ0 and Jnnn)"). The slaves gather metadata from the job table and run the job.
Figure 18-7 Scheduler Components
Description of Figure 18-7 follows
Description of "Figure 18-7 Scheduler Components"
See Also:
Oracle Database Administrator's Guide to learn about the Scheduler

Performance Diagnostics and Tuning

As a DBA, you are responsible for the performance of your Oracle database. Typically, performance problems result from unacceptable response time, which is the time to complete a specified workload, or throughput, which is the amount of work that can be completed in a specified time. Common problems include:
  • CPU bottlenecks
  • Undersized memory structures
  • I/O capacity issues
  • Inefficient or high-load SQL statements
  • Unexpected performance regression after tuning SQL statements
  • Concurrency and contention issues
  • Database configuration issues
The general goal of tuning is usually to improve response time, increase throughput, or both. A specific and measurable goal might be "Reduce the response time of the specified SELECT statement to under 5 seconds." Whether this goal is achievable depends on factors that may or may not be under the control of the DBA. In general, tuning is the effort to achieve specific, measurable, and achievable tuning goals by using database resources in the most efficient way possible.
The Oracle performance method is based on identifying and eliminating bottlenecks in the database, and developing efficient SQL statements. Applying the Oracle performance method involves the following tasks:
  • Performing pre-tuning preparations
  • Tuning the database proactively on a regular basis
  • Tuning the database reactively when users report performance problems
  • Identifying, tuning, and optimizing high-load SQL statements
This section describes essential aspects of Oracle Database performance tuning, including the use of advisors. Oracle Database advisors provide specific advice on how to address key database management challenges, covering a wide range of areas including space, performance, and undo management.
See Also:
Oracle Database 2 Day + Performance Tuning Guide and Oracle Database Performance Tuning Guide provide to learn how to implement the Oracle performance method

Database Self-Monitoring

Self-monitoring take place as the database performs its regular operation, ensuring that the database is aware of problems as they arise. Oracle Database can send a server-generated alert to notify you of an impending problem.
Alerts are automatically generated when a problem occurs or when data does not match expected values for metrics such as physical reads per second or SQL response time. A metric is the rate of change in a cumulative statistic. Server-generated alerts can be based on user-specified threshold levels or because an event has occurred.
Server-generated alerts not only identify the problem, but sometimes recommend how the reported problem can be resolved. An example is an alert that the fast recovery area is running out of space with the recommendation that obsolete backups should be deleted or additional disk space added.
See Also:
Oracle Database Administrator's Guide

Automatic Workload Repository (AWR)

Automatic Workload Repository (AWR) is a repository of historical performance data that includes cumulative statistics for the system, sessions, individual SQL statements, segments, and services. These statistics are the foundation of performance tuning. By automating the gathering of database statistics for problem detection and tuning, AWR serves as the foundation for database self-management.
As shown in Figure 18-8, the database stores recent AWR statistics in the SGA. By default, the MMON process gathers statistics every hour and creates an AWR snapshot (see "Manageability Monitor Processes (MMON and MMNL)"). A snapshot is a set of performance statistics captured at a specific time. The database writes snapshots to the SYSAUX tablespace. AWR manages snapshot space, purging older snapshots according to a configurable snapshot retention policy.
Figure 18-8 Automatic Workload Repository (AWR)
Description of Figure 18-8 follows
Description of "Figure 18-8 Automatic Workload Repository (AWR)"
An AWR baseline is a collection of statistic rates usually taken over a period when the system is performing well at peak load. You can specify a pair or range of AWR snapshots as a baseline. By using an AWR report to compare statistics captured during a period of bad performance to a baseline, you can diagnose problems.
An automated maintenance infrastructure known as AutoTask illustrates how Oracle Database uses AWR for self-management. By analyzing AWR data, AutoTask can determine the need for maintenance tasks and schedule them to run in Oracle Scheduler maintenance windows. Examples of tasks include gathering statistics for the optimizer and running the Automatic Segment Advisor.
See Also:

Automatic Database Diagnostic Monitor (ADDM)

Automatic Database Diagnostic Monitor (ADDM) is a self-diagnostic advisor built into Oracle Database. Using statistics captured in AWR, ADDM automatically and proactively diagnoses database performance and determines how identified problems can be resolved. You can also run ADDM manually.
ADDM takes a holistic approach to system performance, using time as a common currency between components. ADDM identifies areas of Oracle Database consuming the most time. For example, the database may be spending an excessive amount of time waiting for free database buffers. ADDM drills down to identify the root cause of problems, rather than just the symptoms, and reports the effect of the problem on Oracle Database overall. Minimal overhead occurs during the diagnostic process.
In many cases, ADDM recommends solutions and quantifies expected performance benefits. For example, ADDM may recommend changes to hardware, database configuration, database schema, or applications. If a recommendation is made, then ADDM reports the time benefit. The use of time as a measure enables comparisons of problems or recommendations.
Besides reporting potential performance issues, ADDM documents areas of the database that are not problems. Subcomponents such as I/O and memory that are not significantly impacting database performance are pruned from the classification tree at an early stage. ADDM lists these subcomponents so that you can quickly see that there is little benefit to performing actions in those areas.
See Also:
Oracle Database 2 Day + Performance Tuning Guide and Oracle Database Performance Tuning Guide

Active Session History (ASH)

Active Session History (ASH) samples active database sessions each second, writing the data to memory and persistent storage. ASH is an integral part of the database self-management framework and is useful for diagnosing performance problems.
Unlike instance-level statistics gathered by AWR, ASH statistics are gathered at the session level. An active session is a session that is using CPU and is not waiting for an event in the idle wait class.
You can use Enterprise Manager or SQL scripts to generate ASH reports that gather session statistics gathered over a specified duration. You can use ASH reports for:
  • Analysis of short-lived performance problems not identified by ADDM
  • Scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL ID
For example, a user notifies you that the database was slow between 10:00 p.m. and 10:02 p.m. However, the 2-minute performance degradation represents a small portion of the AWR snapshot interval from 10:00 p.m. and 11:00 p.m. and does not appear in ADDM findings. ASH reports can help identify the source of the transient problem.
See Also:
Oracle Database 2 Day + Performance Tuning Guide and Oracle Database Performance Tuning Guide

Application and SQL Tuning

Oracle Database completely automates the SQL tuning process. ADDM identifies SQL statements consuming unusually high system resources and therefore causing performance problems. In addition, AWR automatically captures the top SQL statements in terms of CPU and shared memory consumption. The identification of high-load SQL statements happens automatically and requires no intervention.
SQL Tuning Advisor
Automatic SQL tuning is exposed through SQL Tuning Advisor. SQL Tuning Advisor runs automatically during system maintenance windows as a maintenance task. During each automatic run, the advisor selects high-load SQL queries in the database and generates recommendations for tuning these queries.
SQL Tuning Advisor recommendations fall into the following categories:
  • Statistics analysis
  • SQL profiling
  • Access path analysis
  • SQL structure analysis
A SQL profile contains additional statistics specific to a SQL statement and enables the optimizer to generate a better execution plan. Essentially, a SQL profile is a method for analyzing a query. Both access path and SQL structure analysis are useful for tuning an application under development or a homegrown production application.
A principal benefit of SQL Tuning Advisor is that solutions come from the optimizer rather than external tools (see "Overview of the Optimizer"). Thus, tuning is performed by the database component that is responsible for the execution plans and SQL performance. The tuning process can consider past execution statistics of a SQL statement and customizes the optimizer settings for this statement.
See Also:
Oracle Database 2 Day + Performance Tuning Guide and Oracle Database Performance Tuning Guide
SQL Access Advisor
SQL Access Advisor offers advice on how to optimize data access paths. Specifically, it recommends how database performance can be improved through partitioning, materialized views, indexes, and materialized view logs.
Schema objects such as partitions and indexes are essential for optimizing complex, data-intensive queries. However, creation and maintenance of these objects can be time-consuming, and space requirements can be significant. SQL Access Advisor helps meet performance goals by recommending data structures for a specified workload.
The SQL Access Advisor can be run from Enterprise Manager using the SQL Access Advisor Wizard or by invoking the DBMS_ADVISOR package. The DBMS_ADVISOR package consists of a collection of analysis and advisory functions and procedures callable from any PL/SQL program.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)