Re: Sitescope oracle DB Monitor- error 31008 (1636 Views)
Reply
Valued Contributor
Amarnath_BAC
Posts: 150
Registered: ‎10-19-2010
Message 1 of 14 (2,820 Views)
Accepted Solution

Sitescope oracle DB Monitor- error 31008

Hi All,

 

When I try to check using db tool to check i get the following error, I have ensured DB URL is correct and I have the necessary drivers in place in sis directory. Please advice.

 

 

ERROR:

 

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... ***ERROR***: ***ERROR***: Exception Message: Io exception: The Network Adapter could not establish the connection

 

 

Are there any ports needs to be opened?

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...
***ERROR***: ***ERROR***:
Exception Message: Io exception: The Network Adapter could not establish the connection

Valued Contributor
Amarnath_BAC
Posts: 150
Registered: ‎10-19-2010
Message 2 of 14 (2,814 Views)

Re: Sitescope oracle DB Monitor- error 31008

Do we need to install oracle driver on sitescope?

Respected Contributor
dcsbeemer
Posts: 299
Registered: ‎07-19-2011
Message 3 of 14 (2,809 Views)

Re: Sitescope oracle DB Monitor- error 31008

[ Edited ]

Hi Amarnath_BAC

 

 

At first glance it looks like a connection issue. Easy way to check if your Sitescope server can connect to the DB port:

 

telnet ora_DB_server_IP 1521

 

 

Port 1521 is the default TCP port used by Oracle, but might be something else.

 

 

 

Here's what we did:

 

1) Installed the Oracle "Instant Client" (depending on the version of the Oracle DB, available here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html)

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

Driver: oracle.jdbc.driver.OracleDriver

 

More info on the connection strings here: http://www.orafaq.com/wiki/JDBC

 

 

After all the above, it should work, but some other errors may still pop up. If any do, let us know and we'll continue from there.

Valued Contributor
Amarnath_BAC
Posts: 150
Registered: ‎10-19-2010
Message 4 of 14 (2,801 Views)

Re: Sitescope oracle DB Monitor- error 31008

Hi,

 

Do we need to install oracle client in sitescope machine, If yes we have oracle 9,10 & 11 which one should we install in sitescope.

 

and all the oracle DB's are in linux and hp ux servers, Is it needed to open any ports between these  servers.

 

Appreciate your help! After your reply will try installing the client and follow the steps.

 

Thanks again !!!

Respected Contributor
dcsbeemer
Posts: 299
Registered: ‎07-19-2011
Message 5 of 14 (2,790 Views)

Re: Sitescope oracle DB Monitor- error 31008

[ Edited ]

Hi

 

 

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.

Valued Contributor
Amarnath_BAC
Posts: 150
Registered: ‎10-19-2010
Message 6 of 14 (2,767 Views)

Re: Sitescope oracle DB Monitor- error 31008

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!

 

But I am still not able to get the counters

 

 

Valued Contributor
Amarnath_BAC
Posts: 150
Registered: ‎10-19-2010
Message 7 of 14 (2,760 Views)

Re: Sitescope oracle DB Monitor- error 31008

Hi,

 

Please advice what should be done? Appreciate all your help

 

Respected Contributor
dcsbeemer
Posts: 299
Registered: ‎07-19-2011
Message 8 of 14 (2,757 Views)

Re: Sitescope oracle DB Monitor- error 31008

Hi

 

 

What SQL code are you using to test with? Could be that it just doesn't return any data. Quick and easy way to test:

 

select 'SYS_DATE', sysdate from dual

Valued Contributor
Amarnath_BAC
Posts: 150
Registered: ‎10-19-2010
Message 9 of 14 (2,755 Views)

Re: Sitescope oracle DB Monitor- error 31008

Thanks for replying back, I am trying to setup a monitor to get all the counters in a db. Please help me with what query should I mention to get all the counters.

 

Thanks again!

Valued Contributor
Amarnath_BAC
Posts: 150
Registered: ‎10-19-2010
Message 10 of 14 (2,753 Views)

Re: Sitescope oracle DB Monitor- error 31008

[ Edited ]

When I used the query which you have suggested, I get the below error

 

Status: error

Status Summary: SYS_DATE/SYSDATE = n/a

 

However When I tried in DB tool, I am getting the results but I am not able to get in the monitor

 

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: Connection ESTABLISHED SUCCESSFULLY!  
Step 4: Attempting to Execute Query... Statement created. Statement executed.1 rows RETURNED SUCCESSFULLY!
SYS_DATE
SYSDATE
SYS_DATE
2012-09-25 00:55:37.0
 Step 5: Attempting to Close ResultSet, Statement, and Connection... All Resources CLOSED SUCCESSFULLY!

 

Respected Contributor
dcsbeemer
Posts: 299
Registered: ‎07-19-2011
Message 11 of 14 (2,745 Views)

Re: Sitescope oracle DB Monitor- error 31008

[ Edited ]

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:

Database connection URL: Connection string: jdbc:oracle:thin:@[HOST][:PORT]:SID OR jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
Database driver: oracle.jdbc.driver.OracleDriver


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.

Valued Contributor
Amarnath_BAC
Posts: 150
Registered: ‎10-19-2010
Message 12 of 14 (2,722 Views)

Re: Sitescope oracle DB Monitor- error 31008

Hi Dcsbeemer,

 

 

As you said, this was a permission issue. I worked with the DBA to fix it. Thanks for all your help and support!!!!!

 

Respected Contributor
dcsbeemer
Posts: 299
Registered: ‎07-19-2011
Message 13 of 14 (2,719 Views)

Re: Sitescope oracle DB Monitor- error 31008

Hi Amarnath_BAC

 

 

Ah cool, glad you got it working. If you get stuck anywhere else, just let us know.

 

 

Cheers

Occasional Advisor
Tareq_Hadid
Posts: 12
Registered: ‎12-02-2013
Message 14 of 14 (1,636 Views)

Re: Sitescope oracle DB Monitor- error 31008

Hi,

 

I just got the same error, and when I asked the DBA about the Data Dictionary access, they asked which data dictionary view it is failing on. Can you please advise on this.

 

Regards,

Tareq Hadid

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.