4
Pack Configuration

Some of the applications in the Oracle Diagnostics Pack and the Oracle Tuning Pack require configuration. This chapter covers the following topics:

• Setting Up Oracle TopSessions on page 4-2
• Setting Up Oracle Trace on page 4-4
• Setting Up Oracle Performance Manager on page 4-7
• Setting Up Oracle Tablespace Manager on page 4-9
• Setting Up Oracle Expert on page 4-9
• Setting Up Oracle SQL Analyze on page 4-10

These applications are set up after you have installed the Oracle Diagnostics Pack, Oracle Tuning Pack, and the Oracle Change Management Pack.
 

Note:  The Oracle Diagnostics Pack, Oracle Tuning Pack, and the Oracle Change Management Pack are separately licensed and purchased products.

   

Setting Up the Oracle Diagnostics Pack

The Oracle Diagnostics Pack provides easy-to-use tools for monitoring the health of the system, diagnosing problems, detecting problems automatically, and planning for the future.

This section discusses the following topics:

• Setting Up Oracle TopSessions on page 4-2
• Setting Up Oracle Trace on page 4-4
• Setting Up Oracle Performance Manager on page 4-7

Setting Up Oracle TopSessions

In order to install the features of Oracle TopSessions,

• you need to create additional tables and views on each database you want to connect to from TopSessions.
• certain SELECT privileges must be granted

The smptsixx.sql scripts have been provided to help automate this process.

The xx in the file name identifies the version of the database against which the script should be run. The script for each database version is located in the $ORACLE_HOME\SYSMAN\ADMIN directory..

Version of the Database	Script to Run
Oracle 7.3	smptsi73.sql script
Oracle 8.0	smptsi80.sql script

 

When smptsixx.sql is run, it also automatically runs the following two scripts:

• catbloxx.sql
• utlxplxx.sql

These two scripts create in the managed database some additional tables, views, and public synonyms that are required by the Oracle Advanced Events.

To set up Oracle TopSessions for a database:

1. From the Oracle Enterprise Manager program group, double-click on the SQL Worksheet icon to launch the tool.

For information on starting Oracle Enterprise Manager and tools, see Starting Enterprise Manager DBA Applications on page 2-11.

2. Use the Login Information dialog box to connect to the managed database as SYS.

See the online help or the Oracle Enterprise Manager Administrator's Guide.

3. Grant SELECT ANY TABLE privileges to each administrator account. This step may be omitted if the account has already been granted the same privileges as SYSTEM.

Note that when preparing to run the smptsixx.sql script on managed databases, you should log into each database as SYS, as mentioned previously.


Refer to Setting Up the Repository User Account on page 2-2 for an example of how to grant privileges to an account.

4. Run the smptsixx.sql script for the managed database.

If the smptsixx.sql script is not run on a managed database, you may see a "Table or View does not exist" message when you try to use Oracle TopSessions.

5. Exit SQL Worksheet.

Setting Up Oracle Trace

Oracle Trace relies on correct client/server configuration.

Oracle Trace requires that:

• the Oracle Enterprise Manager intelligent agent for Oracle 7.3.3 or later is installed and running on the nodes that are targets for Oracle Trace collections
• the Oracle Enterprise Manager Console is running, and the nodes you want to use Oracle Trace on are listed in the console Navigator. Oracle Trace uses the Oracle Enterprise Manager job subsystem to perform its tasks.

This section contains the following topics:

• Using Oracle Trace for Oracle 8 Server Collections on page 4-4
• Using Oracle Trace for Oracle 7.3 Server Collections on page 4-4
• Creating Oracle Trace Repository Tables on page 4-4
• Creating Oracle Trace Formatter Tables on page 4-5
• Other Configuration Information on page 4-6

Using Oracle Trace for Oracle 8 Server Collections

If you are using Oracle Trace for Oracle8 server collections, you must set the value of the ORACLE_TRACE_ENABLE parameter in your INITsid.ORA file to TRUE.

Using Oracle Trace for Oracle 7.3 Server Collections

If you are using Oracle Trace for Oracle 7.3 server collections, check that the Oracle Trace user account, TRACESVR, and the Oracle Trace stored procedure packages: DBMS_ORACLE_TRACE_AGENT and DBMS_ORACLE_TRACE_USER, exist. If they do not, you must create them by running the otrcsvr.sql script as SYS. The otrcsvr.sql script is located in $ORACLE_HOME/otrace/admin on UNIX systems and in $ORACLE_HOME\otracexx\admin on NT systems.

The otrcsvr.sql script is run automatically during database installation on most platforms. However, if your server platform is NT, you must run this script manually.

Creating Oracle Trace Repository Tables

All the necessary tables for Oracle Trace are created or updated automatically when any Oracle Diagnostics Pack, Oracle Tuning Pack, or Oracle Change Management Pack product is started for the first time.

Creating Oracle Trace Formatter Tables

If you are using the Oracle Trace Collection Services version 8.0.4, the formatter tables are created for you.

The Oracle Trace formatter tables are required in managed databases by Oracle Trace, which converts and loads an Oracle Trace binary file (collection_name.dat) into Oracle tables for access.

Oracle Trace data collected from Oracle 7.3.3 and later databases can only be stored in databases that are using the latest Oracle Trace formatter tables. Use the Repository Manager or the vobsh command from a DOS window to create new formatter tables, delete existing formatter tables, or upgrade earlier versions of formatter tables to the latest version for a database.

Please refer to the Oracle Enterprise Manager Administrator's Guide for information on the Repository Manager. The formatter table operations and vobsh commands to perform those operations are described below.

These vobsh commands fail if the specified user does not have an account for the specified service. In this case, create an account for the user on the specified service, then execute the vobsh command again.

Creating New Formatter Tables

To create new formatter tables in a database where no formatter tables currently exist, use the vobsh command:

vobsh -c "user/password@service" -o CREATE -p "EPCFMT"

Note that the formatted data stored in formatter tables can grow very large, so the database in which formatter tables are created should have sufficient space to store large amounts of data.

Dropping Existing Formatter Tables

To drop an existing formatter tables from a table, use the vobsh command:

vobsh -c "user/password@service" -o DROP -p "EPCFMT"

This command drops any version of formatter tables from the specified database. Note that in addition to dropping the existing formatter tables, vobsh also deletes the formatted data stored in the tables.

Upgrading Formatter Tables

To validate (upgrade) older versions of the formatter tables (which creates, drops, or upgrades the formatter tables as needed), use vobsh command:

vobsh -c "user/password@service" -o VALIDATE -p "EPCFMT"

This command determines what version of the formatter tables the database has and allows you to upgrade them to the new version, if necessary.

Other Configuration Information

If you experience any problems running Oracle Trace after completing the configuration instructions in this chapter, refer to the "Troubleshooting Oracle Trace" appendix of the Oracle Enterprise Manager Oracle Trace User's Guide.

Setting Up Oracle Performance Manager

This section contains the following topics:

Converting Old Performance Manager User-defined Charts on page 4-7

Using Performance Manager with Parallel Server on page 4-8

Converting Old Performance Manager User-defined Charts

If you created user-defined charts using Oracle Performance Manager 1.5.0 or earlier (the Windows version of Oracle Performance Manager), you can convert those charts so that they can be used with Oracle Performance Manager 1.6.0. To convert the user-defined charts:

1. Run vmmmig.exe, which creates a text file named vtmusr.txt in $ORACLE_HOME\SYSMAN\ADMIN. This text file contains data about the user-defined charts created using Oracle Performance Manager 1.5.0 or earlier. When you run vmmmig.exe, specify the username, password, and service for the Oracle Enterprise Manager repository that contains the user-defined charts that you want to convert, for example:

vmmmig joseph/password@my_rep

Note that my_rep in the previous command line is the service name for the Oracle Enterprise Manager repository.

2. Run vmm2vtm, which uses the data in vtmusr.txt to create user-defined charts in the repository that can be used with Oracle Performance Manager 1.6.0. When you run vmm2vtm, supply a username, password, and service for the Oracle Enterprise Manager 1.6.0 repository and the name of the service under which you want the user-defined charts stored for Oracle Performance Manager 1.6.0, for example:

vmm2vtm joseph/password@my_rep my_db

Note that my_rep in the previous command line is the service name of the Oracle Enterprise Manager 1.6.0 repository and my_db is the name of the target service under which you want the user-defined charts stored. In other words, after the previous command is executed, all the user-defined charts are converted and stored under the my_db service in the Oracle Performance Manager 1.6.0 tree view.


If any error messages are generated when you execute vmm2vtm, edit vtmusr.txt and execute vmm2vtm again.

Using Performance Manager with Parallel Server

To use Performance Manager in a Parallel Server environment, two instances must be started, otherwise Performance Manager does not treat the server as a Parallel Server.

There are three scenarios for using Performance Manager in an Oracle Parallel Server environment, and different configuration steps for each scenario.

The three scenarios and their configuration steps are:

1. If you are using Performance Manager 1.5.0 or later to monitor an Oracle7 Parallel Server environment, you must run the following scripts:
• ops_ctab.sql
• ops_dbl.sql
• ops_gdl.sql
• ops_mon.sql
• ops_pack.sql

These Parallel Server scripts are provided with the Parallel Server installation. These scripts are also available in the $ORACLE_HOME\SYSMAN\ADMIN directory. Running these scripts creates performance monitoring tables and views that Performance Manager requires to fetch and display Oracle7 Parallel Server data in charts.

2. If you are using Performance Manager 1.5.0 to monitor an Oracle8 Parallel Server environment, you must run the ops_8mon.sql script.

This script is provided in the $ORACLE_HOME\SYSMAN\ADMIN directory. Running the script creates performance monitoring tables and views that Performance Manager requires to fetch and display Oracle8 Parallel Server data in charts.

3. If you are using Performance Manager 1.5.5 or later to monitor an Oracle8 Parallel Server environment, you do not need to run any of the Parallel Server scripts.

For more information about Parallel Server scripts, see the Oracle Parallel Server Management User's Guide in the Parallel Server documentation set.

Setting Up the Oracle Tuning Pack

Oracle Tuning Pack addresses particular tuning needs and activities that ensure the database and applications run at peak efficiency.

This section discusses the following topics:

• Setting Up Oracle Tablespace Manager on page 4-9
• Setting Up Oracle Expert on page 4-9
• Setting Up Oracle SQL Analyze on page 4-10

Setting Up Oracle Tablespace Manager

Before you can use Tablespace Manager's Analyze Wizard against a database, the CHAINED_ROWS table must exist in the database. To create the CHAINED_ROWS table:

1. Log into the database as SYS.
2. Run utlchain.sql, which is located in the $ORACLE_HOME/RDBMS80/ADMIN directory.

The CHAINED_ROWS table is also used by the Oracle Advanced Events chained rows event.

Setting Up Oracle Expert

Oracle Expert requires a set of database tables in the Oracle Enterprise Manager repository. These tables store the data associated with each Oracle Expert tuning session.

All the necessary tables are created or updated automatically when any Oracle Diagnostics Pack, Oracle Tuning Pack, or Oracle Change Management Pack product is started for the first time.

Setting Up Oracle SQL Analyze

In order to run Oracle SQL Analyze, you must have certain object privileges which are available to users that are granted the DBA role. If you want to assign the minimum privileges required, you can use the optional SQLADMIN role, which assigns the base set of object privileges required by a user.

The VMQROLE.SQL script has been provided to help automate the process of creating the SQLADMIN role. It is located in the $ORACLE_HOME\SYSMAN\ADMIN directory.

1. From the Oracle Enterprise Manager program group, double-click on the SQL Worksheet icon to start the tool.
2. Use the Login Information dialog box to connect to the database you want to run SQL Analyze on as SYS.
3. From the Worksheet menu, run the VMQROLE.SQL script to create the SQLADMIN role for the managed database.
4. In the SQL Worksheet bottom pane, assign the SQLADMIN role to the user by typing

Grant SQLADMIN to <user>;

5. Exit SQL Worksheet.

---