5

Need help finding Oracle home path corresponding to a database instance in RAC environment. I am aware of few of the ways to achieve the same. Listing them below to avoid the same answers.

  1. /etc/oratab This file is not mandatory and hence may not have all instance information.

  2. Parsing contents of Listener.ora In RAC environment, the listener.ora can be located at non default location.

  3. use TNS_ADMIN to find Listener.ora location and parse the file.

  4. ORACLE_HOME env variable May not be set always.

  5. ps -ef | grep tns to get the home path from service name. Gives path for currently running listener

  6. select "SYSMAN"."MGMT$TARGET_COMPONENTS"."HOME_LOCATION"
    from "SYSMAN"."MGMT$TARGET_COMPONENTS"
    where "SYSMAN"."MGMT$TARGET_COMPONENTS"."TARGET_NAME" = <Database SID>

    The schema sysman can be dropped after first time login to oracle.

  7. SELECT NVL(SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '\', -1, 2) -1) , SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '/', -1, 2) -1)) FOLDER
    FROM DBA_LIBRARIES
    WHERE LIBRARY_NAME = 'DBMS_SUMADV_LIB';

    So if a DBA changes Oracle Home (and hence the location of libqsmashr.so) after installation of Oracle, the path retrieved from above query would be invalid.

  8. . oraenv Works only for 11g

I am trying to find out a generic way which will work for all Oracle versions and it should not be dependent on anything which is not useful to DBA.

Do you have any way other than listed above to do the same?

Many Thanks in advance.

migrated from stackoverflow.com Jun 4 '13 at 3:16

This question came from our site for professional and enthusiast programmers.

2

First of all, I should thank you for all other steps that you mentioned above. As an addition to your points, I have this one which I've been using so far.

Solaris and Linux

$ pgrep  -lf _pmon_
12586 ora_pmon_ee10204

ORACLE_SID is ee10204

$ pwdx 12586
12586: /u01/oracle/10.2.0.4/ee1/dbs

ORACLE_HOME is /u01/oracle/10.2.0.4/ee1

HPUX

$ ps -ef | grep pmon
ora1024 25611     1  0  Mar 21  ?         0:24 ora_pmon_itanic10

ORACLE_SID is itanic10

$ pfiles 25611 | grep  bin
25611:                  /opt/ora1024/app/db_1/bin/oracle

ORACLE_HOME is /opt/ora1024/app/db_1

AIX

$ ps -ef | grep pmon
ora1024   262314        1   0   Mar 23      -  0:12 ora_pmon_mercury

ORACLE_SID is mercury

$ ls -l /proc/262314/cwd
lr-x------   2 ora1024  dba  0 Mar 23 19:31 cwd -> /data/opt/app/product/10.2.0.4/db_1/dbs/

ORACLE_HOME is /data/opt/app/product/10.2.0.4/db_1

Personally, I haven't seen any environment that tries to confuses the person who will maintain the system. Most of the time, I've always seen the normal /u01/app/oracle for the ORACLE_BASE.

  • 1
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Colin 't Hart Apr 25 '14 at 9:56
  • Thanks for the input Colin, I've edited the comment and provide the steps. – Adhika Apr 26 '14 at 1:57
1

Your best chance is to take a look at /etc/oratab or /var/opt/oracle/oratab. If you are in an installation where every PATH and/or ORACLE_HOME is hardcoded in scripts then you are out of luck, as is with any other option you mentioned.

Even if you happen to find a listener.ora, it could very well be that is setup for dynamic registration in which case you won't find your ORACLE_HOME's in the listener.ora too. Also, don't forget that the listener.ora does not need to be in ORACLE_HOME/network/admin, it can be in any location pointed to by TNS_ADMIN, /etc/, /var/opt/oracle ...

BTW: the oratab works for all Oracle versions, only, with v11 Oracle did include a call to a script (orabase) that only exists in v11 to set ORACLE_BASE. They even did this in such a way that you need OSDBA privileges to be able to run the script. Also see oraenv oddities in 11gR2

  • Thanks ik_zelf for the response. So what happens in case of dynamic registration? Where is all information ? how is oracle home and instance is associated. I tried finding these details on net.. but couldn't find it mentioned clearly anywhere – user1787572 Jun 3 '13 at 12:39
  • reg info is in memory and flushed when instance[s] or listener[s] stop[s]. – ik_zelf Jun 3 '13 at 13:45

Your Answer

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