Solved

getting error to use DB link

Posted on 2011-09-19
11
341 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 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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 76

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 84
Component is listed with a Protocol more than once 3 26
grant user/role question 11 25
Oracle dataguard 5 30
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

808 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