| ORA-12514 error on database link [message #490809] |
Thu, 27 January 2011 09:45  |
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 #490812 is a reply to message #490810] |
Thu, 27 January 2011 09:51  |
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 |
Roachcoach
Messages: 1576
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 |
cookiemonster
Messages: 13922
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 |
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 #490819 is a reply to message #490818] |
Thu, 27 January 2011 10:04 |
Roachcoach
Messages: 1576
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 #490823 is a reply to message #490821] |
Thu, 27 January 2011 10:08 |
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 #490827 is a reply to message #490825] |
Thu, 27 January 2011 10:19 |
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 #492334 is a reply to message #490836] |
Sat, 29 January 2011 09:21 |
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 |
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 #492437 is a reply to message #492436] |
Mon, 31 January 2011 04:14 |
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 |
cookiemonster
Messages: 13922
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 |
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 |
 |
Barbara Boehmer
Messages: 9092
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 |
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 |
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 |
|
Barbara Boehmer
Messages: 9092
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
