Rrdora - rbogusze/oracleinfrastructure GitHub Wiki
For a long time (since at least version 8) the Oracle RDBMS has provided the waits interface that allowed an administrator to actually see where the time is spend during the query execution.
It is strange that we had to wait till version 10g for a usefull GUI interface for those waits. I started to use a nice, simple project called rrdora - http://members.tele2.nl/r.vermeer/rrdora/ when Oracle 9i was rulling and althou the performance view on 10g is much more than that (lets you split the waits by the users). This certainly is not full blown wait interface provided by Diagnostic Pack (requires a separate license on top of EE) that is available from Grid Control but is an alternative in case you have no other choice - do not want to buy this Diagnostic Pack license or if you use a Standard Edition in which such wait interface is not available.
I modified a bit the rrdora:
- added the tps statistic (transactions per second)
- connected it to LDAP through intermediate script
- added new page with selected statistics from all databases on one page
I tried to contact the author but and discuss my additions, but received no response. Due to the GNU GPL license I provide the source code with my additions.
Please note, it is assumed that the databases beeing monitored have a user that is able to connect to database and select instance statistics. For the sake of simplicity and previous experiences I use the user perfstat available after installing statspack. Althou superseeded in 10g by AWR reports can still be used simultaniusly.
To install statspack do (in examples below I use password: perfstat):
$ sqlplus / as sysdba > @?/rdbms/admin/spcreate
Download the rrdora code
$ cd $ svn checkout https://github.com/rbogusze/oracleinfrastructure/trunk/oi_rrdora $ mkdir oi_rrdora/logs
Install the http server
# yum install httpd # chkconfig httpd on # service httpd start # yum install rrdtool # mkdir /var/www/html/rrdora # chown orainf:orainf /var/www/html/rrdora
The provided rrdora.dblist contains a static TEST2 entry, lets try to run with that for to see if rrdora works in its simple configuration.
To populate the round-robin database that stores the waits statistics run:
$ ./rrdora.rrd.sh rrdora.dblist
Lets run the statistics gathering
$ ./rrdora.get.sh rrdora.dblist
Lets generate the images for web page
$ ./rrdora.img.sh rrdora.dblist
Wait 5min and repeat the statistics images gathering.
Note: each of the executables: rrdora.get.sh, rrdora.img.sh, rrdora.rrd.sh has by default logging to oi\_rrdora/logs enabled. They can generate substantial amount of data. If you make sure that everything is fine turn the off. This can be done by editing each script.
OK, now we should see a nice page uder http://logwatch/rrdora/ - with scope for one database, and under http://logwatch/rrdora/index2.html - with scope for one statistic through all databases
So far we have only one database, so this may seem not usefull, but with dozens of databases to manage a fast first look at statistics here presented can give you and impression of where the problem lies and quickly point to right further investigation.
Now we will add special LDAP attributes that will determine whether that certain database should be monitored using rrdora. Next step will be a generation of now static rrdora.dblist file that determines what databases are monitored. This will be done based on LDAP configuration.
Adding rrdora specific LDAP attributes. This is why I like LDAP, I can add attributes that are connected to database and that allows me to control utilities that talk to databases.
| orainfDbRrdoraMonitoring = | TRUE/FALSE | determines whether we consider that DB for rrdora monitoring |
| orainfDbRrdoraUser = | <USERNAME> | user used to connect to database |
$ ldapvi -D "cn=Manager,dc=orainf,dc=com" --host logwatch --discover Add two attributes to cn=TEST2,cn=OracleContext,dc=orainf,dc=com: orainfDbRrdoraMonitoring: TRUE orainfDbRrdoraUser: perfstat
Now we need to be able to generate dynamically the rrdora.dblist based on the info from LDAP.
To be able to ask the LDAP from bash I created a wrapper which contains the connection information to LDAP server. That way when the location changes I have change only one file.
Install required perl module
# yum install perl-LDAP
Ask LDAP using a perl script.
$ $HOME/scripto/perl/ask_ldap.pl "orainfDbRrdoraMonitoring=TRUE" "['cn', 'orainfDbRrdoraUser']" TEST2 perfstat
The above command should return one row for now. See that we filter the LDAP entries for having orainfDbRrdoraMonitoring=TRUE and want to display the values of ‘cn’, ‘orainfDbRrdoraUser’ attributes. That is the beauty of LDAP.
Now lets combine it all to a script that will output expected format for rrdora.dblist and put into crontab
$ echo "V_PASS=perfstat" >> $HOME/.credentials
$ crontab -e
#
# RRDORA - monitoring waitow bazodanowych
#
*/5 * * * * . ${HOME}/.bash_profile; cd ${HOME}/oi_rrdora; ./prepare_rrdora.dblist.sh > rrdora.dblist; ./rrdora.rrd.sh rrdora.dblist; ./rrdora.get.sh rrdora.dblist; ./rrdora.img.sh rrdora.dblist
As you see we provide a hardcoded password, for now lets assume we can afford that. We will deal with that later.
As a result we have the statistics that are refresh every 5 min, and everytime we add a database to LDAP and set the required attribute the database will be monitored by rrdora.
To see its power lets assume that we have created new database. The easy way to add a new database to LDAP is to copy the current entry and modify the required attributes.
$ ldapsearch -x -b 'dc=orainf,dc=com' '(cn=TEST2)' > new.ldif
Modify requred attributes, delete the info messages and add new record to LDAP. Let us assume it called ZEBRA.
$ vim new.ldif $ ldapadd -x -D "cn=Manager,dc=orainf,dc=com" -W -f new.ldif
Make sure you can establish the connection
$ tnsping ZEBRA $ sqlplus perfstat@ZEBRA
After 10min the new database should be available to chose from the pop-up menu at our rrdora web site = http://logwatch/rrdora/
Done.
Extra storage of the day view images.
# mkdir /var/www/html/rrdora_history # chown orainf:orainf /var/www/html/rrdora_history
And add to orainf crontab
$ crontab -e
12 18 * * * . ${HOME}/.bash_profile; cd ${HOME}/oi_rrdora; ./rrdora_history.sh > /dev/null
During image generation by rrdtool.
rrdtool graph (…) –unit 1 but this option is not present in current rrdtool - http://oss.oetiker.ch/rrdtool/doc/rrdgraph.en.html removing it
Althou the apache log says that the image is properly ‘get’ the browser shows nothing. What is weird is that picture properties show: naturalHeight: 0 naturalWidth: 0 but in reality it is for example width=’498’ height=’285’.
After a long and painful research it turned out that this behaviour happens only on Windows OS and when the Widows Firewall is enabled.
I do not know how to make some kind of exception, but switching the windows firewall off helps :)