Oracle Essentials Oracle Database 11g (26 page)

|

123

Manageability Features

Oracle Database 10
g
and its “Intelligent Infrastructure” was a huge step forward in simplifying management of the Oracle database. Many manual steps needed to manage database releases previous to Oracle Database 10
g
were eliminated. Oracle Database 11
g
introduced still more self-tuning and self-management features. Key maintenance tasks are automated, including optimizer statistics gathering, the Segment Advisor, and the SQL Tuning Advisor. Management of the entire infrastructure is accomplished through self-managing capabilities within the database and through Oracle Enterprise Manager.

Statistics containing active session history are now gathered and populate the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM) automatically tracks changes in database performance leveraging the data in the AWR. Server-generated alerts occur “just-in-time” and appear in Enterprise Manager. Resolving system utilization problems can be as simple as reviewing the alerts and accepting the recommendations. This is in sharp contrast to steps typically taken prior to Oracle Database 10
g
that included actively watching for events, exploring V$ tables, identifying related SQL, and then figuring out the needed steps to resolve the problem.

Database Advisors

ADDM is one of several advisors present in Oracle and accessible using Enterprise Manager today. Other performance related advisors include:

SQL Advisor

Oracle Database 11
g
includes the SQL Tuning Advisor, SQL Access Advisor, and Partition Advisor. The SQL Tuning Advisor analyzes SQL statements and makes SQL improvement recommendations. The SQL Access Advisor and Partitioning Advisor recommend when to create indexes, materialized views, or partitioned tables.

SQL Performance Impact Advisor

Introduced in Oracle Database 11
g
, this advisor enables youto forecast how a system change will impact SQL performance.

Memory Advisors

The Memory Advisor is an expert system that provides automatic memory management and eliminates manual adjustment of the SGA and PGA when enabled (and recommended in Oracle Database 11
g
). If just automatic shared memory is enabled instead, youwill have access to the Shared Pool (SGA) Advisor and PGA Advisor. Finally, if youare manually managing shared memory, youwill have access to the Shared Pool (SGA) Advisor, Buffer Cache Advisor, and PGA Advisor.

124

|

Chapter 5: Managing Oracle

Segment Advisor

Use of the Segment Advisor eliminates the need to identify fragmented objects and reorganize the objects using scripts. The Segment Advisor advises which objects to shrink and allows youto simply accept the recommendations. You might also use this information in capacity planning.

Undo Advisor

The Undo Advisor helps size the undo tablespace and can be used to set the low threshold of undo retention for Flashback. Oracle Database 11
g
features automatic undo management.

MTTR Advisor

The Mean Time to Recovery (MTTR) Advisor provides guidance regarding the impact of MTTR settings and physical writes. The mean time for recovery from a system failure is specified based on business needs by the database administrator using Enterprise Manager, and then needed reconfiguration of Oracle components automatically takes place.

Streams Tuning Advisor

The Streams Tuning Advisor reports on throughput and latency for a Streams topology among Oracle databases and can identify bottlenecks.

Another class of advisors introduced in Oracle Database 11
g
can be used to resolve database issues. When critical errors are detected, the fault diagnosability infrastructure for Oracle Database 11
g
can perform a deeper analysis called a
health check
using a Health Monitor. The advisors leverage diagnostic data including database traces, the alert log, Health Monitor reports, and other diagnostic information stored in the Automatic Diagnostic Repository (ADR). The infrastructure also includes a SQL Test Case Builder used for reproducing the problem and transmitting the information to Oracle Support. The advisors in this infrastructure include:
SQL Repair Advisor

If a SQL statement fails with a critical error, the SQL Repair Advisor will analyze the statement and recommend a patch to repair it.

Data Recovery Advisor

The Data Recovery Advisor is used in recovering from corrupted blocks, corrupted or missing files, and other data failures and is integrated with database health checks and RMAN.

Automatic Storage Management

Oracle Database 10
g
introduced Automatic Storage Management (ASM). ASM provides a file system and volume manager in the database, enabling automated striping of files and automating mirroring of database extents. DBAs simply define a pool of
Manageability Features

|

125

storage or disk group and manage the disk group through EM. Disk groups are created with normal redundancy as the default (2-way mirroring). You can also create disk groups with high redundancy (3-way mirroring) or external redundancy (no mirroring). Failure groups are ASM disks that share a common failure point, so mirroring will automatically occur to a different failure group to provide high availability.

Oracle manages the files that are stored in ASM disk groups. ASM can manage Oracle datafiles, logfiles, control files, archive logs, and RMAN/backup sets. Workloads can be dynamically rebalanced as storage is reconfigured such that when storage is added or removed from the pool, data can be redistributed in the background.

Oracle Enterprise Manager

Oracle Enterprise Manager was first distributed with Oracle7 and was designed for simplifying database management. Early EM versions required Windows-based workstations as client machines. A Java applet browser-based EM console appeared with the Oracle8
i
database release. The HTML-based console was introduced with Oracle9
i
products, including the Oracle Application Server, and is now the basis for Enterprise Manager included with the database. As of Oracle Database 11
g
, the Java applet console is no longer available.

Today, EM is far more than just a database management interface. EM has many optional packs that extend its ability to manage not only Oracle databases but also other infrastructure components commonly present. These packs include:
Database Management Packs

Diagnostics, Tuning, Change Management, Configuration Management, Provisioning

Standalone Management Packs

Provisioning, Service Level Management

Application Management Packs

E-Business Suite, PeopleSoft Enterprise, Siebel

Middleware (Oracle Application Server) Management Packs

Diagnostics, Configuration Management, Identity Management, Provisioning, SOA Management

Management Connectors

Microsoft Operations Manager, Remedy Helpdesk

Operating System Management Packs

Oracle Linux

System Monitoring Plug-ins

EMC Celerra, EMC Symmetrix DMX, NetApp Filer, BEA WebLogic, JBoss Application Server, IBM WebSphere, IBM WebSphere MQ, IBM DB2, Microsoft IIS Server, Microsoft Active Directory, Microsoft BizTalk Server, Microsoft
126

|

Chapter 5: Managing Oracle

Commerce Server, Microsoft ISA Server, Microsoft .NET framework, Microsoft SQL Server, Check Point Firewall, Juniper Netscreeen Firewall, F5 BigIP Local Traffic Manager, Linux Hosts, Unix Hosts, Windows Hosts

Since our primary focus in this book is on the Oracle’s database, we’ll focus on EM’s role in such management. Taking a closer look at the Database Management Packs, the functionality provided by each is as follows:

Database Diagnostics Pack

Provides automatic performance diagnostics by leveraging ADDM, the AWR, monitoring templates, and advanced event notification and alerting
Database Tuning Pack

Provides the statistics, SQL profiling, access path, and SQL structure analysis leveraged by the SQL Tuning Advisor and includes the SQL Access Advisor and Object Reorganization Wizard

Database Change Management Pack

Provides capture and version baselines, database object and data copying, and object definition updates

Database Configuration Management Pack

Provides system inventory collection and reporting, configuration comparisons and history, policy manager, and critical patch advisor

Database Provisioning Pack

Provides automated patching, cloning, provisioning, and single instance to RAC

conversion

Enterprise Manager Architecture

Enterprise Manager can be used for management tasks locally, remotely, and/or through firewalls. Individual consoles can manage single databases or multiple databases. Where EM is managing Oracle deployed on a cluster of computers, it is sometimes referenced as
Grid Control
.

The home page for Grid Control lists the software being managed and provides a high-level view of the status of Grid components. You can drill from Grid Control into the consoles for individual databases, application servers, and other targets.

Figure 5-1 illustrates a typical Grid Control home page.

The Enterprise Manager architecture includes the following components:
Oracle Management Agents

These agents monitor targets for health, status, and performance. Agents automatically discover all Oracle components and also report back other relevant hardware and software configuration information to EM via HTTP/HTTPS.

Each monitored Oracle instance has its own agent.

Oracle Enterprise Manager

|

127

Figure 5-1. Typical Grid Control home page

Enterprise Manager Console

The console allows youto view the status of all monitored components and provides management and diagnostic tools.

Oracle Management Service (OMS)

Receives information from Oracle Management Agents and stores it in the Oracle Management Repository. A local version of OMS services Management Repositories on each local database. The OMS is a J2EE Web application that also renders the Grid Control console user interface at the central location.

Oracle Management Repository

Enterprise Manager accesses this repository of health, status, and performance data. A repository is automatically installed on each local database to service each local Enterprise Manager Database Control console. Oracle Grid Control accesses a central Oracle Management Repository serviced by a central OMS and central Oracle Management Agent.

The EM architecture is shown in Figure 5-2.

Management Agents are available for the wide variety of operating systems on which the Oracle database is available and are responsible for automatic service discovery, event monitoring, and job (predefined task) execution. Management Agents can also send Simple Network Management Protocol (SNMP) traps to database performance monitors in other system monitoring tools.

128

|

Chapter 5: Managing Oracle

Monitored Targets

HTTP

Management

HTTP

Management

Service

agent

Central Console

JDBC

HTTP

Management

(Grid Control)

agent

Management

Repository

HTTP

Management

agent

Figure 5-2. Oracle Enterprise Manager architecture

Oracle Enterprise Manager Consoles

EM’s popularity grew as deployment of the Oracle database expanded within companies to multiple operating systems and as additional Oracle software components were added to the mix. EM provides a common interface to managing all of these environments, something that DBA scripts were not always designed for. Further, the Enterprise Manager interface and framework provide simple access to new database self-monitoring features, responding to alerts, and managing jobs, reports, roles, and privileges. An EM console and the underlying “Intelligent Infrastructure” are installed as part of the normal Oracle database installation process. EM automatically discovers target databases as soon as it is installed.

Simple EM management interfaces can also be deployed through the Oracle Application Server Portal. Management portlets are prepackaged for use with the Portal providing displays of target summaries, outstanding alerts (notifications where thresholds are reached or exceeded), metric details, availability timelines, and executive summary information.

Logging into Enterprise Manager after a typical installation brings youto the home page database management console. Tabs are shown to enable quick navigation of EM and can vary based on the Enterprise Manager version deployed. Oracle has continued to modify the interface over the years to make finding management capabilities through the interface more intuitive. The version shipping with early versions of Enterprise Manager for Oracle Database 11
g
includes tabs for Home, Performance, Availability, Server, Schema, Data Movement, and Software and Support pages. Prior to Oracle Database 11
g
, the Enterprise Manager version included tabs for Home, Administration, Maintenance, and Performance pages (see
Figure 5-3).
At the top of the console page in each of these versions are links to setup (for setting up and managing additional administrators, notification methods, etc.), preferences (for example, notification schedules), help, and logout.