oracle中sid及有关name的解释 <转>

最新推荐文章于 2023-07-28 23:58:16 发布

nourewang

最新推荐文章于 2023-07-28 23:58:16 发布

阅读量1.1k

点赞数

分类专栏： DB

本文链接：https://blog.csdn.net/nourewang/article/details/9114123

版权

DB 专栏收录该内容

21 篇文章 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

修改的步骤如下：

（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 theOracle 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 andOracle homevalues 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 ofno 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 andup 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)