Each version of Oracle contains a new set of features that require corresponding management capabilities. This ever-increasing feature curve previously required a fairly steep learning curve on the part of the novice. Over the past few releases, Oracle has made more database features self-tuning and self-managing. Oracle Database 10g and its "Intelligent Infrastructure" is a huge step forward in this effort. Many manual steps needed to manage releases previous to Oracle Database 10g have been eliminated. Management of this infrastructure is accomplished through the self-managing capabilities of the database and through the Oracle Enterprise Manager 10g.
EM can now manage not only multiple Oracle databases, but also the Oracle Application Server, E-Business Suite, and Collaboration Suite. However, as we've mentioned, our primary focus in this book is on Oracle's database management capabilities.
Oracle first distributed the Oracle Enterprise Manager with database packaging in Oracle7. Initial EM versions required Windows-based workstations as client machines. A browser-based EM console built using Java appeared with the Oracle8i database release. The HTML-based console first appeared with Oracle9 iAS and is now included as Enterprise Manager 10g with the database in Oracle Database 10g. Enterprise Manager 10g can be used for management tasks locally, remotely, and/or through firewalls. Individual consoles can manage single databases or multiple databases. Where EM is used to manage Oracle deployed on a grid of computers, or to manage multiple Oracle database instances and products, it is installed using a Grid Control Enterprise Manager CD.
EM is composed of the following components:
These agents monitor databases and services on the local and remote nodes. An agent can both monitor the database and services on a remote node and communicate the results of this monitoring back to EM.
The console allows you to view the status of database components.
Oracle Management Service (OMS)
This service, located in the Oracle Application Server, is deployed as an OC4J (Oracle Application Server Containers for J2EE) application and renders the HTML user interface.
Management Knowledge Center (MKC) / Management Repository
This is the central repository of enterprise-wide management data, including hardware and software configuration data leveraged in lifecycle, cloning, and patch management.
The EM architecture is shown in Figure 5-1.
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 tools such as CA Unicenter Network and Systems Management, the HP OpenView Operations console, and IBM Tivoli. Through the agents, EM can be used to manage Oracle databases running on Unix, Linux, Windows, and many other operating systems.
The growth in deployment within single companies of Oracle on Windows, Linux, and Unix has resulted in growing popularity of the EM interface to manage Oracle instances residing on a mixture of operating systems. The Enterprise Manager 10g initially shipping with Oracle Database 10g provides HTML-based and Java-based consoles. (We'll describe the functionality provided by each later on.) These provide a common interface for management across multiple platforms and a framework that supports a variety of monitoring tools. The HTML console will include more and more functionality over time, including much of what is initially only in the Java console.
The Enterprise Manager 10g interface and framework are used for monitoring and managing events, jobs, new database self-monitoring features, reports, roles and privileges, and audit trails. An EM console and the underlying Intelligent Infrastructure (described in a subsequent section) are installed as part of the normal Oracle Database 10g database installation process. Much of the functionality in previous Enterprise Manager optional packs, such as diagnostics and tuning, performance, and change management, are now included in EM and are built upon the new infrastructure. EM automatically discovers target databases as soon as it is installed.
The HTML console can manage many database tasks and components, including backup and recovery, export and import, instances, Real Application Clusters (RAC), schema, security (users and roles), the OLAP option, and Data Guard. Included is iSQL*Plus, a new browser-based version of SQL*Plus. The Java console enables additional management of Advanced Queues, instances, replication, HTML DB, schema, security, the Spatial Option, storage, Streams, workspace, Fail Safe, LogMiner, and Text. Both also provide SQL Scratchpads, the SQL*Plus worksheet, and management of the OLAP Option and materialized views (Summary Advisor).
Most network and database connectivity management is available in the Java console, although the HTML console now provides some Oracle Net administration. Advanced security management is also through the Java console, including the Enterprise Security Manager, Wallet Manager, Policy Manager, and Directory Manager.
Additional functionality is provided in options (sometimes called packs) to Enterprise Manager in the areas of diagnostics, tuning, change management, and configuration management. The following options are now fully integrated into the EM console and are built upon the new management infrastructure:
This option, which monitors databases on single systems and across systems, aids in identifying performance bottlenecks and providing tools for problem resolution.
Application Server Diagnostics option
This option enables the monitoring of an application that has been deployed using the middle-tier Application Server for availability and overall response.
This option provides recommendations for improving transaction and query performance.
Database Change Management option
This option is used in the evaluation and implementation of database schema changes.
Database Configuration Management optionApplication Server Configuration option
These options are used to track operating systems and associated hardware, as well as software installations and patches. They also provide automated replication and patching.
Finally, the HTML console also contains wireless-enabled features and can be used for management of the Oracle Application Server, including real-time and historical performance monitoring, events monitoring and statistics, J2EE diagnostics, unified Application Server management, application deployment, and cluster management.
A simple management interface is also deployable through the Oracle Application Server Portal. Five 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.
Let's have a closer look at managing Oracle through the HTML console that is available as part of a normal Oracle Database 10g instance installation. Logging into Enterprise Manager brings you to the home page of your database (see Figure 5-2). Four tabs are shown to provide quick EM navigation: a home tab (your initial location), an administration tab, a maintenance tab, and a performance tab. At the top of the console page, there are also links to setup (for setting up and managing additional administrators, notification methods, etc.), preferences (e.g., notification schedules), help, and logout. The following describe what you'll see in each of the tabbed areas:
Home Page
The Home Page is segmented into the following areas: general characteristics (e.g., status, instance name, version, host, listener names, and Oracle home), host CPU, active sessions, space usage, advise (a link to the Automatic Database Diagnostic Monitor (ADDM) findings, which are described later in this chapter), high availability (instance recovery time and archiving status), activity summary of scheduled and running jobs, and related alerts. The page also includes related links to Advisor Central, all metrics, manage metrics, iSQL*Plus, alert history, blackouts (periods during which data collection activities are suspended), metric collection errors, alert log content, jobs, and configuration monitoring.
Administration Page
The Administration Page has links to instance administration (initialization parameters, undo management, and memory parameters), storage administration (control files, tablespaces, datafiles, rollback segments, redo log groups, archive logs, and temporary tablespace groups), security administration (user, roles, and profiles), the database resource manager (resource monitors, consumer groups, and plans), the scheduler (jobs, schedules, programs, job classes, windows, and window groups), data warehouse administration (OLAP cubes, dimensions, and measures and summary management materialized views, view logs, and refresh groups), schema administration (tables, indexes, views, synonyms, sequences, database links, source types, and user types), and workload administration (workload repository and SQL Tuning Sets).
Maintenance Page
The Maintenance Page has links to utilities (export to files, import from files, load data from files, SQL Access Advisor (described later), gather statistics, reorganize objects, and make tablespace locally managed), recovery (backup, recovery, backup settings, recovery settings, backup management, and recovery catalog), and software management (patching).
Performance Page
The Performance Page graphically shows parameters such as host run queue length, paging rate, instance service time (CPU used, concurrency, scheduler, administrative, configuration, system I/O, application, network, user I/O, commit, and other workload), and instance throughput (logins and transactions, physical reads and redo size). The page also has additional monitoring links to Top Sessions, Top SQL, Advisor Central, blocking sessions, database locks, instance activity, and top consumers.
Advisor Central provides links to many of the new tuning tools in Oracle Database 10g, including ADDM, SQL Tuning, SQL Access, the Memory Advisor, Mean Time to Recovery (MTTR) management, the Segment Advisor, and undo management tools.
Oracle Database 10g also includes a more sophisticated scheduler that enables control of when and where jobs take place. The scheduler programs describe the names of programs to be run, the types of programs, and the program arguments. Jobs are created through the link in the Administrator Page to specify what is to be executed (e.g., a specific PL/SQL program you name for execution) and an execution schedule. Job parameters specified include description, log level, job class, whether a job can be restarted, and the PL/SQL statement to be used. Jobs can be scheduled to run immediately or at specific times. A schedule can be shared by multiple jobs.
EM2Go is a mobile version of Enterprise Manager introduced with Oracle Database 10g. It can be used for remote wireless management of database instances and Oracle Application Server. Providing a subset of the functionality in Enterprise Manager, EM2Go leverages the previously described Oracle Management Service, associated Management Repository, and Oracle Agents in the EM architecture. The Enterprise Manager Console is accessed through a Pocket PC browser on a PDA device. Communication between the Console and OMS and between OMS and the agents is via HTTP. The administrator begins by logging into Oracle Enterprise Manager from the EM2Go home page by entering the appropriate EM username and password. Upon logging in, the administrator is presented with a summary of alerts, database and Application Server availability, and links to more detailed information describing the alerts, availability, and performance of the targets being monitored.
Database administration capabilities in EM2Go include the ability to perform basic administration tasks on control files, tablespaces, datafiles, rollback segments, redo segments, redo log groups, and archive logs. The performance management interface has links into the current Top Sessions, which identify the sessions consuming the most time and resources, their associated SQL statements, and wait status, as well as information on locks, and the Top SQL assessment.
Host monitoring provides a view of the health of the underlying hardware and operating system. Application Server monitoring includes performance statistics and enables setting of thresholds to more easily determine the severity of reported statistics.