Limitations of Database links
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Limitations of Database links

Hybrid View

  1. #1
    Join Date
    Nov 2006
    Posts
    158

    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.
    Regards,

    divroro12

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2006
    Posts
    158
    Also, doesn't Streams use DB links in the same way?
    Regards,

    divroro12

  4. #4
    Join Date
    Nov 2006
    Posts
    158
    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?
    Regards,

    divroro12

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by divroro12 View Post
    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?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Nov 2006
    Posts
    158
    yes, Each laptop has Oracle 10.2.0.4 running on it, but the enterprise remote server runs Oracle 11.2.0.1
    Regards,

    divroro12

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Nov 2006
    Posts
    158
    Thanks PavB, I'll explore the various options
    Regards,

    divroro12

  9. #9
    Join Date
    Nov 2006
    Posts
    158
    PavB, what about materialized views or Transportable tablespaces?
    Any thoughts on those?
    Regards,

    divroro12

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by divroro12 View Post
    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.
    Last edited by PAVB; 01-26-2012 at 03:18 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width