.
\nDo you have complex connection strings with DESCRIPTION_LIST, DESCRIPTION, ADDRESS_LIST, ADDRESS and a nice combination of FAILOVER and LOAD_BALANCE? You probably checked the documentation, telling you that FAILOVER=YES is the default at all levels, but LOAD_BALANCE=YES is the default only for DESCRIPTION_LIST. But when disaster recovery and availability is concerned, the documentation is not sufficient. I want to test it. And here is how I do it.
\n<\/p>\n
I don’t want to test it with the real configuration and stop the different instances. And I don’t need to. My way to test an address list is to define a tnsnames.ora with the connection string, such as the following:<\/p>\n
\nNET_SERVICE_NAME=\n (DESCRIPTION_LIST=\n (DESCRIPTION=\n (CONNECT_DATA=(SERVICE_NAME=pdb1))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=101))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=102))\n )\n (DESCRIPTION=\n (CONNECT_DATA=(SERVICE_NAME=pdb1))\n (ADDRESS_LIST=\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=201))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=202))\n )\n )\n )\n<\/code><\/pre>\nI used localhost because I know it’s there and I don’t want to wait for the TCP timeout. But I use fake ports, which do not exist. So finally, a connection will never be established but I will be able to see all that are tried. I check them with strace on the connect() system call, with the following script:<\/p>\n
\nfor i in {1..10}\ndo\nTNS_ADMIN=\/tmp strace -T -e trace=connect sqlplus -s -L sys\/oracle@NET_SERVICE_NAME as sysdba <<< \"\" 2>&1 | awk '\n\/sa_family=AF_INET, sin_port=htons\/{\n gsub(\/[()]\/,\" \") ; printf \"%s \",$5\n}\nEND{\n print \"\"\n}\n'\ndone | sort | uniq<\/code><\/pre>\nSo, I used meaningful numbers for my fake ports: 101 and 102 for the addresses in the first description of the description list, and 201 and 202 for the address list in the second description. The awk script shows the sequence that was tried. And, because of the random round robin, I run them in a loop several times to see all patterns, aggregated by sort|uniq<\/p>\n
So here is the result from the connection string above using the defaults for load balancing and failover:<\/p>\n
\n101 102 201 202\n201 202 101 102\n<\/code><\/pre>\nThe sequence within the address list is always in order (101,102 and 201,202) because LOAD_BALANCE=NO is the default there. But I have two combinations for the descriptions because LOAD_BALANCE=YES is the default in DESCRIPTION_LIST. Finally, all adresses are tried because FAILOVER=YES is the default at all levels.<\/p>\n
LOAD_BALANCE<\/h3>\n
If I define LOAD_BALANCE at all levels, such as:<\/p>\n
\nNET_SERVICE_NAME=\n (DESCRIPTION_LIST=(FAILOVER=YES)(LOAD_BALANCE=YES)\n (DESCRIPTION=(FAILOVER=YES)(LOAD_BALANCE=YES)\n (CONNECT_DATA=(SERVICE_NAME=pdb1))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=101))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=102))\n )\n (DESCRIPTION=(FAILOVER=YES)\n (CONNECT_DATA=(SERVICE_NAME=pdb1))\n (ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=YES)\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=201))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=202))\n )\n )\n )\n<\/code><\/pre>\nThe result shows that all combinations can be tried in any order:<\/p>\n
\n101 102 201 202\n101 102 202 201\n102 101 201 202\n102 101 202 201\n201 202 101 102\n201 202 102 101\n202 201 101 102\n202 201 102 101\n<\/code><\/pre>\nBy running it in a large loop you will confirm that any address will be tried at most once.<\/p>\n
FAILOVER<\/h3>\n
Now, If I set FAILOVER=NO within the first description:<\/p>\n
\nNET_SERVICE_NAME=\n (DESCRIPTION_LIST=(FAILOVER=YES)(LOAD_BALANCE= NO)\n (DESCRIPTION=(FAILOVER= NO)(LOAD_BALANCE=YES)\n (CONNECT_DATA=(SERVICE_NAME=pdb1))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=101))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=102))\n )\n (DESCRIPTION=(LOAD_BALANCE=NO )\n (CONNECT_DATA=(SERVICE_NAME=pdb1))\n (ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=YES)\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=201))\n (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=202))\n )\n )\n )\n<\/code><\/pre>\nthe first attempt can be 101 or 102 (because of LOAD_BALANCING) but only one will be tried in this address list, because of no failover. Then, the second description is attempted (because FAILOVER=YES at description list level) and with all addresses there (because of LOAD_BALANCING=YES). The result of all possible combinations is:<\/p>\n
\n101 201 202\n102 201 202\n102 202 201\n<\/code><\/pre>\nSo here it is. You can test any complex connection description to check what will be the possible connections and in which order they will be tried. From this, you can infer what will happen with a real configuration: the wait for TCP timeout for addresses tested on hosts that are not up, and the load balancing given be the different possible combinations.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . Do you have complex connection strings with DESCRIPTION_LIST, DESCRIPTION, ADDRESS_LIST, ADDRESS and a nice combination of FAILOVER and LOAD_BALANCE? You probably checked the documentation, telling you that FAILOVER=YES is the default at all levels, but LOAD_BALANCE=YES is the default only for DESCRIPTION_LIST. But when disaster recovery and availability is concerned, the […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[511,1294,96],"type_dbi":[],"class_list":["post-10835","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-failover","tag-load_balance","tag-oracle"],"acf":[],"yoast_head":"\n
(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO) - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO)\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . Do you have complex connection strings with DESCRIPTION_LIST, DESCRIPTION, ADDRESS_LIST, ADDRESS and a nice combination of FAILOVER and LOAD_BALANCE? You probably checked the documentation, telling you that FAILOVER=YES is the default at all levels, but LOAD_BALANCE=YES is the default only for DESCRIPTION_LIST. But when disaster recovery and availability is concerned, the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-02-15T05:02:57+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO)\",\"datePublished\":\"2018-02-15T05:02:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/\"},\"wordCount\":495,\"commentCount\":0,\"keywords\":[\"failover\",\"load_balance\",\"Oracle\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/\",\"name\":\"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO) - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-02-15T05:02:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO) - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/","og_locale":"en_US","og_type":"article","og_title":"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO)","og_description":"By Franck Pachot . Do you have complex connection strings with DESCRIPTION_LIST, DESCRIPTION, ADDRESS_LIST, ADDRESS and a nice combination of FAILOVER and LOAD_BALANCE? You probably checked the documentation, telling you that FAILOVER=YES is the default at all levels, but LOAD_BALANCE=YES is the default only for DESCRIPTION_LIST. But when disaster recovery and availability is concerned, the […]","og_url":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/","og_site_name":"dbi Blog","article_published_time":"2018-02-15T05:02:57+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO)","datePublished":"2018-02-15T05:02:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/"},"wordCount":495,"commentCount":0,"keywords":["failover","load_balance","Oracle"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/","url":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/","name":"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO) - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-02-15T05:02:57+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/description_listdescriptionaddress_listfailoveryesload_balanceno\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO)"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10835"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10835\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10835"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10835"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}