Is there any documentation for creating a database link between 11g and 10g database

I have a client who upgraded their windows 2003 server to Oracle 11g. They previously had Oracle 9.2.0.4. When they had 9.2.0.4 they created a database link from 9.2.0.4 to a 10g database running on Solaris 9. Except for an occasional  SQL exception in TEMS_LOAD: ORA-02049: timeout: distributed transaction waiting for lock error there were no problems. After upgrading from 9.2.0.4 to 11g  they created a database link from 11g to 10g the same exact way they did it for the 9.2.0.4 to 10g database. However, they have been unable to establish the database link. Can someone tell me what kind of questions I can ask to help them troubleshoot this issue and any relevant Oracle documentation

 
sikyalaSenior Database AdministratorAsked:
Who is Participating?
Advertise Here
 
sdstuberConnect With a Mentor Commented:
from the server where you create the link

go to an OS prompt  and try this...  (all one line)


tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=000.000.00.128)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=url)))    

if that doesn't return or has a very long time then try...


ping 000.000.00.128

see where the timing lag is coming from.
0
 
sdstuberConnect With a Mentor Commented:
11g and 10g can definitely talk to each other across db links.  I do it both ways.

what is the error you're getting?  and, is the error at link creation time or when you try to query something across the link?
0
 
sonicefuConnect With a Mentor Commented:
Are correctly doing the following ?

1. db link command is correct
2. An entry for the 2-nd database (10g) has to be in the tnsnames.ora on your 11g server.

3. can you tnsping to 10g database ?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Go Premium
 
sonicefuCommented:
0. you are working at 11g database

1. tnsnames.ora has an entry like this for 10g database at your 11g database server

ora10g =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db10gserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db10g)
    )
  )

-->> tnsping ora10g                  at command prompt, is the result successful ?

2. db link command is correct, following is the syntax

CREATE [PUBLIC] DATABASE LINK dblink
[CONNECT TO user IDENTIFIED BY password]
[USING connect_string]

for example:

create public database link db10g
connect to db10gUserName identified by password
using ora10g;

--->> test this dblink              select * from tblName@db10g;
0
 
sdstuberCommented:
sonicefu,

where are you getting these suggestions from?
I agree with everything you've said, but only if the error is in resolving the names between the 10g and 11g databases or for 2, if  the error is in syntax of creating the link.

but, the asker hasn't told us that.  Is there a related question you're pulling extra info from?  or are you just guessing?  if the former,  which?  if the latter, that's fine,  I'm just curious.
0
 
sonicefuCommented:
@sdstuber

How are you boss ?

following are the answers of your questions
 
Is there a related question you're pulling extra info from?   10%

or

are you just guessing?  90%   -->>> mostly these are the things which create problems in db links, i think you'll agree with me.
0
 
sikyalaSenior Database AdministratorAuthor Commented:
There is an error message. My client has said he will provide it when he calls me at 11 AM.
0
 
sonicefuConnect With a Mentor Commented:
<<Is there any documentation for creating a database link between 11g and 10g database>>

Following links will provide the answers for your question

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_admin002.htm
http://www.psoug.org/reference/db_link.html
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Here is what he did and the error he is getting:

create database link TEMSDB.LOCAL
  connect to TEMSLOAD identified by ********
  using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=000.000.00.128)(PORT=1521))
                      (CONNECT_DATA=(SERVICE_NAME=url)))';

 

We also have a synonym defined for the BIB_HOLDINGS_COMPARE table&

 

create or replace synonym TEMSDATATABLE
  for TEMSLOAD.BIB_HOLDINGS_COMPARE@TEMSDB.LOCAL;



When I connect to our database as the owner of the two objects above, and try this query&

 

select count(1)
from temsdatatable

 

I get the following error:

 

ORA-12170: TNS:Connect timeout occurred
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Just to add they also changed operating systems. They were running 9.2.0.4 on Sun V880/Solaris. Now they are running 11g on Windows 2003 server
0
 
sonicefuConnect With a Mentor Commented:
ping 000.000.00.128
0
 
sdstuberCommented:
also, what are your sqlnet.ora settings for timeouts?

0
 
sikyalaSenior Database AdministratorAuthor Commented:
He tried this is the result:
tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=000.000.00.128)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=url)))    

The first took a very long time so I aborted.  

The second reported timeouts on all 4 packet requests.

ping ip address
0
 
sonicefuCommented:
this is the problem
0
 
sikyalaSenior Database AdministratorAuthor Commented:
from what he said it appears that he can login but when he tries to query he gets the timeout error
0
 
sonicefuCommented:
They were running 9.2.0.4 on Sun V880/Solaris.  i think ip address of this server was 192.168.58.128

Now they are running 11g on Windows 2003 server --> ask them for the ip address of this server and replace it in the following

create database link TEMSDB.LOCAL
  connect to TEMSLOAD identified by ********
  using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=000.000.00.128)(PORT=1521))
                      (CONNECT_DATA=(SERVICE_NAME=url)))';

--> actually this is the network problem
0
 
sdstuberConnect With a Mentor Commented:
yes a login would be to the local database.  The query would try to go to the remote database and since you can't ping the remote server, you can't establish a sqlnet connection across that network either.
0
 
sonicefuConnect With a Mentor Commented:
if the output of
ping ip address

is following
The second reported timeouts on all 4 packet requests.

then its impossible to connect using dblink
0
 
sdstuberCommented:
yep!
0
 
sikyalaSenior Database AdministratorAuthor Commented:
000.000.00.128 is 10g server they are trying to connect from 11g to 10g
0
 
sdstuberCommented:
ok,  but if you can't reach that server (ping failures tell you that), then you can't create a db link to it either.
0
 
sonicefuConnect With a Mentor Commented:
if you are unable to
ping 000.000.00.128

or

tracert 000.000.00.128

then you cannot proceed

--> ask your network admin to resolve this issue
0
 
sdstuberCommented:
or, more accurately,  you can create the link  (as you've already seen)

you just can't use it (as you've already seen)


either that's not really the ip address or you've got a horrible network lag between your 11g server and your 10g server.

based on the ip address itself (000.*.*.*)  , that's probably going across a vpn or some other small sub-network.   Is the vpn down ?
0
 
sonicefuConnect With a Mentor Commented:
Just to get familiar with dblink, read the following link

http://www.oraclefaq.net/2007/06/26/how-to-create-database-links/
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Thank you for the document.
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Hi all,

Thank you all so much for your input it was extremely valuable and instrumental in helping me solve my clients problem. The issue was network related. Although I don't have the details they were able to resolve the issue with not being able to ping the 10g database server and then connect successfully and query via the database link. Thank You all!
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Not only was my question answered (Can someone tell me what kind of questions I can ask to help them troubleshoot this issue and any relevant Oracle documentation?) but a solution was provided. My client contacted the network administrators and they identified and fixed the problem. Thank you all!
0
 
sikyalaSenior Database AdministratorAuthor Commented:
I found one more modus_operandi: ID 21814129 Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.