Home » SQL & PL/SQL » SQL & PL/SQL » DB link issue in cloned databases on same host ( 11.1.0.7.0)
DB link issue in cloned databases on same host [message #679002] Tue, 28 January 2020 00:54 Go to next message
aloksk
Messages: 3
Registered: January 2020
Junior Member
I two cloned database on same host.

database link is defined with domain on both like abc.domain.com

Link works on both database as :
select sysdate from dual@abc.domain.com;

but fails on one of the database:
select sysdate from dual@abc;

Any idea's?
Re: DB link issue in cloned databases on same host [message #679003 is a reply to message #679002] Tue, 28 January 2020 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

"fails" is not an Oracle error.
There can be several roots depending on the actual error.
Use SQL*Plus and copy and paste your session, the WHOLE session.


Re: DB link issue in cloned databases on same host [message #679004 is a reply to message #679003] Tue, 28 January 2020 01:59 Go to previous messageGo to next message
aloksk
Messages: 3
Registered: January 2020
Junior Member
On DB 1:
select * from dba_db_links where DB_LINK like 'TRAINING%' order by 2;SQL> SQL> SQL> SQL>

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------
PUBLIC TRAINING.domain.COM ****** trainING.WORLD 20-DEC-19

SQL> select sysdate from dual@TRAINING.domain.COM;
select sysdate from dual@TRAINING;
SYSDATE
---------
28-JAN-20

SQL>

SYSDATE
---------
28-JAN-20


On DB 2(clone of DB1)
select * from dba_db_links where DB_LINK like 'TRAINING%' order by 2;SQL> SQL> SQL> SQL>

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------
PUBLIC TRAINING.domain.COM ********* trainING.WORLD 20-DEC-19

SQL> select sysdate from dual@TRAINING.domain.COM;
select sysdate from dual@TRAINING;
SYSDATE
---------
28-JAN-20

SQL>
select sysdate from dual@TRAINING
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

Both DB's are on same host, have same OH and TNS_ADMIN.
Re: DB link issue in cloned databases on same host [message #679005 is a reply to message #679004] Tue, 28 January 2020 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please put your code and SQL*Plus session between code tags like:
[code]SQL> select sysdate from dual;
SYSDATE
-------------------
28/01/2020 09:06:50

1 row selected.[/code]

which then gives when displayed:
SQL> select sysdate from dual;
SYSDATE
-------------------
28/01/2020 09:06:50

1 row selected.
What is the value of DB_DOMAIN in both instances (query v$parameter)?

Re: DB link issue in cloned databases on same host [message #679006 is a reply to message #679004] Tue, 28 January 2020 06:53 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
aloksk wrote on Tue, 28 January 2020 01:59
On DB 1:
select * from dba_db_links where DB_LINK like 'TRAINING%' order by 2;SQL> SQL> SQL> SQL>

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------
PUBLIC TRAINING.domain.COM ****** trainING.WORLD 20-DEC-19

SQL> select sysdate from dual@TRAINING.domain.COM;
select sysdate from dual@TRAINING;
SYSDATE
---------
28-JAN-20

SQL>

SYSDATE
---------
28-JAN-20


On DB 2(clone of DB1)
select * from dba_db_links where DB_LINK like 'TRAINING%' order by 2;SQL> SQL> SQL> SQL>

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------
PUBLIC TRAINING.domain.COM ********* trainING.WORLD 20-DEC-19

SQL> select sysdate from dual@TRAINING.domain.COM;
select sysdate from dual@TRAINING;
SYSDATE
---------
28-JAN-20

SQL>
select sysdate from dual@TRAINING
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

Both DB's are on same host, have same OH and TNS_ADMIN.
It appears you are selectively copying bits and pieces from your 'proof' session. There are simply too many anomolies. For instance, on the first server, you show two results, but only one query. On the second, you show two queries that are identical except one is properly terminated with ';' and the second is not. And the error ORA-02019 is inconsistent with a query that is not properly terminated.

Bottom line - I can't trust what you show to be reflective of reality.

[Updated on: Tue, 28 January 2020 06:54]

Report message to a moderator

Re: DB link issue in cloned databases on same host [message #679007 is a reply to message #679002] Tue, 28 January 2020 07:00 Go to previous messageGo to next message
John Watson
Messages: 8351
Registered: January 2010
Location: Global Village
Senior Member
Historically, there were zillions of issues (even bugs... Can you believe it...) with database domains, database global names, and database links. The contents of your sqlnet.ora file(s) is also relevant. Eventually I concluded that domains cause nothing but trouble and that it is best never to use them. Your release, 11.1.0.7, is old but should be new enough not to introduce new bugs with this but if the databases have been upgraded from previous releases you may have problems. The use of that .WORLD domain (which dates back to release 9i) suggests upgrades.

One hack that I have had to use is to delete the rows from sys.link$ and start again, with all domains removed everywhere. There is probably a better solution.
Re: DB link issue in cloned databases on same host [message #679010 is a reply to message #679007] Tue, 28 January 2020 07:29 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
John Watson wrote on Tue, 28 January 2020 07:00
Historically, there were zillions of issues (even bugs... Can you believe it...) with database domains, database global names, and database links. The contents of your sqlnet.ora file(s) is also relevant. Eventually I concluded that domains cause nothing but trouble and that it is best never to use them. Your release, 11.1.0.7, is old but should be new enough not to introduce new bugs with this but if the databases have been upgraded from previous releases you may have problems. The use of that .WORLD domain (which dates back to release 9i) suggests upgrades.

One hack that I have had to use is to delete the rows from sys.link$ and start again, with all domains removed everywhere. There is probably a better solution.
Indeed.
In sqlnet.ora, I comment out 'names.default_domain '. And in the database I unset db_domain.
Re: DB link issue in cloned databases on same host [message #679030 is a reply to message #679010] Tue, 28 January 2020 23:50 Go to previous messageGo to next message
aloksk
Messages: 3
Registered: January 2020
Junior Member
Solved: By Cloning the link to the name that was not working.
Re: DB link issue in cloned databases on same host [message #681279 is a reply to message #679030] Fri, 03 July 2020 03:04 Go to previous message
Sooqa
Messages: 1
Registered: July 2020
Junior Member
Hey there Hello thank you for the post, it was useful to me
Previous Topic: Oracle query
Next Topic: Newbie needs help with wm_concat
Goto Forum:
  


Current Time: Tue Sep 29 22:04:02 CDT 2020