Saturday, July 28, 2007

How to find Cluster Interconnect IP address from Oracle Database

Hello,

The easiest way to find the cluster interconnect is to view the “hosts” file. The “hosts” file is located under:

UNIX .......... /etc

Windows ...... C:\WINDOWS\system32\drivers\etc

Following are the ways to find the cluster interconnect through Oracle database:

1) Query X$KSXPIA

The following query provides the interconnect IP address registered with Oracle database:

SQL> select IP_KSXPIA from x$ksxpia where PUB_KSXPIA = 'N';
IP_KSXPIA
----------------
192.168.10.11

This query should be run on all instances to find the private interconnect IP address used on their respective nodes.

2) Query GV$CLUSTER_INTERCONNECTS view

Querying GV$CLUSTER_INTERCONNECTS view lists the interconnect used by all the participating instances of the RAC database.

SQL> select INST_ID, IP_ADDRESS from GV$CLUSTER_INTERCONNECTS;

INST_ID IP_ADDRESS
---------- ----------------
1 192.168.10.11
2 192.168.10.12

3) ASM and Database log

We can find out the cluster interconnect used by viewing the log file of both ASM instance and the database instance.

When the ASM instance is started it registers the private interconnect used. Following is the excerpt from the ASM alert log on node 1:

:
:
Cluster communication is configured to use the following interface(s) for this instance
192.168.10.11
:
:

Similarly, the database instance also registers the private interconnect with its instance and following message is recorded in the alert log:

:
:
Cluster communication is configured to use the following interface(s) for this instance
192.168.10.11
:
:

4) CLUSTER_INTERCONNECT initialization parameter

This parameter provides Oracle with information on the availability of additional cluster interconnects. It overrides the default interconnect settings at the OS level with a preferred one.

You can view the current value of this parameter by issuing the following in SQL*Plus, connected as SYS user:

SQL> show parameter cluster_interconect

Thanks for reading :-)

3 comments:

ZY1 木匠 said...

Good stuff, please add the Oracle Version number too.

Asif Momen said...

I will make sure, I don't miss version numbers in future posts.

I have tested this procedure on Oracle 10g Release 2 (10.2.0.3).

Ravi said...

That is really awesome.. Thanks for the post..

Can you please post something on enabling Jumbo frames on Linux.. with its advantages/disadvantages.
Thanks
Ravindra