Quantcast

Monitoring multiple oracle databases on a single server

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

Monitoring multiple oracle databases on a single server

BeeJay

What is the best way to monitor multiple databases on a single server?

 

I currently have set up jdbc queries that select single values for a database, and 1 row per tablespace from that database.  This works ok for a single database on a server, as the database level data is stored at the node level, and the tablespace specific data is stored in a custom resource type, dbtableSpace.

 

However, this configuration falls down once I need to monitor multiple databases  as the “single value per databases” becomes multiple values per node, and the tablespace data, needs to be able to be linked to the database the tablespaces belong to.

 

It seems to me that I need a “database” custom resource type.  But is it possible to have sub-resource types, so that the dbTableSpace resourceType is a subtype of the dbDatabase resourceType?

 

 


                                                                                                       

 

 

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

 

 

 


------------------------------------------------------------------------------
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: Monitoring multiple oracle databases on a single server

Roskens, Ronald-2

With the current existing StorageStrategies now? I don’t think so. But… I've recently been working on a StorageStrategy that uses JEXL to build the resource's directory name, and it might be possible to use it for this. See NMS-9080.

 

You would define your collectd service with 2 extra parameters: database and tablespace.

 

      <service name="Oracle-db1-tbl1" interval="300000" user-defined="false" status="on">

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

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

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

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

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

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

        <parameter key="database" value="db1"/>

        <parameter key="tablespace" value="tbl1"/>

      </service>

 

Then in your resource type would use the JexlIndexStorageStrategy and set the index-format to the values you wanted:

 

<resourceType name="oracleIndex" label="Oracle JDBC">

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

<storageStrategy class="org.opennms.netmgt.collection.support.JexlIndexStorageStrategy">

<parameter key="index-format" value="${database}-${tablespace}}" />

</storageStrategy>

</resourceType>

 

Ron

 

From: JohnD Blackburn [mailto:[hidden email]]
Sent: Tuesday, April 04, 2017 11:05 PM
To: General OpenNMS Discussion
Subject: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

What is the best way to monitor multiple databases on a single server?

 

I currently have set up jdbc queries that select single values for a database, and 1 row per tablespace from that database.  This works ok for a single database on a server, as the database level data is stored at the node level, and the tablespace specific data is stored in a custom resource type, dbtableSpace.

 

However, this configuration falls down once I need to monitor multiple databases  as the “single value per databases” becomes multiple values per node, and the tablespace data, needs to be able to be linked to the database the tablespaces belong to.

 

It seems to me that I need a “database” custom resource type.  But is it possible to have sub-resource types, so that the dbTableSpace resourceType is a subtype of the dbDatabase resourceType?

 

 


                                                                                                       

 

 

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: Monitoring multiple oracle databases on a single server

BeeJay

Could it be done without defining 1 service per tablespace?  1 service per table space would require me to define a lot of services as I also currently need one service per SID/Port number pair…

 

I can modify my queries to return the SID with each row of data.  If I could define the data point name to contain the SID, that might get around it?

 

So something like:

 

           <query name="tablespace_PctUsed" ifType="ignore" instance-column="TableSpaceName" resourceType="dbTableSpace">

             <statement>

               <queryString> select a.name as dbname, b.value2 as TableSpaceName, b.value1 as PctUsed from zen_tab_5min b, v$database a where b.name='tablespace_%used'</queryString>

             </statement>

             <columns>

               <column name="Databasename" alias="dbDatabaseName" data-source-name="DatabaseName" type="string"/>

               <column name="TableSpaceName" alias="dbTableSpaceName" data-source-name="TableSpaceName" type="string"/>

               <column name="PctUsed" alias="dbTSpctUsed" data-source-name="PctUsed" type="gauge"/>

             </columns>

           </query>

 

 

<resourceType name="oracleIndex" label="Oracle JDBC">

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

<storageStrategy class="org.opennms.netmgt.collection.support.JexlIndexStorageStrategy">

<parameter key="index-format" value="${dbDatabaseName}-${dbTableSpaceName}}" />

</storageStrategy>

</resourceType>

 

Or I could just build an instance column that contains both the dbname and the tablespacename?

 

<queryString> select a.name||'_'||b.value2 as TableSpaceName, b.value1 as PctUsed from zen_tab_5min b, v$database a where b.name='tablespace_%used'</queryString>

             </statement>

 

John

 

 

 

 

From: Roskens, Ronald [mailto:[hidden email]]
Sent: Wednesday, 5 April 2017 11:37 PM
To: General OpenNMS Discussion <[hidden email]>
Subject: Re: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

With the current existing StorageStrategies now? I don’t think so. But… I've recently been working on a StorageStrategy that uses JEXL to build the resource's directory name, and it might be possible to use it for this. See NMS-9080.

 

You would define your collectd service with 2 extra parameters: database and tablespace.

 

      <service name="Oracle-db1-tbl1" interval="300000" user-defined="false" status="on">

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

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

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

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

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

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

        <parameter key="database" value="db1"/>

        <parameter key="tablespace" value="tbl1"/>

      </service>

 

Then in your resource type would use the JexlIndexStorageStrategy and set the index-format to the values you wanted:

 

<resourceType name="oracleIndex" label="Oracle JDBC">

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

<storageStrategy class="org.opennms.netmgt.collection.support.JexlIndexStorageStrategy">

<parameter key="index-format" value="${database}-${tablespace}}" />

</storageStrategy>

</resourceType>

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Tuesday, April 04, 2017 11:05 PM
To: General OpenNMS Discussion
Subject: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

What is the best way to monitor multiple databases on a single server?

 

I currently have set up jdbc queries that select single values for a database, and 1 row per tablespace from that database.  This works ok for a single database on a server, as the database level data is stored at the node level, and the tablespace specific data is stored in a custom resource type, dbtableSpace.

 

However, this configuration falls down once I need to monitor multiple databases  as the “single value per databases” becomes multiple values per node, and the tablespace data, needs to be able to be linked to the database the tablespaces belong to.

 

It seems to me that I need a “database” custom resource type.  But is it possible to have sub-resource types, so that the dbTableSpace resourceType is a subtype of the dbDatabase resourceType?

 

 

 


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: Monitoring multiple oracle databases on a single server

Roskens, Ronald-2

Since you can name the instance column for the query, the latter. And you can just use the IndexStorageStrategy since you don't need any special handling of the resource name.

 

From: JohnD Blackburn [mailto:[hidden email]]
Sent: Wednesday, April 05, 2017 5:58 PM
To: General OpenNMS Discussion
Subject: Re: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

Could it be done without defining 1 service per tablespace?  1 service per table space would require me to define a lot of services as I also currently need one service per SID/Port number pair…

 

I can modify my queries to return the SID with each row of data.  If I could define the data point name to contain the SID, that might get around it?

 

So something like:

 

           <query name="tablespace_PctUsed" ifType="ignore" instance-column="TableSpaceName" resourceType="dbTableSpace">

             <statement>

               <queryString> select a.name as dbname, b.value2 as TableSpaceName, b.value1 as PctUsed from zen_tab_5min b, v$database a where b.name='tablespace_%used'</queryString>

             </statement>

             <columns>

               <column name="Databasename" alias="dbDatabaseName" data-source-name="DatabaseName" type="string"/>

               <column name="TableSpaceName" alias="dbTableSpaceName" data-source-name="TableSpaceName" type="string"/>

               <column name="PctUsed" alias="dbTSpctUsed" data-source-name="PctUsed" type="gauge"/>

             </columns>

           </query>

 

 

<resourceType name="oracleIndex" label="Oracle JDBC">

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

<storageStrategy class="org.opennms.netmgt.collection.support.JexlIndexStorageStrategy">

<parameter key="index-format" value="${dbDatabaseName}-${dbTableSpaceName}}" />

</storageStrategy>

</resourceType>

 

Or I could just build an instance column that contains both the dbname and the tablespacename?

 

<queryString> select a.name||'_'||b.value2 as TableSpaceName, b.value1 as PctUsed from zen_tab_5min b, v$database a where b.name='tablespace_%used'</queryString>

             </statement>

 

John

 

 

 

 

From: Roskens, Ronald [[hidden email]]
Sent: Wednesday, 5 April 2017 11:37 PM
To: General OpenNMS Discussion <[hidden email]>
Subject: Re: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

With the current existing StorageStrategies now? I don’t think so. But… I've recently been working on a StorageStrategy that uses JEXL to build the resource's directory name, and it might be possible to use it for this. See NMS-9080.

 

You would define your collectd service with 2 extra parameters: database and tablespace.

 

      <service name="Oracle-db1-tbl1" interval="300000" user-defined="false" status="on">

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

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

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

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

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

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

        <parameter key="database" value="db1"/>

        <parameter key="tablespace" value="tbl1"/>

      </service>

 

Then in your resource type would use the JexlIndexStorageStrategy and set the index-format to the values you wanted:

 

<resourceType name="oracleIndex" label="Oracle JDBC">

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

<storageStrategy class="org.opennms.netmgt.collection.support.JexlIndexStorageStrategy">

<parameter key="index-format" value="${database}-${tablespace}}" />

</storageStrategy>

</resourceType>

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Tuesday, April 04, 2017 11:05 PM
To: General OpenNMS Discussion
Subject: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

What is the best way to monitor multiple databases on a single server?

 

I currently have set up jdbc queries that select single values for a database, and 1 row per tablespace from that database.  This works ok for a single database on a server, as the database level data is stored at the node level, and the tablespace specific data is stored in a custom resource type, dbtableSpace.

 

However, this configuration falls down once I need to monitor multiple databases  as the “single value per databases” becomes multiple values per node, and the tablespace data, needs to be able to be linked to the database the tablespaces belong to.

 

It seems to me that I need a “database” custom resource type.  But is it possible to have sub-resource types, so that the dbTableSpace resourceType is a subtype of the dbDatabase resourceType?

 

 

 


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: Monitoring multiple oracle databases on a single server

BeeJay

Thanks Ronald,

 

I’ll go with that then.

 


                                                                                                       

 

 

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: Thursday, 6 April 2017 11:26 PM
To: 'General OpenNMS Discussion' <[hidden email]>
Subject: Re: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

Since you can name the instance column for the query, the latter. And you can just use the IndexStorageStrategy since you don't need any special handling of the resource name.

 

From: JohnD Blackburn [[hidden email]]
Sent: Wednesday, April 05, 2017 5:58 PM
To: General OpenNMS Discussion
Subject: Re: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

Could it be done without defining 1 service per tablespace?  1 service per table space would require me to define a lot of services as I also currently need one service per SID/Port number pair…

 

I can modify my queries to return the SID with each row of data.  If I could define the data point name to contain the SID, that might get around it?

 

So something like:

 

           <query name="tablespace_PctUsed" ifType="ignore" instance-column="TableSpaceName" resourceType="dbTableSpace">

             <statement>

               <queryString> select a.name as dbname, b.value2 as TableSpaceName, b.value1 as PctUsed from zen_tab_5min b, v$database a where b.name='tablespace_%used'</queryString>

             </statement>

             <columns>

               <column name="Databasename" alias="dbDatabaseName" data-source-name="DatabaseName" type="string"/>

               <column name="TableSpaceName" alias="dbTableSpaceName" data-source-name="TableSpaceName" type="string"/>

               <column name="PctUsed" alias="dbTSpctUsed" data-source-name="PctUsed" type="gauge"/>

             </columns>

           </query>

 

 

<resourceType name="oracleIndex" label="Oracle JDBC">

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

<storageStrategy class="org.opennms.netmgt.collection.support.JexlIndexStorageStrategy">

<parameter key="index-format" value="${dbDatabaseName}-${dbTableSpaceName}}" />

</storageStrategy>

</resourceType>

 

Or I could just build an instance column that contains both the dbname and the tablespacename?

 

<queryString> select a.name||'_'||b.value2 as TableSpaceName, b.value1 as PctUsed from zen_tab_5min b, v$database a where b.name='tablespace_%used'</queryString>

             </statement>

 

John

 

 

 

 

From: Roskens, Ronald [[hidden email]]
Sent: Wednesday, 5 April 2017 11:37 PM
To: General OpenNMS Discussion <[hidden email]>
Subject: Re: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

With the current existing StorageStrategies now? I don’t think so. But… I've recently been working on a StorageStrategy that uses JEXL to build the resource's directory name, and it might be possible to use it for this. See NMS-9080.

 

You would define your collectd service with 2 extra parameters: database and tablespace.

 

      <service name="Oracle-db1-tbl1" interval="300000" user-defined="false" status="on">

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

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

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

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

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

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

        <parameter key="database" value="db1"/>

        <parameter key="tablespace" value="tbl1"/>

      </service>

 

Then in your resource type would use the JexlIndexStorageStrategy and set the index-format to the values you wanted:

 

<resourceType name="oracleIndex" label="Oracle JDBC">

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

<storageStrategy class="org.opennms.netmgt.collection.support.JexlIndexStorageStrategy">

<parameter key="index-format" value="${database}-${tablespace}}" />

</storageStrategy>

</resourceType>

 

Ron

 

From: JohnD Blackburn [[hidden email]]
Sent: Tuesday, April 04, 2017 11:05 PM
To: General OpenNMS Discussion
Subject: [opennms-discuss] Monitoring multiple oracle databases on a single server

 

What is the best way to monitor multiple databases on a single server?

 

I currently have set up jdbc queries that select single values for a database, and 1 row per tablespace from that database.  This works ok for a single database on a server, as the database level data is stored at the node level, and the tablespace specific data is stored in a custom resource type, dbtableSpace.

 

However, this configuration falls down once I need to monitor multiple databases  as the “single value per databases” becomes multiple values per node, and the tablespace data, needs to be able to be linked to the database the tablespaces belong to.

 

It seems to me that I need a “database” custom resource type.  But is it possible to have sub-resource types, so that the dbTableSpace resourceType is a subtype of the dbDatabase resourceType?

 

 

 


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...