goldengate for oracle 10g學習

westzq1984發表於2010-03-10
GoOracle 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.200    SID:SOURCE10       TNSNAMES:SOURCE10

目標     IP:83.16.16.201    SID:TARGET10       TNSNAMES: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

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-629104/，如需轉載，請註明出處，否則將追究法律責任。

相關文章