马上注册，结交更多数据大咖，获取更多知识干货，轻松玩转大数据
您需要 登录 才可以下载或查看，没有帐号？立即注册
x
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 2315: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 RealApplication 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 2316: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=xxxxscope=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 2711:35:01 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know ofservice 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 2711: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 RealApplication Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and RealApplication Testing options 这也很好理解，因为监听程序，现在监听的service是XXXX而不是orcl了。 [oracle@db98 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on27-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 on27-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 2711: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 RealApplication Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and RealApplication Testing options [oracle@db98 ~]$ sqlplus system/oracle@localhost/yyyy SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 2711: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 RealApplication 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_typefrom 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.0Production 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 RealApplication 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> createpublic 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 ---------- ---------- ------------------- 20ICOL$ TABLE 44I_USER1 INDEX 28CON$ TABLE 15UNDO$ TABLE 29C_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 todbdream 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_IDOBJECT_NAM OBJECT_TYPE ---------- ---------- ------------------- 20ICOL$ TABLE 44I_USER1 INDEX 28CON$ TABLE 15UNDO$ TABLE 29C_COBJ# CLUSTER 那么问题来了，可不可以在global_names=true的情况下，在本地数据库创建一个访问本地数据库的dblink呢？下面做下实验(注意：以下实验更换为最开始的测试库ORCL)。 SQL> showparameter 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 selectobject_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 todbdream 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的时候呢？ 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 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 todbdream identified by dbdream using 'ORCL'; create public database link ORCL connect to dbdreamidentified by dbdream using 'ORCL' * ERROR at line 1: ORA-02082: a loopback database link must have aconnection 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 todbdream 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名字冲突问题，可以创建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 todbdream 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 SETDATABASE 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最好不要随便修改，我这是测试环境，无所谓了，生产环境最好不要随便动，除非有百分百的把握。
来自群组: Oracle DBA交流 | 
/1 
发表于 2014-10-28 13:47:02
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
转播
分享
淘帖
赞
踩
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜
发表于 2014-10-28 14:46:34
楼主