Another way to monitor Oracle

[dalle]

Hi to everyone, i've wrote within a workmate a plugin for zabbix to monitor oracle instances.
This script work on serverside and don't use zabbix send, but simply the zabbix server run some script and collect data from oracle instances.
This plugin is server-centric because for me is more simple manage and upgrade a single application.
The work that is done with zabora is simply great,and i've really apreciate! this is just another way to do more or less the same work.
requirements:

• a working sqlplus on zabbix server with configured tnsnames, and tnsping utility too. I've installed a complete client and not simply the instantclient, because tnsping is not included inside instant-client.
• an user on every oracle instances

installation steps
create an user with this oracle script:
CREATE USER ZABBIX
IDENTIFIED BY <REPLACE WITH PASSWORD>
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for ZABBIX
GRANT CONNECT TO ZABBIX;
GRANT RESOURCE TO ZABBIX;
ALTER USER ZABBIX DEFAULT ROLE ALL;
-- 5 System Privileges for ZABBIX
GRANT SELECT ANY TABLE TO ZABBIX;
GRANT CREATE SESSION TO ZABBIX;
GRANT SELECT ANY DICTIONARY TO ZABBIX;

untar in /etc/zabbix/externalscripts check_ora.tar.gz
write your credentials /username/password and instance etc..) inside
/etc/zabbix/externalscripts/check_ora/credentials
create items like on attachment check_ora_items.txt or import my template.
then the last thing to do is add an host with the same name of SID.
an you'll have a lot of graphs about performaces etc..
in the picture you'll see some graphs

See ya =)
Dalle

[dalle]

just another screenshot =)

[bottie]

graph question

Hi Dalle,

thanks for the script.
could you please explain how to get the hit ratio graph in kb/s - Mb/s just like in your example?
-----
don't matter just read the doc and think it solved... thanks

[dagun]

Hi dalle!
I don't understand the "write your credentials /username/password and instance etc..) inside
/etc/zabbix/externalscripts/check_ora/credentials"
Can you explaint it for me ?Thanks.

[dalle]

Hi dalle!
I don't understand the "write your credentials /username/password and instance etc..) inside
/etc/zabbix/externalscripts/check_ora/credentials"
Can you explaint it for me ?Thanks.

sure
to connect to a database you need spacify username, password and instance name.
e.g.
sqlplus username/[email protected]

databaseA.example.com is defined inside your tnsnames.ora e.g.
tnsnames.ora:
databaseA.example.com=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.0.1)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=databaseA)
)
)

obviously on HOST you need spcify your ip address and on SERVICE_NAME your instance name.

and so the credentials file is going to contain:
databaseA;zabbix/[email protected]

databaseA is the name of your monitored host and the name of database.
I use the name of instance as zabbix host so you can have moultiple instance of oracle on the same server.
for any trouble ask to me.
see ya
Dalle

[dagun]

Help me!

When I use the oracle client to connect the oracle server , I get the error.
My oracle server is on the window.the oracle service_nameracle
My oracle client is on the linux.When I excute"sqlplus hrsoft/hrsoft@szg"
the fllow problem is:
ORA-12504:TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
the"szg" is window's host name.ip:192.168.2.124

My tnsnames.ora configuration is:
szg=(DESCRIPTION)=
(PROTOCOL=tcp)
(HOST=192.168.2.124)
(PORT=1521)
(CONNECT_DATA=
(SID=oracle)
(GLOBAL_NAME=oracle)
)
I will ask GLOBAL_NAME equal SERVICE_NAME?
Thanks.

[dagun]

Ok

Hi dalle.
I have resolve this problem.
You do a good job.Thank you.

[dalle]

New release

Hi to everyone,
i wanna pubblish a new release of check_ora, this version is slightly different from the previous because now zabbix server don't execute more the "chechk_ora.sh" but now check_ora.sh send trap to zabbix server so everything is asincronous and the server don't have more trouble with queue if you have a lot of database.
In this release you'll need to schedule on crontab
*/5 * * * * /etc/zabbix/externalscripts/check_ora_cron.sh
Then import the new template where every item is converted to "ZABBIX trapper"
and extract the two files "check_ora_sendtrap.sh" ancd "check_ora_cron.sh" and place them on "/etc/zabbix/externalscripts"
Everything is contained on the zip file in attach.
See yaaa
Dalle

[vjunior1981]

CFGFILE=&quot;/etc/zabbix/externalscripts/globalcfg&quot;

First of all, sorry for my pour english...

What I need in file CFGFILE="/etc/zabbix/externalscripts/globalcfg"?
This is a file or a directory?
When i execute the script ./check_ora_sendtrap.sh I have this:

./check_ora_sendtrap.sh: line 46: source: /etc/zabbix/externalscripts/globalcfg: is a directory
./check_ora_sendtrap.sh: line 184: /home/zabbix/.bash_profile: No such file or directory

Thansk for any help...

[dalle]

First of all, sorry for my pour english...

What I need in file CFGFILE="/etc/zabbix/externalscripts/globalcfg"?
This is a file or a directory?
When i execute the script ./check_ora_sendtrap.sh I have this:

./check_ora_sendtrap.sh: line 46: source: /etc/zabbix/externalscripts/globalcfg: is a directory
./check_ora_sendtrap.sh: line 184: /home/zabbix/.bash_profile: No such file or directory

Thansk for any help...

Don't worry about your english is not worse than mine
globalcfg is a configuration file imported by the script and contains

cat /etc/zabbix/externalscripts/globalcfg

ZBX_SENDER="/usr/local/sbin/zabbix_sender"
ZBX_SERVER="10.10.64.93"
ZBX_PORT="10051"


so you can write your file if is not present and put inside:
ZBX_SENDER="<LOCATION OF ZABBIX SEND>/zabbix_sender"
ZBX_SERVER="<IP_ZABBIX_SERVER>"
ZBX_PORT="<ZABBIX_PORT>"
See ya

[vjunior1981]

Thanks for help...

Again, sorry for my pour english...
I just have this now, think something is missing...

canes2:/etc/zabbix/externalscripts # ./check_ora_sendtrap.sh -i comrtst
./check_ora_sendtrap.sh: line 184: /home/zabbix/.bash_profile: No such file or directory

Can you help me?

Thanks for any help!

[dalle]

Again, sorry for my pour english...
I just have this now, think something is missing...

canes2:/etc/zabbix/externalscripts # ./check_ora_sendtrap.sh -i comrtst
./check_ora_sendtrap.sh: line 184: /home/zabbix/.bash_profile: No such file or directory

Can you help me?

Thanks for any help!

sure no ptoblem, i'm used to put there environment etc... on mine there is:

##FROM HERE:
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
##UNTIL HERE IS STANDARD
## FROM HERE THERE IS THE CONFIGURATION FOR SQLPLUS/ORACLE CLIENT
export ORACLE_BASE=/usr/local/oracle/10.2.0.4
export ORACLE_HOME=$ORACLE_BASE
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=/lib/:/usr/lib:$ORACLE_BASE/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin

export PATH
unset USERNAME

let me know if work
see ya

[vjunior1981]

I'm almo there... again...

Thanks for the help.
Now, if i execute this, it works!!! \o/

zabbix:/etc/zabbix/externalscripts # ./check_ora_sendtrap.sh -i comrtst -q pool_free_mem
110.03

Buuuut...

Some noob doubt's:

I have to run this scripts in the zabbix machine, or in all the machines I want to monitor?

When I run the file:
canes2:/etc/zabbix/externalscripts # ./check_ora_cron.sh
Sending failed. Use option -vv for more detailed output.
/usr/local/sbin/zabbix_sender -z 10.10.100.139 -p 10051 -s comrtst -k audit -o

If I don't put the -r parameter it works fine for me, but I don't get any information in the zabbix screen, just this:

Free memory
Processor load
Processor load15
Processor load5
Total memory

The -r parameter it's the parameter to send to the zabbix screen the information?

Thanks again!

[vjunior1981]

Uouuu!!!

Man... I think I have some kind of problem.

Sending the sessions I have in database. They all doing the same thing.

[dalle]

Man... I think I have some kind of problem.

Sending the sessions I have in database. They all doing the same thing.

try to execute
sh -x /etc/zabbix/externalscripts/check_ora_cron.sh
from a shell so you are going to see some extra information.
i'm sorry but this morning is an hell in office and i can't help you soon