| Limitations of Database links [message #540810] |
Wed, 25 January 2012 18:37  |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
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.
- divroro12 -
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Limitations of Database links [message #540816 is a reply to message #540815] |
Wed, 25 January 2012 19:24  |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
From my initial post, these are my concerns/questions
1. Is the database link the best option for such a configuration?
2. why PUBLIC database links are considered not to be very secure?
The public database link resides on the remote database server, not on the laptops, & is being used by the field reps for their daily DB synch between remote & local (laptop)
|
|
|
|
|
|
| Re: Limitations of Database links [message #540818 is a reply to message #540817] |
Wed, 25 January 2012 19:41 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
I think you're out of context here. My issue isn't connectivity between the remote & local. My concerns are clearly spelled out in my 2 questions in the initial posting. I don't think deviating from the original post (which is what you're trying to do) will help in my current assignment. It's rather steering others who could have helped away from the issue at stake here.
If you don't have answers to my questions, I'll appreciate it if you would let others respond.
We're actually wasting valuable time here.
Thanks for your responses & questions, but they're not helping...
|
|
|
|
| Re: Limitations of Database links [message #540827 is a reply to message #540818] |
Wed, 25 January 2012 23:14 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I think BlackSwan answered your questions:
Quote:>1. Is the database link the best option for such a configuration?
If DBLINK is not used, what replaces it?
Indeed, I fail to see another (practical) way. Do you see another one?
There is materialized view but it uses a db link in the same way; you didn't say how you use the db link.
Quote:>2. why PUBLIC database links are considered not to be very secure?
anyone logged into the DB can utilize any object owned by PUBLIC
This is indeed the reason.
Regards
Michel
[Updated on: Wed, 25 January 2012 23:15] Report message to a moderator |
|
|
|
| Re: Limitations of Database links [message #540834 is a reply to message #540818] |
Thu, 26 January 2012 02:21 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
divroro12 wrote on Thu, 26 January 2012 02:41My issue isn't connectivity between the remote & local. My concerns are clearly spelled out in my 2 questions in the initial posting.
They are NOT clear. You never specified IN WHICH DATABASE the database link resides. In the one on the laptop or the central database?
Then you later specified it is in the central database, but to know what the security implications are in that scenario one would have to know how the connection to initiate the transfer happens, which BlackSwan asked.
But if you don't want to clarify, it's no skin of MY nose.
[Updated on: Thu, 26 January 2012 02:27] Report message to a moderator |
|
|
|
| Re: Limitations of Database links [message #540923 is a reply to message #540834] |
Thu, 26 January 2012 10:09 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
Michel, I don't think Black Swan any of my questions.
If DB Links aren't used, I was considering Golden Gate or transportable tablespaces, which ever will be a cheaper but more performant alternative to using Db links.
The db links are used thus; the same schema exists between the remote & local DBs. The link resides on the remote server & is called by PL/SQL procedures from the laptops (local). The link is embedded in the SQL within the procedures like "select * from tab@<db_link);
Hope that's clear ThomasG?
|
|
|
|
| Re: Limitations of Database links [message #540940 is a reply to message #540923] |
Thu, 26 January 2012 11:17 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Quote:The link resides on the remote server & is called by PL/SQL procedures from the laptops (local)
So you have hundreds of database links set up on the central server, each of them pointing to a different laptop database?
In that case, since they are public, every laptop user can read all data from the other laptop users once they are connected to the central database, even when they have not yet uploaded it.
Also, how does the central database decide which link to use? If that is done with some sort of dynamic SQL then that might be a reason for the bad performance right there.
The approach to have the link set up in the laptop database, connection to the central database (perhaps filling tables in a dedicated "import" schema) would be a better approach both security and performance wise since the data then pushed then, not pulled.
[Updated on: Thu, 26 January 2012 11:21] Report message to a moderator |
|
|
|
| Re: Limitations of Database links [message #540942 is a reply to message #540923] |
Thu, 26 January 2012 11:39 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
ThmasG is certainly correct to suggest that a better design would be to create the database links in the laptop databases, rather than the central database.
Having done that, you might want to consider replacing the home-made code such as "select * from tab@<db_link)" with replication, based on either Advanced Replication or Streams. Using Adv Rep, if you have standard edition licences you can use Materialized View replication, which is very easy and reliable. Enterprise Edition licenses would permit Multi-Master Replication, which you probably don't need. Streams gives similar functionality, but would also require Ent Edition licences.
Any of these will give you much better fault tolerance and error handling (eg, guarantee that each row is transmitted, successfully, exactly once) than anything home-made.
The alternative of hundreds of Golden Gate licences? The Oracle sales droids will love you.
|
|
|
|
| Re: Limitations of Database links [message #540944 is a reply to message #540942] |
Thu, 26 January 2012 11:50 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
Actually, there's just a single dblink at the server side that's called by the field laptops.
All processing b etween laptops & server uses this single dblink, NOT separate links.
|
|
|
|
|
|
| Re: Limitations of Database links [message #540949 is a reply to message #540944] |
Thu, 26 January 2012 12:49 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
Hi John,
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?
|
|
|
|
|
|
|
|
|
|
| Re: Limitations of Database links [message #540956 is a reply to message #540950] |
Thu, 26 January 2012 13:52 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:And Streams will be obsolete since the next version. Is there a reference for this? If not, I don't believe it. People said that Adv Rep was dead from release 9, but it is still going strong. I suspect that all the suggestions that Golden Gate is the future are because GG is separately licensed, not bundled like Streams and Adv Rep.
|
|
|
|
|
|
|
|
| Re: Limitations of Database links [message #540968 is a reply to message #540944] |
Thu, 26 January 2012 16:37 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
divroro12 wrote on Thu, 26 January 2012 18:50Actually, there's just a single dblink at the server side that's called by the field laptops.
All processing b etween laptops & server uses this single dblink, NOT separate links.
That's not possible. A database link always points to a specific other instance. Unless either the IP configuration, sqlnet configuration or the database link itself is manipulated on the fly, but that would then make it impossible for two laptops to run updates concurrently.
And how would Oracle know WHICH laptop to poll when the select is run on the database link?
But, coming back to the original question of "public or not public". That seems to be of a lesser concern as the question "how does the laptop actually connect to execute the update, and how does the central server know where to pull the data from"
As such I would put the public or no public question on the back burner and figure that out first.
[Updated on: Thu, 26 January 2012 16:42] Report message to a moderator |
|
|
|
| Re: Limitations of Database links [message #540971 is a reply to message #540968] |
Thu, 26 January 2012 16:42 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
Well ThomasG, I didn't build this system. I've just been brought in to assess it & provide alternative solutions/recommendations.
What I see here is a single DB link on the server side which is being called concurrenlty by hundreds of standalone laptops for data synch (uploads/downloads).
Is there anything I'm not looking at rightly with this configuration? Your suggestions will definitely be of help
|
|
|
|
|
|
|
|
| Re: Limitations of Database links [message #540974 is a reply to message #540973] |
Thu, 26 January 2012 16:55 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
Here is the dblink definition
CREATE PUBLIC DATABASE LINK <DBLINK_NAME>
CONNECT TO <SCHEMA_NAME>
IDENTIFIED BY <PWD>
USING '(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = <servername.company.com)(PORT = 1521))
(CONNECT_DATA =(SERVICE_NAME = <DB_NAME.company.com)))';
|
|
|
|
| Re: Limitations of Database links [message #541037 is a reply to message #540974] |
Fri, 27 January 2012 04:00 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Ah, that makes sense.
OK, security-wise:
Once someone has acquired access to the database on the laptop they also have the login possibility to the central database the link points to.
For that it makes not much difference whether the DBLINK is public or not. When they have physical access to the laptop they can reset all OS passwords one way or another, and so get DBA access as the Oracle user. Below Oracle 10 they can even see the clear text password, 10 and up they can export the database link to use it in some other database. Recreating Database Link
Incidentally, at the moment there is one very nasty flaw that could be used to bring all the databases down with one manipulated database in the pool of databases that connect.
What I personally would never do is expose the listener on servername.company.com directly to the internet, but only via a VPN or SSH tunnel with extra required connection credentials. That way the person that "found" such a laptop can't connect to the central server at all, since they would have to activate the tunnel first which they can't.
That "only" leaves the possibility of one of the field reps turning on you. They have a login and physical access to the database and they know how to log into the tunnel.
|
|
|
|
| Re: Limitations of Database links [message #541506 is a reply to message #541037] |
Tue, 31 January 2012 15:22 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
|
Hi guys, 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?
|
|
|
|
| Re: Limitations of Database links [message #541508 is a reply to message #541506] |
Tue, 31 January 2012 15:33 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
divroro12 wrote on Tue, 31 January 2012 16:22Hi guys, 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?
From following this thread, it is my understanding that the link is on the client. Therefore the are no db_links necessary on the host. so what multiple links are you talking about?
You can have hundreds of links on your client database. you are only using one link per action. If every client pc called the link something different or the same, it would make no difference.
Or, I just do not understand the question.
|
|
|
|
| Re: Limitations of Database links [message #541509 is a reply to message #541508] |
Tue, 31 January 2012 15:48 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
You do understand the question alright. My concern is if over 800 clients are calling the same link at the same time, even though from different clients (but same <dblink_name>), wouldn't there be some kind of contention?
Alternatively, is grouping the 800 clients, into lets say 10 groups and using different dblinks going to help if there's any contention by using the previous approach of a single link?
|
|
|
|
|
|
| Re: Limitations of Database links [message #541518 is a reply to message #541510] |
Tue, 31 January 2012 20:46 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
I'm looking at the single DBLink as a single logical route throughwhich everyone has to commute into the remote database server, so when I talk of contention here what I'm trying to imply is contention for route space (just like a traffic jam on the freeway)
|
|
|
|
|
|
| Re: Limitations of Database links [message #541523 is a reply to message #541518] |
Tue, 31 January 2012 21:15 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
There's a single destination DB on the server side, even though laptops are independent, everyone goes to this final destination via the same logic route, which in this case is the single dblink.
This link is configured exactly the same on each and every laptop.
|
|
|
|
|
|
| Re: Limitations of Database links [message #541556 is a reply to message #541524] |
Wed, 01 February 2012 02:37 |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
|
Before each individual dblink connection from a client becomes a sesion within the remote Oracle database, they all have to pass through the same route/port, that's where I'm concerned, not when they're already within the db, now being treated as independent db sessions.
|
|
|
|
| Re: Limitations of Database links [message #541563 is a reply to message #541556] |
Wed, 01 February 2012 03:22 |
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you have all 800 connecting simultaneously you are far more likely to get contention issues in the DB before you get any on the network.
You worrying about the wrong thing.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
