checking if database is open on Unix

Hi,

Is there a way to check if a database is open and available on unix without connecting to the database. I know that in 7.3 you can check for the sgadef*.dbf file and 8i you can check for PMON. But I also foud out that sometimes, if the db is mouted in restricted mode or was shutdown improperly a "ps -ef | grep ora_pmon" command will still show PMON even though users cannot access it. Even the sgadef*.dbf file sometimes is not deleted when the instance shuts down.

What's the most accurate method of performing the db check?

Thanks in advance,
Leo

Use DB Event Triggers to create a dummy file indicating its open.

Abhay.

very easy, create some dummy/inexistent user and connect to database and look for ORA-01017 error like

Code:

---

a=`(sqlplus ksaks/kajsa|grep ORA-01017) << EOF
EOF
`

if [ "${a}" != "" ]; then
  echo Database is up"
fi

---

Quote:

---

Originally posted by pando
very easy, create some dummy/inexistent user and connect to database and look for ORA-01017 error

---

Pando I think he meant even without attempting to connect to DB.

Quote:

---

Originally posted by leonard905
Is there a way to check if a database is open and available on unix without connecting to the database

---

without connecting you only bet is look pmon

Quote:

---

Originally posted by pando
without connecting you only bet is look pmon

---

Why not with a trigger "After Startup ON Database" call some PROC where in you can output to a file some dumb info like (DB open with timestamp)& delete or rather append/flush the file with yet another dumb info like (DB Shutdown with timestamp) "Before SHUTDOWN"

Abhay.

what happens if your instance crashes, you will still have this file but no database

Ahh !! one of those rare case senarioz & ofcourse shutdown abort.


Abhay.

Checking the PMON does not help as far as telling whether the database is in restricted mode, a long running "shutdown in progress", an archival halt or an audit space freeze.

I think that any check for the status of the database will have to be more complicated than a single test.

For example, the check on the invalid user may need to be embedded in a background job so that the test can time out in a predictable manner if the database is in an archival hang. The invalid user will not work as far as detecting restricted session mode either because the password is checked before the restricted session privilege.

Perhaps a variation on the file idea but using DBMS_JOB to schedule an activity every minute (or 10 seconds or 5 seconds depending on how up to date you want to be) that uses FILE_UTL to update a file with a timestamp and the LOGINS field from V$INSTANCE... I am not sure if they would catch everything.

Hi.

Whatever method you try it won't be as accurate as actually connecting to the database, so why not just do it. Apart from all the issues listed above there are sometimes issues like the archiver hanging which mean that the DB us up and running but you can't use it etc. All the tests you try will not guarantee that a user trying to connect will be able to unless you actually test using a connection yourself.

What is the reason for not using a test connection?

Cheers

Thanks for all the post. The primary reason for not using an actual connection is simplicity and maintenance. we will have to update the password file whenever the password changes. Also, our policy is to discourage storing password info on a file.

Thanks again,
Leo

well I dont see why my approach would even need some proper user and password :D

Quote:

---

Originally posted by pando
well I dont see why my approach would even need some proper user and password :D

---

Quote:

---

the check on the invalid user may need to be embedded in a background job so that the test can time out in a predictable manner if the database is in an archival hang. The invalid user will not work as far as detecting restricted session mode either because the password is checked before the restricted session privilege.

---

well in a proper monitored database we dont only have a job to check if database is up, we have processes which check if filesystem is full, we have a job to check alert log every 5 minutes to check for errors, we have job which checks number of sessions every 30 minutes, jobs which checks on memory usage etc etc etc

if your database hangs and you dont know about it then well... very well monitored :D

also instead of grep ORA-01017 you can grep for ORA- and treat them according to the codes you want to treat just use case instead of if

the way I am suggesting has been working since years

Still this point is not addressed

"The invalid user will not work as far as detecting restricted session mode either because the password is checked before the restricted session privilege. "

As I said you can grep ORA- and treat them using case and let me ask who would open database in restricted mode? DBA right? And who's that? In my case is me and who wrote the monitoring script. Even we are in restricted mode, very rare (and you said database shutdowns abnormally is very rare, not sure if you have worked as Production DBA) You can have lots of options to treat this issue (but as I said it's very rare during daytime you would open database in restricted mode)

Quote:

---

Originally posted by pando
As I said you can grep ORA- and treat them using case

---

I dunnoo how will you be able to differentiate/interpret between same ORA- errs using CASE or whatever, when you use Junk/Non existent user trying to connect to DB in restrict mode & in OPEN mode(restrict disabled).

Ok here's simple eg

Code:

---

TEST:SYS> conn junk/junk
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
TEST:SYS> conn / as sysdba
Connected.
TEST:SYS> alter system enable restricted session;

System altered.

TEST:SYS> conn junk/junk
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
TEST:SYS> conn test/test
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

---

Ok, as you said this is rare case senario, so your method holds good in any other situation.


Abhay.

I make a pardon, grep would not work in that case.

The script is not perfect but it has been working for years with no problems.

ps -ef | grep oracle


or


ps -ef | grep lgw*

that doesnt mean you connect to the database ta all, or that it is even running in a normal way - read the rest of the thread