选用的是
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