Re: On tnsnames.ora, the SERVICE NAME=<sid> should be change to SID=<sid> under what circumstances?

On Thu, 14 Aug 2003 03:38:30 -0700, Jeffrey Yee wrote:

> Hi,
>
> I'm at my client office, and I found out that some of the databases I
> tried to connect to, needs to use SID=<ORACLE_SID>, instead of SERVICE
> NAME=<ORACLE_SID>, in the tnsnames.ora file. All the databases that I
> connect to, is at least Oracle8i or above.
>
> After some investigation, I came to 2 conclusion, that causes this,
> but I'm not absolutely sure.
>
> 1. If client and server is on the different side of the firewall
> 2. if using the GLOBAL_DBNAME, that can be found in the listener.ora
> file.
>
> Is this correct? Can someone help me to understand the concept behind
> this, like why the SERVICE NAME & GLOBAL_DBNAME doesn't support
> firewall.
>
> Thank you in advance.
>
> Best Regards,
> Jeffrey Yee

Jeffrey:

The real reasoning behind the change (between 8.0 and 8i) from SID= to SERVICE= was simply that a service can be many more things than a boring old solitary instance. 8i and above allows you to connect to a nebulous bunch of things, all grouped together under the one banner of 'service=x'.

For example, if you are running a 32-node cluster (either 8i's Parallel Server or 9i's Real Application Cluster), you might well want to connect to a specific instance in order to do some specific maintenance work. At that point, use of SID=XXXX in the init.ora would be perfectly fine.

But your users probably couldn't give two hoots about which instance they are connected to. They connect to this "thing" called the 'Sales Database', which just happens to be a collection of 32 different instances, but they wouldn't know that and wouldn't be interested even if they did. So what you want is all 32 instances to be identifiable as one entity... a "service" called 'SALES'. Thus, each instance has its own instance name (SID, if you like), but they register themselves with the listener(s) under the one service name. Once the listener sees them all as one entity, your users do too.

Clustering is one area where it becomes important to have a higher level of abstraction than specific instances; replication might be another.

It has nothing to do with firewalls, in short. Global_dbname gets a mention simply because it's the default service name by which an instance registers itself with a listener (in turn, it is itself made up, by default, from the DB_NAME and DB_DOMAIN init.ora parameters). Global_dbname isn't actually of huge use in RAC, but gets used a fair bit when you delve into replication. But in either case, if you miss it out of the tnsnames.ora, the instance uses one anyway when it registers itself with the listener, constructing it on the fly from those two other parameters.

Short answer is that in 8i and above it is probably best to be consistent and stick with SERVICE=, and ditch the SID= version. But it's not going to kill you if you use the old way of doing it, until you want to dabble with the more advanced bits of Oracle functionality I've mentioned here.

Regards
HJR Received on Thu Aug 14 2003 - 15:13:41 CDT