Oracle Database Auto Discovery

Hi,

I was asked by our development team to provide the best way to identify database parameters from database host, I was surprised to find so many options.

Identifying all instances on the current machine

Option 1: 

$ ps -ef |grep smon | grep -v grep
oracle    3025  1  0  2016 ?   00:00:48 asm_smon_+ASM
oracle   11459  1  0 17:24 ?  00:00:00 ora_smon_fdb

oracle SID is fdb and process id is 11459

Option 2: 

$ pgrep  -lf _pmon_
3025 asm_pmon_+asm
11459 ora_pmon_fdb

oracle 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

oracle 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.pdb.db       1        ONLINE  ONLINE       primary_host                Open,STABLE

Option 5:

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

Identify oracle_home

Option 1:

Using process id we already found in the previous run (look above for option 1 or 2)

$ grep -z ^ORACLE_HOME  /proc/11459/environ
ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1

 option 2:

$ pwdx 11459
11459: /oracle/product/12.1.0.2/dbhome_1/dbs

 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

option 4:

$ cat /etc/oratab | grep -v ^'#' | grep -v ^$ | awk -F ":" ' { print $2 }' | grep -v ASM | uniq
/oracle/product/12.1.0.2/grid/oracle/product/12.1.0.2/dbhome_1

If needed more information than connection to the remote instances should be created via SQL*Net for this we should have sys password

Checking from the Far sync - What is the name of the Primary Database 

select INST_ID,SOURCE_DB_UNIQUE_NAME from  gv$dataguard_stats where NAME='transport lag';
     SOURCE
     DB
INST UNIQUE
  ID NAME
---- -------
   1 pdb 

Checking from the Far sync - What is the name of the Standby Database 

SQL> SELECT db_unique_name FROM v$archive_dest WHERE destination IS NOT NULL and TARGET not in ('LOCAL','PRIMARY');

DB

UNIQUE

NAME

-------

sdb

Identifying primary and standby database hosts and ports via tnsping (from previous results)

$ tnsping pdb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 06-FEB-2017 18:04:00

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =primary_host)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdb)))
OK (0 msec)

Please let me know if you have some other tricks