Today, I have spent quite some time to try to get a db link from a 10g db (DBWRKEV1) to a 9i db (DBWRK920) work.
At DBWRKEV1, we can see the global_name has a funny suffix.
z9xxx94@DBWRKEV1> select * from global_name;
GLOBAL_NAME
---------------------------------------------------------
DBWRKEV1.REGRESS.RDBMS.DEV.US.ORACLE.COM
Based on this post, I tried to remove it by this way:
SQL> show user;
USER is "SYS"
SQL> update GLOBAL_NAME SET GLOBAL_NAME='DBWRKEV1';
1 row updated.
SQL> commit;
Commit complete.
z9xxx94@DBWRKEV1> select * from global_name;
GLOBAL_NAME
---------------------------------------------
DBWRKEV1
Then, I tried to create the db link again:
z9xxx94@DBWRKEV1> create database link dbwrk920 connect to z9xxx94 identified by vxxx using 'dbwrk920';
Database link created.
z9xxx94@DBWRKEV1> select * from global_name@dbwrk920;
select * from global_name@dbwrk920
*
ERROR at line 1:
ORA-02085: database link DBWRK920.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to DBWRK920
Still, I have not succeeded. It should be noted that the initialization parameter global_names is TRUE in the DBWRKEV1(10g)
Finally, I was able to use the db link through following approach:
At DBWRK920
z9xxx94@DBWRK920> select * from global_name;
GLOBAL_NAME
---------------------------------------------------------------------------
DBWRK920
z9xxx94@DBWRK920> alter database rename global_name to dbwrk920.world;
Database altered.
z9xxx94@DBWRK920> select * from global_name;
GLOBAL_NAME
---------------------------------------------------------------------------
DBWRK920.WORLD
At DBWRKEV1
z9xxx94@DBWRKEV1> create database link dbwrk920.world connect to z9xxx94 identified by vxxx using 'dbwrk920';
Database link created.
z9xxx94@DBWRKEV1>
z9xxx94@DBWRKEV1> select * from global_name@dbwrk920.world;
GLOBAL_NAME
--------------------------------------------------------------
DBWRK920.WORLD
AWR Snap ID
2 weeks ago
1 comment:
Denis,
Thanks for the comment and cross linking to my blog.
I had a look at your solution and I think if both of your server had GLOBAL_NAMES set to true and GLOBAL_NAME was without any suffix, it should have work without any issue. See below where I have tries something similar,
[oracle@oratestbox admin]$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jul 14 11:15:32 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production
SYS@erpstage> select * from global_name;
GLOBAL_NAME
-------------------------------------------------------------------------------
ERPSTAGE
Elapsed: 00:00:00.00
SYS@erpstage> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SYS@erpstage> alter session set global_names=true;
Session altered.
Elapsed: 00:00:00.01
SYS@erpstage> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SYS@erpstage> CREATE DATABASE LINK "ERP_DIFF" CONNECT TO "test_ro" IDENTIFIED BY test USING 'ERP';
Database link created.
Elapsed: 00:00:00.26
SYS@erpstage> select * from global_name@ERP_DIFF;
select * from global_name@ERP_DIFF
*
ERROR at line 1:
ORA-02085: database link ERP_DIFF connects to ERP
Elapsed: 00:00:00.59
SYS@erpstage> CREATE DATABASE LINK "ERP" CONNECT TO "test_ro" IDENTIFIED BY test USING 'ERP';
Database link created.
Elapsed: 00:00:00.01
SYS@erpstage> select * from global_name@ERP;
GLOBAL_NAME
------------------------------------------------------------------------------------------------------
ERP
Elapsed: 00:00:00.50
SYS@erpstage> select value from sys.v_$parameter@ERP where name='global_names';
VALUE
------------------------------------------------------------------------------------------------------
TRUE
Elapsed: 00:00:00.12
SYS@erpstage>
Post a Comment