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.

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;
3
7

For current session:

SELECT sys_context('USERENV', 'INSTANCE') AS instance#,
       sys_context('USERENV', 'INSTANCE_NAME') AS instance_name
FROM dual
0
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
0
SELECT sid,serial#, inst_id,username,event,ownerid
    FROM GV$SESSION
    WHERE sid=(select sid from v$mystat where rownum=1);
2
  • 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! 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

Your Answer

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

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