OGG简单配置

针对部分表进行ogg同步的话，可以参考下面的步骤进行操作，正常情况下按顺序执行就差不多了
【数据库准备】
首先，开启归档、全局附加日志和forcelogging
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
--查询结果要是yes，不是的话执行alter
select supplemental_log_data_min,force_logging from v$database;
alter database add supplemental log data;
alter database force logging;

下面是源库和目标库都需要做
1、创建ogguser用户
create user ogguser identified by ogguser;
grant connect to ogguser;
grant resource to ogguser;
grant unlimited tablespaces to ogguser;
grant execute on tul_file to ogguser;
grant select any dictionary to ogguser;
grant select any table to ogguser;
grant alter any table to ogguser;
grant flashback any table to ogguser;
grant execute on dbms_flashback to ogguser;

2、解压安装goldengate并创建目录 ./ggsci、create subdirs
[oracle@rac1 goldengate]$ ./ggsci
ggsci (rac1) 2> create subdirs

3、创建ckpt表
edit param ./globals
ggschema ogguser
checkpointtable ogguser.checkpoint
ggsci (dbdata) 9> dblogin userid ogguser password ogguser
successfully logged into database.

ggsci (dbdata as ogguser@test233) 10> add checkpointtable ogguser.checkpoint
successfully created checkpoint table ogguser.checkpoint.

--如果出现已存在或有问题，通过sqlplus来drop,再重新执行上面的
sqlplus ogguser/ogguser
drop table checkpoint;
drop table checkpoint_lox;

4、创建并启动mgr进程
edit param mgr
port 7839
dynamicportlist 7840-7914
autostart extract *
autorestart extract *, retries 3, waitminutes 3
purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 5
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45

ggsci (rac1 as ogguser@rac1) 15> start mgr
manager started.
【源库】
0\识别需要同步的表并添加附加日志：
alter table test.t_test add supplemental log data (all) columns;
alter table test.aa add supplemental log data (all) columns;
alter table test.bb add supplemental log data (all) columns;

1、抽取进程
add ext ext1, tranlog, threads 1, begin now
add exttrail ./dirdat/r1, extract ext1,megabytes 1000

edit param ext1
extract ext1
setenv (nls_lang=american_america.zhs16gbk)
setenv (oracle_sid=rac1)
userid ogguser, password ogguser
--reportcount every 1 minutes, rate
--numfiles 5000
discardfile ./dirrpt/ext.dsc,append, megabytes 100
discardrollover at 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
tranlogoptions convertucs2clobs
tranlogoptions dblogreader
fetchoptions nousesnapshot
fetchoptions fetchpkupdatecols
statoptions reportfetch
warnlongtrans 5h,checkinterval 30m
----tables------
table test.t_test;
table test.aa;
table test.bb;

start ext1

--数据库环境变量字符集的查询，要与下面的对应
sql> select * from nls_database_parameters;
parameter value
------------------------------ --------------------------------------------------
--nls_language american
--nls_territory america
nls_currency $
nls_iso_currency america
nls_numeric_characters .,
--nls_characterset zhs16gbk

2、传输进程
add extract pxt1,exttrailsource ./dirdat/r1
add rmttrail ./dirdat/p1, extract pxt1,megabytes 1000
--其中 192.168.5.233 对应的是目标端的主机ip
edit param pxt1
extract pxt1
dynamicresolution
passthru
rmthost 192.168.5.233, mgrport 7839, compress
rmttrail ./dirdat/p1
numfiles 500
---tables
table test.t_test;
table test.aa;
table test.bb;

start pxt1

3、注意事项：
1、启动这个之前要启动目标机的mgr进程
2、检查源、目标库抓取和获取数据是否正常 ll dirdat
验证源端抓取是否正常，已产生r1000000 文件
[oracle@rac1 goldengate]$ ll dirdat
16
-rw-r----- 1 oracle oinstall 1370 10-17 18:33 r1000000

验证目标端获取数据是否正常，已产生 p1000000 文件
[oracle@dbdata goldengate]$ ll dirdat
total 12
-rw-r----- 1 oracle oinstall 0 oct 17 18:46 p1000000

【目标库数据初始化】
1、源库获取scn
需要用到源库scn
col current_scn format 999999999999999
select current_scn from v$database;
2、源+目标库
create or replace directory dump_dir as '/home/oracle/dump_dir';
grant read,write on directory dump_dir to ogguser;
3、expdp/impdp
源库
expdp test/test directory=dump_dir tables=t_test,aa,bb dumpfile=expdp_test_2tabs.dmp logfile=expdp_test_2tabs.log flashback_scn=8689213127
--传输文件到目标库，然后就恢复
scp /mnt/dump_dir/expdp_test_2tabs* 192.168.5.233:/home/oracle/dump_dir
目标库
impdp ogguser/ogguser directory=dump_dir remap_schema=test:ogguser dumpfile=expdp_test_bb.dmp logfile=expdp_test_bb_imp.log

【目标库】
复制进程
用到的文件/目录是源端传输进程的p1，对应关系要一一对应
add replicat rxt1, exttrail ./dirdat/p1,

edit param rxt1
replicat rxt1
handlecollisions
assumetargetdefs
--setenv (oracle_sid=test233)
setenv (nls_lang=american_america.zhs16gbk)
userid ogguser,password ogguser
dboptions nosuppresstriggers
reportcount every 1 minutes, rate
--reperror default, abend
numfiles 500
discardfile ./dirrpt/rxt.dsc, append, megabytes 100
allownoopupdates
----tables-----
map test.t_test, target ogguser.t_test;
map test.aa, target ogguser.aa;
map test.bb, target ogguser.bb;

start rxt1

注意事项
nosuppresstriggers 目标端需要用到触发器时，要加这个参数，否则触发器不能用。

【(带触发器的表)同步测试】
--源表与目标表（结构一样，初始从源库impdp过来），必须有主键，主键必是触发器中的条件
create table bb
(
bb varchar2(30) not null,
sdate date not null
);
alter table bb add constraint pk_b primary key (bb);
--中间表，可不要主键
create table bb_changes
(
bb varchar2(30),
timestamp timestamp(6),
status number default 0
);
--触发器，源表主键必是其中的一个条件
create or replace trigger tr_bb_changes
after insert or update on bb
for each row
declare
cnt number;
begin
select count(1)
into cnt
from bb_changes s
where :new.bb = s.bb
and s.status = 0;
if cnt = 0 then
insert into bb_changes
(bb, timestamp)
values
(:new.bb, current_timestamp);
end if;
end;

在源库执行
insert into bb (bb, sdate) values ('1', sysdate);
commit;
insert into bb (bb, sdate) values ('2', sysdate);
commit;
insert into bb (bb, sdate) values ('3', sysdate);
commit;

update bb set bb='11' where bb='1';
commit;
--在目标库可以看到bb表中已同步存在上面的数据，且bb_changes中会增加相应的dml记录
select * from ogguser.bb order by sdate desc ;
select * from ogguser.bb_changes;