Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there an easy way to determine to which RAC node of an Oracle 11g R2 system I am connected? I am trying to perform some failover tests and I want to make sure my application is correctly connected to one note and upon the server shutdown of that node makes the transition smoothly to another node without any noticable delay on the frontend. May it is worth mentioning that we make use of TAF.

I considered using Enterprise Manager for this, but I guess that when I am connected to one node running em and this node goes down I will not really have a chance to monitor the nodes connectivity status.

share|improve this question

3 Answers 3

up vote 4 down vote accepted

For your current session?

select host_name from gv$instance where instance_number=userenv('instance');

For all sessions:

select i.host_name, s.username from 
  gv$session s join
  gv$instance i on (i.inst_id=s.inst_id)
where 
  username is not null;
share|improve this answer
    
OK, so this shows me how my own session is connected. Is there a chance I can monitor others as well? If I login using sqlplus to check the webapp that logs with a different session? –  Stephan Feb 2 '11 at 9:43
    
USERENV works but Oracle recommends sys_context instead, sys_context('userenv', 'instance'). In 8i: Oracle recommends that you use the application context feature or the SYS_CONTEXT function with the USERENV option. docs.oracle.com/cd/A87860_01/doc/server.817/a85397/… –  Shannon Severance Jun 1 '12 at 1:31
    
From 9i to 11gR2: USERENV is a legacy function that is retained for backward compatibility. Oracle Corporation recommends that you use the SYS_CONTEXT function with the built-in USERENV namespace. docs.oracle.com/cd/A91202_01/901_doc/server.901/a90125/… docs.oracle.com/cd/E11882_01/server.112/e26088/… –  Shannon Severance Jun 1 '12 at 1:32

For current session:

SELECT sys_context('USERENV', 'INSTANCE') AS instance#, sys_context('USERENV', 'INSTANCE_NAME') AS instance_name FROM dual

share|improve this answer
    
Thanks, that was indeed helpful :) –  Stephan Feb 4 '11 at 20:55

If you are connected to node1

SQL> select host_name from v$instance;
instance1

If you are connected to nodeN

SQL> select host_name from v$instance;
instanceN

If you like to know all instances and all nodes

SQL> select instance_name,host_name from gv$instance;

or

$ crsctl stat res -t
cluster resource
 1 node host1  online
 2 node host2  online
 n node hostn  offline
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.