Re: Data movement logic in data guard

Thanks for the guidance here.
 I will try to see how I can test the scenario to ensure the replication order will be exactly the same in DR as in primary even during catching up large lag.

Two questions with related to same

1. We don't want to keep on hitting the data read queries in those transaction tables if the lag is very high and should wait for the lag to catch up. For that reason is it okay to refer to the v$standby_log. And keep on looping with a sleep something as below?

LOOP SELECT SYSDATE, ( SELECT max(last_time) FROM gv$standby_log)

    INTO current_dt,

         last_replication_dt

    FROM DUAL;    WHILE (current_dt > last_replication_dt)

   LOOP        DBMS_LOCK.SLEEP(30);        SELECT max(last_time)

       INTO last_replication_dt

       FROM gv$standby_log;

 END LOOP;  /

2) For insert only TABLES it's easy to take MAX(date_created) and store it in some reference tables and then use that to do the incremental data load to target . But how to achieve this data movement/replication, if UPDATE/DELETE also happens on the base transaction table? Is there any recommended way for that?

On Sat, Dec 19, 2020 at 1:04 AM Clay Jackson (cjackson) < Clay.Jackson_at_quest.com> wrote:

> While I admit I haven’t specifically tested this case, which I would
> recommend you do, I think you’re “overthinking” this.
>
>
>
> The whole point behind DataGuard (or, at least one of the major “use
> cases”) is to create a “consistent” COPY of your primary database, so that
> in the event of a failure (loss of access), users can be moved to the
> secondary database without loss of data (or consistency). The mechanism
> by which this happens is through physical copying of the redo log(s), or
> records within the logs from the primary to the secondary, and then
> “applying” those logs (or records) to the secondary, IN THE SAME order as
> they were “applied” on the primary. So, while the actual writing of the
> transactions to the database on the secondary will ALWAYS happen at a point
> in time AFTER transaction was written to the primary, I don’t think the
> scenario you outlined (records being written “out of order” is possible.
>
>
>
>
>
> Clay Jackson
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Lok P
> *Sent:* Friday, December 18, 2020 11:06 AM
> *To:* Oracle L <oracle-l_at_freelists.org>
> *Subject:* Re: Data movement logic in data guard
>
>
>
> *CAUTION:* This email originated from outside of the organization. Do not
> follow guidance, click links, or open attachments unless you recognize the
> sender and know the content is safe.
>
>
>
> Moving data from one to other system looks to be very common but I have
> never came across building such logic manually in code. And I don't have
>
> much idea about how the archive log apply happens at standby. But it seems
> like, to maintain constraints it has to be in exactly in same order as the
> data load happens in primary. Else things will break.
>
>
>
> Can somebody guide me here, if the logic which we are going to rely to
> decide reference date/time in our case for data movement will never fail?
>
>
>
> On Fri, 18 Dec 2020, 4:27 pm Lok P, <loknath.73_at_gmail.com> wrote:
>
> Actually in golden gate setup with multiple parallel replication threads ,
> we have encountered scenarios where two transactions generated from source
> can reach target in different order thus causing the data pickup from
> target to miss some rows.
>
>
>
>
>
>
>
> On Fri, Dec 18, 2020 at 9:30 AM Lok P <loknath.73_at_gmail.com> wrote:
>
> Its version 11.2.0.4 of oracle exadata. We have a requirement in which we
> need to move the data to another system and for that we want to utilize the
> DR database(which is a physical standby with data guard configured) rather
> than Primary, as we want to not to affect the key applications which are
> running on primary. And we are almost saturating the DB resources on the
> primary during peak hours.
>
> For copying/moving data without miss in each ~15minutes interval
> frequency, we are relying on "date_created" column as reference column of
> the transaction table , so in case we have some additional lag from primary
> to DR, is it possible that a record created on primary and DR as below
> sequence, such that the row-3 created on DR before Row-2? In that case we
> may miss that row when we take MAX(date_created) from our transaction table
> to move the data.
>
> In such a scenario , how should we make our logic full proof to pick the
> max(date_created) on source so that we won't miss any data? Or should we
> some way utilize the column last_time of v$standby_log to make our logic
> full proof?
>
> Aso i am wondering if by any way we can handle UPDATE/DELETE of the
> records in source?
>
> On Primary:-
>
> Row-1 created at 18th DEC 10:00AM with date_created as 18th DEC 10:00AM
>
> Row-2 created at 18th DEC 10:05 AM with date_created as 18th DEC 10:05 AM
>
> Row-3 created at 18th DEC 10:06 AM with date_created as 18th DEC 10:06 AM
>
> On DR when we have lag of say ~5minutes:-
>
> Row-1 created at 18th DEC 10:05AM with date_created as 18th DEC 10:00AM
>
> Row-3 created at 18th DEC 10:11:01 AM with date_created as 18th DEC 10:06AM
>
> Row-2 created at 18th DEC 10:11:02 AM with date_created as 18th DEC 10:05AM
>
>
>
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l