Re: DB Link design question

In my earlier work place I worked on same setup. Your D2 was our Datawarehouse instance with EBS as D1.

We used HEAP table which was getting refreshed whenever EBS to DW batch job stream run. Due to volume of data we were running 5 times per day.

Very limited realtime access to EBS instance via given due to avoid any impact to EBS.

In my opinion you are going on correct direction.

Karth

> On Mar 20, 2015, at 5:50 AM, Kumar Madduri <ksmadduri_at_gmail.com> wrote:
>
> 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