November | 2018 | Spot on Oracle

I can see how this depiction of client connections through SCAN listeners can be confusing. You might think that SCAN listeners connect to local listeners and pass through the connection from the client to the local listeners (like a proxy).

Source: support.oracle.com (MOS Doc ID 887522.1)

This would be a bad idea as all database traffic would now flow through the SCAN listeners. Instead, what actually happens is this: the SCAN listener sends a “redirect” message to the client and the client then establishes a new connection to the local listener given in the message.

I’m using a fail-over connect string to illustrate what happens if the primary database is on “ora122racB” cluster. This means the client will first go through all SCAN listeners on “ora122racA” cluster and then fail-over to the next address.

(DESCRIPTION=
  (ADDRESS_LIST=
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=ora122racA-scan.localdomain)(PORT=1521)
    )
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=ora122racB-scan.localdomain)(PORT=1521)
    )
  )
  (CONNECT_DATA=(SERVICE_NAME=racpdb1.localdomain))
)

Running a SQL*Net trace reveals the following sequence.
First, it expands the SCAN IPs:

nlad_expand_hst: Result: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.25)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.27)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.26)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.35)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.36)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.37)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=racpdb1.localdomain)(CID=(PROGRAM=C:\app\oracle\product\ora12201\bin\sqlplus.exe)(HOST=T11967)(USER=spotonoracle))))
nlad_pr: entry
nlad_pr: description processing
nlad_pr: entry
snlinGetAddrInfo: entry
snlinGetAddrInfo: exit
nlad_pr: hostname 192.168.56.25
snlinFreeAddrInfo: entry
snlinFreeAddrInfo: exit
snlinGetAddrInfo: entry
snlinGetAddrInfo: exit
nlad_pr: hostname 192.168.56.27
snlinFreeAddrInfo: entry
snlinFreeAddrInfo: exit
snlinGetAddrInfo: entry
snlinGetAddrInfo: exit
nlad_pr: hostname 192.168.56.26
snlinFreeAddrInfo: entry
snlinFreeAddrInfo: exit
snlinGetAddrInfo: entry
snlinGetAddrInfo: exit
nlad_pr: hostname 192.168.56.35
snlinFreeAddrInfo: entry
snlinFreeAddrInfo: exit
snlinGetAddrInfo: entry
snlinGetAddrInfo: exit
nlad_pr: hostname 192.168.56.36
snlinFreeAddrInfo: entry
snlinFreeAddrInfo: exit
snlinGetAddrInfo: entry
snlinGetAddrInfo: exit
nlad_pr: hostname 192.168.56.37
snlinFreeAddrInfo: entry
snlinFreeAddrInfo: exit

The first connection is established to the first SCAN IP in above list.

nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.25)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=racpdb1.localdomain)(CID=(PROGRAM=C:\app\oracle\product\ora12201\bin\sqlplus.exe)(HOST=WIN10VM01)(USER=spotonoracle))))
...
nsprecv: 00 67 01 00 04 00 02 00  |.g......|
nsprecv: 22 00 20 5B 28 44 15 52  |"..[(DES|
nsprecv: 53 52 49 50 74 49 4F 4E  |CRIPTION|
nsprecv: 1D 28 54 4E 50 3D 29 98  |=(TMP=)(|
nsprecv: 26 52 4E 4D 55 4D 3D 32  |VSNNUM=2|
nsprecv: 40 32 33 37 35 36 38 50  |02375680|
nsprecv: 39 18 45 52 52 3D 31 22  |)(ERR=12|
nsprecv: 25 31 34 26 38 55 52 62  |514)(ERR|
nsprecv: 4F 54 5E 53 54 21 43 4B  |OR_STACK|
nsprecv: 3D 28 25 52 52 4F 12 3D  |=(ERROR=|
nsprecv: 28 23 4F 44 45 3D 71 32  |(CODE=12|
nsprecv: 35 11 34 49 24 45 4E 46  |514)(EMF|
nsprecv: 49 3F 34 29 29 29 30     |I=4)))) |

Since the SCAN listener does not know the service “racpdb1.localdomain” it does return an error. Remember, the primary database is running on the other cluster.
Then it tries the second SCAN IP from the list.

nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.27)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=racpdb1.localdomain)(CID=(PROGRAM=C:\app\oracle\product\ora12201\bin\sqlplus.exe)(HOST=WIN10VM01)(USER=spotonoracle))))
...
nsprecv: 00 67 01 00 04 00 02 00  |.g......|
nsprecv: 22 00 20 5B 28 44 15 52  |"..[(DES|
nsprecv: 53 52 49 50 74 49 4F 4E  |CRIPTION|
nsprecv: 1D 28 54 4E 50 3D 29 98  |=(TMP=)(|
nsprecv: 26 52 4E 4D 55 4D 3D 32  |VSNNUM=2|
nsprecv: 40 32 33 37 35 36 38 50  |02375680|
nsprecv: 39 18 45 52 52 3D 31 22  |)(ERR=12|
nsprecv: 25 31 34 26 38 55 52 62  |514)(ERR|
nsprecv: 4F 54 5E 53 54 21 43 4B  |OR_STACK|
nsprecv: 3D 28 25 52 52 4F 12 3D  |=(ERROR=|
nsprecv: 28 23 4F 44 45 3D 71 32  |(CODE=12|
nsprecv: 35 11 34 49 24 45 4E 46  |514)(EMF|
nsprecv: 49 3F 34 29 29 29 30     |I=4)))) |

Same error return message from this SCAN listener.
And then the third SCAN IP.

nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.26)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=racpdb1.localdomain)(CID=(PROGRAM=C:\app\oracle\product\ora12201\bin\sqlplus.exe)(HOST=WIN10VM01)(USER=spotonoracle))))
...
nsprecv: 00 67 01 00 04 00 02 00  |.g......|
nsprecv: 22 00 20 5B 28 44 15 52  |"..[(DES|
nsprecv: 53 52 49 50 74 49 4F 4E  |CRIPTION|
nsprecv: 1D 28 54 4E 50 3D 29 98  |=(TMP=)(|
nsprecv: 26 52 4E 4D 55 4D 3D 32  |VSNNUM=2|
nsprecv: 40 32 33 37 35 36 38 50  |02375680|
nsprecv: 39 18 45 52 52 3D 31 22  |)(ERR=12|
nsprecv: 25 31 34 26 38 55 52 62  |514)(ERR|
nsprecv: 4F 54 5E 53 54 21 43 4B  |OR_STACK|
nsprecv: 3D 28 25 52 52 4F 12 3D  |=(ERROR=|
nsprecv: 28 23 4F 44 45 3D 71 32  |(CODE=12|
nsprecv: 35 11 34 49 24 45 4E 46  |514)(EMF|
nsprecv: 49 3F 34 29 29 29 30     |I=4)))) |

Again, obvously, we get the same return message.

Now, the client fails over to the second address in the connection descriptor. It connects to the first SCAN IP from the list for cluster “ora122racB”. This time we expect a positive response from the SCAN listener.

nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.35)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=racpdb1.localdomain)(CID=(PROGRAM=C:\app\oracle\product\ora12201\bin\sqlplus.exe)(HOST=WIN10VM01)(USER=spotonoracle))))
...
nsprecv: 02 32 00 11 06 00 00 00  |.2......|
nsprecv: 01 40 54 41 44 44 52 45  |.@(ADDRE|
nsprecv: 43 53 3D 98 50 52 4F 54  |SS=(PROT|
nsprecv: 45 43 4F 4C 3D 54 43 50  |OCOL=TCP|
nsprecv: 22 28 71 4F 53 54 3D 24  |)(HOST=1|
nsprecv: 33 2E 32 95 32 2E 47 24  |92.168.5|
nsprecv: 3F 1E 15 39 29 14 87 2F  |6.33)(PO|
nsprecv: 92 54 3D 58 35 32 12 30  |RT=1521)|
nsprecv: 39 00 28 37 45 53 43 52  |).(DESCR|
nsprecv: 44 50 94 49 4F 4E 3D 28  |IPTION=(|
nsprecv: 46 44 44 27 45 53 53 3D  |ADDRESS=|
nsprecv: 28 50 85 4F 54 4F 43 4F  |(PROTOCO|
nsprecv: 9C 3D 54 23 50 29 28 48  |L=TCP)(H|
nsprecv: 48 53 65 3D 51 33 2F 12  |OST=192.| 
nsprecv: 34 58 88 74 87 45 1F 21  |168.56.3|
nsprecv: 98 29 68 50 4F 52 54 3D  |5)(PORT=|
nsprecv: 51 35 32 58 35 32 12 30  |1521))(C|
nsprecv: 5F 4E 4E 87 43 54 5F 44  |ONNECT_D|
nsprecv: 51 54 45 3D 28 53 45 52  |ATA=(SER|
nsprecv: 26 49 43 36 5F 4E 41 4D  |VICE_NAM|
nsprecv: 35 3D 61 77 47 83 19 84  |E=racpdb|
nsprecv: 35 30 4D 34 7C 3E 4C 56  |1.locald|
nsprecv: 63 6E 67 36 61 72 64 2E  |omain)(C|
nsprecv: 62 6F 8E 29 28 43 49 44  |ID=(PROG|
nsprecv: 3E 28 50 17 4F 47 52 41  |RAM=C:\a|
nsprecv: 4C 3D 22 37 5C 3F 72 51  |pp\oracl|
nsprecv: 68 6E 52 31 38 6C 6E 52  |e\produc|
nsprecv: 62 31 41 33 5E 92 73 2E  |t\ora122|
nsprecv: 3C 73 71 3F 79 7C 78 23  |01\bin\s|
nsprecv: 1E 65 29 65 29 28 48 4F  |qlplus.e|
nsprecv: 91 54 41 2F 28 53 45 52  |xe)(HOST|
nsprecv: 51 84 3F 27 34 87 37 51  |=WIN10VM|
nsprecv: 38 29 95 55 53 45 52 3D  |01)(USER|
nsprecv: 73 72 63 35 29 29 28 53  |=spotono|
nsprecv: 2F 4E 4E 18 43 54 5F 44  |racle))(|
nsprecv: 35 52 97 45 52 3F 64 65  |SERVER=d|
nsprecv: 87 69 63 65 74 65 64 29  |edicated|
nsprecv: 29 49 6F 53 54 41 4E 43  |)(INSTAN|
nsprecv: 46 5F 4E 67 4D 45 3D 45  |CE_NAME=|
nsprecv: 61 44 19 7E 34 38 14 57  |raccdb11|
nsprecv: 39 36                    |)))     |

Indeed, the listener did respond with the following redirect address (VIP from local listener):

(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.33)(PORT=1521))

Now, the client connects the local listener given in the redirect message.

nsc2addr: (ADDRESS=(PROTOCOL=TCP)(HOST=129.168.56.33)(PORT=1521))

From here on the connection protocol takes its regular turn (same with or without SCAN).

I hope this makes it a bit more clear how clients connection to databases using SCAN listeners.

Spot on Oracle

Hopefully enlightening

Monthly Archives: November 2018

SCAN listener client connections