Hello All,
Below is Golden Gate Scenario which
deals with how to stop Replicat at a particular SCN; such kind of scenario is
good for the requirement wherein let say any team requires a data upto certain
SCN and same can be achieved via below.
Approach used here is to create one
additional table say scn_eva and add it to GG replication and we would be
inserting SCN no of source schema and when Replicat receives the transaction
record for this operation, it stops gracefully.
This is all achieved via using event
marker , also known as the event marker infrastructure (EMI), which enables the
Oracle GoldenGate processes to take a defined action based on an event record
in the transaction log or in the trail (depending on the data source of the process).
The event record is a record that satisfies a specific filter criterion for
which you want an action to occur. You can use this system to customize Oracle
Golden-Gate processing based on database events.
For example, you can use the event
marker system to start, suspend, or stop a process, to perform a
transformation, or to report statistics
The event marker feature is supported
for the replication of data changes, but not for initial loads.
The system requires the following input
components:
1. The event record that triggers the
action can be specified with FILTER, WHERE, or SQLEXEC in a TABLE or MAP
statement. Alternatively, a special TABLE statement in a Replicat parameter
file enables you to perform EVENTACTIONS actions without mapping a source table
to a target table.
2. In the TABLE or MAP statement where
you specify the event record, include the EVENTACTIONS parameter with the
appropriate option to specify the action that is to be taken by the process.
Let’s do one test case:
Source side: Below is the setup
done at source side. Both extract's<extract and pump> are configured.
SCN_TEST is the normal table which will be replicated and SCN_EVA is the one
which will be used to insert SCN so that once received at Replicat. Replicat
will stop gracefully.
create table SCN_TEST(X NUMBER primary key, Y NUMBER);
create table scn_eva (
SCN_ID number primary key
);
ADD TRANDATA scn_eva
ADD Trandata SCN_TEST
EXTRACT es1
USERID gguser, PASSWORD ZZZZZZ
EXTTRAIL /oravl02/oracle/GG/dirdat/es
TABLE XX.SCN_TEST;
TABLE XX.scn_eva;
ADD EXTRACT es1, TRANLOG, BEGIN
NOW
ADD EXTTRAIL /oravl02/oracle/GG/dirdat/es, EXTRACT es1
EXTRACT ep1
USERID gguser, PASSWORD ZZZZZ
RMTHOST PPPP, MGRPORT 7809
RMTTRAIL /oravl02/oradata/GG/GG/dirdat/et
TABLE XX.SCN_TEST;
TABLE XX.scn_eva;
ADD EXTRACT ep1 EXTTRAILSOURCE /oravl02/oracle/GG/dirdat/es
ADD RMTTRAIL /oravl02/oradata/GG/GG/dirdat/et, EXTRACT ep1
Target Side: Below is the
configuration at Target replica side. Note here EVENTACTIONS is the factor
which is important for this activity.
create table SCN_TEST(X NUMBER primary_key, Y NUMBER);
create table scn_eva (
SCN_ID number primary key);
REPLICAT er1
USERID gguser, PASSWORD zzzzzz
ASSUMETARGETDEFS
DISCARDFILE /oravl02/oradata/GG/GG/discards, PURGE
MAP XX.SCN_TEST, TARGET XX.SCN_TEST;
MAP XX.scn_eva, TARGET XX.scn_eva EVENTACTIONS (STOP);------------->> eventactions
ADD REPLICAT er1, EXTTRAIL /oravl02/oradata/GG/GG/dirdat/et
See the status of Extracts and Replicat.
GGSCI> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
EP1 00:00:00
00:00:02
EXTRACT RUNNING
ES1 00:00:00
00:00:08
Source Side: Lets Insert values to SCN_TEST
table.
insert into SCN_TEST values (1000,1001);
insert into SCN_TEST values (2000,2001);
insert into SCN_TEST values (3000,3001);
commit;
Target Side: See if data is replicating or
not? So data is replicating properly. No issues.
select * from scn_test;
X
Y
---------- ----------
1000 1001
2000 2001
3000 3001
GGSCI > info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING ER1
00:00:00 00:08:54
Now at source side take the SCN at
which you want Replicat to STOP and insert the same to the table.
select to_char(current_scn) from
v$database;: So at this SCN Replicat will
Stop and all the data prior inserted to this will get flushed.
CURRENT_SCN
------------------
15337069313472
-----Insert the value to scn_eva so that it
can be replicated to Target side-----------------
insert into scn_eva (SCN_ID) values (15337069313472);
commit;
Target Side: See here replicat stopped
gracefully.
GGSCI > info all
Program Status Group
Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED ER1
00:00:55 00:00:02
GGSCI > info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED
ER1 00:00:55
00:00:06
select * from scn_eva;
SCN_ID
----------
15337069313472
Enjoy Learning!!!!
