Hi!
There's actually more:
db_name - identifies database name, has to be the
same what is stated in controlfile (using create database or create
controlfile). You can have several databases with same db_name in one server,
there is no restriction.
SID - When we actually want to open and use the
database, we have to start an instance which will be servicing the
database. When starting instance, whe have to specify SID (system identifier)
for it. This is actually just a operating system name for instance (or should I
say SGA shared memory segments). As you know, SID is specified using ORACLE_SID
OS environment variable and is only used by listener, when spawning new
processes or when attaching directly to SGA using bequeath protocol. You
can not have instances with same SID in one server, even if you use different
oracle homes.
instance_name - An Oracle parameter for specifying
Oracle instance name. Seems that operating system doesn't know anything about
it. For example, if you use SID_LIST parameters in your listener.ora, then
listener always knows how to spawn processes for given SID, because (almost) all
it takes to start another server process, is the location of oracle executable
and SID value. But if you want to connect using instance name (not SID itself)
then Oracle instance actually has to register itself with listener before any
server processes can be spawned.
Note that ORACLE_SID and instance_name variables do
not have to match. (Tested on 9.2.0.1 on W2K). If my SID is ORCL for example and
instance_name is TEST, then I can connect using both SID=ORCL and SID=TEST in
CONNECT_DATA section of TNSNAMES (again, the instance name has to be registered
with listener). V$INSTANCE still shows SID (ORCL) in instance name
fielt.
As we know, in OPS and RAC environment you could
have several instances servicing one database, this is one of common
reasons why db_name and instance_name would be different. (ORCL for db_name,
ORC0, ORC1, ORC2, etc.. for instance names for example). Btw, does anyone know
if I can have the same instance name in all RAC nodes? RAC shouldn't care much,
because it uses instance numbers anyway?
service_names - this is an additional layer
for logically spreading work across nodes in RAC (and possibly other load
balanced environments such replicated databases and even logical standby). For
example, if you have 4 node RAC and have defined two "services" such OLTP and
REPORTING, then you could assign OLTP for nodes and REPORTING for one, but
during month end reporting you could still keep OLTP on 3 nodes, 1 node purely
for REPORTING and add REPORTING to one or more OLTPs as well. So, when user's
are connecting, they don't request a specific node or instance, they just
request a service. And in load balancing environments then users are directed to
instances, where appropriate service is defined. It gives somewhat flexibility,
but I've never used it myself (thus my explanation here might wrong
;)
Cheers,
Tanel.
|
- service name, sid .. AK
- RE: service name, sid .. Goulet, Dick
- RE: service name, sid .. Stephen Lee
- RE: service name, sid .. Stephen Lee
- Re: service name, sid .. Tanel Poder
- Re: service name, sid .. AK
- RE: service name, sid .. Goulet, Dick
- RE: service name, sid .. Stephane Paquette
- RE: service name, sid .. Luis deUrioste
- Re: service name, sid .. Tanel Poder
- RE: service name, sid .. Goulet, Dick