OGG For Oracle To PostgreSQL

最新推荐文章于 2023-08-04 09:49:10 发布

DBGuardian

最新推荐文章于 2023-08-04 09:49:10 发布

阅读量1.2k

点赞数

分类专栏： OGG oracle相关

本文链接：https://blog.csdn.net/shipeng1022/article/details/105615448

版权

oracle相关同时被 2 个专栏收录

86 篇文章 0 订阅

订阅专栏

OGG

7 篇文章 0 订阅

订阅专栏

源端：

172.30.78.2 oracle11g

目标端:

172.30.78.95 postresql9.4

一、软件下载

正在上传…重新上传取消

二、数据库配置

2.1、源端（Oracle）

2.1.1、创建 GoldenGate 操作系统用户

专门用ogg用户来维护操作GoldenGate。

useradd ogg

passwd ogg

2.1.2、创建 GoldenGate 安装目录

mkdir -p /data/ogg

chown -R ogg:ogg /data/ogg

2.1.3、安装 GoldenGate

将文件解压到/data/ogg下

unzip fbo_ggs_Linux_x64_ora11g_64bit.tar.zip

tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /data/ogg

2.1.4、配置 GoldenGate环境变量

export GGATE=/data/ogg

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export PATH=$PATH:$GGATE:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GGATE:$LD_LIBRARY_PATH

alias sqlplus='rlwrap sqlplus'

alias ggsci='rlwrap ggsci'

2.1.5、配置Oracle数据库

　　由于 GoldenGate 的原理是根据 Oracle 的日志进行抽取复制。为了保证日志的完整性；需要打开 Oracle 数据库归档；

SQL> alter system set log_archive_dest_1 = 'LOCATION=/app/oracle/oradata/arch' scope = spfile;

SQL> shutdown immediate;

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

# 查看归档模式

SQL> archive log list

# 还需要开启 force log 和 supplemental log

SQL> alter database force logging;

SQL> alter database add SUPPLEMENTAL log data;

# 查询结果

SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

NAME OPEN_MODE FOR SUPPLEME--------- -------------------- --- --------

ORA235 READ WRITE YES YES

2.1.6、oracle创建复制用户ogg

# 建议使用单独的表空间存放 GoldenGate 数据

create tablespace tbs_ogg datafile '/app/oracle/oradata/amrdb/ogg01.dbf' size 512M autoextend on;

# 创建 GoldenGate 用户并赋权

create user ogg identified by ogg default tablespace tbs_ogg;

grant dba to ogg;

# 严谨一些；这样赋权

grant connect, resource TO ogg;

grant select any dictionary, select any table TO ogg;

grant flashback any table TO ogg;

grant execute on dbms_flashback TO ogg;

2.2、目标端（PostgreSQL）

2.2.1、创建 GoldenGate 操作系统用户

　　专门用ogg用户来维护操作GoldenGate。

useradd ogg

passwd ogg

2.2.2、创建 GoldenGate 安装目录

mkdir -p /data/ogg

chown -R ogg:ogg /data/ogg

2.2.3、安装 GoldenGate

unzip 122022_ggs_Linux_x64_PostgreSQL_64bit.zip

tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar -C /data/ogg

2.2.4、配置 GoldenGate环境变量

export GGATE=/data/ogg

export PGHOME=/opt/pgsql10

export PGDATA=/data/postgres/data1

export PATH=$PGHOME/bin:$HOME/bin:$GGATE:$PATH

export LD_LIBRARY_PATH=$PGHOME/lib:$GGATE/lib:$LD_LIBRARY_PATH

export ODBCINI=$GGATE/odbc.ini

alias ggsci='rlwrap ggsci'

2.2.5、创建复制用户ogg

create user ogg superuser identified by ogg;

\c amrdb ogg #切换数据库为amrdb，用户为ogg

create schema ogg;

2.2.6、创建DSN

　　生成odbc.ini文件

[ODBC Data Sources]

GG_Postgres=DataDirect 10 PostgreSQL Wire Protocol

[ODBC]

IANAAppCodePage=106

InstallDir=/data/ogg

[GG_Postgres]

Driver=/data/ogg/lib/GGpsql25.so

Description=DataDirect 10 PostgreSQL Wire Protocol

Database=amrdb

HostName=172.30.78.95

PortNumber=5432

LogonID=ogg

Password=ogg

三、GoldenGate进程配置

3.1、源端（Oracle）

3.1.1、创建 GoldenGate 的工作目录

执行ggsci，登录GoldenGate，只需要执行create subdirs命令就可以了。

GGSCI (oracle221) 1> create subdirs

Creating subdirectories under current directory /data/ogg

Parameter files /data/ogg/dirprm: already exists

Report files /data/ogg/dirrpt: created

Checkpoint files /data/ogg/dirchk: created

Process status files /data/ogg/dirpcs: created

SQL script files /data/ogg/dirsql: created

Database definitions files /data/ogg/dirdef: created

Extract data files /data/ogg/dirdat: created

Temporary files /data/ogg/dirtmp: created

Stdout files /data/ogg/dirout: created

3.1.2、配置 MGR 进程组

　　负责启动GoldenGate进程，以及启动动态进程，管理trail文件，错误信息。运行ggsci程序；输入 “EDIT PARAMS MGR” 编辑参数文件：

GGSCI (oracle221) 2> edit params mgr

PORT 7809

DYNAMICPORTLIST 7800-8000

AUTOSTART ER *

AUTORESTART EXTRACT *,WAITMINUTES 2, RESETMINUTES 5

PURGEOLDEXTRACTS /data/ogg/dirdat/*,USECHECKPOINTS,MINKEEPDAYS 3

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

SYSLOG ERROR,WARN

GGSCI (oracle221) 3> start mgr

Manager started.

GGSCI (oracle221) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

PORT：指定服务监听端口；默认端口为7809
DYNAMICPORTLIST：动态端口：可以制定最大256个可用端口的动态列表，当指定的端口不可用时，管理进程将会从列表中选择一个可用的端口，源端和目标段的Collector、Replicat、GGSCI进程通信也会使用这些端口
AUTORESTART：自动重启参数设置：本处设置表示每2分钟尝试重新启动所有EXTRACT进程；以后5分钟清零。
PURGEOLDEXTRACTS：定期清理trail文件设置：本处设置表示对于超过3天的trail文件进行删除

3.1.3、配置 Extract 进程

　　负责抓取需要传输数据；下面在源端配置抽取进程。

GGSCI (oracle221) 5> edit params EORA_1

EXTRACT EORA_1

SETENV (ORACLE_HOME="/app/oracle/product/11.2.0/dbhome_1")

SETENV (ORACLE_SID="amrdb")

USERID ogg, PASSWORD ogg

DISCARDFILE /app/oggdata/dirrpt/EORA_1.dsc,APPEND,MEGABYTES 1024

EXTTRAIL /app/oggdata/dirdat/aa

TABLE ogg.*;

# 在源端用 add extract 命令创建 extract 组；

GGSCI (oracle221) 6> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW

EXTRACT added.

# 用add exttrail 命令创建本地 trail 文件

GGSCI (oracle221) 7> ADD EXTTRAIL /data/ogg/dirdat/aa, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

# 启动extract进程

GGSCI (oracle221) 8> start extract eora_1

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

GGSCI (oracle221) 9> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:33 00:00:0

3.1.4、配置 Pump 进程

extract进程负责将抓取的数据写入本地trail文件；而需要Pump进程把trail文件传输到目标端。使用 EDIT PARAMS PORA_1 添加配置文件

GGSCI (oracle221) 10> EDIT PARAMS PORA_1

EXTRACT PORA_1

PASSTHRU

RMTHOST 172.30.78.95, MGRPORT 7809

RMTTRAIL /data/ogg/dirdat/pa

TABLE ogg.*;

备注:这里的端口要和目标端一致

# 用 add extract 指定本地 trail 文件,这里一定要指定对本地的路径

#生成本地trail文件持久化，并将其投送到目标端

ADD EXTRACT PORA_1, EXTTRAILSOURCE /app/oggdata/dirdat/aa

# 用 add rmttrail 指定远程 trail 文件

#指定投送到目标端的位置

ADD RMTTRAIL /data/ogg/dirdat/pa, EXTRACT PORA_1, MEGABYTES 5

# 启动Pump 进程

START EXTRACT PORA

3.1.5、生成define文件

　　在异构的同步复制；需要生成define文件。用于数据兼容不同数据库。该文件为表之间的映射关系（该文件很重要）。

# 1. 进入ggsci命令行

GGSCI (oracle221) 16> edit params defgen

defsfile ./dirdef/defgen.def

userid ogg, password ogg

TABLE ogg.*;

GGSCI (oracle221) 17> exit

# 退出ggsci命令；生成文件

#在该路径下，根据defgen.prm生成对应的defgen.def文件

[ogg@dbserver oggdata]$ defgen paramfile ./dirprm/defgen.prm

#生成的文件在当前目录的/dirdef/defgen.def下面

[ogg@dbserver oggdata]$ more ./dirdef/defgen.def

*+- Defgen version 2.0, Encoding UTF-8

* Definitions created/modified 2020-04-19 10:14

* Field descriptions for each column entry:

* 1 Name

* 2 Data Type

* 3 External Length

* 4 Fetch Offset

* 5 Scale

* 6 Level

* 7 Null

* 8 Bump if Odd

* 9 Internal Length

* 10 Binary Length

* 11 Table Length

* 12 Most Significant DT

* 13 Least Significant DT

* 14 High Precision

* 15 Low Precision

* 16 Elementary Item

* 17 Occurs

* 18 Key Column

* 19 Sub Data Type

Database type: ORACLE

Character set ID: UTF-8

National character set ID: UTF-16

Locale: neutral

Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14

Definition for table OGG.T_OGG

Record length: 210

Syskey: 0

Columns: 5

ID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2

V1 64 100 56 0 0 1 0 100 100 0 0 0 0 0 1 0 0 0

V2 192 19 162 0 0 1 0 19 19 19 0 5 0 0 1 0 0 0

V3 0 10 184 0 0 1 0 10 10 0 0 0 0 0 1 0 0 0

V4 134 14 198 2 0 1 0 8 8 8 0 0 0 0 1 0 0 3

End of definition

Definition for table OGG.T_OGG2

Record length: 210

Syskey: 0

Columns: 5

ID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2

V1 64 100 56 0 0 1 0 100 100 0 0 0 0 0 1 0 0 0

V2 192 19 162 0 0 1 0 19 19 19 0 5 0 0 1 0 0 0

V3 0 10 184 0 0 1 0 10 10 0 0 0 0 0 1 0 0 0

V4 134 14 198 2 0 1 0 8 8 8 0 0 0 0 1 0 0 3

End of definition

# 并传送到目标端

scp /app/oggdata/dirdef/defgen.def ogg@172.30.78.95:/data/ogg/dirdef

3.2、目标端（PostgreSQL）

3.2.1、创建GoldenGate的工作目录

create subdirs

3.2.2、配置MGR进程组

# 编辑mgr

edit param mgr

PORT 7809

# 启动mgr

start mgr

3.2.3、添加checkpiont表

为了保证源和目标端在传数据的时候不会重复或者少传，在目标端添加checkpoin表

dblogin sourcedb GG_Postgres userid ogg

add checkpointtable ogg.checkpointtab

3.2.4、配置Replicat 进程

# 配置rora_1进程

GGSCI (lottu02) 1> EDIT PARAMS RORA_1

REPLICAT RORA_1

SOURCEDEFS /data/ogg/dirdef/defgen.def

SETENV(PGCLIENTENCODING = "UTF8" )

SETENV(ODBCINI="/data/ogg/odbc.ini" )

SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

TARGETDB GG_Postgres,userid ogg,password ogg

DISCARDFILE /data/ogg/dirrpt/RORA_aa.rpt, append

MAP ogg.*, TARGET ogg.*;

# 在目标端使用 add replicat 添加 Replicat 进程

add replicat RORA_1,exttrail /data/ogg/dirdat/pa,begin now,checkpointtable ogg.checkpointtab

# 使用 start replicat 启动 Replicat 进程

start REPLICAT RORA_1

四、验证同步

4.1、查看GoldenGate进程状态

# 源端

GGSCI (oracle221) 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:09

# 目标端

GGSCI (lottu02) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

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

4.2、测试同步

五、常用操作

start extract ext_name

启动某个extract进程

view report ext_name

查看某个进程的详细日志,有助于排错

GGSCI (dbserver) 1> view report PORA_1

2020-04-19 10:52:02 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).

2020-04-19 10:52:02 INFO OGG-01052 No recovery is required for target file /data/ogg/dirdat/pa000003, at RBA 0 (file not opened).

2020-04-19 10:52:02 INFO OGG-01478 Output file /data/ogg/dirdat/pa is using format RELEASE 11.2.

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

** Run Time Messages **

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

Opened trail file /app/oggdata/dirdat/aa000013 at 2020-04-19 10:52:02

Wildcard TABLE resolved (entry ogg.*):

TABLE "OGG"."T_OGG";

PASSTHRU mapping resolved for source table OGG.T_OGG

info ext_name

查看进程的chekpoint信息

GGSCI (dbserver) 3> info PORA_1

EXTRACT PORA_1 Last Started 2020-04-19 10:51 Status RUNNING

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

Log Read Checkpoint File /app/oggdata/dirdat/aa000013

2020-04-19 13:10:11.000000 RBA 1934

stats ext_name

进程目前有多个是事务，包含insert delete update等

用法：stats <进程名称>,<时间频度>,table 用户名.表名

时间频度:daily HOURLY等

可以查看进程处理的记录数。该报告会详细的列出处理的类型和记录数。

GGSCI (dbserver) 4> info EORA_1

EXTRACT EORA_1 Last Started 2020-04-19 10:51 Status RUNNING

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

Log Read Checkpoint Oracle Redo Logs

2020-04-19 13:55:46 Seqno 12754, RBA 3926528

SCN 0.29422768 (29422768)

GGSCI (dbserver) 5> stats EORA_1

Sending STATS request to EXTRACT EORA_1 ...

Start of Statistics at 2020-04-19 13:57:28.

Output to /app/oggdata/dirdat/aa:

Extracting from OGG.T_OGG to OGG.T_OGG:

*** Total statistics since 2020-04-19 10:59:06 ***

Total inserts 4.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 5.00

*** Daily statistics since 2020-04-19 10:59:06 ***

Total inserts 4.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 5.00

*** Hourly statistics since 2020-04-19 13:00:00 ***

Total inserts 1.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 1.00

*** Latest statistics since 2020-04-19 10:59:06 ***

Total inserts 4.00

Total updates 1.00

Total deletes 0.00

Total discards 0.00

Total operations 5.00

End of Statistics.

Lag ext_name

查看延时，以及文件抽取应用情况

GGSCI (mdw) 134> dblogin sourcedb GG_Postgres userid ogg

Password:

2020-04-19 14:18:24 INFO OGG-03036 Database character set identified as UTF-8. Locale: root.

2020-04-19 14:18:24 INFO OGG-03037 Session character set identified as UTF-8.

Successfully logged into database.

GGSCI (mdw as ogg@GG_Postgres) 135> lag RORA_1

Sending GETLAG request to REPLICAT RORA_1 ...

Last record lag 6 seconds.

At EOF, no more records to process.#表示没有延迟

info ext_name showch

查看详细处理过的事物记录

GGSCI (mdw as ogg@GG_Postgres) 139> info RORA_1 showch

REPLICAT RORA_1 Last Started 2020-04-19 11:11 Status RUNNING

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

Process ID 10059

Log Read Checkpoint File /data/ogg/dirdat/pa000003

2020-04-19 13:23:17.657451 RBA 1966

Current Checkpoint Detail:

Read Checkpoint #1

GGS Log Trail

Startup Checkpoint (starting position in the data source):

Sequence #: 3

RBA: 0

Timestamp: Not Available

Extract Trail: /data/ogg/dirdat/pa

Seqno Length: 6

Current Checkpoint (position of last record read in the data source):

Sequence #: 3

RBA: 1966

Timestamp: 2020-04-19 13:23:17.657451

Extract Trail: /data/ogg/dirdat/pa

Seqno Length: 6

Header:

Version = 2

Record Source = A

Type = 1

# Input Checkpoints = 1

# Output Checkpoints = 0

File Information:

Block Size = 2048

Max Blocks = 100

Record Length = 2048

Current Offset = 0

Configuration:

Data Source = 0

Transaction Integrity = -1

Task Type = 0

Database Checkpoint:

Checkpoint table = ogg.checkpointtab

Key = 3203906823 (0xbef7bd07)

Create Time = 2020-04-16 15:21:02

Status:

Start Time = 2020-04-19 11:11:23

Last Update Time = 2020-04-19 14:25:27

Stop Status = A

Last Result = 400

info ext_name detail

查看更详细的信息，包括所使用的trail文件，参数文件，报告文件，告警日志文件位置等

GGSCI (mdw as ogg@GG_Postgres) 138> info RORA_1 detail

REPLICAT RORA_1 Last Started 2020-04-19 11:11 Status RUNNING

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

Process ID 10059

Log Read Checkpoint File /data/ogg/dirdat/pa000003

2020-04-19 13:23:17.657451 RBA 1966

Current Log BSN value: (no data)

Last Committed Transaction CSN value: 29417492

Extract Source Begin End

/data/ogg/dirdat/pa000003 * Initialized * 2020-04-19 13:23

/data/ogg/dirdat/pa000003 * Initialized * First Record

/data/ogg/dirdat/pa000002 2020-04-19 10:49 2020-04-19 10:49

/data/ogg/dirdat/pa000003 * Initialized * First Record

/data/ogg/dirdat/pa000002 2020-04-19 10:49 2020-04-19 10:49

/data/ogg/dirdat/pa000002 2020-04-19 10:31 2020-04-19 10:49

/data/ogg/dirdat/pa000002 2020-04-19 10:31 2020-04-19 10:32

/data/ogg/dirdat/pa000002 2020-04-19 10:10 2020-04-19 10:32

/data/ogg/dirdat/pa000002 2020-04-19 10:10 First Record

/data/ogg/dirdat/pa000002 2020-04-19 09:34 2020-04-19 10:10

/data/ogg/dirdat/pa000002 2020-04-19 09:34 First Record

/data/ogg/dirdat/pa000002 2020-04-19 09:27 2020-04-19 09:34

/data/ogg/dirdat/pa000002 2020-04-19 08:55 2020-04-19 09:27

/data/ogg/dirdat/pa000002 * Initialized * 2020-04-19 08:55

/data/ogg/dirdat/pa000001 * Initialized * First Record

/data/ogg/dirdat/pa000002 2020-04-16 15:21 2020-04-18 23:49

/data/ogg/dirdat/pa000000000 * Initialized * 2020-04-16 15:21

Current directory /data/ogg

Report file /data/ogg/dirrpt/RORA_1.rpt

Parameter file /data/ogg/dirprm/rora_1.prm

Checkpoint file /data/ogg/dirchk/RORA_1.cpr

Checkpoint table ogg.checkpointtab

Process file /data/ogg/dirpcs/RORA_1.pcr

Error log /data/ogg/ggserr.log

send extrac查看长交易

GGSCI> send extract ext1,showtrans

Ggsci> send extract <进程名> , showtrans [thread n] [count n]

其中，<进程名>为所要察看的进程名，如extsz/extxm/extjx等；

Thread n是可选的，表示只查看其中一个节点上的未提交交易；

Count n也是可选的，表示只显示n条记录。

例如,查看extsz进程中节点1上最长的10个交易，可以通过下列命令：

Ggsci> send extract extsz , showtrans thread 1 count 10

输出结果是以时间降序排列的所有未提交交易列表，通过xid可以查找到对应的事务，查找出未提交原因，通过数据库予以提交或者回滚后GoldenGate的checkpoint会自动向前滚动。

使用GoldenGate命令跳过或接受长交易的方法

在GoldenGate中强制提交或者回滚指定事务，可以通过以下命令（<>中的为参数）：

Ggsci> SEND EXTRACT <进程名>, SKIPTRANS <5.17.27634> THREAD <2> //跳过交易

Ggsci> SEND EXTRACT <进程名>, FORCETRANS <5.17.27634> THREAD <1> //强制认为该交易已经提交

说明：使用这些命令只会让GoldenGate进程跳过或者认为该交易已经提交，但并不改变数据库中的交易，他们依旧存在于数据库中。因此，强烈建议使用数据库中提交或者回滚交易而不是使用GoldenGate处理。

10. 配置长交易告警

可以在extract进程中配置长交易告警，参数如下所示：

extract extsz

……

warnlongtrans 12h, checkintervals 10m

exttrail /backup/goldengate/dirdat/sz

….

以上表示GoldenGate会每隔10分钟检查一下长交易，如果有超过12个小时的长交易，GoldenGate会在根目录下的ggserr.log里面加入一条告警信息。可以通过察看ggserr.log或者在ggsci中执行view ggsevt命令查看这些告警信息。以上配置可以有助于及时发现长交易并予以处理。

说明：在OGG 11g中，extract提供了BR参数可以设置每隔一段时间（默认4小时）将长交易缓存到本地硬盘（默认dirtmp目录下），因此extract只要不停止一般需要的归档日志不超过8个小时（极限情况）。但是如果extract停掉后，便无法再自动缓存长交易，需要的归档日志就会依赖于停机时间变长。

11. –重置抽取进程，本地文件序列号从0开始生成。

alter extract ext1,extseqno 0,extrba 0

12.–重置读取进程，重新从0号trial文件开始读取。

alter replicat rep1,extseqno 0,extrba 0

13.查看帮助

help, help add exttrail

14.启动和停止服务

start mgr,stop mgr

start extract einikk,stop extract einikk

start replicat rinikk,stop replicat rinikk

15.修改参数文件

edit params <group name>

16.显示当前的GoldenGate环境信息

show

17.历史命令

history

18.使用shell脚本

gdora@testdb11 ggs]$ cat /home/gdora/ggs/yzhq.txt

status manager

status mgr

[gdora@testdb11 ggs]$

Using UNIX batch and shell scripts

On a UNIX system, you can issue GoldenGate commands from a script such as a startup

script, shutdown script, or failover script by running GGSCI and calling an input file. Use

the following syntax:

ggsci < <input_file>

[gdora@testdb11 ggs]$ ggsci < yzhq.txt

GoldenGate Command Interpreter for Oracle

Version 10.4.0.12 Build 001

Linux, x86, 32bit (optimized), Oracle 10 on Aug 21 2009 14:34:04

The GoldenGate software may include other code written by third parties;

details regarding such third party code, including applicable copyright,

legal and licensing notices are available at http://support.goldengate.com.

GGSCI (testdb11.zhyhl.com) 1>

Manager is running (IP port testdb11.zhyhl.com.7809).

GGSCI (testdb11.zhyhl.com) 2>

Manager is running (IP port testdb11.zhyhl.com.7809).

GGSCI (testdb11.zhyhl.com) 3> [gdora@testdb11 ggs]$

[gdora@testdb11 ggs]$

18.查看参数文件的内容

view params mgr

19.添加进程

select log_mode,supplemental_log_data_min,force_logging from v$database;

GGSCI (node3) 10> view params ./GLOBALS

GGSCHEMA ggs

CHECKPOINTTABLE ggs.ggschkpt

1、抽取进程

add extract gpyun,tranlog,begin now

add exttrail /opt/ggs/dirdat/bl,extract gpyun

edit params gpyun

GGSCI (gg1) 28> view params ext01

extract ext01 --提取进程组的名字

userid ggtest, password oracle --同步用户

exttrail /u01/ggate/dirdat/sd --trail 文件目录和标识（类似于sd*这样文件)，命名只能是2个字符。

tranlogoptions excludeuser ggtest --避免循环复制的出现

DDL INCLUDE ALL

DDLOPTIONS ADDTRANDATA

table ssgg.*; --同步的表或用户下所有的表

当你需要同步除Goldengate和Oracle数据库自带的用户外所有用户的DDL操作时，可在源端主抽取进程中加入下面的内容

ddl include all

然后再在目标端Replicat进程参数文件中加入DDL错误的处理语句即可。

2、传输进程

add extract gptosz,exttrailsource /opt/ggs/dirdat/bl,begin now

add rmttrail /opt/ggs/dirdat/bl,extract gptosz

edit params gptosz

GGSCI (gg1) 29> view params p01

extract p01

userid ggtest,password oracle

rmthost 192.168.50.231,mgrport 7809 --指定远端IP和MGR端口

rmttrail /u01/ggate/dirdat/pd

table ssgg.*;

3、应用进程

为replicat进程创建checkpoint表：

GGSCI (gg2) 1> dblogin userid ggtest,password ggtest

Successfully logged into database.

GGSCI (gg2) 2> add checkpointtable ggs.ggschkpt

Successfully created checkpoint table goldengate.rep_demo_ckpt.

add replicat gprep,exttrail /opt/ggs/dirdat/nl,checkpointtable ggs.ggschkpt

add replicat repbj,exttrail /opt/ggs/dirdat/bl,checkpointtable ggs.ggschkpt

edit parmas gprep

edit params repbj

实现双向复制，一般用于双业务中心环境下在实际应用中。双向复制（DDL＋DML）面临着许多问题，主要有如下几点：

1. 如果两个库同时更新同一条记录如何处理？调整业务规则

2. 如果网络出现失败如何处理？支持断点续传

3. 如果数据不同步后如何修复？导出源端，导入目标端

总结：

双向复制避免数据冲突是难点，解决这个难点根本还是调整业务：

a) 只在一端开展业务

b) 两端开展不同业务，涉及不同数据集

c) 两端开展相同业务，但依据地域或其他条件对数据予以区分，两边不操作同一条数据

add replicat repbj,exttrail /opt/ggs/dirdat/mdgroup/bj,checkpointtable ggs.ggschkpt

GGSCI (qfangbj2) 2> view params repbj

REPLICAT repbj

ASSUMETARGETDEFS

SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

SETENV(ORACLE_HOME = "/oracle/database/product/11.2.0/dbhome_1")

USERID ggs, PASSWORD oracleggs

APPLYNOOPUPDATES

REPERROR DEFAULT, DISCARD

discardfile ./dirrpt/repbj.dsc,APPEND,MEGABYTES 1024

DYNAMICRESOLUTION

DDL INCLUDE MAPPED

DDLERROR DEFAULT IGNORE RETRYOP

MAP GROUP.t_person, TARGET group.t_person;

MAP GROUP.t_org, TARGET group.t_org;

MAP GROUP.t_store, TARGET group.t_store;

MAP GROUPUSER.* , TARGET GROUPUSER.* ;

alter EXTRACT MODTOSH begin 2016-01-19 17:59:10

alter EXTRACT MGOUPEXT begin 2016-01-28 03:10:10

alter REPLICAT MSZGREP begin 2016-07-25 10:50:00

alter REPLICAT MSZGREP begin now

Alter extract E2,etrollover #具体作用了解下

加密：

[ggs@qfangbj2 ggs]$ ./keygen 192 4

mykey1 0x63E3C4423A054D1FADA86118960F9B7416731D7B4E0C4931

mykey2 0x4477853632F5E47AD58B2B0C2E1A9E23AEC65C7C7DD9483B

mykey3 0x250B462A2BE57C56FC6EF57FC524A152461A9C7DACA64845

mykey4 0x069F061E24D514322452BF735D2FA401DE6DDB7EDB73484F

DBOPTIONS DECRYPTPASSWORD AADAAAAAAAAAAAOAYHMDTEVFJJEIKEOHCBXIGAEAJEYAYHEEFDRDWEZGBIJAFEQIGJUDHBIDMGQJCJGBYELABGPEAGBCNDIH AES192 encryptkey mykey1

排除某张表：

TABLEEXCLUDE fin.TEST

TABLE fin.*

DBGuardian

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录