OGG配置DML单向复制一例及错误分析

来源：互联网发布：uefi linux 编辑：程序博客网时间：2024/05/23 02:22

环境：11.2.0.3+OEL5.7

192.168.1.55zlm sid:zlm11g

192.168.1.60zlm2 sid:zlm11g

gg软件包：ogg112101_fbo_ggs_Linux_x64_ora11g_64bit

一、安装OGG软件并配置实验环境

*******

源主库：

*******

1.把gg软件包复制到源主机，2次解压到gg安装目录gg11

[oracle@zlmdb_1]$ cd $OACLE_BASE

[oracle@zlm~]$ ls

incremental_hot_database_backup.sh ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

incremental_hot_database_backup.sh.out orz.sh

[oracle@zlmoracle]$ mkdir gg11

[oracle@zlmoracle]$ cd gg11

[oracle@zlmgg11]$ unzip /home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@zlmgg11]$ ll

total223764

-rw-rw-r--1 oracle oinstall 228556800 Apr 23 2012fbo_ggs_Linux_x64_ora11g_64bit.tar

-rwxrwxrwx1 oracle oinstall 220546 May 2 2012OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf

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

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

[oracle@zlmgg11]$ tar xvoffbo_ggs_Linux_x64_ora11g_64bit.tar

UserExitExamples/

UserExitExamples/ExitDemo_more_recs/

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX

UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX

UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj

UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c

UserExitExamples/ExitDemo_more_recs/readme.txt

.............

2.修改环境变量文件.bash_profile，加入OGG的环境变量

export GGHOME=/$ORACLE_BASE/gg11

exportPATH=$ORACLE_BASE/gg11:$ORACLE_HOME/bin:/usr/bin/:$PATH

[oracle@zlmgg11]$ . ~/.bash_profile

[oracle@zlmgg11]$ ggsci

ggsci:error while loading shared libraries: libnnz11.so: cannot open shared objectfile: No such file or directory

由于没有设置LD_LIBRARY_PATH环境变量，所以无法执行ggsci，重新添加到.bash_profile

在环境变量中添加如下命令：

export LD_LIBLARY_PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/lib:$ORACLE_HOME/bin

重新source一下后执行ggsci

[oracle@zlmgg11]$ . ~/.bash_profile

[oracle@zlmgg11]$ ggsci

OracleGoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux,x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

3.创建OGG专用目录subdirs

GGSCI (zlm)1> create subdirs

Creatingsubdirectories under current directory /u01/app/oracle/gg11

Parameterfiles /u01/app/oracle/gg11/dirprm: already exists

Reportfiles /u01/app/oracle/gg11/dirrpt: created

Checkpointfiles /u01/app/oracle/gg11/dirchk:created

Processstatus files /u01/app/oracle/gg11/dirpcs: created

SQLscript files /u01/app/oracle/gg11/dirsql: created

Databasedefinitions files /u01/app/oracle/gg11/dirdef: created

Extractdata files /u01/app/oracle/gg11/dirdat: created

Temporaryfiles /u01/app/oracle/gg11/dirtmp: created

Stdoutfiles /u01/app/oracle/gg11/dirout: created

4.对源数据库设置补充日志,并开启归档模式和强制归档

SQL>alter database add supplemental log data;

Databasealtered.

SQL>alter system switch logfile;

Systemaltered.

SQL>select supplemental_log_data_min,log_mode,force_logging from v$database;

SUPPLEMELOG_MODE FOR

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

YES ARCHIVELOG NO

SQL> alterdatabase force logging;

Databasealtered.

SQL>select supplemental_log_data_min,log_mode,force_loggingfrom v$database;

SUPPLEMELOG_MODE FOR

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

YES ARCHIVELOG YES

5.编辑源数据库管理进程参数文件

GGSCI(zlm) 1> edit params mgr

Cannotload ICU resource bundle 'ggMessage', error code 2 - No such file or directory

Aborted

这里报错是因为之前进入ggsci界面是并没有在OGG安装目录，必须要在$GGHOME(如果配置过的话，我这里$GGHOME=/u01/app/oracle/gg11)

[oracle@zlm~]$ cd $ORACLE_BASE/gg11

[oracle@zlmgg11]$ ggsci

OracleGoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux,x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

GGSCI(zlm) 1> edit params mgr

在mgr中添加入如下一行：(和vi编辑器一样操作)

PORT 7809

6.启动管理进程及查看管理进程信息

GGSCI(zlm) 3> start mgr

Managerstarted.

GGSCI(zlm) 4> info mgr

Manageris running (IP port zlm.7809).

********

目标库：

********

7.在目标主机重复以上步骤1-6(具体步骤略),启动目标库管理进程并查看

GGSCI(zlm2) 4> start mgr

Managerstarted.

GGSCI(zlm2) 5> info mgr

Manageris running (IP port zlm2.7809).

*******

源主库：

*******

8.创建源数据库OGG实验用户ggtest并给予connect和resource权限

SQL>create user ggtest identified by ggtest defaulttablespace userstemporary tablespace tempquota unlimited on users;

Usercreated.

SQL>grant connect,resourceto ggtest;

Grantsucceeded.

9.创建源数据库OGG实验表(此处用到了OGG软件包中自带的2个测试脚本)

SQL>conn ggtest/ggtest

Connected.

SQL>select object_name,object_type from user_objects;

no rowsselected

SQL>@/u01/app/oracle/gg11/demo_ora_create.sql

DROPTABLE tcustmer

ERROR atline 1:

ORA-00942:table or view does not exist

Tablecreated.

DROPTABLE tcustord

ERROR atline 1:

ORA-00942:table or view does not exist

Tablecreated.

10.创建源数据库OGG实验表中的数据

SQL>@/u01/app/oracle/gg11/demo_ora_insert.sql

1 rowcreated.

Commitcomplete.

********

目标库：

********

11.创建目标数据库OGG实验用户ggtest，并给予connect和resource权限

SQL>create user ggtest identified by ggtest defaulttablespace users temporary tablespace tempquota unlimited on users;

Usercreated.

SQL>grant connect,resourceto ggtest;

Grantsucceeded.

SQL>conn ggtest/ggtest

Connected.

SQL>@/u01/app/oracle/gg11/demo_ora_create.sql

DROPTABLE tcustmer

ERROR atline 1:

ORA-00942:table or view does not exist

执行完SQL脚本后，此时目标库的测试环境是2张空表，tcustmer和tcustord，稍后可以进行DML的测试

12.查看源数据库用户表的记录

SQL>show user

USER is"GGTEST"

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

SQL>select * from tcustord;

CUSTORDER_DAT PRODUCT_ ORDER_IDPRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID

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

WILL30-SEP-94 CAR 144 17520 3 100

JANE11-NOV-95 PLANE 256 133300 1 100

13.查看目标库用户表中的记录

SQL>show user

USER is"GGTEST"

SQL>select * from tcustmer;

no rowsselected

SQL>select * from tcustord;

no rowsselected

二、一次性抽取同步实验(不配置任何TRAIL文件)

1.用OGG推荐的方法(即INITIAL EXTRACT)一次性抽取，初始化源库到目标库的数据

GGSCI(zlm) 1> dblogin userid system,password oracle

Successfullylogged into database.

GGSCI(zlm) 2> add trandata ggtest.tcustmer

Loggingof supplemental redo data enabled for table GGTEST.TCUSTMER.

GGSCI(zlm) 3> add trandata ggtest.tcustord

Loggingof supplemental redo data enabled for table GGTEST.TCUSTORD.

GGSCI(zlm) 4> info trandata ggtest.*

Loggingof supplemental redo log data is enabled for table GGTEST.TCUSTMER.

Columnssupplementally logged for table GGTEST.TCUSTMER: CUST_CODE.

Loggingof supplemental redo log data is enabled for table GGTEST.TCUSTORD.

Columnssupplementally logged for table GGTEST.TCUSTORD: CUST_CODE, ORDER_DATE,PRODUCT_CODE, ORDER_ID.

2.源数据库增加extrace进程组einit并配置参数

GGSCI(zlm) 5> add extract einit,sourceistable

EXTRACTadded.

GGSCI(zlm) 6> edit params einit

extracteinit

useridsystem,password oracle

rmthostzlm2,mgrport 7809

rmttaskreplicat,group rinit

tableggtest.tcustermer;

tableggtest.tcustord;

GGSCI(zlm) 7> info extract *,tasks

EXTRACT EINIT Initialized 2013-08-2301:55 Status STOPPED

CheckpointLag Not Available

Log ReadCheckpoint Not Available

First Record Record 0

Task SOURCEISTABLE

3.目标库增加replicat组rinit并配置参数

GGSCI(zlm2) 1> add replicat rinit,specialrun

REPLICATadded.

GGSCI(zlm2) 2> edit params rinit

replicatrinit

assumetargetdefs --表示是同构表(相同数据库软件之间的复制，如：oracle-oracle)

useridsystem,password oracle

discardfile ./dirrpt/rinit.dsc,purge

mapggtest.*,target ggtest.*;

GGSCI(zlm2) 3> info replicat *,task

REPLICAT RINIT Initialized 2013-08-2302:02 Status STOPPED

CheckpointLag 00:00:00 (updated 00:03:13 ago)

Log ReadCheckpoint Not Available

Task SPECIALRUN

INITIALEXTRACT是通过配置rmttask参数来指定的，SOURCEISTABLE表示初始化整个表，SPECIALRUN表示只抽取一次，这个是OGG初始化抽取必须配置的几个参数，注意，此处不用配置EXTTRAIL/RMTTRAIL参数

4.启动源库extract并查看源库extract报告

GGSCI(zlm) 8> start extract einit

SendingSTART request to MANAGER ...

EXTRACTEINIT starting

GGSCI (zlm)9> view report einit

......(略)

Output torinit:

FromTable GGTEST.TCUSTMER:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

FromTable GGTEST.TCUSTORD:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

5.查看目标库replicat报告

GGSCI(zlm2) 5> view report rora

......(略)

FromTable GGTEST.TCUSTMER to GGTEST.TCUSTMER:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

FromTable GGTEST.TCUSTORD to GGTEST.TCUSTORD:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

注意：用INITIAL EXTRACT进行一次性抽取初始化数据时，目标库的replicat进程不用手动启动，只要源库的extract进程start以后，自动会同步到目标库，而且此时用info all是看不到extract和replicat进程的，只能看见mgr进程

6.目标库用ggtest用户查看表

[oracle@zlm2gg11]$ sqlplus '/as sysdba'

SQL*Plus:Release 11.2.0.3.0 Production on Fri Aug 2303:29:33 2013

Connectedto:

OracleDatabase 11g Enterprise Edition Release 11.2.0.3.0- 64bit Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options

SQL>show user

USER is"SYS"

SQL>conn ggtest/ggtest

Connected.

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

SQL>select * from tcustord;

CUSTORDER_DAT PRODUCT_ ORDER_IDPRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID

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

WILL30-SEP-94 CAR 144 17520 3 100

JANE11-NOV-95 PLANE 256 133300 1 100

之前通过replicat的report信息中已经知道，数据应该已经从源库同步到目标库了，现在通过查看目标库的测试表，发现数据确实已经同步过来了

三、不配置PUMP进程和本地TRAIL路径的同步实验

1.配置DML方式复制，源数据库添加extract组并配置参数

GGSCI(zlm) 1> add extract eora,tranlog,begin now,[threads 1]

EXTRACTadded.

启用tranlog日志 (mssql和oracle是用tranlog，mysql用的是vam)，立即开始

threads 1可缺省

GGSCI(zlm) 2> info extract *

EXTRACT EORA Initialized 2013-08-2409:48 Status STOPPED

CheckpointLag 00:00:00 (updated 00:00:08 ago)

Log ReadCheckpoint Oracle Redo Logs

2013-08-24 09:48:13 Thread 1, Seqno 0, RBA 0

SCN 0.0 (0)

GGSCI(zlm) 3> edit params eora

extracteora

useridsystem,password oracle

rmthost zlm2,mgrport 7809 --mssql用的是7815端口

rmttrail ./dirdat/rt

tableggtest.tcustmer;

tableggtest.tcustord;

由于没有配置PUMP抽取进程，此处直接指定远端trail路径，本地不配置trail路径

2.添加源库rmttrail并启动extract

GGSCI(zlm) 5> add rmttrail ./dirdat/rt,extracteora,megabytes 5

RMTTRAILadded.

此操作执行后，会在远端创建一个rt00000文件，如果已经有了，那么继续使用原来的rt000000文件，megabytes指定TRAIL文件大小的上限，此处是5M，默认是10M一个文件

GGSCI(zlm) 6> info rmttrail *

Extract Trail: ./dirdat/rt

Extract: EORA

Seqno: 0

RBA: 0

File Size: 5M

GGSCI(zlm) 7> start extract eora

ERROR:Manager not currently running.

GGSCI(zlm) 8> start mgr

Managerstarted.

GGSCI(zlm) 9> start extract eora

SendingSTART request to MANAGER ...

EXTRACTEORA starting

3.配置目标库全局参数文件./GLOBALS，并添加checkpointtable

GGSCI(zlm2) 1> edit params ./GLOBALES

checkpointtable system.chkpt

GGSCI(zlm2) 2> dblogin userid system,password oracle

Successfullylogged into database.

GGSCI(zlm2) 3> add checkpointtable

ERROR:Missing checkpoint table specification.

报错：丢失指定的checkpoint table，查看下./GLOBALS参数，表是配置了的

GGSCI(zlm2) 4> view params ./GLOBALS

checkpointtablesystem.chkpt

指定shcema.table_name后再添加一次

GGSCI(zlm2) 5> add checkpointtable system.chkpt

Successfullycreated checkpoint table system.chkpt.

注意，此处需要指定object_name. table_name，而且必须要在./GLOBALS参数中添加checkpointtable参数，并添加相应的checkpoint table，否则replicat进程是无法启动的

4.添加replicat组rora并修改参数

GGSCI (zlm2) 6> addreplicat rora exttrail ./dirdat/rt --配置本地trail路径，该路径是在extract eora中配置rmttrail时指定的那个路径

ERROR: Nocheckpoint table specified for ADD REPLICAT.

报错，也必须指定object_name.table_name

GGSCI(zlm2) 7> add replicat rora exttrail ./dirdat/rtcheckpointtable system.chkpt

REPLICATadded.

GGSCI(zlm2) 8> edit params rora

replicatrora

useridsystem,password oracle

handlecollisions --冲突自动处理

assumetargetdefs --源和目标是同构的，如果异构，此处需指定sourcedefs ./dirdef\xxx.def

reperror default,discard --对错误的响应，可以不配置，默认为写入指定的discardfile

discardfile./dirrpt/rora.dsc,purge --也可以把purge改成append,megabytes 5

mapggtest.*, target gguser.*;

5.启动replicat，并分别查看报告、进程运行状态、replicat进程信息

GGSCI(zlm2) 9> start replicat rora

SendingSTART request to MANAGER ...

REPLICATRORA starting

GGSCI(zlm2) 10> view report rora

内容略...

GGSCI(zlm2) 11> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING RORA 00:00:00 00:00:07

GGSCI(zlm2) 12> info rora

REPLICAT RORA Last Started 2013-08-24 11:08 Status RUNNING

CheckpointLag 00:00:00 (updated 00:00:03 ago)

Log ReadCheckpoint File ./dirdat/rt000000

First Record RBA 0

6.源库启动extract进程eora

GGSCI(zlm) 10> start extract eora

SendingSTART request to MANAGER ...

EXTRACTEORA starting

GGSCI(zlm) 11> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA 00:00:00 01:27:18

7.测试在源库表中插入/更新/删除记录

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

测试插入表数据

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

在源库中插入数据后提交

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

SQL>desc tcustmer

Name Null? Type

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

CUST_CODE NOT NULLVARCHAR2(4)

NAME VARCHAR2(30)

CITY VARCHAR2(20)

STATE CHAR(2)

SQL>insert into tcustmer values('zhao','aaron','shanghai','sh');

1 rowcreated.

SQL> commit; --必须提交

Commitcomplete.

SQL>select * from tcustmer;

CUST NAME CITY ST

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

zhao aaron shanghai sh

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

查看目标库相应的表

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

发现此时并没有同步，源库已经commit了，为什么会么有同步呢？

肯定是哪里配置有错误了，查看备库replicat进程rora的报告

GGSCI(zlm2) 5> view report rora

在报告的Run Time Messages一栏里报了2个错误：

WARNINGOGG-00869 Couldnot retrieve defintion for the table gguser.TCUSTM

ER.

ERROR OGG-00199 Table gguser.TCUSTMER does not exist intarget database.

查看replicat参数

GGSCI(zlm2) 1> view params rora

replicatrora

useridsystem,password oracle

handlecollisions

assumetargetdefs

discardfile./dirrpt/rora.dsc,purge

mapggtest.*, target gguser.*;

原来是target的用户名写错了把gguser.*改成ggtest.*后保存，实验用户是ggtest

GGSCI(zlm2) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED RORA 00:00:00 00:53:10

GGSCI(zlm2) 3> info rora

REPLICAT RORA Last Started 2013-08-24 12:00 Status ABENDED

CheckpointLag 00:00:00 (updated 00:53:17 ago)

Log ReadCheckpoint File ./dirdat/rt000000

First Record RBA 1017

GGSCI(zlm2) 4> start rora

SendingSTART request to MANAGER ...

REPLICATRORA starting

GGSCI(zlm2) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING RORA 00:00:00 00:00:01

再次查看replicat报告

GGSCI(zlm2) 6> view report rora

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

** Run Time Messages **

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

Openedtrail file ./dirdat/rt000000 at 2013-08-24 12:21:20

WildcardMAP resolved (entry ggtest.*):

map "GGTEST"."TCUSTMER",target ggtest."TCUSTMER";

Usingfollowing columns in default map by name:

CUST_CODE, NAME, CITY, STATE

Using thefollowing key columns for target table GGTEST.TCUSTMER: CUST_CODE.

再次查看目标库表里的数据

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

zhao aaron shanghai sh

完成插入的同步

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

测试更新表数据

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

更新源库表内新插入的那条数据并查看

SQL>select * from tcustmer;

CUST NAME CITY ST

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

zhaoaaron beijing bj

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

SQL> commit;

Commitcomplete.

注意：与之前插入时一样，必须commit，以为OGG是根据事务结束为标志，开始同步数据的

SQL>select * from tcustmer;

CUST NAME CITY ST

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

zhao aaron beijing bj

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

在目标库查看表

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

zhao aaron shanghai sh

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

zhao aaron beijing bj

完成更新的同步

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

测试删除表数据

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

删除源表中最新加入的那条数据

SQL>delete from tcustmer where name='aaron';

1 rowdeleted.

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

SQL> commit;

Commitcomplete.

在目标库查看表

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

zhaoaaron beijing bj

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

在以上测试过程中，在extract及replicat进程中配置的目标trail文件夹(./dirdata/lr)下生成了lr000000的文件，我用logdump记录了3个DML操作时该文件的值

**********

插入数据时：

**********

GGHOME目录执行logdump进入操作界面：

Logdump 1>cd ./dirdat

Logdump 2>ls

rt000000

Logdump 3>open rt000000

CurrentLogTrail is /u01/app/oracle/gg11/dirdat/rt000000

Logdump 4>count

LogTrail/u01/app/oracle/gg11/dirdat/rt000000 has 2 records

TotalData Bytes 1058

Avg Bytes/Record 529

Insert 1

Others 1

After Images 1

Averageof 2 Transactions

Bytes/Trans ..... 577

Records/Trans ... 1

Files/Trans ..... 1

**********

更新数据时：

**********

Logdump 5>open ./dirdat/rt000000

CurrentLogTrail is /u01/app/oracle/gg11/dirdat/rt000000

Logdump 6>count

LogTrail/u01/app/oracle/gg11/dirdat/rt000000 has 3 records

TotalData Bytes 1093

Avg Bytes/Record 364

Insert 1

FieldComp 1

Others 1

After Images 2

Averageof 3 Transactions

Bytes/Trans ..... 412

Records/Trans ... 1

Files/Trans ..... 1

**********

删除数据时：

**********

Logdump 7>open ./dirdat/rt000000

CurrentLogTrail is /u01/app/oracle/gg11/dirdat/rt000000

Logdump 8>count

LogTrail/u01/app/oracle/gg11/dirdat/rt000000 has 4 records

TotalData Bytes 1105

Avg Bytes/Record 276

Delete 1

Insert 1

FieldComp 1

Others 1

Before Images 1

After Images 2

Averageof 4 Transactions

Bytes/Trans ..... 324

Records/Trans ... 1

Files/Trans ..... 1

以上的测试环境没有配置pump，而在实际生产环境中，一般都要再配置一个pump的extract进程，这样可以网络环境不是很稳定的情况下，保证OGG传输的可靠性，这个机制大致是：在先本地trail文件保存变更并提交的事务信息，然后可以断点续传到目标库，再由目标库的replicat进程完成同步复制，只要源库trail不被删除，网络通畅时就可以同步到目标库

四、配置PUMP EXTRACT进程的同步实验

1.先修改原extract配置文件eora，注释或删掉以下两行：

--rmthost zlm2,mgrport 7809

--rmttrail ./dirdat/rt

添加一行：exttrail ./dirdata/lt

2.新增pump进程并修改参数

GGSCI(zlm) 1> add extract pora,exttrailsource ./dirdat/lt--添加本地trail(ltxxxxxx)

EXTRACTadded.

注意：这里其实是指定在extract eora中配置的本地路径，所以用了exttrailsource，如果之前没有用add extract eora,exttrail./dirdat/lt创建过本地trail的话，那么该条命令就新建一个；有的话，就指定一下。注意2个extract用的参数是不同的

GGSCI(zlm) 2> edit params pora

extracpora

rmthost zlm2,mgrport 7809

rmttrail ./dirdat/rt

passthru –直接传递，不检测

tableggtest.tcustmer;

tableggtest.tcustord;

相当于原来配置在eora抽取进程中备注释掉的2行参数挪动到了这里，因为现在是通过PUMP进程pora与远端进行通信

GGSCI(zlm) 3> add rmttrail ./dirdat/rt,extract pora --添加远端trail(rtxxxxxx)

RMTTRAILadded.

配置完pump进程pora后，启动该进程

GGSCI(zlm) 4> start pora

SendingSTART request to MANAGER ...

EXTRACTPORA starting

GGSCI(zlm) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA 00:00:00 00:00:04

EXTRACT RUNNING PORA 00:00:00 00:03:27

开始测试数据(为节省篇幅，仅测试插入，更新和删除也是同样实现的)：

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

插入数据测试

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

源库查询表插入一条新数据，提交后查询

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

SQL>insert into tcustmer values('zhao','aaron','shanghai','sh');

1 row created.

SQL> commit;

Commitcomplete.

SQL>select * from tcustmer;

CUST NAME CITY ST

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

zhao aaron shanghai sh

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

目标库查询表

SQL>select * from tcustmer;

CUST NAME CITY ST

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

WILL BGSOFTWARE CO. SEATTLE WA

JANEROCKY FLYER INC. DENVER CO

zhao aaron shanghai sh

加入并配置pump进程pora后，数据依然能够顺利地同步到目标库，分别来看看在源库配置的两个etxtract进程的报告

查看eora报告

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

** Run Time Messages **

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

TABLEresolved (entry ggtest.tcustmer):

table"GGTEST"."TCUSTMER";

Using thefollowing key columns for source table GGTEST.TCUSTMER: CUST_CODE.

查看pora报告

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

** Run Time Messages **

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

Openedtrail file ./dirdat/lt000000 at 2013-08-2414:13:21

TABLEresolved (entry ggtest.tcustmer):

table"GGTEST"."TCUSTMER";

PASSTHRUmapping resolved for source table GGTEST.TCUSTMER

2013-08-2414:16:29 INFO OGG-01054 Recovery completed for target file ./dirdat/rt000001,at

RBA 1426, CSN 1448898.

2013-08-2414:16:29 INFO OGG-01057 Recovery completed for all targets.

从上面可以看到，加入pump进程后，先由pump去本地trail路径./dirdat/lt去读取lt000000文件，然后通过网络同步传输到远程trail路径./dirdat/rt,覆盖到rt000001文件中去

再来看一下目标库上的replicat进程的报告

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

** Run Time Messages **

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

Opened trail file ./dirdat/rt000000 at 2013-08-24 12:21:20

WildcardMAP resolved (entry ggtest.*):

map "GGTEST"."TCUSTMER",target ggtest."TCUSTMER";

Usingfollowing columns in default map by name:

CUST_CODE, NAME, CITY, STATE

Using thefollowing key columns for target table GGTEST.TCUSTMER: CUST_CODE.

Switching to next trail file ./dirdat/rt000001 at 2013-08-24 14:13:17due to EOF, with current R

BA 1464

Openedtrail file ./dirdat/rt000001 at 2013-08-24 14:13:17

2013-08-2414:13:17 INFO OGG-01020 Processed extract process RESTART_ABEND record at seq 1,

rba 1367 (aborted 0 records).

目标库replicat进程rora先处理本地trail路径上的文件rt000000，由于一个EOF，又重新创建了一个rt000001文件进行数据的同步复制

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

关于用户权限的说明：

一般为了在使用OGG同步数据时可以顺利进行，需要给予用户以下权限：

Onsource database (extract):

GRANT CREATE SESSION, ALTER SESSION,RESOURCE, CONNECT,SELECT ANY dictionary TOggtest;

GRANT flashback ANY TABLE, SELECTANY TABLE TO ggtest;

GRANT EXECUTE ON dbms_flashback TO ggtest;

Ontarget (Replicat) database:

GRANT CREATE SESSION, ALTER SESSION,RESOURCE, CONNECT,SELECT ANY dictionary TOggtest;

GRANT SELECT ANY TABLE, INSERT ANYTABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO ggtest;

GRANT CREATE TABLE, ALTER ANY TABLE,LOCK ANY TABLE TO ggtest;

DespiteGoldenGate documentation one grant is missing (that’s probably why Oracle grant DBA to thisaccount):

GRANT ALTER ANY TABLE TO ggtest;

也可以直接给予用户DBA权限，但是这样做是有一定安全隐患的

GRANT DBA TO ggtest

如果是配置DDL操作碰到权限引起的问题，可以考虑加上以上提及的权限，之前的实验只给了2个权限,CONNECT和RESOURCE，实验中并没有遇到不能同步的问题，用的授权语句是GRANT CONNECT,RESOURCE TO ggtest;来看看这2个权限具体包含哪些权限：

SQL>select GRANTEE,PRIVILEGE from DBA_SYS_PRIVSwhere GRANTEE = 'CONNECT';

GRANTEE PRIVILEGE

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

CONNECT CREATE SESSION

SQL>select GRANTEE,PRIVILEGE from DBA_SYS_PRIVSwhere GRANTEE = 'RESOURCE';

GRANTEE PRIVILEGE

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

RESOURCE CREATE CLUSTER

RESOURCE CREATE INDEXTYPE

RESOURCE CREATE OPERATOR

RESOURCE CREATE PROCEDURE

RESOURCE CREATE SEQUENCE

RESOURCE CREATE TABLE

RESOURCE CREATE TRIGGER

RESOURCE CREATE TYPE

8 rowsselected

关于实验中出现错误的处理办法：

通常遇见的错误，就是参数没有配置好，如trail路径不正确，少了字母导致参数找不到，造成目标库数据库无法同步，还有在应用OGG的时候一定要记住，只有commit后，事务处理造成变化的数据才会被同步到目标库，当问题出现时，如某个extract进程不能START，一直是ABENDED状态等，最简单的方法就是直接查看某个进程的报告，里面有详细的消息日志信息，会提示你可能是什么原因造成的错误，以及如何解决等信息，除了report，其实还可以在info中加入detail参数，如下：

GGSCI(zlm) 11> info eora,detail

EXTRACT EORA Initialized 2013-08-24 11:15 Status STOPPED

CheckpointLag 00:00:00 (updated 00:05:25 ago)

Log ReadCheckpoint Oracle Redo Logs

2013-08-24 13:40:07 Thread 1, Seqno 32, RBA 35381248

SCN 0.1449446 (1449446)

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

./dirdat/rt 0 1464 5

./dirdat/lt 0 0 5

Extract Source Begin End

/u01/app/oracle/oradata/zlm11g/redo02.log * Initialized * 2013-08-24 13:40

Not Available * Initialized * 2013-08-24 09:48

Currentdirectory /u01/app/oracle/gg11

Reportfile /u01/app/oracle/gg11/dirrpt/EORA.rpt

Parameterfile /u01/app/oracle/gg11/dirprm/eora.prm

Checkpointfile /u01/app/oracle/gg11/dirchk/EORA.cpe

Processfile /u01/app/oracle/gg11/dirpcs/EORA.pce

Stdoutfile /u01/app/oracle/gg11/dirout/EORA.out

Errorlog /u01/app/oracle/gg11/ggserr.log

从以上内容可以看到，所有的错误在GGHOME下相应的目录中都是有记录的，常用的是ggserr.log，查看这个log文件就可以分析并解决错误了

另外，借助OGG自带的工具logdump，可以通过查看trail文件具体的内容，一定程度上也可以帮你辅助分析OGG在同步过程中发生了什么，帮助你加深理解OGG的同步流程。

具体如何使用可以查阅OGG官方文档e27273.pdf，以下是链接：

http://docs.oracle.com/cd/E28323_01/doc.1121/e27273.pdf

具体trail文件格式说明在第297页，APPENDIX3-Figure 26 Sample trail record as viewed with the Logdump utility

OGG主要进程作用说明：

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

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

初始时间装载阶段：在初始数据装载阶段，Extract进程直接从源端的数据表中抽取数据

同步变化捕获阶段：初始数据同步完成以后，Extract进程负责捕获源端数据的变化(DML和DDL)

Pump(可选)：运行在数据库源端，其作用是将源端产生的本地trail文件，把trail以数据块的形式通过TCP/IP 协议发送到目标端，这通常也是推荐的方式。pump进程本质是extract进程的一种特殊形式，如果不使用trail文件，那么extract进程在抽取完数据以后，直接投递到目标端，生成远程trail文件。如果要使用pump，必须配置本地trail，否则不用。

Collector：与 Pump进程对应的叫Server Collector进程，这个进程不需要引起我的关注，因为在实际操作过程中，无需我们对其进行任何配置，所以对我们来说它是透明的。它运行在目标端，其任务就是把Extract/Pump投递过来的数据重新组装成远程trail文件。

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

数据库中。

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

By aaron8219 ChinaunixBlog：http://blog.chinaunix.net/uid/24612962.html

原创内容，转载请注明链接，谢谢！

http://blog.csdn.net/aaron8219/article/details/10275431