The Oracle Enterprise Manager combines a graphical console, agent processes, and common services to provide an integrated and comprehensive systems management platform for managing Oracle databases on the network. You can perform the following tasks from Enterprise Manager:
| Don't install OEM 1.2.2 and OEM 1.5.0 in Oracle 8.0.3 home |
OEM v1.2.2 isn't compatible with Oracle Server 7.3.3 and 8.0.x. OEM v1.5.0 shouldn't be installed in an Oracle Server 8.0.3 home directory. OEM's latest version (1.5.5), however, works fine for Oracle 7.3.3 and 8.0.x. |
Table 4.1 describes OEM's database application tools that allow you to perform the primary database administration tasks; Figure 4.1 shows these components.
Figure 4.1 : OEM comprises various components that can be used for specific tasks.
| Backup Manager |  | Lock Manager |
 | Data Manager |  | TopSessions |
 | Instance Manager |  | Performance Manager |
 | Schema Manager |  | Oracle Trace |
 | Security Manager |  | Navigator window |
 | SQL Worksheet |  | Job Scheduling window |
 | Storage Manager |  | Event Management window |
| Instance Manager | Manage instances, INIT.ORA file initialization parameters, and sessions |
| TableSpace Manager | Manage fragmentation and free space in tablespaces |
| Storage Manager | Manage tablespaces, data files, and rollback segments |
| Security Manager | Manage users, roles, privileges, and profiles |
| Schema Manager | Manage schema objects such as tables, indexes, views, clusters, synonyms, and sequences |
| Server Manager | Perform line-mode database operations from the client |
| Software Manager | Manage the software distribution process |
| Backup Manager | Perform database backups and create backup scripts |
| Data Manager | Perform export/import and data loads |
SEE ALSO
Using the Instance Manager,
The Enterprise Manager environment consists of the following major components:
The basic OEM functionality is available to you with Oracle Server; however, you can install several optional management packs: Change Management Pack, Diagnostic Pack, and Tuning Pack.
The console user interface contains a set of windows that provide
various views of the system. There's only one console per client
machine. Table 4.2 describes the various components of the console.
| Navigator window | A tree view of all the objects in the system and their relationships |
| Map window | Allows customization of the system views |
| Job window | User interface to the Job Scheduling system |
| Event Management window | User interface to the event management system |
The following services are common to various OEM components (see Table 4.3 for details on how these components interact):
| Multiple repositories can exist within the same database |
You can use one repository, or you can switch between multiple repositories stored in the same database. |
| Reactive management is provided by the job and event systems |
You can use the Job and Event systems together to provide a reactive management system. This is achieved by allowing certain jobs to get executed when the specified events occur. |
| Console and communication daemon | The console sends job and event requests to the communication daemon, and the status of these jobs and events are sent back to the console. Authentication requests of users logging in to the console are sent to the daemon. The daemon sends information to update the tree of nodes and services in the Navigator. |
| Communication daemon and Common Services | Job and event requests are handed to the Job or Event Management systems. The Common Services passes job and event status back to the communication daemon. Service Discovery information is passed from the Common Services to the daemon. |
| Communication daemon and intelligent agent | Agents communicate with the daemon to report results and status messages for jobs and events from the remote nodes. |
| Common Services and Repository | The Event Management and Job Management systems write event and job information, respectively, to the Repository. |
Figure 4.2 represents the communication path between different components of the Enterprise Manager in terms of the jobs, events, or any other requests logged in to the console.
Figure 4.2 : Interaction between the various OEM components is well-defined.
Intelligent agents are intelligent processes running on remote nodes. Each agent resides on the same node as the service it supports and can support all the services on that node. Intelligent agents perform the following functions:
| Use an intelligent agent to manage an older Oracle release |
Each intelligent agent is compatible with the database with which it's released and prior database releases. When used to manage an older release of the database, the intelligent agent must be installed in an ORACLE_HOME directory current with the agent release. Older releases of the intelligent agent aren't compatible with newer releases of the database. |
An agent is required for all or some functionality of these components: Service Discovery; Job Control System; Event Management System; Backup Manager; Software Manager; Data Manager's Export, Import, and Load applications; Oracle Events; and Trace.
The APIs available with Enterprise Manager enable third-party applications-for example, applications that can analyze the data collected through Oracle Expert-to integrate the console with the Common Services. Third-party applications written in C++ that use OLE technology work very well with these APIs. Applications can be integrated at the console, service, or agent level; however, this integration depends on the third-party applications.
Several issues are involved in the installation and configuration of Enterprise Manager that you should address for the components to work together. Some issues include setting up the client, the server, and the Repository. Configuration involves setting preferred credentials and setting up security, among other things.
| Not available for UNIX |
OEM is available only for Windows NT and Windows 95. However, the intelligent agent can run on UNIX or Windows NT. |
You need the following minimum hardware resources to install and use the OEM components:
| Installing documentation is optional |
The OEM documentation can take a lot of space. If you don't have enough disk space, you can run it from the CD-ROM when needed. |
The following minimum software resources are needed:
Table 4.4 lists the components of Oracle Enterprise Manager version
1.5.0 and their compatibility with specific releases of Oracle
Server.
| Repository | |||||
| Local | |||||
| Remote | |||||
| Service Discovery | |||||
| Job Control System | |||||
| Event Management System | |||||
| Database Applications | |||||
| Backup Manager | |||||
| Instance Manager | |||||
| Schema Manager | |||||
| Security Manager | |||||
| Storage Manager | |||||
| SQL Worksheet | |||||
| Software Manager | |||||
| Utility Applications | |||||
| Data Manager/Export | |||||
| Data Manager/Import | |||||
| Data Manager/Load | |||||
| Performance Pack | |||||
| Expert | |||||
| Lock Manager | |||||
| Oracle Events | |||||
| Performance Manager | |||||
| Tablespace Manager | |||||
| Top Sessions | |||||
| Trace | |||||
| OEM 1.5 must be installed in a different home if there is a local 8.0.3 database. | |
| Software Manager can support Oracle Server 7.3.3 agents (Windows NT only) with upgraded OSM job files. |
Install and configure Enterprise Manager (general steps)
You can use the following tools to generate the different files required for Net8 and Enterprise Manager:
| Net8 must be installed before installing OEM |
If Net8 isn't installed on the machine, select it from the OEM installer. You also can choose to install Performance Pack at this point. You need to configure Net8 so that it can use the database you want to access with OEM. |
You use Network Manager and Topology Generator to generate the TNSNAMES.ORA file, which will contain the information for the sample database. However, you need to edit this file with the Net8 easy configuration utility or a text editor so it will have information of other databases that you will use from OEM.
You can choose to install the intelligent agent as part of the Oracle Server installation, or you can install it later by running the Oracle installer.
The following is required for the agent to function correctly:
Before the agent is started, you must do the following to create a user account with appropriate privileges for the intelligent agent:
SEE ALSO
Creating a user,
Granting privileges to roles,
Install Oracle Enterprise Manager
Before Oracle Enterprise Manager is used, you must create a set of base tables that contain environment information for the managed databases-this is the Repository. You create the necessary tables in the Repository by using the SMPCRE.SQL and XPOCR.SQL scripts found in the $ORACLE_HOME/ rdbms/admin directory.
An Oracle user must be created with appropriate permissions to access the Repository before the scripts are run. For each user that needs to access the console, a separate Repository must be created and setup scripts must be run.
| Console and repository compatibility |
The Repository must be compatible with the version of the Oracle Enterprise Manager. If the Repository version is older or newer than the console version, you must install a more recent compatible version of Enterprise Manager. |
Set up the user and Repository
SVRMGR> create user sysman identified by sysman
SVRMGR>grant dba to sysman
SVRMGR> connect sysman/sysman@testdb
SVRMRG>@smpcre.sql SVRMGR>@xpocr.sql
The user sysman can now log in to the Oracle Enterprise Manager.
For Enterprise Manager to connect and work successfully, the intelligent agent and the listener must be started on the server. For the client to communicate with the server, the communication daemon must be running.
| Starting the daemon |
The communication daemon is started and shut down automatically when Enterprise Manager is started. |
The following shows how to run the agent and the listener:
| Start the agent | c: > net start oracleagent |
| Shut down the agent | c: > net stop oracleagent |
| View agent's status | c: > net start |
| Start the listener on UNIX | $ lsnrctl start testdblsnr |
| Shut down the listener on UNIX | $ lsnrctl stop testdblsnr |
| Start/stop the listener in Windows NT |
Use the Control Panel's Services tool to start and stop the listener in Windows NT. |
Test the configuration
$ lsnrctl stop testlsnr $ lsnrctl dbsnmp_stop
| Logging in to OEM doesn't require the agent to be running |
Log in is possible without the agent running, because the agent is required only for jobs and events submitted or returned by the remote database or the console. |
$ lsnrctl start testlsnr
Figure 4.3 : Log in to OEM by providing the information requested.
| The username to use for connecting to the database (for example, sysman) |
| The password for the username |
| The Net8 service name for the database to which you're connecting |
| Connect as Normal, SYSOPER, or SYSDBA |
Setting Up Preferred Credentials
You set up preferred credentials to avoid retying the service name, service type, and username for each database, listener, or node that the user intends to access. The Preferred Credentials page of the User Preferences dialog box shows the list of databases, listeners, and nodes in the network (see Figure 4.4).
Figure 4.4: You can set preferred credentials from the console.
Set the preferred credentials
The following operations on a remote instance require that security be set up for Enterprise Manager users:
Set up remote security
$ orapwd file=orapwtestdb password=testpass entries=10
SVRMGR> grant sysdba to sysman SVRMGR> grant sysoper to sysman
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
At this point, the database instance can be shut down from Enterprise Manager, but local security needs to be set up on Windows NT clients to start up the database from OEM.
Set up local security
OEM uses several files on the client side-SQLNET.ORA, LISTENER.ORA, and TOPOLOGY.ORA-in the $ORACLE_HOME/network/admin directory. SQLNET.ORA contains optional parameters that can be used for tracing Net8 connections, whereas LISTENER.ORA is used to provide the address on which the listener listens.
======================================================================================================================
| ORACLE_HOME is set to c:\orant |
| Because the default domain and zone are set to world, the service names in TNSNAMES.ORA should have world tagged to them |
======================================================================================================================
| The domain world must match the SQLNET.ORA file |
| Should match the port in the SNMP.ORA file |
| Should match the port in the LISTENER.ORA file |
| The database and SID name is test |
The port numbers in TNSNAMES.ORA must be unused by any other service and must be valid port numbers as per TCP/IP standards.
======================================================================================================================
| Should match agent name in TNSNAMES.ORA |
| Should match listener name in LISTENER.ORA |
| Database name should match the one in TNSNAMES.ORA |
The agent uses several files on the server side-SQLNET.ORA, TNSNAMES.ORA, and LISTENER.ORA-that reside in the $ORACLE_HOME/network/agent directory. SQLNET.ORA contains optional parameters that can be used for tracing Net8 connections, whereas LISTENER.ORA is used to provide the address on which the listener listens.
=========================================================== AUTOMATIC_IPC=OFF trace_level_server=off TRACE_LEVEL_CLIENT=off SQLNET.EXPIRE_TIME=0 NAMES.DEFAULT_DOMAIN=world NAME.DEFAULT_ZONE=world SQLNET.CRYPTO_SEED="-2089208790-14606653312" ===========================================================
){kind=link}
){kind=link}
){kind=link}
){kind=link}
===========================================================
#Database Addresses
test.world=(description=
(address_list=
(address=
(community=tcp.world)
(protocol=tcp)
(host=fastmachine)
(port=1701)
)
)
(connect_data=
(sid=test)
(global_name=test.world)
)
)
===========================================================
======================================================================================================================
| Listener name, domain, host name, and SID are the same in all the other files |
| Listener name, domain, host name, and SID are the same in all the other files |
| Must match the port in TNSNAMES.ORA on the client and server machines |
| Listener name, domain, host name, and SID are the same in all the other files |
======================================================================================================================
| Listener name, SID, and host name are the same in the other files |
| Must match exactly with agent address in TNSNAMES.ORA on client machine |
As an Oracle DBA, you'll be performing several tasks on a daily basis on OEM, such as starting up shutting down the database and managing users.
After you set up remote and local security, you can start or shut down an Oracle database from the Enterprise Manager console. To start up or shut down a database, you must have the SYSOPER or SYSDBA role.
Start up the database
Shut down the database
You can easily manage users and privileges by using OEM's Security Manager component (see Figure 4.5). You can manage user information for several databases from one centralized location.
Figure 4.5 : Security Manager enables you to manage users, profiles, and roles.
After Security Manager successfully connects to the database, you see a tree structure with three context-sensitive objects. The database name is displayed next to the database container, and the Users, Roles, and Profiles containers branch from the current database container.
You can use Security Manager's User menu to create, edit, or remove existing users on a database.
| Manipulate roles and profiles from the menus |
Roles and profiles can also be similarly created, edited, and removed by using the Roles and Profiles menus. |
Create a user
Quick-edit a user
Remove a user
The user can also be removed by right-clicking the highlighted username and choosing Remove from the pop-up menu.
The User menu can be used to give privileges to users.
Assign privileges to users
The Security Manager's Profile menu can be used to assign existing profiles to existing users.
Assign profiles to users
Copy a user between databases
Manage database user properties such as quotas, roles, and privileges
You can use Storage Manager (see Figure 4.9) to perform administrative tasks associated with managing database storage, such as managing tablespaces and rollback segments and adding and renaming data files.
You can use Oracle's Tablespace Manager to monitor and manage database storage. It can be used to display graphically how storage has been allocated for the database segments, to defragment segments, and to coalesce free adjacent blocks.
Tablespace Manager's main window includes a tree list on the left and a drill-down on the right for a detailed view. You use the Tablespace Manager as follows:
As a DBA, you should frequently monitor your system resources to identify contention. OEM provides various predefined charts that can help you in monitoring the usage of different resources that can contribute to contention. (For additional information on how to identify and reduce various types on contention, see Chapter 21, "Identifying and Reducing Contention.") Three resources need to be carefully monitored:
| My tuning philosophy |
Performance tuning shouldn't be treated as a reactive strategy; instead, it should be a preventive action based on trends detected through analysis by using tools such as the Performance Pack. |
The Performance Pack is a value-added component of the Oracle Enterprise Manager. It provides various tools to monitor and tune the performance of your database. It's important to understand that taking a point-in-time snapshot of the system doesn't do performance tuning, but it's a way to take into consideration the system performance over a period of time.
You can perform three different types of tuning by using the Performance
Pack components (see Table 4.5).
| Routine Tuning | Used to identify and solve potential problems before they occur |
| Focused Tuning | Used to resolve known performance problems |
| What-If Tuning | Used to determine what would happen if a particular configuration change is made |
The Performance Pack provides several tools (see Table 4.6) to
capture, store, and analyze information so you can improve overall
performance.
| Performance Manager | Displays tuning statistics on contention, database instance, I/O, load, and memory within predefined or customized charts |
| Oracle Expert | Collects and analyzes performance-tuning data on predefined rules, generates tuning recommendations, and provides scripts that help with the implementation of tuning recommendations |
| Oracle Trace | Collects performance data based on events and generates data for the Oracle Expert |
| Oracle TopSessions Monitor | Displays the top 10 sessions based on any specified sort criteria |
| Tablespace Viewer | Displays the free space left on each data file |
| Oracle Lock Manager | Displays the blocked and waiting sessions |
| Oracle Advanced Events | Monitors the specified conditions in the databases, nodes, and networks |
To start the performance-monitoring applications from the OEM console, use the Performance Pack launch palette or the Performance Pack option on the Tools menu.
Performance Manager is a tool for monitoring database performance in real-time. It provides a number of predefined charts for displaying various statistics in different formats, including tables, line charts, bar charts, cube charts, and pie charts (see Figure 4.11).
Figure 4.11: Read consistency hit ratio is one type of information that can be charted.
Performance Manager's Display menu includes items for seven different
categories of predefined charts. Table 4.7 describes these categories
and the set of charts that focus on displaying information of
that category.
| Contention | Circuit, Dispatcher, Free List Hit %, Latch, Lock, Queue, Redo Allocation Hit %, Rollback NoWait Hit %, and Shared Server |
| Database_Instance | Process, Session, System Statistics, Table Access, Tablespace, Tablespace Free Space, #Users Active, #Users Waiting for Locks, and #Users Running |
| I/O | File I/O Rate, File I/O Rate Details, Network I/O Rate, and System I/O Rate |
| Load | Buffer Gets Rate, Network Bytes Rate, Redo Statistics Rate, Sort Rows Rate, Table Scan Rows Rate, and Throughput Rate |
| Memory | Buffer Cache Hit %, Data Dictionary Cache Hit %, Library Cache Hit %, Library Cache Details, SQL Area, Memory Allocated, Memory Sort Hit %, Parse Ratio, and Read Consistency Hit % |
| Overview | #Users Active, #Users Logged On, #Users Running, #Users Waiting, Buffer Cache Hit, Data Dictionary Cache Hit, File I/O Rate, Rollback NoWait Hit %, System I/O Rate, and Throughput Rate |
| User-Defined | Charts created by the user |
By default, information in the predefined charts is presented in the following manner:
The overview charts are a set of 12 predefined charts that give
a good overall picture of the system (see Table 4.8).
| Number of Users Active | Shows the number of users actively using the database instance. Obtains information from the V$SESSION view. |
| Number of Users Logged On | Shows the number of concurrent users logged on to the database instance, regardless of whether any activity is being performed. Obtains information from V$LICENSE. |
| Number of Users Running | Shows the number of concurrent users logged on to the database instance and now running a transaction. Obtains information from V$SESSION_WAIT. |
| Number of Users Waiting | Shows the number of users now waiting. Obtains information from V$SESSION_WAIT. |
| Buffer Cache Hit % | Shows the buffer cache hit percentage. Obtains information from V$SYSSTAT. |
| Data Dictionary Cache Hit | Shows the Data Dictionary cache hit. Obtains information from V$ROWCACHE. |
| File I/O Rate | Shows the number of physical reads and writes per second for each file of the database instance. Obtains information from V$DBFILE. |
| Rollback NoWait Hit % | Shows the hits and misses for online rollback segments. Obtains information from V$ROLLSTAT. |
| System I/O Rate | Shows I/O statistics including buffer gets, block changes, and physical reads per second for the database instance. Obtains information from V$SYSSTAT. |
| Throughput Rate | Shows the number of user calls and transactions per second for the instance. Obtains information from V$SYSSTAT. |
Get an overall picture of activity on a database with the Overview chart
Monitor disk access, resource contention, and memory utilization
If the predefined charts don't suit your needs, you can create your own charts and save them for future use.
Creating your own charts
You can choose to record data in a chart for analysis at a later time. The collection size varies based on the polling interval, database activity at the time, and the collection interval.
Collect historical data
Playback recorded data
Oracle Expert is a tool in the Performance Pack that you can use to tune a database. All tuning inputs and recommendations are stored in a tuning repository that allows the review and modification of the data and the rules at a later time. It has a knowledge base of tuning rules, designed through a tight relationship between the Oracle Server, Oracle Trace, and Oracle Expert development teams. It provides an explanation for all the recommendations it makes.
| Don't run multiple sessions of Oracle Expert against the same repository |
You can run multiple sessions of Oracle Expert against the same repository, but it's not recommended because it can lead to data conflicts between sessions. |
You should use Oracle Expert to complement your tuning experience, not as a tool to replace your function as a database performance tuner. You should instead focus on what to do with the findings and suggestions provided by Oracle Expert and enhance the rules used by Oracle Expert in analyzing the performance data.
You can use Oracle Expert to tune the following:
| Increase the information analyzed by Oracle Expert |
Before performing instance SGA tuning, run XPVIEW.SQL (in $ORACLE_HOME\rdbms\ admin) against the database being tuned to get better recommendations from Oracle Expert. Doing so causes Oracle Expert to collect additional information about the data-base's shared SQL segment. |
For Oracle Expert to perform data collection, the target database being tuned should have the following tables: dba_tab_columns, dba_constraints, dba_users, dba_data_files, dba_objects, dba_indexes, dba_segments, dba_ind_columns, dba_tables, dba_rollback_segs, dba_sequences, dba_views, dba_tablespaces, dba_synonyms, dba_ts_quotas, and dba_clusters. Oracle Expert doesn't collect information regarding index-only tables, partitioned tables, partitioned indexes, object types, object tables, and object views.
Use Oracle Expert to gather tuning information (general steps)
| Don't tune the SYS or system schema |
Don't use Oracle Expert to tune the SYS or system schema. You should let Oracle tune these items automatically. |
Start an Expert Tuning session
| Have enough privileges to perform some functions |
If the database management functions are grayed out from the menu bar, it may be because you aren't authorized to perform those functions. Reconnect as SYSOPER or SYSDBA. |
The collection classes to use are determined by the selected tuning categories for a tuning session.
| Reuse collected data |
When tuning multiple categories, the common classes need to be collected only once because Oracle Expert will be able to reuse the data for analysis. |
Start Oracle Expert
| Permissions to use Oracle Expert |
The user running Oracle Expert must have SELECT ANY TABLE privilege for the database in which the repository is stored. |
You can use the Oracle TopSessions utility to view the top Oracle sessions based on specified criteria, such as CPU usage and disk activity. Before running TopSessions for Oracle8, run $ORACLE_HOME/sysman/smptsi80.SQL to create all the supporting tables.
Identify Oracle sessions that use the most CPU
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}