goldengate for oracle10g,goldengate for oracle 10g学习

选用的是

a reporting configuration with a data pump on the source system + Loading data with a GoldenGate direct load

也就是使用DATA PUMP的单向复制+使用GG来进行数据初始化

环境

源IP:83.16.16.200SID:SOURCE10TNSNAMES:SOURCE10

目标IP:83.16.16.201SID:TARGET10TNSNAMES:TARGET10

创建OGG用户

SOURCE库(SYS)

CREATE USER ogg IDENTIFIED BY oracle;

@marker_setup.sql

@ddl_setup.sql

@role_setup.sql

GRANT CREATE SESSION TO ogg;

GRANT ALTER SESSION TO ogg;

GRANT RESOURCE TO ogg;

GRANT CONNECT TO ogg;

GRANT SELECT ANY DICTIONARY TO ogg;

GRANT FLASHBACK ANY TABLE TO ogg;

GRANT SELECT ANY TABLE TO ogg;

GRANT EXECUTE ON dbms_flashback TO ogg;

GRANT GGS_GGSUSER_ROLE TO ogg;

GRANT ALTER ANY TABLE TO ogg;

GRANT DELETE ANY TABLE TO ogg;

GRANT EXECUTE ON UTL_FILE TO ogg;

--@ddl_enable.sql

--@ddl_pin.sql

TARGET库(SYS)

CREATE USER ogg IDENTIFIED BY oracle;

GRANT CREATE SESSION TO ogg;

GRANT ALTER SESSION TO ogg;

GRANT RESOURCE TO ogg;

GRANT CONNECT TO ogg;

GRANT INSERT ANY TABLE TO ogg;

GRANT UPDATE ANY TABLE TO ogg;

GRANT DELETE ANY TABLE TO ogg;

GRANT CREATE TABLE TO ogg;

GRANT SELECT ANY TABLE TO ogg;

GRANT ALTER ANY TABLE TO ogg;

GRANT SELECT ANY DICTIONARY TO ogg;

配置MGR(SOURCE/TARGET)

SOURCE10

GGSCI (zhangqiaoc) 73> edit params mgr

PORT 7809

start mgr

TARGET10

GGSCI (zhangqiaoc2) 73> edit params mgr

PORT 7809

PURGEOLDEXTRACTS TRUE

start mgr

在TARGET上创建CKPT表

EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ogg.ckpt_table

DBLOGIN USERID ogg@target10 PASSWORD oracle

ADD CHECKPOINTTABLE ogg.ckpt_table

在SOURCE上开启FORCE LOGGING

GGSCI (zhangqiaoc) 8> dblogin userid ogg@source10 password oracle

Successfully logged into database.

GGSCI (zhangqiaoc) 9> info TRANDATA hr.*

Logging of supplemental redo log data is disabled for table HR.COUNTRIES.

Logging of supplemental redo log data is disabled for table HR.DEPARTMENTS.

Logging of supplemental redo log data is disabled for table HR.EMPLOYEES.

Logging of supplemental redo log data is disabled for table HR.JOBS.

Logging of supplemental redo log data is disabled for table HR.JOB_HISTORY.

Logging of supplemental redo log data is disabled for table HR.LOCATIONS.

Logging of supplemental redo log data is disabled for table HR.REGIONS.

GGSCI (zhangqiaoc) 10> add TRANDATA hr.*

Logging of supplemental redo data enabled for table HR.COUNTRIES.

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

Logging of supplemental redo data enabled for table HR.EMPLOYEES.

Logging of supplemental redo data enabled for table HR.JOBS.

Logging of supplemental redo data enabled for table HR.JOB_HISTORY.

Logging of supplemental redo data enabled for table HR.LOCATIONS.

Logging of supplemental redo data enabled for table HR.REGIONS.

配置变化数据捕获

SOURCE10

GGSCI (zhangqiaoc) 13> add extract ext1,tranlog,begin now

EXTRACT added.

GGSCI (zhangqiaoc) 14> add exttrail /home/ogg/goldengate/dirdat/e1,extract ext1

EXTTRAIL added.

GGSCI (zhangqiaoc) 15> edit params ext1

-- Identify the Extract group:

EXTRACT ext1

-- Specify database login information as needed for the database:

USERID ogg@source10, PASSWORD oracle

-- Specify the local trail that this Extract writes to:

EXTTRAIL /home/ogg/goldengate/dirdat/e1

DDL INCLUDE MAPPED OBJNAME "HR.*"

-- Specify tables to be captured:

TABLE hr.*;

GGSCI (zhangqiaoc) 16> add extract pump1,exttrailsource /home/ogg/goldengate/dirdat/e1,begin now

EXTRACT added.

GGSCI (zhangqiaoc) 17> add rmttrail /home/ogg/goldengate/dirdat/r1,extract pump1

RMTTRAIL added.

GGSCI (zhangqiaoc) 18> edit params pump1

-- Identify the data pump group:

EXTRACT pump1

-- Specify database login information as needed for the database:

USERID ogg@source10, PASSWORD oracle

-- Specify the name or IP address of the target system:

RMTHOST 83.16.16.201, MGRPORT 7809

-- Specify the remote trail on the target system:

RMTTRAIL /home/ogg/goldengate/dirdat/r1

-- Allow mapping, filtering, conversion or pass data through as-is:

NOPASSTHRU

-- Specify tables to be captured:

TABLE hr.*;

TARGET10

GGSCI (zhangqiaoc2) 3> add replicat rep1,exttrail /home/ogg/goldengate/dirdat/r1,begin now

REPLICAT added.

GGSCI (zhangqiaoc2) 4> edit params rep1

-- Identify the Replicat group:

REPLICAT rep1

-- State whether or not source and target definitions are identical:

ASSUMETARGETDEFS

-- Specify database login information as needed for the database:

USERID ogg@target10, PASSWORD oracle

-- Specify error handling rules:

-- #REPERROR (, )

DDL INCLUDE MAPPED OBJNAME "HR.*"

-- Specify tables for delivery:

MAP hr.*, TARGET hr.*

配置初始化(GoldenGate direct load)

SOURCE10

GGSCI (zhangqiaoc) 30>add extract ext1_ini,sourceistable

EXTRACT added.

GGSCI (zhangqiaoc) 31> edit params ext1_ini

EXTRACT ext1_ini

USERID ogg@source10,password oracle

RMTHOST 83.16.16.201,MGRPORT 7809

RMTTASK REPLICAT,GROUP rep1_ini

TABLE hr.*

TARGET10

GGSCI (zhangqiaoc2) 11> add replicat rep1_ini,specialrun

REPLICAT added.

GGSCI (zhangqiaoc2) 12> edit params rep1_ini

REPLICAT rep1_ini

USERID ogg@target10,password oracle

ASSUMETARGETDEFS

MAP hr.*,target hr.*;

同步

SOURCE10

GGSCI (zhangqiaoc) 34> start extract ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI (zhangqiaoc) 35> status extract ext1

EXTRACT EXT1: RUNNING

GGSCI (zhangqiaoc) 51> start extract pump1

Sending START request to MANAGER ...

EXTRACT PUMP1 starting

GGSCI (zhangqiaoc) 52> status pump1

EXTRACT PUMP1: RUNNING

GGSCI (zhangqiaoc) 36> start extract ext1_ini

Sending START request to MANAGER ...

EXTRACT EXT1_INI starting

TARGET10

GGSCI (zhangqiaoc2) 19> start replicat rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (zhangqiaoc2) 20> status rep1

REPLICAT REP1: RUNNING