简单说明:
依据《11. Setting up a simple GoldenGate replication confguration between two single node databases》
部署试验环境模拟本次实验,源端和目标端均只部署完MGR即可开始本次实验
注意在Oracle 11.2.0.4 之后需要打开ENABLE_GOLDENGATE_REPLICATION参数:
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
目标库新增异构表:
su - oracle
sqlplus scott/tiger
-- 创建和emp表列序不同的表:EMP_DIFFCOL_ORDER
create table EMP_DIFFCOL_ORDER
(
mgr NUMBER(4),
deptno NUMBER(2),
hiredate DATE,
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER(7,2),
comm NUMBER(7,2)
);
alter table EMP_DIFFCOL_ORDER
add constraint PK_EMP_DIFFCOL_ORDER primary key (EMPNO)
using index tablespace USERS;
alter table EMP_DIFFCOL_ORDER
add constraint FK_EMP_DIFFCOL_ORDER_DEPTNO foreign key (DEPTNO)
references DEPT (DEPTNO);
-- 外键创建索引
create index IND_EMP_DIFFCOL_ORDER_DEPTNO on EMP_DIFFCOL_ORDER(DEPTNO);
-- 装入数据
insert into EMP_DIFFCOL_ORDER (empno,ename,job,mgr,hiredate,sal,comm,deptno)
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;
commit;
-- 创建比emp表多出一列LAST_UPDATE_TIME的表:EMP_EXTRACOL
create table EMP_EXTRACOL
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
LAST_UPDATE_TIME DATE default sysdate
);
alter table EMP_EXTRACOL
add constraint PK_EMP_EXTRACOL primary key (EMPNO)
using index tablespace USERS;
alter table EMP_EXTRACOL
add constraint FK_EMP_EXTRACOL_DEPTNO foreign key (DEPTNO)
references DEPT (DEPTNO);
-- 外键创建索引
create index IND_EMP_EXTRACOL_DEPTNO on EMP_EXTRACOL(DEPTNO);
-- 装入数据
insert into EMP_EXTRACOL (empno,ename,job,mgr,hiredate,sal,comm,deptno)
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;
commit;
-- 创建基于HIREDATE列分区的emp分区表:EMP_PART
create table EMP_PART
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
partition by range (HIREDATE)
(
partition P_1980 values less than (TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition P_1981 values less than (TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition P_1982 values less than (TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition P_1987 values less than (TO_DATE(' 1988-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition P_MAX values less than (MAXVALUE) tablespace USERS
);
alter table EMP_PART
add constraint PK_EMP_PART primary key (EMPNO)
using index tablespace USERS;
alter table EMP_PART
add constraint FK_EMP_PART_DEPTNO foreign key (DEPTNO)
references DEPT (DEPTNO);
-- 外键创建分区索引
create index IND_EMP_PART_DEPTNO on EMP_PART(DEPTNO,HIREDATE) local;
-- 装入数据
insert into EMP_PART (empno,ename,job,mgr,hiredate,sal,comm,deptno)
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;
commit;
select count(*) from EMP_PART partition (P_1981);
exit
su - oracle
cd /ggs
./ggsci
DBLOGIN USERID system PASSWORD oracle
ADD TRANDATA scott.*
INFO TRANDATA scott.*
exit
# 考虑到灾备切换,目标端打开表级附加日志
实验步骤:
1° 源端创建defgen配置文件:
su - oracle
cd /ggs/dirprm
cat >defs.prm<<EOF
DEFSFILE ./dirdef/defs.def
USERID ggs, PASSWORD ggs
TABLE SCOTT.EMP;
TABLE SCOTT.DEPT;
TABLE SCOTT.BONUS;
TABLE SCOTT.SALGRADE;
EOF
cd /ggs
./defgen paramfile ./dirprm/defs.prm
cat ./dirdef/defs.def
# 将生成的表结构文件复制到目标端对应位置
scp ./dirdef/defs.def 192.168.77.11:/ggs/dirdef
2° 源端配置抽取和发送进程配置文件:
su - oracle
cd /ggs/dirprm
cat >EGGTEST1.prm<<EOF
EXTRACT EGGTEST1
USERID ggs, PASSWORD ggs
EXTTRAIL /ggs/dirdat/st
TABLE SCOTT.*;
EOF
cat >PGGTEST1.prm<<EOF
EXTRACT PGGTEST1
USERID ggs, PASSWORD ggs
RMTHOST 192.168.77.11, MGRPORT 8809
RMTTRAIL /ggs/dirdat/rt
TABLE SCOTT.*;
EOF
3° 目标端配置应用进程配置文件:
su - oracle
cd /ggs/dirprm
cat >RGGTEST1.prm<<EOF
REPLICAT RGGTEST1
USERID ggs, PASSWORD ggs
DISCARDFILE /ggs/dirrpt/RGGTEST1.dsc, append, MEGABYTES 500
SOURCEDEFS ./dirdef/defs.def
-- 使用参数 SOURCEDEFS 指定表结构文件
MAP SCOTT.*, TARGET SCOTT.*;
-- 默认的表配置MAP
MAP SCOTT.EMP, TARGET SCOTT.EMP_DIFFCOL_ORDER;
MAP SCOTT.EMP, TARGET SCOTT.EMP_EXTRACOL, COLMAP(USEDEFAULTS,LAST_UPDATE_TIME = @DATENOW ());
-- 使用COLMAP参数指定@DATENOW()的值为多余字段的值
MAP SCOTT.EMP, TARGET SCOTT.EMP_PART;
-- 新增的表单独配置MAP
EOF
4° 源端和目标端进程添加和启动:
# 源端操作:
su - oracle
cd /ggs
./ggsci
ADD EXTRACT EGGTEST1, TRANLOG, BEGIN NOW
ADD EXTTRAIL /ggs/dirdat/st, EXTRACT EGGTEST1
ADD EXTRACT PGGTEST1, EXTTRAILSOURCE /ggs/dirdat/st
ADD RMTTRAIL /ggs/dirdat/rt, EXTRACT PGGTEST1
START EXTRACT EGGTEST1
STATUS EXTRACT EGGTEST1
START EXTRACT PGGTEST1
STATUS EXTRACT PGGTEST1
exit
# 目标端操作:
su - oracle
cd /ggs
./ggsci
DBLOGIN, USERID ggs PASSWORD ggs
ADD CHECKPOINTTABLE ggs.rggtest1_ckpt
ADD REPLICAT RGGTEST1, EXTTRAIL /ggs/dirdat/rt, CHECKPOINTTABLE ggs.rggtest1_ckpt
START REPLICAT RGGTEST1
STATUS REPLICAT RGGTEST1
exit
5° 验证:
# 源库操作:
sqlplus scott/tiger
insert into emp values(9999,'vincent','vincent',7788,sysdate,100,100,10);
commit;
exit
# 目标库验证:
sqlplus scott/tiger
select * from EMP where empno=9999;
select * from EMP_DIFFCOL_ORDER where empno=9999;
select * from EMP_EXTRACOL where empno=9999;
select * from EMP_PART where empno=9999;
exit
# 验证通过
额外功能验证:
有时候只是将源库的非分区表映射成目标库同名分区表,依然需要使用defgen
1° 实验环境清理准备:
# 源端清理:
su - oracle
cd /ggs
echo 'stop *'|./ggsci
# 目标端清理:
su - oracle
cd /ggs
./ggsci
DBLOGIN, USERID ggs PASSWORD ggs
DELETE REPLICAT RGGTEST1
DELETE CHECKPOINTTABLE ggs.rggtest1_ckpt !
exit
rm -rf /ggs/dirprm/RGGTEST1
# 目标库准备:
su - oracle
sqlplus scott/tiger
create table EMP_SWAP
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
partition by range (HIREDATE)
(
partition P_1980 values less than (TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition P_1981 values less than (TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition P_1982 values less than (TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition P_1987 values less than (TO_DATE(' 1988-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition P_MAX values less than (MAXVALUE) tablespace USERS
);
alter table EMP_SWAP
add constraint PK_EMP_SWAP primary key (EMPNO)
using index tablespace USERS;
alter table EMP_SWAP
add constraint FK_EMP_SWAP_DEPTNO foreign key (DEPTNO)
references DEPT (DEPTNO);
-- 外键创建分区索引
create index IND_EMP_SWAP_DEPTNO on EMP_SWAP(DEPTNO,HIREDATE) local;
-- 装入数据
insert into EMP_SWAP (empno,ename,job,mgr,hiredate,sal,comm,deptno)
select empno,ename,job,mgr,hiredate,sal,comm,deptno from EMP;
commit;
-- 改名替换
alter table EMP rename to EMP_OLD;
alter table EMP_SWAP rename to EMP;
select count(*) from EMP partition (P_1981);
exit
2° 目标端配置应用进程配置文件:
su - oracle
cd /ggs/dirprm
cat >RGGTEST1.prm<<EOF
REPLICAT RGGTEST1
USERID ggs, PASSWORD ggs
DISCARDFILE /ggs/dirrpt/RGGTEST1.dsc, append, MEGABYTES 500
SOURCEDEFS ./dirdef/defs.def
-- 使用参数 SOURCEDEFS 指定表结构文件
MAP SCOTT.*, TARGET SCOTT.*;
EOF
3° 源端和目标端进程添加和启动:
# 源端操作:
su - oracle
cd /ggs
./ggsci
START EXTRACT EGGTEST1
STATUS EXTRACT EGGTEST1
START EXTRACT PGGTEST1
STATUS EXTRACT PGGTEST1
exit
# 目标端操作:
su - oracle
cd /ggs
./ggsci
DBLOGIN, USERID ggs PASSWORD ggs
ADD CHECKPOINTTABLE ggs.rggtest1_ckpt
ADD REPLICAT RGGTEST1, EXTTRAIL /ggs/dirdat/rt, CHECKPOINTTABLE ggs.rggtest1_ckpt
START REPLICAT RGGTEST1
STATUS REPLICAT RGGTEST1
exit
4° 验证:
# 源库操作:
sqlplus scott/tiger
insert into emp values(8888,'vincent','vincent',7788,sysdate,100,100,10);
commit;
exit
# 目标库验证:
sqlplus scott/tiger
select * from EMP where empno=8888;
select * from EMP_DIFFCOL_ORDER where empno=8888;
select * from EMP_EXTRACOL where empno=8888;
select * from EMP_PART where empno=8888;
exit
# 经验证,只有EMP有新纪录,额外的表没有新纪录
# 只要源库和目标库对应的表结构不同,就必须使用defgen进行同步
总结:
当源库和目标库的对象结构存在不一致时,不能使用参数ASSUMETARGETDEFS
当结构不一致时,使用SOURCEDEFS或TARGETDEFS来指定生成的表结构文件
生成表结构文件时,配置文件需要库登录配置、表结构文件全路径和所有表的列表
如果源库要新增表,则停掉源端和目标端进程,重新生成表结构文件并同步到目标端
一般情况下在源端生成表结构文件,特殊情况基于目标库的表结构生成表结构文件
[TOC]