In human language: What is the difference between: TNS, SID and a Listener? Thanks!
SID aka ORACLE_SID identifies the processes running an instance for the database. It is a physical kind of identifier. RAC databases typically have multiple instances and there for use multiple ORACLE_SID's. SID's can be used to connect to the specified database instance. If that instance is down, the connection fails.
SERVICE_NAME is a logical identifier where clients can connect to, instead of connecting to a SID. Especially in a RAC database, it can happen that a service is running on only a subset of the instances of the database. When using services, the application does not need to know on which ORACLE_SID the requested service is provided. Also, with services you can limit access to your database by disabling a service during maintenance. The ORACLE_SID is always available and can only be put in a restricted mode, limiting all access to the full instance. When an application uses services and as long as at least one instance is serving the service_name, a connection to that service succeeds.
TNS Tranparent Network Substrate, a location transparent and protocol independent network technology. The location transparency is made by using services and in the database a combination of synonyms and database links.