A Oracle DBA's BLOG

Welcome to my ORACLE DBA blog. You will be Amazing!!!

Friday, December 12, 2014

Oracle GoldenGate Data Integration Replication Test

Oracle GoldenGate Create Extract, Repilicat and Pump Testing Case

The previous post was about  Step by Step Installing and Configuring Oracle 11gR2 GoldenGate So, In this article i would like to present the basic replication of Oracle GoldenGate. 


SOURCEISTABLE Designates Extract as an initial load process extracting records directly from the source tables.
 SPECIALRUN Implements the initial-load Replicat as a one-time run that does not use checkpoints.
  1. Create the Extract Groups (AT SOURCE)

    GGSCI (excellentJob1) 1> ADD EXTRACT ext2, SOURCEISTABLE
     EXTRACT added. 
     
     GGSCI (excellentJob1) 2> ADD EXTTRAIL /u01/ogg1/dirdat/lt, EXTRACT ext2
     EXTTRAIL added. 

    GGSCI (excellentJob1) 3> EDIT PARAMS ext2
     
    -- SET the parameters extract
     
     EXTRACT ext2
    userid ogg, password oracle
    rmthost 192.17.242.188, mgrport 7809
    rmttrail /u01/app/ogg2/dirdat/lt
    DDL INCLUDE ALL;
    TRANLOGOPTIONS DBLOGREADER
    ddl include mapped objname WINSURE.*;
    table ECHO.*;
    table ECHO.APPLVLLOG;
    table ECHO.VEYSI;
    table ECHO.DETAIL;
    :wq!
     
     
    GGSCI (excellentJob1) 4> ADD EXTRACT pump1, EXTTRAILSOURCE /u01/ogg1/dirdat/lt
    EXTRACT added.
     
    GGSCI (excellentJob1) 5> EDIT PARAMS pump1
     
    -- set parameter pump1
      EXTRACT PUMP1
    USERID OGG, PASSWORD oracle
    SETENV (ORACLE_SID = "ORCL")
    SETENV (NLS_LANG = "AMERICAN_AMERICA.WE8ISO8859P9")
    STATOPTIONS, RESETREPORTSTATS, REPORTFETCH
    REPORTCOUNT EVERY 2 MINUTES, RATE
    DISCARDFILE /u01/ogg1/dirrpt/PUMP1.dsc, APPEND, MEGABYTES 200
    RMTHOST 192.17.242.188, MGRPORT 7809, COMPRESS
    RMTTRAIL /u01/app/ogg2/dirdat/1t, FORMAT LEVEL 2
    PASSTHRU
    TABLE WINSURE.*;
    :wq!
    
    
    
    
  2. Create the Replicat Group (AT TARGET)

      GGSCI (excellentJob2) 1> EDIT PARAMS ./GLOBAL

    -- Set Parameter Global 

     GGSCHEMA OGG
    CHECKPOINTTABLE OGG.checkpoint


      GGSCI (excellentJob2) 2> dblogin userid ogg password oracle
     Successfully logged into database.
     
    GGSCI (excellentJob2) 3> add checkpointtable ogg.checkpoint
     Successfully created checkpoint table ogg.CHECKPOINT.

    GGSCI (excellentJob2) 4> ADD REPLICAT rep2, SPECIALRUN

    GGSCI (excellentJob2) 5> EDIT PARAMS  rep2 

    REPLICAT rep2
    ASSUMETARGETDEFS
    HANDLECOLLISIONS
    APPLYNOOPUPDATES
    userid ogg, password oracle
    DDL INCLUDE ALL
    DISCARDFILE /u01/app/ogg2/discards, PURGE
    --SOURCEDEFS /u01/ogg1/dirdef/defgen1.sql
    DDLERROR DEFAULT IGNORE RETRYOP

    MAP ECHO.*,                         TARGET ECHO.*;
    MAP ECHO.APPLVLLOG,       TARGET ECHO.APPLVLLOG;
    MAP ECHO.VEYSI,               TARGET ECHO.VEYSI;
    MAP ECHO.DETAIL,           TARGET ECHO.DETAIL;

     :wq!

    Now get SCN from database for initial 

     SQL> SELECT to_char (dbms_flashback.get_system_change_number) from dual; 
    TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE
    ----------------------------------------
    1343190

    EXPORT SOURCE
    [oracle@excellentJob1 ]$ expdp directory=test_dir dumpfile=ECHO.dmp logfile=ECHO.log schemas=ECHO flashback_scn=1343190

    [oracle@excellentJob1 ]$ scp –p ECHO.dmp 192.17.242.188:/u01/pump

     IMPORT TARGET 

    [oracle@excellentJob2 ]$ impdp directory=test_dir dumpfile=ECHO.dmp logfile=ECHO1.log schemas=ECHO REMAP_TABLESPACE=old:new
    


    [oracle@excellentJob1 ogg1]$ ggsci
     
    GGSCI (excellentJob1) 1> start ext2
     Sending START request to MANAGER …
    EXTRACT EXT2 starting 
     
    GGSCI (excellentJob1) 2> start pump1
     Sending START request to MANAGER ...
    EXTRACT PUMP1 starting
     
      
    GGSCI (excellentJob1) 3> info all
     Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING
    EXTRACT     RUNNING     EXT2        00:00:00      00:00:11
    EXTRACT     RUNNING     PUMP1       00:00:00      00:00:07
     
     
    [oracle@excellentJob2 ogg2]$ ggsci,
     
    GGSCI (excellentJob2) 1> start replicat rep2
      Sending START request to MANAGER …
    REPLICAT REP2 starting 

    GGSCI (excellentJob2) 2> info all 

    Program          Status           Group       Lag at Chkpt    Time Since Chkpt
     

    MANAGER  RUNNING
    REPLICAT    RUNNING     REP2        00:00:00           00:00:08


    Now Lest create new table at source database.

    --SOURCE--

    [oracle@excellentJob1 ]$ sqlplus / as sysdba
     
    SQL> create table ECHO.gold(
    
    ID                 VARCHAR2(5 BYTE) ,
    First_Name         VARCHAR2(20 BYTE),
    Last_Name          VARCHAR2(20 BYTE),
    Salary             Number(8,2),
    City               VARCHAR2(10 BYTE),
    );
     
    Table created. 
    
    
    SQL> Insert into ECHO.gold values(
    1, ‘no1’);
    SQL> Insert into ECHO.gold values(
    2,’no2’);
    SQL> Insert into ECHO.gold values(
    3,’no3’);
    SQL> Insert into ECHO.gold values(
    4,’Uno4’);
    SQL> Insert into ECHO.gold values(
    5,’no5’);
    SQL> commit;
     
     SQL> select count(*) from ECHO.gold;
     
      COUNT(*)
    ----------
            5
     
     [oracle@excellentJob1 ogg1]$ggsci
      
    GGSCI (excellentJob1) 1> stop *
     
    GGSCI (excellentJob1) 1>EDIT PARAMS ext2
     
    -- SET the parameters extract
     
     EXTRACT ext2
    userid ogg, password oracle
    rmthost 192.17.242.188, mgrport 7809
    rmttrail /u01/app/ogg2/dirdat/lt
    DDL INCLUDE ALL;
    TRANLOGOPTIONS DBLOGREADER
    ddl include mapped objname WINSURE.*;
    table ECHO.*;
    table ECHO.APPLVLLOG;
    table ECHO.VEYSI;
    table ECHO.DETAIL;
    table ECHO.GOLD;
     :wq!
     
    --TARGET--
     
    GGSCI (excellentJob2) 1> STOP *

    GGSCI (excellentJob2) 2> EDIT PARAMS  rep2 

    REPLICAT rep2
    ASSUMETARGETDEFS
    HANDLECOLLISIONS
    APPLYNOOPUPDATES
    userid ogg, password oracle
    DDL INCLUDE ALL
    DISCARDFILE /u01/app/ogg2/discards, PURGE
    --SOURCEDEFS /u01/ogg1/dirdef/defgen1.sql
    DDLERROR DEFAULT IGNORE RETRYOP

    MAP ECHO.*,                       TARGET ECHO.*;
    MAP ECHO.APPLVLLOG,       TARGET ECHO.APPLVLLOG;
    MAP ECHO.VEYSI,                TARGET ECHO.VEYSI;
    MAP ECHO.DETAIL,              TARGET ECHO.DETAIL;

    MAP ECHO.gold,                   TARGET ECHO.gold;
     :wq!

    --SOURCE--
    [oracle@excellentJob1 ogg1]$ ggsci
     
    GGSCI (excellentJob1) 1> start ext2
     Sending START request to MANAGER …
    EXTRACT EXT2 starting 
     
    GGSCI (excellentJob1) 2> start pump1
     Sending START request to MANAGER ...
    EXTRACT PUMP1 starting
     
      
    GGSCI (excellentJob1) 3> info all
     Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING
    EXTRACT     RUNNING     EXT2        00:00:00      00:02:09
    EXTRACT     RUNNING     PUMP1       00:00:00      00:02:07
     
     --TARGET--
     
    GGSCI (excellentJob2) 1> start replicat rep2
      Sending START request to MANAGER …
    REPLICAT REP2 starting 

    GGSCI (excellentJob2) 2> info all 

    Program          Status           Group       Lag at Chkpt    Time Since Chkpt
     

    MANAGER  RUNNING
    REPLICAT    RUNNING     REP2        00:00:00           00:01:02
     
    [oracle@excellentJob2 ]$ SQLPLUS / AS SYSDBA
      SQL> select count(*) from ECHO.gold;   
    ----------
            5
     
     

No comments:

Post a Comment