Basics commands for DBA part 1 - finding instance

Hi,
In the next few blogs I will discuss on scripting technics that DBA usually needs.
I am concentrating in the commands, that are usually part of larger script.
Later on I will submit some scripts that uses these technics.

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 location 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 was used when started the database
$ 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 way if I missed some options
Thanks
Yossi

Comments

Popular posts from this blog

Data Guard - Changing IP Addresses

Install Oracle Internet Directory (OID) in Standalone mode

Fixing & Registering ORACLE_HOMES in Central Inventory