Oracle Goldengate Windows平台Oracle-Oracle单向复制

最新推荐文章于 2023-03-05 01:52:11 发布

weixin_30332241

最新推荐文章于 2023-03-05 01:52:11 发布

阅读量148

点赞数

文章标签：操作系统数据库 shell

原文链接：http://www.cnblogs.com/Roobbin/p/9875813.html

版权

实验目的

Goldengate最基本的从源端一对一的单向复制，注意其中Goldengate版本取决于Oracle的版本。单向复制一般适用于保持目标数据库的实时更新，且目标数据库用来检索，如报表或者分析使用。

Source DB

操作系统：Windows 10 64
Oracle版本:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
GoldenGate版本：Version 11.2.1.0.1

Target DB

操作系统：Windows 7 64
Oracle版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
GoldenGate：Version 11.2.1.0.1

配置GoldenGate软件

分两种，一种是免安装，一种是通过exe文件进行安装；这里我使用的是免安装，把压缩包解压到c:\ggs目录即可，目标端和源端路径一致。

1.设置环境变量

我的电脑右键属性，选择高级系统设置，高级里面找到环境变量，添加系统变量

完成后，进入DOS命令窗口(win+r)输入：echo %ORACLE_HOME%，echo %ORACLE_SID%

Microsoft Windows [版本 10.0.16299.726]
(c) 2017 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>echo %ORACLE_HOME%
D:\app\Administrator\product\11.2.0\dbhome_1

C:\Users\Administrator>echo %ORACLE_SID%
orcl

C:\Users\Administrator>

2.开始配置GoldenGate

将GoldenGate解压至c:\ggs，进入ggs目录，运行ggsci，随后创建子目录

Microsoft Windows [版本 10.0.16299.726]
(c) 2017 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>cd \ggs

C:\ggs>ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Windows x64 (optimized), Oracle 11g on Apr 23 2012 04:55:02

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



GGSCI (DESKTOP-8NP7VO0) 1> Create subdirs

Creating subdirectories under current directory C:\ggs

Parameter files                C:\ggs\dirprm: already exists
Report files                   C:\ggs\dirrpt: created
Checkpoint files               C:\ggs\dirchk: created
Process status files           C:\ggs\dirpcs: created
SQL script files               C:\ggs\dirsql: created
Database definitions files     C:\ggs\dirdef: created
Extract data files             C:\ggs\dirdat: created
Temporary files                C:\ggs\dirtmp: created
Stdout files                   C:\ggs\dirout: created

主目录介绍

dirchk：存放检查点（Checkpoint）文件
dirdat：存放Trail与Extract文件，以后详述
dirdef：通过DEFGEN工具生成的源或目标的数据定义文件
dirpcs：存放进程状态文件
dirprm：存放参数文件
dirrpt：存放进程报告文件
dirsql：存放SQL脚本文件
dirtmp：当事务所需要的内存超过已分配内存时，缺省存储于此。

3.添加manager进程到Windows系统服务

edit params ./GLOBALS命令（注意./GLOBALS为大写），进入编辑文件，输入下面内容：

MGRSERVNAME GGMGR

保存退出后，用install addservice命令添加服务，如下所示

GGSCI (DESKTOP-8NP7VO0) 2> edit params ./GLOBALS


GGSCI (DESKTOP-8NP7VO0) 3> exit

C:\ggs>install addservice

Service 'GGMGR' created.


Install program terminated normally.

4.命令解释

GGSCI (DESKTOP-8NP7VO0) 1> help


GGSCI Command Summary:

Object:          Command:
SUBDIRS          CREATE
ER               INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP
EXTRACT          ADD, ALTER, CLEANUP, DELETE, INFO, KILL,
                 LAG, REGISTER, SEND, START, STATS, STATUS, STOP
                 UNREGISTER
EXTTRAIL         ADD, ALTER, DELETE, INFO
GGSEVT           VIEW
MANAGER          INFO, SEND, START, STOP, STATUS
MARKER           INFO
PARAMS           EDIT, VIEW
REPLICAT         ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND,
                 START, STATS, STATUS, STOP
REPORT           VIEW
RMTTRAIL         ADD, ALTER, DELETE, INFO
TRACETABLE       ADD, DELETE, INFO
TRANDATA         ADD, DELETE, INFO
SCHEMATRANDATA   ADD, DELETE, INFO
CHECKPOINTTABLE  ADD, DELETE, CLEANUP, INFO

Commands without an object:
(Database)       DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCE
                 MININGDBLOGIN
(DDL)            DUMPDDL
(Miscellaneous)  FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL,
                 SHOW, VERSIONS, ! (note: you must type the word
                 COMMAND after the ! to display the ! help topic.)
                 i.e.: GGSCI (sys1)> help ! command



For help on a specific command, type HELP <command> <object>.

Example: HELP ADD REPLICAT

GGSCI (DESKTOP-8NP7VO0) 2>

配置Oracle数据库

1.进入数据库SYS用户创建GoldenGate管理用户，并授予相应的权限，测试环境直接给DBA即可，生产环境请严格控制

CREATE USER GGS IDENTIFIED BY "ggs" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT DBA TO GGS_SOURCE;

2.在源端打开归档模式，我这里已经配置好了，按照下面的命令执行即可

SQL>archive log list;
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>startup force;

3.查看是否打开数据库级别的补充日志,返回结果为Y即可

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

如果查询结果不是Y，需要打开数据库级别的补充日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

4.增加表级最小日志

AlTER TABLE BJXXJGXT.RYJBXXB ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
AlTER TABLE BJXXJGXT.KSSZPB ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

5.在源端库中关闭回收站，如果是oracle 10.1版本使用alter system set "_recyclebin=off"

SQL> show parameter recyclebin
 
NAME TYPE VALUE
------------- ----------- ------------------------------
recyclebin string on
 
SQL> alter system set recyclebin=off;
 
System altered.

6.在源端添加表级的Trandata，表名可以使用通配符，如add trandata bjxxjgxt.*

GGSCI (DESKTOP-8NP7VO0) 1> dblogin userid ggs_test,password 123456
Successfully logged into database.

GGSCI (DESKTOP-8NP7VO0) 2> add trandata bjxxjgxt.ryjbxxb

Logging of supplemental redo log data is already enabled for table BJXXJGXT.RYJBXXB.

配置goldengate进程组-目标端

在源端：mgr进程，extract（抽取）进程，pump进程

在目标端：mgr进程，replicat（复制）

1.配置源端mgr管理进程

GGSCI (DESKTOP-8NP7VO0) 3> edit params mgr


GGSCI (DESKTOP-8NP7VO0) 4> view params mgr
port 7500  #MGR进程通信端口
dynamicportlist 7501-7505 #Manager进程可以为源端和目标端的动态的指定端口
autorestart extract *,waitminutes 2,retries 5 #autorestart extract表示自动重启Extract进程组,每次尝试的时间间隔为2秒，最多尝试5次，如果没有成功则放弃

启动mgr

GGSCI (DESKTOP-8NP7VO0) 5> start mgr

Starting Manager as service ('GGMGR')...
Service started.


GGSCI (DESKTOP-8NP7VO0) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

2.配置源端Extract抽取进程组

编辑添加extract进程配置文件

GGSCI (DESKTOP-8NP7VO0) 7> edit params eora

添加如下内容

extract eora #表一个名为eora的extract进程
dynamicresolution #GoldenGatex动态解析源端表名
userid ggs_test,password 123456 #ogg专用用户密码
--setenv(ORACLE_SID=ORCL) #设置环境变量
exttrail c:\ggs\dirdat\et #文件目录和标识（类似于et*这样文件)，命名只能是2个字符。
table bjxxjgxt.ryjbxxb; #同步那些表,表名可以用通配符*代替,*代表该用户下所有表

添加extract进程

GGSCI (DESKTOP-8NP7VO0) 8> add extract eora,tranlog,begin now
EXTRACT added.

创建本地trail文件，extract进程组负责写这部分文件，pump进程负责读取它

GGSCI (DESKTOP-8NP7VO0) 9> add exttrail c:\ggs\dirdat\et,extract eora
EXTTRAIL added.

启动eora进程，使用start eora或start ext eora或start extract eora

GGSCI (DESKTOP-8NP7VO0) 10> start eora

Sending START request to MANAGER ('GGMGR') ...
EXTRACT EORA starting

GGSCI (DESKTOP-8NP7VO0) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EORA        00:00:25      00:00:01

3.配置源端pump进程组

编辑添加pump进程配置文件

GGSCI (DESKTOP-8NP7VO0) 12> edit params pump

添加如下内容

extract pump #一个名为pump的extract进程
Dynamicresolution #GoldenGatex动态解析源端表名
userid ggs_test,password 123456 #ogg用户和密码
rmthost 192.168.11.91, mgrport 7500 #指定目标端IP和mgr端口
--Setenv(ORACLE_SID=ORCL) #设置环境变量
rmttrail c:\ggs\dirdat\et #
table bjxxjgxt.ryjbxxb;

添加pump进程

GGSCI (DESKTOP-8NP7VO0) 13> add extract pump,exttrailsource c:\ggs\dirdat\et
EXTRACT added.

添加该rmttrail

GGSCI (DESKTOP-8NP7VO0) 14> add rmttrail c:\ggs\dirdat\et,extract pump
RMTTRAIL added.

启动pump进程，最终结果如下图所示，如有异常请查看进程组日志

GGSCI (DESKTOP-8NP7VO0) 15> start pump

Sending START request to MANAGER ('GGMGR') ...
EXTRACT PUMP starting

GGSCI (DESKTOP-8NP7VO0) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EORA        00:00:00      00:00:03
EXTRACT     RUNNING     PUMP        00:00:00      00:00:24

配置目标端MGR管理进程

1.依据源端的配置，配置目标端mgr进程；在目标机器上编辑GLOBALS文件，添加一行，checkpointtable ggs.checkpoint

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>cd \ggs

C:\ggs>ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Windows x64 (optimized), Oracle 11g on Apr 23 2012 04:55:02

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

GGSCI (Roobbin-PC) 1> create subdirs

Creating subdirectories under current directory C:\ggs

Parameter files                C:\ggs\dirprm: already exists
Report files                   C:\ggs\dirrpt: created
Checkpoint files               C:\ggs\dirchk: created
Process status files           C:\ggs\dirpcs: created
SQL script files               C:\ggs\dirsql: created
Database definitions files     C:\ggs\dirdef: created
Extract data files             C:\ggs\dirdat: created
Temporary files                C:\ggs\dirtmp: created
Stdout files                   C:\ggs\dirout: created

GGSCI (Roobbin-PC) 2> edit params ./GLOBAL

添加如下内容

MGRSERVNAME GGMGR

checkpointtable ggs.checkpoint

添加系统服务

GGSCI (Roobbin-PC) 3> exit

C:\ggs>install addservice

Service 'GGMGR' created.

Install program terminated normally.

增加检测机制

GGSCI (Roobbin-PC) 1> dblogin userid ggs_test,password 123456
Successfully logged into database.

GGSCI (Roobbin-PC) 2> add checkpointtable ggs_test.checkpoint

Successfully created checkpoint table ggs_test.checkpoint.

2.依据源端的配置，配置目标端mgr进程

GGSCI (Roobbin-PC) 3> edit params mgr

添加如下内容

port 7500
dynamicportlist 7501-7505
autostart er *
autorestart extract *,waitminutes 2,retries 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts c:\gg\dirdat\et*,usecheckpoints,minkeepdays 3

3.配置目标端Replicat复制进程组

编辑添加Replicat进程配置文件

GGSCI (Roobbin-PC) 5> edit params repl

添加如下内容

replicat repl
userid ggs_test,password 123456
assumetargetdefs
reperror default,discard
discardfile ./dirrpt/repl.dsc,append,megabytes 50
dynamicresolution
MAP bjxxjgxt.ryjbxxb, target bjxxjgxt.ryjbxxb;

添加replicat进程

GGSCI (Roobbin-PC) 6> add replicat repl,exttrail c:\ggs\dirdat\et,checkpointtable ggs_test.checkpoint
REPLICAT added.

启动mgr，repl

GGSCI (Roobbin-PC) 19> start mgr

Starting Manager as service ('GGMGR')...
Service started.

GGSCI (Roobbin-PC) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPL        00:00:00      00:00:08