Enterprise Manager Grid Control - Version: 10.1.0.3.0 Information in this document applies to any platform.
Goal
This note describes how to configure a Database Target for Complete Monitoring in Enterprise Manager Grid Control.
Solution
Monitoring 10g and above Databases
When
you first discover an Oracle Database 10g target, you should check the
monitoring credentials to be sure the password for the DBSNMP database
user account is set correctly in the database target properties. Besides setting the monitoring credentials, no other configuration tasks are required to monitor
an Oracle Database 10g target.
Monitoring pre-10g Databases
When you monitor an Oracle9i database or an Oracle8i
database, there is some additional configuration required if you want
to monitor certain types of database performance metrics using the Grid
Control Console.
To monitor these additional performance metrics Enterprise Manager requires that Oracle Statspack and some additional Enterprise Manager packages be installed and configured in the database you are monitoring.
If
these additional objects are not available and configured in the
database, Enterprise Manager will not be able to gather the data for
the complete set of performance metrics. In addition, Enterprise
Manager will not be able to gather information that otherwise could be
readily available from the Database home page, such as Bad SQL and Top SQL Report.
You
can use the Configure Database wizard in the Grid Control Console to
install the required packages into the database, or you can use a
manual procedure.
Monitoring Configuration of pre-10g Databases using the Configure Database wizard
In the Grid Control, go to the Database Home Page of the database you want to configure for complete monitoring
Scroll down to the bottom of the page in the Related Links Pane.
Click on the Monitoring Configuration link
This will launch the wizard and guide you through through the monitoring configuration tasks. This wizard can also be launched to disable the Performance Metrics Monitoring. For additional information on this feature please consult the note below: Note 274436.1 How To Integrate Statspack with EM 10G
Monitoring Configuration of pre-10g Databases using SQL*Plus
To manually install Statspack
and the other required database objects into an Oracle9i database that you are managing with Enterprise Manager
Grid Control, you can use SQL*Plus and the following procedure:
For
each of the commands in this procedure, replace AGENT_HOME with the
actual path to the Oracle Management Agent home directory and replace
ORACLE_HOME with the path to the database home directory.
Log in to the database host using an account with privileges that allow you to write to the database home directory
and to the Management Agent home directory. Start SQL*Plus and connect to the database using the SYS account with SYSDBA privileges. For example:
$PROMPT> ./sqlplus "connect / as sysdba"
Enter the following command to run the database dbmon script:
Enter value for dbm_password: When prompted, enter the password for the DBSNMP account.
The script performs several configuration changes and returns you to the SQL*Plus prompt.
OEM_MONITOR role and DBSNMP user configuration Connect as the SYS user and enter the following command:
SQL> grant OEM_MONITOR to dbsnmp;
If the database is an Oracle8i database, also enter the following commands:
SQL> grant select on sys.ts$ to OEM_MONITOR; SQL> grant select on sys.seg$ to OEM_MONITOR; SQL> grant select on sys.user$ to OEM_MONITOR; SQL> grant select on sys.obj$ to OEM_MONITOR; SQL> grant select on sys.sys_objects to OEM_MONITOR; SQL> grant select on sys.file$ to OEM_MONITOR; SQL> grant select on sys.attrcol$ to OEM_MONITOR; SQL> grant select on sys.clu$ to OEM_MONITOR; SQL> grant select on sys.col$ to OEM_MONITOR; SQL> grant select on sys.ind$ to OEM_MONITOR; SQL> grant select on sys.indpart$ to OEM_MONITOR; SQL> grant select on sys.indsubpart$ to OEM_MONITOR; SQL> grant select on sys.lob$ to OEM_MONITOR; SQL> grant select on sys.lobfrag$ to OEM_MONITOR; SQL> grant select on sys.partobj$ to OEM_MONITOR; SQL> grant select on sys.tab$ to OEM_MONITOR; SQL> grant select on sys.tabpart$ to OEM_MONITOR; SQL> grant select on sys.tabsubpart$ to OEM_MONITOR; SQL> grant select on sys.undo$ to OEM_MONITOR;
Connect as the DBSNMP user.
SQL> connect DBSNMP
Enter the following command:
SQL> @AGENT_HOME/sysman/admin/scripts/db/config/response.plb SQL> grant EXECUTE on dbsnmp.mgmt_response to OEM_MONITOR;
Set up Statspack
Connect as SYS and enter the following command to create the PERFSTAT user:
SQL> @ORACLE_HOME/rdbms/admin/spcreate
Connect as the PERFSTAT user and issue the following commands
For any supported database version, enter the following command from the SYS account:
SQL> show parameter job_queue_processes
If the output from the show parameter command is zero, then perform the following steps to modify the job_queue_processes initialization parameter.
If you start the database using an spfile, enter the following command:
SQL> alter system set job_queue_processes = 2 SCOPE=BOTH;
Otherwise, do the following:
SQL> alter system set job_queue_processes = 2;
Exit
SQL*PLUS and update the init.ora database configuration file with the
following entry so the parameter will be applied whenever the database
is restarted: job_queue_processes=2
Exit SQL*Plus and change directory to the following directory in the home directory of the Management Agent that
is monitoring the database:
$ cd AGENT_HOME/bin Reload the Management Agent by entering the following command:
$PROMPT> ./emctl agent reload
Using
the Grid Control Console, return to the Database home page and verify
that the Bad SQL and Top SQL Report metrics are now being gathered. You may have to wait for 24 hours in order to get the first Top SQL report snapshots
References
Note 274436.1 - How To Integrate Statspack with EM 10G
Keywords
CONFIGURE~DATABASE; MONITORING~CONFIGURATION; DATABASE~CONFIGURATION; OEM_MONITOR; DBSNMP; STATSPACK;
Help us improve our service. Please email us your comments for this document.
.