instance_name、db_name、db_unique_name、global_names、service_names的含义及修改
昨天在《jdbc连接不上oracle数据库的问题》一文中提到了sid和service_name，(详见http://www.dbdream.com.cn/2015/01/04/jdbc%E8%BF%9E%E6%8E%A5%E4%B8%8D%E4%B8%8Aoracle%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E9%97%AE%E9%A2%98-2/)很多初学者都区分不开oracle数据库的这几个名字，今天介绍下这几个名字分别是什么含义、有什么作用、怎么修改。本文主要介绍以下容易混淆的名字。
1. instance_name 2. db_name 3. db_unique_name 4. global_name 5. service_name
instance(实例)是由内存和后台进程构成，instance_name就是实例的名字，实例名并不保存在参数文件中，而是在参数文件的名字中体现，二进制参数文件的名字结构是spfile+实例名+.ora，文本参数文件(pfile)的名字结构是init+实例名+.ora，如下是实例名为orcl的数据库的参数文件名，本案例数据库版本11.2.0.4.0。
[oracle@db98 ~]$ cd $ORACLE_HOME/dbs [oracle@db98 dbs]$ ls hc_orcl.dat init.ora initorcl.ora lkORCL orapworcl spfileorcl.ora
以下是默认安装的名字是orcl的单实例数据库，默认情况单实例的instance_name、db_name、service_names、db_unique_name都是一样的。
oracle@db98 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 23 15:14:05 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl db_unique_name string orcl global_names boolean FALSE instance_name string orcl service_names string orcl
单实例的数据库，默认这些名字是一样的，但是在RAC环境，实例名和数据库名是不一样的，在DG环境，db_unique_name和这些名字也是不一样的。
下面介绍一下怎么修改数据库的实例名，修改数据库的实例名非常简单，并不需要修改参数文件的内容，只需要修改参数文件的名字就可以了，数据库的SID基本指的就是实例名，修改数据库的实例名，见下面的测试。
SQL> select dbid from v$database;
DBID
----------
1384673061
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/11.2.0/db_1/db
s/spfileorcl.ora
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@db98 dbs]$ pwd
/u01/app/oracle/11.2.0/db_1/dbs
[oracle@db98 dbs]$ mv spfileorcl.ora spfiledbdream.ora
[oracle@db98 dbs]$ export ORACLE_SID=dbdream
[oracle@db98 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 23 16:40:17 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1607008256 bytes
Fixed Size 1364928 bytes
Variable Size 452987968 bytes
Database Buffers 1140850688 bytes
Redo Buffers 11804672 bytes
Database mounted.
Database opened.
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string dbdream
service_names string orcl
SQL> select dbid from v$database;
DBID
----------
1384673061
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/11.2.0/db_1/db
s/spfiledbdream.ora
只需要修改参数文件的名字，就可更改数据库的实例名，下面看下db_unique_name。
db_unique_name是数据库的唯一别名，主要用于区分同db_name的数据库，一般都是在DG环境中会使用到，这是静态参数，修改后需要重启才会生效。
SQL> alter system set db_unique_name=xxxx scope=spfile; System altered. SQL> startup force ORACLE instance started. Total System Global Area 1607008256 bytes Fixed Size 1364928 bytes Variable Size 452987968 bytes Database Buffers 1140850688 bytes Redo Buffers 11804672 bytes Database mounted. Database opened. SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl db_unique_name string XXXX global_names boolean FALSE instance_name string dbdream service_names string XXXX
默认情况下，修改db_unique_name参数，service_names参数自动跟着修改，service_names是数据库的服务名，客户端 通过监听程序访问数据库通常使用的都是服务名，这在tnsnames.ora文件里很常见，如下：
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db98)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
现在service_names已经被改成了XXXX，在使用这个字符串已经不能远程访问数据库了，需要讲标红的orcl改成XXXX才可以，下面使用简单连接模式测试下：
[oracle@db98 ~]$ sqlplus system/oracle@localhost/orcl SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 27 11:35:01 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus [oracle@db98 ~]$ [oracle@db98 ~]$ sqlplus system/oracle@localhost/XXXX SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 27 11:35:09 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
这也很好理解，因为监听程序，现在监听的service是XXXX而不是orcl了。
[oracle@db98 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-OCT-2014 11:30:28 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db98)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 27-OCT-2014 11:30:04 Uptime 0 days 0 hr. 0 min. 23 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/db98/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db98)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "XXXX" has 1 instance(s). Instance "dbdream", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "dbdream", status READY, has 1 handler(s) for this service... The command completed successfully
service_names是动态参数，可以直接修改并生效。
SQL> alter system set service_names=yyyy; System altered. SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl db_unique_name string XXXX global_names boolean FALSE instance_name string dbdream service_names string YYYY
修改service_names参数后，会自动同步到监听程序中。
[oracle@db98 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-OCT-2014 11:38:41 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db98)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 27-OCT-2014 11:30:04 Uptime 0 days 0 hr. 8 min. 36 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/db98/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db98)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "XXXX" has 1 instance(s). Instance "dbdream", status READY, has 1 handler(s) for this service... Service "YYYY" has 1 instance(s). Instance "dbdream", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "dbdream", status READY, has 1 handler(s) for this service... The command completed successfully
此时使用XXXX和YYYY都可以访问数据库。
[oracle@db98 ~]$ sqlplus system/oracle@localhost/XXXX SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 27 11:40:12 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@db98 ~]$ sqlplus system/oracle@localhost/yyyy SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 27 11:40:19 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
细心的人会发现，service_names参数和其他name参数不一样，多了个s，这是因为service_names参数可以有多个值，这通常在RAC环境中会见到，在RAC环境中，为了减少GC等待，通常需要指定一个service_name的主节点是哪个，从节点是哪个，只要主节点不宕机，使用这个service_name访问数据库的链接都会连到主节点，只有主节点异常，连接会被自动分配到从节点。这时service_names参数就会有多个值。
SQL> alter system set service_names=xxxx,yyyy; System altered. SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl db_unique_name string XXXX global_names boolean FALSE instance_name string dbdream service_names string XXXX, YYYY
这样，使用service_names参数的多个值就都可以访问数据库。
global_names参数通常和dblink有关，当该参数的值等于FALSE的时候，在使用dblink的时候，dblink的名字可以和被连接的数据库的global_name不一致，当该参数的值为TRUE时，dblink的名字就必须和被连接的数据库的global_name一致。下面在10.2.0.4.0版本的dbdream数据库创建测试用户dbdream并创建测试表。(注意：以下实验更换了测试数据库，访问端版本11.2.0.4.0，数据库名字mining，被访问端数据库版本10.2.0.4.0，数据库名字dbdream。)
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> create user dbdream identified by dbdream; User created. SQL> grant dba to dbdream; Grant succeeded. SQL> conn dbdream/dbdream Connected. SQL> create table t1 as select object_id,object_name,object_type from dba_objects; Table created.
修改11.2.0.4.0版本的数据库的tnsnames.ora文件，添加dbdream连接字符串。
DBDREAM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.249.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbdream)
)
)
[oracle@mining admin]$ sqlplus system/oracle@dbdream
SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 27 16:33:53 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
在11.2.0.4.0版本的数据库global_names参数的值为FALSE的时候，创建dblink。
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL> create public database link xxxx connect to dbdream identified by dbdream using 'DBDREAM';
Database link created.
SQL> select * from t1@xxxx where rownum<6;
OBJECT_ID OBJECT_NAM OBJECT_TYPE
---------- ---------- -------------------
20 ICOL$ TABLE
44 I_USER1 INDEX
28 CON$ TABLE
15 UNDO$ TABLE
29 C_COBJ# CLUSTER
在global_names参数的值为TRUE的时候，创建dblink。
SQL> alter system set global_names=true;
System altered.
SQL> drop public database link xxxx;
Database link dropped.
SQL> create public database link xxxx connect to dbdream identified by dbdream using 'DBDREAM';
Database link created.
SQL> select * from t1@xxxx where rownum <6;
select * from t1@xxxx where rownum <6
*
ERROR at line 1:
ORA-02085: database link XXXX connects to DBDREAM
被连接的数据库的global_name是DBDREAM，那么如上实验可知，使用名字为XXXX的数据链无法访问global_name是DBDREAM的数据库。
SQL> select * from global_name; GLOBAL_NAME ------------- DBDREAM
讲10.2.0.4.0版本的数据库名为dbdream的数据库的global_name改为xxxx，和dblink的名字一样。
SQL> alter database rename global_name to xxxx; Database altered. SQL> select * from global_name; GLOBAL_NAME ---------------- XXXX
这样dblink的名字和被访问的数据库的global_name一致，都是xxxx，才可以使用dblink访问。
SQL> select * from t1@xxxx where rownum <6;
OBJECT_ID OBJECT_NAM OBJECT_TYPE
---------- ---------- -------------------
20 ICOL$ TABLE
44 I_USER1 INDEX
28 CON$ TABLE
15 UNDO$ TABLE
29 C_COBJ# CLUSTER
那么问题来了，可不可以在global_names=true的情况下，在本地数据库创建一个访问本地数据库的dblink呢？下面做下实验(注意：以下实验更换为最开始的测试库ORCL)。
SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl db_unique_name string XXXX global_names boolean FALSE instance_name string dbdream service_names string XXXX, YYYY
当global_names=false时，是可以创建连接本地数据库的dblink的，如下实验。
SQL> conn dbdream/dbdream Connected. SQL> create table t1 as select object_id,object_name,object_type from dba_objects; Table created.
修改tnsnames.ora文件。
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db98)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxxx)
)
)
创建dblink，并测试。
SQL> create public database link xxxx connect to dbdream identified by dbdream using 'ORCL'; Database link created. SQL> select * from t1@xxxx where rownum <6; OBJECT_NAM OBJECT_ID OBJECT_TYPE ---------- ---------- ------------------- ICOL$ 20 TABLE I_USER1 46 INDEX CON$ 28 TABLE UNDO$ 15 TABLE C_COBJ# 29 CLUSTER
可见，当global_names参数的值是false时，是可以创建连接本地数据库的dblink的，那么当参数值等于true的时候呢？
QL> alter system set global_names=true;
System altered.
SQL> select * from t1@xxxx where rownum <6;
select * from t1@xxxx where rownum <6
*
ERROR at line 1:
ORA-02085: database link XXXX connects to DBDREAM
SQL> select * from global_name;
GLOBAL_NAM
----------
ORCL
SQL> drop public database link xxxx;
Database link dropped.
SQL> create public database link ORCL connect to dbdream identified by dbdream using 'ORCL';
create public database link ORCL connect to dbdream identified by dbdream using 'ORCL'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
在global_names参数等于true时，ORACLE不允许创建和当前数据库global_name一样的dblink名字，同时ORACLE还要求dblink的名字要和被访问的数据库的global_name一致，这样就不能在global_names参数等于true时创建连接本地数据库的dblink。即使手动修改global_name和dblink名字一致，dblink也是无法使用的。如下，手动修改global_name，骗过ORACLE的校验，使global_name和dblink名字一致，首先，在global_names=true的情况下创建名字为XXXX的dblink。
SQL> create public database link xxxx connect to dbdream identified by dbdream using 'ORCL'; Database link created.
修改global_name为false，验证dblink有效。
SQL> alter system set global_names=false; System altered. SQL> select * from t1@xxxx where rownum<6; OBJECT_NAM OBJECT_ID OBJECT_TYPE ---------- ---------- ------------------- ICOL$ 20 TABLE I_USER1 46 INDEX CON$ 28 TABLE UNDO$ 15 TABLE C_COBJ# 29 CLUSTER
修改global_name为true，此时由于dblink的名字为xxxx而被访问的数据库的global_name是ORCL，两个名字不一致，所以dblink不可用。
SQL> alter system set global_names=true;
System altered.
SQL> select * from t1@xxxx where rownum<6;
select * from t1@xxxx where rownum<6
*
ERROR at line 1:
ORA-02085: database link XXXX connects to ORCL
下面修改global_name为xxxx，使dblink的名字和global_name一致，都是xxxx。
SQL> alter database rename global_name to xxxx; Database altered.
现在dblink的名字和global_name都是xxxx，理论上应该可以使用dblink访问数据库了。
SQL> select * from t1@xxxx where rownum<6;
select * from t1@xxxx where rownum<6
*
ERROR at line 1:
ORA-00942: table or view does not exist
实验证明，这样也是不行的，但是有意思的是，报错竟然变成了表或视图不存在了？这是什么情况？我估计是讲global_name强行修改和dblink名字一样，ORACLE就找不到dblink了，有时间再研究这个问题。那么，就没办法在global_names=true的时候，使用dblink访问本地数据库了吗？经查看老熊的BLOG，找到了解决办法，原文链接http://www.laoxiong.net/database_link_global_names.html。
原来为了解决global_name名字冲突问题，可以创建global_name+@标识这样的名字的dblink，不理解的话看下面的测试，一目了然。
SQL> alter database rename global_name to orcl; Database altered.
讲global_name改回ORCL，然后创建global_name+@标识这样的名字的dblink。
SQL> create public database link orcl@link connect to dbdream identified by dbdream using 'ORCL'; Database link created.
这样使用orcl@link的数据链就可以访问数据了，ORACLE认为只要@前面global_name一致就可以。
SQL> select * from t1@orcl@link where rownum<6; OBJECT_NAM OBJECT_ID OBJECT_TYPE ---------- ---------- ------------------- ICOL$ 20 TABLE I_USER1 46 INDEX CON$ 28 TABLE UNDO$ 15 TABLE C_COBJ# 29 CLUSTER
下面在看看db_name，显而易见，这是数据库的名字，没啥好说的，修改db_name可就不想修改其他名字那么简单了，因为db_name不止在参数文件中有记载，在控制文件中也有记录，这就需要至少修改两个文件，修改参数文件没啥好说的，修改控制文件怎么搞？重建控制文件。值得一提的是db_name参数是不允许在数据库中直接修改的，需要在pfile中修改。下面演示讲db_name从orcl修改为dbdream，首先需要修改pfile，讲orcl改为dbdream。
--创建pfile SQL> create pfile from spfile; File created. --讲控制文件备份成文本 SQL> alter database backup controlfile to trace; Database altered. [oracle@db98 dbs]$ vi initdbdream.ora --*.db_name='orcl' *.db_name=dbdream
讲数据库启动到nomount状态。
SQL> create spfile from pfile; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 1607008256 bytes Fixed Size 1364928 bytes Variable Size 452987968 bytes Database Buffers 1140850688 bytes Redo Buffers 11804672 bytes
根据控制文件的文本备份，重建控制文件。
SQL> CREATE CONTROLFILE REUSE SET DATABASE dbdream RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, 9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, 10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/u01/app/oracle/oradata/orcl/system01.dbf', 14 '/u01/app/oracle/oradata/orcl/sysaux01.dbf', 15 '/u01/app/oracle/oradata/orcl/undotbs01.dbf', 16 '/u01/app/oracle/oradata/orcl/users01.dbf' 17 CHARACTER SET ZHS16GBK 18 ; Control file created.
需要注意的是在reuse的后面需要加上set关键字，而且需要使用resetlog方式。接下来讲数据库打开即可。
SQL> alter database open RESETLOGS; Database altered. SQL> select name from v$database; NAME --------- DBDREAM
db_name最好不要随便修改，我这是测试环境，无所谓了，生产环境最好不要随便动，除非有百分百的把握。
instance_name、db_name、db_unique_name、global_names、service_names的含义及修改：目前有2 条留言
发表评论
2014-11-03 11:09
2015-01-04 17:16