Oracle OGG 資料同步簡單配置

1、安裝包準備

資料庫版本：oracle database 11g release 2(11.2.0.4.0)

安裝包版本：oracle goldengate 12.3.0.1.4 for oracle on linux x86-64

2、建立ogg使用者

useradd -u 700 -g oinstall -g dba ogg

3、建立軟體安裝目錄

mkdir -p /ogg/product/ogg_home

mkdir -p /ogg/setup/

chown -r ogg:oinstall /ogg

chmod -r g+w /ogg

4、配置環境變量

# oracle

export lang=en_us

export oracle_sid=orcl

export oracle_base=/u01/app/oracle

export oracle_home=/u01/app/oracle/product/db_home_1

export ld_library_path=/u01/app/oracle/product/db_home_1/lib

export path=$oracle_home/bin:$oracle_home/jdk/bin:$path

export nls_date_format="yyyy-mm-dd hh24:mi:ss";

export nls_lang=american_america.utf8;

# ogg 

export ogg_home=/ogg/product/ogg_home

export path=$ogg_home:$path

export ld_library_path=$ogg_home:$ld_library_path

5、解壓安裝軟體安裝完成即可

./runinstaller

6、在源和目标建立ogg專用表空間和使用者并授予權限

$ sqlplus / as sysdba

sql> create tablespace ogg logging datafile '/u01/app/oracle/oradata/orcl/ogg.dbf' size 20m autoextend on uniform size 2m;

sql> create user ogg identified by oracle default tablespace ogg temporary tablespace temp quota unlimited on ogg;

sql> grant unlimited tablespace to ogg;

sql> grant connect, resource, dba to ogg;

sql> grant create session, alter session to ogg;

sql> grant alter system to ogg;

sql> grant select any dictionary to ogg;

sql> grant flashback any table to ogg;

sql> grant select any table, insert any table, update any table, delete any table, drop any table to ogg;

sql> grant create table, create sequence to ogg;

sql> grant select on dba_clusters to ogg;

sql> grant select on v_$database to ogg;

sql> grant select on sys.logmnr_buildlog to ogg;

sql> grant select any transaction to ogg;

sql> grant lock any table to ogg;

sql> grant ggs_ggsuser_role to ogg;

sql> grant execute on dbms_flashback to ogg;

sql> grant execute on dbms_logmnr_d to ogg;

sql> grant execute on dbms_capture_adm to ogg;

sql> grant execute on dbms_streams to ogg;

sql> grant execute on utl_file to ogg;

sql> exec dbms_streams_auth.grant_admin_privilege('ogg');

7、配置源和目标資料庫模式

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

sql> alter system set enable_goldengate_replication=true scope=both; -- 11.2.0.4

sql> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog_1';

sql> shutdown immediate;                          

sql> startup mount;                               

sql> alter database archivelog;                   

sql> alter database open;                         

sql> alter database force logging;                

sql> alter database add supplemental log data;

8、源及目标庫建立測試用表

create table customers

(

    id          number,

    name          varchar2(20),

    city          varchar2(20),

    state         varchar2(20),

    constraint pk_custid primary key(id)

);

9、使用者級别的附加日志

$ ggsci

ggsci (dbsrc) 1> dblogin userid ogg, password oracle

ggsci (dbsrc as ogg@orcl) 2> add schematrandata ogg

10、表級别的附加日志

ggsci (dbsrc as ogg@orcl) 2>  add trandata ogg.customers;

11、執行配置腳本配置ddl同步

sql> @marker_setup

sql> @ddl_setup

sql> @role_setup

sql> @ddl_enable

12、配置manager程序(管理程序)

ggsci (dbsrc) 1>  info mgr

-- 源端

ggsci (dbsrc) 2> edit param mgr

port 7809

dynamicportlist  7810-7850

autostart extract *

autorestart extract *, retries 5, waitminutes 2

purgeoldextracts /ogg/product/ogg_home/dirdat/ *, usecheckpoints, minkeepdays 5

lagreporthours 1

laginfominutes 30

lagcriticalminutes 45

-- 目标端：

ggsci (dbtrg) 1> edit param mgr

autostart replicat *

autorestart replicat *, retries 5, waitminutes 2

purgeoldextracts /ogg/product/ogg_home/dirdat/ *, usecheckpoints, minkeepdays 10

purgeddlhistory minkeepdays 7, maxkeepdays 10

purgemarkerhistory minkeepdays 7, maxkeepdays 10

13、配置extract程序（隻在源端配置）

-- 配置抽取程序

ggsci (dbsrc) 5> dblogin userid ogg, password oracle

ggsci (dbsrc as ogg@orcl) 6> add extract ext1,tranlog,begin now

ggsci (dbsrc as ogg@orcl) 7> add exttrail /ogg/product/ogg_home/dirdat/sr, extract ext1

ggsci (dbsrc as ogg@orcl) 8> edit param ext1 

extract ext1

setenv(oracle_sid="orcl")

setenv(nls_lang=american_america.al32utf8)

userid ogg, password oracle

reportcount every 30 minutes, rate

discardfile /ogg/product/ogg_home/dirrpt/extsr.dsc, append, megabytes 1024

discardrollover at 3:00

exttrail /ogg/product/ogg_home/dirdat/sr

dynamicresolution

dboptions  allowunusedcolumn

fetchoptions nousesnapshot

fetchoptions fetchpkupdatecols

table ogg.*;

-- 配置投遞程序

ggsci (dbsrc as ogg@orcl) 9> add extract dpump1, exttrailsource /ogg/product/ogg_home/dirdat/sr

ggsci (dbsrc as ogg@orcl) 10> edit param dpump1

extract dpump1

rmthost 192.168.93.226, mgrport 7809, compress

passthru

rmttrail /ogg/product/ogg_home/dirdat/tr

ggsci (dbsrc as ogg@orcl) 11> add rmttrail /ogg/product/ogg_home/dirdat/tr, extract dpump1

14、目标端配置replicat程序

ggsci (dbtrg) 1> dblogin userid ogg, password oracle

-- 建立和配置checkpoint table

ggsci (dbtrg as ogg@orcl) 2> add checkpointtable ogg.checkpoint

ggsci (dbtrg as ogg@orcl) 3> edit param /ogg/product/ogg_home/globals

ggschema ogg

checkpointtable ogg.checkpoint

-- 建立replicat程序

ggsci (dbtrg as ogg@orcl) 4> add replicat rep1, exttrail /ogg/product/ogg_home/dirdat/tr, checkpointtable ogg.checkpoint

ggsci (dbtrg as ogg@orcl) 5> edit param rep1

replicat rep1

report at 06:00

reportrollover at 02:00

reperror default, abend

allownoopupdates

assumetargetdefs

handlecollisions

discardfile /ogg/product/ogg_home/dirrpt/repsa.dsc, append, megabytes 1024

discardrollover at 02:00

map ogg.*, target ogg.*;

15、初始化資料

初始化資料可以使用ogg程序初始化，也可以使用其他同步工具來初始化。

16、啟動goldengate

注：因為在mgr都相應的配置了extract程序和replicat程序的自啟動，是以在mgr程序啟動後會自動啟動extract程序和replicat程序

-- 目标端

ggsci (dbtrg) 1> start mgr

ggsci (dbtrg) 2> info all

ggsci (dbtrg) 3> start rep1  

ggsci (dbsrc) 1> start mgr

ggsci (dbsrc) 2> info all

ggsci (dbsrc) 3> start ext1

ggsci (dbsrc) 4> start dpump1

日志檔案存儲路徑：

goldengate安裝目錄下/ogg/product/ogg_home/ggserr.log

17、同步測試

-- 測試的時候分别測試insert、delete、update操作

sql> conn ogg/oracle

sql> select count(*) from customers;

sql> insert into customers(id,name,city,state) values(1,'ahern','cq','cn');

sql> commit;

-- 檢視源端extract程序狀态

ggsci (dbsrc) 5> stats ext1

ggsci (dbsrc) 6> stats dpump1

-- 檢視目标端replicat程序狀态

ggsci (dbtrg) 4> stats rep1

-- 目标端資料查詢

sql> select * from customers where name='ahern';