checking if database is open on Unix
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: checking if database is open on Unix

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227

    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
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Use DB Event Triggers to create a dummy file indicating its open.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
    Last edited by pando; 09-08-2003 at 12:06 PM.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    Originally posted by leonard905
    Is there a way to check if a database is open and available on unix without connecting to the database
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    without connecting you only bet is look pmon

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    Last edited by abhaysk; 09-08-2003 at 12:26 PM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    what happens if your instance crashes, you will still have this file but no database

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Ahh !! one of those rare case senarioz & ofcourse shutdown abort.


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Aug 2003
    Location
    Dhahran
    Posts
    33
    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.

  10. #10
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width