How check that the Oracle Database is up?

  • For our application we have a configuration file for connecting to the Oracle database (URL, username, password).

    The application takes 2-5 minutes to start. If there is something wrong with database I see this in logs, but I have to wait 2-5 minutes. This is a lot of time.

    So I want to find out quickly and simply a way to determine that the database is all OK and online.

    Do you have any idea how I might do it ?

  • If you run the following query:

    SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;
    

    It should return:

    INSTANCE_NAME    STATUS       DATABASE_STATUS
    ---------------- ------------ -----------------
    YOUR_DBASE       OPEN         ACTIVE
    
  • ps -ef | grep pmon

    PMON (process monitor) process checks all other background processes. Then you must check alert log for further investigation.

  • First thing first: you need to know the username and password to connect to database for step 2

    1. Check the oracle process runs:

      On Un*x:

      ps -ef|grep pmon
      

      On Windows:

      tasklist|findstr /i oracle
      

      if the command in any case returns output i.e. if the pmon/oracle process is running in your environment the database is running.

    2. Go to ORACLE_HOME/bin and run:

       ./sqlplus /nolog
      

      If after login you get errors, then the database does not run:

      SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 31 21:61:61 2014
      Copyright (c) 1982, 2014, Oracle.  All rights reserved.
      Enter user-name: oracle
      Enter password: 
      ERROR:
      ORA-01034: ORACLE not available
      ORA-27101: shared memory realm does not exist
      Linux-x86_64 Error: 2: No such file or directory
      Process ID: 0
      Session ID: 0 Serial number: 0
      

    on windows start of sqlplus would be sqlplus /nolog

  • You should use/write a script that will connect to the server and see if your database is online. Here the Oracle guys will help. Just execute it from a batch/shell script (see Running Scripts From SQL Command Line) and see it's output. If everything is alright than you have your answer.

    Or just try with SQL*Plus client.

  • It might be wise to run an actual query against the database as well. This just ensures that you can both connect and query. I would make it something very simple and fast, such as:

    SELECT pk_column 
      FROM sometable
    WHERE rownum <= 1
    
  • PMON will check all the bg processes

    ps -ef|grep pmon
    

    In addition, we can check the database is running or not.

    ps -ef|grep mydb
    
  • To check general database status, I recommend:

    1. Check if database processes are running. For example, from a Unix shell, running:

      $ ps -ef | grep pmon
      

      will show the running databases, startup times, and which Oracle users started each one.

    2. Check if listeners are running using $ ps -ef | grep tns and $ lsnrctl status LISTENER

    3. Selecting gv$resource_limit will show if database reached some configured limit.

    4. To check if there were any database errors, you should check alert.log.

    For more details see my blog post.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM