GLOBAL_NAMES, GLOBAL_NAME & DB LINK （ORA-02082）
在尝试配置oracle streams replication的时候，看到文档上说源数据和目标数据库(source database & destination database)都要把参数GLOBAL_NAMES设置成TRUE (默认值为FALSE), 如果这个参数设置为TRUE, 那么Oracle就会要求创建的database link的名字的跟目标数据库的global name保持一致，否则会有问题。
下面来测试看看...
假设source database为orcl, destination database 为orcl2. 这两个数据库是位于同一台机器上的，tnsnames.ora文件内容如下....
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora10g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora10g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl2)
)
)
现在连到orcl上，看一下参数GLOBAL_NAMES, DB_DOMAIN的设置...
SQL> conn sys/sys@orcl as sysdba
Connected.
SQL> show parameter global_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string oracle.com
连到orcl数据库的strmadmin用户下，创建一个dblink连接到orcl2数据库的strmadmin用户 （注意orcl和orcl2上的strmadmin都拥有DBA的权限）
SQL> conn strmadmin/strmadmin@orcl;
Connected.
SQL> drop database link orcl2;
Database link dropped.
SQL> create database link orcl2
2 connect to strmadmin identified by strmadmin
3 using 'orcl2';
Database link created.
SQL> select * from scott.dept@orcl2;
select * from scott.dept@orcl2
*
ERROR at line 1:
ORA-02085: database link ORCL2.ORACLE.COM connects to
ORCL2.REGRESS.RDBMS.DEV.US.ORACLE.COM
发现尝试通过dblink orcl2访问数据库orcl2出错了，从错误原因可以看出创建的dblink实际上连接到的是'ORCL2.REGRESS.RDBMS.DEV.US.ORACLE.COM', 而不是简单的orcl2!
问题出在什么地方呢？ 如果把orcl数据库的参数GLOBAL_NAMES 设置成false会怎么样呢？
SQL> show parameter global_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL> alter system set global_names=false;
System altered.
SQL> select * from scott.dept@orcl2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
发现如果把参数GLOBAL_NAMES设置成FALSE是可以成功通过刚才创建的dblink连接到数据库orcl2上的。但是因为streams要求参数GLOBAL_NAMES必须为TRUE，因此需要另寻出路。
注意到虽然刚才在创建dblink的时候只是简单地命名为orcl2, 实际上dblink的名字是orcl2.oracle.com. 多出来的oracle.com是数据库orcl的domain。而当把GLOBAL_NAMES设置成true的时候，注意从显示的错误信息上看orcl2连接到是数据库的名字是ORCL2.REGRESS.RDBMS.DEV.US.ORACLE.COM，这个名字显然同orcl2.oracle.com是不相同的，违背了GLOBAL_NAMES=TRUE的要求。
那么为什么orcl2的global name会是这么长呢?现在来看一下orcl2的相关参数设置....
SQL> conn sys/sys@orcl2 as sysdba
Connected.
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL2.REGRESS.RDBMS.DEV.US.ORACLE.COM
注意到数据库orcl2的参数db_domain没有设置，global_name就是刚才看到的那一长串的东西。那么解决方法貌似应该有两种，要么修改参数db_domain, 要么修改global_name。但是后面的测试证明，修改db_domain的值，并不会改变global_name的值。
因此最简单的方法是修改global_name, 如下... (注意因为global_name不是参数，需要通过alter database rename来重命名。
SQL> conn sys/sys@orcl2 as sysdba
Connected.
SQL> alter database rename global_name to orcl2.oracle.com;
Database altered.
SQL> conn strmadmin/strmadmin@orcl
Connected.
SQL> select * from scott.dept@orcl2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
不过貌似可以直接update 表global_name来修改global_name的值，不过最好还是不要这么做了...
SQL> update global_name set global_name='orcl2.dev.oracle.com';
1 row updated.
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
orcl2.dev.oracle.com
SQL> rollback;
Rollback complete.
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL2.ORACLE.COM
OK，刚才遇到的问题应该算是解决了，需要修改destination database上的global_name的值，使得目标数据库的global_name同在源数据上创建的db link的名字是相同的，在这个测试中是orcl2.oracle.com.
但是，既然前面提到orcl2的db_domain参数没有设置，那么现在来尝试设置一下....
SQL> alter system set db_domain = oracle.com;
alter system set db_domain = oracle.com
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set db_domain = oracle.com scope=spfile;
alter system set db_domain = oracle.com scope=spfile
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set db_domain='oracle.com' scope=spfile;
System altered.
可以看到db_domain这个参数的修改需要对instance重启，因此刚才直接修改global_name是最简单的。而且，注意到db_domain的值需要用引号引起来，否则会报错，而且错误信息很是misleading的。
那么现在来重启下instance...
SQL> startup force
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL> conn sys/sys@orcl2 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
糟糕，重启不了了，因为listener识别不了数据库orcl2！ 打开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)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = ora10g)(PORT = 1521))
)
)
因为orcl2是新创建的数据库，创建完毕之后instance是自动注册到listener中了，现在重启这个instance了导致listener识别不了orcl2了。因此需要修改listener.ora把orcl2静态注册进来，然后重启listener。修改文件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.oracle.com)
)
(SID_DESC=
(SID_NAME = orcl2)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = ora10g)(PORT = 1521))
)
)
现在重启下Listener，看看如何...
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 27-FEB-2011 20:51:15
Uptime 0 days 0 hr. 39 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10g)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.oracle.com" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl2" has 1 instance(s).
Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl2.oracle.com" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orcl2XDB.oracle.com" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orcl2_XPT.oracle.com" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB.oracle.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT.oracle.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl.oracle.com" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl2" has 1 instance(s).
Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl2.oracle.com" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl2XDB.oracle.com" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ora10g, pid: 14637>
(ADDRESS=(PROTOCOL=tcp)(HOST=ora10g)(PORT=21155))
Service "orcl2_XPT.oracle.com" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orclXDB.oracle.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ora10g, pid: 2861>
(ADDRESS=(PROTOCOL=tcp)(HOST=ora10g)(PORT=29929))
Service "orcl_XPT.oracle.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
LSNRCTL>
现在listener可以识别orcl2了，可以启动orcl2数据库了....
[oracle@ora10g ~]$ sqlplus sys/sys@orcl2 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 27 20:58:35 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 205520896 bytes
Fixed Size 1218532 bytes
Variable Size 83888156 bytes
Database Buffers 117440512 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string oracle.com
看以看到db_domain被修改为了oracle.com了。
----------------------------------------------华丽的分割线-------------------------------------------------------------------------------
本来这篇blog应该结束了，但是偶然间发现如下一个问题，我在数据库上尝试删除database link ‘orcl2’的时候居然报了一个“诡异”的错误。注意到数据库orcl2上其实是不存在orcl2这个名字的database link的，这个database link是存在于数据库orcl上的。
SQL> select name from v$database;
NAME
---------
ORCL2
SQL> show user
USER is "SYS"
SQL> select owner, db_link from dba_db_links;
no rows selected
SQL> drop database link orcl2;drop database link orcl2*ERROR at line 1:ORA-02082: a loopback database link must have a connection qualifier
SQL>
给出的错误信息并不是说db link不存在，而是说了个loopback database link的错误！！！ 造成这个错误的原因可能在于global_name和db link之间的关系。现在databas link orcl2 (其实是orcl2.oracle.com) 跟 global_name (orcl2.oracle.com) 名字是完全一样的。这种情况下，去drop或者是create这个名为orcl2的db link都会报错,
SQL> create database link orcl2 connect to strmadmin identified by strmadmin using 'orcl';
create database link orcl2 connect to strmadmin identified by strmadmin using 'orcl'
*ERROR at line 1:ORA-02082: a loopback database link must have a connection qualifier
SQL> create database link orcl2.oracle.com connect to strmadmin identified by strmadmin using 'orcl';
create database link orcl2.oracle.com connect to strmadmin identified by strmadmin using 'orcl'
*ERROR at line 1:ORA-02082: a loopback database link must have a connection qualifier
SQL> drop database link orcl2;
drop database link orcl2
*ERROR at line 1:ORA-02082: a loopback database link must have a connection qualifier
SQL> drop database link orcl2.oracle.com;
drop database link orcl2.oracle.com
*ERROR at line 1:ORA-02082: a loopback database link must have a connection qualifier
如果更改global_name会如何呢?
SQL> alter database rename global_name to 'orcl2.dev.oracle.com';
alter database rename global_name to 'orcl2.dev.oracle.com'
*
ERROR at line 1:
ORA-01729: database link name expected
oh....my lady gaga!!! 屋漏偏逢连阴雨！居然现在更改global_name会报出这个莫名其妙的错误来！！看来这个global_name和db link真有扯不清的关系。
换一种方法来改global_name,
SQL> update global_name set global_name='orcl2.dev.oracle.com';
1 row updated.
SQL> commit;
Commit complete.
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
orcl2.dev.oracle.com
SQL> select name from v$database;
NAME
---------
ORCL2
SQL> drop database link orcl2;
drop database link orcl2
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> drop database link orcl2.oracle.com;
drop database link orcl2.oracle.com
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> drop database link orcl2.dev.oracle.com;
drop database link orcl2.dev.oracle.com
*
ERROR at line 1:
ORA-02024: database link not found
我晕....我已经解释不清楚了，这个问题到底点解！！ 而且我现在在把global_name改回来，用alter database居然又成功了，god....
SQL> alter database rename global_name to orcl2.oracle.com;
Database altered.
SQL> drop database link orcl2.dev.oracle.com;
drop database link orcl2.dev.oracle.com
*
ERROR at line 1:
ORA-02024: database link not found
SQL> drop database link orcl2.oracle.com;
drop database link orcl2.oracle.com
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
god....崩溃.... 所以避免这个头疼的问题就是不要创建一个跟数据库global_name一样名字的database link！！！
那么如果非要创建名字为orcl2的db lin该怎么办呢，那么就需要在db link名字中加上所谓的connection qualifier,
SQL> create database link orcl2@orcl2优质内容筛选与推荐>>
2 connect to strmadmin identified by strmadmin
3 using 'orcl';
Database link created.
SQL> drop database link orcl2@orcl2;
Database link dropped.
1、MySQL连接问题浅析
2、HDU 5414 CRB and String （字符串，模拟）
3、为mongodb加上权限
4、leetcode 106. 从中序与后序遍历序列构造二叉树
5、Dom4j+PLSQL XML文件导入数据库