Home » RDBMS Server » Server Administration » Limitations of Database links (Oracle 10.2.0.4/Oracle 11.2.0.1)
Limitations of Database links [message #540810] Wed, 25 January 2012 18:37 Go to next message
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 #540811 is a reply to message #540810] Wed, 25 January 2012 18:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what happens when a laptop goes missing & falls into the hands of a competitor?
Re: Limitations of Database links [message #540812 is a reply to message #540811] Wed, 25 January 2012 19:03 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
well i guess that's no skin off my nose...
Re: Limitations of Database links [message #540813 is a reply to message #540812] Wed, 25 January 2012 19:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>well i guess that's no skin off my nose...
They continue to obtain your corporate database updates daily!
Re: Limitations of Database links [message #540814 is a reply to message #540813] Wed, 25 January 2012 19:10 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
I know about that, but that's not my concern right now.
I'll rather we focus on the questions I posted.
Re: Limitations of Database links [message #540815 is a reply to message #540814] Wed, 25 January 2012 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
On a single user laptop, there is no difference in security between private & PUBLIC database link.
Re: Limitations of Database links [message #540816 is a reply to message #540815] Wed, 25 January 2012 19:24 Go to previous messageGo to next message
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 #540817 is a reply to message #540816] Wed, 25 January 2012 19:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1. Is the database link the best option for such a configuration?
If DBLINK is not used, what replaces it?
>2. why PUBLIC database links are considered not to be very secure?
anyone logged into the DB can utilize any object owned by PUBLIC

From the perspective of a laptop, is the connection with the central server initiated by the laptop or the central server?
If connection is initiated by the central server, how does central server know how to address/find each remote laptop
& how does central server know when the remote laptop is available?
Re: Limitations of Database links [message #540818 is a reply to message #540817] Wed, 25 January 2012 19:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
divroro12 wrote on Thu, 26 January 2012 02:41
My 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3211
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 #540948 is a reply to message #540944] Thu, 26 January 2012 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
divroro12 wrote on Thu, 26 January 2012 09:50
Actually, there's just a single dblink at the server side that's called by the field laptops.


What does the DBLINK on the server point to?

if no DBLINK exists on the field laptop, then how does the laptop
initiate interaction with the DB server?
Re: Limitations of Database links [message #540949 is a reply to message #540944] Thu, 26 January 2012 12:49 Go to previous messageGo to next message
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 #540950 is a reply to message #540949] Thu, 26 January 2012 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Streams is based on database link.
And Streams will be obsolete since the next version.
Why not just materializer views in this case?

Regards
Michel
Re: Limitations of Database links [message #540951 is a reply to message #540950] Thu, 26 January 2012 13:00 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Ok Michel, i'll explore the option of either materialized views or transportable tablespaces: Any thoughts on that?
Re: Limitations of Database links [message #540953 is a reply to message #540951] Thu, 26 January 2012 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want multi-site updates you can't use transportable tablespaces.

Regards
Michel
Re: Limitations of Database links [message #540956 is a reply to message #540950] Thu, 26 January 2012 13:52 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #540957 is a reply to message #540953] Thu, 26 January 2012 13:52 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
OK, thanks
Re: Limitations of Database links [message #540958 is a reply to message #540956] Thu, 26 January 2012 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a reference for this?


I don't know if there is some reference on it but I get it from some Oracle employees.
In addition, there is no more Streams course on oracle education (at least in my country).

Regards
Michel
Re: Limitations of Database links [message #540968 is a reply to message #540944] Thu, 26 January 2012 16:37 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
divroro12 wrote on Thu, 26 January 2012 18:50
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.


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 Go to previous messageGo to next message
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 #540972 is a reply to message #540971] Thu, 26 January 2012 16:44 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
FYI, all laptops users have use the same schema for this synch (hence same username/password)
Re: Limitations of Database links [message #540973 is a reply to message #540972] Thu, 26 January 2012 16:47 Go to previous messageGo to next message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
I'm very sorry for misinforming you guys, the PUBLIC DBLink is on each of the laptops, NOT on the server DB
Re: Limitations of Database links [message #540974 is a reply to message #540973] Thu, 26 January 2012 16:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3211
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
divroro12 wrote on Tue, 31 January 2012 16:22
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?


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 Go to previous messageGo to next message
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 #541510 is a reply to message #541509] Tue, 31 January 2012 17:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>wouldn't there be some kind of contention?
contention for which resource?
Re: Limitations of Database links [message #541518 is a reply to message #541510] Tue, 31 January 2012 20:46 Go to previous messageGo to next message
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 #541522 is a reply to message #541518] Tue, 31 January 2012 21:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
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)

I thought that every agent had their own laptop that operate independent of each other.
By your illogic every one should have a congestion problem running Excel because everyone use the same executable name.
Re: Limitations of Database links [message #541523 is a reply to message #541518] Tue, 31 January 2012 21:15 Go to previous messageGo to next message
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 #541524 is a reply to message #541523] Tue, 31 January 2012 21:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>There's a single destination DB on the server side,
How is this different from having a 800 person call center in one building?

The DB Server must have sufficient resources to handle the maximum number of concurrent sessions;
regardless from where the sessions originate.
Oracle RDBMS does not really know or care where any particular client is located or exactly how the connection originated.
Re: Limitations of Database links [message #541556 is a reply to message #541524] Wed, 01 February 2012 02:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Limitations of Database links [message #541564 is a reply to message #541563] Wed, 01 February 2012 03:24 Go to previous message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
OK cokkiemonster, thanks
Previous Topic: ora 002236
Next Topic: Hybrid Columnar Compression: useful bug
Goto Forum:
  


Current Time: Thu Mar 28 12:34:05 CDT 2024