How do I know to which node of my Oracle RAC I am connected?
Solution 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;
Solution 2:
For current session:
SELECT sys_context('USERENV', 'INSTANCE') AS instance#,
sys_context('USERENV', 'INSTANCE_NAME') AS instance_name
FROM dual
Solution 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