关于有SCAN-NAME，但是仍使用VIP连接的问题

武汉-SSH

昨天在群里，谈到有SCAN-NAME，但是仍使用VIP连接会有什么问题，我当时回答“无服务端LB，且客户端FAILOVER会失败吧”，但是后来一想，跟客户端FAILOVER应该没什么关系。

求正解。

武汉-SSH

找到正确答案了：
针对 ORA-12170 降低TIMEOUT的方法的确是调整系统参数，LINUX 的是 net.ipv4.tcp_syn_retries 。

10.2.0.1

[root@testdb ~]# echo 0 > /proc/sys/net/ipv4/tcp_syn_retries
[root@testdb ~]# cat /proc/sys/net/ipv4/tcp_syn_retries
0

01:18:26 SQL> conn system/oracle@test
ERROR:
ORA-12170: TNS:Connect timeout occurred


Warning: You are no longer connected to ORACLE.
01:18:30 SQL>

4秒

Maclean Liu(刘相兵

CONNECT_TIMEOUT

Purpose

To specify the timeout duration in seconds for a client to establish an Oracle Net connection to an Oracle database. Put this parameter under the DESCRIPTION parameter.

Usage Notes

The timeout interval specified by CONNECT_TIMEOUT is a superset of the TCP connect timeout interval. It includes the time to be connected to the database instance providing the requested service, not just the duration of the TCP connection.

The default value of CONNECT_TIMEOUT is dependent on the TCP_CONNECT_TIMEOUT parameter. Oracle recommends setting the CONNECT_TIMEOUT value slightly greater than the TCP_CONNECT_TIMEOUT value.

The timeout interval is applicable for each ADDRESS in an ADDRESS_LIST, and each IP address to which a host name is mapped.

The CONNECT_TIMEOUT parameter is equivalent to the sqlnet.ora parameter SQLNET.OUTBOUND_CONNECT_TIMEOUT and overrides it.

Example

net_service_name=
(DESCRIPTION=
  (CONNECT_TIMEOUT=10)(RETRY_COUNT=3)
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=
   (SERVICE_NAME=sales.us.example.com)))




TCP.CONNECT_TIMEOUT

Purpose

To specify the time, in seconds, for a client to establish a TCP connection (PROTOCOL=tcp in the TNS connect address) to the database server. If a TCP connection to the database host is not established in the time specified, then the connection attempt is terminated. The client receives an ORA-12170: TNS:Connect timeout occurred error.

The timeout applies to each IP address to which a host name resolves. For example, if a host name resolves to an IPv6 and an IPv4 address, and if the host is not reachable through the network, then the connection request times out twice the TCP.CONNECT_TIMEOUT setting because there are two IP addresses. In this example, the default timeout setting of 60 would cause a timeout in 120 seconds.

Default

60

Example

TCP.CONNECT_TIMEOUT=10


Solution

In Oracle 11g there is a workaround to this limitation, you can configure the TCP connection timeout by setting the SQL*Net parameter TCP.CONNECT_TIMEOUT in the database server's SQLNET.ORA file.  This parameter is described as follows in the 11g Oracle Database Net Services Reference documentation:

Purpose

Use the TCP.CONNECT_TIMEOUT parameter to specify the time, in seconds, for a client to establish a TCP connection to the database server.

If a TCP connection to the database host is not established in the time specified, the connect attempt is terminated. The client receives an ORA-12170: TNS:Connect timeout occurred error.

Without this parameter, a client connection request to the database server can block for the default duration of the TCP connect timeout (approximately 8 minutes on Linux) when the database server host system is unreachable.

This parameter only applies to TCP connections (PROTOCOL=tcp in the TNS connect address).

Example

Inside the SQLNET.ORA file on the database server add the following parameter which will time out ALL TCP connection attempts that take longer than 60 seconds to connect.
TCP.CONNECT_TIMEOUT = 60

武汉-SSH

今天又与朋友聊到 SCAN，谈到 ORACLE 提出 SCAN 的概念后，VIP 是否还有存在的必要？
我们一致认为，VIP 的模型应该可以取消了，大家怎么想？

武汉-SSH

11.2.0.3 是1分钟
01:19:21 SQL> conn system/oracle@test
ERROR:
ORA-12170: TNS: 连接超时


Warning: You are no longer connected to ORACLE.
01:20:22 SQL>
01:20:52 SQL>
01:20:55 SQL>
01:20:56 SQL> conn system/oracle@test
ERROR:
ORA-12170: TNS: 连接超时


01:21:57 SQL>

武汉-SSH

14:00:44 SQL> conn system/oracle@test
ERROR:
ORA-12170: TNS:Connect timeout occurred


14:03:54 SQL>

10.2.0.1 默认是190秒啊？

[ 本帖最后由 武汉-SSH 于 2012-3-11 22:46 编辑 ]

武汉-SSH

11:42:27 SQL> conn system/oracle@test
ERROR:
ORA-12170: TNS:Connect timeout occurred

11:45:37 SQL>

武汉-SSH

再次用ML相同的脚本测试，OCI  无论VIP漂与不漂，都会获得连接。
测试数据库版本 10.2.0.4 RAC
也就是说，我这边当前测试结果，OCI  与 JDBC.THIN 无论VIP漂与不漂，都会获得连接。

Q:但是奇怪，指定 FAILOVER = OFF，仍然会去尝试下个地址，仍然会获得连接。。。。
A:根据我的测试来看，是因为 JDBC.THIN 的语法检查更严格一些，当有ADDRESS_LIST时，FAILOVER 在 ADDRESS_LIST 内部，对 ADDRESS  达到 FAILOVER 的效果；FAILOVER 在 ADDRESS_LIST 外部，在 DESCRIPTION  内时，面向范围是多个 ADDRESS_LIST。但是 OCI 的连接对这个语法没这么严格，FAILOVER 在 ADDRESS_LIST 外部，在 DESCRIPTION  内时，仍然对  ADDRESS_LIST 内的 ADDRESS 生效。

测试场景：
RAC NODE01 实例关闭，监听可以选择关与不关，VIP 可以选择漂与不漂。URL 中 NODE01-VIP 写在第一，LB=OFF，FAILOVER=OFF
JDBC THIN 使用 ojdbc14.jar

预期：
无法获得连接

实测结果：
OCI 无论 FAILOVER=OFF 在哪里都生效，无法获得连接；
JDBC THIN 会根据 FAILOVER=OFF 所在的位置，严格按照语法实现；可能获得连接，也可能无法获得连接。

这个实验也基本上证明了在11G RAC上不使用SCAN-NAME，而仍然沿用VIP的方法，损失的应该只是服务端LB的功能，当然，这是从表面来看。

遗留问题：
1.顺便这里想问一下有没有将VIP 关闭以后不自动起的方法？
2.JAVA 的连接代码能否抛出更丰富信息的异常？这个异常是由JAVA提供，还是ORACLE 驱动提供？当前看到的代码都是JAVA提供的，只能抛出SQLException，在获取不到连接时，信息不够丰富。
3.如何 TRACE 连接时  FAILOVER。

[ 本帖最后由 武汉-SSH 于 2012-3-8 02:25 编辑 ]

Maclean Liu(刘相兵

Version 11.2.0.1 on Windows

C:\Windows\system32>tnsping VIP_FAILOVER

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 08-3月 -2012 01:54:19

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

已使用的参数文件:
D:\app\Maclean.Liu\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS_LIST = (failover= on) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh2-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521))) (CONNECT_DATA = (SERVICE_N
AME = VPROD)))
OK (20 毫秒)

SQL> show parameter instance_number

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
instance_number                      integer
2


shutdown  instance 2



ora.vrh1.vip
      1        ONLINE  ONLINE       vrh1                                         
ora.vrh2.vip
      1        ONLINE  ONLINE       vrh2                                         
          
vip didn't  failover           



C:\Windows\system32>sqlplus system/oracle@VIP_FAILOVER       ==> successful
C:\Windows\system32>sqlplus system/oracle@VIP_FAILOVER       ==> successful



Version 11.2.0.3 on Linux

[oracle@vrh1 admin]$ tnsping VIP_FAILOVER

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 08-MAR-2012 04:55:37

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (failover= on) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh2-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = VPROD)))
OK (30 msec)


[oracle@vrh1 admin]$ sqlplus  system/oracle@VIP_FAILOVER             ==> successful
[oracle@vrh1 admin]$ sqlplus  system/oracle@VIP_FAILOVER             ==> successful


NO Connection Time Failover

VIP_NO_FAILOVER=
(DESCRIPTION =
(ADDRESS_LIST =
  (failover= off)
  (ADDRESS = (PROTOCOL = TCP)(HOST = vrh2-vip)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521))
)
(CONNECT_DATA =
   (SERVICE_NAME = VPROD)
)
)
[oracle@vrh1 admin]$ tnsping VIP_NO_FAILOVER

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 08-MAR-2012 04:58:10

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (failover= off) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh2-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = VPROD)))
OK (20 msec)



[oracle@vrh1 admin]$ sqlplus  system/oracle@VIP_NO_FAILOVER

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 8 04:58:19 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

[oracle@vrh1 admin]$ sqlplus  system/oracle@VIP_NO_FAILOVER

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 8 04:58:50 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


就以上来看client connect time failover 没有问题，  failover=OFF也发挥了正常的作用，

看起来是我昨天的测试遇到了一些问题...........

武汉-SSH

该代码来自MOS

import java.sql.*;

class TestFailover
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    String url = "jdbc:oracle:thin:@(DESCRIPTION =(FAILOVER=on)(ADDRESS_LIST = \n" +
    "    (ADDRESS = (PROTOCOL = TCP)(HOST = racone01-vip)(PORT = 1521))\n" +
    "    (ADDRESS = (PROTOCOL = TCP)(HOST = racone02-vip)(PORT = 1521)))\n" +
    "    (CONNECT_DATA =\n" +
    "      (SERVICE_NAME = aaaa)\n" +
    "    )\n" +
    "  )\n" +
    "\n";

    // Get connection
    Connection conn = DriverManager.getConnection (url, "aaa", "aaa");

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the host name from the instance you are connected to
    ResultSet rset = stmt.executeQuery ("select host_name from v$instance");
   
    // Iterate through the result and print the host name of the DB instance
    while (rset.next ()){
      System.out.println (rset.getString (1));
       }
    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Close the connection
    conn.close();
  }
}

[ 本帖最后由 武汉-SSH 于 2012-3-8 00:04 编辑 ]

武汉-SSH

我这边当前测试结果，无论VIP漂与不漂，都会获得连接。JAVA 代码 连接测试。。。。
但是奇怪，指定 FAILOVER = OFF，仍然会去尝试下个地址，仍然会获得连接。。。。

武汉-SSH

不对啊，你这个测试，构造有问题，VIP应该没有漂移吧？

Maclean Liu(刘相兵

Failover TEST Script 参考：http://www.oracledatabase12g.com ... 6%9C%ACloop-sh.html

Maclean Liu(刘相兵

具体测试了一下 发现 client 用 VIP failover(非SCAN-VIP) 还是有问题

ODM TEST:

[grid@vrh2 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.3.0]

[oracle@vrh1 ~]$ srvctl config scan
SCAN name: vrh-cluster-scan, Network: 1/192.168.1.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /vrh-cluster-scan/192.168.1.200


[grid@vrh1 ~]$ srvctl config nodeapps
Network exists: 1/192.168.1.0/255.255.255.0/eth0, type static
VIP exists: /vrh1-vip/192.168.1.162/192.168.1.0/255.255.255.0/eth0, hosting node vrh1
VIP exists: /vrh2-vip/192.168.1.164/192.168.1.0/255.255.255.0/eth0, hosting node vrh2
GSD exists
ONS exists: Local port 6100, remote port 6200, EM port 2016



[grid@vrh2 ~]$ crsctl status res ora.vprod.db
NAME=ora.vprod.db
TYPE=ora.database.type
TARGET=OFFLINE, ONLINE
STATE=OFFLINE, ONLINE on vrh2


Only Vrh2 has  an open instance ,  another instance has been closed.




SCAN_FAILOVER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vrh-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = VPROD)
    )
  )





VIP_FAILOVER=
(DESCRIPTION =
(ADDRESS_LIST =
  (failover= on)
  (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = vrh2-vip)(PORT = 1521))
)
(CONNECT_DATA =
   (SERVICE_NAME = VPROD)
)
)


[oracle@vrh1 admin]$ tnsping SCAN_FAILOVER

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 07-MAR-2012 03:29:38

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vrh-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = VPROD)))
OK (0 msec)
[oracle@vrh1 admin]$ tnsping VIP_FAILOVER

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 07-MAR-2012 03:29:42

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (failover= on) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh2-vip)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = VPROD)))
OK (10 msec)


[oracle@vrh1 admin]$ cat loop1.sh
  nohup sqlplus su/su@SCAN_FAILOVER @verify.sql &
     sleep 1
  nohup sqlplus su/su@SCAN_FAILOVER @verify.sql &
     sleep 1
  nohup sqlplus su/su@SCAN_FAILOVER @verify.sql &
     sleep 1
  nohup sqlplus su/su@SCAN_FAILOVER @verify.sql &
     sleep 1


loop1.sh  failover using SCAN_FAILOVER

[oracle@vrh1 admin]$ cat nohup.out

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 03:33:40 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SID  SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
---- -------- ------------- --------------- -----------
197    12141 NONE          NONE            NO


INSTANCE_NAME
----------------
VPROD2

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 03:33:41 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SID  SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
---- -------- ------------- --------------- -----------
197    12147 NONE          NONE            NO


INSTANCE_NAME
----------------
VPROD2

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 03:33:42 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SID  SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
---- -------- ------------- --------------- -----------
197    12153 NONE          NONE            NO


INSTANCE_NAME
----------------
VPROD2

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 03:33:43 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SID  SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
---- -------- ------------- --------------- -----------
192    10629 NONE          NONE            NO


INSTANCE_NAME
----------------
VPROD2





loop2 failover using VIP_FAILOVER

[oracle@vrh1 admin]$ cat loop2.sh


  nohup sqlplus su/su@VIP_FAILOVER @verify.sql &
     sleep 1
  nohup sqlplus su/su@VIP_FAILOVER @verify.sql &
     sleep 1
  nohup sqlplus su/su@VIP_FAILOVER @verify.sql &
     sleep 1
  nohup sqlplus su/su@VIP_FAILOVER @verify.sql &
     sleep 1


[oracle@vrh1 admin]$ ./loop2.sh



[oracle@vrh1 admin]$ cat nohup.out

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 03:35:34 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0


Enter user-name:
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 03:35:35 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0


Enter user-name:
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 03:35:36 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0


Enter user-name:
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 03:35:37 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0