Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

getting error to use DB link

Posted on 2011-09-19
11
Medium Priority
?
349 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
  • 6
  • 5
11 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36562508
From a command prompt on kedwd post the results of:
lsnrctl status
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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

Technology Partners: 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

972 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