Home » RDBMS Server » Networking and Gateways » DBLink Creation - ORA-12154
DBLink Creation - ORA-12154 [message #242963] Tue, 05 June 2007 11:08 Go to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
Hello, I'm trying to create a DBLink between 2 databases.

Distant database = baseD
Local database = baseL

To do, I created this DBLINK :

CREATE DATABASE LINK sourceID connect to login identified by pwd using 'alias1';

Extract of TNS_NAMES.ora :

alias1.world=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = oratoto1)
(Port = 1521)
)

)
(CONNECT_DATA = (SID = sourceID)
)
)

I use this alias for others connections without any problems. These informations are OK.

But when I try to request data in the Distant DB, Oracle can't resolve service name (error ORA-12154)

select * from table1@sourceID;

ORA-12154 TNS : could not resolve service name

I'm working with Toad.

Can you help me ?
Re: DBLink Creation - ORA-12154 [message #242964 is a reply to message #242963] Tue, 05 June 2007 11:12 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
CREATE DATABASE LINK sourceID connect to login identified by pwd using 'alias1';




use db_domain at the place of alias1.
Re: DBLink Creation - ORA-12154 [message #242965 is a reply to message #242964] Tue, 05 June 2007 11:14 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
CREATE DATABASE LINK sourceID connect to login identified by pwd using sourceID;

like that ?
Re: DBLink Creation - ORA-12154 [message #242968 is a reply to message #242965] Tue, 05 June 2007 11:19 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
The real syntax of my creation order is :

CREATE DATABASE LINK sourceID connect to login identified by pwd using 'alias1.world';

what do I have to change ?
Re: DBLink Creation - ORA-12154 [message #242969 is a reply to message #242968] Tue, 05 June 2007 11:21 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
sorry not db domain ...use service _names

show parameter service_names
Re: DBLink Creation - ORA-12154 [message #242977 is a reply to message #242969] Tue, 05 June 2007 11:31 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
like this ?

CREATE DATABASE LINK sourceID connect to login identified by pwd using sourceID;

alias1.world=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = oratoto1)
(Port = 1521)
)

)
(CONNECT_DATA = (SID = sourceID)
)
)
Re: DBLink Creation - ORA-12154 [message #242981 is a reply to message #242977] Tue, 05 June 2007 11:35 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      mydb.xxxx.xxxx.com
 CREATE DATABASE LINK DEV01_mydb
     CONNECT TO  oracle
     IDENTIFIED BY  alrmoracle
     USING 'mydb.xxxx.xxxx.com';

[Updated on: Tue, 05 June 2007 11:36]

Report message to a moderator

Re: DBLink Creation - ORA-12154 [message #242988 is a reply to message #242981] Tue, 05 June 2007 12:10 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
KO...

Thank you but I have the same problem
Re: DBLink Creation - ORA-12154 [message #242989 is a reply to message #242977] Tue, 05 June 2007 12:13 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
alias1.world


Does this works? user/password@alias1.world


the both database are on same machine? or different.
Re: DBLink Creation - ORA-12154 [message #242990 is a reply to message #242968] Tue, 05 June 2007 12:15 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Check this

http://www.psoug.org/reference/db_link.html
Re: DBLink Creation - ORA-12154 [message #243010 is a reply to message #242989] Tue, 05 June 2007 13:26 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
the both databases are on different machines
Re: DBLink Creation - ORA-12154 [message #243013 is a reply to message #243010] Tue, 05 June 2007 13:36 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
(CONNECT_DATA = (SID = sourceID)


SOURCEID is ur dbname??
Quote:
Distant database = baseD
Local database = baseL


???


can you paste tnsnames entires from both machine?

[Updated on: Tue, 05 June 2007 13:39]

Report message to a moderator

Re: DBLink Creation - ORA-12154 [message #243022 is a reply to message #242989] Tue, 05 June 2007 13:55 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
I know this site but I can't find my error...
Re: DBLink Creation - ORA-12154 [message #243024 is a reply to message #243022] Tue, 05 June 2007 13:59 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
post your tnsnames.ora file and dbnames.
Re: DBLink Creation - ORA-12154 [message #243025 is a reply to message #242963] Tue, 05 June 2007 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
OS name & version?
Oracle version to FOUR decimal places?
Re: DBLink Creation - ORA-12154 [message #243026 is a reply to message #243013] Tue, 05 June 2007 14:03 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
baseDistant.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = oracle04)
(Port = 1521)
)

)
(CONNECT_DATA = (SID = TOTO21)
)
)

baseLocal.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = oradev01)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = oradev01)
(Port = 1526)
)
)
(CONNECT_DATA = (SID = TITI30)
)
)

thank you for your help
Re: DBLink Creation - ORA-12154 [message #243028 is a reply to message #242963] Tue, 05 June 2007 14:06 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
Oracle 9.2

Local DB :
Global_name=TITI30

Distant DB :
Global_name=TOTO21.world
Re: DBLink Creation - ORA-12154 [message #243030 is a reply to message #243026] Tue, 05 June 2007 14:14 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = 
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = oradev01)
(Port = 1521)
)
(ADDRESS = 
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = oradev01)
(Port = 1526)
)
)
(CONNECT_DATA = (SID = TITI30)
)
)



use this code

(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = 
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = oradev01)
(Port = 1521)
)
(ADDRESS = 
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(CONNECT_DATA = (SID = TITI30)
)
)

Re: DBLink Creation - ORA-12154 [message #243031 is a reply to message #242963] Tue, 05 June 2007 14:16 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You can lead some people to knowledge, but you can NOT make them think!
Operating System name & version????????????????????
>Oracle 9.2
Oracle version to FOUR decimal places?
What part of "to FOUR decimal places" do you NOT understand?
You're On Your Own (YOYO)!
Re: DBLink Creation - ORA-12154 [message #243033 is a reply to message #242963] Tue, 05 June 2007 14:34 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
Windows 2000 Version 5.0.2195

Oracle Client (9.2)

Oracle Client (8.1.7.2.1 ODBC 8.1.7.5)

thank you anacedent for this moral lesson...
Re: DBLink Creation - ORA-12154 [message #243236 is a reply to message #242963] Wed, 06 June 2007 10:26 Go to previous messageGo to next message
michael.mazuk
Messages: 14
Registered: June 2007
Junior Member
I tried to change my TNSNAME... KO.
Re: DBLink Creation - ORA-12154 [message #243240 is a reply to message #243236] Wed, 06 June 2007 10:35 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
still doesn't work??
Re: DBLink Creation - ORA-12154 [message #274667 is a reply to message #243240] Tue, 16 October 2007 14:46 Go to previous messageGo to next message
gnbc
Messages: 1
Registered: October 2007
Junior Member
I register just to reply this.
The solution for this folows:

****INSTEAD OF:

CREATE DATABASE LINK prod
CONNECT TO scott IDENTIFIED BY tiger
USING 'prod';

****TRY THIS:

CREATE DATABASE LINK prod
CONNECT TO scott IDENTIFIED BY tiger
USING '(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=....)(PORT=1521))
)
(CONNECT_DATA=
(SID=prod)
)
)';

The las was get on TNSNAMES.ORA. Just put your host and your sid. Works fine to me.

I hope this help you

Gabriel Novy B. Chaves

[Updated on: Tue, 16 October 2007 14:47]

Report message to a moderator

Re: DBLink Creation - ORA-12154 [message #274673 is a reply to message #274667] Tue, 16 October 2007 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I register just to reply this.

I don't know it this will work but this is nice from you.

Regards
Michel
Re: DBLink Creation - ORA-12154 [message #274768 is a reply to message #242963] Wed, 17 October 2007 01:50 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:

****INSTEAD OF:

CREATE DATABASE LINK prod
CONNECT TO scott IDENTIFIED BY tiger
USING 'prod';

****TRY THIS:

CREATE DATABASE LINK prod
CONNECT TO scott IDENTIFIED BY tiger
USING '(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=....)(PORT=1521))
)
(CONNECT_DATA=
(SID=prod)
)
)';


Whats wrong with the first one if I just put an entry in tnsnames.ora holding prod !
Previous Topic: ora -12547 TNS: lost contact
Next Topic: Client Side Application Hanging when connecting to Database
Goto Forum:
  


Current Time: Mon Dec 05 19:21:56 CST 2016

Total time taken to generate the page: 0.17818 seconds