database link from 11g to 10g [message #559628] |
Thu, 05 July 2012 01:37 |
|
moulshree
Messages: 13 Registered: July 2012
|
Junior Member |
|
|
I am trying to create a database link from the 11g database to the 10g database using:
create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;
It Returns
Database link created.
select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Please let me know what changes I need to make to tnsnames and listener at both servers.
|
|
|
Re: database link from 11g to 10g [message #559629 is a reply to message #559628] |
Thu, 05 July 2012 01:43 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
11g error message:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
*Cause: The listener was not configured with a default service and
SERVICE_NAME was missing from the CONNECT_DATA received by the listener.
*Action: Possible solutions are:
- Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service
name. Reload the listener parameter file using reload [<listener_name>].
This should be done by the LISTENER administrator.
- If using a service name, Check that the connect descriptor corresponding
to the service name in TNSNAMES.ORA has a SERVICE_NAME or SID component
in the CONNECT_DATA.
10g error message:
ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
*Cause: The SID was missing from the CONNECT_DATA.
*Action: Check that the connect descriptor corresponding to the service
name in TNSNAMES.ORA has an SID component in the CONNECT_DATA.
Regards
Michel
[Updated on: Thu, 05 July 2012 01:44] Report message to a moderator
|
|
|
|
|
|
Re: database link from 11g to 10g [message #559636 is a reply to message #559633] |
Thu, 05 July 2012 02:59 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Hello. Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
Before you change anything, do a couple of tests. From your 11g oracle home, test the TNS names resolution and prove that the connect string does actually work. Show the results. Like this:c:\users\john\home>
c:\users\john\home>
c:\users\john\home>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 05-JUL-2012 08:57:59
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
c:\app\john\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = jwdell.bplc.co.za)(PORT = 1521)) (CO
_NAME = orcl)))
OK (20 msec)
c:\users\john\home>sqlplus system/oracle@orcl
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 5 08:58:13 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Session altered.
orcl>
|
|
|
|
|
Re: database link from 11g to 10g [message #559998 is a reply to message #559639] |
Mon, 09 July 2012 02:14 |
|
moulshree
Messages: 13 Registered: July 2012
|
Junior Member |
|
|
create public database link ORCL10R2 connect to <userid> identified by <pswd> using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <destination IP>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ICAS)
)
)'
|
|
|