OraFAQ Forum: Networking and Gateways » Connection problem with DBLINK

Home » RDBMS Server » Networking and Gateways » Connection problem with DBLINK (Oracle 11g r2)

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

Connection problem with DBLINK [message #603482]

Mon, 16 December 2013 23:25

mokarem
Messages: 109
Registered: November 2013
Location: Dhaka

Senior Member

Hi all,

I have two phyiscally separated DB(Development and Test). I connect to both using SQL Developer, version 3.2.There is no Oracle Client installed in my PC and so there is no TNS file in my PC.I have created a DBLINK using SQL Developer, to connect from Test DB to Development. But I am not getting the connection using this DBLINK and getting following error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Need help. Thanks.

Report message to a moderator

Re: Connection problem with DBLINK [message #603483 is a reply to message #603482]

Mon, 16 December 2013 23:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ORA-12514 ALWAYS only occurs due to a problem on DB Server system.
One cause of this problem is when the Oracle database is down & needs to be started.
A remote client send a request to the Listener asking to be connected to a specific service.
If/when the listener does not know anything about that service, the listener responds with ORA-12514

post results from the following two OS commands:
lsnrctl status
lsnrctl service

Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

*This line contains valuable debugging details. So post this line & surrounding lines.*

for additional debugging suggestions read the URL below:
http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

Report message to a moderator

Re: Connection problem with DBLINK [message #603484 is a reply to message #603483]

Mon, 16 December 2013 23:43

mokarem
Messages: 109
Registered: November 2013
Location: Dhaka

Senior Member

The Complete Error message is as below:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
12514. 00000 - "TNS:listener does not currently know of service requested in connect descriptor"
*Cause: The listener received a request to establish a connection to a
database or other service. The connect descriptor received by the listener
specified a service name for a service (usually a database service)
that either has not yet dynamically registered with the listener or has
not been statically configured for the listener. This may be a temporary
condition such as after the listener has started, but before the database
instance has registered with the listener.
*Action:
- Wait a moment and try to connect a second time.
- Check which services are currently known by the listener by executing:
lsnrctl services <listener name>
- Check that the SERVICE_NAME parameter in the connect descriptor of the
net service name used specifies a service known by the listener.
- If an easy connect naming connect identifier was used, check that
the service name specified is a service known by the listener.
- Check for an event in the listener.log file.

Note: I am connecting to both DB server using my local PC where there is no oracle client is installed.

Report message to a moderator

Re: Connection problem with DBLINK [message #603487 is a reply to message #603484]

Mon, 16 December 2013 23:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

Report message to a moderator

Re: Connection problem with DBLINK [message #603489 is a reply to message #603487]

Mon, 16 December 2013 23:58

mokarem
Messages: 109
Registered: November 2013
Location: Dhaka

Senior Member

I have executed following SQL using my Test DB Connection

select * from dual@DLNK_DEV_PSTG.EU.NOVARTIS.NET

Details of My DB LINK is as Below:

DB_LINK: DLNK_DEV_PSTG.EU.NOVARTIS.NET
USERNAME: PR_PSTG_ADMIN
HOST: PHCHBS-SD260001.eu.novartis.net:1521/DVOPRISM

Report message to a moderator

Re: Connection problem with DBLINK [message #603490 is a reply to message #603489]

Tue, 17 December 2013 00:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

since you decide to ignore previously posted response, I won't waste more time here

Report message to a moderator

Re: Connection problem with DBLINK [message #603492 is a reply to message #603490]

Tue, 17 December 2013 00:09

mokarem
Messages: 109
Registered: November 2013
Location: Dhaka

Senior Member

Sorry. Actually I am using SQL Developer which is plugable and there is no client installed in my local PC.

Following commands won't be executed in my local PC

lsnrctl status
lsnrctl service

And hence there no listener.log file in my local PC

Report message to a moderator

Re: Connection problem with DBLINK [message #603494 is a reply to message #603492]

Tue, 17 December 2013 00:15

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you have no access to the db servers then you can't do anything, ask your DBA.

Report message to a moderator

Re: Connection problem with DBLINK [message #603495 is a reply to message #603492]

Tue, 17 December 2013 00:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ORA-12514 ALWAYS only occurs due to a problem on DB Server system.

Report message to a moderator

Re: Connection problem with DBLINK [message #603497 is a reply to message #603492]

Tue, 17 December 2013 00:21

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How exactly did you create that database link? Please, post the whole CREATE DATABASE LINK statement (mask password). Did you test whether the database link is correctly created? Oracle will always say "yes", but you have to test it. Here's an example (obvious rubbish & misleading information):

SQL> create database link dbl_will_fail
  2  connect to laksjdflasjf
  3  identified by alsdjkfaosif
  4  using 'oiqwrqowirjoj';

Database link created.              --> misleading information!

SQL> select * from dual@dbl_will_fail;
select * from dual@dbl_will_fail
                   *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

Report message to a moderator

Re: Connection problem with DBLINK [message #603499 is a reply to message #603497]

Tue, 17 December 2013 00:32

mokarem
Messages: 109
Registered: November 2013
Location: Dhaka

Senior Member

I have created DBLINK using wizard of SQL DEVELOPER, 'New Database Link...' option.

As service name I provided below:

PHCHBS-SD260001.eu.novartis.net:1521/DVOPRISM

'Fixed User' is selected by default. It is a local DBLINK.

Report message to a moderator

Re: Connection problem with DBLINK [message #603504 is a reply to message #603499]

Tue, 17 December 2013 01:53

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What does mean "It is a local DBLINK"?

Quote:

As service name I provided below:

PHCHBS-SD260001.eu.novartis.net:1521/DVOPRISM

This is not a service name.

Forget the wizard and use the statement Littlefoot gave.
The last part ("using") is a tnsnames.ora entry ON THE SERVER.

Report message to a moderator

Re: Connection problem with DBLINK [message #603506 is a reply to message #603504]

Tue, 17 December 2013 02:00

mokarem
Messages: 109
Registered: November 2013
Location: Dhaka

Senior Member

There are two types of DBLINK. Local and Public.

In my PC from where I am conenctiong to DB there is no TNSNAME.ora file. No client to connect to DB. I am using plugable SQL Developer and connecting to DB using Host name.

Report message to a moderator

Re: Connection problem with DBLINK [message #603515 is a reply to message #603506]

Tue, 17 December 2013 03:17

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Read what people tell you. TNSNAMES.ORA is a file located [i]on a database server[/b], not your own PC. Database you are trying to create a database link to must be specified in that TNSNAMES.ORA file, on the server.

Report message to a moderator

Re: Connection problem with DBLINK [message #603566 is a reply to message #603482]

Tue, 17 December 2013 07:17

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

The key to the problem comes in the understanding that when you are using a dblink, the database itself (the one in which the link is defined) is taking on the role of 'client'. So when you read through helps on debugging this error, you have to keep firmly in mind that when the discussion talks about 'client', it is (in the case of a db link) talking about the database (and it's host server) in which the dblink is defined.

SQLDeveloper -----> databaseA ---dblink---> databaseB

In the above, sqldev is the client to databaseA, but databaseA is the client to databaseB

Report message to a moderator

Re: Connection problem with DBLINK [message #603573 is a reply to message #603489]

Tue, 17 December 2013 10:10

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

mokarem wrote on Tue, 17 December 2013 05:58

I have executed following SQL using my Test DB Connection

select * from dual@DLNK_DEV_PSTG.EU.NOVARTIS.NET

Details of My DB LINK is as Below:

DB_LINK: DLNK_DEV_PSTG.EU.NOVARTIS.NET
USERNAME: PR_PSTG_ADMIN
HOST: PHCHBS-SD260001.eu.novartis.net:1521/DVOPRISM

Let me have a try to assist. If the link was created with the host specified as above, then no tnsnames.ora file is needed. Mokarem, please can you confirm this by running this query:

select host from user_db_links where db_link='DLNK_DEV_PSTG.EU.NOVARTIS.NET';

You say that you get an ora-12514. That means that the listener on B does not know of the service DVOPRISM. You need to ask your DBA to give you the output of running

lsnrctl status

on server B to diagnose this.

--update:
better explain: I was using "B" because that was what Ed used. I should have said "Development", which was the term you used in your first post for the remote server.

[Updated on: Tue, 17 December 2013 10:14]

Report message to a moderator

Previous Topic:	Tns connection not established
Next Topic:	Read SQL Server table from Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 19:39:17 CST 2025