Golden Gate : How To Stop Replicat At a Particular SCN

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!!!!