?
Solved

How to log TNSPING every 15 minutes

Posted on 2014-01-22
9
Medium Priority
?
1,345 Views
Last Modified: 2014-04-10
Hi,
I have an application server which holds an webapp and it talks to Oracle database server. I want to use tnsping and log it. How can we do it? Are there any tools to do this?

Thanks,
Ravi
0
Comment
5 Comments
 
LVL 1

Accepted Solution

by:
Qwadrat4 earned 200 total points
ID: 39802173
You can use batch file on windows or shell script on unix and run with scheduler or cron:
tnsping your_ORACLE_SID >> log.txt

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39802916
The above post will work to capture the output of the tnsping.

My question is:
What are you wanting to guarantee with the tnsping?

All tnsping does is talk to the listener and see that it is listening for that SID/SERVICE.

The instance can be down and tnsping can still show OK.

If you are wanting to ensure the instance is up you will actually need to connect and select something like: select sysdate from dual;

Possible a better option is to have the database actually email you from a stored procedure.
0
 
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 200 total points
ID: 39802930
Possible a better option is to have the database actually email you from a stored procedure.
This could be done periodically via dbms_job ("old style") or scheduler.

But I'd prefer the OS version, like sqlplus with "select 1 from dual"...
0
 

Author Comment

by:Ravi Kiran Reddy Katkuri
ID: 39828557
Hi,
But the issue is Database is not in our environment and we dont have control on it. Can we use sqlplus to connect and log it to some output files. Then we can use nagios to monitor the log files.

Thanks,
Ravi
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 39828628
>>Can we use sqlplus to connect and log it to some output files.

Yes.

What OS?

If a flavor of Unix, you can use a HERE script and do everything in 1 file.  There a re a LOT of examples on the Internet about reading output from sqlplus in a shell script.

In Windows, it is common to use two files.  Once .sql script that does the select and possibly spooled output and a CMD/BAT script to run sqlplus.

Again, a lot of examples out there.

A quick and dirty:

create q.sql:
set pages 0
spool q.out
select sysdate from dual;
spool off

Open in new window


create q.bat:
sqlplus user/password @q.sql

Open in new window


The the result will be in q.out.  You just need to parse the file for the correct value.  Sorry, I don't have one of those readily available.

I prefer sysdate instead of '1' just in case you want to parse the date for the 'correct' value.  Or the CMD file could just delete the spooled file.

It is all up to you for what you want to do.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question