先看我的系统的一些参数！
SQL> show parameter service_names; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string wenchaodb SQL> show parameter instance_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string wenchaodb SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string wenchaod SQL> show parameter db_unique_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string wenchaodb SQL> SQL> show parameter db_domain; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_domain string SQL> SELECT * FROM GLOBAL_NAME; GLOBAL_NAME -------------------------------------------------------------------------------- WENCHAOD
---
SQL> select name from v$database; NAME --------- WENCHAOD SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- wenchaodb SQL>
----
[oracle@centos-fuwenchao admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos-fuwenchao)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@centos-fuwenchao admin]$----
[oracle@centos-fuwenchao admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
WENCHAONETSERVICE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.2.115)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = wenchaoservice)
)
)-----
一个客户端的tnsname
wenchao =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.2.115)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = wenchaodb)
)
)疑问：这些名字之间有什么区别和联系呢？为什么db_name为wenchaod而不是wenchaodb呢？
这篇文章中要讲的几个参数：
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参数。
2.2. DB_DOMAIN
官网说明如下：
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
官网说明如下：
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
官网说明：
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就可以了。
