最近整理了一篇文章：oracle listener 有網友對數據庫是否顯式設置了instance_name和service_names提出疑問。

由此引發出db_name,instance_name,oracle_sid等等這些常見的參數都代表什么意思，怎么取值的，有什么區別？

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE??? 11.2.0.3.0????? Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter name

NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert???????????? string
db_name?????????????????????????????? string?????? ORCL
db_unique_name??????????????????? string?????? ORCL
global_names???????????????????????? boolean??? FALSE
instance_name?????????????????????? string?????? ORCL
lock_name_space?????????????????? string
log_file_name_convert????????????? string
processor_group_name??????????? string
service_names??????????????????????? string?????? ORCL

看到這么多參數，但是服務器參數(spfile)中僅僅設置了db_name那么其它的name比如db_unique_name,instance_name,service_names的值是怎么出來的？

官方是這樣說的：When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file must at a minimum specify the DB_NAME parameter. All otherparameters have default values.

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

SQL> create pfile='/u01/pfile.ora' from spfile;

File created.

[oracle@resoft u01]$ vi pfile.ora

ORCL.__db_cache_size=243269632
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=4194304
ORCL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=293601280
ORCL.__sga_target=553648128
ORCL.__shared_io_pool_size=0
ORCL.__shared_pool_size=289406976
ORCL.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.audit_trail='DB','EXTENDED'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=847249408
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

各種name或者id的解釋：--括弧內中文名稱是我們一致認為比較合理的翻譯，但是如果想準確的表達，請直接說英文名稱，不要去翻譯，以免造成誤解。

db_name(數據庫名)

Property??????????????????????????????????????????????? Description
Parameter type????????????????????????????????????? String
Syntax????????????????????????????????????????????????? DB_NAME = database_name
Default value????????????????????????????????????????? There is no default value.
Modifiable????????????????????????????????????????????? No
Basic??????????????????????????????????????????????????? Yes
Oracle RAC?????????????????????????????????????????? You must set this parameter for every instance. Multiple instances must have the same value, or the same value must be

???????????????????????????????????????????????????????????? specified in the STARTUP OPEN SQL*Plus command or the ALTER DATABASE MOUNT SQL statement.

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 different, the database does not start.

db_name必須是一個不超過8個字符的文本串。在數據庫創建過程中，db_name被記錄在數據文件，日志文件和控制文件中。如果數據庫實例啟動過程中參數文件中的db_name和控制文件中的db_name名稱不一樣，則數據庫不能啟動。db_name是最具有穩定意義的參數，官網說不能修改，修改后會導致數據庫不能啟動。

DB_NAME 也就是數據庫的名字標示。這里，數據庫里可能有多個實例，比如RAC里的多節點，這多個節點是不同的實例，但是卻有相同的名字，他們的 DB_NAME是相同的但是Instance_name是不同的。DB_NAME會保持在數據文件頭里，所以更改DB_NAME不能僅僅修改parameter，還需要用nid 來進行更改，并且更改后還需要手工做些工作，是其生效。

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. 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 ($)?? --E文不好，翻譯還不如看原版。

DB_UNIQUE_NAME這在另一個HA的應用，Dataguard會經常提及的，和DB_NAME不一樣的作用，在DG里，要求物理DG，主從庫都有一樣的DB_NAME，雖然他們和RAC不一樣，并不是同一個庫。這里是數據庫的唯一名字。但是他們的DB_UNIQUE_NAME是不一樣的，用以進行不同的標示。DB_UNQUIE_NAME的會影響到Service_names，也會影響到動態監聽的時候的service_name

比如如下片段
Service "zxdbdg1" has 1 instance(s).
Instance "zxdb", status BLOCKED, has 1 handler(s) for this service...
Service "zxdbdg1_XPT" has 1 instance(s).
Instance "zxdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
這里的zxdbdg1就是dg中的從庫。從庫的db_name和主庫保持一樣為zxdb,DB_UNIQUE_NAME不同。在動態監聽后，注冊為
zxdbdg1的service，啟動的instance_name還是zxdb
Instance_name簡單講就是ORACLE_SID,oracle里通過ORSCLE_SID來管理不同的數據庫實例。
另，
上面的動態監聽信息里出現了Instance "zxdb", status BLOCKED
這里是因為我的從庫數據庫不是open狀態。

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

In a single-instance database system, the instance name is usually the same as the database namespecifying a particular instance by which to connect to the database. INSTANCE_NAME specifies the unique name of this instance.

用于和操作系統之間的聯系，用于對外部連接時使用。在操作系統中要取得與數據庫之間的交互，必須使用數據庫實例名。該值允許修改，但一般設置與數據庫名相同即可

INSTANCE_NAME 的默認值就是oracle SID。一般跟數據庫庫名稱相同，也可以不相同。

服務器參數spfile中沒有設置instance_name,所以取默認值oracle_sid,我這里sid 為ORCL 所以instance_name=ORCL

oracle_sid(數據庫實例名)

SID的全稱為site identifier，Oracle_SID則為Oracle site identifier.

Unix/Linux查看oralce_sid: export $oracle_sid? 或者直接查看環境變量文件 more .bash_profile

下面引用Tom(Thomas Kyte)的一段話來解釋Oracle_SID

If you’re unfamiliar with the term SID or ORACLE_SID, a full definition is called for. The SID is a site identifier. It and ORACLE_HOME (where the Oracle software is installed) are hashed together in UNIX to create a unique key name for attaching an SGA. If your ORACLE_SID or ORACLE_HOME is not set correctly, you’ll get the ORACLE NOT AVAILABLE error, since you can’t attach to a shared memory segment that is identified by this unique key. On Windows, shared memory isn’t used in the same fashion as UNIX, but the SID is still important. You can have more than one database on the same ORACLE_HOME, so you need a way to uniquely identify each one, along with their configuration files.

即：在Unix/Linux系統中，SID和ORACLE_HOME在一起哈希后得到一個唯一的值作為SGA的key。

當oracle實例啟動時，在操作系統上的fork進程則根據Oracle_SID來創建相關后臺進程。

Oracle 11g 支持Oracle_SID的長度為12位，db_name的長度為8位

作用：

用于區別同一臺主機上不同的Oracle實例

決定實例所啟動后臺進程的名稱。(實例由SGA和后臺進程組成)

決定了參數文件的名稱。如spfile<ORACLE_SID>.ora,init<ORACLE_SID>.ora

決定后臺進程產生的相關跟蹤文件、日志文件等。

如alert_<ORACLE_SID>.log,<ORACLE_SID>_arc1_<spid>.trc,<SID>_ora_<SPID>.trc

同一主機上不同的$ORACLE_HOME，可以創建相同的ORACLE_SID

由此可以得出

ORACLE_HOME相同時，可以使用不同的ORACLE_SID

ORACLE_HOME不同時，可以使用相同的ORACLE_SID

[oracle@RESOFT~]$ export ORACLE_SID=orcl --設定ORACLE_SID為orcl

SQL> ho ps -ef | grep oracle???????? --ORACLE_SID參與了后臺進程命名

oracle 3272 1 0 09:46 ? 00:00:00 ora_pmon_orcl

oracle 3274 1 0 09:46 ? 00:00:00 ora_psp0_orcl

oracle 3276 1 1 09:46 ? 00:00:00 ora_mman_orcl

oracle 3278 1 0 09:46 ? 00:00:00 ora_dbw0_orcl

oracle 3280 1 0 09:46 ? 00:00:00 ora_lgwr_orcl

oracle 3282 1 0 09:46 ? 00:00:00 ora_ckpt_orcl

oracle 3284 1 0 09:46 ? 00:00:00 ora_smon_orcl

---------............部分結果省略.............. --------------

dbid(數據庫id)

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

----------

1318255748

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 connect.

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。多用于分布式系統。

global_name命名規則= db_name+[db_domain]

也就是由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;

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 (#)

通常用于分布式數據庫系統中標識一個數據庫的邏輯位置。為數據定義一個域，該參數作為Global_names的一部分，即在不同的域中可以使用相同的數據庫名稱，該參數缺省情況下位空，在RAC環境中需要為每一個實例指定該值，且多實例具有相同的值

SQL> show parameter db_domain

NAME TYPE VALUE

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

db_domain string

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 在tnsnames.ora中用的最多。

service_names=DB_UNIQUE_NAME.DB_DOMAIN

我的環境中db_unique_name,db_domanin都沒有設置，而db_unique_name在單實例下默認值為：db_name = ORCL

所以service_names=ORCL

這也就解釋了剛開始提出的問題：沒有在spfile中設置instance_name,service_names 但是動態注冊時怎么發生的？