Home » RDBMS Server » Server Administration » ORA-12514 error on database link
ORA-12514 error on database link [message #490809] Thu, 27 January 2011 09:45 Go to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
Hi Guru's,

One of colleague (who has left) created a database link using the statement:
CREATE PUBLIC DATABASE LINK CIVCLMANLINK USING ''CIVCLMAN''

When I try to use this link like:
SELECT * FROM CLIENTMANAGEMENT.ADDRESS@CIVCLMANLINK;

I get the following error:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

I can connect to the CIVCLMAN using the Oracle Enterprise Manager / SQL Plus, I can tnsping as well

Any ideas folks?

Thanks
Re: Database Links [message #490810 is a reply to message #490809] Thu, 27 January 2011 09:49 Go to previous messageGo to next message
Roachcoach
Messages: 1128
Registered: May 2010
Location: UK
Senior Member
iirc, DB links use the TNS names entry on the server - perhaps someone has modified this?
Re: Database Links [message #490812 is a reply to message #490810] Thu, 27 January 2011 09:51 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
are you referring to the tnsnames.ora file on the REMOTE machine?
How should this be configured? Sorry I am new to database links and tried few things but can't get it to work
I'm sure I'm missing something silly

It looks like both of the databases are on the same physical machine if that helps any body

Cheers

[Updated on: Thu, 27 January 2011 09:53]

Report message to a moderator

Re: Database Links [message #490814 is a reply to message #490812] Thu, 27 January 2011 09:55 Go to previous messageGo to next message
Roachcoach
Messages: 1128
Registered: May 2010
Location: UK
Senior Member
No, I mean the tnsnames on the server which holds the DB link.

Just because you can connect to the server the link is trying to connect to, that doesn't mean the server can.

User: TNS Names A - Contains remote data, can access
Server: TNS Names B - Does not contain remote server data, cannot access

User calls DB link on Server, server uses server TNS names (B), cant find DB - fails.



It might not be that, but its worth checking

[Updated on: Thu, 27 January 2011 09:56]

Report message to a moderator

Re: Database Links [message #490815 is a reply to message #490812] Thu, 27 January 2011 09:57 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
It'll use tnsnames.ora on the local machine, just as sqlplus on your pc will use tnsnames.ora on your pc not the db server.
Re: Database Links [message #490816 is a reply to message #490814] Thu, 27 January 2011 10:00 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
On the database server machine where ALL the databases are installed, the tnsnames.ora file contains the following entries:


# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

CIVAUDTR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.195.168)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CIVAUDTR)
)
)

CIVLYNX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.195.168)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CIVLYNX)
)
)

PSGLYNX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.195.168)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PSGLYNX)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

PSGAUDIT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.195.168)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PSGAUDIT)
)
)

PSGCLMAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.195.168)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PSGCLMAN)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.195.168)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.44.195.168)
)
)

CIVAUDIT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.195.168)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CIVAUDIT)
)
)

CIVCLMAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.195.168)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CIVCLMAN)
)
)

DEMCLMAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = svr-policetest1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PPDCLMAN)
(SERVER = DEDICATED)
)
)

DEMAUDIT =
(DESCRIPTION
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = svr-policetest1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PPDAUDIT)
(SERVER = DEDICATED)
)
)

DEMLYNX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = svr-policetest1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PPDLYNX)
(SERVER = DEDICATED)
)
)

DEMAUDTR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = svr-policetest1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = CIVAUDTR)
(SERVER = DEDICATED)
)
)




CIVLYNX which creates the database link to connect to CIVCLMAN
Both databases are on the same machine so is sharing the one tnsnames.ora file

Thanks

[Updated on: Thu, 27 January 2011 10:02]

Report message to a moderator

Re: Database Links [message #490817 is a reply to message #490816] Thu, 27 January 2011 10:03 Go to previous messageGo to next message
Roachcoach
Messages: 1128
Registered: May 2010
Location: UK
Senior Member
Then it aint that Smile
Re: Database Links [message #490818 is a reply to message #490816] Thu, 27 January 2011 10:03 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
What happens if you do
sqlplus <user>/<pass>@CIVCLMAN
on the server?
Re: Database Links [message #490819 is a reply to message #490818] Thu, 27 January 2011 10:04 Go to previous messageGo to next message
Roachcoach
Messages: 1128
Registered: May 2010
Location: UK
Senior Member
Or TNS Ping it from the server to see what files it using - its not unheard of for multiple TNS names to be kicking around.
Re: Database Links [message #490821 is a reply to message #490819] Thu, 27 January 2011 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
And compare that tnsnames.ora against the one on your pc since I assume you're running OEM from there.
Re: Database Links [message #490823 is a reply to message #490821] Thu, 27 January 2011 10:08 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
when i do user/pass@civclman it fails with the same error
when i do tnsping civclman i get the following:

C:\>tnsping civclman

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 20-JAN-2
011 10:04:30

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

Used parameter files:
D:\app\Administrator\product\11.2.0\dbhome_2\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.44.19
5.168)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CIVCLM
AN)))
OK (20 msec)

C:\>

thanks
Re: Database Links [message #490825 is a reply to message #490809] Thu, 27 January 2011 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 57638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@faizul

Please do not hijack a topic with a question that is not related to it ("database link" is not sufficient to say it is the same, otherwise there would be only ONE topic on SQL).

Regards
Michel
Re: Database Links [message #490826 is a reply to message #490825] Thu, 27 January 2011 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 57638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I splitted the topic, your last question is now under the topic named "ORA-12514 error on database link"

Regards
Michel
Re: Database Links [message #490827 is a reply to message #490825] Thu, 27 January 2011 10:19 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
hiya,

this thread was already hijacked soon after i created it

however i have found where the culprit was

some idiots keep on renaming databases

thanks anyway
Re: Database Links [message #490828 is a reply to message #490826] Thu, 27 January 2011 10:19 Go to previous messageGo to next message
Roachcoach
Messages: 1128
Registered: May 2010
Location: UK
Senior Member
I'm at a loss as to how you can successfully TNSPING but get that error using sql*plus, sorry.
Re: Database Links [message #490836 is a reply to message #490827] Thu, 27 January 2011 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 57638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
some idiots keep on renaming databases

Some people deserve some kicks somewhere... Smile

Regards
Michel
Re: Database Links [message #492334 is a reply to message #490836] Sat, 29 January 2011 09:21 Go to previous messageGo to next message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
Do you have multiple oracle homes?
You may check the tnsnames.ora file in location "D:\app\Administrator\product\11.2.0\dbhome_2\network\admin\tnsnames.ora" and make sure you have correct tns entry there.

Another way to check will be to start sqlplus from your oracle home that tnsping is using "D:\app\Administrator\product\11.2.0\dbhome_2\BIN\"... in case your path setting is not correct.
Re: Database Links [message #492435 is a reply to message #492334] Mon, 31 January 2011 04:10 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
Hello again, I've managed to correct the connection issue but now facing another one.
from DB1 I'm trying to access ADDRESS on DB2
The database link is a connected user link and there's an user LYNXTEST on both databases
I have logged in as sysdba on DB2 and have granted references/select permission on ADDRESS as follows:

SQL> GRANT REFERENCES ON CLIENTMANAGEMENT.ADDRESS TO LYNXTEST WITH GRANT OPTION;

Grant succeeded.

SQL> GRANT SELECT ON CLIENTMANAGEMENT.ADDRESS TO LYNXTEST;

Grant succeeded.

SQL>

However when I login to DB1 as LYNXTEST user I cannot select table ADDRESS as follows:
SQL> SELECT * FROM CLIENTMANAGEMENT.ADDRESS@CIVCLMANLINK;
SELECT * FROM CLIENTMANAGEMENT.ADDRESS@CIVCLMANLINK
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from CIVCLMANLINK

Could someone please tell me what permissions do I need to set on the remote database so that the local database can access the remote data

Many thanks
Re: Database Links [message #492436 is a reply to message #492435] Mon, 31 January 2011 04:12 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
That'd depend on how the db link is set up. What syntax did you use to create it?
Re: Database Links [message #492437 is a reply to message #492436] Mon, 31 January 2011 04:14 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
As per my first thread it was created using the following:
CREATE PUBLIC DATABASE LINK CIVCLMANLINK USING ''CIVCLMAN''

cheers
Re: Database Links [message #492451 is a reply to message #492437] Mon, 31 January 2011 05:11 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
That will have created a db link that is connected to the remote database using the login credentials of the user who created it. So regardless of who you are logged into the local db as you connect to the remote db as the creator of the link. I'm guessing it wasn't created by LYNXTEST.
I suggest you have a read of the documentation on managing db links
Re: Database Links [message #492469 is a reply to message #492451] Mon, 31 January 2011 05:47 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
Hiya, I logged into the local DB as sysdba and granted permission to lynxtest as follows:
SQL> GRANT CREATE DATABASE LINK TO LYNXTEST;

Grant succeeded.

SQL> GRANT CREATE PUBLIC SYNONYM TO LYNXTEST;

Grant succeeded.
SQL> GRANT DROP PUBLIC SYNONYM TO LYNXTEST;

Grant succeeded.

SQL>

and then I logged into sql plus as LYNXTEST and created the db link as follows:
SQL> CREATE DATABASE LINK CIVCLMAN USING 'CIVCLMAN';

Database link created.

BUT when I then tried to access the data as follows:
SQL> SELECT * FROM CLIENTMANAGEMENT.ADDRESS@CIVCLMAN;
SELECT * FROM CLIENTMANAGEMENT.ADDRESS@CIVCLMAN


I got the following error:

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from CIVCLMAN

Thanks
Re: Database Links [message #492514 is a reply to message #492469] Mon, 31 January 2011 19:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
Clientmanagement needs to grant select on address to lynxtest. Alternatively, the link could be created with username and password. Please see the reproduction of the error, followed by the first solution below, then the alternative solution below that. Just ignore the "@LOOPBACK" that is used for simulating and testing a link to another database in an environment with only one database.

-- reproduction of error:
SCOTT@orcl_11gR2> -- create users and grant privileges:
SCOTT@orcl_11gR2> CREATE USER clientmanagement IDENTIFIED BY clientmanagement
  2  /

User created.

SCOTT@orcl_11gR2> CREATE USER LYNXTEST IDENTIFIED BY LYNXTEST
  2  /

User created.

SCOTT@orcl_11gR2> GRANT CONNECT, RESOURCE TO clientmanagement, LYNXTEST
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> GRANT CREATE DATABASE LINK TO LYNXTEST
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> -- connect as clientmanagement, and
SCOTT@orcl_11gR2> -- create address table, enter data:
SCOTT@orcl_11gR2> CONNECT clientmanagement/clientmanagement
Connected.
CLIENTMANAGEMENT@orcl_11gR2> CREATE TABLE clientmanagement.address
  2    (test_col  VARCHAR2 (30))
  3  /

Table created.

CLIENTMANAGEMENT@orcl_11gR2> INSERT INTO clientmanagement.address (test_col)
  2  VALUES ('test data')
  3  /

1 row created.

CLIENTMANAGEMENT@orcl_11gR2> COMMIT
  2  /

Commit complete.

CLIENTMANAGEMENT@orcl_11gR2> -- connect as lynxtest,
CLIENTMANAGEMENT@orcl_11gR2> -- create database link, and
CLIENTMANAGEMENT@orcl_11gR2> -- test select from clientmanagement.address
CLIENTMANAGEMENT@orcl_11gR2> -- using database link:
CLIENTMANAGEMENT@orcl_11gR2> CONNECT LYNXTEST/LYNXTEST
Connected.
LYNXTEST@orcl_11gR2> CREATE DATABASE LINK orcl@LOOPBACK USING 'orcl'
  2  /

Database link created.

LYNXTEST@orcl_11gR2> SELECT * FROM clientmanagement.address@orcl@LOOPBACK
  2  /
SELECT * FROM clientmanagement.address@orcl@LOOPBACK
                               *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from ORCL@LOOPBACK


-- solution:
LYNXTEST@orcl_11gR2> -- connect as clientmanagement and
LYNXTEST@orcl_11gR2> -- grant select on clientmanagement.address to lynxtest:
LYNXTEST@orcl_11gR2> CONNECT clientmanagement/clientmanagement
Connected.
CLIENTMANAGEMENT@orcl_11gR2> GRANT SELECT ON clientmanagement.address TO LYNXTEST
  2  /

Grant succeeded.

CLIENTMANAGEMENT@orcl_11gR2> -- connect as lynxtest and
CLIENTMANAGEMENT@orcl_11gR2> -- test select from clientmanagement.address
CLIENTMANAGEMENT@orcl_11gR2> -- using database link:
CLIENTMANAGEMENT@orcl_11gR2> CONNECT LYNXTEST/LYNXTEST
Connected.
LYNXTEST@orcl_11gR2> SELECT * FROM clientmanagement.address@orcl@LOOPBACK
  2  /

TEST_COL
------------------------------
test data

1 row selected.

LYNXTEST@orcl_11gR2>


-- alternative solution:
LYNXTEST@orcl_11gR2> -- without select on invidual table:
LYNXTEST@orcl_11gR2> CONNECT clientmanagement/clientmanagement
Connected.
CLIENTMANAGEMENT@orcl_11gR2> REVOKE SELECT ON clientmanagement.address FROM LYNXTEST
  2  /

Revoke succeeded.

CLIENTMANAGEMENT@orcl_11gR2> -- add username and password to database link:
CLIENTMANAGEMENT@orcl_11gR2> CONNECT LYNXTEST/LYNXTEST
Connected.
LYNXTEST@orcl_11gR2> DROP DATABASE LINK orcl@LOOPBACK
  2  /

Database link dropped.

LYNXTEST@orcl_11gR2> CREATE DATABASE LINK orcl@LOOPBACK
  2  CONNECT TO clientmanagement IDENTIFIED BY clientmanagement
  3  USING 'orcl'
  4  /

Database link created.

LYNXTEST@orcl_11gR2> SELECT * FROM clientmanagement.address@orcl@LOOPBACK
  2  /

TEST_COL
------------------------------
test data

1 row selected.

LYNXTEST@orcl_11gR2>

[Updated on: Mon, 31 January 2011 20:06]

Report message to a moderator

Re: Database Links [message #492609 is a reply to message #492514] Tue, 01 February 2011 07:26 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
hiya thanks for the guidance but still no luck as can be seen below:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 1 13:17:55 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CONN CLIENTMANAGEMENT@CIVCLMAN
Enter password: ******
Connected.
SQL> GRANT SELECT ON CLIENTMANAGEMENT.ADDRESS TO LYNXTEST;

Grant succeeded.

SQL> CONN LYNXTEST@CIVLYNX
Enter password: ******
Connected.
SQL> CREATE DATABASE LINK TESTLINK USING 'CIVCLMAN'
2 /

Database link created.

SQL> SELECT * FROM CLIENTMANAGEMENT.ADDRESS@TESTLINK;
SELECT * FROM CLIENTMANAGEMENT.ADDRESS@TESTLINK
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from TESTLINK


SQL> CONN CLIENTMANAGEMENT@CIVCLMAN
Enter password: ******
Connected.
SQL> SELECT * FROM ADDRESS WHERE ADDRESS_ID = 1;

ADDRESS_ID ADDRESS_TYPE
---------- --------------------
BUILDING
--------------------------------------------------------------------------------
STREET
--------------------------------------------------------------------------------
DISTRICT
--------------------------------------------------
POST_TOWN
--------------------------------------------------
COUNTY
--------------------------------------------------
COUNTRY AREA_ POST_CO
-------------------------------------------------- ----- -------
Re: Database Links [message #492613 is a reply to message #492609] Tue, 01 February 2011 07:33 Go to previous messageGo to next message
faizul
Messages: 23
Registered: June 2005
Junior Member
the alternate result:

SQL> conn lynxtest@civlynx
Enter password: ******
Connected.
SQL> DROP DATABASE LINK TEXTLINK;
DROP DATABASE LINK TEXTLINK
*
ERROR at line 1:
ORA-02024: database link not found


SQL> DROP DATABASE LINK TESTLINK;

Database link dropped.

SQL> CREATE DATABASE LINK TESTLINK
2 CONNECT TO CLIENTMANAGEMENT IDENTIFIED BY CIVICA USING CIVCLMAN;
CONNECT TO CLIENTMANAGEMENT IDENTIFIED BY CIVICA USING CIVCLMAN
*
ERROR at line 2:
ORA-02010: missing host connect string


SQL> CREATE DATABASE LINK TESTLINK
2 CONNECT TO CLIENTMANAGEMENT IDENTIFIED BY CIVICA USING 'CIVCLMAN';

Database link created.

SQL> SELECT * FROM CLIENTMANAGEMENT.ADDRESS@TESTLYNX;
SELECT * FROM CLIENTMANAGEMENT.ADDRESS@TESTLYNX
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
Re: Database Links [message #492661 is a reply to message #492613] Tue, 01 February 2011 11:36 Go to previous message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
Can you?:

select * from dual@testlink;

What is the value of your global_names parameter? If it is true, can you set it to false?:

SCOTT@orcl_11gR2> alter system set global_names = false;

System altered.

SCOTT@orcl_11gR2> select value from v$parameter where name = 'global_names';

VALUE
--------------------------------------------------------------------------------
FALSE

1 row selected.

SCOTT@orcl_11gR2>

Previous Topic: Contents of redo log files
Next Topic: Db Migration from 9i to 10g
Goto Forum:
  


Current Time: Wed Apr 23 11:39:00 CDT 2014

Total time taken to generate the page: 0.10286 seconds