OraFAQ Forum: SQL & PL/SQL » DB link issue in cloned databases on same host

Home » SQL & PL/SQL » SQL & PL/SQL » DB link issue in cloned databases on same host ( 11.1.0.7.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DB link issue in cloned databases on same host [message #679002]

Tue, 28 January 2020 00:54

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?

Report message to a moderator

Re: DB link issue in cloned databases on same host [message #679003 is a reply to message #679002]

Tue, 28 January 2020 01:24

Michel Cadot
Messages: 68421
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.

Report message to a moderator

Re: DB link issue in cloned databases on same host [message #679004 is a reply to message #679003]

Tue, 28 January 2020 01:59

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.

Report message to a moderator

Re: DB link issue in cloned databases on same host [message #679005 is a reply to message #679004]

Tue, 28 January 2020 02:09

Michel Cadot
Messages: 68421
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)?

Report message to a moderator

Re: DB link issue in cloned databases on same host [message #679006 is a reply to message #679004]

Tue, 28 January 2020 06:53

EdStevens
Messages: 1375
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

John Watson
Messages: 8805
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.

Report message to a moderator

Re: DB link issue in cloned databases on same host [message #679010 is a reply to message #679007]

Tue, 28 January 2020 07:29

EdStevens
Messages: 1375
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.

Report message to a moderator