Before planning a multiple database installation, it is a good idea to be thoroughly familiar with the contents of this guide. In addition to reading this guide, try actually installing and using PCP for ORACLE on a single database instance. Try monitoring a database from one or more other systems. Try using -r to specify a name other than the ORACLE_SID on one of the remote systems. Try removing PCP for ORACLE.

Each PCP performance metric has a name (for example, irix.swap.pagesout), which is mapped to a more compact internal representation for the PCP namespace. This internal representation is known as a PMID (performance metric identifier). The pmcd process on each system manages a collection of Performance Metrics Domain Agents (PMDAs, or simply agents). All requests for performance metrics on a system go to the pmcd.

Each of a pmcd's agents is responsible for gathering performance data from a domain, such as IRIX, ORACLE, Cisco routers, and so on. Each agent that pmcd manages is assigned a unique numeric domain identifier. Every PMID contains a domain identifier that tells its pmcd which agent is responsible for supplying the metric.

For example, there is only one IRIX agent on any given system, because only one copy of IRIX can run on a system. Domain number 1 is reserved for IRIX on all PCP collector systems. So the namespace on every system maps all IRIX performance metrics to a set of PMIDs that all have the domain number 1. Similarly, every PMCD knows that the IRIX agent has domain 1, so any request for a PMID with a domain of 1 is for the IRIX agent.

This works well provided that there is only one copy of a particular agent on a system. Now consider a system with more than one ORACLE database. Running a single “super” agent able to supply information from all of the ORACLE databases on a system is not feasible for a number of reasons (performance, fault resilience, complexity, and so on). Instead, a separate agent is created for each database instance on the system.

Each PCP for ORACLE agent supplies performance metrics from a single database instance. If you have multiple databases, a separate copy of the same agent program is running for each, but with different command-line arguments. The metrics supplied by the agents are distinguished by giving them different names in the namespace, and different domain numbers.

Consider a system with two databases named production and test. There is one copy of pmdaoracle (the PCP for ORACLE agent) running using the production database and another using the test database. The latch miss statistics for the two databases are available as oracle.production.latch.misses and oracle.test.latch.misses, respectively. The domains for these two metrics in the namespace are different, but they must agree with the domains used by pmcd for the two ORACLE agents. This is also the case for remote systems wishing to monitor the production and test databases.

In the following example, there is a single database server system with two databases: production and test.

On the database server system, an agent is required for each database and the namespace must be updated to include ORACLE metrics for each database. Using the Bourne shell, the superuser enters the following commands:

# cd /var/pcp/pmdas/oracle 
# ORACLE_HOME=/oracle/home 
# export ORACLE_HOME 
# ORACLE_SID=production 
# export ORACLE_SID 
# ./Install pcpora/pcpora 

The Install command above prints instructions describing how to add the necessary database user, and generates a Verify script for the production database, Verify-production in the /var/pcp/pmdas/oracle directory. Continuing:

# ORACLE_SID=test 
# export ORACLE_SID 
# ./Install pcpora/pcpora 

Install prints instructions describing how to add the necessary database user, and generates a Verify script for the test database, Verify-test also in /var/pcp/pmdas/oracle.

Now pminfo displays the following output:

$ pminfo -m oracle | grep reqdist 
oracle.production.reqdist PMID: 32.5.0
oracle.test.reqdist PMID: 33.5.0

The above command sequence prints out the PMID of one metric from each of the databases. The first integer in the dotted triple is the domain assigned to that database by the Install script. In this example, the production database is domain 32 and the test database is domain 33.

---

Note: A common error when fetching metrics in a distributed environment is forgetting to specify the host from which the metrics are to be fetched. If you forget to specify a database server system when fetching metrics, localhost gets used by default, and no meaningful values are returned. The following example shows a typical sequence, corrected:

---

$ pmval -t 10 oracle.orders.all.table.scanrows 
pmval: pmLookupDesc: Unknown or illegal metric identifier
$ pmval -h bigboy -t 10 oracle.orders.all.table.scanrows 
metric:    oracle.orders.all.table.scanrows
host:      bigboy
semantics: cumulative counter (converting to rate)
units:     count (converting to count / sec)
samples:   all
interval:  10.00 sec
    53.6
   103.1
    87.4
    97.8

If your organization has several systems, each with one or more ORACLE database instance, then you may wish to assign unique domain numbers to each database instance. This is usually less confusing than having two ORACLE agents for the same PCP domain on different database servers. To accomplish this, you must assign unique domains to the databases manually. The Install script is not sophisticated enough to search through an entire network looking for ORACLE databases.

Create and maintain a central registry of domain numbers. The registry specifies the system's hostname, the database instance name, and the corresponding PCP ORACLE agent's domain number. Keep the registry up to date and periodically check it against all of the /etc/pmcd.conf files in your organization.

Assign a unique domain identifier to each database you intend to monitor. The range of domain identifiers reserved for ORACLE databases instances is 32 to 39, inclusive. Additional numbers in the range 128 to 254 may be used if they are not already taken by other agents.

The following example illustrates the process of configuring PCP for ORACLE support for multiple databases on multiple systems. Assume that there are three systems:

First, allocate six unique domain identifiers, as shown in Table 3-1. Although the test databases on systems sys1 and sys2 have the same name, they are different database instances.

There are two databases named test. You must give them different names in the PCP namespace to distinguish them, such as test_sys1 and test_sys2. You need only rename them in the PCP namespace; there is no need to re-create the database with a new name on the system.

Next, install an agent for each database running on a database system.

The -d option manually specifies the domain. For the test databases, -r specifies an alternate name for the databases in the namespace. For example, using the Bourne shell on system sys1, the superuser enters the following commands:

# cd /var/pcp/pmdas/oracle 
# ORACLE_HOME=/oracle/home 
# export ORACLE_HOME 
# export ORACLE_SID=production 
# export ORACLE_SID 
# ./Install -d 32 pcpora/pcpora 

Add any necessary database users and run the Verify script for the production database, /var/pcp/pmdas/oracle/Verify-production. Continuing:

# ORACLE_SID=test 
# export ORACLE_SID 
# ./Install -d 33 -r test_sys1 pcpora/pcpora 

Add any necessary database users and run the test database Verify script, Verify-test.

The same process takes place on the systems sys2 and sys3. For these, only the Install commands are shown. After setting up the environment for the dev database on sys2:

# ./Install -d 34 pcpora/pcpora 

After setting up the environment for the proto database on sys2:

# ./Install -d 35 pcpora/pcpora 

After setting up the environment for the test database on sys2:

# ./Install -d 36 -r test_sys2 pcpora/pcpora 

And after setting up the environment for the demo database on sys3, enter this command:

# ./Install -d 37 pcpora/pcpora 

You do not have to add all the databases to every system's namespace. For example if a system named dbspy is used to monitor only the production database on sys1 plus the dev and test databases on sys2, the following commands suffice. As superuser on dbspy, enter the following commands after installing PCP for ORACLE:

# cd /var/pcp/pmdas/oracle 
# ./Install -N -d 32 -r production 
# ./Install -N -d 34 -r dev 
# ./Install -N -d 36 -r test_sys2 

In the example above, the hostname was appended to the database name for the test databases. For complex ORACLE database deployments, it is a good idea to do this for all the database names, as an aid to remembering which database resides on which system. Remember that you must specify the correct host from which to fetch the metrics. This is easier if the metric name actually contains the correct hostname.

If you have a collection of workstations potentially used to monitor any database, augmentation is not necessary because of distributed namespaces.

Because pmdaoracle is statically linked to the ORACLE libraries in ORACLE_HOME, different ORACLE homes (for different versions of ORACLE) produce different code in the agent. Although it is unusual to have more than one version of ORACLE running on a system, the Install script supports this situation by appending the name of the ORACLE instance to pmdaoracle.