Oracle GoldenGate快速入门教程：基本概念和配置

最新推荐文章于 2021-12-14 11:44:54 发布

茄肥猫

最新推荐文章于 2021-12-14 11:44:54 发布

阅读量2.3k

点赞数

ORACLE 文档同时被 2 个专栏收录

224 篇文章 1 订阅

订阅专栏

LINUX

125 篇文章 0 订阅

订阅专栏

一 GoldenGate简介

Oracle Golden Gate软件是一种基于日志的结构化数据复制备份软件，它通过解析源数据库在线日志或归档日志获得数据的增量变化，再将这些变化应用到目标数据库，从而实现源数据库与目标数据库同步。Oracle Golden Gate可以在异构的IT基础结构（包括几乎所有常用操作系统平台和数据库平台）之间实现大量数据亚秒一级的实时复制,从而在可以在应急系统、在线报表、实时数据仓库供应、交易跟踪、数据同步、集中/分发、容灾、数据库升级和移植、双业务中心等多个场景下应用。同时，Oracle Golden Gate 可以实现一对一、广播(一对多)、聚合(多对一)、双向、点对点、级联等多种灵活的拓扑结构。

RAC环境下管理OGG-HA http://www.linuxidc.com/Linux/2012-07/65796.htm

RAC环境下配置OGG同步 http://www.linuxidc.com/Linux/2012-07/65794.htm

GoldenGate单向表DML同步 http://www.linuxidc.com/Linux/2013-04/82942.htm

Oracle GoldenGate 系列：Extract 进程的恢复原理 http://www.linuxidc.com/Linux/2013-04/82563.htm

Oracle GoldenGate安装配置 http://www.linuxidc.com/Linux/2013-02/79455.htm

Oracle goldengate的OGG-01004 OGG-1296错误 http://www.linuxidc.com/Linux/2011-08/40951.htm

二、技术结构

和传统的逻辑复制一样，Oracle GoldenGate 实现原理是通过抽取源端的redo log或者archive log，然后通过TCP/IP投递到目标端，最后解析还原应用到目标端，使目标端实现同源端数同步。以下是OracleGoldenGate的技术架。

三、OGG进程

1、Manager进程

Manager进程是GoldenGate的控制进程，运行在源端和目标端上。它主要作用有以下几个方面：启动、监控、重启Goldengate的其他进程, 报告错误及事件，分配数据存储空间，发布阀值报告等。

2、Extract进程

Extract运行在数据库源端，负责从源端数据表或者日志中捕获数据。

3、Pump进程

pump进程运行在数据库源端。

其作用是如果源端使用了本地的trail文件，那么pump 进程就会把trail以数据块的形式通过TCP/IP协议发送到目标端，这通常也是推荐的方式。pump进程本质是extract进程的一种特殊形式，如果不使用trails文件，那么就是extract进程在抽取完数据以后，直接投递到目标端。

4.Trail文件

为了更有效、更安全的把数据库事务信息从源端投递到目标端。GoldenGate引进 trail文件的概念。前面提到extract抽取完数据以后Goldengate会将抽取的事务信息转化为一种GoldenGate专有格式的文件。然后pump负责把源端的trail文件投递到目标端，所以源、目标两端都会存在这种文件。trail文件存在的目的旨在防止单点故障，将事务信息持久化，并且使用checkpoint机制来记录其读写位置，如果故障发生，则数据可以根据 checkpoint记录的位置来重传。

5.Replicat进程

Replicat进程，通常我们也把它叫做应用进程。运行在目标端，是数据传递的最后一站，负责读取目标端trail文件中的内容，并将其解析为DML或DDL语句，然后应用到目标数据库中。

6.GGSCI

GGSCI是GoldenGate Software Command Interface 的缩写，

它提供了十分丰富的命令来对Goldengate进行各种操作，如创建、修改、监控GoldenGate进程等等。

三、应用的拓展结构、支持平台和数据库

一、Oracle OGG下载：

http://www.oracle.com/technetwork/cn/middleware/goldengate/downloads/index.html

Oracle GoldenGate快速入门教程：基本概念和配置

二：OGG安装（RedHat EL6)

系统架构：

Oracle GoldenGate快速入门教程：基本概念和配置

Test Environment：

Item	Source System	Target System
Platform	RedHat EL6	RedHat EL6
HostName	ogg	rh6
DataBase Version	Oracle 11.2.0.1.0	Oracle 11.2.0.1.0
Character Set	zhs16gbk	zhs16gbk
ORACLE_SID	prod	prod
Listener	linstener/1521	linstener/1521
GoldenGate User	ogg	ogg

1、检测安装环境

[oracle@rh6 ogg]$ uname -a

Linux rh6.cuug.net 2.6.32-71.el6.i686 #1 SMP Wed Sep 1 01:26:34 EDT 2010 i686 i686 i386 GNU/Linux

[oracle@rh6 ogg]$ arch

i686

2、安装OGG软件

[oracle@rh6 ogg]$ mkdir -p /u01/ogg

[oracle@rh6 ogg]$ ls -l

total 85912

-rw-r----- 1 oracle oinstall 87966150 Sep 10 11:33 ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

注意：

下载的OGG 11g版本有32位和64位，如果在操作系统为32位，安装64位的OGG，将出现以下错误

“不可执行的二进制文件”

[oracle@rh6 ogg]$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

Archive: ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar

inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf

inflating: Oracle GoldenGate 11.2.1.0.1 README.txt

inflating: Oracle GoldenGate 11.2.1.0.1 README.doc

[oracle@rh6 ogg]$ ls -l

total 290704

-rw-rw-r-- 1 oracle oinstall 209367040 Apr 23 2012 fbo_ggs_Linux_x86_ora11g_32bit.tar

-rw-r----- 1 oracle oinstall 87966150 Sep 10 11:33 ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

-rwxrwxrwx 1 oracle oinstall 220546 May 2 2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf

-rwxrwxrwx 1 oracle oinstall 93696 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.doc

-rwxrwxrwx 1 oracle oinstall 24390 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.txt

[oracle@rh6 ogg]$ tar xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

查看安装目录：

[oracle@rh6 ogg]$ ls -l

total 474820

-rw-r----- 1 oracle oinstall 426 Oct 15 2010 bcpfmt.tpl

-rw-r----- 1 oracle oinstall 1725 Oct 15 2010 bcrypt.txt

drwxr-x--- 2 oracle oinstall 4096 Apr 23 2012 cfg

-rw-r----- 1 oracle oinstall 1285 Feb 29 2012 chkpt_ora_create.sql

-rwxr-x--- 1 oracle oinstall 5307711 Apr 23 2012 cobgen

-rwxr-x--- 1 oracle oinstall 5054612 Apr 23 2012 convchk

-rw-r----- 1 oracle oinstall 159 Oct 15 2010 db2cntl.tpl

-rw-r----- 1 oracle oinstall 1059 Oct 15 2010 ddl_cleartrace.sql

-rwxr-x--- 1 oracle oinstall 5584197 Apr 23 2012 ddlcob

-rw-r----- 1 oracle oinstall 4189 Oct 15 2010 ddl_ddl2file.sql

-rw-r----- 1 oracle oinstall 746 Oct 15 2010 ddl_disable.sql

-rw-r----- 1 oracle oinstall 692 Oct 15 2010 ddl_enable.sql

-rw-r----- 1 oracle oinstall 3518 Oct 14 2011 ddl_filter.sql

-rw-r----- 1 oracle oinstall 388 Oct 15 2010 ddl_nopurgeRecyclebin.sql

-rw-r----- 1 oracle oinstall 13689 Dec 15 2011 ddl_ora10.sql

-rw-r----- 1 oracle oinstall 3167 Aug 29 2011 ddl_ora10upCommon.sql

-rw-r----- 1 oracle oinstall 14358 Dec 15 2011 ddl_ora11.sql

-rw-r----- 1 oracle oinstall 14051 Dec 15 2011 ddl_ora9.sql

-rw-r----- 1 oracle oinstall 1026 Oct 15 2010 ddl_pin.sql

-rw-r----- 1 oracle oinstall 1227 Oct 15 2010 ddl_purgeRecyclebin.sql

-rw-r----- 1 oracle oinstall 4138 Aug 9 2011 ddl_remove.sql

-rw-r----- 1 oracle oinstall 425 Oct 15 2010 ddl_session1.sql

-rw-r----- 1 oracle oinstall 1053 Oct 15 2010 ddl_session.sql

-rw-r----- 1 oracle oinstall 296951 Apr 3 2012 ddl_setup.sql

-rw-r----- 1 oracle oinstall 10506 Feb 27 2012 ddl_status.sql

-rw-r----- 1 oracle oinstall 2506 Oct 15 2010 ddl_staymetadata_off.sql

-rw-r----- 1 oracle oinstall 2501 Oct 15 2010 ddl_staymetadata_on.sql

-rw-r----- 1 oracle oinstall 2955 Oct 15 2010 ddl_tracelevel.sql

-rw-r----- 1 oracle oinstall 2721 Dec 28 2010 ddl_trace_off.sql

-rw-r----- 1 oracle oinstall 2862 Oct 15 2010 ddl_trace_on.sql

-rwxr-x--- 1 oracle oinstall 8317872 Apr 23 2012 defgen

-rw-r----- 1 oracle oinstall 1217 Oct 15 2010 demo_more_ora_create.sql

-rw-r----- 1 oracle oinstall 967 Oct 15 2010 demo_more_ora_insert.sql

-rw-r----- 1 oracle oinstall 883 Oct 15 2010 demo_ora_create.sql

-rw-r----- 1 oracle oinstall 821 Oct 15 2010 demo_ora_insert.sql

-rw-r----- 1 oracle oinstall 4015 Oct 15 2010 demo_ora_lob_create.sql

-rw-r----- 1 oracle oinstall 2275 Oct 15 2010 demo_ora_misc.sql

-rw-r----- 1 oracle oinstall 1269 Oct 15 2010 demo_ora_pk_befores_create.sql

-rw-r----- 1 oracle oinstall 1227 Oct 15 2010 demo_ora_pk_befores_insert.sql

-rw-r----- 1 oracle oinstall 2520 Oct 15 2010 demo_ora_pk_befores_updates.sql

drwxr-x--- 2 oracle oinstall 4096 Apr 23 2012 dirjar

drwxr-x--- 2 oracle oinstall 4096 Apr 23 2012 dirprm

-rwxr-x--- 1 oracle oinstall 5901919 Apr 23 2012 emsclnt

-rwxr-x--- 1 oracle oinstall 34210648 Apr 23 2012 extract

-rw-rw-r-- 1 oracle oinstall 209367040 Apr 23 2012 fbo_ggs_Linux_x86_ora11g_32bit.tar

-rw-r----- 1 oracle oinstall 1968 Oct 15 2010 freeBSD.txt

-rwxr-x--- 1 oracle oinstall 5825921 Apr 23 2012 ggcmd

-rw-r----- 1 oracle oinstall 1257280 Apr 23 2012 ggMessage.dat

-rwxr-x--- 1 oracle oinstall 12266773 Apr 23 2012 ggsci

-rw-r----- 1 oracle oinstall 169753 Feb 1 2012 help.txt

-rwxr-x--- 1 oracle oinstall 406 Apr 23 2012 jagent.sh

-rwxr-x--- 1 oracle oinstall 316003 Apr 23 2012 keygen

-rwxr-x--- 1 oracle oinstall 83229 Apr 19 2012 libantlr3c.so

-rwxr-x--- 1 oracle oinstall 1740066 Apr 19 2012 libdb-5.2.so

-rwxr-x--- 1 oracle oinstall 6569009 Apr 23 2012 libgglog.so

-rwxr-x--- 1 oracle oinstall 1893448 Apr 23 2012 libggrepo.so

-rwxr-x--- 1 oracle oinstall 15581116 Apr 19 2012 libicudata.so.38

-rwxr-x--- 1 oracle oinstall 4508019 Apr 19 2012 libicui18n.so.38

-rwxr-x--- 1 oracle oinstall 3349821 Apr 19 2012 libicuuc.so.38

-rwxr-x--- 1 oracle oinstall 4803823 Apr 19 2012 libxerces-c.so.28

-rw-r----- 1 oracle oinstall 1668 Oct 15 2010 libxml2.txt

-rwxr-x--- 1 oracle oinstall 6441773 Apr 23 2012 logdump

-rw-r----- 1 oracle oinstall 2278 Aug 9 2011 marker_remove.sql

-rw-r----- 1 oracle oinstall 3824 Aug 9 2011 marker_setup.sql

-rw-r----- 1 oracle oinstall 1715 Oct 15 2010 marker_status.sql

-rwxr-x--- 1 oracle oinstall 9250398 Apr 23 2012 mgr

-rw-r----- 1 oracle oinstall 213535 Jan 19 2012 notices.txt

-rw-r----- 1 oracle oinstall 87966150 Sep 10 11:33 ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip

-rwxr-x--- 1 oracle oinstall 4600385 Apr 23 2012 oggerr

-rwxrwxrwx 1 oracle oinstall 220546 May 2 2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf

-rwxrwxrwx 1 oracle oinstall 93696 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.doc

-rwxrwxrwx 1 oracle oinstall 24390 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.txt

-rw-r----- 1 oracle oinstall 5364 Mar 22 2012 params.sql

-rw-r----- 1 oracle oinstall 1272 Dec 28 2010 prvtclkm.plb

-rwxr-x--- 1 oracle oinstall 1015 Apr 23 2012 pw_agent_util.sh

-rw-r----- 1 oracle oinstall 2393 Apr 8 2011 remove_seq.sql

-rwxr-x--- 1 oracle oinstall 27303957 Apr 23 2012 replicat

-rwxr-x--- 1 oracle oinstall 4622859 Apr 23 2012 retrace

-rwxr-x--- 1 oracle oinstall 654448 Apr 23 2012 reverse

-rw-r----- 1 oracle oinstall 4375 Apr 8 2011 role_setup.sql

-rw-r----- 1 oracle oinstall 34564 Nov 16 2011 sequence.sql

-rwxr-x--- 1 oracle oinstall 11984721 Apr 23 2012 server

-rw-r----- 1 oracle oinstall 248 Oct 15 2010 sqlldr.tpl

-rw-r----- 1 oracle oinstall 759 Oct 15 2010 tcperrs

-rw-r----- 1 oracle oinstall 16763 Mar 3 2012 ucharset.h

-rw-r----- 1 oracle oinstall 6747 Aug 25 2011 ulg.sql

drwxr-x--- 7 oracle oinstall 4096 Apr 23 2012 UserExitExamples

-rw-r----- 1 oracle oinstall 28041 Dec 30 2011 usrdecs.h

-rw-r----- 1 oracle oinstall 1476 Oct 15 2010 zlib.txt

3、执行ggsci脚本，建立相关目录

[oracle@rh6 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

GGSCI (ogg) 2> 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 (ogg) 3> create subdirs

Creating subdirectories under current directory /u01/ogg

Parameter files /u01/ogg/dirprm: already exists

Report files /u01/ogg/dirrpt: created

Checkpoint files /u01/ogg/dirchk: created

Process status files /u01/ogg/dirpcs: created

SQL script files /u01/ogg/dirsql: created

Database definitions files /u01/ogg/dirdef: created

Extract data files /u01/ogg/dirdat: created

Temporary files /u01/ogg/dirtmp: created

Stdout files /u01/ogg/dirout: created

GGSCI>create subdirs

--不需要指定路径，默认在当前目录下（所以进入ggsci的时候，一定要在gg的当前目录）

新建出来的目录有：

name Purpose

--dirchk Checkpoint files存放检查点（Checkpoint）文件 (和Oracle的checkpoint含义不同，如oralce的一个实例失败，从最近一次的checkpoint开始recorver, oracle的一次 checkpoint是让dbwrt进程把被修改的数据从数据缓冲区中写入数据文件。而OGG的checkpoint是OGG捕捉了很多事务日志，哪些被传输了，哪些未被传输。每隔一段时间，执行一次checkpoint,之前的所有数据都要传送过去。如果soure or target db断电了，那下次启动的时候，就要支持断点续传，那那个断点位置就是最后一次OGG执行checkpoint的位置)

--dirdat GoldenGate trails, 存放Trail与Extract文件

--dirdef Data Definition files,过DEFGEN工具生成的源或目标的数据定义文件

--dirprm Parameter files, 存放参数文件

--dirpcs Process status files, 存放进程状态文件

--dirrpt Report files, 存放进程报告文件

--dirsql SQL script files,存放SQL脚本文件

--dirtmp Temporary files,当事务所需要的内存超过已分配内存时，缺省存储于此

@至此，OGG在linux下安装基本完成，后面将进入OGG的基本配置

系统架构：

Oracle GoldenGate快速入门教程：基本概念和配置

OGG基本配置（单向传输）

1、数据库配置（Source DB)

建立Tablespace：

11:58:56 SYS@ prod >create tablespace ogg_tbs

11:59:12 2 datafile '/u01/app/Oracle/oradata/prod/oggtbs1.dbf' size 100m ;

建立ogg用户：

12:01:17 SYS@ prod >create user ogg identified by ogg default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;

User created.

用户授权：

12:01:39 SYS@ prod >grant connect,resource to ogg;

Grant succeeded.

12:01:55 SYS@ prod >grant create session,alter session to ogg;

Grant succeeded.

12:02:18 SYS@ prod >grant select any dictionary,select any table to ogg;

Grant succeeded.

12:02:42 SYS@ prod >grant alter any table to ogg;

Grant succeeded.

12:02:53 SYS@ prod >grant flashback any table to ogg;

Grant succeeded.

12:03:03 SYS@ prod >grant execute on dbms_flashback to ogg;

Grant succeeded.

在Database上启用附加日志功能：

12:19:26 SYS@ prod >select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

12:20:53 SYS@ prod >alter database add supplemental log data;

Database altered.

数据库应为归档模式：

12:21:10 SYS@ prod >archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /dsk4/arch_prod

Oldest online log sequence 78

Next log sequence to archive 80

Current log sequence 80

13:22:45 SYS@ prod >select force_logging from v$database;

FOR

---

13:24:22 SYS@ prod >alter database force logging;

Database altered.

13:24:30 SYS@ prod >select force_logging from v$database;

FOR

---

YES

建立用于传输测试的Table：

12:12:26 SCOTT@ prod >create table emp_ogg as select * from emp;

Table created.

12:12:40 SCOTT@ prod >alter table emp_ogg add constraint pk_ogg primary

key(empno);

注意：Oracle建议给所有要复制的表建立主键或唯一键

2、数据库配置（Target DB)

建立Tablespace：

12:07:50 SYS@ prod >create tablespace ogg_tbs

12:08:09 2 datafile '/u01/app/oracle/oradata/prod/oggtbs1.dbf' size 100m;

建立ogg用户：

12:08:47 SYS@ prod >create user ogg identified by ogg default tablespace ogg_tbs temporary tablespace temp quota unlimited on ogg_tbs;

User created.

用户授权：

12:09:44 SYS@ prod >grant connect,resource to ogg;

Grant succeeded.

12:10:06 SYS@ prod >grant create session ,alter session to ogg;

Grant succeeded.

12:10:20 SYS@ prod >grant select any table,select any dictionary to ogg;

Grant succeeded.

12:10:45 SYS@ prod >grant create table to ogg;

创建测试Table：

12:13:47 SCOTT@ prod >create table emp_ogg as select * from emp;

Table created.

12:14:04 SCOTT@ prod >grant all on emp_ogg to ogg；

Grant succeeded.

3、OGG配置（source）

配置MGR:

[oracle@ogg ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

添加用于capture data change的table

GGSCI (ogg) 2> dblogin userid ogg,password ogg;

Successfully logged into database.

GGSCI (ogg) 3> add trandata scott.emp_ogg;

ERROR: No viable tables matched specification.

goldengate add trandata 提示找不到表 ERROR: No viable tables matched specification

goldengate对符号比较敏感，在add trandata时不要用分号

GGSCI (ogg) 4> add trandata scott.emp_ogg

2014-09-10 14:00:25 WARNING OGG-00869 No unique key is defined for table 'EMP_OGG'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

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

注意：提示table：emp_ogg未建立primary key，建立primary key后重新add

GGSCI (ogg) 8> delete trandata scott.emp_ogg

Logging of supplemental redo log data disabled for table SCOTT.EMP_OGG.

GGSCI (ogg) 9> add trandata scott.emp_ogg

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

查看在table上是否启用了supplemental log

GGSCI (ogg) 11> info trandata scott.emp_ogg

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

Columns supplementally logged for table SCOTT.EMP_OGG: EMPNO.

GGSCI (ogg) 13> edit params mgr

port 7809

dynamicportlist 7800-8000

autorestart extract *,waitminutes 2,resetminutes 5

编辑mgr参数文件，并写入以上信息！

GGSCI (ogg) 14> start mgr

Manager started.

GGSCI (ogg) 16> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

Oracle GoldenGate快速入门教程：基本概念和配置

配置Extract：

添加extract服务，并启动extract服务：

GGSCI (ogg) 11> add extract eini_1,sourceistable

EXTRACT added.

校验Extract process

GGSCI (ogg) 14> info extract *,tasks

EXTRACT EINI_1 Initialized 2014-09-10 14:28 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

编辑extract参数文件

GGSCI (ogg) 18>EDIT PARAMS EINI_1

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

RMTHOST 192.168.8.249,MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.EMP_OGG;

GGSCI (ogg) 15> start eini_1

Sending START request to MANAGER ...

EXTRACT EINI_1 starting

GGSCI (ogg) 16> info eini_1

EXTRACT EINI_1 Initialized 2014-09-10 14:28 Status RUNNING

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

GGSCI (ogg) 17> view report eini_1

2014-09-10 14:30:34 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************

Oracle GoldenGate Capture for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:20:38

Starting at 2014-09-10 14:30:34

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Wed Sep 1 01:26:34 EDT 2010, Release 2.6.32-71.el6.i686

Node: ogg

Machine: i686

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 6159

Description:

***********************************************************************

** Running with the following parameters **

***********************************************************************

2014-09-10 14:30:34 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg,PASSWORD ***

RMTHOST 192.168.8.249,MGRPORT 7809

RMTTASK REPLICAT,GROUP RINI_1

TABLE scott.EMP_OGG;

Using the following key columns for source table SCOTT.EMP_OGG: EMPNO.

2014-09-10 14:30:36 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u01/ogg/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 2G

CACHEPAGEOUTSIZE (normal): 4M

PROCESS VM AVAIL FROM OS (min): 2.75G

CACHESIZEMAX (strict force to disk): 2.47G

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Database Language and Character Set:

NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"

NLS_LANGUAGE = "AMERICAN"

NLS_TERRITORY = "AMERICA"

NLS_CHARACTERSET = "ZHS16GBK"

2014-09-10 14:30:36 WARNING OGG-01223 TCP/IP error 111 (Connection refus

Source Context :

SourceModule : [er.idlx]

SourceID : [/net/ap1109nap-vlan900/vol/ifarm_ports/ifarm_views/aime_oggcore_210874/oggcore/OpenSys/src/app/er/idlx.c]

SourceFunction : [idlx_tcp_error]

SourceLine : [1414]

ThreadBacktrace : [10] elements

: [/u01/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x1f9166]]

: [/u01/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2c5) [0x1f5125]]

: [/u01/ogg/libgglog.so(_MSG_ERR_TCP_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x36) [0

x1d755c]]

: [/u01/ogg/extract() [0x8148d0c]]

: [/u01/ogg/extract(IDLX_tcp_send_data+0xf4) [0x8149ae0]]

: [/u01/ogg/extract(IDLX_send_session_begin_cmd+0xa6) [0x8149e12]]

: [/u01/ogg/extract(process_source_files()+0x584) [0x819662e]]

: [/u01/ogg/extract(main+0x5fc) [0x819820c]]

: [/lib/libc.so.6(__libc_start_main+0xe6) [0x8aecc6]]

: [/u01/ogg/extract(__gxx_personality_v0+0x3ad) [0x81144e1]]

2014-09-10 14:32:35 ERROR OGG-01224 TCP/IP error 111 (Connection refused); retries exceeded.

2014-09-10 14:32:35 ERROR OGG-01668 PROCESS ABENDING.

注意：出现以上tcp/ip错误，是因为目标端未配置mgr，无法通讯！

源端配置extract进程，用于数据同步：

GGSCI (ogg) 25> edit params eora_1

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg,PASSWORD ogg EXTTRAIL

./dirdat/aa

TABLE scott.emp_ogg;

添加extract primary group

GGSCI (ogg) 27> add extract eora_1,tranlog,begin now

EXTRACT added.

定义goldengate local trail

注意：‘aa’ is prefix for local trail file

GGSCI (ogg) 29> add exttrail ./dirdat/aa,extract eora_1,megabytes 5

EXTTRAIL added.

GGSCI (ogg) 30> start extract eora_1

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

GGSCI (ogg) 31> info extract eora_1

EXTRACT EORA_1 Initialized 2014-09-10 14:47 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:02:06 ago)

Log Read Checkpoint Oracle Redo Logs

2014-09-10 14:47:22 Seqno 0, RBA 0

SCN 0.0 (0)

在源端配置pump进程，用于数据同步：

GGSCI (ogg) 41> edit params pora_1

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST 192.168.8.249,MGRPORT 7809

RMTTRAIL

./dirdat/pa

TABLE scott.emp_ogg;

添加pump extract group

GGSCI (ogg) 35> add extract pora_1,exttrailsource ./dirdat/pa

EXTRACT added.

添加remote trail file在source system

ggsci> ADD RMTTRAIL ./dirdat/pa,EXTRACT PORA_1, MEGABYTES 5

RMTTRAIL added.

GGSCI (ogg) 36> info extract pora_1

EXTRACT PORA_1 Initialized 2014-09-10 15:01 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:22 ago)

Log Read Checkpoint File ./dirdat/pa000000

First Record RBA 0

GGSCI (ogg) 42> start extract pora_1

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

GGSCI (ogg) 43> info extract pora_1

EXTRACT PORA_1 Initialized 2014-09-10 15:01 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:03:35 ago)

Log Read Checkpoint File ./dirdat/pa000000

First Record RBA 0

以上eora_1和pora_1服务都处于stop状态，正常应为running，通过以下方式进行纠正；

解决方法：

GGSCI (rh6.cuug.net) 7> delete extract eora_1,tranlog,begin now

2014-09-10 16:16:15 WARNING OGG-01753 Cannot unregister EXTRACT EORA_1 from database because no database login was provided. You can manually unregister this group later with the UNREGISTER EXTRACT command with LOGRETENTION. Issue DBLOGIN first.

Deleted EXTRACT EORA_1.

GGSCI (rh6.cuug.net) 8> dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (rh6.cuug.net) 9> delete extract eora_1,tranlog,begin now

ERROR: EXTRACT EORA_1 does not exist.

GGSCI (rh6.cuug.net) 10> add extract eora_1,tranlog,begin now

EXTRACT added.

GGSCI (rh6.cuug.net) 11> add exttrail ./dirdat/aa ,extract eora_1,megabytes 5

EXTTRAIL added.

GGSCI (rh6.cuug.net) 12> start extract eora_1

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

GGSCI (rh6.cuug.net) 13> info extract eora_1

EXTRACT EORA_1 Last Started 2014-09-10 16:17 Status RUNNING

Checkpoint Lag 00:00:54 (updated 00:00:07 ago)

Log Read Checkpoint Oracle Redo Logs

2014-09-10 16:16:51 Seqno 80, RBA 42201104

SCN 0.0 (0)

GGSCI (rh6.cuug.net) 27> edit params pora_1

RMTTRAIL

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST 192.168.8.249,MGRPORT 7809

RMTTRAIL ./dirdat/pa

TABLE scott.emp_ogg;

GGSCI (rh6.cuug.net) 28> start extract pora_1

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

GGSCI (rh6.cuug.net) 29> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

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

EXTRACT RUNNING PORA_1 00:00:00 00:03:18

Oracle GoldenGate快速入门教程：基本概念和配置

@至此，Source端，Extract、Pump服务配置基本完毕！

4、OGG配置（Target）

配置MGR

GGSCI (rh6.cuug.net) 4> edit params mgr

port 7809

dynamicportlist 7800-8000

autorestart extract *,waitminutes 2,resetminutes 5

GGSCI (rh6.cuug.net) 5> start mgr

Manager started.

GGSCI (rh6.cuug.net) 6> info mgr

Manager is running (IP port rh6.cuug.net.7809).

配置Replicate服务

[oracle@rh6 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

GGSCI (rh6.cuug.net) 1> add replicat rini_1,specialrun

REPLICAT added.

GGSCI (rh6.cuug.net) 2> info replicat *,tasks

REPLICAT RINI_1 Initialized 2014-09-10 14:33 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:13 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

GGSCI (rh6.cuug.net) 3> edit params rini_1

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg,PASSWOR ogg

DISCARDFILE ./dirrpt/RINIaa.dsc,PURGE

MAP scott.emp_ogg,TARGET scott.emp_ogg;

注意：在源端启动eini_1 process，目标端rini_1 process将会被自动启动

添加replicate checkpoint table

GGSCI (rh6.cuug.net) 1> edit params ./GLOBALS

CHECKPOINTTABLE ogg.oggchkpt

GGSCI (rh6.cuug.net) 2> exit

必须退出ggsci，checkpoint table 才会生效

[oracle@rh6 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

GGSCI (rh6.cuug.net) 1> dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (rh6.cuug.net) 2> add checkpointtable

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

Successfully created checkpoint table ogg.oggchkpt.

添加replicate group

GGSCI (rh6.cuug.net) 4> add replicat rora_1,exttrail ./dirdat/pa

REPLICAT added.

编辑replicate parameterfile

GGSCI (rh6.cuug.net) 5> edit param rora_1

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg,PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC,PURGE

MAP scott.oem_ogg,TARGET scott.emp_ogg;

启动replicate process

GGSCI (rh6.cuug.net) 7> start replicat rora_1

Sending START request to MANAGER ...

REPLICAT RORA_1 starting

GGSCI (rh6.cuug.net) 10> info replicat rora_1

REPLICAT RORA_1 Last Started 2014-09-10 15:53 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:05 ago)

Log Read Checkpoint File ./dirdat/pa000000

First Record RBA 0

GGSCI (rh6.cuug.net) 9> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING RORA_1 00:00:00 00:00:00

5、同步测试

source：

16:27:33 SCOTT@ prod >select * from emp_ogg;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

16:27:41 SCOTT@ prod >update emp_ogg set deptno=40 where empno=7788;

1 row updated.

16:28:06 SCOTT@ prod >commit;

Commit complete.

target：

16:26:31 SCOTT@ prod >truncate table emp_ogg;

Table truncated.

16:26:44 SCOTT@ prod >select * from emp_ogg;

no rows selected

16:28:23 SCOTT@ prod >/

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 40

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

@至此，目标端同源端的数据同步成功，OGG的单向传输基本配置完成！

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

茄肥猫

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
Oracle GoldenGate快速入门教程：基本概念和配置

一 GoldenGate简介Oracle Golden Gate软件是一种基于日志的结构化数据复制备份软件，它通过解析源数据库在线日志或归档日志获得数据的增量变化，再将这些变化应用到目标数据库，从而实现源数据库与目标数据库同步。Oracle Golden Gate可以在异构的IT基础结构（包括几乎所有常用操作系统平台和数据库平台）之间实现大量数据亚秒一级的实时复制,从而在可以在
复制链接

扫一扫