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

 

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

       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

 

修改的步骤如下：

（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). 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

 

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

 

 

       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/tianlesoftware/archive/2010/04/30/5543166.aspx

 

       不管是否指定了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

       官网说明如下：

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

 

       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

官网说明如下：

 

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

 

       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

官网说明如下：

Property	Description
Parameter type	String
Syntax	INSTANCE_NAME = instance_id
Default value	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.
Modifiable	No
Range of values	Any alphanumeric characters and the underscore (_) character
Basic	No

 

       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

官网说明如下：

Property	Description
Parameter type	String
Syntax	SERVICE_NAMES = db_service_name [, db_service_name [ ... ] ]
Default value	DB_UNIQUE_NAME.DB_DOMAIN if defined
Modifiable	ALTER SYSTEM
Range of values	Any ASCII string or comma-separated list of string names
Basic	No
Oracle RAC	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.

 

       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

 

2.8  GLOBAL_NAMES

官网说明：

 

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	true | false
Basic	No

 

       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就可以了。

 

 

 

 

 

整理自网络

 

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

 

Blog： http://blog.csdn.net/tianlesoftware

网上资源： http://tianlesoftware.download.csdn.net

相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群：62697716(满); DBA2 群：62697977(满)

DBA3 群：62697850   DBA 超级群：63306533;    

聊天 群：40132017

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请