Re: Sitescope oracle DB Monitor- error 31008 (1216 Views)
Reply
Valued Contributor
Amarnath_BAC
Posts: 138
Registered: ‎10-19-2010
Message 1 of 14 (2,400 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

Please use plain text.
Valued Contributor
Amarnath_BAC
Posts: 138
Registered: ‎10-19-2010
Message 2 of 14 (2,394 Views)

Re: Sitescope oracle DB Monitor- error 31008

Do we need to install oracle driver on sitescope?

Please use plain text.
Respected Contributor
dcsbeemer
Posts: 298
Registered: ‎07-19-2011
Message 3 of 14 (2,389 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.

Please use plain text.
Valued Contributor
Amarnath_BAC
Posts: 138
Registered: ‎10-19-2010
Message 4 of 14 (2,381 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 !!!

Please use plain text.
Respected Contributor
dcsbeemer
Posts: 298
Registered: ‎07-19-2011
Message 5 of 14 (2,370 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.

Please use plain text.
Valued Contributor
Amarnath_BAC
Posts: 138
Registered: ‎10-19-2010
Message 6 of 14 (2,347 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

 

 

Please use plain text.
Valued Contributor
Amarnath_BAC
Posts: 138
Registered: ‎10-19-2010
Message 7 of 14 (2,340 Views)

Re: Sitescope oracle DB Monitor- error 31008

Hi,

 

Please advice what should be done? Appreciate all your help

 

Please use plain text.
Respected Contributor
dcsbeemer
Posts: 298
Registered: ‎07-19-2011
Message 8 of 14 (2,337 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

Please use plain text.
Valued Contributor
Amarnath_BAC
Posts: 138
Registered: ‎10-19-2010
Message 9 of 14 (2,335 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!

Please use plain text.
Valued Contributor
Amarnath_BAC
Posts: 138
Registered: ‎10-19-2010
Message 10 of 14 (2,333 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!

 

Please use plain text.
Respected Contributor
dcsbeemer
Posts: 298
Registered: ‎07-19-2011
Message 11 of 14 (2,325 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.

Please use plain text.
Valued Contributor
Amarnath_BAC
Posts: 138
Registered: ‎10-19-2010
Message 12 of 14 (2,302 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!!!!!

 

Please use plain text.
Respected Contributor
dcsbeemer
Posts: 298
Registered: ‎07-19-2011
Message 13 of 14 (2,299 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

Please use plain text.
Occasional Advisor
Tareq_Hadid
Posts: 12
Registered: ‎12-02-2013
Message 14 of 14 (1,216 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

Please use plain text.
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