DBID,SID,DB_NAME,DB_DOMAIN,INSTANCE_NAME,DB_UNIQUE_NAME,SERVICE_NAMES 及监听参数的说明

谁践踏了优雅 2022-06-16 11:10 151阅读 0赞

这篇文章中要讲的几个参数：

DB 相关的： DBID, SID

PFILE中的参数：DB\_NAME，DB\_DOMAIN,  INSTANCE\_NAME，

DB\_UNIQUE\_NAME,SERVICE\_NAMES,

GLOBAL\_NAME，GLOBAL\_NAMES

Listener.ora中参数：

SID\_NAME，GLOBAL\_DBNAME

Tnsnames.ora中参数：

SERVICE\_NAME，SID

一. 与DB 相关的2个参数

1.1  DBID（DataBase IDentifier）

Oracle 官网对DBID的解释如下：

An internal, uniquely generated number that differentiates databases. Oracle creates this number automatically when you create the database.

DBID 可以看成是db\_name在数据库内部的表示。 DBID是在创建数据库时，用db\_name 结合一种算法来创建的。 具体用什么算法，不太清楚。它存在与数据文件和控制文件，用于表示数据文件的归属。 所以这个DBID 是唯一的。  对于不同的数据库，DBID 是不同的，但是db\_name 有可能相同。

用身份证打个比方： 可以有同名的人，但是它的省份证号码肯定是不同的。

查看DBID:

SQL> select dbid from v$database;

DBID

\----------

1262006473

我们可以用命令来修改DBID, 这个官网有说明：

Changing the DBID and DBNAME of a Database

[http://download.oracle.com/docs/cd/E11882\_01/server.112/e16536/dbnewid.htm\#SUTIL1543][http_download.oracle.com_docs_cd_E11882_01_server.112_e16536_dbnewid.htm_SUTIL1543]

修改的步骤如下：

（1）将数据库启动到mount状态

（2）用nid命令修改：nid TARGET=/  DBNAME=tianlesoftware

（3）修改之后，在启动到mount：startup mount

（4）resetlogs 打开数据库：ALTER DATABASE OPEN RESETLOGS;

要注意一点：

修改DBID 之后，之前的备份和归档都将无效。 具体参考官网内容：

Ramifications of Changing the DBID and DBNAME

Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the datafiles. After you change the DBID, backups and archive logs that were created prior to the change can no longer be used because they still have the original DBID, which does not match the current DBID. You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 (see the [Oracle Database Administrator's Guide)][Oracle Database Administrator_s Guide]. Consequently, you should make a backup of the whole database immediately after changing the DBID.

Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You must change the DB\_NAME initialization parameter after a database name change to reflect the new name. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change.

From：

Ramifications of Changing the DBID and DBNAME

[http://download.oracle.com/docs/cd/E11882\_01/server.112/e16536/dbnewid.htm\#SUTIL1541][http_download.oracle.com_docs_cd_E11882_01_server.112_e16536_dbnewid.htm_SUTIL1541]

1.2.  SID（system identifier）

官网的说明如下：

The system identifier (SID) is a unique name for an Oracle database instance on a specific host. On UNIX and Linux, Oracle Database uses the SID and[Oracle home][] values to create a key to shared memory. Also, the SID is used by default to locate the parameter file, which is used to locate relevant files such as the database control files.

On most platforms, the ORACLE\_SID environment variable sets the SID, whereas the ORACLE\_HOME variable sets the Oracle home. When connecting to an instance, clients can specify the SID in an Oracle Net connection or use a net service name. Oracle Database converts a service name into an ORACLE\_HOME and ORACLE\_SID.

查看SID:

SQL> select instance\_name from v$instance;

INSTANCE\_NAME

\----------------

orcl

尽管v$instance中字段 instance\_name 看起来是实例名，但是实际上存储的是sid。   在win下, 不管oracle\_home是否相同，sid不能重复。

在unix/linux下只要不同版本的oracle安装在不同的oracle\_home下就可以创建相同sid的实例，但是win下不可以，主要是受到windows服务的限制，在服务中不能存在服务名相同的oracle服务，服务名是由如下格式组成的：OracleServiceSID,因为服务名中包括了sid，所以sid如果相同了，服务名就相同了，这是windows所不允许的。因此在win下无法创建相同sid的不同实例。

二.  Pfile 中的参数

2.1.  DB\_NAME

官网的说明如下：

DB\_NAME specifies a database identifier of up to 8 characters. This parameter must be specified and must correspond to the name specified in the CREATE DATABASE statement.

If you have multiple databases, the value of this parameter should match the Oracle instance identifier of each one to avoid confusion with other databases running on the system. The value of DB\_NAME should be the same in both the standby and production initialization parameter files.

The database name specified in either the STARTUP command or the ALTER DATABASE ... MOUNT statement for each instance of the cluster database must correspond to the DB\_NAME initialization parameter setting.

The following characters are valid in a database name: alphanumeric characters, underscore (\_), number sign (\#), and dollar sign ($). No other characters are valid. Oracle removes double quotation marks before processing the database name. Therefore you cannot use double quotation marks to embed other characters in the name. The database name is case insensitive.

DB\_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB\_NAME is recorded in the datafiles, redo log files, and control file of the database. If during database instance startup the value of the DB\_NAME parameter (in the parameter file) and the database name in the control file are not the same, the database does not start.

From:

[http://download.oracle.com/docs/cd/E11882\_01/server.112/e17110/initparams062.htm\#REFRN10041][http_download.oracle.com_docs_cd_E11882_01_server.112_e17110_initparams062.htm_REFRN10041]

DB\_NAME 是数据库名，它的长度不能超过8个字节，超过8个会被截断。对于RAC环境，各个节点之间的DB\_NAME都是相同的，INSTANCE\_NAME 不同。 对于Data Guard环境，DB\_NAME相同，DB\_UNIQUE\_NAME 不同。

DB\_NAME记录在datafile，redo log和control file中。 这个记录方式就是通过DBID。 因为DBID 就是用DB\_NAME 加算法来生成的。

还是借用身份证的比喻： DB\_NAME 就是姓名，DBID就是身份证。

所以如果要修改DB\_NAME，只能用nid命令来修改DBID，然后修改相关pfile参数。

查看DB\_NAME

SQL> show parameter db\_name

NAME                   TYPE        VALUE

\------------------------------------ ----------- ------------------------------

db\_name                 string      orcl

SQL> select dbid,name from v$database;

DBID NAME

\---------- ---------

1262006473 ORCL

db\_name还有一个非常重要的作用就是动态注册监听.  关于动态注册和静态注册，参考Blog：

Oracle Listener 动态注册 与 静态注册

[http://blog.csdn.net/xujinyang/article/details/6829560][http_blog.csdn.net_xujinyang_article_details_6829560]

不管是否指定了service\_name,或者说service\_name的值是什么，pmon都会使用db\_name动态注册监听的。

注册到监听器中的服务值从init.ora文件中的参数service\_names取得。如果该参数没有设定值，数据库将拼接init.ora文件中的 db\_name和db\_domain的值来注册自己。如果选择提供service\_names值，您可以使用完全限定的名称（比如 orcl.oracle.com)或缩写的名称（比如orcl）。如果选择缩写的名称并设置了db\_domain参数，注册到监听器中的服务将是service\_name值和db\_domain值的拼接。

2.2.  DB\_DOMAIN

官网说明如下：

<table> 
 <thead> 
 <tr> 
 <td> Property </td> 
 <td> Description </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> Parameter type </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> Syntax </td> 
 <td> DB_DOMAIN = domain_name </td> 
 </tr> 
 <tr> 
 <td> Default value </td> 
 <td> There is no default value. </td> 
 </tr> 
 <tr> 
 <td> Modifiable </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Range of values </td> 
 <td> Any legal string of name components, separated by periods and&nbsp;up to 128 characters long (including the periods). This value cannot be NULL. </td> 
 </tr> 
 <tr> 
 <td> Basic </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Oracle RAC </td> 
 <td> You must set this parameter for every instance, and multiple instances must have the same value. </td> 
 </tr> 
 </tbody> 
</table>

In a distributed database system, DB\_DOMAIN specifies the logical location of the database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system. The value consists of the extension components of a global database name, consisting of valid identifiers (any alphanumeric ASCII characters), separated by periods. Oracle recommends that you specify DB\_DOMAIN as a unique string for all databases in a domain.

This parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales department's DB\_DOMAIN is JAPAN.ACME.COM, then their SALES database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB\_NAME = SALES but with DB\_DOMAIN = US.ACME.COM.

If you omit the domains from the name of a database link, Oracle expands the name by qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary. The characters valid in a database domain name are: alphanumeric characters, underscore (\_), and number sign (\#).

2.3.  DB\_UNIQUE\_NAME

官网说明如下：

<table> 
 <thead> 
 <tr> 
 <td> Property </td> 
 <td> Description </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> Parameter type </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> Syntax </td> 
 <td> DB_UNIQUE_NAME = database_unique_name </td> 
 </tr> 
 <tr> 
 <td> Default value </td> 
 <td> Database instances: the value of DB_NAME Automatic Storage Management instances: +ASM </td> 
 </tr> 
 <tr> 
 <td> Modifiable </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Basic </td> 
 <td> Yes </td> 
 </tr> 
 <tr> 
 <td> Oracle RAC </td> 
 <td> Multiple instances must have the same value. </td> 
 </tr> 
 </tbody> 
</table>

DB\_UNIQUE\_NAME specifies a globally unique name for the database. Databases with the same DB\_NAME within the same DB\_DOMAIN (for example, copies of a database created for reporting or a physical standby) must have a unique DB\_UNIQUE\_NAME. Every database's DB\_UNIQUE\_NAME must be unique within the enterprise.

\-- 对于DB\_NAME 系统的的数据库必须要有不同的DB\_UNIQUE\_NAME。 Data Guard就是这么回事。

The value of DB\_UNIQUE\_NAME can be up to 30 characters and is case insensitive. The following characters are valid in a database name: alphanumeric characters, underscore (\_), number sign (\#), and dollar sign ($).

DB\_UNQUIE\_NAME的会影响到Service\_names，也会影响到动态监听的时候的service\_name。 如在Data Guard 环境下，如果采用动态注册，那么注册的Service就是DB\_UNIQUE\_NAME。但instance 还是instance\_name,即SID.

如：

Service " orcl\_st " has 1 instance(s).  
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...  
Service " orcl\_st \_XPT" has 1 instance(s).  
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...  
The command completed successfully

2.4.  INSTANCE\_NAME

官网说明如下：

<table> 
 <thead> 
 <tr> 
 <td> Property </td> 
 <td> Description </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> Parameter type </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> Syntax </td> 
 <td> INSTANCE_NAME = instance_id </td> 
 </tr> 
 <tr> 
 <td> Default value </td> 
 <td> The instance's SID Note: The SID identifies the instance's shared memory on a host, but may not uniquely distinguish this instance from other instances. </td> 
 </tr> 
 <tr> 
 <td> Modifiable </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Range of values </td> 
 <td> Any alphanumeric characters and the underscore (_) character </td> 
 </tr> 
 <tr> 
 <td> Basic </td> 
 <td> No </td> 
 </tr> 
 </tbody> 
</table>

In a Real Application Clusters environment, multiple instances can be associated with a single database service. Clients can override Oracle's connection load balancing by specifying a particular instance by which to connect to the database. INSTANCE\_NAME specifies the unique name of this instance.

In a single-instance database system, the instance name is usually the same as the database name.

INSTANCE\_NAME  的默认值就是SID。 一般跟数据库库名称相同，也可以不相同。

查看：

SQL> show parameter db\_name

NAME                   TYPE        VALUE

\------------------------------------ ----------- ------------------------------

db\_name                  string      racdb

SQL> show parameter instance\_name

NAME                    TYPE        VALUE

\------------------------------------ ----------- ------------------------------

instance\_name             string      racdb1

INSTANCE\_NAME 会影响进程的命名：

\[root@racnode1 ~\]\# ps -ef|grep pmon

grid      7474     1  0 Dec16 ?        00:00:05 asm\_pmon\_+ASM1

oracle    8077     1  0 Dec16 ?        00:00:07 ora\_pmon\_racdb1

root     20204 20176  0 00:13 pts/1    00:00:00 grep pmon

initSID.ora 和 orapwSID 文件要与INSTANCE\_NAME保持一致：

\[oracle@racnode1 ~\]$ cd $ORACLE\_HOME/dbs

\[oracle@racnode1 dbs\]$ ls

hc\_DBUA0.dat  hc\_racdb1.dat  init.ora  initracdb1.ora  orapwracdb1  snapcf\_racdb1.f

2.5  SERVICE\_NAMES

官网说明如下：

<table> 
 <thead> 
 <tr> 
 <td> Property </td> 
 <td> Description </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> Parameter type </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> Syntax </td> 
 <td> SERVICE_NAMES = db_service_name [, db_service_name [ ... ] ] </td> 
 </tr> 
 <tr> 
 <td> Default value </td> 
 <td> DB_UNIQUE_NAME.DB_DOMAIN if defined </td> 
 </tr> 
 <tr> 
 <td> Modifiable </td> 
 <td> ALTER SYSTEM </td> 
 </tr> 
 <tr> 
 <td> Range of values </td> 
 <td> Any ASCII string or comma-separated list of string names </td> 
 </tr> 
 <tr> 
 <td> Basic </td> 
 <td> No </td> 
 </tr> 
 <tr> 
 <td> Oracle RAC </td> 
 <td> Do not set the SERVICE_NAMES parameter for Oracle RAC environments. Instead, define services using Oracle Enterprise Manager and manage those services using Server Control (SRVCTL) utility. </td> 
 </tr> 
 </tbody> 
</table>

SERVICE\_NAMES specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.

You can specify multiple service names in order to distinguish among different uses of the same database. For example:

SERVICE\_NAMES = sales.acme.com, widgetsales.acme.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB\_DOMAIN parameter. If DB\_DOMAIN is not specified, then no domain will be applied to the non-qualified SERVICE\_NAMES values.

注意一点，服务名是复数， service\_names 可以是多个值。

在Data Guard中， 如果采用动态注册，建议在primary，standby上使用相同的service\_names,这样可能便于尽可能的实现透明切换。

如果配置了静态注册的监听在primary，standby上也务必保持在listener中要求输入的服务名相同，尽可能的实现透明切换。

查看service\_names：

SQL> show parameter service\_names

NAME                   TYPE        VALUE

\------------------------------------ ----------- ------------------------------

service\_names            string      orcl

2.6  GLOBAL\_NAME

global\_name 是由db\_name.db\_domain构成。

查看Global\_name：

SQL> SELECT \* FROM GLOBAL\_NAME;

GLOBAL\_NAME

\--------------------------------------------------------------------------------

RACDB

我们可以修改GLOBAL\_NAME. 如：

ALTER DATABASE RENAME GLOBAL\_NAME TO sales.us.example.com;

具体参考：

Changing the Domain in a Global Database Name

[http://download.oracle.com/docs/cd/E11882\_01/server.112/e17120/ds\_admin001.htm\#ADMIN12147][http_download.oracle.com_docs_cd_E11882_01_server.112_e17120_ds_admin001.htm_ADMIN12147]

2.8  GLOBAL\_NAMES

官网说明：

<table> 
 <thead> 
 <tr> 
 <td> Property </td> 
 <td> Description </td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> Parameter type </td> 
 <td> Boolean </td> 
 </tr> 
 <tr> 
 <td> Default value </td> 
 <td> false </td> 
 </tr> 
 <tr> 
 <td> Modifiable </td> 
 <td> ALTER SESSION, ALTER SYSTEM </td> 
 </tr> 
 <tr> 
 <td> Range of values </td> 
 <td> true | false </td> 
 </tr> 
 <tr> 
 <td> Basic </td> 
 <td> No </td> 
 </tr> 
 </tbody> 
</table>

GLOBAL\_NAMES specifies whether a database link is required to have the same name as the database to which it connects.

If the value of GLOBAL\_NAMES is false, then no check is performed. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to true to ensure the use of consistent naming conventions for databases and links in a networked environment.

Global\_names 是一个布尔值，global\_names的作用是创建db link时是否强制使用远程数据库的global\_name,如果global\_names=true,则db link name必须要求是remote database的global\_name,否则创建之后db link 不能连同，缺省值是false。多用于分布式系统。

SQL> show parameter global\_names

NAME                  TYPE        VALUE

\------------------------------------ ----------- -------

global\_names             boolean     FALSE

三.  Listener.ora 文件中的参数

先看一个listener.ora 文件：

\[oracle@dg2 admin\]$ cat listener.ora

SID\_LIST\_LISTENER =

(SID\_LIST =

(SID\_DESC =

(SID\_NAME = PLSExtProc)

(ORACLE\_HOME = /u01/app/oracle/product/10.2.0/db\_1)

(PROGRAM = extproc)

)

(SID\_DESC =

(SID\_NAME = orcl)

(ORACLE\_HOME = /u01/app/oracle/product/10.2.0/db\_1)

(GLOBAL\_DBNAME = orcl)

)

3.1 SID\_NAME

SID\_NAME指数据库的运行的实例名，和instance\_name保持一致。

3.2 GLOBAL\_DBNAME

配置静态监听注册时，需要输入SID和GLOBAL\_NAME。 我上面贴出来的内容，就是静态监听配置的内容。 SID 已经说过，和Instance\_name 保持一致就可以了。

GLOBAL\_DBNAME 是listener配置的对外网络连接名称，我们可以写成任意值。 在客户端配置监听的tnsnames.ora 文件中的service\_name 与这个GLOBAL\_DBNAME 保持一致就可以了。因为客户端访问数据库是通过监听来实现的。

如果采用动态注册的话，PMON进程会根据初始化参数initSID.ora中的instance\_name,service\_names两个参数将实例和服务动态注册到listener中，这时自动注册的对外网络连接名称就是initSID.ora文件中service\_names.

因为service\_names可以有多个值，如果有多个值，就会注册多个。 但是他们对应都是同一个instance\_name。 这样，我们在客户端配置tnsnames.ora 时，在service\_name 写其中任意一个都可以正常连上数据库。

四. Tnsnames.ora 文件

先看一个tnsnames.ora 文件：

ORCL\_PD =

(DESCRIPTION =

(ADDRESS\_LIST =

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

)

(CONNECT\_DATA =

(SERVICE\_NAME = orcl)

)

4.1  SERVICE\_NAME

如果服务器采监听采用了静态注册，那么这个参数就等于Listener.ora 文件中的GLOBAL\_DBNAME的值。

如果是动态注册，那么这个值就是initSID.ora 文件中service\_names中的值。

4.2  SID

在tnsnames.ora文件中还可以使用SID。 如果使用该参数，只需要把该参数指定为instance\_name就可以了。

[http_download.oracle.com_docs_cd_E11882_01_server.112_e16536_dbnewid.htm_SUTIL1543]: http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dbnewid.htm%23SUTIL1543
[Oracle Database Administrator_s Guide]: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/toc.htm
[http_download.oracle.com_docs_cd_E11882_01_server.112_e16536_dbnewid.htm_SUTIL1541]: http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dbnewid.htm%23SUTIL1541
[Oracle home]: http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/glossary.htm#CHDDDBFG
[http_download.oracle.com_docs_cd_E11882_01_server.112_e17110_initparams062.htm_REFRN10041]: http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams062.htm%23REFRN10041
[http_blog.csdn.net_xujinyang_article_details_6829560]: http://blog.csdn.net/xujinyang/article/details/6829560
[http_download.oracle.com_docs_cd_E11882_01_server.112_e17120_ds_admin001.htm_ADMIN12147]: http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/ds_admin001.htm%23ADMIN12147