Limitations of Database links

[divroro12]

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.

[PAVB]

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

[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?

[divroro12]

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

[PAVB]

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?

[divroro12]

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

[PAVB]

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.

[divroro12]

Thanks PavB, I'll explore the various options

[divroro12]

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

[PAVB]

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.