How do I know to which node of my Oracle RAC I am connected?

Question

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 node and upon the shutdown of this node node makes the transition smoothly to another node without any noticeable delay on the front end. Maybe 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.

Answer 1

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;

Answer 2

For current session:

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

Answer 3

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

Answer 4

SELECT sid,serial#, inst_id,username,event,ownerid
    FROM GV$SESSION
    WHERE sid=(select sid from v$mystat where rownum=1);

Answer 5

I was wondering the same thing as I tried to come up with a way to set the ORACLE_SID in a RAC environment. One of my fellow DBAs had a scheme where he looked at the pmon process and extracted the last digit as the instance number, but that only worked if the instance was running. The following is my solution as custom code at the end of oraenv (renamed to oraenvr), that will grab node number as the last digit of the running instance name, or extract the last digit from a running ASM instance. It only needs to do this for non-ASM instances, because with ASM, the database name in /etc/oratab is also the instance name (e.g., +ASM1). But, again, you'll need either the database or ASM instance running so it will have a running process from which to get the node number.

#
# Install any "custom" code here
#
# Add proceeding digit to ORACLE_SID if it's a database instance
#
if [ `expr $ORACLE_SID|cut -c1-4` != "+ASM" ]; then ## ASM instance name is also the /etc/oratab database name, so we don't need to change it.
   len=`ps -ef|grep smon|grep $ORACLE_SID|wc -c`
   if [ $len != "0" ]; then # we found the running non-ASM database instance
      len1=`expr $len - 1`
      nodenum=`ps -ef|grep smon|grep $ORACLE_SID|cut -c$len1-$len`
   else ## use ASM instance as node number reference since database instance is probably down.
      len=`ps -ef|grep smon|grep ASM|wc -c`
      len1=`expr $len - 1`
      nodenum=`ps -ef|grep smon|grep ASM|cut -c$len1-$len`
   fi
   ORACLE_SID=$ORACLE_SID$nodenum
fi

Blockquote