Table of contents
Introduction
Single Client Access Name (SCAN) is 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 biggest benefit is same and single name in SQL*Net configuration of your clients whatever you add or remove nodes in your cluster. Means that you do not need to create an entry like:
RAC.WORLD = (DESCRIPTION = (LOAD_BALANCE = on) (FAILOVER = on) (ADDRESS = (PROTOCOL = TCP)(Host = server2-vip.domain.com)(Port = 1541)) (ADDRESS = (PROTOCOL = TCP)(Host = server1-vip.domain.com)(Port = 1541)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac.world) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 5) (DELAY = 5) ) ) ) |
You would now create something like:
RAC_SCAN.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac.domain.com)(PORT = 1541)) ) (CONNECT_DATA = (SERVICE_NAME = rac.world) ) ) |
SCAN provides load balancing and failover for client connections to the database as well as cluster alias for databases in the cluster.
Example of EZConnect and Thin JDBC Connect Strings:
- EZconnet sqlplus yjaquier/password@rac.domain.com:1541/rac.world
- JDBC connect jdbc:oracle:thin:@rac.domain.com:1541/rac.world
Single Client Access Name (SCAN) configuration
First you need to ask a DNS configuration to have your RAC cluster name answering to multiple IP addresses. Oracle recommend 3 IP addresses for load balancing and high availability:
[root@server1 ~]# nslookup rac.domain.com Server: 192.168.154.205 Address: 192.168.154.205#53 Non-authoritative answer: Name: rac.domain.com Address: 192.168.226.45 Name: rac.domain.com Address: 192.168.226.46 Name: rac.domain.com Address: 192.168.226.43 |
Using this SCAN name should return the three IP addresses in a rolling fashion, which can be tested with ping command:
[root@server1 ~]# ping -c 1 rac.domain.com PING rac.domain.com (192.168.226.43) 56(84) bytes of data. 64 bytes from rac.domain.com (192.168.226.43): icmp_seq=1 ttl=64 time=0.029 ms --- rac.domain.com ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 0ms rtt min/avg/max/mdev = 0.029/0.029/0.029/0.000 ms [root@server1 ~]# ping -c 1 rac.domain.com PING rac.domain.com (192.168.226.46) 56(84) bytes of data. 64 bytes from rac.domain.com (192.168.226.46): icmp_seq=1 ttl=64 time=0.030 ms --- rac.domain.com ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 0ms rtt min/avg/max/mdev = 0.030/0.030/0.030/0.000 ms [root@server1 ~]# ping -c 1 rac.domain.com PING rac.domain.com (192.168.226.45) 56(84) bytes of data. 64 bytes from rac.domain.com (192.168.226.45): icmp_seq=1 ttl=64 time=0.129 ms --- rac.domain.com ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 0ms rtt min/avg/max/mdev = 0.129/0.129/0.129/0.000 ms [root@server1 ~]# ping -c 1 rac.domain.com PING rac.domain.com (192.168.226.43) 56(84) bytes of data. 64 bytes from rac.domain.com (192.168.226.43): icmp_seq=1 ttl=64 time=0.029 ms --- rac.domain.com ping statistics --- 1 packets transmitted, 1 received, 0% packet loss, time 0ms rtt min/avg/max/mdev = 0.029/0.029/0.029/0.000 ms |
If this has been done before the installation then at Oracle level all should be fine unless follow How to Modify SCAN Setting after Installation [ID 972500.1] and issue (same SCAN name as supplied during installation and same subnetwork):
[root@server1 ~]# /ora_crs/software/bin/srvctl stop scan_listener [root@server1 ~]# /ora_crs/software/bin/srvctl stop scan [root@server1 ~]# /ora_crs/software/bin/srvctl modify scan -n rac.domain.com [root@server1 ~]# /ora_crs/software/bin/srvctl modify scan_listener -u [root@server1 ~]# /ora_crs/software/bin/srvctl start scan_listener [root@server1 ~]# /ora_crs/software/bin/srvctl config scan SCAN name: rac.domain.com, Network: 1/192.168.226.0/255.255.255.0/eth0 SCAN VIP name: scan1, IP: /rac.domain.com/192.168.226.46 SCAN VIP name: scan2, IP: /rac.domain.com/192.168.226.43 SCAN VIP name: scan3, IP: /rac.domain.com/192.168.226.45 [root@server1 ~]# /ora_crs/software/bin/srvctl config scan_listener SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1541 SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1541 SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1541 |
Remark:
In the following oragrid is Unix account of Grid Infrastructure installation and orarac is Unix account of Oracle database installation.
All listeners configuration is done in Grid Infrastructure network directory i.e. $GRID_HOME/network/admin (/u01/app/grid/11.2.0/network/admin if you are using Optimal Flexible Architecture (OFA)). The files you should find here are small and you should not edit them manually:
[oragrid@server1 ~]$ cd $TNS_ADMIN [oragrid@server1 admin]$ cat listener.ora LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent [oragrid@server1 admin]$ cat endpoints_listener.ora LISTENER_server1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server1-vip)(PORT=1531))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.226.29)(PORT=1531)(IP=FIRST)))) [oragrid@server1 admin]$ cat sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = /u01/app/oracle |
In network directory of your database installation i.e. $DATABASE_HOME/network/admin (or /u01/app/oracle/11.2.0/network/admin if you are using Optimal Flexible Architecture (OFA)) you have nothing, except maybe a tnsnames.ora file.
For all databases (ASMs and databases instance) you have to configure the following initialization parameters:
For ASM instances:
SQL> show parameter listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string local_listener string server1-vip.domain.com:1531 remote_listener string |
Which you do with something like (spfile usage):
SQL> ALTER SYSTEM SET local_listener='server1-vip.domain.com:1531' sid='+ASM1'; SYSTEM altered. SQL> ALTER SYSTEM SET local_listener='server2-vip.domain.com:1531' sid='+ASM2'; SYSTEM altered. |
For database instances:
SQL> show parameter liste NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string local_listener string server1-vip.domain.com:1531 remote_listener string rac.domain.com:1541 |
If when changing remote_listener parameter you get:
ORA-02097: parameter cannot be modified because specified VALUE IS invalid ORA-00119: invalid specification FOR SYSTEM parameter REMOTE_LISTENER ORA-00132: syntax error OR unresolved network name 'rac.domain.com:1541' |
Make sure you added EZCONNECT connection method in sqlnet.ora !! Apparently SCAN listener registration is based on this SQL*Net method…
Then if database fail to register in listener you may issue:
SQL> ALTER SYSTEM register; SYSTEM altered. |
To magically finally see:
[oragrid@server1 ~]$ lsnrctl status listener LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-DEC-2012 18:51:35 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-DEC-2012 14:19:57 Uptime 0 days 4 hr. 31 min. 37 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/grid/11.2.0/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/server1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.226.29)(PORT=1531))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.226.39)(PORT=1531))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "rac.domain.com" has 1 instance(s). Instance "rac1", status READY, has 1 handler(s) for this service... The command completed successfully [oragrid@server1 ~]$ lsnrctl services listener LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-DEC-2012 18:51:58 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "rac.domain.com" has 1 instance(s). Instance "rac1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully |
And:
[oragrid@server1 ~]$ lsnrctl status listener_scan1 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-DEC-2012 18:52:32 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN1 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 18-DEC-2012 16:09:46 Uptime 0 days 2 hr. 42 min. 46 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/grid/11.2.0/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/server1/listener_scan1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.226.46)(PORT=1541))) Services Summary... Service "rac.domain.com" has 2 instance(s). Instance "rac1", status READY, has 1 handler(s) for this service... Instance "rac2", status READY, has 1 handler(s) for this service... The command completed successfully [oragrid@server1 ~]$ lsnrctl services listener_scan1 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-DEC-2012 18:52:46 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) Services Summary... Service "rac.domain.com" has 2 instance(s). Instance "rac1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 state:ready REMOTE SERVER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.226.39)(PORT=1531))) Instance "rac2", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready REMOTE SERVER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.226.41)(PORT=1531))) The command completed successfully |
Single Client Access Name (SCAN) testing
I initially configured local_listener for my database to server1-vip.domain.com:1541 and it gave me a very strange behavior, all connections where failing for one of below error (either if local on one RAC node or from my desktop):
ERROR: ORA-12516: TNS:listener could not find available handler with matching protocol stack ERROR: ORA-12520: TNS:listener could not find available handler for requested type of server |
I did not noticed it immediately but state of my listener was blocked (see below):
[oragrid@server1 admin]$ lsnrctl services LISTENER_SCAN1 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-DEC-2012 18:19:48 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) Services Summary... Service "rac.domain.com" has 2 instance(s). Instance "rac1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:blocked REMOTE SERVER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.226.39)(PORT=1541))) Instance "rac2", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:blocked REMOTE SERVER (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.226.41)(PORT=1541))) The command completed successfully |
In your 11gR2 client tnsnames.ora you have to put something like:
RAC_SCAN.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac.domain.com)(PORT = 1541)) ) (CONNECT_DATA = (SERVICE_NAME = rac.domain.com) ) ) |
You can then test it with:
C:\Users\yjaquier>sqlplus yjaquier/password@rac_scan SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 2 17:10:49 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- rac1 |
In another session:
C:Usersyjaquier>sqlplus yjaquier/password@rac_scan SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 2 17:11:08 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- rac2 |
We see than the SCAN tnsnmaes.ora entry do automatic load balancing between the cluster node.
Remark:
Note that client pre-11gR2 are not compatible with SCAN.
References
- http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
- 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained [ID 887522.1]
- How to Modify SCAN Setting after Installation [ID 972500.1]
- How to Troubleshoot Connectivity Issue with 11gR2 SCAN Name [ID 975457.1]
Davor says:
Hi, first of all great post. I followed your steps, but I get the same message as you in “Testing” part: ORA-12516: TNS:listener could not find available handler with matching protocol stack. That’s happening only if I’m connecting via some client, local connecting is ok. Also, state of my listener is blocked, so, what is cause of this problem?
Best regards.
Yannick Jaquier says:
Hi,
Thanks for comment !
My problem was wrong setting of local_listener initialization parameters, at the end I set up (for the RAC database not the ASM one):
Setting to blank local_listener was not possible (default value instead) so the setting using local listener port (1531) while remote listener is set up using SCAN port (1541). One drawback I see is possible connection using local listener and so 1531 to RAC database…
Good luck in your set up…