Monitoring Oracle

Templates, scripts for templates, scripts and requests for templates.

Moderators: Moderators, Developers

Author
Message
User avatar
o_dupuis
Posts: 18
Joined: Fri Mar 18, 2005 12:55 pm
Location: Paris/France

Monitoring Oracle

#1 Post by o_dupuis » Thu May 12, 2005 8:22 am

Hi,

Here are the first XML Files I made to monitor our Oracle Server (8.1.7.2 under Solaris).

To be able to use those template you will have to have the following agent working on the Oracle server :

- Oracle peer Master agent
- Oracle peer encapsulator
- An standard snmp agent, net-snmp for instance.

(I might post a procedure if someone is interested)


The list of template is the following :

The first two of them are based on the infos found here :
http://www-rohan.sdsu.edu/doc/oracle/se ... 01/ch3.htm

* cacti_graph_template_application_oracle_physical_access.xml
This template gives the physical access (block/sec read and write) for each database. This is an overview of the I/O activity of the database. This is not an absolute measurement because of possible OS disk cache.

(see graph 1)

* cacti_graph_template_application_oracle_cache_hit_ratio.xml
This template gives the cache hit ratio for each database. No need to explain this one, on our server it is always above 95% because of the large amount of RAM.

(see graph 2)

* cacti_graph_template_application_oracle_filetable_tablespace_size.xml
This template gives for each TableSpace of each Database, the space used/space allocated in octets. Useful to see if the space allocated is still enough.

(see graph3)

* cacti_graph_template_application_oracle_file_table_physical_access.xml
This template gives for each File (.dbf) of each Database, the block read/sec and block write/sec. This could be useful to optimize the location of files accross the different file systems.

(see graph4)

After you import the templates, you will have 3 more Data queries available for your host :


SNMP - ORACLE - DbSysTable : For graph 1 and 2 (this query gathers infos about the Databases)

SNMP - ORACLE- TableSpace : For graph 3 (this query gathers infos about the tablespaces of the Databases)

SNMP - ORACLE - DbDataFileTable : For the Graph 4 (this query will gather infos about the dbf files)


I am still working on other monitors I will post later on.
I think those informations could still be relevant to other version of Oracle server, but the OID may (or not, I didn't check) have changed.


Feedback appreciated.


Olivier
Attachments
graph1 - Database Physical Accesses.JPG
graph 1 - Database Physical Accesses (Block/sec)
graph1 - Database Physical Accesses.JPG (75.45 KiB) Viewed 88724 times
graph2 - Database Cache Hit Ratio.JPG
graph 2 - Database Cache Hit Ratio (%)
graph2 - Database Cache Hit Ratio.JPG (62.27 KiB) Viewed 88724 times
graph3 - TableSpace size used vs allocated.JPG
graph 3 - TableSpace used vs allocated
graph3 - TableSpace size used vs allocated.JPG (40.19 KiB) Viewed 88724 times
Last edited by o_dupuis on Thu May 12, 2005 8:23 am, edited 1 time in total.

User avatar
o_dupuis
Posts: 18
Joined: Fri Mar 18, 2005 12:55 pm
Location: Paris/France

The last graph

#2 Post by o_dupuis » Thu May 12, 2005 8:23 am

Here is the graph 4.
Attachments
graph4 - dbf_File_Block_Accesses.JPG
graph 4 - dbf File Block accesses (read/write)
graph4 - dbf_File_Block_Accesses.JPG (73.72 KiB) Viewed 88726 times

User avatar
fmangeant
Cacti Guru User
Posts: 2326
Joined: Fri Sep 19, 2003 8:36 am
Location: Sophia-Antipolis, France
Contact:

#3 Post by fmangeant » Thu May 12, 2005 10:40 am

Thanks for the templates :D

Some how-to have been posted here in the past : http://forums.cacti.net/viewtopic.php?t=829 and http://forums.cacti.net/viewtopic.php?t=6209
[size=84]
[color=green]HOWTOs[/color] :
[list][*][url=http://forums.cacti.net/viewtopic.php?t=15353]Install and configure the Net-SNMP agent for Unix[/url]
[*][url=http://forums.cacti.net/viewtopic.php?t=26151]Install and configure the Net-SNMP agent for Windows[/url]
[*][url=http://forums.cacti.net/viewtopic.php?t=28175]Graph multiple servers using an SNMP proxy[/url][/list]
[color=green]Templates[/color] :
[list][*][url=http://forums.cacti.net/viewtopic.php?t=15412]Multiple CPU usage for Linux[/url]
[*][url=http://forums.cacti.net/viewtopic.php?p=125152]Memory & swap usage for Unix[/url][/list][/size]

Suzumushi
Posts: 8
Joined: Thu May 12, 2005 9:14 am

?

#4 Post by Suzumushi » Fri May 13, 2005 4:25 am

i can't see any templates to download.... :)
only jpg files

where i can download them?

best regards

User avatar
o_dupuis
Posts: 18
Joined: Fri Mar 18, 2005 12:55 pm
Location: Paris/France

Templates

#5 Post by o_dupuis » Fri May 13, 2005 8:49 am

Oops, I forgot the most important, silly me...
Attachments
cacti_graph_template_application_oracle_physical_access.xml
This template gives the physical access (block/sec read and write) for each database
(20.81 KiB) Downloaded 4481 times
cacti_graph_template_application_oracle_cache_hit_ratio.xml
This template gives the cache hit ratio for each database
(21.05 KiB) Downloaded 4155 times
cacti_graph_template_application_oracle_filetable_tablespace_size.xml
This template gives for each TableSpace of each Database, the space used/space allocated in octets.
(16.57 KiB) Downloaded 6210 times

User avatar
o_dupuis
Posts: 18
Joined: Fri Mar 18, 2005 12:55 pm
Location: Paris/France

And last but not least

#6 Post by o_dupuis » Fri May 13, 2005 8:50 am

The last template.

Thanks Suzumushi
Attachments
cacti_graph_template_application_oracle_file_table_physical_access.xml
This template gives for each File (.dbf) of each Database, the block read/sec and block write/sec
(13.32 KiB) Downloaded 4055 times

doucetp
Posts: 5
Joined: Wed May 18, 2005 4:36 am
Location: PARIS / FRANCE

#7 Post by doucetp » Wed May 18, 2005 4:40 am

Hello,

Thank's for the template, but how can i fill Index Type and Index values ? Can you explain it ? :oops:

Pierre

kevinchang
Posts: 5
Joined: Wed Jan 26, 2005 10:31 pm

#8 Post by kevinchang » Thu May 19, 2005 4:54 am

thank first.
today i try to import template of oracle xml files, there is no any error information , and i cannot see any template at cacti, In the other xml template case , i can import success,

would u give me a hand , or someone know why ? thank a lot , :o

User avatar
o_dupuis
Posts: 18
Joined: Fri Mar 18, 2005 12:55 pm
Location: Paris/France

#9 Post by o_dupuis » Thu May 19, 2005 5:13 am

Hi,


doucetp :
Could you be a bit more precise about the problem you have with the index type and Index value, I can't figure out where you need to fill this (screenshot welcome ;) ).

kevinchang:
Could you tell me which template gives you trouble, it will be easier to help you.

Olivier

naska
Posts: 1
Joined: Sun May 01, 2005 5:50 am

Please, post Data queries

#10 Post by naska » Thu May 19, 2005 5:31 am

Hi!

Can you post de Data queries for Oracle templates¿¿

Thanks.
:lol:

User avatar
o_dupuis
Posts: 18
Joined: Fri Mar 18, 2005 12:55 pm
Location: Paris/France

Thanks naska

#11 Post by o_dupuis » Thu May 19, 2005 8:02 am

Yes, of course without the data queries, it won't work :D. Sorry I thought it was exported via the Export command.

Here they are :

1st :
oracle_DataFileTable.xml
associated with graph : Application - Oracle - File Table - Physical Access

2nd :
oracle_Table.xml
associated with graph :
Application - Oracle - Block Get Rate
Application - Oracle - Block Visit per transaction
Application - Oracle - Cache Hit Ratio
Application - Oracle - Physical Access
Application - Oracle - Redo Logs Wait Ratio

3rd :
oracle_TableSpaceTable.xml
associated with graph :
Application - Oracle - TableSpace Size



Sorry, it must have been confusing for people who tried to use the graph templates.
Attachments
oracle_DataFileTable.xml
(3.21 KiB) Downloaded 3351 times
oracle_Table.xml
(4.65 KiB) Downloaded 3639 times
oracle_TableSpaceTable.xml
(2.68 KiB) Downloaded 4088 times

proxyo
Posts: 11
Joined: Wed Apr 20, 2005 9:07 am
Location: France

#12 Post by proxyo » Thu May 19, 2005 8:32 am

I have any question with your template...

first, can you post the procedure you asked in your first post ? :roll:
installation of the oracle software, and configuration frighten me a little... :(

and are these template can run under windows???? :roll:


thanks in advance....

ProxyO

User avatar
o_dupuis
Posts: 18
Joined: Fri Mar 18, 2005 12:55 pm
Location: Paris/France

#13 Post by o_dupuis » Thu May 19, 2005 9:01 am

Hi,

I have no time at the moment to make a procedure (sorry), the links fmangeant posted are very well documented :

http://forums.cacti.net/viewtopic.php?t=829
http://forums.cacti.net/viewtopic.php?t=6209

I think you will find all the infos to make oracle agent work.

About working under Win32, these templates are based on SNMP, not scripts run by the "cacti server" so it should work without problem.

Olivier

doucetp
Posts: 5
Joined: Wed May 18, 2005 4:36 am
Location: PARIS / FRANCE

#14 Post by doucetp » Fri May 20, 2005 2:22 am

Thank's olivier,


Now with Data Queries, all info are being graphed except "Cache It Ratio".
I think something is missing for this graph .

Bye
Pierre
Attachments
gkrellShoot_05-20-05_092514.jpg
gkrellShoot_05-20-05_092514.jpg (32.75 KiB) Viewed 88287 times

User avatar
o_dupuis
Posts: 18
Joined: Fri Mar 18, 2005 12:55 pm
Location: Paris/France

#15 Post by o_dupuis » Fri May 20, 2005 4:38 am

Hi Pierre,

Do you use the Create->New Graph menu on the left handside of the screen ? If not, have a try. This Cache Hit Ratio template works exactly the same way the others do, so if you get "Application - Oracle - Block Get Rate", or "Application - Oracle - Physical Access" working, you 'should' also get the cache hit ratio.


Olivier

Post Reply