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
GGSCI (dbsrc) 1> dblogin userid ogg, password oracle
GGSCI (dbsrc as ogg@orcl) 2> add trandata ogg.customers;
11、执行配置脚本配置DDL同步
SQL> @marker_setup
SQL> @ddl_setup
SQL> @role_setup
SQL> grant ggs_ggsuser_role to ogg;
SQL> @ddl_enable
12、配置Manager进程(管理进程)
$ ggsci
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
PORT 7809
DYNAMICPORTLIST 7810-7850
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
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
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
SETENV(ORACLE_SID="orcl")
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
RMTHOST 192.168.93.226, mgrport 7809, COMPRESS
PASSTHRU
RMTTRAIL /ogg/product/ogg_home/dirdat/tr
DYNAMICRESOLUTION
table ogg.*;
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
SETENV(ORACLE_SID="orcl")
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD oracle
REPORT AT 06:00
REPORTCOUNT EVERY 30 MINUTES, RATE
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
GGSCI (dbtrg) 1> start mgr
GGSCI (dbtrg) 2> info all
GGSCI (dbtrg) 3> start rep1
-- 源端
$ ggsci
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操作
$ sqlplus / as sysdba
SQL> conn ogg/oracle
SQL> select count(*) from customers;
SQL> insert into customers(id,name,city,state) values(1,'ahern','CQ','CN');
SQL> select count(*) from customers;
SQL> commit;
-- 查看源端extract进程状态
GGSCI (dbsrc) 5> stats ext1
GGSCI (dbsrc) 6> stats dpump1
-- 查看目标端replicat进程状态
GGSCI (dbtrg) 4> stats rep1
-- 目标端数据查询
$ sqlplus / as sysdba
SQL> conn ogg/oracle
SQL> select count(*) from customers;
SQL> select * from customers where name='ahern';