Limitations of Database links

Hi Gurus,

I'm currently assessing the design/performance of a Distributed System in which hundreds of Field reps have local Oracle DBs (10.2.0.4) on laptops & have to update a remote database (11.2.0.1) via a PUBLIC database link. Field data (millions of records) collected daily is synched from the local to the remote DB & vise versathrough this database link. I have 2 concerns here:

1. Is the database link the best option for such a configuration? (recently field reps have been complaining about the slowness in synchronizing data between local & remote DBs).
If not, what other options are available for such processing?

2. I've read a lot about security concerns with using PUBLIC database links, but haven't seen any documents to proof they're a majority security issue. Can anyone tell me(with real life examples, if possible) why PUBLIC database links are considered not to be very secure?

Your responses will be highly appreciated.

In my experience DBLinks perform poorly; have not seen security issues related to them provided dblink account has the right privileges and password is kept in secret.

Have you considered Oracle Streams?http://www.oracle.com/technetwork/da...gr1-134658.pdf

Hi PavB,

Thanks for your response. I thought of Oracle Streams, but we're talking here of a configuration between over 800 laptops at the one end & a 2-node Oracle RAC db server. From my understanding of Streams, the configuration has to be done between each of the laptops & the server.
Isn't this going to be a very manual process?

Also, doesn't Streams use DB links in the same way?

Quote:

---

Originally Posted by divroro12

Hi PavB,

Thanks for your response. I thought of Oracle Streams, but we're talking here of a configuration between over 800 laptops at the one end & a 2-node Oracle RAC db server. From my understanding of Streams, the configuration has to be done between each of the laptops & the server.
Isn't this going to be a very manual process?

---

oh... I thought it was between two servers. Do you have a local Oracle running on each one of the 800 laptops?

yes, Each laptop has Oracle 10.2.0.4 running on it, but the enterprise remote server runs Oracle 11.2.0.1

A non dblink alternative would be the use of SQLPlus copy command. SQLPlus copy command opens two connections, one with the source and one with the target database.

I know copy command is supposed to be obsolete/deprecated but if you do "help copy" on SQLPlus 11gR2 it is still there.

Another non dblink alternative would be one relying on an expdp/ftp/impdp kind of process which can be scripted and automated.

Thanks PavB, I'll explore the various options

PavB, what about materialized views or Transportable tablespaces?
Any thoughts on those?

Quote:

---

Originally Posted by divroro12

PavB, what about materialized views or Transportable tablespaces?
Any thoughts on those?

---

Materialized Views have to be created over a dblink - in this scenario - which I think defeats the purpose - I have to agree that it looks like a very atractive and elegant solution.

In regards to Transportable Tablespaces, I think you can generate a Transportable Tablespace only on Enterprise Edition while it can be imported into any edition; plaforms with different endian format would require some extra setup synchronization like compatibility_level I think. If laptops are not running Enterprise Edition it wouldn't be possible to send data to the central database this way. Also, source and target database have to have the same character set and national character set configuration. I have the feeling this solution keeps adding potential issues to the solution.

Ok, thanks a lot for ur input.

I think I did misinform you guys. Actually, the dblink is on the laptops, not on the server, & all laptop users use the same schema for the processing b/w laptop & database server.
The link is defined on each of the laptops as follows:

CREATE PUBLIC DATABASE LINK
CONNECT TO
IDENTIFIED BY
USING '(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = (CONNECT_DATA =(SERVICE_NAME =

CREATE PUBLIC DATABASE LINK
CONNECT TO
IDENTIFIED BY
USING '(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = (CONNECT_DATA =(SERVICE_NAME =

all database links are done on the server, not on clients

Quote:

---

Originally Posted by davey23uk

all database links are done on the server, not on clients

---

True.
Having said that, I understand that in this particular case "clients" are fully functional Ora10g instances.

You're right PavB, all "Clients" in this case are actually standalone Oracle 10.2.0.4 instances.

Thanks for your input PavB

PavB, would you know if there are any concurrency limitations to using a single dblink, i.e. using a single dblink as against using multiple dblinks for network traffic distribution?

Since client databases are not connected all the time I do not think it is a good idea to have dblink definitions on the central database side but on the client database side.

Having said that, "client database" dblinks will be hitting whichever listener was setup on the central database - the closer the connection gets to the central database in terms of network the more dense the "client" traffic will be - very little you can do to avoid this but this is just how it works for every and all clients that connect to the database.

In terms of limitations, I remember there is a parameter that sets a limit on the number of remote databases a particular session can reach over dblinks - on the top of my mind the default value is 4 or something like that but I do not think it applies here where connections will be one-to-one.

SQL> show parameter open_links;

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
open_links integer 4
open_links_per_instance integer 4

That's the default value, but I know it can be increased, to what max value, I'm not sure yet.

What about the idea of having a dedicated listener just for dblink connections? I want to believe that might improve on the traffic for the default listener for the central db?

Having a dblink (private) listerner wouldn't hurt.

Don't think it will help a lot on the network traffic side of the equation - assuming listener will be attached to the same network card - but it may help having separate listener logs for troubleshooting purposes.

OK, thanks