The interconnect is a very important part of the cluster environment it is on of the aorta’s of a cluster environment. The interconnect is used as physical layer between the cluster nodes to perform heartbeats as well as the cache fusion is using it. The interconnect must be a private connection. Cross over cable is not support.
In a day to day operation it is proven that when the interconnect is configured correctly the interconnect will not be the bottleneck in case of performance issues. In the rest of this article will be focus on the how to validate the interconnect is really used. A DBA must be able to validate the interconnect settings in case of performance problems. Out of scope is the physical attachment of the interconnect.
Although you should thread performance issues in a Cluster environment the way you would normally also do in no-cluster environments here some area’s you can focus on.
Normally the average interconnect latency using gigabit must be < 5ms.
Latency around 2ms are normal.
Solution Validation of the interconnect ?
There are several ways to validate the interconnect.
This can be down using the x$ksxpia table, by using oradebug, or using queries on gv$ views(not possible in Oracle 9i). Besides the queries it is also possible to validate the use of the interconnect from the alert.log of your instance. Below we will list the options how to use them. Available interfaces? This query shows all the interfaces which are know within the Oracle database instances.
The query will work on Oracle 10g and 11g, not in 9i.
set linesize 120
col name for a22
col ip_address for a15
select inst_id,name,ip_address,is_public from gv$configured_interconnects order by 1,2; This
set linesize 120
col name for a22
col ip_address for a15
select inst_id,name,ip_address,is_public from gv$cluster_interconnects order by 1,2;
SQL> connect / as sysdba
SQL> alter session set tracefile_identifier=oradebug_interc
SQL> oradebug setmypid
SQL> oradebug ipc
SQL> exit
Now if you open the tracefile, in the bdump location, you can find the IP address used for the interconnect. Here is the result of the above oradebug ipc command.
dmno 0x7902775e admport:
SSKGXPT 0x10569c44 flags SSKGXPT_READPENDING active network 0
info for network 0
socket no 7 IP 145.72.220.83 UDP 53032
HACMP network_id 0 sflags SSKGXPT_UP
context timestamp 0
no ports
sconno accono ertt state seq# sent async sync rtrans acks
Query x$ksxpia
The last option is to query the x$ksxpia, which is instance specific query. A query on this view is providing the information from which setting the interconnect is picked up. Depending on the environment this can be useful to indicate if an where the configuration went wrong. This query will work in Oracle 9i,10g and 11g.
Below is an example of the output.
col picked_ksxpia format a15
col indx format 9999
col name_ksxpia format a5
col ip_ksxpia format a20
select * from x$KSXPIA
ADDR INDX INST_ID PUB_KSXPIA PICKED_KSXPIA NAME_ IP_KSXPIA ---------------- ----- ---------- ------------------------------ --------------- ----- -------------------- 00000001105D6540 0 1 N OCR en7 145.72.220.83 00000001105D6540 1 1 Y OCR en6 145.72.220.10
Note: The pub_ksxpia indicate if the interface is a public or private one. The picked_ksxpia indicated from where the information is collect. In the example the interface information from the OCR is used. Other values are OSD which is meaning third party clusterware is used. And CI is also possible which means the cluster_interconnect parameter is set. (Last I recommend not to do).
Using Alert.log
Instead of using one or more of the above queries you can also check the alert.log of the instances involved. During the startup of the RDBMS instance the interface used for the public and private connect is mentioned. Note this start from Oracle 10g, and is not available in Oracle 9i.
Example: alert.log
Interface type 1 eth1 10.10.10.0 configured from OCR for use as a cluster interconnect
Interface type 1 eth0 192.168.2.0 configured from OCR for use as a public interface
Using Oracle Cluster Registry to validate settings.
This method can be used to validate the settings in the OCR. But it does not mean it is also used by the database instances. To validate the settings of ocr you need to use the oifcfg command to retrieve the information from the OCR.
The oifcfg iflist list all the interfaces available on the Operating system and will not get this from the OCR.
The oifcfg getif list the configuration from the OCR.
An example:
racworkshop1:/export/home/oracle$ oifcfg iflist
eth0 192.168.2.0
eth1 10.10.10.0
racworkshop1:/export/home/oracle$ oifcfg getif
eth0 192.168.2.0 global public
eth1 10.10.10.0 global cluster_interconnect
Source: http://www.rachelp.nl/index_kb.php?menu=articles&actie=show&id=35
