Home » RDBMS Server » Server Administration » query to identify the Oracle Home (DB 11.x, 12.x)
query to identify the Oracle Home [message #604424] Mon, 30 December 2013 08:55 Go to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Is there a way to identify the location of an instance's Oracle Home? Using SQL?
The closest I can get is the Oracle Base, which I can do with this:
select  KSPPINM, INDX, KSPPSTVL from  x$ksppcv join x$ksppi using(indx) where KSPPINM='__oracle_base';
and even this doesn't work for an ASM instance.
I need to find the location and contents of the OUI Inventory as well, I am nowhere on that.

Any insight will be very welcome.

Report message to a moderator

Re: query to identify the Oracle Home [message #604426 is a reply to message #604424] Mon, 30 December 2013 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How can you connect to any DB to issue any SQL without ORACLE_HOME containing valid value?
ORACLE_HOME for the client?

[Updated on: Mon, 30 December 2013 09:08]

Report message to a moderator

Re: query to identify the Oracle Home [message #604427 is a reply to message #604424] Mon, 30 December 2013 09:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Have no idea with SQL means.
Using OS utilities (Solaris in this case)
oracle@xxxt#ps -ef | grep smon
  oracle  1674     1   0   Oct 18 ?           2:07 asm_smon_+ASM
  oracle 24854     1   0   Dec 29 ?           0:05 ora_smon_widw
  oracle  1924     1   0   Oct 18 ?           1:25 ora_smon_bailout
  oracle  7595  7557   0 10:05:55 pts/1       0:00 grep smon
oracle@xxx#pwdx 1674 24854 1924
1674:   /u01/base/product/11.2.0.3/grid/dbs
24854:  /u01/base/product/11.2.0.3/home/dbs
1924:   /u01/base/product/11.2.0.3/home/dbs


Edit: 


 
SQL> var thisHome varchar2(150);

SQL> exec dbms_system.get_env('ORACLE_HOME',:thisHome);

PL/SQL procedure successfully completed.

SQL> print :thisHome

THISHOME
--------------------------------------------------------------------------------
/u01/base/product/11.2.0.3/home

Source/Tip for above came from
http://orafaq.com/papers/dbms_sys.doc

[Updated on: Mon, 30 December 2013 10:32]

Report message to a moderator

Re: query to identify the Oracle Home [message #604430 is a reply to message #604426] Mon, 30 December 2013 10:41 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying.

It is the Oracle Home off which the instance is running that I need.
If I could find a V$ view or X$ table with a file or process name that includes the full path to something guaranteed to be in the Oracle Home (not, for example, the path to the spfile which might have been placed somewhere else) that would do.

Report message to a moderator

Re: query to identify the Oracle Home [message #604431 is a reply to message #604427] Mon, 30 December 2013 10:45 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Thank you, that looks really good. Except that I can't use DBMS_SYSTEM in a ASM instance. But it gets me half way there.

In release 12, I have this:
 select sys_context('userenv','oracle_home') from dual;
but I need something for 11.2.x as well.

Report message to a moderator

Re: query to identify the Oracle Home [message #604433 is a reply to message #604431] Mon, 30 December 2013 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does this not give you the client oracle home?

Report message to a moderator

Re: query to identify the Oracle Home [message #604435 is a reply to message #604433] Mon, 30 December 2013 11:30 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Thank you for making me do the test. It does give the server home, which is perhaps rather surprising:
192.168.56.101:1521/orclz>
192.168.56.101:1521/orclz> select sys_context('userenv','oracle_home') from dual;

SYS_CONTEXT('USERENV','ORACLE_HOME')
------------------------------------------------------------------------------------------------------------
/u01/app/oracle/product/12.1.0/dbhome_1

192.168.56.101:1521/orclz> conn / as sysdba
Connected.

orclz> select sys_context('userenv','oracle_home') from dual;

SYS_CONTEXT('USERENV','ORACLE_HOME')
------------------------------------------------------------------------------------------------------------
C:\app\oracle\product\12.1.0\dbhome_1

orclz>


I remember reading an article somewhere recently that used sys_context to query a built-in context. It was NOT the USERENV context, it was something else (undocumented, of course) that had all sorts of server information. I've been trying to find it, but I can't. Is there a view that lists all the context namespaces? For example, USERENV is not listed in any DBA or V$ view that I can find.

--update: added the demo. Both DBs are called orclz, but they are on different platforms.

[Updated on: Mon, 30 December 2013 11:32]

Report message to a moderator

Re: query to identify the Oracle Home [message #604440 is a reply to message #604435] Mon, 30 December 2013 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
--update: added the demo. Both DBs are called orclz, but they are on different platforms.


Ah, OK I didn't understand the demo. Smile

DBA_CONTEXT gives all the contexts created by CREATE CONTEXT and variables/values are stored in either UGA or SGA (depending if it is a private or shared context) but USERENV is not. It is a built-in one whose variables/values are private or shared depending on the variable. It is a "false" context like PUBLIC is a "false" user/role.

Report message to a moderator

Re: query to identify the Oracle Home [message #604471 is a reply to message #604435] Tue, 31 December 2013 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Searching I found oracle home nowhere (in 11g) in any x$/v$ but I found that Oracle added a step in startup where it retrieves oracle_base. You can see it in alert.log (or V$DIAG_ALERT_EXT) with lines starting with "ORACLE_BASE from environment = ".

Can you check if you have the same kind of thing for oracle home in 12c (I have not it).

Report message to a moderator

Re: query to identify the Oracle Home [message #604473 is a reply to message #604471] Tue, 31 December 2013 05:14 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Thank you for this suggestion, it is the same in 12.x. I guess this must be the point at which __oracle_base is set. But it does not occur in the ASM instance alert log (which I have to look at through x$dbgalertrext, of course) so it doesn't get me where I need to go. I still can't find that built-in context.

Report message to a moderator

Re: query to identify the Oracle Home [message #604476 is a reply to message #604473] Tue, 31 December 2013 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is not stored anywhere, it is actually branches in sys_context code.
Something like:
if namespace = 'USERENV' then
  case parameter
    when 'ACTION' then retrieve value from somewhere
    when ...
    ...
    else raise error ORA-02003: invalid USERENV parameter
  end case
else -- general case
  ...
end if

Report message to a moderator

Re: query to identify the Oracle Home [message #604485 is a reply to message #604424] Tue, 31 December 2013 07:09 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
John Watson wrote on Mon, 30 December 2013 08:55
Is there a way to identify the location of an instance's Oracle Home? Using SQL?
The closest I can get is the Oracle Base, which I can do with this:
select  KSPPINM, INDX, KSPPSTVL from  x$ksppcv join x$ksppi using(indx) where KSPPINM='__oracle_base';
and even this doesn't work for an ASM instance.
I need to find the location and contents of the OUI Inventory as well, I am nowhere on that.

Any insight will be very welcome.


Does this need to be done in SQL --- from within the database?
I don't know if it will solve your real need or not, but I recently wrote a shell script that gives me an entire inventory report, cross-matching the central inventory with the oratab file. If that sounds useful, you can see it at http://edstevensdba.wordpress.com/category/uncategorized/

Report message to a moderator

Re: query to identify the Oracle Home [message #604488 is a reply to message #604485] Tue, 31 December 2013 07:38 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
How to find the ORACLE_HOME path in Oracle Database?
In 9i:

SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM dba_libraries
WHERE library_name='DBMS_SUMADV_LIB';

In 10g:

SQL > var OHM varchar2(100);
SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
SQL > PRINT OHM

Linux/Unix:

echo $ORACLE_HOME

Windows:

start - run - regedit (enter) - HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE
======================================================================
Anyway, $ORACLE_HOME can, to my knowledge, not be queried from the
database. The main question here why would someone want to. It is
possible however to display it using:

SQL> host echo $ORACLE_HOME , on UNIX/Linux

SQL> host echo %ORACLE_HOME% , on Window$
/Michael
Source: http://bytes.com/topic/oracle/answers/64531-query-oracle_home-sqlplus
------------------------------------------------------------------------------

Report message to a moderator

Re: query to identify the Oracle Home [message #604491 is a reply to message #604488] Tue, 31 December 2013 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe you should read the question and the whole topic before answering in any way.

Report message to a moderator

Re: query to identify the Oracle Home [message #604492 is a reply to message #604485] Tue, 31 December 2013 07:44 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying.
I want to use only SQL because some of the work will be done within ASM instances, where I have no access to facilities such as utl_file or the Scheduler that let me use OS files and scripts. However, I may end up having to find a way of doing something with scripts. Thanks for the pointer to your work, it may give me a few ideas.

Report message to a moderator

Re: query to identify the Oracle Home [message #604495 is a reply to message #604488] Tue, 31 December 2013 07:52 Go to previous message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
The problem with the dbms_system call that you and Mahesh suggested is that it is not available in an ASM instance. But thank you for replying.

Report message to a moderator

Previous Topic: Oracle configuration
Next Topic: Character Code Chart for AL32UTF8 on Oracle 11
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 07:34:58 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.