RE: Is listener working for every database

    I remember one similar check that I did for my databases. What I did was
I logged in to the database as sys / as sysdba and hence made sure that the
database is up. But when I tried from a client machine using tns ,it didn't
get in.it showed some error with the listener.
The reason being when you actually login using sys / as sysdba it is using
BTEQ protocol and hence bypassing the listener.
So an appropriate check would be using the default account which validates
both listener and database.

SsN

Sent from my Android phone

-----Original Message-----
*From:* Joel.Patterson@xxxxxxxxxxx [Joel.Patterson@xxxxxxxxxxx]
*Received:* 09/02/11 11:34 PM
*To:* hemantkchitale@xxxxxxxxx [hemantkchitale@xxxxxxxxx]
*CC:* oracle-l@xxxxxxxxxxxxx [oracle-l@xxxxxxxxxxxxx]
*Subject:* RE: Is listener working for every database


 Not familiar with SunCluster.

Cycling through sys as sysdba is to spool a temp file and then check it.
Not worried about audit flags, this should be temporary anyway.

The database not accepting new connections is what I was driving at, thank
you.

I didn’t intend on reading the listener.ora, partially for those reasons



So far, I like the locked account idea as simple and elegant by TJ
Kiernan.    I still have to check into it though.



Joel Patterson
Database Administrator
904 727-2546
  ------------------------------

*From:* Hemant K Chitale [mailto:hemantkchitale@xxxxxxxxx]
*Sent:* Wednesday, February 09, 2011 12:07 PM
*To:* Patterson, Joel
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Is listener working for every database





If I remember correctly, it was in SunCluster that they had (or still have)
an account that logs in to the database and queries V$SYSSTAT.  It is able
to identify that the database is "alive and active"  (e.g. by seeing that at
least some of the statistics do get incremented -- at least user logons !).



Cycling through SYS AS SYSDBA isn't a good idea.  It will also raise audit
flags.

lsnrctl status may not tell you if the database is not accepting new
connections (e.g. because the archivelog dest is full !)

reading the listener.ora is unsafe -- not every database instance may be
present and some entries may not represent a database that is still active !





Hemant K Chitale

On Thu, Feb 10, 2011 at 12:02 AM, <Joel.Patterson@xxxxxxxxxxx> wrote:

All oracle versions, solaris 9,10.  No ASM instance, so one
listener/oracle_home.



Back in the day, we had a couple scripts set up that would cycle through the
databases and log in to an account using Sqlplus.   This would tell us that
the database was up and the listener was up and working as well.   If not,
then we would be notified.



Today, there is EM to name one method, but I may have a short term
requirement to manually check again, and perhaps this time without using a
password that is plain text in any file including the script.   The script
would be executed from cron by oracle user.



Instead of creating an oracle account with only create session privilege on
every database:



I can cycle through the databases and log in as sys / as sysdba, and see
that the database is up without using a password.  I can also cycle through
the listeners using lsnrctl and I suppose check the status or something,
OR grep for a ps for the tnslsnr listener name – thus trying to circumvent
the password issue.



But does that tell me the same thing as actually logging into each database
using the listener?   Discarding the idea that a database service name could
have been ‘left out’ of the listener.ora file – because these are production
databases and that is not likely, is there a better idea?



Just checking for feedback.



Joel Patterson

Database Administrator

904 727-2546








-- 

Hemant K Chitale
http://hemantoracledba.blogspot.com

Other related posts: