?
Solved

Oracle dblink problems.

Posted on 2011-04-18
32
Medium Priority
?
4,186 Views
Last Modified: 2013-12-18
I have remote database (remote_db). I need to access some table from this database from other local database. (let us assume UserA has 3 tables, tab1, tab2 and tab 3)

I also have 2 local database
1. db1 running on unix
2. db2 running on win2003

then I create database link for db1
CREATE DATABASE LINK remote_dblink CONNECT TO USERA IDENTIFIED BY UASERA_PWD
USING 'remote_db';

then I create synonym
CREATE SYNONYM TAB1 FOR USERA.TAB1@remote_dblink;
CREATE SYNONYM TAB2 FOR USERA.TAB2@remote_dblink;
CREATE SYNONYM TAB3 FOR USERA.TAB3@remote_dblink;

SELECT * FROM TAB1; --- tab2 and tab3 as well
IT WORKS FINE AS ECPECTED......

BUT WHEN I REPEAT ABOVE STEPS FOR DB2, I GOT ERROR TNS:CONNECTION TIMEOUT OCCURED....

SO WHAT IS THE PROBLEM...
I TEST TO PING REMOTEDB USING TNSPING, AND IT WORKS...

NEED YOUR HELP.
THANKS.


0
Comment
Question by:KG1973
  • 11
  • 10
  • 5
  • +3
32 Comments
 
LVL 14

Accepted Solution

by:
ajexpert earned 750 total points
ID: 35421584
Have you tried creating dblink using FULL TNS NAMES Entry?

Here is example

http://halisway.blogspot.com/2006/11/oracle-database-links-using-full-tns_29.html
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 300 total points
ID: 35422296
hi KG,
- i assume the db2 database have been around for quite sometimes and its running well. except that setting up the dblink results with the tns error.

- disable the windows firewall for a moment and test the dblink again if it works. this is to see if the problem caused by firewall. if it is then you need to set the Windows Firewall exception list to allow the port 1521 or any port that is configured with the listener > http://www.orafaq.com/wiki/ORA-12170

- add a string value USE_SHARED_SOCKET=TRUE in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.  this is to allows OS to share the listening port between multiple processes.

0
 
LVL 1

Author Comment

by:KG1973
ID: 35422785
ajexpert,
it works !!!!. But need to know why ?

OP_Zaharin, your guess about db2 and db1 is very true.... but the solution is what ajexpert said.
I already configured the firewall, I allow 1521.... but no luck.

What if we purposely block port 1521, will ajexpert's solution still work or blocked ?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35422836
- it will be blocked, because port 1521 is for database connections using Oracle Net over TCP/IP. in ajexpert using TNSNAMES solutions also specifies PORT=1521.

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35423594
To clarify:

The way Oracle networking works is the listener listens on port 1521 (or a port you pick).  The listener pick a random high port between 1021 and 65535 (from memory might be off a little) hands off the connection then goes back to listening.

There are two ways around this and they both have negatives.  The use_shared_sockect mentioned above or configuring multi-threaded server where you can configure a range.

I suggest opening all orts in the firewall and let Oracle do what it does.
0
 
LVL 2

Expert Comment

by:spyresponse
ID: 35438562
just entry in TNS and listener entry in ur local and remote sites both .....in your database.....
0
 
LVL 1

Author Comment

by:KG1973
ID: 35438669

sligthvw,
>>I suggest opening all ports in the firewall and let Oracle do what it does.
Can you elaborate more taking consideration on security issues ?
May be there is specific IP range that I should open.

spyresponse,
Can you give me example ?

For testing, I might disable both local and remote's firewall (short duration) and see how oracle works.
I also need to redo dblink again but not with full tns entry. Will update the status later.

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35440249
>>May be there is specific IP range that I should open

It's not an IP address issue. It is a network port issue.  It's how Oracle works.

The security concern for some shops is opening up that large of a port range could open the system up to potential vulnerabilities.  Many shops like to close any unused ports to limit potential threats.

Network security folks HATE Oracle because of this.

Disabling the firewalls for a test will work but will not solve the problem.

All this information used to be buried somewhere in the docs but I'm not able to find where is spells this out for you.

>>just entry in TNS and listener entry in ur local and remote sites both .....in your database.....

I believe this is trying to tell you to properly configure your tnsnames.ora and listener.ora files.  That has nothing to do with the timeout issue.  Besides, you already mentioned the tnsping works so you know they are configured properly.
0
 
LVL 1

Author Comment

by:KG1973
ID: 35445081
Slightwv,

I m bit confused. Let me refresh what i understood so far.
First i create dblink and the outcome is timeout.
2nd, i recreate dblik with full tns entry an it works.
BOTH are using same port id which is 1521 and i didnt change anything to our firewall.

Does the 2nd one considered intelligent that able to use different port id if 1521 block or unavailable?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35445138
What I read is you aren't 'recreating' the link.

Between db1 and remote_db, it works.  Between db2 and remote_db it times out.

Am I not understanding?

There really isn't an intelligent connection request.
0
 
LVL 1

Author Comment

by:KG1973
ID: 35454816
Sorry, actually between db1 and remote_db is not an issue anymore.

The main issues is between db2 and remote_db. The following is for db2 connection :

>>First i create dblink and the outcome is timeout. ---- normal dblink without full tns entry

>>2nd, i recreate dblik with full tns entry an it works  --- with full tns entry


What make confused is why creating dblink with full tns work ? Both used port 1521.
ie,

CREATE DATABASE LINK remote_dblink CONNECT TO USERA IDENTIFIED BY USERA_PWD
USING 'remote_db';

VS

CREATE DATABASE LINK remote_dblink CONNECT TO USERA IDENTIFIED BY USERA_PWD
USING
'(DESCRIPTION=(ADDRESS_LIST=(
   ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=remote_db)))'
/

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35454859
If you have the information in your tnsnames.ora file used in the first example that is used in the second example, not really possible unless you are hitting a bug.

Are you sure you are looking at the tnsnames.ora file you think you are?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35454866
- a dblink is a connect from one DB Server to another DB Server (not client to DB). tns timeout means your tns setting exist and tns resolution is partially working. or maybe it is pointing at a wrong host. check your tns entry (in tnsnames.ora file located in oracle folder) again at the db2 server. share your tnsnames.ora content on remote_db entry here if its possible.

- if you are up to some test - use sqlplus on server A to connect to the server B using the remote_db alias (c:> sqlplus username/password@remote_db), if it's connected then supposedly it should also work for the dblink. do further test on select on a table - let it idle for 5 minute then do another select to see if a timeout occur. if it does, you might want to setup trace then.  
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35455832
If your db2 is Oracle 10g or up, you can use the shorterd Easy Connect String instead of the full connection description syntax, which looks much better, and has no cons:
CREATE DATABASE LINK remote_dblink CONNECT TO USERA IDENTIFIED BY USERA_PWD
USING 'dbserver/remote_db';

Open in new window

The full reference (for 10g) can be found at http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/network001.htm#sthref193

This connection method does no longer require a TNSNames.Ora, though you can also enter the Easy Connect String into TNSNames to define a connection alias.
The only case in which you cannot use it instead of TNSNames.Ora / full connection description is if you have advanced features, like fail-over or load balancing.

However, that does not really solve or explain your issue. As said above, the TNSNames entry needs to be on the db2 server. If you issue a TNSPing from that server to remote_db, and it succeeds, but if using it in a dblink does not, it is a firewall issue (TNSPing does only test the listener, and there is no port-mapping to the random port for db connection involved). If at the same time using a connection string in the dblink works, you have an issue with the TNSNames for sure.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35457376
>>and has no cons

Didn't you point out one con, TAF?

Another one and the main reason I don't like EZConnect: without very good documentation of everywhere it is used, the DBA cannot change anything about the server.  If they do apps might break.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35457400
But with full connection strings outside of TNSNames an admin has full control? ;-) As mentioned, you can still use TNSNames, but with the simplified EZConnect. So you got the best of the two worlds, and in TNSNames you have structured data, unlike that awful 20-lines-connection descriptor thingy. Obviously the latter is something I don't like at all. (I know I can write all into a single line, too, but have you seen anybody doing that? Besides me, of course.)
0
 
LVL 1

Author Comment

by:KG1973
ID: 35457677
slightwv,
I only have 1 tnsnames.ora file.
The following entry is exactly copy and paste from it (except host = server ipaddress)

'(DESCRIPTION=(ADDRESS_LIST=(
   ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=remote_db)))'

Qlemo,
I disable the firewall already and I redo it again, it give same result.

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35458786
I'm clueless then - provided you've tested everything on the db2 itself. The next step in troubleshooting I would take is to capture associated network traffic on  both db2 and dbserver.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35459270
99.99% of the time, an Oracle timeout is caused by a firewall blocking the handoff from the listener.

The rest of the time, it's a bad/slow connection.

You can turn on tracing if you wish.  Start with the listener.

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35459282
I should also add, I've seen a timeout when the database server had multiple network cards and my config files used host names and not ip address.

Oracle likes to pick the first network card listed.  If this isn't the correct one, the connection will timeout.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35459291
- i am with slightwv on the firewall. it's one of the top in my list  that i would look into if i have problems with my oracle connections. as i suggested earlier too, set the trace on if you want to further investigate.
0
 
LVL 1

Author Comment

by:KG1973
ID: 35464758
to all,

As of now, the solution to my original question is to use full tns entry.

My db2 used 2 network card..... I will check this one and other potential cause later..
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35466410
>>My db2 used 2 network card

Use IP address in your config files and make sure the primary card is set up properly.  Otherwise Oracle will grab the first card it sees.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35468317
Does that (2nd NIC) explain the difference between using TNSnames and providing the connection descriptor directly?
0
 
LVL 1

Author Comment

by:KG1973
ID: 35472150
db2 NIC actually only have 1 ip address.They been configured sort of like a redundant NIC, if one failed, other take over.

I doubt NIC of db2 caused problem because my tns entry is pointing to remote_db.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35472179
You can doubt all you want but you have limited reasons causing a timeout.  It has to be one of them.

If it's not a firewall and not a slow/bad network, it's the NIC.

If you don't think its anything we've mentioned here, I suggest you contact Oracle Support.  I'm out of ideas.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35472191
KG1973, the timeout is likely because tnsnames cannot resolve the hostname. try to change it to IP address in the tnsnames entry HOST=ip address and give it a try again.
0
 
LVL 1

Author Comment

by:KG1973
ID: 35472637
OP_Zaharin:
I already did try both. In fact, most tnsnames are now using ip address.
0
 
LVL 1

Author Comment

by:KG1973
ID: 35604992
slightwv,

>>99.99% of the time, an Oracle timeout is caused by a firewall blocking the handoff from the listener.
>>The rest of the time, it's a bad/slow connection.
>>You can turn on tracing if you wish.  Start with the listener

Can you give me reference on how to do tracing ?
Thanks.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 450 total points
ID: 35689394
Start with:
http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/troublestng.htm

Then for your specific error (The docs actually say to start with the client...  OK then start there):

http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/troublestng.htm#CEGEDBIE

Unfortunately I cannot find a good doc link that walks you through tracing the listener but the last time I set it up, it wasn't that difficult.

The parameters are in:
http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/troublestng.htm#sthref1776

Just add them the the listener.ora then reload/bounce the listener.  A 'lsnrctl status' will tell you it tracing is on or not.
0
 
LVL 1

Author Comment

by:KG1973
ID: 35735961
Thanks for your contributions.

I am gonna close this soon. The problem actually solved but I just need to know more on the cause so that I cant prevent it from happening again. Therefore need more time to explore all suggestions.

Best regards.
0
 
LVL 1

Author Closing Comment

by:KG1973
ID: 35736040
Problem solve but what caused it happen still not sure. Anyway thanks for the contributions and your time.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup
Suggested Courses

839 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