目标完成Oracle GoldenGate的配置，部署

建议建立一个专门的用户如Goldengate来负责将所需的数据同步到远端，而不是用已有的业务用户

这个用户需要哪些权限呢?

Create user goldengate identified by goldengate

default tablespace users

temporary tablespace temp

quota unlimited on users ;

GRANT CONNECT TO goldengate;

GRANT ALTER ANY TABLE TO goldengate;

GRANT ALTER SESSION TO goldengate;

GRANT CREATE SESSION TO goldengate;

GRANT FLASHBACK ANY TABLE TO goldengate;

GRANT SELECT ANY DICTIONARY TO goldengate;

GRANT SELECT ANY TABLE TO goldengate;

GRANT RESOURCE TO goldengate;

GRANT drop ANY TABLE TO goldengate;

GRANT DBA TO goldengate;

一数据准备

Local端和Remote端都要

1 Schema

/opt/oracle/product/9ir2/demo/schema/human_resources/hr_main.sql

user:hr

passwd:hr

tsb: users;

ttsb: temp

2 归档要打开

create pfile='/home/oracle/pfile_090720' from spfile;

#edit pfile

*.log_archive_dest='/opt/oracle/archive/'

*.log_archive_start=true

shutdown immediate

startup mount pfile='/home/oracle/pfile_090720';

alter database archivelog;

create spfile from pfile='/home/oracle/pfile_090720';

alter database open;

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /opt/oracle/archive/

Oldest online log sequence 1

Next log sequence to archive 3

Current log sequence 3

3 supplemental logging 补充日志要打开

SQL> select supplemental_log_data_min from v$database;

SUP

---

NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUP

---

YES

4 Close log parallelism

SQL> show parameter LOG_PARALLELISM

NAME TYPE VALUE

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

log_parallelism integer 1

# make sure the value is 1

二配置 GoldenGate

主机：

本地端Local: 10.230.17.31

配置Extract进程，将hr schema数据的变化抽取并投递到Remote端。

一般应配置两个进程，一个负责抽取数据(extmydb)，另一个负责投递数据(dpemydb)。

远端 Remote: 10.230.17.32

配置Replicat进程(repmydb)，负责接收数据，并投递过来的数据应用到数据库中

三安装，配置Local端

1 上传文件ggs_suse9_x64_ora9i_64bit_v10.0.0.13_001.tar.gz 到/home/oracle目录

Put ggs_suse9_x64_ora9i_64bit_v10.0.0.13_001.tar.gz to 10.230.17.31

2 建立安装目录

su - root

passwd:

mkdir /u01/ggs

chmod -R 777 /u01

exit

3 解压ggs_suse9_x64_ora9i_64bit_v10.0.0.13_001.tar.gz 到/u01/ggs

gzip -d ggs_suse9_x64_ora9i_64bit_v10.0.0.13_001.tar.gz | tar -xvf

4 下载pw文件到/u01/ggs

如没有pw文件，则运行./ggsci 时报错

oracle@Z813:/u01/ggs> ./ggsci

GoldenGate Source Context :

SourceModule : [ggsci.main]

SourceID : [$Id: //depot/releases/OpenSys/v10.0.0/src/app/ggsci/xface.c#1 $]

SourceFunction : [init_functions]

SourceLine : [1630]

ThreadBacktrace : [5] elements

: [./ggsci(_ZN15CMessageFactory13CreateMessageEP14CSourceContextjz+0xb3b) [0x4beacb]]

: [./ggsci(_Z14init_functionsiPPc+0x39f) [0x43dd0f]]

: [./ggsci(main+0x70) [0x43e170]]

: [/lib64/libc.so.6(__libc_start_main+0x9d) [0x2a97db30cd]]

: [./ggsci(_ZN7icu_3_813UnicodeStringC1EPKcS2_+0x52) [0x42336a]]

2009-12-24 09:59:29 GGS ERROR 101 Missing Password: Contact GoldenGate Software.

2009-12-24 09:59:29 GGS ERROR 190 PROCESS ABENDING.

有pw文件，一切正常

oracle@Z813:/u01/ggs> ./ggsci

GoldenGate Command Interpreter for Oracle

Version 10.0.0.13 Build 001

Linux, x64, 64bit (optimized), Oracle 9 on Apr 8 2009 09:04:32

Copyright GoldenGate Software, Inc. 1995-2009.

This software includes code written by third parties, including

and other code as specified at http://support.goldengate.com. Additional

details regarding such third party code, including applicable copyright,

legal and licensing notices, are available at the above referenced URL.

GGSCI (Z813) 1>

5 建立目录

GGSCI (Z813) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u01/ggs

Parameter files /u01/ggs/dirprm: created

Report files /u01/ggs/dirrpt: created

Checkpoint files /u01/ggs/dirchk: created

Process status files /u01/ggs/dirpcs: created

SQL script. files /u01/ggs/dirsql: created

Database definitions files /u01/ggs/dirdef: created

Extract data files /u01/ggs/dirdat: created

Temporary files /u01/ggs/dirtmp: created

Veridata files /u01/ggs/dirver: created

Veridata Lock files /u01/ggs/dirver/lock: created

Veridata Out-Of-Sync files /u01/ggs/dirver/oos: created

Veridata Out-Of-Sync XML files /u01/ggs/dirver/oosxml: created

Veridata Parameter files /u01/ggs/dirver/params: created

Veridata Report files /u01/ggs/dirver/report: created

Veridata Status files /u01/ggs/dirver/status: created

Veridata Trace files /u01/ggs/dirver/trace: created

Stdout files /u01/ggs/dirout: createdCREATE SUBDIRS

6 配置管理进程mgr

GGSCI (Z813) 11> edit params mgr

port 7809

(保存退出)

GGSCI (Z813) 12> start mgr

Manager started.

GGSCI (Z813) 13> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

(可以正常启动)

7 添加，配置Extract进程(extmydb)

ADD EXTRACT extmydb, TRANLOG, BEGIN NOW

ADD EXTTRAIL /u01/ggs/dirdat/ex, EXTRACT extmydb, MEGABYTES 20

GGSCI (Z813) 20> ADD EXTRACT extmydb TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI (Z813) 22> ADD EXTTRAIL /u01/ggs/dirdat/ex, EXTRACT extmydb, MEGABYTES 20

EXTTRAIL added.

GGSCI (Z814) 6> edit params extmydb

extract extmydb

setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )

userid goldengate, password goldengate

REPORT AT 01:59

reportrollover at 02:00

tranlogoptions rawdeviceoffset 0

TRANLOGOPTIONS CONVERTUCS2CLOBS

discardfile ./dirrpt/extmydb.dsc,append, megabytes 10

--DDL include all

--ddloptions addtrandata,nocrossrename

--gettruncates

--warnlongtrans 2h, checkintervals 3m

exttrail /u01/ggs/dirdat/ex

numfiles 3000

dynamicresolution

TABLE hr.*;

--TABLE MAILCONTENT.*;

--table goldengate.*;

8 添加，配置Extract进程(dpemydb)

ADD EXTRACT dpemydb, EXTTRAILSOURCE /u01/ggs/dirdat/ex, BEGIN now

ADD RMTTRAIL /u01/ggs/dirdat/re, EXTRACT dpemydb

(/u01/ggs/dirdata/re 为远端接收目录,必须存在)

GGSCI (Z814) 6> edit params dpemydb

extract depmydb

setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )

passthru

REPORT AT 01:59

reportrollover at 02:00

--ansmemory directory (/ggs/dirtmp,8G,4G), ram 2G,transram 500M

rmthost 10.230.17.32, mgrport 7809, compress

rmttrail /u01/ggs/dirdat/re

dynamicresolution

numfiles 3000

--table GOLDENGATE.*;

TABLE hr.*;

--TABLE MAILCONTENT.*;

--table goldengate.*;

9 启动进程

GGSCI (Z813) 27> start extmydb

Sending START request to MANAGER ...

EXTRACT EXTMYDB starting

GGSCI (Z813) 28> start dpemydb

Sending START request to MANAGER ...

EXTRACT DPEMYDB starting

GGSCI (Z813) 29> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPEMYDB 00:00:00 00:00:38

EXTRACT RUNNING EXTMYDB 00:04:47 00:00:08

四安装，配置Remote端

1 上传文件ggs_suse9_x64_ora9i_64bit_v10.0.0.13_001.tar.gz 到/home/oracle目录

Put ggs_suse9_x64_ora9i_64bit_v10.0.0.13_001.tar.gz to 10.230.17.32

2 建立安装目录

su - root

passwd:

mkdir /u01/ggs

chmod -R 777 /u01

exit

3 解压ggs_suse9_x64_ora9i_64bit_v10.0.0.13_001.tar.gz 到/u01/ggs

gzip -d ggs_suse9_x64_ora9i_64bit_v10.0.0.13_001.tar.gz | tar -xvf

4 下载pw文件到/u01/ggs

oracle@Z813:/u01/ggs> ./ggsci

GoldenGate Command Interpreter for Oracle

Version 10.0.0.13 Build 001

Linux, x64, 64bit (optimized), Oracle 9 on Apr 8 2009 09:04:32

Copyright GoldenGate Software, Inc. 1995-2009.

This software includes code written by third parties, including

and other code as specified at http://support.goldengate.com. Additional

details regarding such third party code, including applicable copyright,

legal and licensing notices, are available at the above referenced URL.

GGSCI (Z813) 1>

5 建立目录

GGSCI (Z813) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u01/ggs

Parameter files /u01/ggs/dirprm: created

Report files /u01/ggs/dirrpt: created

Checkpoint files /u01/ggs/dirchk: created

Process status files /u01/ggs/dirpcs: created

SQL script. files /u01/ggs/dirsql: created

Database definitions files /u01/ggs/dirdef: created

Extract data files /u01/ggs/dirdat: created

Temporary files /u01/ggs/dirtmp: created

Veridata files /u01/ggs/dirver: created

Veridata Lock files /u01/ggs/dirver/lock: created

Veridata Out-Of-Sync files /u01/ggs/dirver/oos: created

Veridata Out-Of-Sync XML files /u01/ggs/dirver/oosxml: created

Veridata Parameter files /u01/ggs/dirver/params: created

Veridata Report files /u01/ggs/dirver/report: created

Veridata Status files /u01/ggs/dirver/status: created

Veridata Trace files /u01/ggs/dirver/trace: created

Stdout files /u01/ggs/dirout: createdCREATE SUBDIRS

6 添加，配置rep进程(repmydb)

ADD REPLICAT repmydb, EXTTRAIL /u01/ggs/dirdat/re, nodbcheckpoint

GGSCI (Z814) 6> edit params repmydb

replicat repmydb

setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )

--setenv (ORACLE_SID="mydb")

userid goldengate, password goldengate

sqlexec "Alter session set constraints=deferred"

REPORT AT 01:59

reportrollover at 02:00

--handlecollisions

reperror default,discard

discardfile ./dirrpt/repmydb.dsc,append, megabytes 10

assumetargetdefs

allownoopupdates

dynamicresolution

numfiles 3000

--DDL &

--INCLUDE ALL,EXCLUDE MAPPED OBJNAME "CAPP.DP_X*" &

--EXCLUDE MAPPED OBJNAME "CAPP.DP_Y*" &

--EXCLUDE MAPPED OBJNAME "CAPP.DP_Z*"

--DDLERROR 942 IGNORE

--DDLERROR DEFAULT DISCARD

map hr.* , target hr.*;

--map MAILCONTENT.* , target MAILCONTENT.*;

--map goldengate.* , target goldengate.*;

7 启动进程

GGSCI (Z814) 11> start repmydb

Sending START request to MANAGER ...

REPLICAT REPMYDB starting

GGSCI (Z814) 12> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPMYDB 00:00:00 00:00:01

五测试

在Local 端插入数据并提交.(Countries为hr的一个表）

SQL> insert into countries values('CU','china',3);

1 row inserted

SQL> commit;

Commit complete

查看抽取状态

GGSCI (Z813) 43> stats extmydb

Sending STATS request to EXTRACT EXTMYDB ...

No active extraction maps.

不成功，没有将数据变化写到抽取队列中.

在Remote端，也没有找到对应数据

没有解决？？

奇怪的是在Local端与Remote端都新建一个表:

create table hr.test(id number,name varchar2(20));

在Local端插入数据

SQL> insert into test(id,name) values(1,'gdut');

1 row inserted

SQL> commit;

在Remote端可以查到

SQL> select * from test;

ID NAME

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

1 gdut

在Local端通过Stats也可查到．

GGSCI (Z813) 25> stats extmydb

Sending STATS request to EXTRACT EXTMYDB ...

Start of Statistics at 2009-12-28 11:28:11.

Output to /u01/ggs/dirdat/ex:

Extracting from HR.TEST to HR.TEST:

*** Total statistics since 2009-12-28 11:17:40 ***

Total inserts 2.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

*** Daily statistics since 2009-12-28 11:17:40 ***

Total inserts 2.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

*** Hourly statistics since 2009-12-28 11:17:40 ***

Total inserts 2.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

*** Latest statistics since 2009-12-28 11:17:40 ***

Total inserts 2.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 2.00

End of Statistics.

这是为什么呢？

为什么对原有的表就不行呢？

是哪个参数的问题？

解决:GGS 对Oracle9i不支持IOT.

六问题

1 进程起不来，是哪里有错误？

进程没有添加进来．

应先添加进程，再配置参数文件．

2 如何协调一致开始GoldenGate传送？

ADD EXTRACT extmydb TRANLOG, BEGIN NOW

一旦执行，将从此时刻开始，将数据库的变化写到抽取队列文件中．

3 GoldenGate 登录不了 ?

SQL> conn goldengate/goldengate

Connected.

GGSCI (Z813) 1> dblogin userid goldengate, password goldengate;

ERROR: Failed to open data source for user GOLDENGATE.

多了个分号，GGS中的命令不能分号结尾

ITPub博客

GoldenGate学习笔记(5)_配置例程之单向复制

最新文章