Solved

getting error to use DB link

Posted on 2011-09-19
11
343 Views
Last Modified: 2012-05-12
Hi,
I created DB link and when I try to use It I am getting the following error.
Error : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. I have database entry in the tnsnames.ora on server and as well as on client.

Please help me,,,,,
0
Comment
Question by:nirvairghuman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
ID: 36562391
From a command prompt on the database server where you created the link:
tnsping dbalias

where dbalias is the tnsnames.ora alias you provided in the USING portion of the create database link command.
0
 

Author Comment

by:nirvairghuman
ID: 36562487
From a command prompt on the database server where you created the link:
tnsping dbalias

TNS-03505: Failed to resolve name
kedwd:/ > tnsping mdmd2

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production on 19-SEP-2011 14:45:57

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
/s01/ora11.1.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = kedwd)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mdm2.kedwd) (SID = mdm2)))
OK (10 msec)

where dbalias is the tnsnames.ora alias you provided in the USING portion of the create database link command.

I am typing (using 'mdmd2')  full name of the database.


0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 36562508
From a command prompt on kedwd post the results of:
lsnrctl status
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:nirvairghuman
ID: 36562586

LSNRCTL for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production on 19-SEP-2011 14:59:31

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kedwd)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
Start Date                03-AUG-2011 03:01:16
Uptime                    47 days 11 hr. 58 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /s01/ora11.1.0/network/admin/listener.ora
Listener Log File         /s01/diag/tnslsnr/kedwd/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kedwd.independenthealth.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "diw.kedwd" has 2 instance(s).
  Instance "diw", status UNKNOWN, has 1 handler(s) for this service...
  Instance "diw", status READY, has 1 handler(s) for this service...
Service "diw2.kedwd" has 2 instance(s).
  Instance "diw2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "diw2", status READY, has 1 handler(s) for this service...
Service "diw2XDB.kedwd" has 1 instance(s).
  Instance "diw2", status READY, has 1 handler(s) for this service...
Service "diw2_XPT.kedwd" has 1 instance(s).
  Instance "diw2", status READY, has 1 handler(s) for this service...
Service "diwXDB.kedwd" has 1 instance(s).
  Instance "diw", status READY, has 1 handler(s) for this service...
Service "diw_XPT.kedwd" has 1 instance(s).
  Instance "diw", status READY, has 1 handler(s) for this service...
Service "mdm.kedwd" has 2 instance(s).
  Instance "mdm", status UNKNOWN, has 1 handler(s) for this service...
  Instance "mdm", status READY, has 1 handler(s) for this service...
Service "mdm2.kedwd" has 2 instance(s).
  Instance "mdm2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "mdm2", status READY, has 1 handler(s) for this service...
Service "mdm2XDB.kedwd" has 1 instance(s).
  Instance "mdm2", status READY, has 1 handler(s) for this service...
Service "mdm2_XPT.kedwd" has 1 instance(s).
  Instance "mdm2", status READY, has 1 handler(s) for this service...
Service "mdmXDB.kedwd" has 1 instance(s).
  Instance "mdm", status READY, has 1 handler(s) for this service...
Service "mdm_XPT.kedwd" has 1 instance(s).
  Instance "mdm", status READY, has 1 handler(s) for this service...
The command completed successfully
0
 

Author Comment

by:nirvairghuman
ID: 36562593
Both databases are on same server
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 36562675
>>I am typing (using 'mdmd2')  full name of the database.

I don't see an mdmd2.  I see an mdm2.
0
 

Author Comment

by:nirvairghuman
ID: 36562712
As you know mdm2 is the name of the sid but full name of the database is mdmd2
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 36562719
>>Instance "mdm2", status READY, has 1 handler(s) for this service...

The listener is listening for mdm2.  This is what is necessary in the service_name in your tnsnames.ora file.

0
 

Author Comment

by:nirvairghuman
ID: 36562743
I have checked both the tnsnames.ora on server and client as well, both the files have entries.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 36562775
>>both the tnsnames.ora on server and client as well

If both databases are on the same machine why do you have more than one install?

Please post the entry for mdmd2 in your tnsnames.ora file.
0
 

Author Comment

by:nirvairghuman
ID: 36563198
i think one was 10g and upgraded to 11g,but not sure I am new.

Thanks a lot Slight issue is resolved,, without any change
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question