Home » RDBMS Server » Networking and Gateways » Connection problem with DBLINK (Oracle 11g r2)
Connection problem with DBLINK [message #603482] Mon, 16 December 2013 23:25 Go to next message
mokarem
Messages: 44
Registered: November 2013
Location: Dhaka
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.
Re: Connection problem with DBLINK [message #603483 is a reply to message #603482] Mon, 16 December 2013 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
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/
Re: Connection problem with DBLINK [message #603484 is a reply to message #603483] Mon, 16 December 2013 23:43 Go to previous messageGo to next message
mokarem
Messages: 44
Registered: November 2013
Location: Dhaka
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.


Re: Connection problem with DBLINK [message #603487 is a reply to message #603484] Mon, 16 December 2013 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
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.
Re: Connection problem with DBLINK [message #603489 is a reply to message #603487] Mon, 16 December 2013 23:58 Go to previous messageGo to next message
mokarem
Messages: 44
Registered: November 2013
Location: Dhaka
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
Re: Connection problem with DBLINK [message #603490 is a reply to message #603489] Tue, 17 December 2013 00:02 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
since you decide to ignore previously posted response, I won't waste more time here
Re: Connection problem with DBLINK [message #603492 is a reply to message #603490] Tue, 17 December 2013 00:09 Go to previous messageGo to next message
mokarem
Messages: 44
Registered: November 2013
Location: Dhaka
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
Re: Connection problem with DBLINK [message #603494 is a reply to message #603492] Tue, 17 December 2013 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59079
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Connection problem with DBLINK [message #603495 is a reply to message #603492] Tue, 17 December 2013 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
ORA-12514 ALWAYS only occurs due to a problem on DB Server system.
Re: Connection problem with DBLINK [message #603497 is a reply to message #603492] Tue, 17 December 2013 00:21 Go to previous messageGo to next message
Littlefoot
Messages: 19601
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>
Re: Connection problem with DBLINK [message #603499 is a reply to message #603497] Tue, 17 December 2013 00:32 Go to previous messageGo to next message
mokarem
Messages: 44
Registered: November 2013
Location: Dhaka
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.
Re: Connection problem with DBLINK [message #603504 is a reply to message #603499] Tue, 17 December 2013 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59079
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Connection problem with DBLINK [message #603506 is a reply to message #603504] Tue, 17 December 2013 02:00 Go to previous messageGo to next message
mokarem
Messages: 44
Registered: November 2013
Location: Dhaka
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.
Re: Connection problem with DBLINK [message #603515 is a reply to message #603506] Tue, 17 December 2013 03:17 Go to previous messageGo to next message
Littlefoot
Messages: 19601
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.
Re: Connection problem with DBLINK [message #603566 is a reply to message #603482] Tue, 17 December 2013 07:17 Go to previous messageGo to next message
EdStevens
Messages: 271
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
Re: Connection problem with DBLINK [message #603573 is a reply to message #603489] Tue, 17 December 2013 10:10 Go to previous message
John Watson
Messages: 4552
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:
  


Current Time: Tue Sep 16 04:27:52 CDT 2014

Total time taken to generate the page: 0.09529 seconds