目標完成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 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 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中的命令不能分號結尾

GoldenGate學習筆記(5)_配置例程之單向複製

相關文章