Oracle--ogg（goldengate）

ogg安装配置过程：

1.将ogg压缩包上传到oracle家目录：

scp ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip oracle@oracle0:/home/oracle/
scp ogg112101_fbo_ggs_Linux_x64_ora10g_64bit.zip oracle@172.25.254.250:/home/oracle/

2.解压缩：
mkdir -p /home/oracle/insogg/
unzip /home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip -d /home/oracle/insogg/
mkdir -p /home/oracle/ogg/
tar -xvf /home/oracle/insogg/fbo_ggs_Linux_x64_ora11g_64bit.tar -C /home/oracle/ogg

3.在source和target将/home/oracle/ogg加入环境变量PATH
PATH=$ORACLE_HOME/bin:/home/oracle/ogg:$PATH
source ~/.bashrc

4.创建ogg需要的子目录[所有节点]：必须在/home/oracle/ogg目录下启动ggsci
ggsci
GGSCI (oracle0.example.com) 1> create subdirs

5.在数据库中创建ogg的管理用户[所有节点]
grant connect,
resource,
unlimited tablespace,
select any dictionary,
select any table,
alter any table,
flashback any table
to ggs
identified by ggs;

grant execute on dbms_flashback to ggs;
grant execute on utl_file to ggs;
grant select any dictionary to scott;

grant insert any table,
update any table,
delete any table
to ggs;

6.准备测试用的数据
打开追加日志数据模式
alter database add supplemental log data;

准备测试用的表
create table scott.e01 as select * from scott.emp;
alter table scott.e01 add constraint pk_e01_empno primary key (empno);

将源表的数据导入到目标库
172.25.254.250:
exp scott/tiger tables=e01 file=e01.dmp
scp e01.dmp oracle@172.25.0.10:/home/oracle
172.25.0.10:
imp scott/tiger tables=e01 file=e01.dmp

7.在ogg中添加需要同步的表：在源端添加
GGSCI (foundation0.ilt.example.com) 1> dblogin userid ggs, password ggs
GGSCI (foundation0.ilt.example.com) 2> add trandata scott.e01
GGSCI (foundation0.ilt.example.com) 3> info trandata scott.e01
GGSCI (foundation0.ilt.example.com) 3> info trandata scott.*

8.目标端添加checkpoint表:
vi /home/oracle/ogg/GLOBALS
------------------------------
checkpointtable ggs.checkpoint
------------------------------
GGSCI (oracle0.example.com) 2> dblogin userid ggs, password ggs
GGSCI (oracle0.example.com) 3> add checkpointtable ggs.checkpoint

9.配置管理进程的参数文件[所有节点]：
vi /home/oracle/ogg/dirprm/mgr.prm
------------------------------------------------------
port 7788
userid ggs,password ggs
autorestart extract *,waitminutes 2,retries 5
------------------------------------------------------

10.在源端配置抽取进程的参数文件:
vi /home/oracle/ogg/dirprm/exta.prm
----------------------------------------------
extract exta
userid ggs,password ggs
rmthost 172.25.0.10,mgrport 7788
exttrail /home/oracle/ogg/dirdat/ea
setenv(ORACLE_SID=db01)
setenv(NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
dynamicresolution
table scott.e01;
----------------------------------------------

11.添加抽取进程:
GGSCI (foundation0.ilt.example.com) 2> add extract exta, tranlog, begin now

12.添加抽取进程需要的文件:
GGSCI (foundation0.ilt.example.com) 3> add exttrail /home/oracle/ogg/dirdat/ea,extract exta

13.在源端配置投递进程参数文件：
vi /home/oracle/ogg/dirprm/pumpa.prm
---------------------------------------------------
EXTRACT pumpa
USERID scott, PASSWORD tiger
RMTHOST 172.25.0.12, MGRPORT 7788
RMTTRAIL /home/oracle/ogg/dirdat/pa
TABLE scott.e01;
---------------------------------------------------

14.在源端增加投递进程
GGSCI (foundation0.ilt.example.com) 5> add extract pumpa,exttrailsource /home/oracle/ogg/dirdat/ea , begin now

15.增加投递到远程的文件
GGSCI (foundation0.ilt.example.com) 6> add rmttrail /home/oracle/ogg/dirdat/pa ,extract pumpa

16.配置目标端的复制进程的参数文件:
vi /home/oracle/ogg/dirprm/repa.prm
----------------------------------------------------
replicat repa
userid ggs,password ggs
assumetargetdefs
discardfile /home/oracle/ogg/dirdat/rep1.dsc,append
MAP scott.e01, TARGET scott.e01;
----------------------------------------------------

17.增加复制进程
GGSCI (oracle0.example.com) 8> add replicat repa,exttrail /home/oracle/ogg/dirdat/pa , nodbcheckpoint
==================================================================================
所有需要的参数文件都配置完成，按照顺序启动各个进程:
1.启动源端的管理进程
start mgr
2.启动目标端的管理进程
start mgr
3.启动目标端的复制进程
start repa
4.启动源端的抽取进程
start exta
5.启动源端的投递进程
start pumpa
==================================================================================
18.双向同步:
*需要在所有节点的抽取进程配置文件中添加参数 tranlogoptions excludeuser ggs 禁止循环复制

在B库将e01加入到同步队列
add trandata scott.e01
info trandata scott.*

*在B库增加抽取进程配置文件
vi /home/oracle/ogg/dirprm/extb.prm
----------------------------------------------
extract extb
userid ggs,password ggs
rmthost 172.25.0.12,mgrport 7788
exttrail /home/oracle/ogg/dirdat/eb
tranlogoptions excludeuser ggs
setenv(ORACLE_SID=aux2)
setenv(NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
dynamicresolution
table scott.e01;
----------------------------------------------
*在B库增加抽取进程
add extract extb, tranlog, begin now
*在B库增加抽取进程生成文件
add exttrail /home/oracle/ogg/dirdat/eb,extract extb
*在B库增加投递进程配置文件
vi /home/oracle/ogg/dirprm/pumpb.prm
---------------------------------------------------
EXTRACT pumpb
USERID scott, PASSWORD tiger
RMTHOST 172.25.0.10, MGRPORT 7788
RMTTRAIL /home/oracle/ogg/dirdat/pb
TABLE scott.e01;
---------------------------------------------------
*在B库增加投递进程
add extract pumpb,exttrailsource /home/oracle/ogg/dirdat/eb , begin now
*在B库增加投递到A库的文件
add rmttrail /home/oracle/ogg/dirdat/pb ,extract pumpb

*在A库增加复制进程配置文件
vi /home/oracle/ogg/dirprm/repb.prm
----------------------------------------------------
replicat repb
userid ggs,password ggs
assumetargetdefs
discardfile /home/oracle/ogg/dirdat/rep2.dsc,append
MAP scott.e01, TARGET scott.e01;
----------------------------------------------------
*在A库增加复制进程
add replicat repb,exttrail /home/oracle/ogg/dirdat/pb , nodbcheckpoint

start ext2
start pump2
start rep2
======================================================================================
启动抽取进程：抽取进程一定要先启动，保证所有数据修改在抽取文件中都被包括
start ext1

获取scn
SYS@ aux1> select current_scn from v$database;

CURRENT_SCN
-----------
    3096081

导出源表172.25.254.250:
exp system/uplooking tables=scott.e01 file=e01.dmp flashback_scn=3096081
将dmp文件传到目标库
scp e01.dmp oracle@172.25.0.12:/home/oracle
将源表导入目标库172.25.0.10:
imp system/uplooking file=e01.dmp full=y

在目标启动复制进程：启动复制进程时一定要使用表被导出时的scn，保证没有sql被重复应用！
START REPLICAT rep2, ATCSN 3096081

*可以多抽取，不要多应用！
======================================================================================
添加DDL支持:
需要先禁用recyclebin:源和目标都做
SQL> alter system set recyclebin=off scope=spfile;
SQL> startup force
SQL> purge dba_recyclebin;

进入ogg安装目录运行脚本:
脚本1开始:
SQL> @marker_setup

Enter Oracle GoldenGate schema name: ggs

脚本2开始:
SQL> @ddl_setup
Enter Oracle GoldenGate schema name:ggs

11.2.0.4 bug:
alter trigger sys.ggs_ddl_trigger_before disable;
grant create table,create sequence to ggs;
alter trigger sys.ggs_ddl_trigger_before enable;

SQL> @ddl_setup

脚本3开始:
SQL> @role_setup
Enter GoldenGate schema name:ggs
将脚本3创建的角色授予ogg管理用户
SYS@ aux1> GRANT GGS_GGSUSER_ROLE TO ggs;

脚本4开始:
SQL> @ddl_enable

修改抽取进程的文件添加ddl支持
vi /home/oracle/ogg/dirprm/ext1.prm
----------------------------------------------
extract ext1
userid ggs,password ggs
rmthost 172.25.254.250,mgrport 7788
tranlogoptions excludeuser ggs
exttrail /home/oracle/ogg/dirdat/ea
setenv(ORACLE_SID=aux1)
setenv(NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
dynamicresolution
ddl include all
ddloptions addtrandata
table scott.*;
----------------------------------------------

vi /home/oracle/ogg/dirprm/pump1.prm
---------------------------------------------------
EXTRACT pump1
USERID scott, PASSWORD tiger
RMTHOST 172.25.0.10, MGRPORT 7788
RMTTRAIL /home/oracle/ogg/dirdat/pa
TABLE scott.*;
---------------------------------------------------

vi /home/oracle/ogg/dirprm/rep2.prm
----------------------------------------------------
replicat rep2
userid ggs,password ggs
assumetargetdefs
discardfile /home/oracle/ogg/dirdat/rep2.dsc,append
ddl include mapped
ddlerror default ignore retryop
MAP scott.*, TARGET scott.*;
----------------------------------------------------

vi /home/oracle/ogg/dirprm/ext2.prm
----------------------------------------------
extract ext2
userid ggs,password ggs
rmthost 172.25.0.10,mgrport 7788
exttrail /home/oracle/ogg/dirdat/eb
tranlogoptions excludeuser ggs
setenv(ORACLE_SID=orcl)
setenv(NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
dynamicresolution
ddl include all
ddloptions addtrandata
table scott.*;
----------------------------------------------

vi /home/oracle/ogg/dirprm/pump2.prm
---------------------------------------------------
EXTRACT pump2
USERID scott, PASSWORD tiger
RMTHOST 172.25.254.250, MGRPORT 7788
RMTTRAIL /home/oracle/ogg/dirdat/pb
TABLE scott.*;
---------------------------------------------------

vi /home/oracle/ogg/dirprm/rep1.prm
----------------------------------------------------
replicat rep1
userid ggs,password ggs
assumetargetdefs
discardfile /home/oracle/ogg/dirdat/rep1.dsc,append
ddl include mapped
ddlerror default ignore retryop
MAP scott.*, TARGET scott.*;
----------------------------------------------------