Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBLINKs in critical production system

Re: DBLINKs in critical production system

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Tue, 01 May 2007 20:06:02 +0800
Message-Id: <200705011206.l41C68Yv006494@smtp17.singnet.com.sg>

A DBLink is required for

  1. Accessing CURRENT data from a remote database.
  2. Refreshing Materialized Views (scheduled or on demand) which fetch a subset or a joined set of data from a remote database[MVs were also known as "Snapshots"]
  3. The inherent transfer mechanism in Advanced Replication.

The argument that DBLink requirement is obviated by MVs or Replication is not strong
because MVs and Replication, themselves, need DBLinks. What is important is what data is opened through the DBLink and how it is accessed (what queries are run and when).

  1. If you create a DBLink connecting to the base schema (the schema actually owning the tables being referenced) then that is a big NO NO (read "Security Hole"). The DBLink should be connecting to a "shadow" account which has only limited (ie "SELECT" only) privileges on only the subset of tables that are actually required to be accessed. See http://hemantoracledba.blogspot.com/2007/02/creating-database-links.html
  2. That DBLinks are a performance issue is like saying "User Queries are a Performance Issue". You, as the DBA, must ask first "Why do you need the DBLink ? What queries will be running across the DBLink ? How frequently will the queries be running ?" You must also turn to the Application Manager owning the Database being accessed if he approves of those queries being made against his database -- queries coming from another database / another application.

DBLinks must obtain approval from the Application team on both sides , not just the DBA alone,
for two reasons

  1. Knowing *which* data is being accessed (it should open only a specific list of tables/views to access)
  2. Understanding the performance impact of those queries being run -- on _both_ databases, particularly where the DBLink is being used to run distributed queries that join tables across databases. Ideally, DBLinks should not be exposed to end-user adhoc queries but only through application controls / scheduled jobs.

At 10:43 PM Monday, Thotangare, Ajay \(GTI\) wrote:

>Hi Group,
>
>I have a question about dblink. I always hear that
>
>- dblinks are not good in production system.
>
>- dblink , ohh!! not in critical production system
>
>- dblink are not safe
>
>----------

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com

"First they ignore you, then they laugh at you, then they fight you, then you win" !"
Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 01 2007 - 07:06:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US