GoldenGate Tutorial: Simplest

Posted on April 20, 2010 by Jianming Li

Contents

  1. Overview
  2. Setup Environment Variables
  3. Enable Supplemental Log
  4. Create Source User
  5. Create Target User
  6. Add Trandata
  7. Prepare Extract Group
    1. Create Extract Group
    2. Create Extract Trail
    3. Edit Extract Parameters
    4. Start Extract
  8. Prepare Replicat Group
    1. Creaet Replicat Group
    2. Edit Replicat Parameters
    3. Start Replicat
  9. Test
    1. Insert Data to Source Table
    2. Check Data in Target Table
 

Overview

* This is a simplest scenario where data are replicated from one user (GG_SRC) to another (GG_TGT) on the same database instance using identical table structure (GG_TEST).
* See this post on how to install GoldenGate on a Unix machine.

Setup Environment Variables

This should already have been setup during GoldenGate installation.

ORACLE_HOME=/opt/oracle/product/10.2; export ORACLE_HOME
ORACLE_SID=ggdb; export ORACLE_SID
 
JAVA_HOME=/usr/jdk/instances/jdk1.5.0; export JAVA_HOME
GG_HOME=/home/oracle/goldengate/goldengate1; export GG_HOME
PATH=$GG_HOME:$PATH; export PATH
LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

Enable Supplemental Log

* This should be done once for each database.
* Login as sysdba: sqlplus / as sysdba and run following SQL statements:

-- Enable
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- Start
ALTER SYSTEM SWITCH LOGFILE;
-- Should return 'YES'
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
[oracle@wlbox ~]$ sqlplus / AS sysdba
 
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 
DATABASE altered.
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.
 
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
 
SUPPLEME
--------
YES
 
SQL>

Create Source User

This is the source user whose data will be replicated from.
* Login as sysdba and create src user:

-- Create GG tablespace
CREATE SMALLFILE  TABLESPACE "GG_TABLESPACE" 
DATAFILE 'path/to/oradata/GG_TABLESPACE.dbf' 
SIZE 100M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED 
LOGGING 
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;
 
-- Create src user
CREATE USER GG_SRC IDENTIFIED BY password 
DEFAULT TABLESPACE GG_TABLESPACE
TEMPORARY TABLESPACE TEMP;
 
GRANT CONNECT,resource TO GG_SRC;
GRANT SELECT any dictionary, SELECT any TABLE TO GG_SRC;
GRANT CREATE TABLE TO GG_SRC;
GRANT flashback any TABLE TO GG_SRC;

* Login as newly created src user and create test table:

sqlplus gg_src/password
 
-- Create src table
CREATE TABLE GG_SRC.GG_TEST
(
ID VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20)
, CONSTRAINT GG_TEST_PK PRIMARY KEY
(
ID
)
ENABLE
);

Create Target User

This is the target user where data will be replicated to.
* Login as sysdba and create dest user:

-- Create target user
CREATE USER GG_TGT IDENTIFIED BY password 
DEFAULT TABLESPACE GG_TABLESPACE
TEMPORARY TABLESPACE TEMP;
 
GRANT CONNECT,resource TO GG_TGT;
GRANT SELECT any dictionary, SELECT any TABLE TO GG_TGT;
GRANT CREATE TABLE TO GG_TGT;
GRANT flashback any TABLE TO GG_TGT;

* Login as gg_tgt user and create test table:

sqlplus gg_tgt/password
 
-- Create target table
CREATE TABLE GG_TGT.GG_TEST
(
ID VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20)
, CONSTRAINT GG_TEST_PK PRIMARY KEY
(
ID
)
ENABLE
);

Add Trandata

From GGSCI:

-- Login source schema
DBLOGIN USERID GG_SRC, PASSWORD password
-- Enable trandata for gg_test
ADD TRANDATA gg_src.gg_test

* Example:

GGSCI (wlbox) 13> DBLOGIN USERID GG_SRC, PASSWORD password
Successfully logged into database.
 
GGSCI (wlbox) 14> ADD TRANDATA gg_src.gg_test
 
Logging of supplemental redo data enabled for table GG_SRC.GG_TEST.

Prepare Extract Group

Create Extract Group

From GGSCI:

ADD EXTRACT e_gt, TRANLOG, BEGIN now

Create Extract Trail

From GGSCI:

ADD EXTTRAIL /home/oracle/goldengate/goldengate1/dirdat/gt, EXTRACT e_gt, MEGABYTES 20

Edit Extract Parameters

From GGSCI:

EDIT PARAMS e_gt

From vi:

-- Set some envs
SETENV (ORACLE_HOME = "/opt/oracle/product/10.2")
SETENV (ORACLE_SID = "ggdb")
 
-- Specify extract group
EXTRACT e_gt
 
-- Used to login db
USERID gg_src, PASSWORD password
 
-- Specify extract trail file
EXTTRAIL /home/oracle/goldengate/goldengate1/dirdat/gt
 
-- Specify tables to extract
TABLE gg_src.gg_test;

Start Extract

 
-- Start manager process if not already done
start mgr
start extract e_gt
view report e_gt

Prepare Replicat Group

Creaet Replicat Group

 
ADD REPLICAT r_gt, EXTTRAIL /home/oracle/goldengate/goldengate1/dirdat/gt, NODBCHECKPOINT, BEGIN now

Edit Replicat Parameters

From GGSCI:

EDIT PARAMS r_gt

From vi:

SETENV (ORACLE_HOME = "/opt/oracle/product/10.2")
SETENV (ORACLE_SID = "ggdb")
 
REPLICAT r_gt
-- Target table has identical structure as source
ASSUMETARGETDEFS
USERID gg_tgt, PASSWORD password
-- Map tables
MAP gg_src.gg_test, TARGET gg_tgt.gg_test;

Start Replicat

 
start replicat r_gt

Test

Insert Data to Source Table

 
INSERT INTO GG_SRC.gg_test VALUES ('1','One');
commit;
SELECT * FROM gg_test;
ID                   NAME
--------------- --------------------
1                    One

Check Data in Target Table

 
SELECT * FROM GG_TGT.gg_test;
-- You should see
ID                   NAME
--------------- --------------------
1                    One
This entry was posted in goldengate and tagged , , . Bookmark the permalink.

One Response to GoldenGate Tutorial: Simplest

  1. Pingback: Goldengate visão geral e dicas de configurações padrão | BLOG DO ALEXANDRE PIRES

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.