SCAN listener and Node listener – How does it work

http://www.mydbspace.com/?

p=324

Single Client Access Name (SCAN) is new feature of oracle 11gR2 grid infrastructure. This idea make client transparent to database servers. Any change in terms of adding deleting nodes and even moving database cluster to a new datacenter can be achieved without changing anything in the client. That makes JDBC URL to use just a simple name to access the cluster independent of node name and database SIDs.

Scan listeners running on RAC only accept connections and pass them onto a Node listener.

When the client makes a connection, Connection packet is sent to a Scan listener. Connection is then redirected to a Node Listener. Node Listener forks the shadow process for the dedicated connection to use or passes the connection onto dispatcher for shared server connection model.

The most intense operation a TNS listener does, the fork (bequeath) of a shadow process is completed by the Node listener.

Let us see how we can configure node listener for each of the database running on the RAC cluster. I have configured SCAN listener in 1521. I have two databases TST, PRD running on a two node cluster. My node names arerock,water. These are the status of my SCAN listeners.

LISTENER_SCAN1 is no node water:

+ASM2:water.localdomain:oracle$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 09:02:19

Copyright (c) 1991, 2009, 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.1.0 – Production
Start Date 28-JUL-2011 13:50:50
Uptime 3 days 19 hr. 11 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/grid/11.2.0.1/network/admin/listener.ora
Listener Log File /opt/app/grid/11.2.0.1/log/diag/tnslsnr/water/listener_scan1/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.203)(PORT=1521)))
Services Summary…
Service “PRD” has 2 instance(s).
Instance “PRD1″, status READY, has 1 handler(s) for this service…
Instance “PRD2″, status READY, has 1 handler(s) for this service…
Service “TST” has 2 instance(s).
Instance “TST1″, status READY, has 1 handler(s) for this service…
Instance “TST2″, status READY, has 1 handler(s) for this service…
The command completed successfully

LISTENER_SCAN2, LISTENER_SCAN3 are on node rock

+ASM1:rock.localdomain:oracle$ lsnrctl status LISTENER_SCAN2

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 09:03:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
STATUS of the LISTENER
————————
Alias LISTENER_SCAN2
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 28-JUL-2011 13:47:46
Uptime 3 days 19 hr. 15 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/grid/11.2.0.1/network/admin/listener.ora
Listener Log File /opt/app/grid/11.2.0.1/log/diag/tnslsnr/rock/listener_scan2/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.201)(PORT=1521)))
Services Summary…
Service “PRD” has 2 instance(s).
Instance “PRD1″, status READY, has 1 handler(s) for this service…
Instance “PRD2″, status READY, has 1 handler(s) for this service…
Service “TST” has 2 instance(s).
Instance “TST1″, status READY, has 1 handler(s) for this service…
Instance “TST2″, status READY, has 1 handler(s) for this service…
The command completed successfully

+ASM1:rock.localdomain:oracle$

+ASM1:rock.localdomain:oracle$ lsnrctl status LISTENER_SCAN3

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 09:01:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
STATUS of the LISTENER
————————
Alias LISTENER_SCAN3
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 28-JUL-2011 17:06:33
Uptime 3 days 15 hr. 54 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/grid/11.2.0.1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/rock/listener_scan3/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.202)(PORT=1521)))
Services Summary…
Service “PRD” has 2 instance(s).
Instance “PRD1″, status READY, has 1 handler(s) for this service…
Instance “PRD2″, status BLOCKED, has 1 handler(s) for this service…
Service “TST” has 2 instance(s).
Instance “TST1″, status READY, has 1 handler(s) for this service…
Instance “TST2″, status READY, has 1 handler(s) for this service…
The command completed successfully

Node listener settings are:
On rock

+ASM1:rock.localdomain:oracle$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 09:10:31

Copyright (c) 1991, 2009, 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.1.0 – Production
Start Date 28-JUL-2011 13:47:51
Uptime 3 days 19 hr. 22 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/grid/11.2.0.1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/rock/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.132)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.111)(PORT=1521)))
Services Summary…
Service “+ASM” has 1 instance(s).
Instance “+ASM1″, status READY, has 1 handler(s) for this service…
Service “PRD” has 1 instance(s).
Instance “PRD1″, status READY, has 1 handler(s) for this service…
The command completed successfully

On water

+ASM1:rock.localdomain:oracle$ lsnrctl status LISTENER_TST

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 09:11:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TST)))
STATUS of the LISTENER
————————
Alias LISTENER_TST
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 28-JUL-2011 13:47:56
Uptime 3 days 19 hr. 23 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/grid/11.2.0.1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/rock/listener_tst/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_TST)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.132)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.111)(PORT=1523)))
Services Summary…
Service “TST” has 1 instance(s).
Instance “TST1″, status READY, has 1 handler(s) for this service…
The command completed successfully

My local_listener and romte_listener settings are

PRD1:rock.localdomain:oracle$ si

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 09:05:23 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter listener

NAME TYPE
———————————— ———————————
VALUE
——————————

local_listener string
(DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=rock
-vip)(PORT=1521))))
remote_listener string
rac-scan:1521
SQL>

TST1:rock.localdomain:oracle$ si

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 09:06:20 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter listener

NAME TYPE
———————————— ———————————
VALUE
——————————

local_listener string
(DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=rock
-vip)(PORT=1523))))
remote_listener string
rac-scan:1521
SQL>

On node water:

TST2:water.localdomain:oracle$ si

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 09:07:53 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>
SQL> show parameter listener

NAME TYPE
———————————— ———————————
VALUE
——————————

local_listener string
(DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=wate
r-vip)(PORT=1523))))
remote_listener string
rac-scan:1521
SQL>

PRD2:water.localdomain:oracle$ si

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 09:08:33 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter listener

NAME TYPE
———————————— ———————————
VALUE
——————————

local_listener string
(DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=wate
r-vip)(PORT=1521))))
remote_listener string
rac-scan:1521
SQL>

This is how my environment looks like if I put it in picture.

From the SCAN listener status above, It is clear that all SCAN listeners are aware of the node listeners no matter where node listener is listening on (1523, 1521). PMON registers with SCAN listener based on the remote_listener setting on database. And PMON also register with node listener based on the local listener settings. Local/node listener will always point to the local VIP for the node while the remote_listener will point to SCAN_NAME:Port.

Now let us try connecting with TAF enable connect string.

PRDTAF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRD)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))))

TSTTAF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TST)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))))

[oracle@linux1 admin]$ sqlplus system/oracle@TSTTAF

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 08:51:11 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
TST2

SQL>

Now my connection to TST went to TST2 instance on node water.

+ASM2:water.localdomain:oracle$ lsnrctl services LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 08:52:17

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary…
Service “PRD” has 1 instance(s).
Instance “PRD1″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rock-vip)(PORT=1521)))
Service “TST” has 2 instance(s).
Instance “TST1″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rock-vip)(PORT=1523)))
Instance “TST2″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=water-vip)(PORT=1523)))
The command completed successfully

+ASM2:water.localdomain:oracle$ lsnrctl services LISTENER_TST

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 15:54:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TST)))
Services Summary…
Service “TST” has 1 instance(s).
Instance “TST2″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:1 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Now the established connection count is 1 for LISTENER_TST. But for SCAN listener established connection count actually increased on LISTENER_SCAN3. That means LISTENER_SCAN3 actually received the request from client and forwarded to LISTENER_TST on water.

+ASM1:rock.localdomain:oracle$ lsnrctl services LISTENER_SCAN2

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 15:56:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Services Summary…
Service “PRD” has 2 instance(s).
Instance “PRD1″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:1 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rock-vip)(PORT=1521)))
Instance “PRD2″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=water-vip)(PORT=1521)))
Service “TST” has 2 instance(s).
Instance “TST1″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rock-vip)(PORT=1523)))
Instance “TST2″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=water-vip)(PORT=1523)))
The command completed successfully
+ASM1:rock.localdomain:oracle$ lsnrctl services LISTENER_SCAN3

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-AUG-2011 15:57:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
Services Summary…
Service “PRD” has 2 instance(s).
Instance “PRD1″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rock-vip)(PORT=1521)))
Instance “PRD2″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=water-vip)(PORT=1521)))
Service “TST” has 2 instance(s).
Instance “TST1″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rock-vip)(PORT=1523)))
Instance “TST2″, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:1 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=water-vip)(PORT=1523)))
The command completed successfully
+ASM1:rock.localdomain:oracle$

Now let us see how the failover works

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 16:00:10 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
TST2

SQL>

Now issue a ‘shutdown abort’ on TST2.

TST2:water.localdomain:oracle$ si

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 16:02:24 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

Let us see if my connection failed over to TST1 on node rock. Yes it did without an issue.

[oracle@linux1 admin]$ sqlplus system/oracle@TSTTAF

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 16:00:10 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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
TST2

SQL> select instance_name from v$instance;

INSTANCE_NAME
—————-
TST1

SQL>

Now we can pass the SCAN name and port to application installer or admin team and create as many node listeners as required. We don’t need to let application team to know different connection string and port for different databases on same RAC cluster. You can note that I used SCAN (rac-scan) and port (1521) for both my databases. I segregated my listeners on 1521 and 1523 to PRD and TST databases respectively.