Help setting up monitoring Oracle databases with OpenNMS

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Help setting up monitoring Oracle databases with OpenNMS

BeeJay

I’m trying to set up monitoring of Oracle databases in Opennms, and have set up the following:

 

In collectd-conkfiguration.xml:

 

    <package name="ABBCS-Oracle">

        <filter>IPADDR != '0.0.0.0'</filter>

        <include-range begin="1.1.1.1" end="254.254.254.254"/>

        <include-range begin="::1" end="ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff"/>

 

        <service name="OracleMonitoring" interval="600000" user-defined="false" status="on">

            <parameter key="collection" value="OracleMonitoring"/>

            <parameter key="thresholding-enabled" value="true"/>

            <parameter key="driver" value="oracle.jdbc.driver.OracleDriver"/>

            <parameter key="user" value="XXXXX"/>

            <parameter key="password" value="XXXXX"/>

            <parameter key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

        </service>

    </package>

    <collector service="OracleMonitoring" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

 

In datacollection-config.xml:

 

    <snmp-collection name="ABBCS-Oracle" snmpStorageFlag="select">

        <rrd step="300">

            <rra>RRA:AVERAGE:0.5:1:8928</rra>

            <rra>RRA:AVERAGE:0.5:12:8784</rra>

            <rra>RRA:AVERAGE:0.5:480:366</rra>

            <rra>RRA:MIN:0.5:12:8784</rra>

            <rra>RRA:MAX:0.5:12:8784</rra>

        </rrd>

 

        <include-collection dataCollectionGroup="ABBCS-Oracle"/>

    </snmp-collection>

 

 

datacollection/ABBCS-Oracle.xml:

 

<?xml version="1.0"?>

<datacollection-group name="ABBCS-Oracle">

 

   <resourceType name="oracleQuota" label="Oracle Quota"

          resourceLabel="Account ${UserName} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

          <!-- class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/> -->

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

          <!-- class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/> -->

   </resourceType>

 

</datacollection-group>

 

 

 

Jdbc-datacollection-config.xml:  (This is just a test query to show I’ve got the right configuration.)

 

    <jdbc-collection name="ABBCS-Oracle">

        <rrd step="300">

                  <rra>RRA:AVERAGE:0.5:1:2016</rra>

                  <rra>RRA:AVERAGE:0.5:12:1488</rra>

                  <rra>RRA:AVERAGE:0.5:288:366</rra>

                  <rra>RRA:MAX:0.5:288:366</rra>

                  <rra>RRA:MIN:0.5:288:366</rra>

        </rrd>

      <queries>

        <query name="oracleQuota" ifType="ignore" instance-column="USERNAME" resourceType="oracleQuota">

          <statement>

            <queryString>

select

           username||'.'||tablespace_name as username,

           bytes,

           max_bytes

from

         dba_ts_quotas

where

          max_bytes > 0

            </queryString>

          </statement>

          <columns>

            <column name="USERNAME" alias="UserName" data-source-name="UserName" type="string"/>

            <column name="BYTES" alias="BytesUsed" data-source-name="BytesUsed" type="gauge"/>

            <column name="MAX_BYTES" alias="MaxBytes" data-source-name="MaxBytes" type="gauge"/>

          </columns>

        </query>

      </queries>

    </jdbc-collection>

 

 

And I’ve added the following detectors to the default foreign sources  (will probably reduce to one of these once I figure out which I need)

 

      <detector name="ABBCS-Oracle" class="org.opennms.netmgt.provision.detector.jdbc.JdbcDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="port" value="1521"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

      </detector>

      <detector name="ABBCS-Oracle_query" class="org.opennms.netmgt.provision.detector.jdbc.JdbcQueryDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

         <parameter key="port" value="1521"/>

      </detector>

 

 

But I’m getting the following error in the provisiond.log:

 

2017-03-13 16:05:22,799 ERROR [scanExecutor-6] o.o.n.p.s.BasicDetector: isServiceDetected: JdbcQueryDetector: Unexpected error trying to detect JdbcQueryDetector on address 10.2.3.4 port 1521

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

 

 

 

What do I need to do to enable this to work?  I’m having trouble finding any info on setting this up.  All doco about this seems to be written before capsd was deprecated?

 

I’ve had a DBA install the oracle client 12.0.1 on the opennms server,  but I’m not sure what/how/if I need to get the oracle jdbc jar file loaded into opennms

 

Would appreciate all assistance you can give.

 

Regards,

John.

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 


------------------------------------------------------------------------------
Announcing the Oxford Dictionaries API! The API offers world-renowned
dictionary content that is easy and intuitive to access. Sign up for an
account today to start using our lexical data to power your apps and
projects. Get started today and enter our developer competition.
http://sdm.link/oxford
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Help setting up monitoring Oracle databases with OpenNMS

Roskens, Ronald-2

Yes, you need to add the oracle driver jar ( ojdbc7.jar ) into $OPENNMS_HOME/lib/.

 

The collection key in the collectd-configuration.xml needs to match the name you put in jdbc-datacollection-config.xml. This won't work:

<parameter key="collection" value="OracleMonitoring"/>

<jdbc-collection name="ABBCS-Oracle">

 

 

Think of the two detectors as : JdbcDetector ( can I connect ), JdbcQueryDetector ( can I run a specify query ). If you know the database will always run the query, then the JdbcQueryDetector is just extra work IMHO.

 

 

Another thing you might want to do is use OPENNMS_JDBC_HOSTNAME in the url instead of a fixed hostname.

 

<parameter key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:dbsid"/>

 

By including the hostname in the url, any node can successfully run that query since it knows exactly where to connect.

 

Ron

 

From: JohnD Blackburn [mailto:[hidden email]]
Sent: Monday, March 13, 2017 1:29 AM
To: [hidden email]
Subject: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

I’m trying to set up monitoring of Oracle databases in Opennms, and have set up the following:

 

In collectd-conkfiguration.xml:

 

    <package name="ABBCS-Oracle">

        <filter>IPADDR != '0.0.0.0'</filter>

        <include-range begin="1.1.1.1" end="254.254.254.254"/>

        <include-range begin="::1" end="ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff"/>

 

        <service name="OracleMonitoring" interval="600000" user-defined="false" status="on">

            <parameter key="collection" value="OracleMonitoring"/>

            <parameter key="thresholding-enabled" value="true"/>

            <parameter key="driver" value="oracle.jdbc.driver.OracleDriver"/>

            <parameter key="user" value="XXXXX"/>

            <parameter key="password" value="XXXXX"/>

            <parameter key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

        </service>

    </package>

    <collector service="OracleMonitoring" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

 

In datacollection-config.xml:

 

    <snmp-collection name="ABBCS-Oracle" snmpStorageFlag="select">

        <rrd step="300">

            <rra>RRA:AVERAGE:0.5:1:8928</rra>

            <rra>RRA:AVERAGE:0.5:12:8784</rra>

            <rra>RRA:AVERAGE:0.5:480:366</rra>

            <rra>RRA:MIN:0.5:12:8784</rra>

            <rra>RRA:MAX:0.5:12:8784</rra>

        </rrd>

 

        <include-collection dataCollectionGroup="ABBCS-Oracle"/>

    </snmp-collection>

 

 

datacollection/ABBCS-Oracle.xml:

 

<?xml version="1.0"?>

<datacollection-group name="ABBCS-Oracle">

 

   <resourceType name="oracleQuota" label="Oracle Quota"

          resourceLabel="Account ${UserName} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

          <!-- class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/> -->

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

          <!-- class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/> -->

   </resourceType>

 

</datacollection-group>

 

 

 

Jdbc-datacollection-config.xml:  (This is just a test query to show I’ve got the right configuration.)

 

    <jdbc-collection name="ABBCS-Oracle">

        <rrd step="300">

                  <rra>RRA:AVERAGE:0.5:1:2016</rra>

                  <rra>RRA:AVERAGE:0.5:12:1488</rra>

                  <rra>RRA:AVERAGE:0.5:288:366</rra>

                  <rra>RRA:MAX:0.5:288:366</rra>

                  <rra>RRA:MIN:0.5:288:366</rra>

        </rrd>

      <queries>

        <query name="oracleQuota" ifType="ignore" instance-column="USERNAME" resourceType="oracleQuota">

          <statement>

            <queryString>

select

           username||'.'||tablespace_name as username,

           bytes,

           max_bytes

from

         dba_ts_quotas

where

          max_bytes > 0

            </queryString>

          </statement>

          <columns>

            <column name="USERNAME" alias="UserName" data-source-name="UserName" type="string"/>

            <column name="BYTES" alias="BytesUsed" data-source-name="BytesUsed" type="gauge"/>

            <column name="MAX_BYTES" alias="MaxBytes" data-source-name="MaxBytes" type="gauge"/>

          </columns>

        </query>

      </queries>

    </jdbc-collection>

 

 

And I’ve added the following detectors to the default foreign sources  (will probably reduce to one of these once I figure out which I need)

 

      <detector name="ABBCS-Oracle" class="org.opennms.netmgt.provision.detector.jdbc.JdbcDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="port" value="1521"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

      </detector>

      <detector name="ABBCS-Oracle_query" class="org.opennms.netmgt.provision.detector.jdbc.JdbcQueryDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

         <parameter key="port" value="1521"/>

      </detector>

 

 

But I’m getting the following error in the provisiond.log:

 

2017-03-13 16:05:22,799 ERROR [scanExecutor-6] o.o.n.p.s.BasicDetector: isServiceDetected: JdbcQueryDetector: Unexpected error trying to detect JdbcQueryDetector on address 10.2.3.4 port 1521

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

 

 

 

What do I need to do to enable this to work?  I’m having trouble finding any info on setting this up.  All doco about this seems to be written before capsd was deprecated?

 

I’ve had a DBA install the oracle client 12.0.1 on the opennms server,  but I’m not sure what/how/if I need to get the oracle jdbc jar file loaded into opennms

 

Would appreciate all assistance you can give.

 

Regards,

John.

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 


This e-mail message is being sent solely for use by the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by phone or reply by e-mail, delete the original message and destroy all copies. Thank you.

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Help setting up monitoring Oracle databases with OpenNMS

BeeJay

Thanks for that, I’ll give that a go.  And thanks for the heads up on the OPENNMS_JDBC_HOSTNAME.   Is  there a way I can parameterise the SID?  As there are no 2 SID’s the same on our databases, and every new customer will have 4 databases that we will want to monitor.

 

Also, does anyone have some good documentation on setting up oracle monitoring in OpenNMS?  The DBA’s have quite a collection of metrics they want to monitor…

 

Regards,

John

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 

From: Roskens, Ronald [mailto:[hidden email]]
Sent: Tuesday, 14 March 2017 12:37 AM
To: [hidden email]
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

Yes, you need to add the oracle driver jar ( ojdbc7.jar ) into $OPENNMS_HOME/lib/.

 

The collection key in the collectd-configuration.xml needs to match the name you put in jdbc-datacollection-config.xml. This won't work:

<parameter key="collection" value="OracleMonitoring"/>

<jdbc-collection name="ABBCS-Oracle">

 

 

Think of the two detectors as : JdbcDetector ( can I connect ), JdbcQueryDetector ( can I run a specify query ). If you know the database will always run the query, then the JdbcQueryDetector is just extra work IMHO.

 

 

Another thing you might want to do is use OPENNMS_JDBC_HOSTNAME in the url instead of a fixed hostname.

 

<parameter key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:dbsid"/>

 

By including the hostname in the url, any node can successfully run that query since it knows exactly where to connect.

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Monday, March 13, 2017 1:29 AM
To: [hidden email]
Subject: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

I’m trying to set up monitoring of Oracle databases in Opennms, and have set up the following:

 

In collectd-conkfiguration.xml:

 

    <package name="ABBCS-Oracle">

        <filter>IPADDR != '0.0.0.0'</filter>

        <include-range begin="1.1.1.1" end="254.254.254.254"/>

        <include-range begin="::1" end="ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff"/>

 

        <service name="OracleMonitoring" interval="600000" user-defined="false" status="on">

            <parameter key="collection" value="OracleMonitoring"/>

            <parameter key="thresholding-enabled" value="true"/>

            <parameter key="driver" value="oracle.jdbc.driver.OracleDriver"/>

            <parameter key="user" value="XXXXX"/>

            <parameter key="password" value="XXXXX"/>

            <parameter key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

        </service>

    </package>

    <collector service="OracleMonitoring" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

 

In datacollection-config.xml:

 

    <snmp-collection name="ABBCS-Oracle" snmpStorageFlag="select">

        <rrd step="300">

            <rra>RRA:AVERAGE:0.5:1:8928</rra>

            <rra>RRA:AVERAGE:0.5:12:8784</rra>

            <rra>RRA:AVERAGE:0.5:480:366</rra>

            <rra>RRA:MIN:0.5:12:8784</rra>

            <rra>RRA:MAX:0.5:12:8784</rra>

        </rrd>

 

        <include-collection dataCollectionGroup="ABBCS-Oracle"/>

    </snmp-collection>

 

 

datacollection/ABBCS-Oracle.xml:

 

<?xml version="1.0"?>

<datacollection-group name="ABBCS-Oracle">

 

   <resourceType name="oracleQuota" label="Oracle Quota"

          resourceLabel="Account ${UserName} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

          <!-- class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/> -->

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

          <!-- class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/> -->

   </resourceType>

 

</datacollection-group>

 

 

 

Jdbc-datacollection-config.xml:  (This is just a test query to show I’ve got the right configuration.)

 

    <jdbc-collection name="ABBCS-Oracle">

        <rrd step="300">

                  <rra>RRA:AVERAGE:0.5:1:2016</rra>

                  <rra>RRA:AVERAGE:0.5:12:1488</rra>

                  <rra>RRA:AVERAGE:0.5:288:366</rra>

                  <rra>RRA:MAX:0.5:288:366</rra>

                  <rra>RRA:MIN:0.5:288:366</rra>

        </rrd>

      <queries>

        <query name="oracleQuota" ifType="ignore" instance-column="USERNAME" resourceType="oracleQuota">

          <statement>

            <queryString>

select

           username||'.'||tablespace_name as username,

           bytes,

           max_bytes

from

         dba_ts_quotas

where

          max_bytes > 0

            </queryString>

          </statement>

          <columns>

            <column name="USERNAME" alias="UserName" data-source-name="UserName" type="string"/>

            <column name="BYTES" alias="BytesUsed" data-source-name="BytesUsed" type="gauge"/>

            <column name="MAX_BYTES" alias="MaxBytes" data-source-name="MaxBytes" type="gauge"/>

          </columns>

        </query>

      </queries>

    </jdbc-collection>

 

 

And I’ve added the following detectors to the default foreign sources  (will probably reduce to one of these once I figure out which I need)

 

      <detector name="ABBCS-Oracle" class="org.opennms.netmgt.provision.detector.jdbc.JdbcDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="port" value="1521"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

      </detector>

      <detector name="ABBCS-Oracle_query" class="org.opennms.netmgt.provision.detector.jdbc.JdbcQueryDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

         <parameter key="port" value="1521"/>

      </detector>

 

 

But I’m getting the following error in the provisiond.log:

 

2017-03-13 16:05:22,799 ERROR [scanExecutor-6] o.o.n.p.s.BasicDetector: isServiceDetected: JdbcQueryDetector: Unexpected error trying to detect JdbcQueryDetector on address 10.2.3.4 port 1521

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

 

 

 

What do I need to do to enable this to work?  I’m having trouble finding any info on setting this up.  All doco about this seems to be written before capsd was deprecated?

 

I’ve had a DBA install the oracle client 12.0.1 on the opennms server,  but I’m not sure what/how/if I need to get the oracle jdbc jar file loaded into opennms

 

Would appreciate all assistance you can give.

 

Regards,

John.

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 


This e-mail message is being sent solely for use by the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by phone or reply by e-mail, delete the original message and destroy all copies. Thank you.


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Help setting up monitoring Oracle databases with OpenNMS

Roskens, Ronald-2

The SID's are not a valid parameter. So, think of it like this. You have two different databases to get data from, so you need 2 different services in collectd-configuration.xml. Just put the different URLs in each. If you want the services to be named the same, then you'll have to put each service in its own package, and use a filter to limit which get matched.

 

<package name="Oracle-A" remote="false">

<filter>IPADDR != '0.0.0.0' &amp; catincOracle-A</filter>

<service name="Oracle" interval…>

  …

   <parameter  key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:SID1"/>

</service>

</package>

<package name="Oracle-B" remote="false">

<filter>IPADDR != '0.0.0.0' &amp; catincOracle-B</filter>

<service name="Oracle" interval…>

  …

   <parameter  key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:SID2"/>

</service>

</package>

 

Ron

 

From: JohnD Blackburn [mailto:[hidden email]]
Sent: Monday, March 13, 2017 5:51 PM
To: General OpenNMS Discussion
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

Thanks for that, I’ll give that a go.  And thanks for the heads up on the OPENNMS_JDBC_HOSTNAME.   Is  there a way I can parameterise the SID?  As there are no 2 SID’s the same on our databases, and every new customer will have 4 databases that we will want to monitor.

 

Also, does anyone have some good documentation on setting up oracle monitoring in OpenNMS?  The DBA’s have quite a collection of metrics they want to monitor…

 

Regards,

John

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 

From: Roskens, Ronald [[hidden email]]
Sent: Tuesday, 14 March 2017 12:37 AM
To: [hidden email]
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

Yes, you need to add the oracle driver jar ( ojdbc7.jar ) into $OPENNMS_HOME/lib/.

 

The collection key in the collectd-configuration.xml needs to match the name you put in jdbc-datacollection-config.xml. This won't work:

<parameter key="collection" value="OracleMonitoring"/>

<jdbc-collection name="ABBCS-Oracle">

 

 

Think of the two detectors as : JdbcDetector ( can I connect ), JdbcQueryDetector ( can I run a specify query ). If you know the database will always run the query, then the JdbcQueryDetector is just extra work IMHO.

 

 

Another thing you might want to do is use OPENNMS_JDBC_HOSTNAME in the url instead of a fixed hostname.

 

<parameter key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:dbsid"/>

 

By including the hostname in the url, any node can successfully run that query since it knows exactly where to connect.

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Monday, March 13, 2017 1:29 AM
To: [hidden email]
Subject: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

I’m trying to set up monitoring of Oracle databases in Opennms, and have set up the following:

 

In collectd-conkfiguration.xml:

 

    <package name="ABBCS-Oracle">

        <filter>IPADDR != '0.0.0.0'</filter>

        <include-range begin="1.1.1.1" end="254.254.254.254"/>

        <include-range begin="::1" end="ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff"/>

 

        <service name="OracleMonitoring" interval="600000" user-defined="false" status="on">

            <parameter key="collection" value="OracleMonitoring"/>

            <parameter key="thresholding-enabled" value="true"/>

            <parameter key="driver" value="oracle.jdbc.driver.OracleDriver"/>

            <parameter key="user" value="XXXXX"/>

            <parameter key="password" value="XXXXX"/>

            <parameter key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

        </service>

    </package>

    <collector service="OracleMonitoring" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

 

In datacollection-config.xml:

 

    <snmp-collection name="ABBCS-Oracle" snmpStorageFlag="select">

        <rrd step="300">

            <rra>RRA:AVERAGE:0.5:1:8928</rra>

            <rra>RRA:AVERAGE:0.5:12:8784</rra>

            <rra>RRA:AVERAGE:0.5:480:366</rra>

            <rra>RRA:MIN:0.5:12:8784</rra>

            <rra>RRA:MAX:0.5:12:8784</rra>

        </rrd>

 

        <include-collection dataCollectionGroup="ABBCS-Oracle"/>

    </snmp-collection>

 

 

datacollection/ABBCS-Oracle.xml:

 

<?xml version="1.0"?>

<datacollection-group name="ABBCS-Oracle">

 

   <resourceType name="oracleQuota" label="Oracle Quota"

          resourceLabel="Account ${UserName} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

          <!-- class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/> -->

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

          <!-- class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/> -->

   </resourceType>

 

</datacollection-group>

 

 

 

Jdbc-datacollection-config.xml:  (This is just a test query to show I’ve got the right configuration.)

 

    <jdbc-collection name="ABBCS-Oracle">

        <rrd step="300">

                  <rra>RRA:AVERAGE:0.5:1:2016</rra>

                  <rra>RRA:AVERAGE:0.5:12:1488</rra>

                  <rra>RRA:AVERAGE:0.5:288:366</rra>

                  <rra>RRA:MAX:0.5:288:366</rra>

                  <rra>RRA:MIN:0.5:288:366</rra>

        </rrd>

      <queries>

        <query name="oracleQuota" ifType="ignore" instance-column="USERNAME" resourceType="oracleQuota">

          <statement>

            <queryString>

select

           username||'.'||tablespace_name as username,

           bytes,

           max_bytes

from

         dba_ts_quotas

where

          max_bytes > 0

            </queryString>

          </statement>

          <columns>

            <column name="USERNAME" alias="UserName" data-source-name="UserName" type="string"/>

            <column name="BYTES" alias="BytesUsed" data-source-name="BytesUsed" type="gauge"/>

            <column name="MAX_BYTES" alias="MaxBytes" data-source-name="MaxBytes" type="gauge"/>

          </columns>

        </query>

      </queries>

    </jdbc-collection>

 

 

And I’ve added the following detectors to the default foreign sources  (will probably reduce to one of these once I figure out which I need)

 

      <detector name="ABBCS-Oracle" class="org.opennms.netmgt.provision.detector.jdbc.JdbcDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="port" value="1521"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

      </detector>

      <detector name="ABBCS-Oracle_query" class="org.opennms.netmgt.provision.detector.jdbc.JdbcQueryDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

         <parameter key="port" value="1521"/>

      </detector>

 

 

But I’m getting the following error in the provisiond.log:

 

2017-03-13 16:05:22,799 ERROR [scanExecutor-6] o.o.n.p.s.BasicDetector: isServiceDetected: JdbcQueryDetector: Unexpected error trying to detect JdbcQueryDetector on address 10.2.3.4 port 1521

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

 

 

 

What do I need to do to enable this to work?  I’m having trouble finding any info on setting this up.  All doco about this seems to be written before capsd was deprecated?

 

I’ve had a DBA install the oracle client 12.0.1 on the opennms server,  but I’m not sure what/how/if I need to get the oracle jdbc jar file loaded into opennms

 

Would appreciate all assistance you can give.

 

Regards,

John.

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 


This e-mail message is being sent solely for use by the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by phone or reply by e-mail, delete the original message and destroy all copies. Thank you.


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Help setting up monitoring Oracle databases with OpenNMS

BeeJay

Thanks for your response Ronald.

 

So if I must hardcode the SID in the URL, then I could probably filter on IP address.

 

Updating the collection key seems to have got me connecting to the DB… but my example that I borrowed from the internet, is querying invalid tables :D so I’m getting table or view does not exist.   Just getting a more appropriate query for my database…

 

Regards,

John

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 

From: Roskens, Ronald [mailto:[hidden email]]
Sent: Tuesday, 14 March 2017 10:07 AM
To: General OpenNMS Discussion <[hidden email]>
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

The SID's are not a valid parameter. So, think of it like this. You have two different databases to get data from, so you need 2 different services in collectd-configuration.xml. Just put the different URLs in each. If you want the services to be named the same, then you'll have to put each service in its own package, and use a filter to limit which get matched.

 

<package name="Oracle-A" remote="false">

<filter>IPADDR != '0.0.0.0' &amp; catincOracle-A</filter>

<service name="Oracle" interval…>

  …

   <parameter  key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:SID1"/>

</service>

</package>

<package name="Oracle-B" remote="false">

<filter>IPADDR != '0.0.0.0' &amp; catincOracle-B</filter>

<service name="Oracle" interval…>

  …

   <parameter  key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:SID2"/>

</service>

</package>

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Monday, March 13, 2017 5:51 PM
To: General OpenNMS Discussion
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

Thanks for that, I’ll give that a go.  And thanks for the heads up on the OPENNMS_JDBC_HOSTNAME.   Is  there a way I can parameterise the SID?  As there are no 2 SID’s the same on our databases, and every new customer will have 4 databases that we will want to monitor.

 

Also, does anyone have some good documentation on setting up oracle monitoring in OpenNMS?  The DBA’s have quite a collection of metrics they want to monitor…

 

Regards,

John

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 

From: Roskens, Ronald [[hidden email]]
Sent: Tuesday, 14 March 2017 12:37 AM
To: [hidden email]
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

Yes, you need to add the oracle driver jar ( ojdbc7.jar ) into $OPENNMS_HOME/lib/.

 

The collection key in the collectd-configuration.xml needs to match the name you put in jdbc-datacollection-config.xml. This won't work:

<parameter key="collection" value="OracleMonitoring"/>

<jdbc-collection name="ABBCS-Oracle">

 

 

Think of the two detectors as : JdbcDetector ( can I connect ), JdbcQueryDetector ( can I run a specify query ). If you know the database will always run the query, then the JdbcQueryDetector is just extra work IMHO.

 

 

Another thing you might want to do is use OPENNMS_JDBC_HOSTNAME in the url instead of a fixed hostname.

 

<parameter key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:dbsid"/>

 

By including the hostname in the url, any node can successfully run that query since it knows exactly where to connect.

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Monday, March 13, 2017 1:29 AM
To: [hidden email]
Subject: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

I’m trying to set up monitoring of Oracle databases in Opennms, and have set up the following:

 

In collectd-conkfiguration.xml:

 

    <package name="ABBCS-Oracle">

        <filter>IPADDR != '0.0.0.0'</filter>

        <include-range begin="1.1.1.1" end="254.254.254.254"/>

        <include-range begin="::1" end="ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff"/>

 

        <service name="OracleMonitoring" interval="600000" user-defined="false" status="on">

            <parameter key="collection" value="OracleMonitoring"/>

            <parameter key="thresholding-enabled" value="true"/>

            <parameter key="driver" value="oracle.jdbc.driver.OracleDriver"/>

            <parameter key="user" value="XXXXX"/>

            <parameter key="password" value="XXXXX"/>

            <parameter key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

        </service>

    </package>

    <collector service="OracleMonitoring" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

 

In datacollection-config.xml:

 

    <snmp-collection name="ABBCS-Oracle" snmpStorageFlag="select">

        <rrd step="300">

            <rra>RRA:AVERAGE:0.5:1:8928</rra>

            <rra>RRA:AVERAGE:0.5:12:8784</rra>

            <rra>RRA:AVERAGE:0.5:480:366</rra>

            <rra>RRA:MIN:0.5:12:8784</rra>

            <rra>RRA:MAX:0.5:12:8784</rra>

        </rrd>

 

        <include-collection dataCollectionGroup="ABBCS-Oracle"/>

    </snmp-collection>

 

 

datacollection/ABBCS-Oracle.xml:

 

<?xml version="1.0"?>

<datacollection-group name="ABBCS-Oracle">

 

   <resourceType name="oracleQuota" label="Oracle Quota"

          resourceLabel="Account ${UserName} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

          <!-- class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/> -->

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

          <!-- class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/> -->

   </resourceType>

 

</datacollection-group>

 

 

 

Jdbc-datacollection-config.xml:  (This is just a test query to show I’ve got the right configuration.)

 

    <jdbc-collection name="ABBCS-Oracle">

        <rrd step="300">

                  <rra>RRA:AVERAGE:0.5:1:2016</rra>

                  <rra>RRA:AVERAGE:0.5:12:1488</rra>

                  <rra>RRA:AVERAGE:0.5:288:366</rra>

                  <rra>RRA:MAX:0.5:288:366</rra>

                  <rra>RRA:MIN:0.5:288:366</rra>

        </rrd>

      <queries>

        <query name="oracleQuota" ifType="ignore" instance-column="USERNAME" resourceType="oracleQuota">

          <statement>

            <queryString>

select

           username||'.'||tablespace_name as username,

           bytes,

           max_bytes

from

         dba_ts_quotas

where

          max_bytes > 0

            </queryString>

          </statement>

          <columns>

            <column name="USERNAME" alias="UserName" data-source-name="UserName" type="string"/>

            <column name="BYTES" alias="BytesUsed" data-source-name="BytesUsed" type="gauge"/>

            <column name="MAX_BYTES" alias="MaxBytes" data-source-name="MaxBytes" type="gauge"/>

          </columns>

        </query>

      </queries>

    </jdbc-collection>

 

 

And I’ve added the following detectors to the default foreign sources  (will probably reduce to one of these once I figure out which I need)

 

      <detector name="ABBCS-Oracle" class="org.opennms.netmgt.provision.detector.jdbc.JdbcDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="port" value="1521"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

      </detector>

      <detector name="ABBCS-Oracle_query" class="org.opennms.netmgt.provision.detector.jdbc.JdbcQueryDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

         <parameter key="port" value="1521"/>

      </detector>

 

 

But I’m getting the following error in the provisiond.log:

 

2017-03-13 16:05:22,799 ERROR [scanExecutor-6] o.o.n.p.s.BasicDetector: isServiceDetected: JdbcQueryDetector: Unexpected error trying to detect JdbcQueryDetector on address 10.2.3.4 port 1521

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

 

 

 

What do I need to do to enable this to work?  I’m having trouble finding any info on setting this up.  All doco about this seems to be written before capsd was deprecated?

 

I’ve had a DBA install the oracle client 12.0.1 on the opennms server,  but I’m not sure what/how/if I need to get the oracle jdbc jar file loaded into opennms

 

Would appreciate all assistance you can give.

 

Regards,

John.

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 


This e-mail message is being sent solely for use by the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by phone or reply by e-mail, delete the original message and destroy all copies. Thank you.


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Help setting up monitoring Oracle databases with OpenNMS

BeeJay
In reply to this post by Roskens, Ronald-2

Got some queries from the DBA’s that actually work.

 

I’ve added this to my jdbc-datacollection.xml:

 

        <query name="DaysSinceLastBackedUp" ifType="ignore" instance-column="NAME" resourceType="DaysSinceLastBackedUp">

          <statement>

            <queryString>

select

      NAME,

      VALUE1

from zen_tab_5min

where name='days_without_backup'

            </queryString>

          </statement>

          <columns>

            <column name="NAME" alias="Name" data-source-name="Name" type="string"/>

            <column name="VALUE1" alias="Days" data-source-name="Days" type="gauge"/>

          </columns>

        </query>

 

        <query name="tablespace_PctUsed" ifType="ignore" instance_column="VALUE2" resourceType="tablespace_PctUsed">

          <statement>

            <queryString>

select

      VALUE2 as TableSpace,

      VALUE1 as Used

from zen_tab_5min

where name = 'tablespace_%used'

            </queryString>

          </statement>

          <columns>

            <column name="TABLESPACE" alias="TableSpace" data-source-name="TableSpace" type="string"/>

            <column name="USED" alias="Used" data-source-name="Used" type="gauge"/>

          </columns>

        </query>

 

 

That table is populated by a stored procedure and looks like this:

 

select * from zen_tab_5min;

 

NAME                         VALUE1 VALUE2

------------------------ ---------- ----------------------------------------------

days_without_backup            9999

tablespace_%used                  0 BKPUSER_TAB

tablespace_%used                 25 ELLPRF_IDX

tablespace_%used                 13 SYSAUX

tablespace_%used                  0 XMLDB_TBS

tablespace_%used                 16 STATSPACK_DATA

tablespace_%used                  1 USERS

tablespace_%used                 63 ELLPRF_TAB

tablespace_%used                  0 INJRPT_IDX

tablespace_%used                 14 SYSTEM

tablespace_%used                 31 TOOLS

tablespace_%used                  0 INJRPT_TAB

 

 

I’ve added the following to datacollection/ABBCS-Oracle.xml

 

   <resourceType name="DaysSinceLastBackedUp" label="Days Since Last Backed Up"

          resourceLabel="LastBackup ${Name} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

   </resourceType>

 

   <resourceType name="tablespace_PctUsed" label="Tablespace Percent Used"

          resourceLabel="TableSpace ${TableSpace} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

   </resourceType>

 

 

 

But I’m getting the following in the collectd.log:

 

2017-03-14 16:52:54,819 WARN  [Collectd-Thread-26-of-50] o.o.n.c.JdbcCollector: There was a problem executing query 'DaysSinceLastBackedUp' Please review the query or configuration. Reason: Invalid column name

2017-03-14 16:52:54,907 WARN  [Collectd-Thread-26-of-50] o.o.n.c.JdbcCollector: There was a problem executing query 'tablespace_PctUsed' Please review the query or configuration. Reason: Invalid column name

 

 

 

Would appreciate guidance on how to get this to work.

 

Regards,

John

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 

From: JohnD Blackburn
Sent: Tuesday, 14 March 2017 10:41 AM
To: General OpenNMS Discussion <[hidden email]>
Subject: RE: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

Thanks for your response Ronald.

 

So if I must hardcode the SID in the URL, then I could probably filter on IP address.

 

Updating the collection key seems to have got me connecting to the DB… but my example that I borrowed from the internet, is querying invalid tables :D so I’m getting table or view does not exist.   Just getting a more appropriate query for my database…

 

Regards,

John

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 

From: Roskens, Ronald [[hidden email]]
Sent: Tuesday, 14 March 2017 10:07 AM
To: General OpenNMS Discussion <
[hidden email]>
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

The SID's are not a valid parameter. So, think of it like this. You have two different databases to get data from, so you need 2 different services in collectd-configuration.xml. Just put the different URLs in each. If you want the services to be named the same, then you'll have to put each service in its own package, and use a filter to limit which get matched.

 

<package name="Oracle-A" remote="false">

<filter>IPADDR != '0.0.0.0' &amp; catincOracle-A</filter>

<service name="Oracle" interval…>

  …

   <parameter  key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:SID1"/>

</service>

</package>

<package name="Oracle-B" remote="false">

<filter>IPADDR != '0.0.0.0' &amp; catincOracle-B</filter>

<service name="Oracle" interval…>

  …

   <parameter  key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:SID2"/>

</service>

</package>

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Monday, March 13, 2017 5:51 PM
To: General OpenNMS Discussion
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

Thanks for that, I’ll give that a go.  And thanks for the heads up on the OPENNMS_JDBC_HOSTNAME.   Is  there a way I can parameterise the SID?  As there are no 2 SID’s the same on our databases, and every new customer will have 4 databases that we will want to monitor.

 

Also, does anyone have some good documentation on setting up oracle monitoring in OpenNMS?  The DBA’s have quite a collection of metrics they want to monitor…

 

Regards,

John

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 

From: Roskens, Ronald [[hidden email]]
Sent: Tuesday, 14 March 2017 12:37 AM
To:
[hidden email]
Subject: Re: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

Yes, you need to add the oracle driver jar ( ojdbc7.jar ) into $OPENNMS_HOME/lib/.

 

The collection key in the collectd-configuration.xml needs to match the name you put in jdbc-datacollection-config.xml. This won't work:

<parameter key="collection" value="OracleMonitoring"/>

<jdbc-collection name="ABBCS-Oracle">

 

 

Think of the two detectors as : JdbcDetector ( can I connect ), JdbcQueryDetector ( can I run a specify query ). If you know the database will always run the query, then the JdbcQueryDetector is just extra work IMHO.

 

 

Another thing you might want to do is use OPENNMS_JDBC_HOSTNAME in the url instead of a fixed hostname.

 

<parameter key="url" value="jdbc:oracle:thin:@OPENNMS_JDBC_HOSTNAME:1521:dbsid"/>

 

By including the hostname in the url, any node can successfully run that query since it knows exactly where to connect.

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Monday, March 13, 2017 1:29 AM
To:
[hidden email]
Subject: [opennms-discuss] Help setting up monitoring Oracle databases with OpenNMS

 

I’m trying to set up monitoring of Oracle databases in Opennms, and have set up the following:

 

In collectd-conkfiguration.xml:

 

    <package name="ABBCS-Oracle">

        <filter>IPADDR != '0.0.0.0'</filter>

        <include-range begin="1.1.1.1" end="254.254.254.254"/>

        <include-range begin="::1" end="ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff"/>

 

        <service name="OracleMonitoring" interval="600000" user-defined="false" status="on">

            <parameter key="collection" value="OracleMonitoring"/>

            <parameter key="thresholding-enabled" value="true"/>

            <parameter key="driver" value="oracle.jdbc.driver.OracleDriver"/>

            <parameter key="user" value="XXXXX"/>

            <parameter key="password" value="XXXXX"/>

            <parameter key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

        </service>

    </package>

    <collector service="OracleMonitoring" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

 

In datacollection-config.xml:

 

    <snmp-collection name="ABBCS-Oracle" snmpStorageFlag="select">

        <rrd step="300">

            <rra>RRA:AVERAGE:0.5:1:8928</rra>

            <rra>RRA:AVERAGE:0.5:12:8784</rra>

            <rra>RRA:AVERAGE:0.5:480:366</rra>

            <rra>RRA:MIN:0.5:12:8784</rra>

            <rra>RRA:MAX:0.5:12:8784</rra>

        </rrd>

 

        <include-collection dataCollectionGroup="ABBCS-Oracle"/>

    </snmp-collection>

 

 

datacollection/ABBCS-Oracle.xml:

 

<?xml version="1.0"?>

<datacollection-group name="ABBCS-Oracle">

 

   <resourceType name="oracleQuota" label="Oracle Quota"

          resourceLabel="Account ${UserName} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

          <!-- class="org.opennms.netmgt.collectd.PersistAllSelectorStrategy"/> -->

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

          <!-- class="org.opennms.netmgt.dao.support.IndexStorageStrategy"/> -->

   </resourceType>

 

</datacollection-group>

 

 

 

Jdbc-datacollection-config.xml:  (This is just a test query to show I’ve got the right configuration.)

 

    <jdbc-collection name="ABBCS-Oracle">

        <rrd step="300">

                  <rra>RRA:AVERAGE:0.5:1:2016</rra>

                  <rra>RRA:AVERAGE:0.5:12:1488</rra>

                  <rra>RRA:AVERAGE:0.5:288:366</rra>

                  <rra>RRA:MAX:0.5:288:366</rra>

                  <rra>RRA:MIN:0.5:288:366</rra>

        </rrd>

      <queries>

        <query name="oracleQuota" ifType="ignore" instance-column="USERNAME" resourceType="oracleQuota">

          <statement>

            <queryString>

select

           username||'.'||tablespace_name as username,

           bytes,

           max_bytes

from

         dba_ts_quotas

where

          max_bytes > 0

            </queryString>

          </statement>

          <columns>

            <column name="USERNAME" alias="UserName" data-source-name="UserName" type="string"/>

            <column name="BYTES" alias="BytesUsed" data-source-name="BytesUsed" type="gauge"/>

            <column name="MAX_BYTES" alias="MaxBytes" data-source-name="MaxBytes" type="gauge"/>

          </columns>

        </query>

      </queries>

    </jdbc-collection>

 

 

And I’ve added the following detectors to the default foreign sources  (will probably reduce to one of these once I figure out which I need)

 

      <detector name="ABBCS-Oracle" class="org.opennms.netmgt.provision.detector.jdbc.JdbcDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="port" value="1521"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

      </detector>

      <detector name="ABBCS-Oracle_query" class="org.opennms.netmgt.provision.detector.jdbc.JdbcQueryDetector">

         <parameter key="password" value="XXXXX"/>

         <parameter key="user" value="XXXXX"/>

         <parameter key="dbDriver" value="oracle.jdbc.driver.OracleDriver"/>

         <parameter key="url" value=" jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBSID)))"/>

         <parameter key="port" value="1521"/>

      </detector>

 

 

But I’m getting the following error in the provisiond.log:

 

2017-03-13 16:05:22,799 ERROR [scanExecutor-6] o.o.n.p.s.BasicDetector: isServiceDetected: JdbcQueryDetector: Unexpected error trying to detect JdbcQueryDetector on address 10.2.3.4 port 1521

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

 

 

 

What do I need to do to enable this to work?  I’m having trouble finding any info on setting this up.  All doco about this seems to be written before capsd was deprecated?

 

I’ve had a DBA install the oracle client 12.0.1 on the opennms server,  but I’m not sure what/how/if I need to get the oracle jdbc jar file loaded into opennms

 

Would appreciate all assistance you can give.

 

Regards,

John.

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 


This e-mail message is being sent solely for use by the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by phone or reply by e-mail, delete the original message and destroy all copies. Thank you.


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Loading...