ZenPacks : Oracle Database Monitor : Comments

RE: Oracle Database Monitor

community@zenoss.org — Fri, 11 Mar 2011 09:34:39 GMT

Excellent work!

The folloging grants for the zenoss db user are necessary:

grant select on v_$database to zenoss;
grant select on dba_tablespaces to zenoss;
grant select on dba_data_files to zenoss;
grant select on v_$sysmetric to zenoss;
grant select on dba_free_space to zenoss;
grant select on v_$session to zenoss;
grant select on dba_objects to zenoss;
grant select on dba_segments to zenoss;

In the sizeUsed data source the sql statement is wrong. To get the value of sizeUsed in blocks it's necessary to query dba_segments and not dba_free_space.

RE: Oracle Database Monitor

community@zenoss.org — Mon, 14 Mar 2011 20:27:48 GMT

Hi hune,

thanks for pointing that out, i've fixed sql statement in my Git repository.

Egor

RE: Oracle Database Monitor

community@zenoss.org — Tue, 15 Mar 2011 07:45:01 GMT

Thanks Egor!

Would it be possible to add a threshold for the tablespace usage? I've tried to create one by myself but sadly couldn't get it to work. To get no false alarms because of tablespaces with auto-extension enabled an additional data point maxbytes is necessary:

select tablespace_name, sum(decode(autoextensible, 'YES', maxbytes, 'NO', bytes)) maxbytes from dba_data_files group by tablespace_name;

RE: Oracle Database Monitor

community@zenoss.org — Tue, 22 Mar 2011 21:17:25 GMT

Hi hune,

you can modify SQL query in 'sizeUsed' data source:

SELECT    b.BLOCKS/a.MAXBLOCKS percentUsed,
               b.BLOCKS sizeUsed,
               a.BYTES totalBytes,
               TABLESPACE_NAME
FROM
        (
                SELECT     TABLESPACE_NAME,
                                sum(BYTES) BYTES,
                                sum(decode(AUTOEXTENSIBLE, 'YES', MAXBLOCKS, 'NO', BLOCKS)) MAXBLOCKS
                FROM        dba_data_files
                GROUP BY TABLESPACE_NAME
        ) a
LEFT JOIN
        (
                SELECT     TABLESPACE_NAME,
                                sum(BLOCKS) BLOCKS
                FROM        dba_segments
                GROUP BY TABLESPACE_NAME
        ) b
USING       (TABLESPACE_NAME)
WHERE      TABLESPACE_NAME='${here/dbname}'

and then add 'percentUsed' data point and usage Min/Max threshold with Max value 0.9.

Egor

RE: Oracle Database Monitor

community@zenoss.org — Thu, 31 Mar 2011 10:04:11 GMT

Hi Egor

Could you explain how to add more than one Database instance please?

IE we have one server with two Oracle instances GEOLIVE and GEOTEST

RE: Oracle Database Monitor

community@zenoss.org — Thu, 31 Mar 2011 14:18:08 GMT

Hi Nathan,

you can add more lines to zOracleConnectStrings variable.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${dev/manageIp})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=GEOLIVE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${dev/manageIp})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=GEOTEST)))

RE: Oracle Database Monitor

community@zenoss.org — Fri, 29 Jul 2011 14:26:31 GMT

I'm guess I'm doing something wrong, but I cannot figure out.

Using the query from Mar 22, and debug logging, this is what seems to happen:

11-07-29 15:58:59,365 DEBUG zen.collector.scheduler: Task x.y.z.30 finished, result: {'SOMEPRODUCT_TSIDX_OracleTablespace_sizeUsed': [{'totalBytes': 10...00, 'sizeUsed': 51328, 'percentUsed': 51328}], 'SOMEPRODUCT_TSPOOL_IDX_OracleTablespace_sizeUsed': [{'totalBytes': 15...00, 'sizeUsed': 134656, 'percentUsed': 134656}], 'SOMEPRODUCT_SYSAUX_OracleTablespace_sizeUsed': [{'totalBytes': 24..96, 'sizeUsed': 14864, 'percentUsed': 14864}], 'SOMEPRODUCT_TSHIST_TAB_OracleTablespace_sizeUsed': [{'totalBytes': 31..00, 'sizeUsed': 383872, 'percentUsed': 383872}], 'SOMEPRODUCT_OracleSrvInst_sysmetric': [{'HostCPUUtilization': 2, 'GlobalCacheAverageCRGetTime': 0, 'DBBlockChangesPerSec': 64, 'CurrentLogonsCount': 27, 'GlobalCacheBlocksLost': 0, 'GlobalCacheBlocksCorrupted': 0, 'LogonsPerSec': 2, 'DatabaseWaitTimeRatio': 39, 'DBBlockGetsPerSec': 63, 'SharedPoolFree': 51, 'UserTransactionPerSec': 0, 'PhysicalReadsPerSec': 0, 'RedoGeneratedPerSec': 9784, 'PhysicalWritesPerSec': 0}], 'SOMEPRODUCT_TSTAB_OracleTablespace_sizeUsed': [{'totalBytes': 1572864000, 'sizeUsed': 91008, 'percentUsed': 91008}], 'SOMEPRODUCT_TSHIST_IDX_OracleTablespace_sizeUsed': [{'totalBytes': 1048576000, 'sizeUsed': 127744, 'percentUsed': 127744}], 'SOMEPRODUCT_USERS_OracleTablespace_sizeUsed': [{'totalBytes': 5242880, 'sizeUsed': 368, 'percentUsed': 368}], 'SOMEPRODUCT_OracleSrvInst_session': [{'ActiveBackgroundProcesses': 25, 'TotalBackgroundProcesses': 25, 'TotalSystemProcesses': 0, 'ActiveSystemProcesses': 0, 'TotalUserProcesses': 4, 'ActiveUserProcesses': 2}], 'SOMEPRODUCT_SYSTEM_OracleTablespace_sizeUsed': [{'totalBytes': 2147483648, 'sizeUsed': 152464, 'percentUsed': 152464}], 'SOMEPRODUCT_TSPOOL_TAB_OracleTablespace_sizeUsed': [{'totalBytes': 1887436800, 'sizeUsed': 159360, 'percentUsed': 159360}], 'SOMEPRODUCT_UNDOTBS1_OracleTablespace_sizeUsed': [{'totalBytes': 429916160, 'sizeUsed': 46944, 'percentUsed': 46944}], 'SOMEPRODUCT_OracleSrvInst_objects': [{'DbaInvalidObjects': 576}], 'SOMEPRODUCT_TSLOB_TAB_OracleTablespace_sizeUsed': [{'totalBytes': 9437184000, 'sizeUsed': 1074176, 'percentUsed': 1074176}]}

(TLDR; percentUsed returns the same value as sizeUsed. )

The exact query I'm using now: (have tried a few)

select percentUsed percentUsed,

sizeUsed sizeUsed,

totalBytes totalBytes

from (

SELECT (b.BLOCKS/a.MAXBLOCKS) percentUsed,

b.BLOCKS sizeUsed,

a.BYTES totalBytes,

TABLESPACE_NAME

FROM

(

SELECT TABLESPACE_NAME,

sum(BYTES) BYTES,

sum(decode(AUTOEXTENSIBLE, 'YES', MAXBLOCKS, 'NO', BLOCKS)) MAXBLOCKS

FROM dba_data_files

GROUP BY TABLESPACE_NAME

) a

LEFT JOIN

(

SELECT TABLESPACE_NAME,

sum(BLOCKS) BLOCKS

FROM dba_segments

GROUP BY TABLESPACE_NAME

) b

USING (TABLESPACE_NAME)

WHERE TABLESPACE_NAME='${here/dbname}')

My best guess is I'm either failing to read some of the instructions on http://community.zenoss.org/docs/DOC-5913 (SQL Data source) or I'm using incompatible versions of Oracle Monitor (1.0) and SQL Data Source (1.6).

Update: As I'm writing this the logs now show another message (which I've seen earlier today with another version of the query)

2011-07-29 16:12:21,746 DEBUG zen.zenperfsql: Successful collection from x.y.z.10 [x.y.z.10], results={'SOMEOTHERPRODUCT_SYSAUX_OracleTablespace_sizeUsed': [{'totalBytes': 18...00, 'sizeUsed': 85184, 'percentUsed': 'SYSAUX'}], 'SOMEOTHERPRODUCT_OracleSrvInst_objects': [{'DbaInvalidObjects': xy}], 'SOMEOTHERPRODUCT_SYSTEM_OracleTablespace_sizeUsed': [{'totalBytes': 18...00, 'sizeUsed': 125184, 'percentUsed': 'SYSTEM'}], 'SOMEOTHERPRODUCT_TSLOB_OracleTablespace_sizeUsed': [{'totalBytes': 107374182400, 'sizeUsed': 3860992, 'percentUsed': 'TSLOB'}], 'SOMEOTHERPRODUCT_TSSOMEOTHERPRODUCT_OracleTablespace_sizeUsed': [{'totalBytes': 41943040000, 'sizeUsed': 3149824, 'percentUsed': 'TSSOMEOTHERPRODUCT'}], 'SOMEOTHERPRODUCT_UNDOTBS2_OracleTablespace_sizeUsed': [{'totalBytes': 10485760000, 'sizeUsed': 43536, 'percentUsed': 'UNDOTBS2'}], 'SOMEOTHERPRODUCT_OracleSrvInst_sysmetric': [{'HostCPUUtilization': 2, 'GlobalCacheAverageCRGetTime': 0, 'DBBlockChangesPerSec': 205, 'CurrentLogonsCount': 56, 'GlobalCacheBlocksLost': 0, 'GlobalCacheBlocksCorrupted': 0, 'LogonsPerSec': 0, 'DatabaseWaitTimeRatio': 72, 'DBBlockGetsPerSec': 82914, 'SharedPoolFree': 23, 'UserTransactionPerSec': 3, 'PhysicalReadsPerSec': 2x, 'RedoGeneratedPerSec': 39x, 'PhysicalWritesPerSec': 27}], 'SOMEOTHERPRODUCT_USERS_OracleTablespace_sizeUsed': [{'totalBytes': 5242880, 'sizeUsed': 472, 'percentUsed': 'USERS'}], 'SOMEOTHERPRODUCT_OracleSrvInst_session': [{'ActiveBackgroundProcesses': 35, 'TotalBackgroundProcesses': 35, 'TotalSystemProcesses': 10, 'ActiveSystemProcesses': 2, 'TotalUserProcesses': 11, 'ActiveUserProcesses': 2}]}

TLDR; percentUsed is now set to the value of the TABLESPACE_NAME column

Anyone else has an idea?

RE: Oracle Database Monitor

community@zenoss.org — Fri, 05 Aug 2011 21:31:40 GMT

Hi Erik,

first, test the query from Mar 22 against your server with SQL*Net.

SQLDataSource has some art of queue optimization mechanism. It tried replace multiple SELECT queries from one table with single query.

Query for first component:
SELECT dp1, dp2
FROM table
WHERE compname="comp1"
Query for second component:
SELECT dp1, dp2
FROM table
WHERE compname="comp2"

will be automatically replaced by:

SELECT dp1, dp2, compname
FROM table

not everytime SQL query can be parsed correctly (i'm still working on this). If query can not be correctly parsed it will be sent without any modifications. So you query must be specialy prepared:

1. query must return column names = datapoint names

2. WHERE statement must be at the and of query and contains only 'AND' and '=' statements.

So your qury must looks like this:

select percentUsed percentUsed,
       sizeUsed sizeUsed,
       totalBytes totalBytes,
      TABLESPACE_NAME
from (
SELECT    (b.BLOCKS/a.MAXBLOCKS) percentUsed,
               b.BLOCKS sizeUsed,
               a.BYTES totalBytes,
               TABLESPACE_NAME
FROM
        (
                SELECT     TABLESPACE_NAME,
                                sum(BYTES) BYTES,
                                sum(decode(AUTOEXTENSIBLE, 'YES', MAXBLOCKS, 'NO', BLOCKS)) MAXBLOCKS
                FROM        dba_data_files
                GROUP BY TABLESPACE_NAME
        ) a
LEFT JOIN
        (
                SELECT     TABLESPACE_NAME,
                                sum(BLOCKS) BLOCKS
                FROM        dba_segments
                GROUP BY TABLESPACE_NAME
        ) b
USING       (TABLESPACE_NAME))
WHERE      TABLESPACE_NAME='${here/dbname}'

Egor

RE: Oracle Database Monitor

community@zenoss.org — Tue, 16 Aug 2011 19:24:26 GMT

Hi Egor,

I've gotten the zenpack to almost work. I can't see the performance graphs though. Is there any way I can troubleshoot this?

Thanks!

RE: Oracle Database Monitor

community@zenoss.org — Tue, 16 Aug 2011 19:41:13 GMT

Hi zenoR,

1. try to restart zenperfsql daemon

2. run zenperfsql as zenoss user with enabled debugging:

zenperfsql run -v10 -d yourdbserver

Egor

RE: Oracle Database Monitor

community@zenoss.org — Wed, 17 Aug 2011 16:01:11 GMT

Hi Egor,

I figured it out, the monitoring templates werent bound to the device class i had the oracle servers under. I fixed it.

However i have another problem, the interconnect times and logon rates graphs are not getting any data. Would this be a problem with the permissions i have on the Oracle side or is it something on the Zenoss end.

thanks for all your help!

RE: Oracle Database Monitor

community@zenoss.org — Wed, 17 Aug 2011 16:12:17 GMT

Hi zenoR,

you shouldn't bound any performance templates to device or device classes. Component templates (OracleSrvInst and OracleTablespace) Zenoss will bind automatically. You must add

community.sql.OracleDatabaseMap plugin to zCollectorPlugin list and remodel your server.

Egor

RE: Oracle Database Monitor

community@zenoss.org — Wed, 17 Aug 2011 16:28:06 GMT

Maybe if I explain what I was doing...

I am monitoring windows servers with your WMI pack. One of them is a DB server with oracle instances on it. I added community.sql.OracleDatabaseMap to the server but then the graphs wouldnt show up. So i went into the monitoring templates and overrode the two monitoring templates with a target of CIM/WMI. Then the graphs showed up. Was that wrong?

RE: Oracle Database Monitor

community@zenoss.org — Wed, 17 Aug 2011 18:10:21 GMT

that right.

using Net*SQL execute against your server:

SELECT metric_name,
cast(metric_id AS char(4)) PARAMETER,
VALUE from V$SYSMETRIC
WHERE group_id=2 AND metric_id in (2018, 2098)
ORDER BY metric_name

RE: Oracle Database Monitor

community@zenoss.org — Thu, 18 Aug 2011 12:52:45 GMT

Hi, Egor,

I'm testing this zenpack and it's awesome... now i'm trying to model two TNS on the same host, which is the syntax for separating the TNS list items?

RE: Oracle Database Monitor

community@zenoss.org — Thu, 18 Aug 2011 16:06:53 GMT

http://community.zenoss.org/docs/DOC-10244#comment-3749

RE: Oracle Database Monitor

community@zenoss.org — Thu, 18 Aug 2011 16:41:14 GMT

shame on me, sorry i missread it :S

thanx

RE: Oracle Database Monitor

community@zenoss.org — Mon, 26 Sep 2011 01:35:19 GMT

Im trying to install ZenPAcks.Community.RDBMS-2.1 on command line, but i got the following error:

Link to http://pypi.python.org/simple/ZenPacks.community/deviceAdvDetail/ ***BLOCKED*** by --allow -hosts

Couldn't findindex page for 'ZenPacks.community.deviceAdvDetail' (maybe misspelled?)

Link to http://pypi.python.org/simple ***BLOCKED*** by --allow-hosts

RE: Oracle Database Monitor

community@zenoss.org — Tue, 25 Oct 2011 17:37:05 GMT

Hi bigegor,

When I tried to model the deivce, I found a warning message. Is theere anything that I missed on the symbolic link?

2011-10-26 01:31:24,724 WARNING zen.PythonClient: Error in community.sql.OracleDatabaseMap: libclntsh.so.11.1: cannot open shared object file: No such file or directory

RE: Oracle Database Monitor

community@zenoss.org — Fri, 28 Oct 2011 10:26:39 GMT

Hi, and thanks for the updated query. I've been a little busy with other stuff for a while but wanted to say it seems to work brilliantly now, I just need figure out how to adjust the cycletime.

One snag I spent some time on was that whatever I wrote into "SQL Query" in the "Edit Data Source" field it didn't change the actual query that was used even if I restarted zenperfsql or even zenoss. I ended up unpacking the egg, update the objects.xml file in the package, zip it back to an egg, uninstall Oracle Monitor and install my modified version.

And if anyone here knows how to increase the cycle time, please let me know. : )

RE: Oracle Database Monitor

community@zenoss.org — Fri, 28 Oct 2011 10:39:57 GMT

Hi wilsonlai,

i had the same issue meanwhile zenoss user didn't have ORACLE_HOME and ORACLE_BASE defined... after i defined it on /etc/profile.d/oracle.sh (must be executable), it worked.

hope it helps.

bye!

RE: Oracle Database Monitor

community@zenoss.org — Mon, 31 Oct 2011 02:28:25 GMT

Hi luizz,

You are definitely right. I have some environment variables missed, after adding back, the cx_Oracles works normally.

Many thanks.

RE: Oracle Database Monitor

community@zenoss.org — Tue, 14 Feb 2012 16:25:02 GMT

Dear Egor, We are having a problem with the data displayed appearing to be reversed in the graphic beloq you can see SYSTEM is only 16% utilised.

However it is actually 84% utilised, here is a SQL query to show that

SQL> SELECT     a.BYTES-b.bytes sizeUsed,
2                 a.BYTES totalBytes,
3                 TABLESPACE_NAME
4 FROM
5          (
6                  SELECT     TABLESPACE_NAME,
7                                  sum(BYTES) BYTES
8                  FROM        dba_data_files
9                  GROUP BY TABLESPACE_NAME
        ) a
10   11 LEFT JOIN
12          (
13                  SELECT     TABLESPACE_NAME,
14                                  sum(BYTES) BYTES
15                  FROM        dba_free_space
16                  GROUP BY TABLESPACE_NAME
17          ) b
18 USING       (TABLESPACE_NAME);

SIZEUSED TOTALBYTES TABLESPACE_NAME
---------- ---------- ------------------------------
1357045760 1625292800 SYSTEM

This appears to be correct, could you explain what we need to change this? we have tried adding this SQL query to the ORACLE TABLESPACE template but it didn't seem to change the figures in the table.

Do we have to modify the .py files?

RE: Oracle Database Monitor

community@zenoss.org — Tue, 14 Feb 2012 18:52:37 GMT

Hi Nathan,

this problem already fixed in my github repository ( https://github.com/epuzanov/ZenPacks.community.OracleMon ). But you can simply replace 'dba_free_space' with 'dba_segments' in line 15.

RE: Oracle Database Monitor

community@zenoss.org — Wed, 15 Feb 2012 09:45:52 GMT

HI Egor, thank you for replying but I am still getting that issue, here is the modified cmd and the output the actaully size used is much higher, I notice its looking at BLOCKS sizeUsed should it not be BYTES?

SQL> SELECT	b.BLOCKS sizeUsed,
	a.BYTES totalBytes,
	TABLESPACE_NAME

FROM

	(
	SELECT	TABLESPACE_NAME,
	sum(BYTES) BYTES
	FROM	dba_data_files
	GROUP BY TABLESPACE_NAME
	) a

LEFT JOIN

	(
	SELECT	TABLESPACE_NAME,
	sum(BLOCKS) BLOCKS
	FROM	dba_segments
	GROUP BY TABLESPACE_NAME
	) b
USI 2	3	4	5	6	7	8	9 NG	(TABLESPACE_NAME) 10 11 12 13 14 15 16 17 18 ;

SIZEUSED TOTALBYTES TABLESPACE_NAME

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

	165654 1625292800 SYSTEM

RE: Oracle Database Monitor

community@zenoss.org — Wed, 15 Feb 2012 09:53:28 GMT

Hi, Nathan Harris, we found ourselves with the same problem, and modified the query just like follows:

SELECT NVL((ts.blocks-free.blocks),0) sizeUsed,

NVL(ts.BYTES,0) totalBytes,

NVL((ts.BYTES-free.bytes),0) usedBytes,

TABLESPACE_NAME

FROM

(

SELECT TABLESPACE_NAME,

sum(BYTES) BYTES,

sum(BLOCKS) BLOCKS

FROM dba_data_files

GROUP BY TABLESPACE_NAME

) ts

LEFT JOIN

(

SELECT TABLESPACE_NAME,

sum(BYTES) BYTES,

sum(BLOCKS) BLOCKS

FROM dba_free_space

GROUP BY TABLESPACE_NAME

) free

USING (TABLESPACE_NAME)

WHERE TABLESPACE_NAME='${here/dbname}'

This did the trick, but you have to remember to retrieve this new "usedBytes" point from the OracleTablespace sizeUsed datasource, and, of course, update the datapoints from the graph.

Ah! The NVL is for those moments when a tablespace is not online!

RE: Oracle Database Monitor

community@zenoss.org — Tue, 21 Feb 2012 14:03:03 GMT

Sadly it didn't work for us, thank you for trying though

remember the original output

SIZEUSED TOTALBYTES TABLESPACE_NAME

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

165654 1625292800 SYSTEM

We are still getting the wrong stats in the table but I notice the SQL is correct (above) if you take

SIZEUSED X 8192 (Default Block Size BYTES) = 1352067948 now that is the correct figure but the table shows 1.5GB and 255.8MB used at 16%.

It should be 1625292800/1024/1024 =1550MB or 1.5GB

Then 1352067948/1024/1024=1289MB or 1.2 GB

This makes SYSTEM tablespace 83% full

I have no idea where the 255.8MB is coming from

I am convinced that back end python is wrong, hopefull Egor can help on this one?

RE: Oracle Database Monitor

community@zenoss.org — Fri, 24 Feb 2012 16:00:06 GMT

Hi Egor, any chance you could help with this one its is important I am sure its not just us?

RE: Oracle Database Monitor

community@zenoss.org — Sun, 26 Feb 2012 13:18:58 GMT

Hi Nathan,

have you already tried to install this ZenPack from my github repository (ver:1.0.2)?

RE: Oracle Database Monitor

community@zenoss.org — Mon, 27 Feb 2012 11:21:08 GMT

Hi Egor, sorry did not realise this was different to the egg download

Had some problems with installing the Source file, so just reverted to snapshot image could I just check for this to work I should

a.remove the old OracleMon zenpack first

b.Remove the Oracleplugin from all the Oracle servers and remodel

c.Clone the Git to the zenoss home

d.zenpack --install <New Oraclemon source directory)

e.restart zenoss

f.add the plugin to each Oracle DB server amd remodel

cheers

RE: Oracle Database Monitor

community@zenoss.org — Wed, 14 Mar 2012 16:52:33 GMT

Hi Egor,

I'm searching for a way to model the diskgroups in Oracle. I'm very satisfied about the tablespace monitoring. I altered the query in the OracleDatabaseMap.py modeler script. This had the effect that after modeling the devices, besides the tablespaces, the diskgroups where visible too. But here I encountered a problem, besides the name of the diskgroup, there is no information about the diskgroup (no sizeused, %used,...). So I also altered the query in the OracleTablespace template to make sure that the diskgroup info is queried. But no effect, ... There is no generation of rrd files for the diskgroups. Altough I made sure that the selected colomns matched the defined datapoints in the template.

Do you have any suggestion for me? How can I model diskgroups the same way as you modeled the tablespaces?

Thanks in advance,

Bart

RE: Oracle Database Monitor

community@zenoss.org — Wed, 14 Mar 2012 20:04:29 GMT

Hi Bart,

check DataPoints aliases (here must be columns name in uppercase)

RE: Oracle Database Monitor

community@zenoss.org — Thu, 15 Mar 2012 08:07:16 GMT

The column names are in uppercase in the aliases (I did not alter the aliases, just the query in the oracletablespace template and the query in the modeler). Under the database tab, I can see my diskgroups but no data is provided for them.

RE: Oracle Database Monitor

community@zenoss.org — Mon, 07 May 2012 12:30:25 GMT

If I run the standard query on a device using the test button from the "edit data source" popup (from inside the monitoring templates -> oracle tablespace menu) I get this output:

Preparing Command...

exception while executing command

type: value: query is empty

DONE in 0 seconds

If I remove the last like (the where clause) I get this:

Preparing Command...

Executing query: " select percentUsed percentUsed, sizeUsed sizeUsed, totalBytes totalBytes, TABLESPACE_NAME from ( SELECT (b.BLOCKS/a.MAXBLOCKS) percentUsed, b.BLOCKS sizeUsed, a.BYTES totalBytes, TABLESPACE_NAME FROM ( SELECT TABLESPACE_NAME, sum(BYTES) BYTES, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBLOCKS, 'NO', BLOCKS)) MAXBLOCKS FROM dba_data_files GROUP BY TABLESPACE_NAME ) a LEFT JOIN ( SELECT TABLESPACE_NAME, sum(BLOCKS) BLOCKS FROM dba_segments GROUP BY TABLESPACE_NAME ) b USING (TABLESPACE_NAME))"

exception while executing command

type: value: __import__() argument 1 must be string, not list

DONE in 0 seconds

I've tested out the query using cx_oracle directly from pthon command line ad it works:

root@DEFF-SYSMON:~# cat test_cx_oracle.py

import cx_Oracle

connection=cx_Oracle.connect('system/********@PROTOCOLLO');

cursor = connection.cursor()

cursor.execute("select percentUsed percentUsed, sizeUsed sizeUsed, totalBytes totalBytes, TABLESPACE_NAME from (SELECT (b.BLOCKS/a.MAXBLOCKS) percentUsed, b.BLOCKS sizeUsed, a.BYTES totalBytes, TABLESPACE_NAME FROM (SELECT TABLESPACE_NAME, sum(BYTES) BYTES, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBLOCKS, 'NO', BLOCKS)) MAXBLOCKS FROM dba_data_files GROUP BY TABLESPACE_NAME) a LEFT JOIN (SELECT TABLESPACE_NAME, sum(BLOCKS) BLOCKS FROM dba_segments GROUP BY TABLESPACE_NAME) b USING (TABLESPACE_NAME))")

count = cursor.fetchall()[0][0]

print count

cursor.close()

connection.close()

root@DEFF-SYSMON:~# python test_cx_oracle.py

0.751953125

root@DEFF-SYSMON:~#

Well I only read out just one column but that's enough to check out that cx_oracle is working.

What else is wrong ?

Incidently: PROTOCOLLO is both the connect string in TNSNAMES.ora and the device name for the DB I want to monitor tablespace usage.

RE: Oracle Database Monitor

community@zenoss.org — Fri, 11 May 2012 15:55:02 GMT

Hi Egor,

I really appreciate the work you've done on this zenpack. It works great.

Have you thought about making it so that the zenpack also reports percent used based on the total max size available?

I'm working on this now but I'm by no means an sql expert.

This is what I use in sqlplus to find this information.

SQL> set linesize 120

SQL> SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB, ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL FROM ( SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB, 0 TOTAL_MB, 0 MAX_MB FROM dba_free_space GROUP BY tablespace_name UNION SELECT tablespace_name, 0 CURRENT_MB, SUM(bytes)/1024/1024 TOTAL_MB, SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB FROM dba_data_files GROUP BY tablespace_name) GROUP BY tablespace_name;

Regards,

Ryan

RE: Oracle Database Monitor

community@zenoss.org — Fri, 11 May 2012 16:23:18 GMT

In case anyone wonders this is what the output looks like.

RE: Oracle Database Monitor

community@zenoss.org — Sun, 13 May 2012 20:16:43 GMT

Hi Database Management Inva,

1. how 'connection string' parameter in data source looks?

2. how zOracleConnectionStrings for this server looks like?

PS. SQL Data Source 2.0 has an error in Test function, use a GIT version (2.0.70) or wait for the next release.

RE: Oracle Database Monitor

community@zenoss.org — Sun, 13 May 2012 20:27:52 GMT

Hi hero,

thanks for pointing this out, i've fogotten to add 'percentUsed' datapoint to 'sizeUsed' datasource.

RE: Oracle Database Monitor

community@zenoss.org — Mon, 14 May 2012 07:05:53 GMT

zOracleConnectionString = 'cx_Oracle','system','${here/zOraclePassword}','${here/zOracleDSN}'

zOracleDSN = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=deff-prot2)PORT=1521))(CONNECT_DATA= (SID=orcl)))

zOraclePassword = <the correct password for system>

The conenct string is taken right out of the tnsnames.ora file for the required db. For testing pourpuses we are momenterily using system so that user privileges and grants are surely not the issue.

RE: Oracle Database Monitor

community@zenoss.org — Mon, 14 May 2012 07:22:49 GMT

In the Data Source the connection string looks like this:

${here/zOracleDSN}

Incidently: does this mena that it should be gotten from the current device configuration properties entry zOracleDSN ?

RE: Oracle Database Monitor

community@zenoss.org — Mon, 14 May 2012 07:42:15 GMT

Yeah hero

if I run the query manually using sqlplus from a shell prompt that looks like the output I get but I can't get that to work from the device data source.

RE: Oracle Database Monitor

community@zenoss.org — Mon, 14 May 2012 15:35:39 GMT

Hi Egor and Database Management Inva,

Egor, I think we might have been talking about different things. I see that the percentUsed is calculated correctly but not being mapped anyplace. I added percentUsed to sizeUsed (i.e. sizeUsed.PercentUsed) and then added a threshhold MinMax of .80 with an event class of /DB. I tested this with a very low percent of .15 and it seems to work fine.

The one problem I do have is that it is for all the tablespaces and not individual ones. Any idea on how to get it to alert per tablespace?

What I was referencing in the previous post was that during the select statement you used BYTES instead of MAXBYTES. For us the current size of the database is of no value because we have autoextend for tablespaces on and multiple datafiles allocated to tables. These datafiles are set to grow up to 4GB.

I changed the sum (BYTES) to sum (MAXBYTES) which gives me a more accurate value for the percent of the database files/tablesspaces being used.

For example:

I have a tablespace with roughly 10 datafiles allocated to it that can grow to 4GB each. The datafiles haven't grown to the max size yet so they are currently only 11,000 MB being shown. The Current use is 7025 MB. This was giving me a percent used of 64%. When in reality the max size is 40,000 MB so the percent used should really be 18%. Hope my rambling makes sense. So my sql statement now looks like this:

SELECT	b.BLOCKS/a.MAXBLOCKS percentUsed,
	b.BLOCKS sizeUsed,
	a.BYTES totalBytes,
	s.STATUS status,
	TABLESPACE_NAME

FROM

	(
	SELECT	TABLESPACE_NAME,
	decode( STATUS,
	'ONLINE', 2,
	'READ ONLY', 3,
	'OFFLINE', 4,
	1) STATUS
	FROM	dba_tablespaces
	) s

LEFT JOIN

	(
	SELECT	TABLESPACE_NAME,
	sum(MAXBYTES) BYTES,
	sum(decode(AUTOEXTENSIBLE, 'YES', MAXBLOCKS, BLOCKS)) MAXBLOCKS
	FROM	dba_data_files
	GROUP BY TABLESPACE_NAME
	) a

USING (TABLESPACE_NAME)

LEFT JOIN

	(
	SELECT	TABLESPACE_NAME,
	sum(BLOCKS) BLOCKS
	FROM	dba_segments
	GROUP BY TABLESPACE_NAME
	) b

USING (TABLESPACE_NAME)

WHERE

TABLESPACE_NAME='${here/dbname}'

Ryan

RE: Oracle Database Monitor

community@zenoss.org — Mon, 14 May 2012 17:59:10 GMT

Hi Ryan,

i've understend your problem, and i'm working on solutions. Thanks for your comments.

RE: Oracle Database Monitor

community@zenoss.org — Mon, 14 May 2012 18:05:03 GMT

Hi Database Management Inva,

have you added your database manually? After modelleing with community.sql.OracleDatabaseMap plugin all your databases will be recognized and added to Zenoss automatically.

RE: Oracle Database Monitor

community@zenoss.org — Tue, 15 May 2012 09:20:29 GMT

Is it possible to get with a threshold event the component instead of a blank text?

For example events at the class Perf/Filesystem the component is C:\ Label: Serial Number xxxxx

Or is it possible to transform the event message or summary with python code?

RE: Oracle Database Monitor

community@zenoss.org — Tue, 15 May 2012 11:12:09 GMT

Hi bigegor:

Database was added manually.

There are the zenpacks we have installed:

ZenPacks.OndrejJakubcik.OracleHwMonitoring 1.1
ZenPacks.community.OracleMon 1.1
ZenPacks.community.RDBMS 2.3
ZenPacks.community.SQLDataSource 2.0
ZenPacks.community.VMwareDataSource 1.1.2
ZenPacks.community.VMwareESXiMonitor 1.2
ZenPacks.community.ZenODBC 4.0
ZenPacks.community.deviceAdvDetail 2.7
ZenPacks.community.zenHttpComponent 1.4
ZenPacks.zenoss.HttpMonitor 2.0.3

ZenPacks.zenoss.ZenossVirtualHostMonitor 2.3.6

RE: Oracle Database Monitor

community@zenoss.org — Sun, 20 May 2012 18:00:08 GMT

Hi Ryan,

try version 1.2

RE: Oracle Database Monitor

community@zenoss.org — Sun, 20 May 2012 18:03:35 GMT

Hi Database Management Inva,

add community.sql.OracleDatabaseMap plugin to modeler plugins list and remodel your server.

RE: Oracle Database Monitor

community@zenoss.org — Mon, 21 May 2012 07:43:20 GMT

Hi Bigegor,

If I look in the devices modler plugin tab I can see in the right a such line. Does that mean that it's ok ?

But still I get no tablespace usage data.

RE: Oracle Database Monitor

community@zenoss.org — Tue, 22 May 2012 01:23:56 GMT

Egor version 1.2 is working great. Nice Work.

RE: Oracle Database Monitor

community@zenoss.org — Tue, 12 Jun 2012 15:18:55 GMT

Hello! I'm experiencing problems to install cx_Oracle. I've tried with Oracle Instant client 10.2 and 11.2 versions, but I ever see the same error:

/usr/bin/ld: skipping incompatible /opt/instantclient_10_2//libclntsh.so when searching for -lclntsh

This error makes me impossible to finish cx_Oracle installation... Can someone help me?

Thank you!

RE: Oracle Database Monitor

community@zenoss.org — Tue, 12 Jun 2012 15:30:15 GMT

Ups... It was a problem with platform... I was using 64bit client and this Zenpack needs 32bit client.

Using 32bit client everything works.

Regards!

RE: Oracle Database Monitor

community@zenoss.org — Wed, 13 Jun 2012 11:17:01 GMT

Hello again,

I've successfully installed your ZenPack and its dependencies, I've configured the zProperties, Templates and Modeler plugins... but Zenoss cannot discover the Oracle databases that exist on my servers.

The Oracle-related graphs appear without information.

Exists any log to discover where is the problem?

Thank you very much!

RE: Oracle Database Monitor

community@zenoss.org — Wed, 13 Jun 2012 11:25:43 GMT

More information...

I've discovered that, when I model the device, every modeler appears on the log information... except Oracle "community.sql.OracleDatabaseMap".

Why? I suppose that this is the root cause of the problem...

Regards

RE: Oracle Database Monitor

community@zenoss.org — Tue, 03 Jul 2012 09:13:49 GMT

Hello All,

my zenoss server is running on redhat5-64bits. I am experiencing problems getting most perfromance graphs on oracle 8i and 9i database instances on solaris servers(SPAC)/HPUX. the only graph i get are sessions and odba objects. also the database instance are marked as down with no tables displayed. i used the clone git and it worked perferctly on oracle 10g running on redhat 5. I used cx_oracle-4.4 and oracle-instantclients-10.2.0.5- 64bits to ensure the zenpack would model 8i n 9i database instances. proper permission are granted by the DBA to these databases.

i got this error while modeling

2012-07-03 10:11:01,966 WARNING zen.SQLClient: Error in community.sql.OracleDatabaseMap query "SELECT TABLESPACE_NAME, a.CONTENTS, a.BLOCK_SIZE, b.BLOCKS, ( SELECT NAME FROM v$database WHERE rownum = 1 ) DATABASE FROM ( SELECT TABLESPACE_NAME, CONTENTS, BLOCK_SIZE FROM dba_tablespaces ) a INNER JOIN ( SELECT TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBLOCKS, BLOCKS)) BLOCKS FROM dba_data_files GROUP BY TABLESPACE_NAME) b USING (TABLESPACE_NAME)": ORA-00933: SQL command not properly ended 2012-07-03 10:11:01,966 INFO zen.SQLClient: SQL client finished collection for 192.168.1.1

i really need to get this working. kindly assist.

thanks

RE: Oracle Database Monitor

community@zenoss.org — Tue, 03 Jul 2012 21:24:55 GMT

Hi ade adesote,

if you can provide correct query syntax for Oracle 8i and 9i, i will fix this ZenPack.

RE: Oracle Database Monitor

community@zenoss.org — Wed, 04 Jul 2012 06:35:32 GMT

Hi ade adesote & bigegor,

I have the same problem (only graph sessions and dba objects) against Oracle 9.2.0.8 databases. But this behavior exists because Oracle 9i doesn't have statistics that Oracle 10g and 11g has.

On the other hand, I haven't errors while modeling, but zenperfsql daemon stops after working half an hour, more or less. Without errors recorded in zenperfsql.log... Someone has had this problem?

thanks!

RE: Oracle Database Monitor

community@zenoss.org — Wed, 04 Jul 2012 08:18:24 GMT

Hello Bigegor and Sistemas,

thanks for your prompt response.this is the error i got while modeling either oracle 9.2i or 8.1.7i database. hope this will help. thank you

RE: Oracle Database Monitor

community@zenoss.org — Sat, 07 Jul 2012 19:38:39 GMT

Hi ade adesote,

no this will NOT help, you have posted the same error log, but i've asked for CORRECT QUERY SYNTAX for Oracle 8 and 9.

RE: Oracle Database Monitor

community@zenoss.org — Sat, 07 Jul 2012 19:46:33 GMT

Hi Sistemas Santiveri,

yes, i'm aware of zenperfsql crashs, it looks like it conflict with zeneventlog and zenwin deamons. Try to stop they both.

RE: Oracle Database Monitor

community@zenoss.org — Thu, 12 Jul 2012 09:29:15 GMT

Hello, Sorry guy I am no database expert and I don’t have a clue of what this query should look like. I have spent days searching resources. But digging through this script --OracleDatabaseMap.py, the point where this error occurred is tasks['db_%s'%inst] = ( """SELECT TABLESPACE_NAME, a.CONTENTS, a.BLOCK_SIZE, b.BLOCKS, ( SELECT NAME FROM v$database WHERE rownum = 1 ) DATABASE FROM ( SELECT TABLESPACE_NAME, CONTENTS, BLOCK_SIZE FROM dba_tablespaces ) a INNER JOIN ( SELECT TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBLOCKS, BL OCKS)) BLOCKS FROM dba_data_files GROUP BY TABLESPACE_NAME) b USING (TABLESPACE_NAME)""", None, cs, { 'dbname':'tablespace_name', 'type':'contents', 'blockSize':'block_size', 'totalBlocks':'blocks', 'setDBSrvInst':'database', }) return tasks Permission on the database is : grant select on v_$database to zenoss; grant select on dba_tablespaces to zenoss; grant select on dba_data_files to zenoss; grant select on v_$sysmetric to zenoss; grant select on dba_free_space to zenoss; grant select on v_$session to zenoss; grant select on dba_objects to zenoss; grant select on dba_segments to zenoss

RE: Oracle Database Monitor

community@zenoss.org — Tue, 25 Sep 2012 09:56:44 GMT

Hello Egor,

I want to test Zenoss 4.2, is your zenpack compatible with this version? If not... Have you planned to create a compatible version? Soon?

Thank you very much!

RE: Oracle Database Monitor

community@zenoss.org — Tue, 25 Sep 2012 17:38:52 GMT

Hi Sistemas Santiveri,

it should work on Zenoss 4.2 too, with latest SQL Data Source and RDBMS ZenPacks

RE: Oracle Database Monitor

community@zenoss.org — Thu, 27 Sep 2012 08:32:48 GMT

Thanks bigegor! I will test it today.

Best regards

RE: Oracle Database Monitor

community@zenoss.org — Sat, 03 Nov 2012 20:47:09 GMT

Hi,

I have managed to get this working on 4.2.0 with cx_Oracle and it all works. I am monitorig 2 Oracle servers with 11 databases each. The only problem I am seeing is a lot of "ORA-12545:Connect failed because target hos or object does not exist". Sometimes it's reporting this for all the db's on a server, sometimes only for a few. Any ideas what could be causing this?

Regards, Jim

RE: Oracle Database Monitor

community@zenoss.org — Sat, 03 Nov 2012 21:03:57 GMT

Also what is the separator for zOracleTablespaceIgnoreNames? I have tried space and comma to no avail.

Thanks, Jim

RE: Oracle Database Monitor

community@zenoss.org — Sun, 04 Nov 2012 10:39:51 GMT

Hi Jim,

zOracleTablespaceIgnoreNames, like others *Ignore zProperties in Zenoss used regex syntax, so you must use | (pipe without spaces) as separator.

RE: Oracle Database Monitor

community@zenoss.org — Tue, 06 Nov 2012 20:14:38 GMT

Hi Egor,

Yes that worked, any idea what would cause the "ORA-12545:Connect failed because target hos or object does not exist" errors? I get them 30-40% of the time.

Regards, Jim

RE: Oracle Database Monitor

community@zenoss.org — Wed, 02 Jan 2013 20:33:46 GMT

In Zenoss 4.2 I'm seeing the following error...

Internal exception processing event: AttributeError("'OracleSrvInst' object has no attribute 'id'",)

RE: Oracle Database Monitor

community@zenoss.org — Wed, 02 Jan 2013 21:24:13 GMT

Hi Ryan,

try to add to OracleDatabaseMap.py after line 109:

if not getattr(om, 'id', None): continue

RE: Oracle Database Monitor

community@zenoss.org — Wed, 02 Jan 2013 21:32:06 GMT

I get the error every cycle, not during modelling. The error appears to be coming from zeneventd.

RE: Oracle Database Monitor

community@zenoss.org — Thu, 03 Jan 2013 16:06:39 GMT

I tried the suggested fix anyways even though I was pretty sure it wasn't going to work, then I remodeled the device and I'm still seeing the same error. I'm guessing it's because that ZenPack hasn't really been converted to work with 4.x yet (based on the files in the ZenPack directory). It's barely been converted to work with 3.x (no interfaces file).

RE: Oracle Database Monitor

community@zenoss.org — Thu, 03 Jan 2013 23:39:40 GMT

Hi Ryan,

RDBMS ZenPack provides all necessary files for 3.x-4.x compatibility. This ZenPack only extends DBSrvInst and Database classes. But i think, this transform make the problem: Events/Status/OracleSrvInst. Try to replace string:

if comp.id != evt.component: continue

with

if comp.titleOrId() != evt.component: continue

also check Events/Status/OracleTablespace transform.

RE: Oracle Database Monitor

community@zenoss.org — Tue, 08 Jan 2013 13:56:04 GMT

Hi,

I am trying to monitor a RAC cluster with 2 servers with this zenpack, but the zenpack only works on one server at a time. When it is collecting data from one, the other reports ORA-12545 errors. Do I just need to monitor one of the servers?

Regards, Jim

RE: Oracle Database Monitor

community@zenoss.org — Tue, 08 Jan 2013 21:35:38 GMT

Hi Jim,

check Virtual IP Zenpack, it can help by monitoring clusters resources.

RE: Oracle Database Monitor

community@zenoss.org — Wed, 09 Jan 2013 11:51:24 GMT

Hi Egor,

That Virtual IP zenpack is for 3.x.x and I am using 4.2.0. Also I have started monitoring only one of the devices in the cluster but I still get ORA-12545 errors about 40% of the time. Any idea what could be causing this?

Many thanks, Jim

RE: Oracle Database Monitor

community@zenoss.org — Mon, 04 Mar 2013 04:44:39 GMT

Hi all,

Thank you for the great plugin...

I installed Zenoss 4.2.3 on Ubuntu 12.04 64 bit with hydruid scipts and works well ..:)

until I install OracleMon plugin.

I install the plugin from github.

I have error in Component | Database when it's viewed.

The error in the log like below:

2013-03-04T11:43:25 INFO extdirect Direct request failed: cannot convert float NaN to integer: DeviceRouter.getComponents [{'sort': 'name', 'uid': '/zport/dmd/Devices/Server/Oracle Database/devices/10.1.25.27', 'keys': ['uid', 'severity', 'status', 'dbSrvInst', 'name', 'type', 'totalBytesString', 'usedBytesString', 'capacity', 'locking', 'usesMonitorAttribute', 'monitored', 'monitor', 'uuid', 'uid', 'meta_type', 'monitor'], 'start': 0, 'meta_type': 'Database', 'limit': 50, 'page': 1, 'dir': 'ASC', 'name': None}]

------

2013-03-04T11:43:25 INFO extdirect DirectRouter suppressed the following exception (Response 4230cec5-75ba-42c8-b097-e4875a47d8bd):

Traceback (most recent call last):

File "/usr/local/zenoss/Products/ZenUtils/extdirect/router.py", line 179, in _processDirectRequest

response.result = _targetfn(**data)

File "<string>", line 2, in getComponents

File "/usr/local/zenoss/Products/Zuul/decorators.py", line 115, in serviceConnectionError

return func(*args, **kwargs)

File "/usr/local/zenoss/Products/Zuul/routers/device.py", line 187, in getComponents

data = Zuul.marshal(comps, keys=keys)

File "/usr/local/zenoss/Products/Zuul/__init__.py", line 128, in marshal

marshalled_list.append(marshal(o, keys, marshallerName, objs))

File "/usr/local/zenoss/Products/Zuul/__init__.py", line 101, in marshal

return marshal(marshaller.marshal(keys),

File "/usr/local/zenoss/Products/Zuul/marshalling.py", line 79, in marshal

data = _marshalExplicitly(self._obj, _keys)

File "/usr/local/zenoss/Products/Zuul/marshalling.py", line 53, in _marshalExplicitly

value = getattr(obj, key)

File "/usr/local/zenoss/ZenPacks/ZenPacks.community.RDBMS/ZenPacks/community/RDBMS/info.py", line 60, in capacity

cap = self._object.capacity()

File "/usr/local/zenoss/ZenPacks/ZenPacks.community.RDBMS/ZenPacks/community/RDBMS/Database.py", line 207, in capacity

totalBytes = long(self.totalBytes())

ValueError: cannot convert float NaN to integer

Is there any clue to solve this??

Regards,

Fadjar Tandabawana

RE: Oracle Database Monitor

community@zenoss.org — Mon, 04 Mar 2013 17:11:20 GMT

That error will go away once data has been collected for the components.

RE: Oracle Database Monitor

community@zenoss.org — Mon, 04 Mar 2013 17:22:30 GMT

THe other components already have the result..

BTW, this component run for 1 day without have a result..

Please help.....

Regards,

Fadjar Tandabawana

RE: Oracle Database Monitor

community@zenoss.org — Mon, 04 Mar 2013 19:50:39 GMT

The only way you can fix that to make it go away is to edit the ZenPack code. In one of the .py files there will be functions that are called on to gather the RRD data. You would need to modify those to return 0 if it doesn't get a value. We're actually working on developing a whole new Oracle monitoring pack, but it's not 100% done yet (we weren't overly impressed with the performance of this one). I would also need permission from my management to release it publicly (I'll try to get permission).

RE: Oracle Database Monitor

community@zenoss.org — Wed, 20 Mar 2013 08:45:44 GMT

Ryan, do you have a date when the new & improved Oracle monitoring pack will be available?

Thanks

VicC

RE: Oracle Database Monitor

community@zenoss.org — Wed, 20 Mar 2013 14:19:25 GMT

I still have to get approval to release it in the first place so no date is currently available.

RE: Oracle Database Monitor

community@zenoss.org — Fri, 17 May 2013 08:33:17 GMT

Hi Everyone!

Please excuse my poor English. I'm a Junior Oracle DBA at a small company. I got a new task: try out zenoss oracle monitoring capabilities.

So I am totally new with zenoss, and so far I did a the following:

Install 2 Oracle Linux 6.4 64bit in Virtualbox

-There is an Oracle 11g R2 DB on one of them (default install, port: 1521, sid: orcl). With ip address: 192.168.62.159

-create zenoss Oracle DB user with the following grants:

grant select on v_$database to zenoss;

grant select on dba_tablespaces to zenoss;

grant select on dba_data_files to zenoss;

grant select on v_$sysmetric to zenoss;

grant select on dba_free_space to zenoss;

grant select on v_$session to zenoss;

grant select on dba_objects to zenoss;

grant select on dba_segments to zenoss;

-There is zenoss 4.2 on the other Oracle LInux6, with IP adress 192.168.62.126

The steps how I installed zenoss:

yum -y remove mysql-libs

yum -y install unixODBC

yum -y install SDL

yum -y install tk

yum -y install sysstat

yum -y install net-snmp-utils

yum -y install libgcj

yum -y install memcached

wget --no-check-certificate https://github.com/zenoss/core-autodeploy/tarball/4.2.3 -O auto.tar.gz

tar xvf auto.tar.gz

cd zenoss-core-autodeploy-*

./core-autodeploy.sh

After i installed the following zenpacks with zenoss linux user:

-SQL Data Source ZenPack

-Advanced Device Details ZenPack

-RDBMS Monitoring ZenPack

-Oracle Database Monitor

After that I tried to configure the Oracle DB in zenoss but I could not managed to do it. I think maybe there were 3 main problem.

1. I forgot to install the Oracle client in the first time and maybe the order is matter. I tried to installed it form here:

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html installed the following .rpm with linux root user:

-Instant Client Package - Basic:

-Instant Client Package - SQL*Plus:

-Instant Client Package - ODBC

2. I failed to install the external dependecies correctly:

- "unixODBC. Oracle ODBC driver must be installed and registered with name "OracleDB"." --> How can I do this? I also not sure I hve to install unixODBC and cx_Oracle both or just one of them?

3. I probably defined wrong the following value:

zOracleConnectionString='pyisqldb','DRIVER={OracleDB};SERVER=${192.168.62.159/dsn};UID=${zenoss/zOracleUser};PWD=

${zenoss/zOraclePassword}',ansi=True,cp_good_sql='SELECT 1 FROM DUAL'

PLease somebody can help me with this matter?

Thank you in advancek you in advance for your reply.

RE: Oracle Database Monitor

community@zenoss.org — Thu, 04 Jul 2013 09:39:12 GMT

Hi Ryan,

Is there any news about the permission?

I think I need to solve this bug...:)

Regards,

Fadjar Tandabawana

RE: Oracle Database Monitor

community@zenoss.org — Thu, 04 Jul 2013 14:36:12 GMT

To be honest I don't think it's going to happen but I'll ask again.

RE: Oracle Database Monitor

sly@mach.com — Mon, 15 Jul 2013 12:09:54 GMT

Hi Egor,

Thanks for the beautiful work done.

I have tried install installing on Zenoss version 4.2.3 and seems everything working fine, except the graph not coming.

Requesting for your guidance here.

Regds

Santosh Iyengar

RE: Oracle Database Monitor

sly@mach.com — Mon, 22 Jul 2013 08:13:55 GMT

I am facing an issue when clicked on componets->Databases,

although the number of databases is counted right when it comes to detailing of it....

it flashes an error in yello band, "ValueError: cannot convert float NaN to Integer"

can anyone help me to work around..

As per some analysis done, i get to see that we may need python 2.4 to be used for this Zenpack and it may not work with 2.5 >.... and i am using Python 2.7.

I really don't wish to disturb my Zeenoss setup here.... can anyone help me....

RE: Oracle Database Monitor

community@zenoss.org — Wed, 18 Sep 2013 11:39:46 GMT

For Oracle monitoring the tablespace error, please upgrade to 4.2.4.

If you use Ubuntu, goe to this link:

http://hydruid-blog.com/?p=343#comment-2269

The problem in the oracle database monitoring was missing and everything works well.

Regards,

Fadjar Tandabawana