JDBC 连接SCAN-NAME有没有什么特殊要求？

2^#

发表于 2012-2-2 22:29:48

ODM Data:

Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides
a single name for clients to access Oracle Databases running in a cluster. The benefit is that the client’s connect
information does not need to change if you add or remove nodes in the cluster.

SCAN 的主要优势是对于client 更"便捷"。

If you choose Option 1, you must ask your network administrator to create a single name that resolves to 3 IP
addresses using a round-robin algorithm. Three IP addresses are recommended considering load balancing and high
availability requirements regardless of the number of servers in the cluster. The IP addresses must be on the same
subnet as your public network in the cluster. The name must be 15 characters or less in length, not including the
domain, and must be resolvable without the domain suffix (for example: “sales1-scan’ must be resolvable as opposed
to “scan1-scan.example.com”). The IPs must not be assigned to a network interface (on the cluster), since Oracle
Clusterware will take care of it.
Figure 2: Sample DNS entry for SCAN
You can check the SCAN configuration in DNS using “nslookup”. If your DNS is set up to provide round-robin
access to the IPs resolved by the SCAN entry, then run the “nslookup” command at least twice to see the round-robin
algorithm work. The result should be that each time, the “nslookup” would return a set of 3 IPs in a different order.
EZconnet sqlplus system/manager@sales1-scan:1521/oltp
JDBC connect jdbc:oracle:thin:@sales1-scan:1521/oltp
sales1-scan.example.com IN A 133.22.67.194
IN A 133.22.67.193
IN A 133.22.67.192

Oracle推荐 scan 使用三个IP 以保证负载均衡和高可用，当scan name resolve 成1个ip 时其single client access的特性不变。

3^#

发表于 2012-2-2 22:36:34

Question 2 :使用11G的SCAN-NAME来连接的话，有没有什么特殊要求啊？我这边是纯JDBC.THIN。。

没有大规模使用，不是很了解  参考以下文档

What is SCAN ?  Which version of JDBC supports SCAN ?

SCAN or Single Client Access Name is a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.  Having a single name to access the cluster allows clients to use the EZConnect client and the simple JDBC thin URL to access any database running in the clusters independently of which server(s) in the cluster the database is active. SCAN provides load balancing and failover of client connections to the database. The SCAN works as an IP alias for the cluster.

A typical JDBC URL using SCAN would look like:

jdbc:oracle:thin:@sales1-scan:1521/oltp

Additional Reading:

http://www.oracle.com/technetwor ... iew/scan-129069.pdf

SCAN feature is supported only from JDBC version 11.2.0.1.  This was one of the features added to 11.2 as a part of "IPv6 support".

Using SCAN With Oracle JDBC 11g Thin Driver [ID 1290193.1]
Applies to:
JDBC - Version: 11.1.0.7 to 11.2.0.2.0 - Release: 11.1 to 11.2
Information in this document applies to any platform.
Goal
With the introduction of 11g r2 RDBMS RAC, clusters can be created using SCAN (Single Client Access Name) as described in this white paper below:
http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
In this how to, we use a SCAN JDBC URL to verify that we can load balance connections to the RAC nodes using a SCAN address.
Solution
Verify SCAN Setup
Log into one of you RAC nodes and verify the tnsnames.ora entry for your service as shown below. If you have a valid service created, there will exist an entry in your $ORACLE_HOME/network/admin/tnsnames.ora file.
pas_srv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apctcsol1)(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pas_srv)
)
)
Test using SQL*Plus as shown below to verify you can connect.
[oradb1@auw2k3 admin]$ sqlplus scott/tiger@pas_srv
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 1 09:23:03 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
Run some SQL as follows to check the instance you are connected to.
SQL> col "Instance" format a25
SQL> col "Hostname" format a25
SQL> col "Service" format a25
SQL> select sys_context('userenv', 'instance_name') as "Instance",
2 sys_context('userenv', 'server_host') as "Hostname",
3 sys_context('userenv', 'service_name') as "Service"
4 from dual;
Instance Hostname Service
------------------------- ------------------------- -------------------------
A12 auw2k4 pas_srv
SQL>
Repeat #2 and #3 to verify you are load balancing nodes from SQL*Plus.
SQL> col "Instance" format a25
SQL> col "Hostname" format a25
SQL> col "Service" format a25
SQL>
SQL> select sys_context('userenv', 'instance_name') as "Instance",
2 sys_context('userenv', 'server_host') as "Hostname",
3 sys_context('userenv', 'service_name') as "Service"
4 from dual;
Instance Hostname Service
------------------------- ------------------------- -------------------------
A11 auw2k3 pas_srv
Determine the SCAN host/port by connecting to any of the RAC instances in the cluster as shown below.
SQL> show parameter remote_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string apctcsol1:1521
SQL>
Check the SCAN listener is running as shown by the 2 commands below.
[oradb1@auw2k3 admin]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
[oradb1@auw2k3 admin]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node auw2k3
[oradb1@auw2k3 ~]$ srvctl config scan
SCAN name: apctcsol1, Network: 1/10.187.80.0/255.255.254.0/eth1
SCAN VIP name: scan1, IP: /apctcsol1.au.oracle.com/10.187.80.129
Steps
The following demo can be run to verify your SCAN JDBC URL is load balancing between the RAC nodes.
Download the 11.2.0.2 JDBC driver "ojdbc6.jar" from the following location
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html
Ensure you have JDK 1.6 in your path
C:\temp\notes>java -version
java version "1.6.0_21"
Java(TM) SE Runtime Environment (build 1.6.0_21-b07)
Java HotSpot(TM) 64-Bit Server VM (build 17.0-b17, mixed mode)
Download the source code below onto your file system in the same directory where you have "ojdbc6.jar"
LoadBalanceTestSCAN.java
File system would look as follows.
C:\temp\notes>dir
Volume in drive C is OS
Volume Serial Number is 7C37-0C64
Directory of C:\temp\notes
01/02/2011 01:12 PM <DIR> .
01/02/2011 01:12 PM <DIR> ..
01/02/2011 01:12 PM 3,271 LoadBalanceTestSCAN.java
14/10/2010 09:22 AM 2,152,051 ojdbc6.jar
3 File(s) 2,158,900 bytes
2 Dir(s) 249,171,558,400 bytes free
Edit the code below to specify your SCAN JDBC URL and database username and password.
public final String userId = "scott";
public final String password = "tiger";
private static final String url =
"jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv";
Note: The format of the JDBC URL is as follows.
jdbc:oracle:thin:@{scan-listener-hostname}:{port}/{service-name}
You could also use a URL as follows.
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apctcsol1.au.oracle.com)(PORT=1521))(LOAD_BALANCE=YES)(CONNECT_DATA=
(SERVER=DEDICATED)(SERVICE_NAME=pas_srv)))
6. Compile as follows.
C:\temp\notes>javac -cp ojdbc6.jar;. LoadBalanceTestSCAN.java
Run as follows.
C:\temp\notes>java -cp ojdbc6.jar;. LoadBalanceTestSCAN
Test Started at Tue Feb 01 13:12:46 EST 2011
Obtaining 5 connections
using URL : jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv
=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============
Connection #0 : instance[A11], host[auw2k3], service[pas_srv]
Connection #1 : instance[A12], host[auw2k4], service[pas_srv]
Connection #2 : instance[A11], host[auw2k3], service[pas_srv]
Connection #3 : instance[A12], host[auw2k4], service[pas_srv]
Connection #4 : instance[A12], host[auw2k4], service[pas_srv]
Closing Connections
Test Ended at Tue Feb 01 13:12:47 EST 2011
If you find the JDBC Thin does not load balance among the available scan vips/listeners. Then you could be hitting the following unpublished bug 8812294 - TB: PERFORMANCE: JDBC THIN NOT LOAD-BALANCING AMONG SCAN IP'S

复制代码

4^#

发表于 2012-2-2 23:34:07

已知 jdbc 连接scan存在的一些问题列表：

Which JDBC Driver Versions Support SCAN? [ID 1150835.1]
JDBC TCPS Connections Do Not Failover For SCAN Listeners [ID 1289244.1]
Bug 11769174: JDBC THIN CONNECTION TO SCAN LISTENER FAIL OVER VPN WITH ORA-12520

Bug 10385510 - JDBC TCPS connections via SCAN listeners fail when one of the listeners is down [ID 10385510.8]
Bug 10385510: JDBC TCPS CONNECTIONS DO NOT FAILOVER USING SCAN LISTENERS
11.2.0.2 Client JDBC Thin Connection To SCAN Address Fails With java.sql.SQLException: IO Error: Socket read timed out [ID 1328358.1]
Bug 12594002: JDBC THIN FAILS TO CONNECT TO SCAN LISTENER USING TCPS - NL EXCEPTION
Bug 11853850: JDBC THIN CONNECTIONS OVER SCAN AND NAT FAIL WITH ORA-12516
Bug 13572091: CANNOT CONNECT TO SCAN LISTENER VIA JDBC THIN CLIENT
Bug 12651778 - JDBC thin fails to connect via a SCAN listener when using TCPS and SSL [ID 12651778.8]

How to Access a RAC Database configured with SCAN via Java Thin Driver [ID 1081179.1]

5^#

发表于 2012-2-3 14:36:28

有点迷惑了，SCAN是GRID的功能，还是DB的功能？
如果是个11G的GRID，但是是个10G的DB，还可以用吗？

6^#

发表于 2012-2-3 15:10:08

回复 5# 的帖子

提问之前为啥不先问下Metalink呢？ MOS 是 oracle Premier Support的重要部分，不用真的很可惜。

文档
How to integrate a 10g/11gR1 RAC database with 11gR2 clusterware (SCAN) [ID 1058646.1] 介绍了该问题

While 10g/11gR1 sqlnet does not support certain aspects of this new feature, it is still possible to configure 10g/11gR1 clients and database instances to integrate with the 11gR2 Grid infrastructure using the SCAN. This document provides an example on how to implement this.

即10g/11gR1 的SQLNET 本身是不支持该特性的，但还是可以实现 10g/11gR1的client 和数据库实例利用scan的。

The purpose of this document is to demonstrate how to integrate a 10g (or 11.1) database with 11gR2 Grid Infrastructure.
With 11gR2 Grid, client connections are supposed to use the Single Client Access Name (SCAN) to connect to instances in a RAC cluster.
While 10g/11gR1 sqlnet does not support certain aspects of this new feature, it is still possible to configure 10g/11gR1 clients and database instances to integrate with the 11gR2 Grid infrastructure using the SCAN. This document provides an example on how to implement this.
Please note that there is no need to use the SCAN VIPs when running a 10g/11gR1 database under 11gR2 Grid, it is perfectly fine to continue use the node listeners, both for instance registration as well as client connect strings.
The solution outlined in this document requires that the SCAN VIPs are fixed, hence it cannot be used if the SCAN VIPs are dynamically allocated via GNS (using DHCP).
Solution
Pre-11gR2 clients/database instances do not support multiple IP addresses associated with the same host name (SCAN), consequently the 11gR2 syntax should not be used for the remote_listener parameter or the client connect string as it probably would only register with/connect to the first VIP of the SCAN only.
The VIPs of the SCAN need to be determined, the SCAN can have between 1 to 3 IP addresses associated with it:
oragrid@racnode1 $ nslookup cluster1_scan.rac.example.com
Server: dns1
Address: 10.1.10.183#53
Name: cluster1_scan.rac.example.com
Address: 10.1.10.200
Name: cluster1_scan.rac.example.com
Address: 10.1.10.201
Name: cluster1_scan.rac.example.com
Address: 10.1.10.202
Using above information, TNS aliases for instance parameters local_listener / remote_listener can be created. As these aliases are used by the 10g/11gR1 database instances they need to go into the tnsnames.ora accessed by these instances - default location is $ORACLE_HOME/network/admin. Please note that the alias for 'local_listener' uses the node VIPs, while the alias for 'remote_listener' uses the SCAN VIPs. This matches the setup for 11gR2 databases - see the documentation.
LISTENER_DB10G1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
LISTENER_DB10G2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
LISTENERS_GRID =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.200)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.201)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.202)(PORT = 1521))
)
Set the local_listener/remote_listener parameters for the 10g/11gR1 database instances, please note that the value for 'sid' is case sensitive and needs to match the actual values in the SPFILE:
alter system set local_listener='LISTENER_DB10G1' scope=spfile sid='DB10G1';
alter system set local_listener='LISTENER_DB10G2' scope=spfile sid='DB10G2';
alter system set remote_listener='LISTENERS_GRID' scope=spfile sid='*';
There should be a CRS service configured in the OCR for clients to connect this database - e.g.:
$ srvctl status service -d DB10G
Service DB10G_SCAN is running on instance(s) DB10G1, DB10G2
A corresponding client connect string could look like this:
DB10G_VIA_SCAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.200)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.201)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.202)(PORT = 1521))
)
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB10G_SCAN)
)
)
To check if the 10g/11gR1 database instances have registered their services names with the SCAN listeners the command 'lsnrctl services scan_listener' can be used, however since the SCAN can run on any node of the cluster, it first needs to be established on which node the SCAN listeners are currently running:
$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node racnode2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node racnode1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node racnode1
Please note that CRS dynamically adds entries for the SCAN listeners to the listener.ora under $GRID_HOME/network/admin,so for the following command to succeed, it needs to be executed from the GRID_HOME on the node that currently runs the SCAN listener and environment variables like ORACLE_HOME, TNS_ADMIN (if set) need to point to the GRID_HOME.
All SCAN listeners should show the service names of the 10g/11gR1 database instances - e.g.:
grid@racnode2 $ lsnrctl service LISTENER_SCAN1
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-FEB-2010 14:26:33
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "DB10G" has 2 instance(s).
Instance "DB10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1-vip)(PORT=1521))
Instance "DB10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2-vip)(PORT=1521))
...
Service "DB10G_SCAN" has 2 instance(s).
Instance "DB10G1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1-vip)(PORT=1521))
Instance "DB10G2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2-vip)(PORT=1521))

复制代码

7^#

发表于 2012-2-3 15:11:34

PS : it is still possible to configure 10g/11gR1 clients and database instances to integrate with the 11gR2 Grid infrastructure using the SCAN.

这句话其实也指出了 SCAN 是 11gR2 Grid Infrastructure GI的功能

8^#

发表于 2012-2-3 20:36:15

有点没看懂上面这个案例，它讲的是SCAN对应3个IP的状况，我这个是SCAN对应1个IP的状况。
不知道他这里为什么要在OCR里单独注册一个新的服务？There should be a CRS service configured in the OCR for clients to connect this database
看样子，他这里是通过先向OCR里单独注册一个新的服务，然后由CRS 自动向SCAN 注册？Please note that CRS dynamically adds entries for the SCAN listeners to the listener.ora

我这里尝试直接把 10G的remote_listener 注册到 SCAN 上了，貌似也没什么问题。

有没有问题啊？请指教！

9^#

发表于 2012-2-3 20:39:33

10GR2 RAC:
SQL> alter system set remote_listener = 'scan-name:1521';

11GR2 GRID:
[oracle@node1 ~]$ lsnrctl stat scan-name

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-2月 -2012 20:02:19

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

Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.45)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                   LISTENER_SCAN1
Version                TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date             09-1月 -2012 14:14:55
Uptime                   25 days 5 hr. 47 min. 27 sec
Trace Level             off
Security                ON: Local OS Authentication
SNMP                   OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File       /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.45)(PORT=1521)))
Services Summary...
Service "aaa" has 2 instance(s).
  Instance "aaa1", status READY, has 1 handler(s) for this service...
  Instance "aaa2", status READY, has 1 handler(s) for this service...
Service "aaa_XPT" has 2 instance(s).
  Instance "aaa1", status READY, has 1 handler(s) for this service...
  Instance "aaa2", status READY, has 1 handler(s) for this service...
Service "bbb" has 2 instance(s).
  Instance "bbb1", status READY, has 1 handler(s) for this service...
  Instance "bbb2", status READY, has 1 handler(s) for this service...
The command completed successfully

aaa 为 10G 服务，bbb为 11G 服务。

[ 本帖最后由武汉-SSH 于 2012-2-3 20:41 编辑 ]

10^#

发表于 2012-2-3 20:43:56

[oracle@node1 ~]$ java -cp ojdbc5.jar:. LoadBalanceTestSCAN
Test Started at Fri Feb 03 20:05:42 CST 2012
Obtaining 5 connections
using URL : jdbc:oracle:thin:@192.168.1.45:1521/aaa

=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.3.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============
Connection #0 : instance[aaa1], host[node1], service[aaa]
Connection #1 : instance[aaa2], host[node2], service[aaa]
Connection #2 : instance[aaa1], host[node1], service[aaa]
Connection #3 : instance[aaa2], host[node2], service[aaa]
Connection #4 : instance[aaa1], host[node1], service[aaa]
Closing Connections
Test Ended at Fri Feb 03 20:05:43 CST 2012

11^#

发表于 2012-2-3 20:50:15

回复 8# 的帖子

没有时间和可用的环境测试，猜测如你所说仅是自动和手动的区别，这个结论未必准确。

12^#

发表于 2012-2-3 20:50:33

换了一个10G的JDBC:
[oracle@node1 ~]$ java -cp ojdbc14.jar:. LoadBalanceTestSCAN
Test Started at Fri Feb 03 20:50:37 CST 2012
Obtaining 5 connections
using URL : jdbc:oracle:thin:@192.168.1.45:1521/aaa

=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 10.2.0.4.0
JDBC Driver Major Version is 10
JDBC Driver Minor Version is 2
=============
Connection #0 : instance[aaa1], host[node1], service[aaa]
Connection #1 : instance[aaa2], host[node2], service[aaa]
Connection #2 : instance[aaa1], host[node1], service[aaa]
Connection #3 : instance[aaa2], host[node2], service[aaa]
Connection #4 : instance[aaa1], host[node1], service[aaa]
Closing Connections
Test Ended at Fri Feb 03 20:50:38 CST 2012