| Query on SCAN [message #534264] |
Sun, 04 December 2011 23:59  |
kmsalih
Messages: 197
Registered: March 2006
|
Senior Member |
|
|
Hi DBAs,
Have a confusion in the output of v$configured_interconnects in Oracle 11g Release 2
I am having a 2 node oracle rac configured and using the SCAN feature with 3 SCAN VIPs.
SCAN VIP 1 is configured in Node 1 and SCAN VIP 2 & 3 are in Node 2.
From Instance # 1 the output of the query "Select Name, IP_Address from V$configured_interconnects" looks like this.
Name IP_Address
========== ============
Private 192.168.21.1
Public 100.101.21.21 (Physical IP)
Public 100.101.21.56 (VIP Node 1)
Public 100.101.21.58 (SCAN IP 1)
and
From Instance # 2 the output of the query "Select Name, IP_Address from V$configured_interconnects" looks like this.
Name IP_Address
========== ============
Private 192.168.21.2
Public 100.101.21.22 (Physical IP)
Public 100.101.21.56 (VIP Node 1)
Public 100.101.21.57 (VIP Node 2)
Public 100.101.21.58 (SCAN IP 1)
Public 100.101.21.59 (SCAN IP 2)
Please let me know is there any issues in the output of the query in the 2 instances.
My assumptions
1. VIP of Node 1 is present in both the outputs.If this is the default nature VIP of Node 2 also should present in both the outputs.
2. SCAN IP 3 (100.101.21.60) is missing in the output from Instance 2.
Thanks,
Salih KM
|
|
|
|
| Re: Query on SCAN [message #534391 is a reply to message #534264] |
Mon, 05 December 2011 16:41  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Did you really run the query you give, and get those results? I get nothing like that:[grid@host01 ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 5 22:39:46 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options
SQL> select * from v$configured_interconnects;
NAME IP_ADDRESS IS_ SOURCE
--------------- ---------------- --- -------------------------------
eth1 192.168.1.101 NO Oracle Cluster Repository
eth0 192.0.2.101 YES Oracle Cluster Repository
|
|
|
|
| Re: Query on SCAN [message #534417 is a reply to message #534391] |
Mon, 05 December 2011 21:43 |
kmsalih
Messages: 197
Registered: March 2006
|
Senior Member |
|
|
Hi John,
For getting clarity i added the details about the ips in brackets.
The OS is Windows 2008.
Query :
Select Name, IP_Address from V$configured_interconnects
Node 1 Output
Name IP_Address
---------- -------------
Private 192.168.21.1
Public 100.101.21.21
Public 100.101.21.56
Public 100.101.21.58
Node 2 Output
Name IP_Address
---------- -------------
Private 192.168.21.2
Public 100.101.21.22
Public 100.101.21.56
Public 100.101.21.57
Public 100.101.21.58
Public 100.101.21.59
As you are running the query on ASM instance you will not get the above output.
Please check the query on a Database Instance.
Thanks,
Salih Mohamed Yusuf
[Updated on: Mon, 05 December 2011 21:43] Report message to a moderator |
|
|
|
| Re: Query on SCAN [message #534428 is a reply to message #534417] |
Tue, 06 December 2011 00:39 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It doesn't make any difference whether you query the view in a DB instance or an ASM instance. Here's the result from a DB instance:SQL> select * from gv$configured_interconnects;
INST_ID NAME IP_ADDRESS IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
1 eth1 192.168.1.101 NO Oracle Cluster Repository
1 eth0 192.0.2.101 YES Oracle Cluster Repository
2 eth1 192.168.1.102 NO Oracle Cluster Repository
2 eth0 192.0.2.102 YES Oracle Cluster Repository
SQL> |
|
|
|
| Re: Query on SCAN [message #534430 is a reply to message #534428] |
Tue, 06 December 2011 01:00 |
kmsalih
Messages: 197
Registered: March 2006
|
Senior Member |
|
|
John,
Actual Query and output
SQL>Select * from gv$configured_interconnects;
INST_ID NAME IP_ADDRESS IS_ SOURCE
---------- ----------------------------------- ---------------- --- -------------------------------
2 Private 192.168.21.2 NO
2 Public 100.101.21.22 YES
2 Public 100.101.21.56 YES
2 Public 100.101.21.57 YES
2 Public 100.101.21.58 YES
2 Public 100.101.21.59 YES
1 Private 192.168.21.1 NO
1 Public 100.101.21.21 YES
1 Public 100.101.21.56 YES
1 Public 100.101.21.58 YES
There is one more SCAN VIP (100.101.21.60) configured which is not showing in the output.
Also VIP (100.101.21.56) configured in Node 1 is showing in both the instances, but the VIP (100.101.21.57) configured in Node 2 is showing only in the output of Instance 2.
The database is not yet live. Recently noticed that while connecting using the tnsnames entry with SCAN option, sometimes connections are going only to one instance.
Please know your opinion on this.
When i queried the gv$configured_interconnects in ASM instance, it gives a different output.
CMD>set ORACLE_SID=+ASM1
CMD>sqlplus / as sysasm
SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 5 22:49:52 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> set pages 20
SQL> set lines 120
SQL> Select * from gv$configured_interconnects;
INST_ID NAME IP_ADDRESS IS_ SOURCE
---------- ----------------------------------- ---------------- --- -------------------------------
1 Private 192.168.21.1 NO
1 Public 100.101.21.21 YES
2 Private 192.168.21.2 NO
2 Public 100.101.21.22 YES
2 Public 100.101.21.56 YES
2 Public 100.101.21.57 YES
2 Public 100.101.21.58 YES
2 Public 100.101.21.59 YES
8 rows selected.
SQL>
The main reason for starting this topic is to know whether the output of the gv$configured_interconnects are normal or is there any information missing from the normal output.
Thanks,
Salih Mohamed Yusuf
[Updated on: Tue, 06 December 2011 01:10] Report message to a moderator |
|
|
|
| Re: Query on SCAN [message #534436 is a reply to message #534430] |
Tue, 06 December 2011 01:58 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I don't know why you are getting that result. Is it possible that you made a mistake at install time? Nominating the wrong network cards, or perhaps an error with NIC bonding, or in your DNS config?
I used GPnP, with DHCP and GNS, no bonding. I suspect that you are using addresses statically registered in a DNS. Is that correct?
What do various utilities tell you, oifcg and olsnodes and nslookup:
[grid@host01 ~]$ olsnodes -n -i
host01 1 192.0.2.254
host02 2 192.0.2.250
host03 3 192.0.2.249
[grid@host01 ~]$ olsnodes -l -p
host01 192.168.1.101
[grid@host01 ~]$ oifcfg iflist -p -n
eth0 192.0.2.0 UNKNOWN 255.255.255.0
eth1 192.168.1.0 PRIVATE 255.255.255.0
[grid@host01 ~]$
[grid@host01 ~]$ nslookup sgp01-scan.sgp01.nuffid.com
Server: 192.0.2.1
Address: 192.0.2.1#53
Non-authoritative answer:
Name: sgp01-scan.sgp01.nuffid.com
Address: 192.0.2.252
Name: sgp01-scan.sgp01.nuffid.com
Address: 192.0.2.253
Name: sgp01-scan.sgp01.nuffid.com
Address: 192.0.2.251
And of course the output of
crsctl status resource -t
[update: bad typo above! I said "...with DHCP and DNS...", I've corrected that to "...with DHCP and GNS..." Sorry about that.]
[Updated on: Tue, 06 December 2011 03:05] Report message to a moderator |
|
|
|
| Re: Query on SCAN [message #534452 is a reply to message #534436] |
Tue, 06 December 2011 04:54 |
kmsalih
Messages: 197
Registered: March 2006
|
Senior Member |
|
|
John,
As you mentioned, SCAN IPs are statically registered in DNS.
Output from Node 1
host01>olsnodes -n -i
host01 1 host01vip
host02 2 host02vip
host01>olsnodes -l -p
host01 192.168.21.1
host01>oifcfg iflist -p -n
Public 100.101.21.0 PRIVATE 255.255.255.0
Private 192.168.21.0 PRIVATE 255.255.255.240
host01>nslookup host-scan
Server: abcddc01.comp.domain.com
Address: 110.140.120.130
Name: host-scan.comp.domain.com
Addresses: 100.101.21.59
100.101.21.58
100.101.21.60
host01>crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DBDATA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.DBFLASH.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.LISTENER.lsnr
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.asm
ONLINE ONLINE host01
ONLINE ONLINE host02 Started
ora.gsd
ONLINE OFFLINE host01
ONLINE OFFLINE host02
ora.net1.network
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.ons
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.registry.acfs
ONLINE ONLINE host01
ONLINE ONLINE host02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host01
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE host02
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE host01
ora.cvu
1 ONLINE ONLINE host02
ora.proddb1.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.proddb2.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.proddb3.db
1 ONLINE ONLINE host02 Open
2 ONLINE ONLINE host01 Open
ora.proddb4.db
1 ONLINE ONLINE host02 Open
2 ONLINE ONLINE host01 Open
ora.proddb5.db
1 ONLINE ONLINE host02 Open
2 ONLINE ONLINE host01 Open
ora.oc4j
1 ONLINE UNKNOWN host02
ora.scan1.vip
1 ONLINE ONLINE host01
ora.scan2.vip
1 ONLINE ONLINE host02
ora.scan3.vip
1 ONLINE ONLINE host01
ora.host01.vip
1 ONLINE ONLINE host01
ora.host02.vip
1 ONLINE ONLINE host02
host01>
Output from Node 2
HOST02>olsnodes -n -i
host01 1 host01vip
host02 2 host02vip
HOST02>olsnodes -l -p
host02 192.168.21.2
HOST02>oifcfg iflist -p -n
Public 100.101.21.0 PRIVATE 255.255.255.0
Private 192.168.21.0 PRIVATE 255.255.255.240
HOST02>nslookup host-scan
Server: abcddc01.comp.domain.com
Address: 110.140.120.130
Name: host-scan.comp.domain.com
Addresses: 100.101.21.60
100.101.21.59
100.101.21.58
HOST02>crsctl status resource -t
------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
------------------------------------------------------------------------------
Local Resources
------------------------------------------------------------------------------
ora.DBDATA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.DBFLASH.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.LISTENER.lsnr
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.asm
ONLINE ONLINE host01
ONLINE ONLINE host02 Started
ora.gsd
ONLINE OFFLINE host01
ONLINE OFFLINE host02
ora.net1.network
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.ons
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.registry.acfs
ONLINE ONLINE host01
ONLINE ONLINE host02
------------------------------------------------------------------------------
Cluster Resources
------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host01
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE host02
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE host01
ora.cvu
1 ONLINE ONLINE host02
ora.proddb1.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.proddb2.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.proddb3.db
1 ONLINE ONLINE host02 Open
2 ONLINE ONLINE host01 Open
ora.proddb4.db
1 ONLINE ONLINE host02 Open
2 ONLINE ONLINE host01 Open
ora.proddb5.db
1 ONLINE ONLINE host02 Open
2 ONLINE ONLINE host01 Open
ora.oc4j
1 ONLINE UNKNOWN host02
ora.scan1.vip
1 ONLINE ONLINE host01
ora.scan2.vip
1 ONLINE ONLINE host02
ora.scan3.vip
1 ONLINE ONLINE host01
ora.host01.vip
1 ONLINE ONLINE host01
ora.host02.vip
1 ONLINE ONLINE host02
HOST02>
Thanks,
Salih Mohamed Yusuf
|
|
|
|
| Re: Query on SCAN [message #534457 is a reply to message #534452] |
Tue, 06 December 2011 05:11 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Well, that all looks perfect. Perhaps that view being incorrectly populated is a Windows thing? Sorry, I've nothing further to suggest.
|
|
|
|
| Re: Query on SCAN [message #534468 is a reply to message #534457] |
Tue, 06 December 2011 06:12 |
kmsalih
Messages: 197
Registered: March 2006
|
Senior Member |
|
|
Thanks John for the confirming that the view is not as per the normal Oracle RAC Configuration.
I may have to contact Oracle Support for further investigation to check about the output of the view.
Update :Marked in Bold.
Thanks & Best Regards
Salih Mohamed Yusuf
[Updated on: Tue, 06 December 2011 06:32] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
