That’s Me

Gavin Soorma
Oracle Certified Professional
7.3, 8i, 9i,10g, 11g, 12c
11i Apps DBA OCP
10g RAC OCE
Certified GoldenGate Implementation Specialist
Oracle 11g Exadata Implementation Specialist
10g OCM
11g OCM

Feedback

5737480 hits

Thanks A MILLION for your support!

Please send me your valuable feedback and suggestions

GoldenGate - Online Change Synchronization with the initial data load

Recently a question was posed to me as to how do we handle changes that are happening to the data while the initial data load extract process is in operation. Sometimes it may not be possible to have an application outage just to peform an initial data load and in most cases we will need to perform the initial data load using GoldenGate while users are connected to the database and changes are being made to the database via the application.

In my earlier tutorials we have discussed how to perform an initial data load and how to perform subsequent change synchronization to keep the data in sync. In this case we will just combine both those procedures into one.

So broadly speaking the steps would be :

  • Create the initial data load extract process or group
  • Create the online change synchronization extract process or group
  • Create the initial replicat group
  • Create the online online change synchronization replicat group
  • Start the online change synchronization extract process
  • Start the initial data load extract process
  • When the initial data load process has completed start the online change replicat group
  • Let us see a simple example where we will be peforming an initial data load of the MYOBJECTS table (copy of DBA_OBJECTS) and while the initial data load extract process is running and loading the 70,000 odd rows, we will from another session update the table while the data load is in progress. We will then see how these changes are also replicated to the target.

    Source

    SQL> select count(*) from myobjects;
    
      COUNT(*)
    ----------
         71338
    

    Target

    SQL> select count(*) from myobjects;
    
      COUNT(*)
    ----------
             0
    

    Note – we will see that we have used the HANDLECOLLISIONS keyword in the replicat parameter file. If the source database will remain active during the initial load, include the HANDLECOLLISIONS parameter in the Replicat parameter file.

    HANDLECOLLISIONS accounts for collisions that occur during the overlap of time between the initial load and the ongoing change replication. It reconciles insert operations for which the row already exists, and it reconciles update and delete operations.

    We will turn this off after the initial data load has been completed.

    Create the Initial Data Load Extract and Replicat Processes

    Source

    GGSCI (linux01.oncalldba.com) 20>  ADD EXTRACT ext1, SOURCEISTABLE
    EXTRACT added.
    
    GGSCI (linux01.oncalldba.com) 21> edit params ext1
    
    EXTRACT ext1
    USERID ggs_owner, PASSWORD ggs_owner
    RMTHOST 192.168.10.194, MGRPORT 7809
    RMTTASK replicat, GROUP rep1
    TABLE ggs_owner.myobjects;
    

    Target

    GGSCI (linux02.oncalldba.com) 7> add replicat rep1, specialrun
    REPLICAT added.
    
    GGSCI (linux02.oncalldba.com) 8> edit params rep1
    
    REPLICAT rep1
    HANDLECOLLISIONS
    USERID ggs_owner, PASSWORD ggs_owner
    ASSUMETARGETDEFS
    MAP ggs_owner.myobjects, TARGET ggs_owner.myobjects;
    

    Create the Online Change Synchronization Extract and Replicat Processes

    Source

    GGSCI (linux01.oncalldba.com) 8> ADD EXTRACT ext2, TRANLOG, BEGIN NOW
    EXTRACT added.
    
    GGSCI (linux01.oncalldba.com) 13> ADD RMTTRAIL /home/oracle/goldengate/dirdat/zz, EXTRACT ext2
    RMTTRAIL added.
    
    GGSCI (linux01.oncalldba.com) 9> edit params ext2
    
    EXTRACT ext2
    USERID ggs_owner, PASSWORD  ggs_owner
    RMTHOST 192.168.10.194, MGRPORT 7809
    RMTTRAIL /home/oracle/goldengate/dirdat/zz
    TABLE ggs_owner.myobjects;
    

    Target

    GGSCI (linux02.oncalldba.com) 2> add replicat rep2, exttrail  /home/oracle/goldengate/dirdat/zz
    REPLICAT added.
    
    GGSCI (linux02.oncalldba.com) 4>  edit params rep2
    
    REPLICAT rep2
    HANDLECOLLISIONS
    ASSUMETARGETDEFS
    USERID ggs_owner, PASSWORD ggs_owner
    MAP ggs_owner.myobjects, TARGET ggs_owner.myobjects ;
    

    Start the Online Change Extract EXT2

    GGSCI (linux01.oncalldba.com) 14> start extract ext2
    
    Sending START request to MANAGER ...
    EXTRACT EXT2 starting
    
    GGSCI (linux01.oncalldba.com) 15> info extract ext2
    
    EXTRACT    EXT2      Last Started 2010-07-13 14:15   Status RUNNING
    Checkpoint Lag       00:00:00 (updated 00:03:50 ago)
    Log Read Checkpoint  Oracle Redo Logs
                         2010-07-13 14:11:57  Seqno 149, RBA 19817488
    

    Start the Initial Load Extract

    GGSCI (linux01.oncalldba.com) 39> start extract ext1
    
    Sending START request to MANAGER ...
    EXTRACT EXT1 starting
    
    GGSCI (linux01.oncalldba.com) 41> info extract ext1
    
    EXTRACT    EXT1      Last Started 2010-07-13 14:34   Status RUNNING
    Checkpoint Lag       Not Available
    Log Read Checkpoint  Table GGS_OWNER.MYOBJECTS
                         2010-07-13 14:34:42  Record 2548
    
    

    While the Initial Load Extract is in progress make some changes in the database

    SQL> update myobjects set owner='GAVIN' where owner='SYS';
    
    30001 rows updated.
    
    SQL> commit;
    
    Commit complete.
    

    When the initial extract process has loaded all the rows, it will stop and so will the initial replicat process

    GGSCI (linux01.oncalldba.com) 39> info extract ext1
    
    EXTRACT    EXT1      Last Started 2010-07-13 15:01   Status RUNNING
    Checkpoint Lag       Not Available
    Log Read Checkpoint  Table GGS_OWNER.MYOBJECTS
                         2010-07-13 15:04:22  Record 61964
    Task                 SOURCEISTABLE
    
    
    GGSCI (linux01.oncalldba.com) 40>  info extract ext1
    
    EXTRACT    EXT1      Last Started 2010-07-13 15:01   Status STOPPED
    Checkpoint Lag       Not Available
    Log Read Checkpoint  Table GGS_OWNER.MYOBJECTS
                         2010-07-13 15:04:44  Record 71307
    Task                 SOURCEISTABLE
    

    On Target

    GGSCI (linux02.oncalldba.com) 15> send replicat rep1 getlag
    
    ERROR: REPLICAT REP1 not currently running.
    

    We will now start the online change replicat process. This will apply all the changes which have occurred during the initial data load. Note that once the replicat process has finished applying all the changes that are stored in the trail files (which have been written to by the extract process running on source) we will see the “At EOF, no more records ….”

    GGSCI (linux02.oncalldba.com) 6> start replicat rep2
    
    Sending START request to MANAGER ...
    REPLICAT REP2 starting
    
    GGSCI (linux02.oncalldba.com) 7> info replicat rep2
    
    REPLICAT   REP2      Last Started 2010-07-13 15:06   Status RUNNING
    Checkpoint Lag       00:00:00 (updated 00:05:47 ago)
    Log Read Checkpoint  File /home/oracle/goldengate/dirdat/zz000000
                         First Record  RBA 0
    
    GGSCI (linux02.oncalldba.com) 8> send replicat rep2 getlag
    
    Sending GETLAG request to REPLICAT REP2 ...
    Last record lag: 222 seconds.
    
    GGSCI (linux02.oncalldba.com) 9> send replicat rep2 getlag
    
    Sending GETLAG request to REPLICAT REP2 ...
    Last record lag: 233 seconds.
    
    GGSCI (linux02.oncalldba.com) 10> send replicat rep2 getlag
    
    Sending GETLAG request to REPLICAT REP2 ...
    Last record lag: 238 seconds.
    At EOF, no more records to process.
    

    Let us now check if both the initial data load and the updates have been propagated and applied on the target side.

    SQL> select count(*) from myobjects;
    
      COUNT(*)
    ----------
         71307
    
    SQL> select count(*) from myobjects where owner='GAVIN';
    
      COUNT(*)
    ----------
         30000
    

    Now remove the HANDLECOLLISIONS clause …

    GGSCI (linux02.oncalldba.com) 13> send replicat rep2,nohandlecollisions
    
    Sending NOHANDLECOLLISIONS request to REPLICAT REP2 ...
    REP2 NOHANDLECOLLISIONS set for 1 tables and 0 wildcard entries
    

    Also remove the line from the replicat parameter file via the “edit params replicat rep2 command”

    2 comments to GoldenGate – Online Change Synchronization with the initial data load

    Leave a Reply

      

      

      

    You can use these HTML tags

    <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>