Home » RDBMS Server » Networking and Gateways » database link from 11g to 10g (Oracle 11g)
database link from 11g to 10g [message #559628] Thu, 05 July 2012 01:37 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
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 #559633 is a reply to message #559629] Thu, 05 July 2012 02:25 Go to previous messageGo to next message
moulshree
Messages: 13
Registered: July 2012
Junior Member
11g database: 10.1.1.10
10g database: 10.1.1.12

TNSNAMES.ORA

XYZ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XYZ)
)
)

LISTENER.ORA

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.10)(PORT = 1521))
)
)

what needs to be modified here?
Re: database link from 11g to 10g [message #559634 is a reply to message #559633] Thu, 05 July 2012 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you posted not real information we can't what you have to modify in the real files.

Regards
Michel
Re: database link from 11g to 10g [message #559635 is a reply to message #559634] Thu, 05 July 2012 02:52 Go to previous messageGo to next message
moulshree
Messages: 13
Registered: July 2012
Junior Member
I just changed the IP and service name as I am not allowed to post the real information. What you are not getting from this?
Re: database link from 11g to 10g [message #559636 is a reply to message #559633] Thu, 05 July 2012 02:59 Go to previous messageGo to next message
John Watson
Messages: 4081
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 #559638 is a reply to message #559636] Thu, 05 July 2012 03:42 Go to previous messageGo to next message
moulshree
Messages: 13
Registered: July 2012
Junior Member
It's done. thanks..
Re: database link from 11g to 10g [message #559639 is a reply to message #559638] Thu, 05 July 2012 03:51 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Can you explain the solution? (It is customary to do that here after asking a question, in order to help others)
Re: database link from 11g to 10g [message #559998 is a reply to message #559639] Mon, 09 July 2012 02:14 Go to previous message
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)
)
)'
Previous Topic: ORA-03114 and ORA-03135
Next Topic: Materialized view data replication
Goto Forum:
  


Current Time: Thu Apr 17 12:04:53 CDT 2014

Total time taken to generate the page: 0.12517 seconds