23

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 ?

Mark
  • 103
  • 4
Eugene
  • 231
  • 1
  • 2
  • 3

7 Answers7

31

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
Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
Solego
  • 311
  • 3
  • 2
13

ps -ef | grep pmon

PMON (process monitor) process checks all other background processes. Then you must check database's alert_<SID>.log for further investigation.

check in different operating systems and gets os user.

  SCRIPT=${0##*/}

sid="ORCL"
  PMON_PROCESS="ora_pmon_${sid}"


echo "Definition Oracle Pmon_Prozess:  ${PMON_PROCESS}"


pid=$( UNIX95=TRUE ps -eo pid,args | awk '( ($NF == "'${PMON_PROCESS}'" ) && ($1 != mypid) ){ print $1 }' mypid=$$ )


if [ "X${pid}" = "X" ]; then
    echo "${SCRIPT}: Database &quot;${v_sid}&quot; (pid=${pid}) not started."
    exit 1
  else
    ORA_RUN_RELEASE=$( UNIX95=TRUE ps -eo pid,user,args | awk '( ($NF == "'${PMON_PROCESS}'" ) && ($1 != mypid) ){ print $2 }' mypid=$$ )
  fi

Or Python version (inspired by Tanel Poder's findhomes.sh)

#!/usr/bin/python3

import os
import glob


Emulate trick form tanelpoder


https://tanelpoder.com/2011/02/28/finding-oracle-homes-with/




printf "%6s %-20s %-80s\n" "PID" "NAME" "ORACLE_HOME"


pgrep -lf pmon |


while read pid pname  y ; do


printf "%6s %-20s %-80s\n" $pid $pname ls -l /proc/$pid/exe | awk -F'&gt;' '{ print $2 }' | sed 's/bin\/oracle$//' | sort | uniq


done




It s basically looking up all PMON process IDs and then using /proc/PID/exe link to find out where is the oracle binary of a running process located




for cmd_line_file in glob.glob('/proc/[0-9]*/cmdline'):
    try:
        with open(cmd_line_file) as x:
            cmd_line = x.read().rstrip("\x00")
            if not cmd_line.startswith('ora_pmon_'):
                continue
            , _, SID = cmd_line.split('')
            #
            piddir = os.path.dirname(cmd_line_file)
            exefile = os.path.join(piddir, 'exe')
            inode = os.stat(exefile)
            #
            if not os.path.islink(exefile):
                continue
            oraclefile = os.readlink(exefile)
            ORACLE_HOME = os.path.dirname(oraclefile)
            ORACLE_HOME = os.path.dirname(ORACLE_HOME)
            print("{sid:20s} {home}".format(sid=SID, home=ORACLE_HOME))
    except FileNotFoundError:
        #print("Missing file ignored: {}".format(cmd_line_file)) # process exited quickly
        pass
ibre5041
  • 1,564
  • 8
  • 14
  • it seems there was an easier solution : https://serverfault.com/questions/38488/how-can-i-tell-if-an-oracle-database-is-mounted-and-activated – Nico Feb 23 '23 at 21:38
6

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
Paul White
  • 83,961
  • 28
  • 402
  • 634
coz
  • 161
  • 1
  • 2
3

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.

Marian
  • 15,531
  • 2
  • 60
  • 74
0

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
atokpas
  • 8,634
  • 1
  • 16
  • 27
user3818965
  • 1
0

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
user134623
  • 11
0

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
Richard
  • 6,393
  • 8
  • 42
  • 62