Differences

This shows you the differences between two versions of the page.

Link to this comparison view

en:oracle_connection_strings [2016/12/16 10:16] (current)
Line 1: Line 1:
 +Last modified: Jul 27, 2015 by Aïdel\\
 +\\
 +
 +====== Oracle Connection Strings ======
 +
 +\\
 +\\
 +
 +=====  Introduction ​ =====
 +
 +CC-IN2P3 provides an Oracle database service based on a cluster infrasctructure,​ many nodes into the same cluster can access concurently to the same database.\\
 +Since May 2014, the CC-IN2P3 has strengthlened the Oracle database infrastructure by implementing a standby infrastructure.\\
 +In this topic, we will introduce how you can connect to an Oracle database and afterwards you will see how you take benefit of the standby infrascture.
 +=====  How can I connect me to an Oracle database ?  =====
 +
 +All oracle clients use a connexion string to identify the server name and the service name. Usually, users used to define a TNS description which looks like to this one :
 +<​code>​
 +(DESCRIPTION =
 +    (ADDRESS_LIST =
 +   ​(LOAD_BALANCE=on)
 +      (ADDRESS = (PROTOCOL = TCP)(HOST = ccdbovo01.in2p3.fr)(PORT = 1521))
 +      (ADDRESS = (PROTOCOL = TCP)(HOST = ccdbovo02.in2p3.fr)(PORT = 1521))
 +    )
 +    (CONNECT_DATA =
 +      (SERVICE_NAME = myservice.in2p3.fr)
 +    )
 +  )
 +</​code>​
 +The Oracle client interprets the connection string and connect itself to the database by taking into account all the options. One interesting option is LOAD_BALANCE which is by default to ON. This option defines the rebalancing strategy. When it is enabled, the client requests will be split on the various nodes to balance the load. Otherwise all client requests will be directed to the nodes following the round robin algorithm.
 +
 +
 +
 +
 +
 +
 +
 +
 +{{:​connectionstringlbno.jpg?​500x300}}
 +
 +
 +
 +
 +Pict 1 : connection string in round robin
 +
 +
 +
 +
 +
 +
 +{{:​connectionstringlbon.jpg?​500x300}}
 +
 +
 +
 +
 +Pict 2 : connection string with rebalancing
 +
 +
 +
 +In some cases, few applications can use a TNS alias which is defined in the tnsnames.ora file. Users using AFS can consult all the TNS aliases in the file $TNS_ADMIN/​tnsnames.ora.
 +
 +
 +
 +In case of a node replacement (ie hardware failure or outdated hardware), all or one of the node name can change and that will involve an update of the connection string on the client side.
 +
 +In a distributed environment,​ a change on the connection string may prove to be a difficult, this is why the CC-IN2P3 performs all its efforts to make transparent the location of Oracle servers.
 +
 +In order to minimize the impact of the hardware onto the connexion string , node names are hidden behind network aliases .
 +
 +
 +
 +From version 11gR2, Oracle has simplified the connexion string with the introduction of Single Client Access Name (SCAN) implemented by à Virtual IP. This SCAN virtualizes all the machines of a cluster into one virtual machine. Thank to this feature the software connection string does not care about the number of nodes in the Oracle cluster and node names.
 +<​code>​
 +(DESCRIPTION =
 +    (ADDRESS_LIST =
 +      (ADDRESS = (PROTOCOL = TCP)(HOST = ccscanvo.in2p3.fr)(PORT = 1521))
 +    )
 +    (CONNECT_DATA =
 +      (SERVICE_NAME = myservice.in2p3.fr)
 +    )
 +)
 +</​code>​
 +SCAN feature is supported only from client version above 11.2.0.1 .
 +
 +SCAN acts like a mediator, it receives all connection requests and redirects them automatically to a node. Since version 11gR2, it is possible to rebalance connections according to different parameters: load level, connection number or response time. Following the selected strategy, the new connexion will be rerouted to the node with the lowest load/​connection number/​response time. By defaut, all our services are based on the load rebalancing.
 +=====  How can I use Data Guard ?  =====
 +
 +
 +====  How Data Guard works ?  ====
 +
 +A database logs all updates on its data through transaction logs called redo logs. These redo logs guarantee the database consistency in case of crash.
 +
 +By replaying the redo logs on a remote infrastructure,​ it is possible to implement a synchronized full copy of a production database. This database copy is called a standby database and the synchronisation mechanism is called dataguard.
 +
 +In case of crash, Oracle clients can automatically switch to the standby database if the string connexion is correctly set up.
 +
 +
 +
 +
 +
 +
 +{{:​connectionstringdg.jpg?​500x300}}
 +
 +
 +
 +
 +Pict 2 : connection string using Data Guard
 +
 +
 +
 +
 +
 +1 - A client requests a new connection to the server or scan but all production servers are down due to an upgrade or a hardware failure. The database administrator can decide to make a switchover to the standby servers.
 +
 +
 +
 +
 +
 +2 - After many failed attempts to the production SCAN, if the failover is completed and if the connection string is well configurated,​ the client will automatically try to connect to the standby servers (new production servers).
 +
 +
 +
 +
 +
 +3 - The connection will be established to the standby servers.
 +
 +
 +====  How can I use Data Guard ?  ====
 +
 +As described in the first section, Oracle client interprets a connection string . Among optional parameters, one is dedicated to dataguard, it's named FAILOVER.
 +
 +By default this parameter is to OFF but for using dataguard it would have to be to ON. When you enable FAILOVER, it is required to identify where is the failover infrastrcuture it’s why in your connection string it is required to reference the standby server. Below is listed all connections string and recommand you to use one of them based on the service name value of your current connection string :
 +<​code>​
 +(DESCRIPTION =
 +    (ADDRESS_LIST =
 +   ​(LOAD_BALANCE=on)
 +      (ADDRESS = (PROTOCOL = TCP)(HOST = ccdbovo01.in2p3.fr)(PORT = 1521))
 +      (ADDRESS = (PROTOCOL = TCP)(HOST = ccdbovo02.in2p3.fr)(PORT = 1521))
 +    )
 +    (CONNECT_DATA =
 +      (SERVICE_NAME = myservice.in2p3.fr)
 +    )
 +  )
 +</​code>​
 +**Updating your connection string is highly recommended,​ if you do not then your application could not take benefit of the high availability infrastructure and the database service could be unavailable for hours during hardware or software intervention**
 +
 +
 +
 +WARNING: Before updating your production connection string, please validate it on a local client.
 +
 +If you don't know how to test it, ppen an [[https://​cc-usersupport.in2p3.fr/​otrs/​index.pl|OTRS ticket]] . 
 +===  For client < 11.2.0.1 ​ ===
 +
 +You have to use the template in the right column and to replace the variable $SERVICE_NAME by the name of your service (i.e: chesmettaf.in2p3.fr,​ amonins.in2p3.fr,​ km3net.in2p3.fr,​...) prior to test it.
 +^Service name                                                                                                                                                                   ​^Connection string ​                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               ^
 +|agata.in2p3.fr,​ antares.in2p3.fr,​ babar.in2p3.fr,​ calice.in2p3.fr,​ d0.in2p3.fr,​ eros.in2p3.fr,​ exogam.in2p3.fr,​ km3net.in2p3.fr,​ paris.in2p3.fr,​ pauger.in2p3.fr,​ sdss.in2p3.fr|<​code>​
 + ​(DESCRIPTION_LIST= ​
 +   ​(LOAD_BALANCE=off) ​         ​
 +   ​(FAILOVER=on)
 +   ​(DESCRIPTION= ​
 +           ​(ADDRESS_LIST= ​
 +                 ​(LOAD_BALANCE=on) ​
 +                 ​(ADDRESS=(PROTOCOL=TCP)(HOST=ccdbovo01.in2p3.fr)(PORT=1521))
 +                 ​(ADDRESS=(PROTOCOL=TCP)(HOST=ccdbovo02.in2p3.fr)(PORT=1521))
 +           ​) ​
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))
 +  ) 
 +  (DESCRIPTION=
 +    (ADDRESS_LIST= ​
 +      (LOAD_BALANCE=on)
 +                 ​(ADDRESS=(PROTOCOL=TCP)(HOST= ccdbodgvo.in2p3.fr)(PORT=1521))
 +   ​) ​
 +               ​(CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))
 +  )
 + )
 + </​code> ​                                                                                            |
 +|chesmettaf.in2p3.fr ​                                                                                                                                                           |<​code>​
 + ​(DESCRIPTION_LIST= ​
 +  (LOAD_BALANCE=off) ​         ​
 +  (FAILOVER=on)
 +          (DESCRIPTION= ​
 +           ​(ADDRESS_LIST= ​
 +                (LOAD_BALANCE=on) ​
 +             ​(ADDRESS=(PROTOCOL=TCP)(HOST=ccdbogrid01.in2p3.fr)(PORT=1521))
 +      (ADDRESS=(PROTOCOL=TCP)(HOST=ccdbogrid02.in2p3.fr)(PORT=1521)))
 +                (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))
 +    )
 +          (DESCRIPTION=
 +    (ADDRESS_LIST= ​
 +     ​(LOAD_BALANCE=on)
 +                (ADDRESS=(PROTOCOL=TCP)(HOST= ccdbodggrid.in2p3.fr)(PORT=1521))) ​
 +               ​(CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))
 +  )
 + )
 + </​code> ​                                                                                                         |
 +|dedwentaf.in2p3.fr ​                                                                                                                                                            ​|<​code>​
 + ​(DESCRIPTION_LIST= ​
 +   ​(LOAD_BALANCE=off) ​         ​
 +   ​(FAILOVER=on)
 +         ​(DESCRIPTION= ​
 +         ​(ADDRESS_LIST= ​
 +                 ​(LOAD_BALANCE=on) ​
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccdbocc01.in2p3.fr)(PORT=1521))
 +      (ADDRESS=(PROTOCOL=TCP)(HOST=ccdbocc02.in2p3.fr)(PORT=1521))
 +   )
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))
 +    )
 +         ​(DESCRIPTION=
 +     ​(ADDRESS_LIST= ​
 +     ​(LOAD_BALANCE=on)
 +                (ADDRESS=(PROTOCOL=TCP)(HOST= ccdbodgcc.in2p3.fr)(PORT=1521))) ​
 +               ​(CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))
 +   )
 +  )
 + </​code> ​                                                                                                           |
 +|aliop.in2p3.fr,​amonins.in2p3.fr,​amontaf.in2p3.fr ​                                                                                                                              ​|<​code>​
 + ​(DESCRIPTION_LIST=
 +         ​(LOAD_BALANCE=off)
 +         ​(FAILOVER=on)
 +         ​(DESCRIPTION=
 +                 ​(ADDRESS_LIST=
 +                         ​(LOAD_BALANCE=on)
 +                         ​(ADDRESS=(PROTOCOL=TCP)(HOST=ccdbointra01.in2p3.fr)(PORT=1521))
 +                         ​(ADDRESS=(PROTOCOL=TCP)(HOST=ccdbointra02.in2p3.fr)(PORT=1521))
 +                 )
 +                 ​(CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))
 +         )
 +         ​(DESCRIPTION=
 +                 ​(ADDRESS_LIST=
 +                 ​(LOAD_BALANCE=on)
 +                 ​(ADDRESS=(PROTOCOL=TCP)(HOST= ccdbodgintra.in2p3.fr)(PORT=1521)))
 +                 ​(CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))
 +         )
 +  )
 + </​code>​|
 +|edmstaf.in2p3.fr ​                                                                                                                                                              ​|<​code>​
 + ​(DESCRIPTION = 
 +     ​(ADDRESS_LIST = 
 +           ​(LOAD_BALANCE=on)
 +           ​(ADDRESS = (PROTOCOL = TCP)(HOST = ccdboedms01.in2p3.fr)(PORT = 1521)) ​
 +           ​(ADDRESS = (PROTOCOL = TCP)(HOST = ccdboedms02.in2p3.fr)(PORT = 1521))
 +     ​) ​
 +     ​(CONNECT_DATA = (SERVICE_NAME = edmsdbtaf.in2p3.fr)
 + )
 + </​code> ​                                                                                                                                                                                                                                                                                                                                                                                                           |
 +|ccdev11g.in2p3.fr ​                                                                                                                                                             |<​code>​
 + ​(DESCRIPTION =
 +     ​(ADDRESS_LIST =
 +       ​(ADDRESS = (PROTOCOL = TCP)(HOST = ccdbodev01.in2p3.fr)(PORT = 1521))
 +       ​(ADDRESS = (PROTOCOL = TCP)(HOST = ccdbodev02.in2p3.fr)(PORT = 1521))
 +     )
 +     ​(CONNECT_DATA =
 +       ​(SERVICE_NAME = ccdev11gtaf.in2p3.fr)
 +     )
 +  ) 
 + 
 + </​code> ​                                                                                                                                                                                                                                                                                                                                                                                                                                |
 +
 +
 +===  Oracle client >= 11.2.0.1 ​ ===
 +
 +You have to use the template in the right column and to replace the variable $SERVICE_NAME by the name of your service (i.e: chesmettaf.in2p3.fr,​ amonins.in2p3.fr,​ km3net.in2p3.fr,​...) prior to test it.
 +^Service name                                                                                                                                                                  ^Connection string ​                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               ^
 +|agata.in2p3.fr,​ antares.in2p3.fr,​ babar.in2p3.fr,​calice.in2p3.fr,​ d0.in2p3.fr,​ eros.in2p3.fr,​ exogam.in2p3.fr,​ km3net.in2p3.fr,​ paris.in2p3.fr,​ pauger.in2p3.fr,​ sdss.in2p3.fr|<​code>​
 + ​(DESCRIPTION_LIST=
 +          (LOAD_BALANCE=off)
 +          (FAILOVER=on)
 +          (DESCRIPTION=
 +        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
 +            (ADDRESS_LIST=
 +              (LOAD_BALANCE=on)
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanovo.in2p3.fr)(PORT=1521)))
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME)))
 +          (DESCRIPTION=
 +        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
 +            (ADDRESS_LIST=
 +              (LOAD_BALANCE=on)
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanodgvo.in2p3.fr)(PORT=1521)))
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))))
 + </​code> ​     |
 +|chesmettaf.in2p3.fr ​                                                                                                                                                          ​|<​code>​
 + ​(DESCRIPTION_LIST=
 +          (LOAD_BALANCE=off)
 +          (FAILOVER=on)
 +          (DESCRIPTION=
 +        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
 +            (ADDRESS_LIST=
 +              (LOAD_BALANCE=on)
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanogrid.in2p3.fr)(PORT=1521)))
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME)))
 +          (DESCRIPTION=
 +        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
 +            (ADDRESS_LIST=
 +              (LOAD_BALANCE=on)
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanodggrid.in2p3.fr)(PORT=1521)))
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))))
 + </​code> ​ |
 +|dedwentaf.in2p3.fr ​                                                                                                                                                           |<​code>​
 + ​(DESCRIPTION_LIST=
 +          (LOAD_BALANCE=off)
 +          (FAILOVER=on)
 +          (DESCRIPTION=
 +        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
 +            (ADDRESS_LIST=
 +              (LOAD_BALANCE=on)
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanocc.in2p3.fr)(PORT=1521)))
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME)))
 +          (DESCRIPTION=
 +        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
 +            (ADDRESS_LIST=
 +              (LOAD_BALANCE=on)
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanodgcc.in2p3.fr)(PORT=1521)))
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))))
 + </​code> ​     |
 +|aliop.in2p3.fr,​amonins.in2p3.fr,​amontaf.in2p3.fr ​                                                                                                                             |<​code>​
 + ​(DESCRIPTION_LIST=
 +          (LOAD_BALANCE=off)
 +          (FAILOVER=on)
 +          (DESCRIPTION=
 +        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
 +            (ADDRESS_LIST=
 +              (LOAD_BALANCE=on)
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanointra.in2p3.fr)(PORT=1521)))
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME)))
 +          (DESCRIPTION=
 +        (CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
 +            (ADDRESS_LIST=
 +              (LOAD_BALANCE=on)
 +              (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanodgintra.in2p3.fr)(PORT=1521)))
 +            (CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME))))
 + </​code>​|
 +|ccdev11gtaf.in2p3.fr ​                                                                                                                                                         |<​code>​
 + ​(DESCRIPTION_LIST= ​
 +  (LOAD_BALANCE=off) ​         ​
 +  (FAILOVER=on)
 +          (DESCRIPTION= ​
 +           ​(ADDRESS_LIST= ​
 +               ​(LOAD_BALANCE=on) ​
 +            (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanodev.in2p3.fr)(PORT=1521))
 +                  )
 +            (CONNECT_DATA=(SERVICE_NAME=ccdev11gtaf.in2p3.fr))
 +  )
 +  ) 
 +  ​
 + </​code> ​                                                                                                                                                                                                                                                                                                                                                 |
 +|symodtaf.in2p3.fr,​symodins,​symodweb ​                                                                                                                                          ​|<​code>​
 + ​(DESCRIPTION_LIST=
 +  (LOAD_BALANCE=off)
 +  (FAILOVER=on)
 +          (DESCRIPTION=
 +           ​(ADDRESS_LIST=
 +               ​(LOAD_BALANCE=on)
 +            (ADDRESS=(PROTOCOL=TCP)(HOST=ccscanosymod.in2p3.fr)(PORT=1521)))
 +     ​(CONNECT_DATA=(SERVICE_NAME=symodtaf.in2p3.fr))
 +   )
 +          (DESCRIPTION=
 +   ​(ADDRESS_LIST=
 +   ​(LOAD_BALANCE=on)
 +               ​(ADDRESS=(PROTOCOL=TCP)(HOST= ccscanodgsymod.in2p3.fr)(PORT=1521)))
 +              (CONNECT_DATA=(SERVICE_NAME=symodtaf.in2p3.fr)))
 +    )
 + </​code> ​                                                                                                                                                                       |
 +
 +
  
  • en/oracle_connection_strings.txt
  • Last modified: 2016/12/16 10:16
  • (external edit)