Appendix B. Troubleshooting PCP for ORACLE

Before installing an ORACLE PMDA, please read this book or the README file in /var/pcp/pmdas/oracle. To fix problems you might have to run the Install and Remove scripts in that directory, so it would be a good idea to review the documentation to refresh your memory before continuing.

There is one complete subtree of ORACLE metrics in the PCP namespace for each ORACLE database instance for which you have installed an ORACLE PMDA.

The log files for ORACLE PMDAs are /var/adm/pcplog/oracle-*.log, where the star is replaced by the ORACLE instance name assigned to the PMDA at install time. Thus, the log file for the ORACLE PMDA instance named “xyz” is /var/adm/pcplog/oracle-xyz.log. Take care to check the date and time in the log files to ensure that you are not using an old log file to diagnose problems.

For more information, see the pmdaoracle(1) and pmdaoraping(1) reference pages.

PMDA Cannot Connect to ORACLE


The ORACLE PMDA's log file contains this entry:

PMDA unable to connect to ORACLE
(invalid username/password; logon denied) 


The ORACLE database user account has not been created for the PMDA or the user.


When the script /var/pcp/pmdas/oracle/Install was run to configure the PMDA, a file was created containing the SQL statements that allow the PMDA to connect to the database and access the performance data. The file is in the /var/pcp/pmdas/oracle directory. For example, if the ORACLE database instance is called xyz, the file is named setup-xyz.sql.

Connect to the ORACLE database instance associated with the PMDA using svrmgrl or sqldba, and run the SQL commands in the file.

If the file creates an ops$... user, make sure that a corresponding UNIX user exists and that /var/pcp/pmdas/32/pmdaoracle-xyz exists, is owned by that UNIX user, and has the setuid bit set. Become that UNIX user (using su or equivalent command), set the ORACLE_HOME and ORACLE_SID environment variables, and attempt a default login to the database:

sqlplus / 

On the other hand, if the file created a normal ORACLE user with a password (for example, user pcp with password meter), log in to the database by typing this command:

sqlplus pcp/meter 

In either case, ensure that you use the same ORACLE_HOME and ORACLE_SID as those specified for the PMDA in the log file. If either is wrong in the log file, alter them in the command line for that PMDA in the /etc/pmcd.conf file.

Finally, send PMCD a reconfiguration request:

# killall -HUP pmcd 

Check the log file for the PMDA again.

ORACLE Connection Errors


The ORACLE PMDA's log has errors of the form:

Error connecting to ORACLE. ORACLE not available.
smsgsg: shmget() failed...


This can be caused by a number of things, ranging from ORACLE being unavailable or misconfigured to incorrect parameters being specified when using the Install script to configure the PMDA.


Make sure that the ORACLE database instance that the problematic PMDA utilizes is available. Connect to it using the command:


and try to fetch some data using a database user other than that of the PMDA. A demonstration user such as scott/tiger is ideal here.

If that works, connect to the database using the same ORACLE user that the PMDA does. Before doing so, make absolutely certain that the values of ORACLE_HOME and ORACLE_SID that appear in the PMDA's log file are correct, then set both these variables in the environment. The ORACLE user and password for the PMDA appear on the command line for the PMDA in /etc/pmcd.conf as the argument to the -c option. For example, if the flag -c pcp/pcp appears on the command line, enter this command:

sqlplus pcp/pcp 

If there is no -c option, the PMDA is using an ops$... login name and /var/pcp/pmdas/pmdaoracle will be setuid. In this case, become the user who owns the pmdaoracle binary (using su or equivalent command) and use a default login to sqlplus:

sqlplus / 

Before doing this, remember to check that ORACLE_HOME and ORACLE_SID are correct. If sqlplus indicates that the login is not permitted because the username or password is invalid, see the section titled “PMDA Cannot Connect to ORACLE”.

If sqlplus refuses to let you in, there may be a problem with your ORACLE database configuration. In particular, ORACLE does not handle NFS-mounted ORACLE_HOME directories well, because write permission is required to update control and log files located in $ORACLE_HOME/dbs.

Once you are in sqlplus, enter this command:

describe v$sysstat 

A description of the v$sysstat view should appear. If it does not, rerun the script to grant the PMDA access to the performance data from the database (see the section titled “PMDA Cannot Connect to ORACLE”).

Restart PMCD by becoming superuser or root and entering:

# /etc/init.d/pcp start 

Inspect the log file for the PMDA. If it started successfully this time, you may need to alter your database startup routine. See the section “Cannot Find ORACLE Metrics” for details.

Cannot Find ORACLE Metrics


After a system is rebooted, or after the ORACLE database is shut down and then restarted, it is not possible to get any ORACLE metrics.


If a PMDA is unable to connect to its ORACLE database instance, it cannot retrieve any performance metrics but will try to connect to the database again later. If the PMDA has a connection to the database and the database is shut down, the PMDA does not notice until the next request for ORACLE metrics arrives. This is because a PMDA accesses the ORACLE database only when a request is made. Thus, if a PMDA is idle while its database is shut down and then brought back up again, the next request to it fails because it is trying to use an old connection to the database.


The ORACLE PMDA periodically attempts to reconnect to the database in response to requests for metrics. The pmdaoracle(1) reference page describes how you can customize reconnection behavior.

PMDA Starts but Later Stops Running


The ORACLE PMDA pmdaoracle is installed and started by pmcd, but later requests to retrieve ORACLE performance metrics produce errors such as these:

No PMCD agent for domain of request


No values available


Typically this means that the pmdaoracle agent has been terminated by pmcd when the agent did not respond quickly enough to a request for performance metrics. This can sometimes happen when either the system is very heavily loaded, or when the ORACLE DBMS is very busy (for example, during a checkpoint).

To verify that this is really the cause of the problem, scan the tail of the /var/adm/pcplog/pmcd.log file, looking for lines of this form:

[Mon Nov 4 15:32:26] pmcd(5024) Warning: _pmRead: timeout (after 5.000 sec) on fd=26
Cleanup "ora_production" agent (dom 32): protocol failure for fd=26

In the PMDA's log file (/var/adm/pcplog/oracle-*.log) should be some messages indicating an orderly termination, such as these:

Log for /var/pcp/pmdas/oracle/32/pmdaoracle-xyz on moomba started...
Set ORACLE_HOME=/oracle/home
Log finished Mon Nov 4 15:40:49 1996


The short-term solution is to restart the ORACLE PMDA by sending pmcd a SIGHUP signal:

# killall -HUP pmcd 

This tells pmcd to restart any deceased PMDAs, restarting pmdaoracle with a fresh connection to the database.

If the problem persists, consider changing the pmcd timeout to be longer than 5 seconds in /etc/config/pmcd.options. For example, add this line to change the default reconnect timeout to 10 seconds:

-r 10