2

Last day, I was at one training for Oracle installation and service start & shutdown. There I came to know about some Linux commands to start and shutdown an Oracle instance on a Linux machine.

To identify the instance running : The command below will return all Oracle instances which are currently running on that machine.

ps -ef | grep pmon

This command returns following results:

oracle    823     1  0 Dec03 ?        00:00:17 ora_pmon_instance1
oracle  19632     1  0 Nov17 ?        00:06:35 ora_pmon_instance2
oracle  24199     1  0 Nov20 ?        00:05:23 ora_pmon_instance3

Where instance1, instance2 and instance3 are 3 installations of Oracle on Linux, which are currently running.

My query: So, ps -ef will only show processes currently running. Suppose, instance3 is down and you need to start that instance. But, you don't know how many Oracle instances there are on the machine.

How would you get to know that this instance is down?

In Windows, there is a way called services, where you could came to know that these installations are done on Windows for Oracle.

  • 1
    There is no guaranteed method. That's something that should be documented. You can look for indicators like entries in /etc/oratab but that is no guarantee if someone has really fouled up a server. Just as in Windows, it is possible to not have a service though that wouldn't generally be sensible. – Justin Cave Dec 4 '15 at 5:50
  • Yes, i have tried the way to grep information from file system like /etc/oratab – Rohit Batta Dec 4 '15 at 5:57
  • And that didn't work for some reason? As I said, if you're assuming that someone in the past did installs in a non-standard method, didn't document anything, and the instance isn't running any longer, then they've done an excellent job of hiding it. You can search for things that look like parameter files and spfiles or data files but that likely requires human intervention to determine if they are "real". That's true regardless of OS. – Justin Cave Dec 4 '15 at 6:03
  • @JustinCave : Yes, i have discussed it with dba at my side. He told the same thing, that, if you are installing anything, then you should document it for future reference. Way is only to analyze the folder structure and find out installations – Rohit Batta Dec 4 '15 at 6:25
2

There's no fool-proof way, but here's a list of ideas for you:

/etc/oratab:

Little script to give you a list of SIDs in the oratab:

cat /etc/oratab | grep -v '^#\|^\s*$' | cut -d: -f 1

$ORACLE_HOME/dbs

You can adapt the above script to look in all $ORACLE_HOMEs listed in the oratab, and search for initSID.ora and spfiles for any instances:

for ORACLEHOME in `cat oratab | grep -v '^#\|^\s*$' | cut -d: -f 2 |uniq`
do 
  ls -1 $ORACLEHOME/init*.ora | sed -n 's/init\(.*\).ora/\1/p'
  ls -1 $ORACLEHOME/spfile*.ora | sed -n 's/spfile\(.*\).ora/\1/p'
done

tnsnames.ora / listener.ora

Another option is to look in each $ORACLE_HOME/network/admin and analyse the tnsnames.ora and listener.ora files to see which instances have been configured.

Already Running Instances

You've already covered this with your pmon search. You can also use lsnrctl status to see which instances the listener is currently servicing requests for.

  • Thanks @Phil , So far i found this answer is more relevant. thanks for such nice explanation – Rohit Batta Dec 14 '15 at 5:17
1

IF Oracle restart is in place, as is often the case when RAC and/or ASM is in use, you can use:

crsctl stat res -t -w "TYPE = ora.database.type"|awk '/^ora./ {l=$0;} !/^ora./ { if ( l > "" ) l=l " " $0; print l;l="";}'|grep  ${HOSTNAME%%.*}

to list the databases that are defined on the current host. This comes very close to the Windows services. You could pipe this into the next lines to filter out the database name and request that status of the database, where the instances are listed:

awk -F"." '{print $2}' |while read db
do
  srvctl status database -d $db
done| grep  ${HOSTNAME%%.*}

In case of RAC this again introduces instances running on other nodes so again, filter using the current HOSTNAME

  • We've both gone all SuperUser on the OP with our shell antics :P – Philᵀᴹ Dec 4 '15 at 17:20
  • antics .... like know how you can help yourselves .... ? Creativity. – ik_zelf Dec 4 '15 at 20:29
0

Usually I look at the /etc/oratab entry. But that is again not fool proof as it could be edited by someone having the privilege to edit the file.

For example,

-sh-4.1$ ls -lrt /etc/oratab
-rw-rw-r-- 1 oracle oinstall 729 Nov  2 12:30 /etc/oratab
-sh-4.1$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/scratch/app/oracle/product/12.1.0/dbhome_1:N

Since I have only one database instance named orcl, there is only single entry in the file. If you have multiple entries, then DBCA updates and adds each new entry in the file created by root.sh.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.