DB Link design question

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Fri, 20 Mar 2015 02:50:45 -0700
Message-ID: <CAHDOOG7=w_oZ_iH-FieREv-QdaL1TYb1EoSXk9X=CAATno2Y8A_at_mail.gmail.com>



Hello
We have two databases (D1, D2). Apex applications are developed extensively in D2 database. D1 is an ebusiness database. D2 APEX applications need data from D1 ebusiness applications. Since each apex application is distinct, the way the developers have done is that they have a new parsing schema (and hence a new database schema) whenever they build a new app and they build a new app every few months. Since they require data from D1, they keep asking for db links from D2 to D1. On D1 you have a placeholder schema which basically has grants from apps users to this placeholder (idea is that no direct access to apps and apps password is not exposed).
Problem is we end up have 5 or 6 database links fo far from D2 to D1 (DB link definition would be same except for owning schema name on D2). Apart from having to maintain all these db links across refreshes I feel this is inefficient for the following reasons as well 1. When D1 goes down for scheduled or unscheduled maintenance/outage, D2 apex applications will also go down.
2. IF D2 Apex application has performance issues or they try to do some DML through the db link on D1 , then it would propagate to D1. In other words we are creating a loop that can create availability and scalability issues.
One of the proposals I can think of is

create one central schema in D2
Create db link from that schema to D1 (placeholder user). Create MVs in this central schema on D2. Refresh this MVs on a regular basis.
Grant privilege on these MVs to the custom schema that APEX applications uses

We cant do Golden Gate but probably can do streams. This way when D1 goes down for any reason D2 would not go down. They may have stale data but better than a full outage I think

These approaches (MV or Streams) are better than creating DB links all over. Don't want to create a public db link from D2 to D1 for security.

Are there any other things that I can consider which I might have missed. One thing I might thing that developers would have to ask is they want changes from D2 (the ones that make from D2 APEX apps to be propagated to D1 - bi directional replication which my proposal above cannot do).

Thank you
Kumar

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 20 2015 - 10:50:45 CET

Original text of this message