Database Management

This article introduces the concept of database management in the following sections:

·         Introduction to Database Management

·         Monitoring Databases

·         Administering Databases

·         Maintaining Databases

·         Additional Maintenance Features

·         Monitoring Real Application Clusters

Introduction to Database Management

Database management involves the monitoring, administration, and maintenance of the databases and database groups in your enterprise. Enterprise Manager is the premier tool for managing your database environment.

With Enterprise Manager, you receive:

·         A complete set of integrated features for managing Oracle Databases

·         Unparalleled scalability that lets you manage a single database or thousands of instances

·         An intuitive management product that leads the industry in ease of deployment and use

Database Control Versus Grid Control

Enterprise Manager provides two separate consoles that you can use to monitor your database: Database Control and Grid Control.

·         Database Control is the Enterprise Manager Web-based application for managing Oracle Database 10g Release 2 (10.2). Database Control is installed and available with every Oracle Database 10g installation. From Database Control, you can monitor and administer a single Oracle Database instance or a clustered database.

·         Grid Control is the Enterprise Manager console you use to centrally manage your entire Oracle environment. Within Grid Control, you access the multiple database targets using the Targets tab, then Databases.

Database Home Page as the Management Hub

The Enterprise Manager Database Home page shows important status and performance information about your database instance from a single source, including:

·         A quick view of the status of the database and basic information about the database

·         Relative CPU utilization of the Oracle host

·         Amount of time the instance consumed using CPU and I/O, and the amount of time it consumed in bottlenecks

·         Current response of the tracked set of SQL versus the reference collection response

·         The number of Automatic Database Diagnostic Monitoring (ADDM) findings, the number of policy violations (Database Control only), and the Alert Log status

·         Storage-related issues and recommendations for improved performance, and information about space violations and ADDM

·         The most recent backup time and backup status

·         Outstanding alerts and related alerts

·         Rolled-up number of Oracle policy violations across all member targets in a system

·         A quick view of database security

·         Job executions that show the scheduled, running, suspended, and problem executions

Starting from the Database Home page, you can access additional details and management functions by drilling down through the user interface. In addition, the Database Home page provides a list of Related Links. From these links you can perform activities such as editing metric thresholds, analyzing job activity and metric collection errors, and accessing a number of advisors to help you improve the performance of your database.

See Also: "Oracle Database Home Page" in the Enterprise Manager online help

Monitoring Databases

Comprehensive database monitoring enables you to identify the problem areas in your database environment that are degrading performance. After you have identified the areas to improve, you can tune your databases' performance using the Enterprise Manager administration capabilities.

Enterprise Manager uses data from the Automatic Workload Repository (AWR) to display performance information and initiate database alerts. The user interface provides several real-time performance charts and drill-downs for the targets you manage. Both aggregate and instance-specific performance statistics are displayed using color-coded charts for easier viewing. To help you identify the source of a problem and resolve it, you can click a legend link next to a chart to display a detail page that provides comprehensive information.

The basic work flow in monitoring performance is to first go to the Database Performance page, which provides a high-level, comprehensive overview of important performance indicators.

Assessing Database Performance

Database tuning is much easier and more effective when all the information for an in-depth performance diagnosis is available on a single screen. On the Database Performance page, which is accessible from the Database Home page, you can quickly view performance data for the instance and its underlying host. Additionally, you can analyze trends for all critical performance metrics and compare the trends to those of other database instances.

The most direct method for investigating and diagnosing performance problems from this page consists of accessing the Automatic Database Diagnostic Monitor (ADDM). After you start your Oracle database, the Automatic Workload Repository (AWR) begins taking snapshots of database activity once every hour by default, and ADDM executes when the snapshots are collected. ADDM uses these snapshots to perform a top-down analysis of your database activity and then provide tuning recommendations.

Another method for investigating and diagnosing performance problems consists of observing which of the wait classes beside the Average Active Sessions chart appears to be consuming an excessive amount of time, shown as a spike above the Maximum CPU line, then drilling down for more information. This provides a way for you to visualize the data for which ADDM has made its recommendations.

Either method assists you in diagnosing and resolving problems. The first automated method produces textual findings, whereas the second interactive method produces graphical findings.

The charts on the Database Performance page display current and recent metric information on a common time axis that enables visual correlation of metrics. These charts provide context-sensitive drill-downs where you can find additional details for rapid problem diagnosis.

Table 12-1 Performance Page Charts

Chart	Description
Host	This chart shows potential problems outside the database. The load average is a moving average of the run queue length. The run queue length indicates the level of contention for CPU time.
Average Active Sessions	This chart is the centerpiece of Oracle performance monitoring, and shows potential problems inside the database. It displays a profile of the amount of time sessions are either working or waiting to work in the database instance. The Maximum CPU line on the chart helps determine how much of the CPU resource is being utilized. Session wait time accumulates above the CPU line, and the ratio of wait time to CPU time indicates how efficiently the system is working. Specific wait classes accumulating time indicate where you should focus your tuning efforts.
Instance Disk I/O	This chart shows the total requests that represent the rate at which the database instance is issuing read/write requests.
Instance Throughput	The charts show any contention that appears in the Average Active Sessions chart

The Additional Monitoring Links section enables you to access several related pages. Among those, the following pages have real-time diagnostic capabilities:

·         Top Activity

·         Top Consumers

·         Instance Activity

·         Historical SQL

See Also: "Performance Page" in the Enterprise Manager online help

Diagnosing Problems Interactively

You can manually diagnose problems using an interactive method by first investigating a wait class that you think may be problematic, then drilling down from there for either SQL details or session details. You can alternatively investigate all wait classes in one location and then drill down from there. The following sections explain these methods.

Investigating a Wait Class

Wait class drill-downs enable you to investigate a specific wait class where it appears that time is accumulating. You can then determine whether the problem is caused by one or several SQL statements that are accumulating an unusual amount of time, or whether the problem is caused by one or several sessions.

The wait class drill-downs are called Active Sessions Waiting pages. The pages are sourced from the Active Session History (ASH), which samples session activity every second. It continuously records what is happening, such as which sessions are using CPU and which sessions are waiting on I/O.

Select a wait class link beside the Average Active Sessions chart to obtain detailed information for the wait class. For instance, if you click User I/O, the Active Sessions Waiting: User I/O page appears.

By default, the largest consumers of resources appear at the top of the detail tables. Look for skewed activity for either Top SQL or Top Sessions. If an excessive accumulation of activity appears to originate from an SQL source, you can click its associated SQL ID to go to the SQL Details page, which shows the SQL statement and activity. If excessive accumulation appears to originate from a session source, you can click its associated Session ID to go to the Session Details page, where you can kill the session if needed. You can also view the wait events associated with the session.

See Also: "Active Sessions Waiting Page" in the Enterprise Manager online help

Viewing SQL Details

The SQL Details page, displays the SQL statement you select in the Active Sessions Waiting page. You can also:

·         Examine the activity of this SQL statement over time.

·         View SQL-level statistics.

·         Investigate the SQL plan.

·         Access any prior Tuning Advisor run.

·         Schedule a Tuning Advisor run.

See Also: "SQL Details Page" in the Enterprise Manager online help

Viewing Session Details

The Session Details page, displays the wait events associated with the session you selected in the Active Sessions Waiting page. You can also:

·         View the current values for metrics associated with the current session.

·         View the list of currently open cursors in the selected session, including their hash value and SQL text.

·         View sessions that are blocking other sessions.

See Also: "Session Details Page" in the Enterprise Manager online help

Viewing Top Activity

The Top Activity page, is essentially the sum of all wait class drill-downs. The interface format is the same as the Active Sessions Waiting page, but rather than showing the average active sessions for a particular wait class, the Top Activity page shows the average active sessions for all wait classes. The page also shows the Top SQL and Top Sessions consumers for all wait classes. As with the Active Sessions Waiting pages, look for skewed activity for either Top SQL or Top Sessions.

See Also: "Top Activity Page" in the Enterprise Manager online help

Diagnosing Problems Automatically

As mentioned in Assessing Database Performance, you can use the Automatic Database Diagnostic Monitor (ADDM) to automatically investigate and diagnose performance problems from either the Database Home page or the Database Performance page. Using regularly scheduled snapshots of the database activity, ADDM identifies the most resource-intensive components or operations, and determines whether or not these components or operations are acting as performance bottlenecks. If one or more problems have occurred, ADDM diagnoses these potential problems and provides advice, which may recommend that you run an advisor or change your database configuration.

You can view ADDM findings over a predetermined period of recent time or at the present time. You can view ADDM findings for the most recent time interval by clicking the ADDM Findings link in the Diagnostic Summary section of the Database Home page. You can also view ADDM findings for this interval by clicking the small ADDM icon below the Average Active Sessions chart in the Database Performance page. Either option displays the Automatic Database Diagnostic Monitor page. To determine ADDM findings at the present time, you can click the Run ADDM button on the Database Performance page.

By default, the database takes snapshots at 60-minute intervals. You can use the Automatic Workload Repository to change the snapshot interval, ranging from 10 minutes to 2 hours.

See Also: "Performance Finding Details Page" in the Enterprise Manager online help

Using Additional Diagnostic Pages

Besides the primary diagnostic pages discussed above, there are other important secondary pages that can assist you in diagnosing and correcting performance problems. The following sections discuss these diagnostic pages:

·         Top Consumers

·         Instance Activity

·         Historical SQL

Top Consumers

The Top Consumers link provides global summary information for the top database consumers of system resources. You can access detailed metrics data for a specific top consumer, such as sessions, services, modules, and clients. This enables you to pinpoint the most problematic areas on which to focus your database tuning efforts. Top Modules page of Top Consumers, where you can perform tasks such as enabling and disabling aggregation and SQL traces for the consumer. You can use an SQL trace to trace statistics of SQL statements, such as CPU time, elapsed time, and the Explain Plan.

See Also: "Top Consumers Page" in the Enterprise Manager online help

Instance Activity

The Instance Activity link displays database activity for specific data about groups of metrics, such as cursors, sessions, and transactions. For instance, the Cursors metric group displays information about opened and session cursors along with authentications and parse counts. The legend links below the chart in the graphic view, or the name links in the table view, enable you to access the Top Sessions page for more detailed information.

See Also: "Instance Activity Page" in the Enterprise Manager online help

Historical SQL

The Historical SQL (AWR) link displays statements stored in the Automatic Workload Repository (AWR) for a 24-hour period. This link is available in the Additional Monitoring Links section when you select the Historical data view.

The table at the bottom of the page shows an analysis of all SQL statements for performance and resource consumption. You can select a statement link to see SQL details (statistics, activity, SQL plan, and tuning information) for the statement.You can also run the SQL Tuning Advisor to receive recommendations for one or more statements.

See Also: "Historical SQL (AWR) Page" in the Enterprise Manager online help

Using Other Performance Tools

Enterprise Manager offers several tools that provide additional assistance ranging from viewing the relationships between system components to collecting system statistics when database performance is degraded. The following sections discuss these tools:

·         Topology Viewer

·         Metric Baselines

·         Memory Access Mode

·         Hang Analysis

Topology Viewer

Enterprise Manager provides a Topology Viewer for several applications. The Topology Viewer allows you to view the relationships between components, nodes, or objects within different Oracle applications. You can zoom, pan, see selection details and summary information, and evaluate aggregate components. Individually distinct icons are used for each object type, and standardized visual indicators are used across all applications.

The Topology Viewer is available for the following database applications:

·         Scheduler

·         SQL Details

·         SQL Explain Plans

·         Real Application Clusters

·         Cluster Databases

The Topology Viewer provides a graphical representation of your SQL steps as modeled in Enterprise Manager.

Metric Baselines

From the Database Performance page, you can set a metric baseline—a time period associated with a target that you can use as a reference for evaluating target performance . Statistics are computed over the baseline period for specific target metrics. You can use these statistics to automatically set metric thresholds for alerts, as well as to normalize graphical displays of system performance.

See Also: "Metric Baselines Page" in the Enterprise Manager online help

Memory Access Mode

When the database server experiences performance issues, diagnostic queries could further impact the system. A new feature, called Memory Access Mode, assists you in diagnosing performance-related problems by collecting system statistics even when the database is either slow or hung.

Instead of relying on the normal SQL engine, the data is retrieved by accessing tables directly from the Shared Global Area (SGA). The data collection is quick and does not further impact a system that is already slow. Potentially, this allows for sub-second sampling of performance metrics without causing any additional load on a system.

You can disable the standard SQL Access Mode and switch to Memory Access Mode by clicking the Monitor in Memory Access Mode link in the Related Links section.

See Also: "Database Performance Page" in the Enterprise Manager online help

Hang Analysis

A new feature, called Hang Analysis, enables you to diagnose locking issues that might either slow down a system or cause hanging. Normally, if a system is slow or in a hung state, the diagnostic queries are also either extremely slow or never return a result. This utility bypasses the typical query engine instead, and leverages the Oraclebug API to return results quickly even on systems that might be seemingly in a hung state.

The Hang Analysis page shows a visual map of the sessions that are either blocking or are blocked. A tree view of the sessions is displayed, and the problem session that is blocking other sessions is located at the root of the tree. Each session is shown color-coded, signifying how long the session has been blocked. Clicking on the session box brings up another page that shows session details. Using information from this page, you can cancel the problem session and return the system to its normal state.

You can access the Hang Analysis page by clicking the Hang Analysis link in the Additional Monitoring Links section.

See Also: "Hang Analysis Page" in the Enterprise Manager online help

Administering Databases

Oracle Enterprise Manager effectively keeps your Oracle Databases available and running efficiently. Enterprise Manager can help database administrators perform everyday tasks. Specifically, it provides a graphical user interface for managing database storage structures and schemas. As with database monitoring, administration of the Oracle Database begins with the Oracle Database Home page. From this page, you can display an overview of your database properties and performance. However, you can also use the Administration section of the page to perform common administration tasks such as the following:

·         Allocating system storage and planning future storage requirements for the database system

·         Creating and managing primary database storage structures (tablespaces)

·         Creating and managing primary objects (tables, views, indexes)

·         Enrolling users and maintaining system security by controlling and monitoring user access to the database

·         Backing up and restoring the database

Just as Enterprise Manager monitoring identifies problem areas in your database and database groups, you can administer your database using the Enterprise Manager administration tools. The administration tools allow you to manage database objects and initiate database operations inside an Oracle Database. The following sections provide an overview of the database administrative features available to you in Enterprise Manager.

Managing Storage Objects

As an administrator, you can use the administration tools in Oracle Enterprise Manager to optimize database performance. Using these tools, you can manage storage structures such as control files, tablespaces, datafiles, and archive logs. In addition to viewing, editing, and deleting these structures, you can also perform other functions such as making tablespaces locally managed, displaying the dependencies of a datafile, or backing up a control file to a trace.

Using Database Configuration Features

Oracle Enterprise Manager incorporates many features that help you to manage your database configuration. For example, you can use Enterprise Manager to manage the memory size of the system global area and program global area of your system. You can also use the Undo Management feature to provide a means of explicitly specifying the amount of undo information to retain and ensure that undo information is not overwritten.

You can create or edit initialization parameters for the current database, setting these parameters to specific values to initialize many of the memory and process settings of an Oracle Database instance. You can also display a list of database features, showing how often the features are used in the operation of the database. Usage information can be utilized by support groups and other organizations to gain knowledge about how the system is being used, and to help apportion resources as necessary.

Using Automatic Storage Management

Enterprise Manager uses Automatic Storage Management (ASM) to automate and simplify the layout of datafiles, control files, and log files. Database files are automatically distributed across all available disks, and database storage is rebalanced whenever the storage configuration changes. You can manage your entire Automated Storage Management environment through Enterprise Manger. Additionally, you can easily migrate an existing database to Automatic Storage Management on the same host by using the migration tool in Enterprise Manager. The process defines the migration request and submits a job to complete the migration process.

Converting Single Instances to Real Application Clusters

Oracle Real Application Clusters (RAC) provides a high-availability database environment spanning multiple hosts. Each cluster can be comprised of multiple cluster databases, each of which consists of multiple cluster database instances. A cluster database is available as long as one of its instances is available. You can use Enterprise Manager to asynchronously convert a single-instance database to a RAC database.

Converting to a Locally-Managed Tablespace

An added feature allows you to convert a dictionary-managed tablespace to a locally-managed tablespace, thereby simplifying space allocation, improving performance, and reducing reliance on the data dictionary.

Controlling Resources with Resource Manager

The Database Resource Manager controls the distribution of resources among various sessions by controlling the execution schedule inside the database. By controlling which sessions run and for how long, the Database Resource Manager can ensure that resource distribution matches the plan directive and hence, the business objectives. You can use the Database Resource Manager to automatically assign sessions to specific consumer groups by setting up mappings between session attributes and consumer groups. You can also map consumer groups in specified categories to users, client programs, modules, or services.

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. You can then use a resource plan to specify how the resources are to be distributed among various resource consumer groups.

Resource plans specify the resource consumer groups belonging to the plan and contain directives for how resources are to be allocated among these groups. Plan information is stored in tables in the data dictionary. Several views are available for viewing plan data. In addition to containing resource consumer groups, a plan can contain subplans. Use Enterprise Manager to manage all aspects of the resource plan.

Tracking Statistics to Improve Database Performance

The Workload Repository provides you with a mechanism for gathering database statistics for specific time intervals. You can use the Optimizer Statistics feature of Enterprise Manager to simplify the management of Optimizer Statistics operations such as gathering, restoring, deleting, locking, and unlocking statistics. Use these statistics to improve the performance of your SQL statements.

Using Oracle Scheduler

Oracle Enterprise Manager uses the Scheduler to enable database administrators and application developers to control when and where various tasks occur in the database environment. Using the Scheduler helps to improve the management and planning of these tasks. By separating a task into its component parts such as time, location, and database object, the Scheduler provides an easier way to manage your database environment. Database administrators can schedule and monitor recurring database maintenance jobs, such as backups or nightly data warehousing loads and extracts, and they can schedule job execution based on time or events.

With Enterprise Manager, you can enable and disable Scheduler jobs, alter the settings for existing jobs, start or stop current jobs, and view Scheduler information.

Working With Database Schemas

A schema is a collection of database objects comprised of logical structures that directly refer to the data in the database. Schema objects include structures such as tables, views, and indexes. These schema objects can be created and managed using the tools available in Oracle Enterprise Manager.

Managing Database Objects

Oracle Enterprise Manager provides a comprehensive set of tools that allows you to manage all aspects of database directory objects such as tables, indexes, and views. You can use the tools available in Enterprise Manager for fundamental tasks such as creating, editing, and viewing object properties, but you can also use Enterprise Manager for more comprehensive tasks such as running the Segment Advisor to evaluate a table for block and space usage and to determine whether space can be saved by shrinking highly fragmented segments. The space gained by the implementation of these recommendations is returned to the table.

Indexes are optional structures associated with tables that can be created to increase the performance of data retrieval. When you manage indexes in Enterprise Manager, you can perform functions such as shrinking segments to compact segments and free the recovered space to the current tablespace. Alternatively, you can also eliminate space problems by reorganizing space usage while changing the storage settings and location of the index.

Views are customized presentations of data in one or more tables or other views. In addition to creating, deleting, and managing views, you can also view the objects that are dependencies of the view. The Dependencies table shows you the Object Name and the Object Type that is dependent on the current view. Conversely, Enterprise Manager can also show you the objects on which the current view is dependent.

In general, use the Action menu on the Search page or Property page of any database object to see a list of the available functions you can perform on that object.

Enterprise Manager allows you to manage program structures, such as packages, package bodies, functions, and triggers in the same way. In addition to creating and viewing these elements, you can compile Java Sources, for example, or generate the data definition language (DDL) code for a specified object such as package. For more information about the various functions available for a specific object, you can refer to the online Help.

XML Database Features in Oracle Enterprise Manager

XML, eXtensible Markup Language, is the standard way to identify and describe data on the Web. Oracle XML DB treats XML as a native datatype in the database. Oracle XML DB offers a number of easy ways to create XML documents from relational tables. The result of any SQL query can be automatically converted into an XML document. Oracle also includes a set of utilities, available in Java and C++, to simplify the task of creating XML documents.

Managing Users and Privileges

Oracle includes security features that control how a database is accessed and used. Privileges and roles control user access to data and the types of SQL statements that can be executed. There are three types of privileges and roles: system privileges, object privileges, and roles. The system privilege is an Oracle-defined privilege usually granted only to and by administrators. It allows users to perform specific database operations. An object privilege is an Oracle-defined privilege that controls access to a specific object. A role can be created by users, usually administrators, and is used to group together privileges and other roles. This facilitates the granting of multiple privileges and roles to users.

Privileges and roles can be granted to other users by users who possess the power to do so. Oracle Enterprise Manager allows you to create and manage users, roles and profiles. You can also expire passwords and lock or unlock users by applying those actions against one or more users. When managing roles, you can display all users and roles assigned to the specified role by using the Show Grantees function.

Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement run, or on combinations of factors that can include name, application, time, and so on. Security policies can cause auditing when specified elements in an Oracle Database are accessed or altered, including content. You can set up and adjust audit settings easily within the Enterprise Manager interface. With Enterprise Manager, you can view the database audit configuration and manage audited objects, privileges, and statements. You can also view the content of the audit trail. Out-of-box, Enterprise Manager also provides audit reports to help you monitor successful and failed login attempts, as well as SYSUSER operations.

Managing Materialized Views

Materialized views are schema objects that can be used to summarize, compute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing. Enterprise Manager lets you create and manage materialized views and provides a set of additional tools that allows you to perform specific actions against the view. The Explain Materialized View feature helps you to determine what is possible with a materialized view by indicating whether a materialized view is fast-refreshable, what types of query rewrites you can perform with this materialized view, and whether PCT refresh is possible.

Oracle maintains the data in materialized views by refreshing them after changes are made to their master tables. The refresh method can be incremental (fast refresh) or complete. For materialized views that use the fast refresh method, a materialized view log keeps a record of changes to the master tables. You can use Enterprise Manager to advise you when a query can be rewritten. You can then take the appropriate action required to make a query rewrite. Query rewrite transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables.

Materialized views can be refreshed either on demand or at regular time intervals. You can use the Action menu in Enterprise Manager to manually refresh a materialized view.

About Change Management

A dictionary baseline is an object containing a set of database definitions captured at a certain time. Baselines are stored internally in the Enterprise Manager repository, and are in a form that other Change Management applications can use. You can use Enterprise Manager to capture the database object definitions at a particular point in time and create reusable baseline scope specifications. By capturing a dictionary baseline, you can compare different database objects at different points in time and track changes applied to the database objects.

A dictionary comparison identifies differences in database object definitions between two databases, a database and a baseline, or two schemas within a single database/baseline. With Enterprise Manager, you can compare, view, and track differences between two sets of database object definitions at different points in time. The comparison result displays identical objects, objects/attributes that are different, and objects present either in the left or right source.

Using Advisors in Oracle Enterprise Manager

Advisors are procedures that you can invoke, or Enterprise Manager can invoke internally, that designate a specific object for analysis. An advisor can report on a variety of aspects of the object, and describe a recommended action for each condition worthy of user intervention. The advisor might report that the condition can be corrected by an automated task that it provides.

Some advisors also provide what-if analysis for specific situations. For example, the Undo Advisor provides an analysis of the impact of changing the retention period for undo records on the size of the undo tablespace. Additionally, the Memory Advisor graphically displays the impact on performance of changing the size of a component of the SGA.

You can invoke an advisor from the Advisor Central Home page that is displayed when you click the Advisor Central link under the Related Links heading on the Database Home page, or on other pages where it is listed. You can also invoke advisors in the context of recommendations from alerts. Advisors are powerful tools for tuning your database. In general, advisors produce more comprehensive recommendations than alerts, since alert generation is intended to be low cost and have minimal impact on performance. On the other hand, because Advisors are often user-invoked, they can consume more resources and perform more detailed analysis. Their automated analysis can provide more results than you might normally be able to produce manually in the time afforded you as part of regular operations. This analysis, along with the what-if capability of some advisors, provides vital information for tuning that cannot be procured from any other source.

Maintaining Databases

You can use Oracle Enterprise Manager to control the flow of data between or outside Oracle Databases. The following sections provide an overview of the functions available to you that can help you maintain your Oracle Database.

Using Backup

Backup of an Oracle Database generally refers to physical backup; protecting the files that make up your database. The files protected by the backup and recovery facilities built into Oracle Enterprise Manager include datafiles, control files, and archived redo log files. The backup mechanisms that work at the physical level protect against damage at the file level, such as the accidental deletion of a datafile or the failure of a disk drive. The focus in Oracle backup and recovery is generally on the physical backup of database files, which permit the full reconstruction of your database.

Enterprise Manager's physical backup and recovery features are built on Oracle's Recovery Manager (RMAN) command-line client. Enterprise Manager carries out its backup tasks by composing RMAN commands and sending them to the RMAN client. Enterprise Manager makes available much of the functionality of RMAN, as well as wizards and automatic strategies to simplify and further automate implementing RMAN-based backup and recovery.

Managing backups consists of two things: managing the backups themselves as they exist on disk or tape, and managing the record of backups kept in the RMAN repository.

Datafiles or archived redo logs copied at the operating system level can be cataloged and added to the RMAN repository, after which they can be used in data restore and recovery operations just as if they had been created with RMAN. Backup maintenance provided in Enterprise Manager includes the following:

·         Viewing lists of backups (backup sets and image copies) recorded in the RMAN repository

·         Cross-checking your repository, which marks as expired any backups that are in the repository but not accessible at the time of the crosscheck

·         Deleting expired backups from your RMAN repository

·         Deleting obsolete backups from the repository and from disk. Note that if you use a recovery area for your backup storage, many maintenance activities are reduced or eliminated because of the flash recovery area's automatic management of disk space.

Recovery

Media recovery using Enterprise Manager can be either complete recovery or point-in-time recovery. In complete recovery, all changes from the logs are applied and the database returns to its state at the time of failure. You can then reopen the database with no loss of data.

In point-in-time recovery, you can choose any System Change Number (SCN) between the time of the datafile backup and the last change in the redo logs, and apply only changes up through that SCN. You can thus return your database to any SCN (and thus any point-in-time) between the time of your backup and the most recent SCN in the redo logs. This technique can be used to recover from situations such as user errors that cause logical corruption in the database. Point-in-time recovery is sometimes referred to as incomplete recovery, since not all changes are applied.

Media recovery requires a control file, datafiles, and all online and archived redo logs from the time the datafiles were backed up. It is typically used only in the case of database failure.

Crash recovery is used to recover from a failure either when a single-instance database crashes, or all instances of a RAC database crash. Instance recovery refers to the case where a surviving instance recovers a failed instance in a RAC database. Datafile media recovery is used to recover from a lost or damaged current datafile or control file. Block media recovery is a technique for restoring and recovering individual data blocks while all database files remain online and available.

Flashback Recovery

Enterprise Manager's flashback features provide a range of physical and logical data recovery tools as efficient, easy-to-use alternatives to physical and logical backups. Flashback table allows you to revert a table to its contents at a time in the recent past; and flashback drop allows you to rescue dropped database tables. Neither requires advance preparation such as creating logical-level exports to allow for retrieval of your lost data, and both can be used while your database is available.

Flashback database lets you quickly recover an Oracle Database to a previous time to correct problems caused by logical data corruptions or user errors. If a flash recovery area is configured, then you can return the database to a prior time. Flashback table lets you recover tables to a specified point in time. You can restore table data along with all its associated attributes, such as indexes, triggers, and so on. This is done while the database is online by rolling back only the changes to the given tables. You can revert the table and its contents to a specific time or user-specified SCN. Use flashback table with flashback query and row versions to find a time to which the table should be restored.

The larger the flash recovery area, the more useful it becomes. Ideally, the flash recovery area should be large enough to hold two complete backup copies of your datafiles, plus any incremental backups and archive logs required to restore your database to any point in time during your recovery window.

Backup and Recovery Settings

You can use Enterprise Manager to configure a number of settings and policies that determine how backups are stored, which data is backed up, how backups perform, and how long backups are retained before being purged from the recovery area. You can also configure settings to improve backup performance. For disks, you can configure the default format for storing backups, the location on disk where backups are stored, and whether backup tasks are run in parallel for improved performance. Oracle backups to disk can be saved as image copies or backup sets. Backups to tape and similar media management devices must be stored as backup sets.

Managing Restore Points

You can use Enterprise Manager to create a restore point as a designated point in time to which you can restore your database. A restore point is a name associated with a past point-in-time of the database. You can flash back the database to a restore point if the required flashback logs and archived logs exist. A Guaranteed Restore Point is a restore point to which you can always flash back the database. Each restore point has a name and creation time. The restore points are sorted by creation time beginning with the most recent.

Overview of Data Guard

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle Databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus minimizing the downtime associated with the outage.

Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability. With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.

A Data Guard configuration consists of one production database and one or more standby databases. You can manage primary and standby databases using Enterprise Manager. You can use Enterprise Manager to monitor the status of a configuration as well as the online redo log file activity of the primary and standby databases. At the most basic level, the Data Guard Overview page for the configuration not only displays information about the configuration, but it also includes summary information about its databases.

Additional Maintenance Features

Enterprise Manager allows you to easily move data from files or databases into an existing database. You can export and import data, and you can clone databases using the tools available in Enterprise Manager. The following sections describe the features available in the Maintenance area of the Database Home page.

Exporting and Importing Features

The Export to Files feature enables you to move existing data in Oracle format to and from Oracle Databases. For example, export files can archive database data or move data among different Oracle Databases that run on the same or different operating systems. Using Export to Files, you can back up logical database objects while the database is open and available for use. It writes a read-consistent view of the database's objects to an operating system file.

Conversely, you can use Enterprise Manager to import the contents of a database, objects, and tables. You can also import the contents of a database by using the Import from Database feature. You can import an entire database, schemas within a database and the objects in the schemas, or one or more tables from within a schema. Use the Load Data from File feature to load data from a non-Oracle database into an Oracle Database.

Use the Monitor Export and Import Job feature to view the status of an import and export operation such as a full database export or a tablespace export. You can change the state of a job by suspending it, canceling it, or resuming it if it had previously been suspended. You can also increase the number of threads dedicated to the job and thus increase its resources.

Cloning Databases or Transporting Tablespaces

You can use the Enterprise Manager Clone Database tool to clone an Oracle Database instance to an existing Oracle home. After you have an Oracle Database instance in a known state, you may want to clone that database to another existing Oracle home.

Use the Transport Tablespaces feature to transport tablespaces between different machine architectures and operating systems. Transportable tablespaces entirely bypass the unload and reload steps. Using transportable tablespaces, Oracle datafiles (containing table data, indexes, and almost every other Oracle Database object) can be directly transported from one database to another. You can use the transportable tablespaces feature to move a subset of an Oracle Database and plug it in to another Oracle Database, essentially moving tablespaces between the databases.

Overview of Oracle Streams

Oracle Streams enables the propagation and management of data, transactions, and events in a data stream either within a database, or from one database to another. The stream routes published information to subscribed destinations. As users' needs change, they can simply implement a new capability of Oracle Streams, without sacrificing existing capabilities. Oracle Streams can stream data between databases, nodes, or blade farms in a grid and can keep two or more copies in sync as updates are applied. It also provides a unified framework for information sharing, combining message queuing, replication, events, data warehouse loading, notifications, and publish/subscribe into a single technology.

Oracle Streams provides a set of elements that lets users control what information is put into a stream, how the stream flows or is routed from node to node, what happens to events in the stream as they flow into each node, and how the stream terminates. By specifying the configuration of the elements acting on the stream, you can address specific requirements, such as message queuing or data replication.

Use the tools in Oracle Enterprise Manager to set up and manage your Oracle Streams environment.

Working with Software Configurations

The Oracle Management Agent on a host collects host configuration information for the host and database configuration information for the Oracle Databases on the host and client configuration information and communicates that information over HTTPS to the Oracle Management Service, which stores it in the Oracle Management Repository. Enterprise Manager lets you compare these configurations to determine differences between two or more hosts, clients or databases. The Generic Compare feature allows you to compare various types of current/saved configurations with one or more current/saved configurations.

Using this feature, you can compare the current configuration of a selected target type with one or more current configurations of other targets of the same type, or compare saved configurations with one or more saved configurations of the same or other targets. You can also compare saved configurations with one or more current configurations of the same or other targets, or compare a specific configuration with another configuration and list the differences immediately. Lastly, you can compare a specific configuration with another configuration and schedule the comparison as a job.

Using Database Software Patching

Use Enterprise Manager to simplify the patching of Oracle software on any host where an Oracle Management Agent is running, and to provide critical patch advisories. Enterprise Manager simplifies the process of patching Oracle software. Oracle Patch Advisories describe critical software patches for Oracle products. To help ensure a secure and reliable configuration, all relevant and current critical Oracle patches should be applied. To promote critical patch application, Enterprise Manager performs an assessment of vulnerabilities by examining the host configurations collected for your enterprise to determine the Oracle homes that require one or more critical patches to be installed. All the Critical Patch Advisories are listed with their corresponding Impact areas, a brief description of each advisory, the number of Affected Hosts, and Oracle homes for each advisory.

You can connect to OracleMetaLink via Enterprise Manager, perform a search, download the required patches/patch sets, and apply the patches. You can perform all the patching activities from the Patch Cache. That is, even when the OMS is not connected to OracleMetaLink via the Internet, you can perform a search, download, and apply a patch/patch set.

Monitoring Real Application Clusters

Oracle Real Application Clusters (RAC) provides a high-availability database environment spanning multiple hosts. Each cluster can be comprised of multiple cluster databases, each of which consists of multiple cluster database instances. A cluster database is available as long as one of its instances is available.Enterprise Manager provides performance pages to monitor all levels of a cluster environment, including the cluster, the cluster database, and the cluster database instances. Managing Real Application Clusters databases and instances is similar to managing single-instance databases. Using Enterprise Manager, you can perform various tasks, such as:

·         Managing clusters, cluster databases, and cluster database instances

·         Monitoring key performance statistics

·         Performing administration and maintenance tasks

·         Managing Real Application Clusters features, such as Cluster Managed Database Services

RAC enables each computer (or host) that is a member of the cluster to share access to the database. If one cluster host fails or is taken offline, the other hosts of the cluster continue operating, and the entire RAC database remains available for applications. This means that two or more computers with typical performance appear to applications as if they were a much more powerful computer.To increase performance, availability, and reliability of a two-host RAC database, you can add cluster hosts. Because data is not partitioned between hosts, adding hosts to the cluster does not create instability; instead, applications can run faster or support more users. The more hosts your RAC database has, the less the loss of any individual node affects the database.

Cluster Cache Coherency

Concurrent read and write activity on shared data in a cluster occurs frequently. Depending on the service requirements, this activity does not normally cause performance problems. However, when global cache requests cause a performance problem as indicated on the Cluster Database page, a successful strategy for performance tuning is to optimize SQL plans and the schema to achieve effective local cache hit efficiency and minimize I/O. To assist you in resolving the problem, the Cluster Cache Coherency page enables you to view cache coherency metrics for the entire cluster database, identify processing trends, and optimize performance for your Real Application Clusters environment.

See Also: "Cluster Cache Coherency Page" in the Enterprise Manager online help

The charts have the following purposes:

·         Global Cache Block Access Latency represents the end-to-end elapsed time or latency for a block request.

·         Global Cache Block Transfer Rate shows the total aggregated number of data blocks received by all instances in the cluster through an interconnect.

·         Global Cache Block Transfers and Physical Reads shows the percentage of logical reads that read data from the buffer cache of other instances through Direct Memory Access and from disk.

Cluster Interconnects

The Cluster Interconnects page enables you to view the current state of interfaces on hosts. You can use this page to monitor the interconnect interfaces, determine configuration issues, and identify transfer rate-related issues, such as excess traffic. This page helps determine the load added by individual instances and databases on the interconnect. Sometimes, you can immediately identify interconnect delays due to applications outside the Oracle Database.

See Also: "Cluster Interconnects Page" in the Enterprise Manager online help

Cluster-Managed Database Services

Services are groups or classifications of applications that comprise business components that extend and respond to application workloads. Examples of services are Accounts Payable, Customer Relationship Management, and so on. Services in RAC enable continuous, uninterrupted database operations to support multiple services on multiple instances.

Services enable RAC to integrate cluster database resources into a single system image to optimize cluster manageability. This simplifies system deployment, testing, disaster recovery, and administrative overhead. With services, users connect to a database without regard for which instance executes the SQL application service.

You assign services to run on one or more instances, and alternate instances can serve as backup instances in case the primary instance fails. If a primary instance fails, Oracle moves the service from the failed instance to a surviving alternate instance.

Services enable you to model and deploy both planned and unplanned operations for all types of high availability or disaster recovery scenarios. During outages, RAC automatically restarts key components. Components that are eligible for automatic restart include instances, Oracle Net Services listeners, and the database as well as several database subcomponents.

You can create and edit services using the Create Services page in Enterprise Manager, which you can access from the Cluster Managed Database Services link in the Enterprise Manager Maintenance page.

See Also: "Create Service Page" in the Enterprise Manager online help

Oracle Clusterware and High Availability

When you combine Oracle Clusterware and RAC, you can achieve excellent scalability and high availability. To maintain high availability, the Oracle Clusterware components can respond to status changes to restart applications and processes according to high availability rules you can specify in the Create Service page. Oracle Clusterware achieves high availability with the components in Table 12-2.

Table 12-2 Oracle Clusterware High Availability Components

Component	Description
Voting Disk	The voting disk manages cluster membership by using a health check, and arbitrates cluster ownership among the instances in case of network failures. RAC uses the voting disk to determine which instances are members of a cluster.
Oracle Clusterware Registry	The OCR maintains database and cluster database configuration information as well as configuration information about any cluster database within the cluster. The OCR also manages information about processes that Oracle Clusterware controls.
Application Programming Interface	Oracle Clusterware provides a high availability application programming interface (API) that you can use to manage applications or processes that run on single-instance Oracle databases or RAC databases. This enables you to provide high availability for all of your applications.