Veysi Gunay's ORACLE BLOG: 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.

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!

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

Veysi Gunay's ORACLE BLOG

A Oracle DBA's BLOG

Friday, December 12, 2014

Oracle GoldenGate Data Integration Replication Test

Oracle GoldenGate Create Extract, Repilicat and Pump Testing Case

Create the Extract Groups (AT SOURCE)

Create the Replicat Group (AT TARGET)

Now get SCN from database for initial

No comments:

Post a Comment