Home » Server Options » RAC & Failsafe » Query on SCAN (Oracle Database 11g Release 2, Windows 2008)
Query on SCAN [message #534264] Sun, 04 December 2011 23:59 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8458
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8458
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> 
I don't know what you are doing. Can you show the actual query, not just what you say is the result? And show all the columns.
Re: Query on SCAN [message #534430 is a reply to message #534428] Tue, 06 December 2011 01:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8458
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8458
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 Go to previous message
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

Previous Topic: RMAN backup on RAC cluster
Next Topic: PCP
Goto Forum:
  


Current Time: Sat Jan 23 11:00:34 CST 2021