Oracle GoldenGate 11G同步配置

 

Item	Source System	Target System
Platform	CENTOS7 - 64bit	CENTOS7 - 64bit
Database	Oracle 11.2.0.4	Oracle 11.2.0.4
Character Set	AL32UTF8	AL32UTF8
ORACLE_SID	cd	sz
Listener Name/Port	LISTENER/1521	LISTENER/1521
Goldengate User	ogg	ogg

1.安装OGG，创建目录（源库和目标库均有此操作）

 

[oracle@localhost oracle]$ mkdir ogg
[oracle@localhost oracle]$ cd ogg/
[oracle@localhost ogg]$ unzip V34339-01.zip
[oracle@localhost ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 
[oracle@localhost ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (localhost.localdomain) 1> create subdirs

Creating subdirectories under current directory /dbfile/oracle/ogg

Parameter files                /dbfile/oracle/ogg/dirprm: already exists
Report files                   /dbfile/oracle/ogg/dirrpt: created
Checkpoint files               /dbfile/oracle/ogg/dirchk: created
Process status files           /dbfile/oracle/ogg/dirpcs: created
SQL script files               /dbfile/oracle/ogg/dirsql: created
Database definitions files     /dbfile/oracle/ogg/dirdef: created
Extract data files             /dbfile/oracle/ogg/dirdat: created
Temporary files                /dbfile/oracle/ogg/dirtmp: created
Stdout files                   /dbfile/oracle/ogg/dirout: created

 

各目录用途如下表：

名字	用途
dirprm	存放OGG参数各的配置信息
dirrpt	存放进程报告文件
dirchk	存放检查点文件
dirpcs	存放进程状态文件
dirsql	存放SQL脚本文件
dirdef	存放DEFGEN工具生成的数据定义文件
dirdat	存放Trail文件，也就是Capture进程捕获的日志文件
dirtmp	当事物需要的内存超过已分配内存时，默认存储在这个目录

2.为Goldengate 配置Oracle 数据库

2.1在源库（cd）创建OGG用户并赋权

 

create tablespace OGG datafile '/dbfile/oracle/oradata/cd/OGG.DBF' size 2G;
create user OGG identified by "OGG" default tablespace OGG temporary tablespace TEMP quota unlimited on OGG;
grant CONNECT, RESOURCE to OGG;
grant CREATE SESSION, ALTER SESSION to OGG;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to OGG;
grant ALTER ANY TABLE to OGG;
grant FLASHBACK ANY TABLE to OGG;
grant EXECUTE ON DBMS_FLASHBACK to OGG;
grant SELECT ON DBA_CLUSTERS to OGG;
grant SELECT ANY TRANSACTION to OGG;

 

2.2在源库创建测试表，插入数据，添加主键约束

 

create table SCOTT.TO_BASE
(
  mo_number       VARCHAR2(30) not null,
  creater         VARCHAR2(20),
  createdate      DATE default SYSDATE
);
alter table SCOTT.TO_BASE
  add constraint PK_C_MO_BASE primary key (MO_NUMBER);

insert into SCOTT.TO_BASE (MO_NUMBER, CREATER, CREATEDATE) values ('111', 'LEO', to_date('08-11-2021 18:51:23', 'dd-mm-yyyy hh24:mi:ss'));

insert into SCOTT.TO_BASE (MO_NUMBER, CREATER, CREATEDATE) values ('222', 'TOM', to_date('08-11-2021 18:51:23', 'dd-mm-yyyy hh24:mi:ss'));

commit;

 

2.3在目标库（sz）创建OGG用户并赋权

 

create tablespace OGG datafile '/dbfile/oracle/oradata/sz/OGG.DBF' size 3G;
create user OGG identified by "OGG" default tablespace OGG temporary tablespace TEMP quota unlimited on OGG;
grant CONNECT, RESOURCE to OGG;
grant CREATE SESSION, ALTER SESSION to OGG;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to OGG;
grant CREATE TABLE to OGG;
grant INSERT ANY TABLE to OGG;
grant UPDATE ANY TABLE to OGG;
grant DELETE ANY TABLE to OGG;

 

2.4在目标库创建测试表结构（空表即可）

 

create table SCOTT.TO_BASE
(
  mo_number       VARCHAR2(30) not null,
  creater         VARCHAR2(20),
  createdate      DATE default SYSDATE
);
alter table SCOTT.TO_BASE
  add constraint PK_C_MO_BASE primary key (MO_NUMBER);

 

2.5源库开启补充日志模式和强制记录模式

 

SQL> alter database add supplemental log data;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

 

2.6源库开启归档日志

[oracle@localhost ~]$ mkdir /db/arch
SQL> alter system set log_archive_dest_1='location=/db/arch';
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter system archive log current;
SQL> archive log list;

2.7源库添加表级TRANDATA

GGSCI (localhost.localdomain) 2> dblogin userid OGG,password OGG
Successfully logged into database.

GGSCI (localhost.localdomain) 3> add trandata SCOTT.TO_BASE

Logging of supplemental redo data enabled for table SCOTT.TO_BASE.

GGSCI (localhost.localdomain) 4> info trandata SCOTT.TO_BASE

Logging of supplemental redo log data is enabled for table SCOTT.TO_BASE.

Columns supplementally logged for table SCOTT.TO_BASE: MO_NUMBER.

2.8配置源库MGR管理进程

GGSCI (localhost.localdomain) 5> edit params mgr

PORT 7809
PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS

GGSCI (localhost.localdomain) 6> start mgr

Manager started.

GGSCI (localhost.localdomain) 7> info mgr

Manager is running (IP port localhost.localdomain.7809).

2.9配置目标库MGR管理进程

 

GGSCI (localhost.localdomain) 2> edit params mgr

PORT 7809
PURGEOLDEXTRACTS /dbfile/oracle/ogg/dirdat, USECHECKPOINTS

GGSCI (localhost.localdomain) 3> start mgr

Manager started.

GGSCI (localhost.localdomain) 4> info mgr

Manager is running (IP port localhost.localdomain.7809).

 

参数说明：

PORT 7809：OGG管理进程监控端口。

PURGEOLDEXTRACTS：清除不需要的trail文件。

/ogg/dirdat：trail文件存放位置。

USECHECKPOINTS：使用检查点队列。

本实验只用到上述参数，MGR其他参数详见下表，摘自OGG官方文档。

Manager parameters: General

名字	用途
CHARSET	Speci fies a multibyte character set for the process to us instead of the operating system default when reading the parameter file.
COMMENT	Allows insertion of comments in a parameter file.
SOURCEDB	Specifies a data source name as part of the login
USERID	Provides login information for Manager when it needs to access the database.
SYSLOG	Filters the types of Oracle GoldenGate messages that are written to the system logs.

Manager parameters: Port management

名字	用途
DYNAMICPORTLIST	Specifies the ports that Collector can dynamically allocate.
PORT	Establishes the TCP/IP port number on which Manager listens for requests.

Manager parameters: Process management

名字	用途
AUTORESTART	Specifies processes to be restarted by Manager after a
AUTOSTART	Specifies processes to be started when Manager starts.
BOOTDELAYMINUTES	Determines how long after system boot time Manager delays until performing main processing activities. This parameter supports Windows.
UPREPORT	Determines how often process heartbeat messages are reported.

Manager parameters: Event management

名字	用途
DOWNCRITICAL	Reports processes that stopped gracefully or abnormally.
DOWNREPORT	Controls the frequency for reporting stopped processes.
LAGCRITICAL	Specifies a lag threshold that is considered critical and generates a warning to the error log.
LAGINFO	Specifies a lag threshold at which an informational message is reported to the error log.
LAGREPORT	Sets an interval for reporting lag time to the error log.

Manager parameters: Maintenance

名字	用途
CHECKMINUTES	Determines how often Manager cycles through maintenance
PURGEDDLHISTORY	Purges rows from the Oracle DDL history table when they are no longer needed.
PURGEDDLHISTORYALT	Purges rows from the alternate Oracle DDL history table that keeps track of partition IDs that are associated with a table ID.
PURGEMARKERHISTORY	Purges Oracle marker table rows that are no longer needed.
PURGEOLDEXTRACTS	Purges trail data that is no longer needed.
PURGEOLDTASKS	Purges Extract and Replicat tasks after a specified period of time.
STARTUPVALIDATIONDELAY[CSECS]	Sets a delay time after which Manager checks that processes are still running after startup.

2.10配置初始化数据进程

添加一个名为EINI_1 的Extract 进程：

 

GGSCI (localhost.localdomain) 8> add extract eini_1,sourceistable
EXTRACT added.


GGSCI (localhost.localdomain) 9> info extract *,tasks

EXTRACT    EINI_1    Initialized   2021-11-08 19:44   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE


GGSCI (localhost.localdomain) 10> edit params eini_1

-- GoldenGate Initial Data Capture
-- for SCOTT.TO_BASE
EXTRACT EINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID OGG, PASSWORD OGG
RMTHOST 192.168.1.102, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINI_1
TABLE SCOTT.TO_BASE;

 

参数介绍：

EXTRACT EINI_1：说明这是EXTRACT进程，名字是EINI_1

SETENV：环境变量，一定要设置和数据库字符集一样，否则可能会乱码

USERID：数据库OGG用户

PASSWORD：数据库用户OGG的密码

RMTHOST：目标端地址，如果在/etc/hosts文件里已经设置解析，可以写主机名

MGRPORT：目标端MGR管理进程监听的端口

RMTTASK REPLICAT：目标端REPLICAT应用进程的组和名字

TABLE：源端要初始化数据的表的名字

编辑好捕获进程EINI_1后，还需要在目标端配置REPLICAT应用进程，名字要和源端的捕获进程EINI_1里面RMTTASK REPLICAT参数配置的一样，也就是还需要在目标端配置RMTTASK REPLICAT RINI_1。

 

2.11配置目标端REPLICAT进程

 

GGSCI (localhost.localdomain) 5> add replicat rini_1,specialrun
REPLICAT added.


GGSCI (localhost.localdomain) 6> info replicat *,tasks

REPLICAT   RINI_1    Initialized   2021-11-08 19:49   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN


GGSCI (localhost.localdomain) 7> edit params rini_1

-- GoldenGate Initial Load Delivery
REPLICAT RINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
USERID OGG, PASSWORD OGG
DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE
MAP scott.*, TARGET scott.*;

 

2.12初始化数据

在源库中启动Initial Load 进程EINI_1，然后目标库中的RINI_1 进程将自动启动：

 

GGSCI (localhost.localdomain) 11> start extract eini_1

Sending START request to MANAGER ...
EXTRACT EINI_1 starting

GGSCI (localhost.localdomain) 12> info extract eini_1

EXTRACT    EINI_1    Initialized   2021-11-08 19:44   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

 

启动源端的捕获进程EINI_1后，正常情况下(如果配置没问题)，源端的数据已经传送到目标端了，可以通过VIEW命令查看源端捕获进程EINI_1的工作状态。

 

GGSCI (localhost.localdomain) 13> view report eini_1

 

最后看到两条测试数据已插入

 

Output to RINI_1:

From Table SCOTT.TO_BASE:
       #                   inserts:         2
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


REDO Log Statistics
  Bytes parsed                    0
  Bytes output                  222

 

亦可在目标库查看日志

 

GGSCI (localhost.localdomain) 9> view report rini_1

 

初始加载后，提取进程EINI_1 和复制进程RINI_1 自动停止，通常初始化数据工作只会做一次，可以通过INFO命令查看进程的状态。

源库

 

GGSCI (localhost.localdomain) 14> info extract eini_1

EXTRACT    EINI_1    Last Started 2021-11-08 19:51   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table SCOTT.TO_BASE
                     2021-11-08 19:51:50  Record 2
Task                 SOURCEISTABLE

 

目标库

 

GGSCI (localhost.localdomain) 11> info replicat rini_1

REPLICAT   RINI_1    Initialized   2021-11-08 19:49   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:11:32 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

 

2.13源端配置捕获进程 

GGSCI (localhost.localdomain) 15> edit params eora_1


-- Change Capture parameter file to capture SCOTT.TO_BASE changes
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD OGG
EXTTRAIL ./dirdat/la
TABLE SCOTT.TO_BASE;

GGSCI (localhost.localdomain) 16> view params eora_1

-- Change Capture parameter file to capture SCOTT.TO_BASE changes
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD OGG
EXTTRAIL ./dirdat/la
TABLE SCOTT.TO_BASE;

 2.14源库中执行以下命令以添加 Primary Extract 组

GGSCI (localhost.localdomain) 17> add extract eora_1,tranlog,begin now
EXTRACT added.

2.15源库定义 GoldenGate 本地路径 

GGSCI (localhost.localdomain) 18> ADD EXTTRAIL ./dirdat/la, EXTRACT EORA_1, MEGABYTES 5
EXTTRAIL added.

2.16源库启动主 Extract 进程

GGSCI (localhost.localdomain) 19> start extract eora_1

Sending START request to MANAGER ...
EXTRACT EORA_1 starting

GGSCI (localhost.localdomain) 20> info extract eora_1

EXTRACT    EORA_1    Last Started 2021-11-09 09:09   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2021-11-09 09:16:00  Seqno 64, RBA 303616
                     SCN 0.788791 (788791)

GGSCI (localhost.localdomain) 21> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EORA_1      00:00:00      00:00:04

启动 eora_1后在./dirdat/文件夹下生成la000000文件

[root@localhost ogg]# ls -ll dirdat/
total 4
-rw-rw-rw- 1 oracle oinstall 1470 Nov  9 09:10 la000000

2.17在源库中配置pump进程

GGSCI (localhost.localdomain) 22> edit params pora_1


-- Data Pump parameter file to read the local trail of SCOTT.TO_BASE changes
EXTRACT PORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
PASSTHRU
RMTHOST 192.168.1.102, MGRPORT 7809
RMTTRAIL ./dirdat/ra
TABLE SCOTT.TO_BASE;

2.18在源库添加数据泵提取组

GGSCI (localhost.localdomain) 23> add extract pora_1,exttrailsource ./dirdat/la

EXTRACT added.

2.19在源库添加远程路径

GGSCI (localhost.localdomain) 24> add rmttrail ./dirdat/ra,extract pora_1,megabytes 5
RMTTRAIL added.

2.20在源库启动PUMP传输进程

GGSCI (localhost.localdomain) 25> start extract pora_1

Sending START request to MANAGER ...
EXTRACT PORA_1 starting


GGSCI (localhost.localdomain) 26> info extract pora_1

EXTRACT    PORA_1    Last Started 2021-11-09 11:53   Status RUNNING
Checkpoint Lag       01:46:28 (updated 00:00:01 ago)
Log Read Checkpoint  File ./dirdat/la000000
                     2021-11-09 10:07:12.000000  RBA 1866

GGSCI (localhost.localdomain) 27> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING 
EXTRACT RUNNING EORA_1 00:00:00 00:00:06 
EXTRACT RUNNING PORA_1 00:00:00 00:00:00

2.21在目标库配置checkpoint表 

GGSCI (localhost.localdomain) 13> edit params ./GLOBALS

CHECKPOINTTABLE ogg.ggschkpt

GGSCI (localhost.localdomain) 14> exit

2.22在目标系统中添加复制检查点表

GGSCI (localhost.localdomain) 1> dblogin userid ogg,password OGG
Successfully logged into database.

GGSCI (localhost.localdomain) 2> add checkpointtable

No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...

Successfully created checkpoint table ogg.ggschkpt.

2.23在目标库添加复制组

GGSCI (localhost.localdomain) 3> add replicat rora_1,exttrail ./dirdat/ra
REPLICAT added.

2.24在目标库配置同步进程RORA_1

GGSCI (localhost.localdomain) 4> edit params rora_1


-- Change Delivery parameter file to apply SCOTT.TO_BASE Changes
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD OGG
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORAaa.DSC, PURGE
MAP SCOTT.TO_BASE, TARGET SCOTT.TO_BASE;

可通过view查看内容

GGSCI (localhost.localdomain) 5> view params rora_1

-- Change Delivery parameter file to apply SCOTT.TO_BASE Changes
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD OGG
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORAaa.DSC, PURGE
MAP SCOTT.TO_BASE, TARGET SCOTT.TO_BASE;

2.25启动复制过程

GGSCI (localhost.localdomain) 8> start replicat rora_1

Sending START request to MANAGER ...
REPLICAT RORA_1 starting


GGSCI (localhost.localdomain) 9> info replicat rora_1

REPLICAT   RORA_1    Last Started 2021-11-09 13:55   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint  File ./dirdat/ra000000
                     First Record  RBA 0


GGSCI (localhost.localdomain) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     RORA_1      00:00:00      00:00:01 

此时在源库中对表SCOTT.TO_BASE的增删改操作都会同步到目标库

关机步骤

源库：
GGSCI (localhost.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EORA_1      00:00:00      00:00:02    
EXTRACT     RUNNING     PORA_1      00:00:00      00:00:00    


GGSCI (localhost.localdomain) 2> stop extract eora_1

Sending STOP request to EXTRACT EORA_1 ...
Request processed.


GGSCI (localhost.localdomain) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     EORA_1      00:00:00      00:00:03    
EXTRACT     RUNNING     PORA_1      00:00:00      00:00:06    


GGSCI (localhost.localdomain) 4> stop extract pora_1

Sending STOP request to EXTRACT PORA_1 ...
Request processed.


GGSCI (localhost.localdomain) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     EORA_1      00:00:00      00:00:27    
EXTRACT     STOPPED     PORA_1      00:00:00      00:00:02    


GGSCI (localhost.localdomain) 6> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

备库：
GGSCI (localhost.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     RORA_1      00:00:00      00:00:05    


GGSCI (localhost.localdomain) 2> stop replicat rora_1

Sending STOP request to REPLICAT RORA_1 ...
Request processed.


GGSCI (localhost.localdomain) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     RORA_1      00:00:00      00:00:02    


GGSCI (localhost.localdomain) 4> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.