ORACLE_SID与INSANCE_NAME的联系与区别

ORACLE_SID经常出现在各类Oracle的文档中，是Oracle System Identifier 的缩写，在Oracle系统中，ORACLE_SID以环境变量的形式出现，这个SID用于在系统中将实例与其他实例区分开来。
我们知道Oracle的实例(instance)是由一块共享内存区域(SGA)和一组后台进程(backgroud processes)共同组成；而后台进程是数据库和操作系统进行交互的通道，这些进程的名称就是通过ORACLE_SID决定的。
实例的启动需要一个参数文件，参数文件的名称是由ORACLE_SID决定的，对于init文件，缺省的文件名称是init<ORACLE_SID>.ora，对于spfile文件，缺省的文件名为spfile<ORACLE_SID>.ora，Oracle依据ORACLE_SID来决定和寻找参数文件启动实例。
通过Oracle提供的一个小工具sysresv，我们可以找到对应于不同的ORACLE_SID，操作系统上创建的共享内存段ID(Shared Memory)和信号量ID(Semaphores)等信息:

$ sysresv -l eygle julia
IPC Resources for ORACLE_SID "eygle" :
Shared Memory:
ID KEY
2560 0x79552064
Semaphores:
ID KEY
720896 0xa0e9f594
Oracle Instance alive for sid "eygle"
IPC Resources for ORACLE_SID "julia" :
Shared Memory:
ID KEY
514 0xab281214
Semaphores:
ID KEY
196610 0xa7645a54
Oracle Instance alive for sid "julia"

instance_name是Oracle数据库的一个参数，其缺省值是实例的SID，通常就是ORACLE_SID.
不同的实例可以有相同的实例名:

$ diff initeygle.ora initjulia.ora
$ grep instance_name initeygle.ora
*.instance_name='eygle'
$ ps -ef|grep julia
oracle9 25618 1 0 20:38:30 ? 0:00 ora_qmn0_julia
oracle9 27472 1 0 Feb 09 ? 0:00 ora_pmon_julia
oracle9 27474 1 0 Feb 09 ? 0:00 ora_dbw0_julia
oracle9 27476 1 0 Feb 09 ? 0:00 ora_lgwr_julia
oracle9 27478 1 0 Feb 09 ? 0:00 ora_ckpt_julia
oracle9 27480 1 0 Feb 09 ? 0:03 ora_smon_julia
oracle9 27482 1 0 Feb 09 ? 0:00 ora_reco_julia
oracle9 27484 1 0 Feb 09 ? 0:00 ora_cjq0_julia
oracle9 27488 1 0 Feb 09 ? 0:00 ora_s000_julia
oracle9 27490 1 0 Feb 09 ? 0:00 ora_d000_julia
oracle9 27492 1 0 Feb 09 ? 0:00 ora_arc0_julia
oracle9 27494 1 0 Feb 09 ? 0:00 ora_arc1_julia
oracle9 25622 25491 0 20:38:33 pts/1 0:00 grep julia
$ ps -ef|grep oracle9|grep eygle
oracle9 26904 1 0 Feb 09 ? 0:00 ora_arc1_eygle
oracle9 26890 1 0 Feb 09 ? 0:03 ora_smon_eygle
oracle9 26888 1 0 Feb 09 ? 0:13 ora_ckpt_eygle
oracle9 26884 1 0 Feb 09 ? 0:00 ora_dbw0_eygle
oracle9 26892 1 0 Feb 09 ? 0:00 ora_reco_eygle
oracle9 26898 1 0 Feb 09 ? 0:00 ora_s000_eygle
oracle9 26886 1 0 Feb 09 ? 0:00 ora_lgwr_eygle
oracle9 26882 1 0 Feb 09 ? 0:00 ora_pmon_eygle
oracle9 26902 1 0 Feb 09 ? 0:00 ora_arc0_eygle
oracle9 26900 1 0 Feb 09 ? 0:00 ora_d000_eygle
oracle9 26894 1 0 Feb 09 ? 0:00 ora_cjq0_eygle
oracle9 25635 25491 0 20:39:12 pts/1 0:00 grep eygle
$

这两个实例拥有相同的实例名(instance_name):

bash-2.03$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Feb 9 10:51:12 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
instance_name string eygle
SQL> !
$ export ORACLE_SID=eygle
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Feb 9 10:51:35 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string eygle

在Linux/Unix平台上，在不同的ORACLE_HOME下，可以创建相同实例名的ORACLE实例：

$ ps -ef|grep oracle8
oracle9 25857 25491 0 20:56:21 pts/1 0:00 grep oracle8
oracle8 27072 1 0 Feb 09 ? 0:00 ora_smon_eygle
oracle8 27070 1 0 Feb 09 ? 0:00 ora_ckpt_eygle
oracle8 27066 1 0 Feb 09 ? 0:00 ora_dbw0_eygle
oracle8 27064 1 0 Feb 09 ? 0:00 ora_pmon_eygle
oracle8 27068 1 0 Feb 09 ? 0:00 ora_lgwr_eygle
oracle8 27074 1 0 Feb 09 ? 0:00 ora_reco_eygle
oracle8 27076 1 0 Feb 09 ? 0:00 ora_snp0_eygle
oracle8 27078 1 0 Feb 09 ? 0:00 ora_snp1_eygle
oracle8 27080 1 0 Feb 09 ? 0:00 ora_snp2_eygle
oracle8 27082 1 0 Feb 09 ? 0:00 ora_snp3_eygle
oracle8 27084 1 0 Feb 09 ? 0:00 ora_s000_eygle
oracle8 27086 1 0 Feb 09 ? 0:00 ora_d000_eygle
$ ps -ef|grep oracle9
oracle9 26904 1 0 Feb 09 ? 0:00 ora_arc1_eygle
oracle9 25859 25491 0 20:56:25 pts/1 0:00 grep oracle9
oracle9 26890 1 0 Feb 09 ? 0:03 ora_smon_eygle
oracle9 26888 1 0 Feb 09 ? 0:13 ora_ckpt_eygle
oracle9 26884 1 0 Feb 09 ? 0:00 ora_dbw0_eygle
oracle9 26892 1 0 Feb 09 ? 0:00 ora_reco_eygle
oracle9 26898 1 0 Feb 09 ? 0:00 ora_s000_eygle
oracle9 26886 1 0 Feb 09 ? 0:00 ora_lgwr_eygle
oracle9 26882 1 0 Feb 09 ? 0:00 ora_pmon_eygle
oracle9 26902 1 0 Feb 09 ? 0:00 ora_arc0_eygle
oracle9 26900 1 0 Feb 09 ? 0:00 ora_d000_eygle
oracle9 26894 1 0 Feb 09 ? 0:00 ora_cjq0_eygle
oracle9 27472 1 0 Feb 09 ? 0:00 ora_pmon_julia
oracle9 27474 1 0 Feb 09 ? 0:00 ora_dbw0_julia
oracle9 27476 1 0 Feb 09 ? 0:00 ora_lgwr_julia
oracle9 27478 1 0 Feb 09 ? 0:00 ora_ckpt_julia
oracle9 27480 1 0 Feb 09 ? 0:03 ora_smon_julia
oracle9 27482 1 0 Feb 09 ? 0:00 ora_reco_julia
oracle9 27484 1 0 Feb 09 ? 0:00 ora_cjq0_julia
oracle9 27488 1 0 Feb 09 ? 0:00 ora_s000_julia
oracle9 27490 1 0 Feb 09 ? 0:00 ora_d000_julia
oracle9 27492 1 0 Feb 09 ? 0:00 ora_arc0_julia
oracle9 27494 1 0 Feb 09 ? 0:00 ora_arc1_julia
oracle9 25491 25469 0 20:30:55 pts/1 0:00 -ksh

Oracle的信号量集通过ID在系统上区分开来:

$ ipcs -sa
IPC status from as of Sun Feb 11 20:58:39 CST 2007
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
s 720896 0xa0e9f594 --ra-r----- oracle9 dba oracle9 dba 1004 20:57:16 10:43:25
s 196609 0x79978bac --ra-r----- oracle8 dba oracle8 dba 154 10:45:02 10:45:01
s 196610 0xa7645a54 --ra-r----- oracle9 dba oracle9 dba 1004 20:54:18 10:50:59

参考:
http://www.itpub.net/720783.html
-The End-