6

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.

Stephan
  • 417
  • 1
  • 5
  • 13

5 Answers5

7

For current session:

SELECT sys_context('USERENV', 'INSTANCE') AS instance#,
       sys_context('USERENV', 'INSTANCE_NAME') AS instance_name
FROM dual
Andrew Schulman
  • 8,811
  • 21
  • 32
  • 47
Hakan
  • 71
  • 1
4

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;
HampusLi
  • 3,478
  • 17
  • 14
  • 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 02 '11 at 09: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._ http://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/function.htm#79864 – Shannon Severance Jun 01 '12 at 01: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._ http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90125/functions158.htm#SQLRF06157 http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions228.htm#SQLRF06157 – Shannon Severance Jun 01 '12 at 01:32
2

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
Ladadadada
  • 26,337
  • 7
  • 59
  • 90
aitor
  • 21
  • 1
0
SELECT sid,serial#, inst_id,username,event,ownerid
    FROM GV$SESSION
    WHERE sid=(select sid from v$mystat where rownum=1);
Jenny D
  • 27,780
  • 21
  • 75
  • 114
aitor
  • 21
  • 1
  • 2
    A little context for what this query does, or any text besides the query itself would be great... – GregL May 30 '16 at 12:58
  • The view `v$mystat` contains (in all rows) the `SID` of the session that is querying it, so you may get the value from the firt row (`rownum=1`). This trick is offen used and works fine **on a single instance**, but here definitively fails. The problem is that a session is identifies with `sid`**AND** `serial#`. Several instance can have a session with a same `sid` but different `serial#`. Easy to be checked with `select sid, count(*) from GV$SESSION group by sid having count(*) > 1`. **So this approach is NO GO!** – Marmite Bomber Jun 19 '17 at 11:26
0

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

TexasKev
  • 1
  • 1