Basics Commands for DBAs – Part 1: Finding Instances

Basics Commands for DBAs – Part 1: Finding Instances

Hi, In the next few blogs I will discuss scripting techniques that DBAs usually need. I’m concentrating on the commands that are usually part of a larger script.
Later on I will submit some scripts that use these techniques.

Identifying All Instances Running on the Current Machine

Option 1
# ps -ef |grep smon
oracle 3025 1 0 2016 ? 00:00:48 asm_smon_+ASM
oracle 11459 1 0 17:24 ? 00:00:00 ora_smon_fdb
root 12763 11332 0 17:52 pts/1 00:00:00 grep --color=auto smon

SID is fdb and process ID is 11459.

Option 2
# pgrep -lf _pmon_
3025 asm_pmon_+asm
11459 ora_pmon_fdb
SID is fdb and process ID is 11459.

Option 3
Cleaner way for SID:
$ ps -ef |grep 'ora_smon_.*$' | grep -v grep | awk -F_ '/ora_smon/{print $NF}'
fdb

SID is fdb.

Option 4
When we already know ASM home (grid infrastructure), we can use the cluster commands:
$ /oracle/product/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type"|awk '/^ora./ {l=$0;} !/^ora./ { if ( l > "" ) l=l " " $0; print l;l="";}'|grep ${HOSTNAME%%.*}
ora.fdb.db 1 ONLINE ONLINE dbhost Open,STABLE

SID is fdb.

Option 5
Using /etc/oratab file:
$ cat /etc/oratab | grep -v ^'#' | grep -v ^$ | awk -F ":" ' { print $1 }' | grep -v ASM | uniq
fdb

SID is fdb.

Oratab can be in the following locations:
Solaris: /var/opt/oracle/oratab
/var/opt/oracle
/etc/oratab

 

Identifying ORACLE_HOME of Instance Running on the Current Machine

Option 1
Since we know the process ID of the database, we can look at the environment variable that was used when the database started.
$ grep -z ^ORACLE_HOME /proc/11459/environ
ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1#

ORACLE_HOME is /oracle/product/12.1.0.2/dbhome_1

Option 2
pwdx – report current working directory of a process:
$ pwdx 11459
11459: /oracle/product/12.1.0.2/dbhome_1/dbs

ORACLE_HOME is /oracle/product/12.1.0.2/dbhome_1

Option 3

$ strings /proc/11459/environ | grep ORACLE_HOME
ORACLE_HOME_LISTNER=/oracle/product/12.1.0.2/grid
ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1

ORACLE_HOME is /oracle/product/12.1.0.2/dbhome_1

Option 4
Using /etc/oratab file:
$ cat /etc/oratab | grep ^${DB}: | grep -v ^$ | cut -d":" -f 2 | grep -v ASM | uniq
/oracle/product/12.1.0.2/dbhome_1
ORACLE_HOME is /oracle/product/12.1.0.2/dbhome_1

Please provide your own methods if I’ve missed some options.
Thanks!
Yossi