Database Auto Discovery

Hi,

I was asked by our development team to provide the best way to identify database parameters from the 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:

Here’s a cleaner way for the 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 the 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 more information is needed, the connection to the remote instances should be created via SQL*Net; for this we should have the sys password.

Checking from the Far Sync Instance – 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
—- ——-
pdb

Checking from the Far Sync Instance – 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 any other tricks!