Forum Stats

  • 3,741,282 Users
  • 2,248,404 Discussions
  • 7,861,719 Comments

Discussions

Difference between Service Name and SID.

Psystec
Psystec Member Posts: 5
edited Jun 2, 2017 1:37AM in General Database Discussions

Hello Fellow Earthlings

I'm a bit new when it comes to creating Oracle drivers and I was the lucky winner assigned to make an oracle driver.

My driver works but, the connection string that the driver creates bypasses TNSnames, meaning its a bit of a manual string creation.

Currently my driver only replaces the SID variable and does not cater for Service_Names.

I know that Service Names are used in Cluster's and SID is a unique DB name usually, but beyond this is know nothing more.

Questions:

What is the difference between a Service Name and SID in the TNSnames? (technical answer if possible)

Can I use both in the TNSnames?

Kind Regards and thanks in advance.

Tagged:
PsystecJuanMCharles M

Best Answer

  • ddf_dba
    ddf_dba Member Posts: 1,398 Bronze Trophy
    edited May 26, 2017 9:53AM Accepted Answer

    An SID is specific to a database, it is unique in an environment and 'points' to one, and only one, database in that environment. A service name can be associated with one or more SIDs; think about a RAC environment where each instance is uniquely named yet all can be accessed through the SERVICE_NAME:

    SID                  SERVICE_NAME

    bob1                 bob

    bob2                 bob

    bob3                 bob

    bob4                 bob

    Through the 'magic' of the tnsnames.ora file all four of those SIDs can be associated with the SERVICE_NAME bob, and if load balancing is configured, the listener will 'balance' the workload across all four SIDs.  This doesn't stop you from connecting to bob1 all of the time if you want to, you just need to NOT use the SERVICE_NAME and use the SID.

    Think of the SERVICE_NAME as a town and each SID as a house in that town;  you can choose to drive to town and see who's home (using the SERVICE_NAME) or you can make a direct trip to one of those houses (using the SID).  SERVICE_NAME is a much more flexible choice.  Dynamic registration uses the SERVICE_NAME and allows one or more SIDs to be serviced by that SERVICE_NAME (no pun intended).  If you run ''lsnrctl services' you'll see how the SERVICE_NAME and SID relate:

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

    Services Summary...

    Service "fnorg" has 1 instance(s).    <==== SERVICE_NAME

      Instance "fnorg", status READY, has 1 handler(s) for this service...   <==== SID

        Handler(s):

          "DEDICATED" established:0 refused:0 state:ready

             LOCAL SERVER

    In a 2-node RAC environment it could look like this:

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

    Services Summary...

    Service "fnorg" has 1 instance(s).

      Instance "fnorg1", status READY, has 1 handler(s) for this service...

        Handler(s):

          "DEDICATED" established:0 refused:0 state:ready

             LOCAL SERVER

      Instance "fnorg2", status READY, has 1 handler(s) for this service...

        Handler(s):

          "DEDICATED" established:0 refused:0 state:ready

             LOCAL SERVER

    Multiple SIDs can be associated with a single SERVICE_NAME which is why SERVICE_NAME is much more flexible.

    David Fitzjarrell

    PsystecPsystecCharles M

Answers

  • ddf_dba
    ddf_dba Member Posts: 1,398 Bronze Trophy
    edited May 26, 2017 9:53AM Accepted Answer

    An SID is specific to a database, it is unique in an environment and 'points' to one, and only one, database in that environment. A service name can be associated with one or more SIDs; think about a RAC environment where each instance is uniquely named yet all can be accessed through the SERVICE_NAME:

    SID                  SERVICE_NAME

    bob1                 bob

    bob2                 bob

    bob3                 bob

    bob4                 bob

    Through the 'magic' of the tnsnames.ora file all four of those SIDs can be associated with the SERVICE_NAME bob, and if load balancing is configured, the listener will 'balance' the workload across all four SIDs.  This doesn't stop you from connecting to bob1 all of the time if you want to, you just need to NOT use the SERVICE_NAME and use the SID.

    Think of the SERVICE_NAME as a town and each SID as a house in that town;  you can choose to drive to town and see who's home (using the SERVICE_NAME) or you can make a direct trip to one of those houses (using the SID).  SERVICE_NAME is a much more flexible choice.  Dynamic registration uses the SERVICE_NAME and allows one or more SIDs to be serviced by that SERVICE_NAME (no pun intended).  If you run ''lsnrctl services' you'll see how the SERVICE_NAME and SID relate:

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

    Services Summary...

    Service "fnorg" has 1 instance(s).    <==== SERVICE_NAME

      Instance "fnorg", status READY, has 1 handler(s) for this service...   <==== SID

        Handler(s):

          "DEDICATED" established:0 refused:0 state:ready

             LOCAL SERVER

    In a 2-node RAC environment it could look like this:

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

    Services Summary...

    Service "fnorg" has 1 instance(s).

      Instance "fnorg1", status READY, has 1 handler(s) for this service...

        Handler(s):

          "DEDICATED" established:0 refused:0 state:ready

             LOCAL SERVER

      Instance "fnorg2", status READY, has 1 handler(s) for this service...

        Handler(s):

          "DEDICATED" established:0 refused:0 state:ready

             LOCAL SERVER

    Multiple SIDs can be associated with a single SERVICE_NAME which is why SERVICE_NAME is much more flexible.

    David Fitzjarrell

    PsystecPsystecCharles M
  • User_WVSC7
    User_WVSC7 Member Posts: 258 Blue Ribbon
    edited May 26, 2017 10:13AM

    if you have the Oracle RAC always use the Service name to connect to the database  to have the load balancing across all nodes in the cluster,if not All you can use is the SID to connect to the Database.

    Hope this helps

    -Bhavani Dhulipalla

  • ddf_dba
    ddf_dba Member Posts: 1,398 Bronze Trophy
    edited May 26, 2017 10:30AM
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited May 26, 2017 11:16AM

    Dude, even though it is getting rare to see any other protocol besides TCP being specified I believe that other network protocols are still technically possible.  I expect that both SNA and Decnet are still supported on the appropriate platforms.

    - -

    To address the idea that you should always use the database service name with RAC I will disagree.  With some applications it is desirable to direct specific DML tasks to specific instances to reduce cross instance contention.  You can do this by referencing the SID in the connection or better yet creating a service in the database that connects to only specific instances.  In the tnsnames.ora file you can create an alias for a failover (as opposed to load balancing) connection so that the connections always go one instance if available and another instance if the first instance is not available.

    - -

    HTH -- Mark D Powell --

  • tarfu_dba
    tarfu_dba Member Posts: 134
    edited May 26, 2017 1:43PM

    Let me add to the confusion...from an 11.2 EE perspective if things have changed in 12c...

    Service names can be used with both single instance and clustered databases.  Yes, you can specify both in a single TNS entry, but it's more flexible to use only a service name, especially with a RAC database.  I didn't do any extensive testing, but it appears that the service_name takes precedence over the SID, and will work even if the SID is configured but doesn't actually exist; if the service_name is incorrect, the connection fails even if the SID is correct.

    SID identifies a database instance on a server, the SID name showing up on the instance process names (i.e. ora_smon_<SID>).  On a server, SIDs are unique, but, the same SID can be active on different servers.  It's the combination of hostname and SID that points to a single instance.  SID is more like a single mailbox number in a post office; many post offices can have that same mailbox number, but a mailbox number is unique in one post office.

    SERVICE_NAME is a logical name that an instance registers with the listener when it starts (the default being the database global name db_name dot db_domain).  The name is just a convenience: the Finance users can configure service name FINANCE.WORLD in their TNS entry, the HR users can configure HR.WORLD, etc., but both service names get them connected to database CORP whose instances are started with init.ora service_names='FINANCE.WORLD','HR.WORLD'.  To make things more flexible (and confusing) different instances for the same cluster database can register different service names; for example, instances 1 and 2 can register FINANCE.WORLD, instance 3 can register HR.WORLD, and instance 4 can be left unconfigured in which case it will default to CORP.WORLD (the database global name).  All instances will accept connections to CORP.WORLD regardless of what other specific service_names have been configured for them.

    When the listener gets a connection attempt with a SID, it looks to see if that SID has registered with it and passes off the connection to it.

    When the listener gets a connection attempt with a service name, it looks up what instances (SIDs) have registered with that service name and passes the connection off to one of those instances.  Many instances, even related to different databases, can register the same service name with a listener; which one a connection is established with will be determined by other factors such as how busy a particular instance is (more accurately how busy an instance tells its listener that it is) or simply round-robin.  Typically, you have a service name related to a single database if you want to be sure of what database you're getting into.

    PsystecPsystec
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited May 29, 2017 8:02PM

    I had to hit the internet to check out exactly what that was and after ignoring the Australian Steel standards I found Dec Alpha 4100.  There is one for sale on Amazon.

    - -

    https://www.amazon.com/DEC-AS4100-Alphaserver-Flickr-Sharing/dp/B017O2A71S

    - -

    we ran RAC on DEC Alphas though I cannot remember the model.

    - -

    -- Mark D Powell --

  • Psystec
    Psystec Member Posts: 5
    edited May 31, 2017 9:31AM

    Thank you, you where also very helpful.

  • Psystec
    Psystec Member Posts: 5
    edited May 31, 2017 9:34AM

    Thank you for a very nice explanation.

  • JuanM
    JuanM Member Posts: 2,146 Gold Trophy
    edited May 31, 2017 9:34AM
    Psystec wrote:Hello Fellow EarthlingsI'm a bit new when it comes to creating Oracle drivers and I was the lucky winner assigned to make an oracle driver.My driver works but, the connection string that the driver creates bypasses TNSnames, meaning its a bit of a manual string creation.Currently my driver only replaces the SID variable and does not cater for Service_Names.I know that Service Names are used in Cluster's and SID is a unique DB name usually, but beyond this is know nothing more.Questions:What is the difference between a Service Name and SID in the TNSnames? (technical answer if possible)Can I use both in the TNSnames?Kind Regards and thanks in advance.

    Here some concepts you should to understand.

    https://docs.oracle.com/cd/E11882_01/network.112/e41945/concepts.htm#NETAG002

    Read the concepts carefully and probably you can find answers to your questions.

    PsystecPsystec
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 31, 2017 10:58AM

    David,

    It also goes the other way - a given database instance (let's call it a SID in this case) can have multiple services associated with it, whether in single instance or RAC configurations. Yes, in RAC, it can easily be used to direct specific connections (say for a "reporting" service) to a certain instance/instances (and to move that service to another instance if one crashes), but it can also be used to do interesting things in a single instance. For example, you can set up several services on a single instance and use the Resource Manager to set up different resource limits depending on the service that was used to connect.

    J

    JuanM
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 31, 2017 11:07AM

    Mark,

    I'll disagree with your disagreement. In a RAC environment (where presumably, you're concerned at least in part with high availability), using a SID to connect is brittle. If an instance becomes unavailable, then the connection doesn't work. If, however, you use a service (which could be assigned to your preferred instance), you have the ability to deal with failures without having to do failover configuration at the client. Services can have preferred instances as well as available instances such that if the preferred instance fails, the service will be instantiated on one of the other instances. cf https://docs.oracle.com/cd/B28359_01/rac.111/b28252/configwlm.htm

    J

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited May 31, 2017 3:28PM

    John, from prior post >> better yet creating a service in the database that connects to only specific instances  << so I am not sure exactly how much we disagree except that in our case we do not want some applications to automatically fail over.  If the time to restore service to an instance is short we would rather have the users wait rather than have updates to certain tables come from multiple instances.  We have other applications that we failover immediately.

    - -

    HTH -- Mark D Powell --

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 31, 2017 3:36PM

    Mark - my point was that services can be configured on the server side. You don't have to have "available" instances for a service. What I'm saying is that services can do anything you can do with a SID connection, don't require client-side configuration for failover/load balancing, and can do more (e.g. resource manager) - so I don't see any reason, in 2017, to be using SID for connections, whether it be RAC or single instance. I guess I was disagreeing with the specific sentence "To address the idea that you should always use the database service name with RAC I will disagree" - you do go on to explain how services can do the same thing, and I would suggest that is always the way you should be doing it (services).

  • Unknown
    edited May 31, 2017 6:59PM
    Would it be correct to say the SID is specific to a database instance? From what I understand, the database are just files, but it's the instance that that people connect to. 

    How do you reconcile those statements with the reality of 12c?

    There is only ONE instance but multiple PDBs/databases. People connect to the a PDB/database using a service name not a SID.

    The SID can be set as an environment variable (TWO_TASK) and then you can connect to the pdb directly from a command line.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Jun 1, 2017 11:11AM

    John, you have a good point especially with new environments; however, when you environment has a long history, your customers expect to use a SID and get the dear in headlights look on their face when you mention service names, then you use SID's. But I concede that service names should be what you set up and use for locally developed applications.

    - -

    HTH -- Mark D Powell --

  • jgarry
    jgarry Member Posts: 13,842
    edited Jun 1, 2017 12:55PM

    It is important in the context of this thread, since it may often be required to connect to 12c.  It is important to be explicit about the expanse of what the OP's driver has to connect to.

    I know I always forget about the LOCAL environment variable, too.

  • jgarry
    jgarry Member Posts: 13,842
    edited Jun 1, 2017 6:39PM

    Maybe we're just having some language collision here, but the instance is on the receiving end of a connection.  There could be a listener involved deciding, there could be a connection manager, there could be neither, such as BEQ or more obscurity.  Connecting directly to a pdb requires a service.

This discussion has been closed.