在本地开global_names=true时，如何建db_link访问远端的数据库。

在本地开global_names=true时，如何建db_link访问远端的数据库。

远端：
ys@R2> select * from global_name
  2  ;
GLOBAL_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------
R2


lisener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME=r2)
      (ORACLE_HOME = /u01/app/oracle/product/11g )
      (SID_NAME = r2)
    )
  )
GLOBAL_DBNAME

tnsname.ora
R2=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = R2)
    )
  )


本地：
create  public database link qn connect to qn identified by "qn" using 'R2'
sys@R2> alter system set global_names=false ;
System altered.
sys@R2> desc qn@qn
 Name                                               Null?    Type
 ------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
 Q                                                NUMBER(38)
sys@R2> alter system set global_names=true  ;
System altered.
sys@R2> desc qn@qn
ERROR:
ORA-02085: database link QN connects to R2

[oracle@node1 ~]$ oerr ora 02085
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
//  The connection is rejected.
// *Action: create a database link with the same name as the database it
//  connects to, or set global_names=false.





远端：10.3.4.110   r2
本地  ：192.168.56.10  devdb

本地  
SQL> show parameter global_names

NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
global_names                 boolean                   FALSE


create  public database link qn connect to qn identified by "qn"
　　 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.4.110)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME =r2)))';

SQL> desc qn@qn
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 Q                            NUMBER(38)
 
SQL> alter system set global_names=true ;

System altered.

SQL> desc qn@qn
ERROR:
ORA-02085: database link QN.WMARE.COM connects to R2

02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
//  The connection is rejected.
// *Action: create a database link with the same name as the database it
//  connects to, or set global_names=false.


drop public database link qn ;

create  public database link r2 connect to qn identified by "qn"
　　 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.4.110)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME =r2)))';

SQL> desc qn@r2
ERROR:
ORA-02085: database link R2.WMARE.COM connects to R2

drop public database link R2 ;

SQL> select * from global_name ;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEVDB.WMARE.COM



目标端：
alter system set db_domain='cn' scope=spfile ;
SQL> select * from global_name ;

GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
R2.cn

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME =r2.cn)
      (ORACLE_HOME = /u01/app/oracle/product/11g )
      (SID_NAME = r2)
    )
  )
 

本地：
 create public database link  R2.cn                                  
      connect to qn identified by qn
      using '(DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST =10.3.4.110)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = R2)
                )
              )';   


 
SQL> show parameter db_unique_name

NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
global_names                 boolean                   TRUE


SQL> desc qn@r2.cn
 Name                                                           Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 Q        




远端：
alter system set db_domain='WMARE.COM' scope=spfie;
startup force ;
alter database rename global_name = 'R2.wmare.com' ;

sys@R2> show parameter db_domain

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_domain                 string     wmare.com
sys@R2> select * from global_name
  2  ;

GLOBAL_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------
R2.WMARE.COM

本地：
 create public database link  R2                                
      connect to qn identified by qn
      using '(DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST =10.3.4.110)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = R2)
                )
              )';   

SQL> show parameter db_domain

NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
db_domain                 string                   wmare.com

SQL> select * from global_name
  2  ;

GLOBAL_NAME
--------------------------------------------------------------------------------
DEVDB.WMARE.COM

SQL> show parameter global_names

NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
global_names                 boolean                   TRUE

SQL> desc qn@r2
 Name                                                           Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 Q                                                                NUMBER(38)
 
 至此ORA-02085 问题，通过修改db_domain解决