Solved

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

Posted on 2008-06-18
29
767 Views
Last Modified: 2013-12-18
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

 
0
Comment
Question by:sikyala
  • 10
  • 10
  • 8
29 Comments
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 175 total points
ID: 21812739
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
 
LVL 13

Assisted Solution

by:sonicefu
sonicefu earned 325 total points
ID: 21813067
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 21813243
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21813305
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 21813359
@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
 

Author Comment

by:sikyala
ID: 21813476
There is an error message. My client has said he will provide it when he calls me at 11 AM.
0
 
LVL 13

Assisted Solution

by:sonicefu
sonicefu earned 325 total points
ID: 21813489
<<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
 

Author Comment

by:sikyala
ID: 21813652
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
 

Author Comment

by:sikyala
ID: 21813695
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 175 total points
ID: 21813803
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
 
LVL 13

Assisted Solution

by:sonicefu
sonicefu earned 325 total points
ID: 21813810
ping 000.000.00.128
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21813861
also, what are your sqlnet.ora settings for timeouts?

0
 

Author Comment

by:sikyala
ID: 21814082
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 21814095
this is the problem
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:sikyala
ID: 21814122
from what he said it appears that he can login but when he tries to query he gets the timeout error
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 21814129
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 175 total points
ID: 21814138
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
 
LVL 13

Assisted Solution

by:sonicefu
sonicefu earned 325 total points
ID: 21814147
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21814168
yep!
0
 

Author Comment

by:sikyala
ID: 21814187
000.000.00.128 is 10g server they are trying to connect from 11g to 10g
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21814228
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
 
LVL 13

Assisted Solution

by:sonicefu
sonicefu earned 325 total points
ID: 21814237
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21814257
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
 
LVL 13

Assisted Solution

by:sonicefu
sonicefu earned 325 total points
ID: 21814317
Just to get familiar with dblink, read the following link

http://www.oraclefaq.net/2007/06/26/how-to-create-database-links/
0
 

Author Comment

by:sikyala
ID: 21815061
Thank you for the document.
0
 

Author Comment

by:sikyala
ID: 21815511
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
 

Author Closing Comment

by:sikyala
ID: 31468323
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
 

Author Comment

by:sikyala
ID: 21847108
I found one more modus_operandi: ID 21814129 Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now