DB_NAME,SID,ORACLE_SID等参数的剖析

转载， 原创网址： http://blog.chinaunix.net/uid-25909722-id-3393985.html

在Oracle中，我们会遇到下面一系列的十分重要的参数。同时他们的含义也常常让我们混淆。这些参数有：

ORACLE_SID, SID, INSTANCE_NAME, SERVICE_NAME, SERVICES_NAMES, DB_NAME, GLOBAL_DBNAME, SID_NAME，以及网络服务名(net service name)，实例服务名(instance service name)等。下面我一个一个的来学习他们。

1. ORACLE_SID 与 SID 和 INSTANCE_NAME 以及 SID_NAME

ORACLE_SID：即ORACLE System IDentifier,它是一个环境变量。我们一般在oracle用户的home目录中的.bash_profile中进行定义，一般该文件包含下面一行：

[oracle@localhost ~]$ pwd

/home/oracle

[oracle@localhost ~]$ grep ORACLE_SID .bash_profile

export ORACLE_SID=jiagulun

其作用就是：

在我们使用在sqlplus工具中startup启动数据库时， OS就是利用这个环境变量来fork创建构成Oracle实例的各个进程，以及来命名一些文件的名字。如下所示：

[oracle@localhost ~]$ ps -ef | grep oracle

oracle   31707     1  0 13:41 ?        00:00:01 ora_pmon_ jiagulun

oracle   31709     1  0 13:41 ?        00:00:00 ora_psp0_ jiagulun

oracle   31711     1  0 13:41 ?        00:00:00 ora_mman_ jiagulun

oracle   31713     1  0 13:41 ?        00:00:02 ora_dbw0_jiagulun

oracle   31715     1  0 13:42 ?        00:00:02 ora_lgwr_jiagulun

oracle   31717     1  0 13:42 ?        00:00:05 ora_ckpt_jiagulun

oracle   31719     1  0 13:42 ?        00:00:03 ora_smon_jiagulun

oracle   31721     1  0 13:42 ?        00:00:00 ora_reco_jiagulun

oracle   31723     1  0 13:42 ?        00:00:02 ora_cjq0_jiagulun

oracle   31725     1  0 13:42 ?        00:00:04 ora_mmon_jiagulun

oracle   31727     1  0 13:42 ?        00:00:01 ora_mmnl_jiagulun

oracle   31729     1  0 13:42 ?        00:00:00 ora_d000_jiagulun

oracle   31731     1  0 13:42 ?        00:00:00 ora_s000_jiagulun

oracle   31733 31704  0 13:42 ?        00:00:00 oraclejiagulun (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

racle   31929     1  0 14:48 ?        00:00:00 ora_arc0_jiagulun

oracle   31931     1  0 14:48 ?        00:00:00 ora_arc1_jiagulun

oracle   31935     1  0 14:48 ?        00:00:00 ora_qmnc_jiagulun

oracle   31937     1  0 14:48 ?        00:00:01 ora_q000_jiagulun

oracle   31939     1  0 14:48 ?        00:00:00 ora_q001_jiagulun

oracle   32108     1  1 15:42 ?        00:00:00 ora_j000_jiagulun

[oracle@redhat4 bdump]$ pwd

/u01/app/oracle/admin/jiagulun/bdump

[oracle@redhat4 bdump]$ ls -l alert_jiagulun.log

-rw-r-----  1 oracle oinstall 305534 Nov  2 14:01 alert_ jiagulun.log

[oracle@redhat4 dbs]$ pwd

/u01/app/oracle/product/10.2.0/db_1/dbs

[oracle@redhat4 dbs]$ ls -l

total 108

-rw-rw----  1 oracle oinstall  1544 Aug 31 13:08 hc_jiagulun.dat

-rw-rw----  1 oracle oinstall  1544 Nov  1 17:07 hc_julia.dat

-rw-r-----  1 oracle oinstall 12920 May  3  2001 initdw.ora

-rw-r--r--  1 oracle oinstall  1283 Nov  2 11:33 init jiagulun.ora

-rw-r--r--  1 oracle oinstall    14 Nov  1 17:07 initjulia.ora

-rw-r-----  1 oracle oinstall  8385 Sep 12  1998 init.ora

-rw-rw----  1 oracle oinstall    24 Aug 31 13:10 lkJIAGULUN

-rw-rw----  1 oracle oinstall    24 Nov  1 17:08 lkJULIA

-rw-r-----  1 oracle oinstall  1536 Oct 25 09:43 orapw jiagulun

-rw-r-----  1 oracle oinstall  3584 Nov  2 14:01 spfile jiagulun.ora

-rw-r-----  1 oracle oinstall  3584 Oct  3 22:17 spfilejiagulun.ora.backup

下面的命令的执行必须要有环境变量ORACLE_SID:

SQL> startup

那么我们的环境变量ORACLE_SID应该设置成什么值呢？

应该是： 我们想要startup哪个Oracle实例，就应该将ORACLE_SID设置成哪个实例的 SID：

下面我们看看SID，在我们用dbca创建数据库时，会出现下面的GUI让我们选择：

第一参数，就是要我们设置一个 DB_NAME来唯一地标识数据库。

第二个参数，要我们设置一个 SID来唯一地标识一个Oracle实例。

我们知道Oracle server由Oracle实例和Oracle数据库两者共同组成。

所以：

1）很显然地， 我们想要startup哪个Oracle实例，就应该将环境变量ORACLE_SID设置成哪个SID。

2） SID唯一地标识一个Oracle实例，而ORACLE_SID启动该实例，启动之后我们得到一个Oracle实例，这个实例有一个名字：INSTANCE_NAME。 SID==>>ORACLE_SID==>>INSTANCE_NAME这三者是一致的，是完全相同的。

3）同时这个实例向外提供服务，所以又有一个SERVICE_NAME。

而SID_NAME出现在lisnter.ora文件中：

[oracle@redhat4 admin]$ cat  listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PL***tProc)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      ( SID_NAME = jiagulun)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      ( GLOBAL_DBNAME = jiagulun)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = redhat4)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

lisnter.ora中的SID_NAME的值必须与SID的值一致。通过lisnter.ora中的SID_NAME和GLOBAL_DBNAME两个参数以及客户端的tnsnames.ora中的SERVICE_NAME，这三个参数一起作用，可以实现ORACLE客户端与服务端的隔离。

[oracle@redhat4 admin]$ cat  tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

JIAGULUN =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))

    )

    (CONNECT_DATA =

      ( SERVICE_NAME = jiagulun)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PL***tProc)

      (PRESENTATION = RO)

    )

  )

客户端根据tnsname.ora中的SERVICE_NAME和地址(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))，到这个地址去访问监听器。然后监听器 根据文件lisnter.ora文件中的GLOBAL_NAME来判断是否有一个 GLOBAL_DBNAME 和 SERVICE_NAME 相等。如果相等，则建立客户端到SID标识的服务端实例的连接。(有一个例外：tnsnames.ora中可以用参数SID来取代SERVICE_NAME，这时比较的是tnsnames.ora中的SID和lisnter.ora中的SID_NAME，但是从oracle9i开始不推荐使用SID。因为SID无法隔离客户端和服务端)

tnsnames.ora中的地址(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))是监听器监听的地址。监听器进程一直在这个地址上监听，等待客户端的连接。

总结一下：

1）客户端和服务端的隔离是通过lisnter.ora中的GLOBAL_DBNAME来实现的，GLOBAL_DBNAME是一个连接客户端和服务端的桥梁：

a>client端tnsnames.ora中的SERVICE_NAME和server端lisnter.ora中的GLOBAL_DBNAME相等；

b>server端的lisnter.ora中的SID_NAME与系统的SID相等；

2）SID==>>SID_NAME==>>ORACLE_SID==>>INSTANCE_NAME 四者是一致的，相等的；

3）可以在lisnter.ora中配置多个不同的GLOBAL_NAME来供不同的客户端SERVICE_NAME来对应，从而实现不同的客户端使用不同的SERVICE_NAME来访问同一个SID实例使用，配置如下：

lisnter.ora:

    (SID_DESC =

      ( SID_NAME = jiagulun)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      ( GLOBAL_DBNAME = jiagulun)

    )

    (SID_DESC =

      ( SID_NAME = jiagulun)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      ( GLOBAL_DBNAME = jgl)

    )

tnsnames.ora:

JIAGULUN =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))

    )

    (CONNECT_DATA =

      ( SERVICE_NAME = jiagulun)

    )

  )

JGL =

  (DESCRIPTION =

     (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521)）

     )

     (CONNECT_DATA =

        ( SERVICE_NAME = jgl)

     )

  )

同时设置一下参数 service_names(不进行该项设置似乎也可以，无关紧要)

SQL> show parameter service

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

service_names                        string      jiagulun

SQL> alter system set service_names = 'jiagulun,jgl' scope=both;

System altered.

SQL> show parameter service

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

service_names                        string      jiagulun,jgl

SQL>

测试：

[oracle@redhat4 admin]$  tnsping jiagulun

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 01-NOV-2012 20:12:22

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = jiagulun)))

OK (0 msec)

[oracle@redhat4 admin]$  tnsping jgl

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 01-NOV-2012 20:12:27

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = jgl)))

OK (10 msec)

[oracle@redhat4 admin]$

现在我们在客户端既可以使用SERVICE_NAME=jiagulun来访问服务端，也可以使用SERVICE_NAME=jgl来访问。使用plsql develop用jgl和jiagulun都可以正常登陆。

2. SID 与 DB_NAME

显然，DB_NAME唯一性地标识了 oracle database，与数据库物理文件相关；而SID唯一性地标识了oracle instance，与所有进程相关。而oracle database和oracle instance一起组成了oracle server. SID和DB_NAME在非RAC环境默认是相等的。但是二者相等与否，无关紧要。在RAC环境，因为一个DB_NAME对应多个SID，所以不可能相等了。

DB_NAME是最重要的一个参数，在dbca中填写的DB_NAME，应该与启动参数文件pfile/spfile中的一致。 在dbca中创建数据库时填写DB_NAME被写入到了多个地方：启动参数文件、控制文件、数据文件、日志文件等。

所以我们不能随便地修改启动参数文件中的DB_NAME参数：

sys@JIAGULUN> create pfile from spfile;

File created.

sys@JIAGULUN> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@redhat4 oradata]$ grep db_name /u01/app/oracle/product/10.2.0/db_1/dbs/initjiagulun.ora

*.db_name='jiagulun'

[oracle@redhat4 oradata]$

如果 启动参数文件中的DB_NAME与控制文件中的不一致，则在mount阶段会报错。

3. SERVICE_NAME 与 SERVICE_NAMES

SERVICE_NAME是Oracle实例提供的服务名。它隔离了Oracle实例，客户端仅仅需要知道SERVICE_NAME就可以访问实例。而不需要知道实例的SID。更不需要知道DB_NAME等信息。

SERVICE_NAMES为实例定义一个或多个SERVICE_NAME，这样可以通过多个SERVICE_NAME将不同的用户连接区分开来。

service name似乎应该分为两种，一种是实例服务名 instance service name，一种是网络服务名 net service name，如下tnsnames.ora所示：

net_service_name =

  (DESCRIPTION =

     (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))

     )

     (CONNECT_DATA =

        (SERVICE_NAME =  instance_service_name)

     )

  )

而plsql develop登陆使用的是net_service_name，而不是instance_service_name。

而tnsping 测试的也是net_service_name，而不是instance_service_name。

[oracle@redhat4 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

JIAGULUN =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = jiagulun)

    )

  )

net_jgl =

  (DESCRIPTION =

     (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))

     )

     (CONNECT_DATA =

        (SERVICE_NAME =  jgl)

     )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PL***tProc)

      (PRESENTATION = RO)

    )

  )

[oracle@redhat4 admin]$ tnsping  net_jgl

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 02-NOV-2012 14:01:55

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = jgl)))

OK (10 msec)

[oracle@redhat4 admin]$ tnsping  jgl

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 02-NOV-2012 14:01:59

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

TNS-03505: Failed to resolve name

[oracle@redhat4 admin]$

如上所示：当我们使用 tnsping instance_service_name是失败了。

而 sqlplus scott/tiger@net_jgl 使用的也是net_service_name.

所以 tnsping, sqlplus user/passwd@net_jgl, plsql develop使用的都是net_service_name，而不是instance_service_name.

[oracle@redhat4 admin]$ sqlplus scott/tiger @net_jgl

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 1 21:08:48 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@redhat4 admin]$ sqlplus scott/tiger@jgl

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 2 14:05:57 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

所以这里提供了多层的隔离：

net_service_name ==>> instance_service_name ==>> global_dbname ==>> sid_name ==>> sid

  (sid_name=sid=oracle_sid=instance_name)

1> tnsnames.ora中定义了net_service_name和instance_service_name的对应，或者说隔离。

2> lisnter.ora中定义了global_dbname和sid的对应，或者说隔离；

3> 而tnsnames.ora中的instance_service_name(SERVICE_NAME)又和lisnter.ora中的GLOBAL_DBNAME相等。将两层隔离连接起来。

=================================================

添加：

实验：

[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /home/oracle/app/product/11.2.0/db_1)
      (GLOBAL_DBNAME = test1)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /home/oracle/app/product/11.2.0/db_1)
      (GLOBAL_DBNAME = test2)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle/app

[oracle@localhost admin]$ lsnrctl status


LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-OCT-2016 02:27:00
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                25-OCT-2016 02:25:54
Uptime                    0 days 0 hr. 1 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "test1" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "test2" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost admin]$ 

SQL> 
SQL> show parameter service_names


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl
SQL> 
SQL> 
SQL> alter system set service_names='orcl,test1,test2';


System altered.


SQL> 
SQL> show parameter service_names


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl,test1,test2
SQL> 

tnsnames.ora 添加：

test1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test1)
    )
  )


test2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test2)
    )
  )

测试：

[oracle@localhost admin]$ 
[oracle@localhost admin]$ tnsping test1


TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-OCT-2016 02:34:37


Copyright (c) 1997, 2013, Oracle.  All rights reserved.


Used parameter files:




Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test1)))
OK (0 msec)
[oracle@localhost admin]$ 
[oracle@localhost admin]$ tnsping test2


TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-OCT-2016 02:34:48


Copyright (c) 1997, 2013, Oracle.  All rights reserved.


Used parameter files:




Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test2)))
OK (0 msec)
[oracle@localhost admin]$