LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)
-   -   Check an Oracle database (https://www.linuxquestions.org/questions/linux-newbie-8/check-an-oracle-database-878704/)

brownie_cookie 05-04-2011 01:42 AM
Check an Oracle database
 
Hi all

I need to check with nagios an oracle database that is located on a server.
There are a lot of plugins that are provided, but can someone pls help me with this? the problem is that the database is located on a remote server (i have an hostaddress) and then i need to log on the database (i have username and pass)

So i only need to have a plugin that connects to that hostaddress and logs on, but i can't find any plugin which does that..?!
Can someone help me, i'm not familiar with this (database and etc.)

Greetings

EricTRA 05-04-2011 01:59 AM
Hello Björn,

I've set this up almost a year ago so might be missing some points in my explanation. As I recall it, you'll need to install the Oracle client for the version of your Oracle database server. That will provide you with the sqlplus client. Next you'll need to setup your TNS name file correctly pointing to your server/database. From then on you should be able to connect using sqlplus from the command line. If that works then you can use the plugins from NagiosExchange I believe you've already encountered :). That's the raw procedure, there's lots of information about this on the internet so happy surfing :)

Have a look at this one for example.

Kind regards,

Eric

brownie_cookie 05-04-2011 02:43 AM
So that Oracle client and sqlplus, i need to install it on my monitoring host? the database is version 10g
If not, i can't install anything on the server where the database is located on?!

But i'll sure take a look ;)

Thanks again my good friend ^^

P.S.: i'll keep this thread updated, but i don't have much time, it's kinda busy, so i'll do my best ;)

EricTRA 05-04-2011 02:47 AM
Hello,

Correct! You install the client on your monitoring host (nagios server) and don't need to install anything (if only monitoring Oracle databases) on the Oracle server. You're welcome buddy. Good luck and happy surfing!!

Kind regards,

Eric

brownie_cookie 05-04-2011 03:06 AM
okay, first encouter...

I'm following the website you've provided link

I've downloaded and unzipped the zip files, so i've got that one folder and i did also the three following commands
Code:
sudo cp instantclient_10_2/sdk/include/*.h /usr/include
sudo cp instantclient_10_2/sqlplus        /usr/bin
sudo cp instantclient_10_2/*.so*          /usr/lib
everything is fine until now...
i want to do the following command
Code:
sudo cp tnsnames.ora /etc
but i don't have a tnsnames.ora ...
i also don't have sqlplus installed for the following command
Code:
/usr/bin/sqlplus scott/tiger@myoracle
can you help me out?

EricTRA 05-04-2011 03:11 AM
Hi,

What's the output of
Code:
which sqlplus
You stated you copied the extracted files following the guide. Did you check permissions on those files? There is a sqlplus in the output you posted for the copy command, so most likely the permissions are off.

The tnsnames.ora you can copy from the Oracle server (if you have access), that's the easiest way and the best way to avoid typing errors. Also, executing the sqlplus command of course needs your credentials.

Kind regards,

Eric

brownie_cookie 05-04-2011 03:16 AM
Code:
# which sqlplus
/usr/bin/which: no sqlplus in (/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin)
how can i check that i have access to the oracle server?
I have a login and pass for it (dev user) but i can't connect to it through e.g. PuTTy

EricTRA 05-04-2011 03:22 AM
Hi,

Check if the file sqlplus exists in /usr/bin and check the permissions. If it doesn't then you did something wrong or forgot something.

You can check connectivity to the Oracle database with the tnsping command if I recall correctly, it should be part of the Oracle client package. Of course you'll need the tnsnames.ora file in order to try if I'm not mistaking.

Kind regards,

Eric

brownie_cookie 05-04-2011 03:25 AM
Code:
# ll | grep sqlplus
-rw-r--r-- 1 root  root      7741 May  4 10:02 sqlplus
Code:
# chmod 755 sqlplus
# which sqlplus
/usr/bin/sqlplus
so this is working ;) thx

but i don't know how to get that ORA file :(

EricTRA 05-04-2011 03:43 AM
Hi,

Glad you got that part solved, I was almost sure it were the permissions not set correctly. If you don't have access to the Oracle server to get the tnsnames.ora file, ask the database admin to provide it. The syntax in that file is like such:
Code:
net_service_name=
 (DESCRIPTION=
  (ADDRESS=(protocol_address_information))
  (CONNECT_DATA=
    (SERVICE_NAME=service_name)))
which points to the server : port and database instance if I recall correctly.

Kind regards,

Eric

brownie_cookie 05-04-2011 03:48 AM
okay, but that can take a while

but while i'm waiting, let's review what i should do next.
when i copied that file to /etc
i can test it like so
Code:
/usr/bin/sqlplus scott/tiger@myoracle
but i don't think scott/tiger@myoracle will work, can you tell me what those parts mean?

as i follow the site, i think when i can succesfully test that command, i can use the plugin check_oracle which is provided with the set of plugins from nagios?

EricTRA 05-04-2011 03:53 AM
Hello,

The sqlplus command uses the tnsnames.ora file to connect to the database instance in your example using the user scott with password tiger and connecting to database instance myoracle. I never tried connecting directly without tnsnames.ora but found this thread on the internet talking about the same thing. Maybe you can find out how there.

Kind regards,

Eric

brownie_cookie 05-04-2011 04:00 AM
okay, i'm trying and failing :p lol

Code:
# sqlplus user/pass@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)))(CONNECT_DATA=(SID=sid)))'
-bash: /usr/bin/sqlplus: cannot execute binary file
what does that mean?

EricTRA 05-04-2011 04:08 AM
Hi,

What version of the Oracle client did you download (32 or 64bit) and what version of OS are you running (32 or 64 bit)?

Kind regards,

Eric

brownie_cookie 05-04-2011 04:10 AM
hmmm now you say :p i think i've downloaded the wrong zip files... :doh:

link which are for 64 bit i think
and i have a 32 bit...

EricTRA 05-04-2011 04:11 AM
Quote:
Originally Posted by brownie_cookie (Post 4345982)
hmmm now you say :p i think i've downloaded the wrong zip files... :doh:

link which are for 64 bit i think
and i have a 32 bit...
Hi,

There's your problem. You can check the version of the file with:
Code:
file /usr/bin/sqlplus
if unsure.

Kind regards,

Eric

brownie_cookie 05-04-2011 04:18 AM
Code:
# file /usr/bin/sqlplus
/usr/bin/sqlplus: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), for GNU/Linux 2.4.0, not stripped

# uname -a
Linux host 2.6.18-194.32.1.el5 #1 SMP Wed Jan 5 17:53:09 EST 2011 i686 i686 i386 GNU/Linux
so, which one do i need???

EricTRA 05-04-2011 04:31 AM
Hi,

Are you using CentOS or RHEL? Post the output of /etc/redhat-release to check but I'm sure from the output you posted that you have a 32-bit kernel running and the sqlplus binary is 64-bit so you should download and install the 32 bit version. In order to avoid problems, delete what you copied before installing the new version(s).

Kind regards,

Eric

brownie_cookie 05-04-2011 04:36 AM
I have a RHEL 4 normally ...

Code:
# /etc/redhat-release
-bash: /etc/redhat-release: Permission denied
# chmod 755 /etc/redhat-release
# /etc/redhat-release
/etc/redhat-release: line 1: syntax error near unexpected token `('
/etc/redhat-release: line 1: `CentOS release 5.5 (Final)'

EricTRA 05-04-2011 04:42 AM
Hello,

No you don't. Apparently you have a
Quote:
CentOS release 5.5 (Final)
Kind regards,

Eric

brownie_cookie 05-04-2011 04:45 AM
okay then :p hahaha xD

but that doens't matter for the zip files right?

brownie_cookie 05-04-2011 04:47 AM
should i download from this page then ?

EricTRA 05-04-2011 05:07 AM
Quote:
Originally Posted by brownie_cookie (Post 4346010)
okay then :p hahaha xD

but that doens't matter for the zip files right?
Hi,

Nope, it doesn't matter, the only thing that matters is that you're running a 32-bit kernel and that you need the 32-bit version of the Oracle client. The page you link to is for 64-bit
Quote:
Instant Client Downloads
for Linux AMD64
not for 32-bit. This link is for the 32-bit versions (x86 instead of x86_64).

Kind regards,

Eric

brownie_cookie 05-04-2011 05:11 AM
okay thx !

let's try that and hope we get lucky :p

brownie_cookie 05-04-2011 05:20 AM
wow :p i think it's working xD

Code:
# sqlplus user/host@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=sid)))'

SQL*Plus: Release 11.2.0.2.0 Production on Wed May 4 12:19:28 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options


---------- Post added 05-04-11 at 12:20 PM ----------

So does this mean that i now can use the plugin check_oracle from nagios?

EricTRA 05-04-2011 05:30 AM
Hello Björn,

Nice job buddy! You are connected!!

This means indeed that you can start using the Nagios plugins whenever they refer to SQLPLUS. As a bonus, you can start writing your own plugins for Oracle too. This is a skeleton I've written some time ago (at my previous job) for just that. Use it as a base if you like.
Code:
#!/bin/sh
#
# ## Plugin for Nagios to execute SQL string against Oracle DB
# ## Written by Eric Van Steenbergen (http://www.migrar-a-linux.com/)
# ##
# ## - 2010-07-05 coded and tested for Linux
#
# ## Description:
#
# This plugin executes a specific SQL query against an Oracle DB using sqlplus.
#
# Paths to commands used in this script.  These
# may have to be modified to match your system setup.

PATH=""

find="/usr/bin/find"
xargs="/usr/bin/xargs"
tail="/usr/bin/tail"
awk="/usr/bin/awk"
cut="/usr/bin/cut"
wc="/usr/bin/wc"
grep="/bin/grep"
sqlplus="/usr/bin/sqlplus"

PROGNAME=`/usr/bin/basename $0`
PROGPATH=`echo $0 | /bin/sed -e 's,[\\/][^\\/][^\\/]*$,,'`
REVISION="Revision 1.0"
AUTHOR="Eric Van Steenbergen"

# Exit codes
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
STATE_DEPENDENT=4

print_revision() {
    echo "$REVISION $AUTHOR"
}

print_usage() {
    echo "Usage: $PROGNAME"
    echo "Usage: $PROGNAME --help"
    echo "Usage: $PROGNAME --version"
}

print_help() {
    print_revision $PROGNAME $REVISION
    echo ""
    echo "Plugin for Nagios to run SQL query against Oracle DB using sqlplus"
    echo ""
    print_usage
    echo ""
}

# DB Query:

queryresult=`$sqlplus USER/PASS@TNSNAME <<EOF
command;
EOF`

# Check result:

result="ok"
exitstatus=$STATE_OK

##### Compare with thresholds

if [ "$queryresult" = "" ]; then
        echo ""
        result="critical"
        exitstatus=$STATE_CRITICAL;
else
        echo ""
        result="ok"
        exitstatus=$STATE_OK
fi

exit $exitstatus
Substitute credentials for your own, add commands where needed. It's a base I used back then to create plugins for specific queries to Oracle.

Kind regards,

Eric

brownie_cookie 05-04-2011 05:32 AM
thanks buddy :D i don't know what i should do without you :hattip:

let's try it out

EricTRA 05-04-2011 05:36 AM
Hi,

You're welcome. Good luck!

Kind regards,

Eric

brownie_cookie 05-04-2011 05:37 AM
what does this part do?

Code:
queryresult=`$sqlplus USER/PASS@TNSNAME <<EOF
command;
EOF`
i don't know what EOF is

EricTRA 05-04-2011 05:59 AM
Quote:
Originally Posted by brownie_cookie (Post 4346057)
what does this part do?

Code:
queryresult=`$sqlplus USER/PASS@TNSNAME <<EOF
command;
EOF`
i don't know what EOF is
Hi,

The EOF part is used as termination for a HERE document. Have a look here to read about it. Basically, you put the complete sqlplus command string in one variable so that the final output gets saved in there. The command connects to the database instance with your credentials and then starts executing commnands untill encountering EOF on a single line. Replace command with the query you want to execute on the remote server, for example a select.

Kind regards,

Eric

brownie_cookie 05-04-2011 06:03 AM
thx

but i don't need to execute a command, i just need to test if a user can log on the database.
So i've got something like this

Code:
queryresult=`$sqlplus user/pass@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=sid)))'`
but it doesn't do much :p i know i'm doing something wrong..
i just want to get back one result from that command, so i can test it with an if-statement

brownie_cookie 05-04-2011 06:09 AM
Code:
queryresult=`$sqlplus user/pass@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=sid)))' << EOF
select *;
EOF`
now i come to think about it, it doesn't really matter if i do that 'select' command ;)
it works, and that's the important part :p
now it shouldn't be that hard for integrating that in Nagios

EricTRA 05-04-2011 06:14 AM
Hi,

There you go...

But if it's just to check connection, now that you know how it works without tnsnames, you could most likely download one of the Nagios plugins and change it to your needs. There are some pretty neat plugins over at NagiosExchange to get a variety of statistics, like real diskspace used and such.

Kind regards,

Eric

brownie_cookie 05-04-2011 06:17 AM
thx for the advice ^^
i'll look into that when i've got more time, now it's working in Nagios and that's what i wanted.
Now it's time to get back to my writing...
When everything is written and i've got time , i'll sure take a look !!

Thanks once more my good friend !!!!

:hattip:

EricTRA 05-04-2011 06:54 AM
Hi,

No problem whatsoever! Good to see you've got it up and running the way you needed. Thanks for marking the thread solved and for the reputation given. Have fun with Linux.

Kind regards,

Eric

brownie_cookie 05-05-2011 12:52 AM
Let me write a small HOW-TO for checking your Oracle Database without using the tnsnames.ora file

This is written thanks to the help of my good friend EricTRA.
He provided me helpfull links and helped me for getting the solution.

This being said, here is the link which guided me : Helpfull Link !!

So, I'm going to write this HOW-TO regarding the link and a plugin for Nagios and if you have an Oracle Database, this is the place you want to be.
With Nagios comes a server which we call a MONITORING SERVER. On this server Nagios is installed.
If you got that, you need to download certain zip-files to your monitoring server from the Oracle website
Link to instantclients that you'll need
But be aware, be sure what kind of OS you have (32-64 bit) because there a lot to choose from.
When you know the specific OS that you use, choose the most relevant of the link provided.

Then download the following:
  1. instantclient-basic-linux
  2. instantclient-sdk-linux
  3. instantclient-sqlplus-linux
It doesn't really matter what version you download, but preferable the newest (stable) version.

When you've downloaded that, you have to unzip them. I've selected them at the same time and the unzipped them. Eventually you need to get 1 folder, named:
instantclient_version/

If you got that folder, please do the following commands:
Code:
sudo cp instantclient_version/sdk/include/*.h /usr/include
sudo cp instantclient_version/sqlplus /usr/bin
sudo cp instantclient_version/*.so* /usr/lib
Now, if this has been successfull, it's time to test some things. You'll see in the link i've followed he copies a file. But because I didn't had access to that specific file, I had to do it on a different way. Here is how i did it.
I've used following command:
Code:
sqlplus user/pass@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=sid)))'
Explaination:
- user : the name of the user which must log on
- pass : the password of that user
- host : the hostname of the host where your database is located on
- PORT : default 1521, but can be changed if needed
- sid : the SID of your database (System ID)

When you've used this command (please check your '(' and ')' ) it should work and you probably get something like:
Code:
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 5 07:49:12 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL>
If you get something like that, you'll be fine.
Now when this is working and you want to put this in a plugin for Nagios.
My good friend EricTRA has provided me a very usefull plugin he had written and actually I only needed to change one thing

Code:
queryresult=`$sqlplus USER/PASS@TNSNAME <<EOF
command;
EOF`
TO

Code:
queryresult=`$sqlplus user/pass@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=sid)))' << EOF
select *;
EOF`
So it's not a major thing ;) when you test and run it, you should be fine !!

When you have questions about this, feel free to post me a PM ;) !!

I was glad EricTRA have helped me once more and hopefully someday this will be helpfull for someone else !!

Kind regards

Brownie

:hattip:

EricTRA 05-05-2011 01:14 AM
Hi Björn,

Thanks for the nice words and for the summary how you've got it working. You might want to copy your last post into your blog at LinuxQuestions. It'll be easier to find later on and faster to point to if needed. Great job!

Kind regards,

Eric

brownie_cookie 05-05-2011 01:51 AM
Thanks for the great idea :D

Yeah, my first blog :p


All times are GMT -5. The time now is 12:58 AM.