Google Plus LinkedIn

Single Client Access Name (SCAN)

 

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 = eult92-vip.sgp.st.com)(Port = 1541))
    (ADDRESS = (PROTOCOL = TCP)(Host = eult91-vip.sgp.st.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.sgp.st.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.sgp.st.com:1541/rac.world
  • JDBC connect jdbc:oracle:thin:@rac.sgp.st.com:1541/rac.world

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@eult91 ~]# nslookup rac.sgp.st.com
Server:         164.129.154.205
Address:        164.129.154.205#53
 
Non-authoritative answer:
Name:   rac.sgp.st.com
Address: 164.129.226.45
Name:   rac.sgp.st.com
Address: 164.129.226.46
Name:   rac.sgp.st.com
Address: 164.129.226.43

Using this SCAN name should return the three IP addresses in a rolling fashion, which can be tested with ping command:

[root@eult91 ~]# ping -c 1 rac.sgp.st.com
PING rac.sgp.st.com (164.129.226.43) 56(84) bytes of data.
64 bytes from rac.sgp.st.com (164.129.226.43): icmp_seq=1 ttl=64 time=0.029 ms
 
--- rac.sgp.st.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@eult91 ~]# ping -c 1 rac.sgp.st.com
PING rac.sgp.st.com (164.129.226.46) 56(84) bytes of data.
64 bytes from rac.sgp.st.com (164.129.226.46): icmp_seq=1 ttl=64 time=0.030 ms
 
--- rac.sgp.st.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@eult91 ~]# ping -c 1 rac.sgp.st.com
PING rac.sgp.st.com (164.129.226.45) 56(84) bytes of data.
64 bytes from rac.sgp.st.com (164.129.226.45): icmp_seq=1 ttl=64 time=0.129 ms
 
--- rac.sgp.st.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@eult91 ~]# ping -c 1 rac.sgp.st.com
PING rac.sgp.st.com (164.129.226.43) 56(84) bytes of data.
64 bytes from rac.sgp.st.com (164.129.226.43): icmp_seq=1 ttl=64 time=0.029 ms
 
--- rac.sgp.st.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@eult91 ~]# /ora_crs/software/bin/srvctl stop scan_listener
[root@eult91 ~]# /ora_crs/software/bin/srvctl stop scan
[root@eult91 ~]# /ora_crs/software/bin/srvctl modify scan -n rac.sgp.st.com
[root@eult91 ~]# /ora_crs/software/bin/srvctl modify scan_listener -u
[root@eult91 ~]# /ora_crs/software/bin/srvctl start scan_listener
[root@eult91 ~]# /ora_crs/software/bin/srvctl config scan
SCAN name: rac.sgp.st.com, Network: 1/164.129.226.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /rac.sgp.st.com/164.129.226.46
SCAN VIP name: scan2, IP: /rac.sgp.st.com/164.129.226.43
SCAN VIP name: scan3, IP: /rac.sgp.st.com/164.129.226.45
[root@eult91 ~]# /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@eult91 ~]$ cd $TNS_ADMIN
[oragrid@eult91 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@eult91 admin]$ cat endpoints_listener.ora
LISTENER_EULT91=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=eult91-vip)(PORT=1531))(ADDRESS=(PROTOCOL=TCP)(HOST=164.129.226.29)(PORT=1531)(IP=FIRST))))
[oragrid@eult91 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      eult91-vip.sgp.st.com:1531
remote_listener                      string

Which you do with something like (spfile usage):

SQL> ALTER SYSTEM SET local_listener='eult91-vip.sgp.st.com:1531' sid='+ASM1';
 
SYSTEM altered.
 
SQL> ALTER SYSTEM SET local_listener='eult92-vip.sgp.st.com:1531' sid='+ASM2';
 
SYSTEM altered.

For database instances:

SQL> show parameter liste
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      eult91-vip.sgp.st.com:1531
remote_listener                      string      rac.sgp.st.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.sgp.st.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@eult91 ~]$ 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/eult91/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=164.129.226.29)(PORT=1531)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=164.129.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.sgp.st.com" has 1 instance(s).
  Instance "rac1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oragrid@eult91 ~]$ 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.sgp.st.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@eult91 ~]$ 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/eult91/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=164.129.226.46)(PORT=1541)))
Services Summary...
Service "rac.sgp.st.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@eult91 ~]$ 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.sgp.st.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=164.129.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=164.129.226.41)(PORT=1531)))
The command completed successfully

Testing

I initially configured local_listener for my database to eult91-vip.sgp.st.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@eult91 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.sgp.st.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=164.129.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=164.129.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.sgp.st.com)(PORT = 1541))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rac.sgp.st.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

About 

    Find more about me on:
  • googleplus
  • linkedin

2 comments to Single Client Access Name (SCAN)

  • Davor

    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.

    • 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):

      local_listener                       string      eult91-vip.sgp.st.com:1531
      remote_listener                      string      rac.sgp.st.com:1541

      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…

Leave a Reply

  

  

  


*

You can use these HTML tags

<a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>