Goldengate同步Oracle数据到Oracle

OGG进程
捕获进程（源端）：捕获online redo log或者archived log中增量事务日志
传输进程（源端）：把目标端落地的trail文件通过配置的路由信息传输到目标端
网络传输：tcp/ip协议，传输过程可以对传输的文件进行压缩、加密
投递进程（目标端）：把所接收的队列文件信息拼成SQL语句，并不是复制SQL，而是复制逻辑变化，并且在目标库提交

源端配置：MGR、捕获进程、捕获进程参数、捕获进程本地队列、传输进程、传输进程参数、传输进程远程队列
目标端配置：MGR、投递进程、投递进程参数

1、源端，目标端分别安装数据库软件和dbca建库（源端、目标端）
2、源端数据库开启归档（源）
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 507514504 bytes
Database Buffers 264241152 bytes
Redo Buffers 2633728 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database add supplemental log data;
Database altered.

SQL> alter database open;
Database altered.

SQL> select name,LOG_MODE,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME LOG_MODE SUPPLEME

OGGDB ARCHIVELOG YES

切换日志以使附加日志生效：
SQL> alter system archive log current;
System altered.

3、源端、目标端数据库添加goldengate用户独用的表空间（源端、目标端）
源端：
SQL> create tablespace oggdata datafile '/u01/app/oracle/oradata/oggdb/oggdata.dbf' size 1G autoextend off;
Tablespace created.

目标端：
SQL> create tablespace oggdata datafile '/u01/app/oracle/oradata/destdb/oggdata.dbf' size 1G autoextend off;
Tablespace created.

4、源端、目标端创建goldengate用户，授权并指定默认表空间（这里做测试给DBA权限）（源端、目标端）
源端：
SQL> create user goldengate identified by goldengate default tablespace oggdata account unlock;
User created.

SQL> grant dba to goldengate;
Grant succeeded.

目标端：
SQL> create user goldengate identified by goldengate default tablespace oggdata account unlock;
User created.

SQL> grant dba to goldengate;
Grant succeeded.

5、源端、目标端上传ogg安装包并解压（源端、目标端）
mkdir -p /home/oracle/ogg
tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

6、源端、目标端修改oracle用户环境变量（源端、目标端）
源端：
[oracle@dgdb1 ogg]$ vi ~/.bash_profile

.bash_profile

Get the aliases and functions

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

User specific environment and startup programs

export PATH=$PATH:$HOME/bin
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=oggdb
export DB_UNQNAME_NAME=oggdb
export PATH=$PATH:$ORACLE_HOME/bin
export OGG_HOME=/home/oracle/ogg
export PATH=$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

目标端：
[oracle@dgdb2 ~]$ cat .bash_profile

.bash_profile

Get the aliases and functions

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

User specific environment and startup programs

export PATH=$PATH:$HOME/bin
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=destdb
export DB_UNQNAME_NAME=destdb
export PATH=$PATH:$ORACLE_HOME/bin
export OGG_HOME=/home/oracle/ogg
export PATH=$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

7、源端、目标端执行ggsci（源端、目标端）
[oracle@dgdb1 ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (dgdb1) 1>
这样ogg才算安装成功。
注意：ggsci必须在ogg安装目录执行，否则会报错文件目录不存在。

8、源端、目标端ggsci环境下创建goldengate子目录（源端、目标端）
GGSCI (dgdb1) 2> create subdirs
Creating subdirectories under current directory /home/oracle/ogg

Parameter files /home/oracle/ogg/dirprm: already exists
Report files /home/oracle/ogg/dirrpt: created
Checkpoint files /home/oracle/ogg/dirchk: created
Process status files /home/oracle/ogg/dirpcs: created
SQL script files /home/oracle/ogg/dirsql: created
Database definitions files /home/oracle/ogg/dirdef: created
Extract data files /home/oracle/ogg/dirdat: created
Temporary files /home/oracle/ogg/dirtmp: created
Stdout files /home/oracle/ogg/dirout: created

GGSCI (dgdb2) 1> create subdirs
Creating subdirectories under current directory /home/oracle/ogg

Parameter files /home/oracle/ogg/dirprm: already exists
Report files /home/oracle/ogg/dirrpt: created
Checkpoint files /home/oracle/ogg/dirchk: created
Process status files /home/oracle/ogg/dirpcs: created
SQL script files /home/oracle/ogg/dirsql: created
Database definitions files /home/oracle/ogg/dirdef: created
Extract data files /home/oracle/ogg/dirdat: created
Temporary files /home/oracle/ogg/dirtmp: created
Stdout files /home/oracle/ogg/dirout: created

dirchk：用于存放各个进程的检查点
dirdat：用于存放数据队列文件
dirprm：用于存放各进程参数文件
dirrpt：用于存放各进程报告
dirpcs：存放各个正在运行的进程信息

9、源端配置mgr进程，并启动mgr进程（源）
GGSCI (dgdb1) 3> edit param mgr
port 7809

GGSCI (dgdb1) 4> view param mgr
port 7809

GGSCI (dgdb1) 5> start mgr
Manager started.

查看进程状态
GGSCI (dgdb1) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING

10、源端添加表级附加日志（源）
ggsci>dblogin userid goldengate, password goldengate
ggsci>add trandata test.*

11、配置抽取进程（源）
GGSCI> add ext exta, tranlog, begin now

12、为抽取进程配置队列（源）
GGSCI> add exttrail /home/oracle/ogg/dirdat/la, ext exta, MEGABYTES 20 ---（本地队列）
此时exttrail指定的是本地队列位置。

13、配置传输进程（pump进程）（源）
GGSCI> add extract dpea, EXTTRAILSOURCE /home/oracle/ogg/dirdat/la
此时EXTTRAILSOURCE指定的是本地队列位置。

14、为传输进程配置远程队列（源）
GGSCI> add rmttrail /home/oracle/ogg/dirdat/ra, ext dpea, MEGABYTES 20
队列位置是在目标主机上的位置

15、配置抽取进程参数（源）
GGSCI>edit param exta
EXTRACT exta
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
setenv (ORACLE_SID = oggdb)
USERID goldengate, PASSWORD goldengate
EXTTRAIL /home/oracle/ogg/dirdat/la
dynamicresolution
table test.*;

16、配置传输进程参数（pump）（源）
GGSCI>edit param dpea
extract dpea
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
passthru
rmthost 192.168.12.51,mgrport 7809, compress
rmttrail /home/oracle/ogg/dirdat/ra
dynamicresolution
table test.*;

17、启动源端进程（源）
GGSCI>start ext *
也可以根据进程名分别启动
GGSCI>start exta
GGSCI>start dpea

18、初始化（源端、目标端）
源端、目标端添加test用户，并创建表test
SQL> create user test identified by test account unlock;
User created.

SQL> grant dba to test;
Grant succeeded.

SQL> conn test/test
Connected.

SQL> create table test (id int);
Table created.

19、添加mgr进程（目标端）
GGSCI (dgdb2) 5> edit param mgr
port 7809

启动mgr进程
GGSCI (dgdb2) 6> start mgr
Manager started.

GGSCI (dgdb2) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING

20、添加投递进程（目标端）
GGSCI> add rep repa, exttrail /home/oracle/ogg/dirdat/ra, nodbcheckpoint

21、配置投递进程参数（目标端）
GGSCI>edit param repa
replicat repa
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_SID = destdb)
userid goldengate, password goldengate
reperror default,abend
discardfile /home/oracle/ogg/dirrpt/repa.dsc,append, megabytes 10
assumetargetdefs
dynamicresolution
map test., target test.;

启动repa
GGSCI (dgdb2) 14> start repa

Sending START request to MANAGER ...
REPLICAT REPA starting

GGSCI (dgdb2) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REPA 00:00:00 00:00:05

22、测试
主库：
SQL> insert into test values (1);
1 row created.

SQL> commit;
Commit complete.

主库查看进程状态：
GGSCI (dgdb1) 28> stats exta
stats exta
Sending STATS request to EXTRACT EXTA ...
Start of Statistics at 2019-11-24 08:38:49.
Output to /home/oracle/ogg/dirdat/la:
Extracting from TEST.TEST to TEST.TEST:

*** Total statistics since 2019-11-24 08:38:49 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2019-11-24 08:38:49 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2019-11-24 08:38:49 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2019-11-24 08:38:49 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

End of Statistics.

GGSCI (dgdb1) 43> stats dpea
Sending STATS request to EXTRACT DPEA ...
Start of Statistics at 2019-11-24 08:49:31.
Output to /home/oracle/ogg/dirdat/ra:
Extracting from TEST.TEST to TEST.TEST:
*** Total statistics since 2019-11-24 08:49:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2019-11-24 08:49:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2019-11-24 08:49:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2019-11-24 08:49:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
源端已经捕获新增数据

目标端查看进程状态：
GGSCI (dgdb2) 21> stats repa
Sending STATS request to REPLICAT REPA ...
Start of Statistics at 2019-11-24 08:49:44.
Replicating from TEST.TEST to TEST.TEST:
*** Total statistics since 2019-11-24 08:49:13 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Daily statistics since 2019-11-24 08:49:13 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Hourly statistics since 2019-11-24 08:49:13 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

*** Latest statistics since 2019-11-24 08:49:13 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00

End of Statistics.
目标端也已经投递源端传输过来的数据。

目标库查询test数据：
SQL> select * from test;
ID

1

OGG常用命令：
添加进程、队列：add
启动进程：start exta
编辑参数：edit param exta
查看参数：view param exta
查看进程信息：info exta
查看报错信息：view report exta
查看进程状态：info all
查看具体进程状态：stats exta
以上exta为进程名

备注：
1、参数指定的端口必须一致
2、注意防火墙和互信是否配置正确