Recently came across installations where all DB link were having suffix US.ORACLE.COM or REGRESS.RDBMS.DEV.US.ORACLE.COM and was bit annoying for users so here is how we went forward to fix it.
As per documentation , whenever we create DB link, the db_domain is automatically appended to it. So following SQL should help determines naming of all DB links,
select name,value from v_$parameter where name IN ('db_name', 'db_domain');
db_name = MYDB
db_domain = NULL
So in our case db_domain was NULL then where does US.ORACLE.COM or REGRESS.RDBMS.DEV.US.ORACLE.COM come form, lets check the global name, in case you dont know, GLOBAL_NAME = db_name.db_domain
select global_name from global_name;
global_name = MYDB.US.ORACLE.COM
hmm so global name does show me domain name now, how do I fix it?
Option 1 :
Rename global name with desired domain name e.g. MYWORLD
alter database rename global_name to MYDB.MYWORLD;
Please note with above alter statement, you MUST specify db_domain else it wont work.
Option 2 :
In case you dont wont any domain, Update global name to db_name without db_domain
UPDATE GLOBAL_NAME SET GLOBAL_NAME = 'MYDB';
commit;
Now cross check the changes,
select global_name from global_name ;
global_name = MYDB
Now you can go and create DB link as any user and it will be as expected i.e. with desired suffix or without.
While we are on DB link topic, please note global_name <> global_names i.e.
Global Name is global name of the DB determined by db_name.db_domain
Global Names is a parameter which determines whether DB link should always be named AFTER the global name of the database it connects to. You can set parameter value to be TRUE or FALSE (default) . If you have a replication environment then it is in general a good thing to set it to true.
Subscribe to:
Post Comments (Atom)
Posts
3 comments:
Thanks for the infomation. I did some test based on your tip. However, I still have unwanted suffix to the dblink after update:
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
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
Note
The global_name is true in dbwrkev1(10g). dbwrk920 is a 9i db.
Spend some time with dblink , but no luck so far.
BTW, I found a way to create the db link I wanted. See: this link
Denis,
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