Solved

How to log TNSPING every 15 minutes

Posted on 2014-01-22
9
1,124 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 1

Accepted Solution

by:
Qwadrat4 earned 50 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 77

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 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 50 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

738 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