2) In our case, we start the Sitescope service with an AD account. Logged in to the Sitescope machine (we're using MS Windows 2008 Server to run Sitescope) with the AD account in question, configured the system variables (LD_LIBRARY_PATH, default is C:\OracleClient; SQLPATH, default is C:\OracleClient; TNS_ADMIN, default is C:\OracleClient). Logged out and back in to have the variables take effect.
3) Copied the "ojdbc5.jar" file (will be in the location where you installed the Oracle Instant Client, default is C:\OracleClient) to $SITESCOPE_HOME\WEB-INF\lib\
4) Restarted Sitescope
5) Tested everything works fine by running the DB connect tool. Format is:
Connection string: jdbc:oracle:thin:@[HOST][:PORT]:SID OR jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
The client will need to be installed on your Sitescope machine yes.
If I were you, I'd go with the 11g Instant Client. On our side we monitor mostly 10g, but also some 11g DBs, both working fine with the version of Instant Client we have. I think the important thing is just that the ODBC .jar file is in your Sitescope directory (though I'm speaking under correction).
If there is any firewall between your Sitescope machine and the target Oracle DB servers, you'll definitely need certain ports opened up. Safest way would be to establish with the DBAs which DBs use which TCP ports and get those ports opened up. Typically, if you are to monitor the DB and the servers themselves for things like CPU usage, load averages etc., you'd need TCP ports 7 (ICMP, essentially to be able to ping the server), 22 (for SSH) and 1521 (default Oracle port) opened up. If the Oracle DB SIDs use different ports though, you'd need to get those ranges opened up as well.
So to recap: Oracle Instant Client needs to be installed on your Sitescope machine (I'd go with 11g, but pretty sure 10g should do the trick as well) and if there is a FW between your Sitescope machine and the Oracle server(s) you'll need to get certain TCP ports opened up.
If you get stuck somewhere along the way, let us know and we'll go from there.
EDIT: Just having a look now, doesn't seem like the Oracle Instant Client has that many versions. I'd say as long as you install the correct one for your OS (Windows or *Nix, x86 or x64) you should be good to go.
Step 1:Attempting to Locate Driver Class... Driver Class: oracle.jdbc.driver.OracleDriver LOCATED SUCCESSFULLY! Step 2:Attempting to Load Driver Class... Driver Class: LOADED SUCCESSFULLY! Step 3: Attempting to Connect to Database... Database: jdbc:oracle:thin:@1 Connection ESTABLISHED SUCCESSFULLY! Step 4: Attempting to Close Connection... All Resources CLOSED SUCCESSFULLY!
Oh right, I think you're probably trying to implement the "Oracle Database Monitor"? If so, I got mixed up on my side thinking you're trying to implement the "Database Query" or "Database Counter" monitors. Sorry about that.
If you're not getting any counters back by using the following settings:
Then I'd say that the DB user you're connecting with doesn't have the required access. I'd start with requesting the DBA to grant read access to the data dictionary views to the user you're connecting with, then also any particular "V$" views you'd require. I just did a quick test on this side with the user we're connecting with and I am getting numerous counters back. As a rule, we always request read access to the data dictionary views at the very least.
I'll try to get some more official HP documentation around this in the meantime.
EDIT: Just to make absolutely sure, is it the "Oracle Database" monitor you're trying to implement?
EDIT #2: The Sitescope help documentation only mentions the following:
The user specified in the Credentials section must be granted the permission to access System tablespace.
I'd definitely ask for access to the data dictionary views as well though. I think request your DBA to grant read access to the System tablespace, read access to the data dictionary views and, while we're at it, to the "dual" tablespace as well. I'm sure there's an official Oracle name for the "dual" table, but I'm not sure what it is. It really does make your life a bit easier if you need to test something.
I am also facing the same issue. I have made connection successfully with oracle DB from DB Connection tool and also tried with db information tool both working fine but when i am tried to make ORACLE database templete its showing counters throughing the same error.