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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Lok P
Sent: Friday, December 18, 2020 11:06 AM
To: Oracle L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:loknath.73@xxxxxxxxx>> 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@xxxxxxxxx<mailto:loknath.73@xxxxxxxxx>> 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
