原理介绍：
有两个大过程
1、初始化数据表 （ogg方式、rman、empdp/impdp、exp/imp等方式）
2、同步过程：1、源端extract pump （exttrail日志文件） 2、目标端replicat 进程（使用源端exttrail日志文件）
通俗就是：源端捕获进程捕获数据改变量存放在本地目录，PUMP进程使用本地捕获文件投递到目标端trail文件目录，目标端replicat进程收集源端传输过来的trail文件，并读取文件，将其内容应用到数据本身，达到源目端数据同步。
实验环境描述：
一、操作系统及oracle 软件、ogg软件版本
OS:CentOS Linux release 7.7.1908 (Core)
oracle软件：11.2.0.4 ogg：Oracle_GoldenGate_11.2.1.0.3
二、信息分配
host01:192.0.2.11 host02:192.0.2.12
SID: orcl orclogg
（备注：安装库时添加示例表信息，后面同步测试用到）
三、网络拓扑
四、ogg软件安装（host01，host02库安装步骤省略了，我前面文章里面有的哈）
1、上传软件包到host01，host02 并解压zip包
（Oracle GoldenGate V11.2.1.0.3 for Oracle 11g on Linux x86-64.zip）
[oracle@host01 stage]$ unzip Oracle\ GoldenGate\ V11.2.1.0.3\ for\ Oracle\ 11g\ on\ Linux\ x86-64.zip
2、创建软件安装目录，并将软件解压到相应的目录（host01,host02同样操作）
host01:
[oracle@host01 ~]$ mkdir -p /u01/app/ogg/11.2.0/
[oracle@host01 ~]$chmod 775 /u01/app/ogg -R
[oracle@host01 stage]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/ogg/11.2.0/
host02:
[oracle@host02 ~]$ mkdir -p /u01/app/ogg/11.2.0/
[oracle@host02 ~]$chmod 775 /u01/app/ogg -R
[oracle@host02 stage]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/ogg/11.2.0/
3、设置oracle账户环境变量（用于运行ogg）
host01:
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH
alias alert_log='tail -f /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log'
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/ogg/11.2.0
alias ggsci='/u01/app/ogg/11.2.0/ggsci'
source .bash_profile
登录OGG，只需要执行create subdirs命令 创建ogg管理目录。
GGSCI (host01) 1> show all
Parameter settings:
SET SUBDIRS ON
SET DEBUG OFF
Current directory: /u01/app/ogg/11.2.0
Using subdirectories for all process files
Editor: vi
Reports (.rpt) /u01/app/ogg/11.2.0/dirrpt
Parameters (.prm) /u01/app/ogg/11.2.0/dirprm
Stdout (.out) /u01/app/ogg/11.2.0/dirout
Replicat Checkpoints (.cpr) /u01/app/ogg/11.2.0/dirchk
Extract Checkpoints (.cpe) /u01/app/ogg/11.2.0/dirchk
Process Status (.pcs) /u01/app/ogg/11.2.0/dirpcs
SQL Scripts (.sql) /u01/app/ogg/11.2.0/dirsql
Database Definitions (.def) /u01/app/ogg/11.2.0/dirdef
host02:
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=ORCLOGG
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/ogg/11.2.0
alias ggsci='/u01/app/ogg/11.2.0/ggsci'
source .bash_profile
登录OGG，只需要执行create subdirs命令 创建ogg管理目录。
GGSCI (host02) 1> show all
Parameter settings:
SET SUBDIRS ON
SET DEBUG OFF
Current directory: /u01/app/ogg/11.2.0
Using subdirectories for all process files
Editor: vi
Reports (.rpt) /u01/app/ogg/11.2.0/dirrpt
Parameters (.prm) /u01/app/ogg/11.2.0/dirprm
Stdout (.out) /u01/app/ogg/11.2.0/dirout
Replicat Checkpoints (.cpr) /u01/app/ogg/11.2.0/dirchk
Extract Checkpoints (.cpe) /u01/app/ogg/11.2.0/dirchk
Process Status (.pcs) /u01/app/ogg/11.2.0/dirpcs
SQL Scripts (.sql) /u01/app/ogg/11.2.0/dirsql
Database Definitions (.def) /u01/app/ogg/11.2.0/dirdef
五、设置源目数据强制日志和附加补充日志选项(用于记录数据改变信息能够准确的传输到备库)
host01:
SQL> select force_logging,supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> select force_logging,supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
YES YES
tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
ORCLOGG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLOGG)
)
)
tnsping orcl
tnsping orclogg
host02:
SQL> select force_logging,supplemental_log_data_min from v$database;
FOR SUPPLEME
--- --------
YES YES
tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
ORCLOGG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLOGG)
)
)
tnsping orcl
tnsping orclogg
六、配置源目ogg管理表空间及需要传输数据表（源表有数据，目标表只有结构，没有数据）
host01:
SQL> create tablespace ogg datafile '/oradata/ORCL/ogg01.dbf' size 50m autoextend on ;
Tablespace created.
SQL> create user ogg identified by ogg default tablespace ogg temporary tablespace TEMP;
User created.
SQL> grant dba to ogg; #这里赋予权限最大，防止测试过程中报错。可有针对不同的对象开通不同的权限。
Grant succeeded.
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> conn scott/tiger
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT
[oracle@host01 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 1 11:39:10 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table empnew as select *from emp;
Table created.
SQL> alter table empnew add constraint pk_empnew_empno primary key (empno);
Table altered.
host02:
SQL> create tablespace ogg datafile '/oradata/ORCLOGG/ogg01.dbf' size 50m autoextend on ;
Tablespace created.
SQL> create user ogg identified by ogg default tablespace ogg temporary tablespace TEMP;
User created.
SQL> grant dba to ogg; #这里赋予权限最大，防止测试过程中报错。可有针对不同的对象开通不同的权限。
Grant succeeded.
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> conn scott/tiger
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT
[oracle@host02 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 1 11:39:10 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table empnew as select *from emp where 1=2;（只要表，不需要数据）
Table created.
SQL> alter table empnew add constraint pk_empnew_empno primary key (empno);
Table altered.
七、初始化表数据（源目数据一致）
1、配置ogg管理进程 mgr
host01:
edit params mgr
PORT 7809
PURGEOLDEXTRACTS /u01/app/ogg/11.2.0/dirdat, USECHECKPOINTS
GGSCI (host01) 4> start mgr
Manager started.
GGSCI (host01) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
host02:
GGSCI (host02) 5> edit params mgr
PORT 7809
PURGEOLDEXTRACTS /u01/app/ogg/11.2.0/dirdat, USECHECKPOINTS
GGSCI (host02) 6> start mgr
GGSCI (host02) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
2、配置extract捕获进程
GGSCI (host01) 1> dblogin userid ogg ,password ogg
Successfully logged into database.
GGSCI (host01) 2> add trandata scott.empnew
Logging of supplemental redo data enabled for table SCOTT.EMPNEW.
GGSCI (host01) 3> info trandata scott.empnew
Logging of supplemental redo log data is enabled for table SCOTT.EMPNEW.
Columns supplementally logged for table SCOTT.EMPNEW: EMPNO.
GGSCI (host01) 4> edit params eini_1
EXTRACT EINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
USERID ogg, PASSWORD ogg
RMTHOST host02, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINI_1
TABLE scott.*;
[oracle@host01 ~]$ 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 (host01) 1> add extract eini_1,sourceistable #(直接从源端库捕获数据到目标库)
EXTRACT added.
GGSCI (host01) 2> info eini_1
EXTRACT EINI_1 Initialized 2021-08-01 12:32 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
3、replicat复制进程
REPLICAT RINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
ASSUMETARGETDEFS
USERID ogg, PASSWORD ogg
DISCARDFILE /u01/app/ogg/11.2.0/dirrpt/RINIaa.dsc, PURGE
MAP scott.*, TARGET scott.*;
GGSCI (host02) 6> add replicat RINI_1,specialrun
REPLICAT added.
GGSCI (host02) 7> info rini_1
REPLICAT RINI_1 Initialized 2021-08-01 12:37 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN
4、启动host01 eini_1进程，同步数据
GGSCI (host01) 3> start eini_1
Sending START request to MANAGER ...
EXTRACT EINI_1 starting
GGSCI (host01) 4> info eini_1
EXTRACT EINI_1 Initialized 2021-08-01 12:32 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
GGSCI (host01) 10> info eini_1
EXTRACT EINI_1 Last Started 2021-08-01 12:40 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SCOTT.EMPNEW
2021-08-01 12:40:29 Record 14
Task SOURCEISTABLE
[oracle@host02 ~]$ 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 (host02) 1> view report rini_1
Report at 2021-08-01 12:40:34 (activity since 2021-08-01 12:40:29)
From Table SCOTT.EMPNEW to SCOTT.EMPNEW:
# inserts: 14
# updates: 0
# deletes: 0
# discards: 0
CACHE OBJECT MANAGER statistics
检查host01和host02库数据对比：
host01:
SQL> select count(*) from empnew;
COUNT(*)
----------
14
host02:
SQL> select count(*) from empnew;
COUNT(*)
----------
14
八、配置自动同步数据（增量数据）
1、配置./GLOBALS (为了主机宕机，网络中断等情况增加检查点，待主机恢复后，数据继续同步)主机host01，host02都需要配置
host01:
GGSCI (host01) 1> edit params ./GLOBALS
CHECKPOINTTABLE ogg.ggschkpt
GGSCI (host01) 1> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (host01) 2> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...
Successfully created checkpoint table ogg.ggschkpt.
此时用OGG登录数据库，就可以看到OGG创建的检查点表。
[oracle@host01 ~]$ sqlplus ogg/ogg
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 1 12:56:47 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from tab where tname like 'GGSCHKPT%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
GGSCHKPT TABLE
GGSCHKPT_LOX TABLE
host02:
GGSCI (host02) 1> edit params ./GLOBALS
CHECKPOINTTABLE ogg.ggschkpt
GGSCI (host02) 1> exit
GGSCI (host02) 1> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (host02) 2> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...
Successfully created checkpoint table ogg.ggschkpt.
此时用OGG登录数据库，就可以看到OGG创建的检查点表。
[oracle@host02 ~]$ sqlplus ogg/ogg
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 1 12:56:47 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from tab where tname like 'GGSCHKPT%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
GGSCHKPT TABLE
GGSCHKPT_LOX TABLE
2、配置源端捕获日志数据放在本地exttrail
GGSCI (host01) 1> edit params EORA_1
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/ogg/11.2.0/dirdat/aa
TABLE scott.*;
增加捕获进程：
GGSCI (host01) 2> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (host01) 7> ADD EXTTRAIL /u01/app/ogg/11.2.0/dirdat/aa, EXTRACT EORA_1, MEGABYTES 5
EXTTRAIL added.
#捕获进程获取数据放在本地exttrial日志文件。
GGSCI (host01) 3> info eora_1
EXTRACT EORA_1 Initialized 2021-08-01 13:02 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:21 ago)
Log Read Checkpoint Oracle Redo Logs
2021-08-01 13:02:20 Seqno 0, RBA 0
SCN 0.0 (0)
3、配置pump进程，传出日志到备机
GGSCI (host01) 1> edit params PORA_1
EXTRACT PORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
PASSTHRU
RMTHOST host02.example.com, MGRPORT 7809
RMTTRAIL /u01/app/ogg/11.2.0/dirdat/pa
TABLE scott.*;
增加PUMP进程，使用本地exttrial日志文件
GGSCI (host01) 8> ADD EXTRACT PORA_1, EXTTRAILSOURCE /u01/app/ogg/11.2.0/dirdat/aa
EXTRACT added.
增加pump进程PORA_1指定将本地TRAIL文件传输到目标端，保存目标端trail文件文件名称
GGSCI (host01) 10> info PORA_1
EXTRACT PORA_1 Initialized 2021-08-01 15:25 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:04:32 ago)
Log Read Checkpoint File /u01/app/ogg/11.2.0/dirdat/aa000000
First Record RBA 0
4、启动进程EXTRACT PUMP进程 EORA_1 、PORA_1
GGSCI (host01) 12> start EORA_1
Sending START request to MANAGER ...
EXTRACT EORA_1 starting
GGSCI (host01) 13> START PORA_1
Sending START request to MANAGER ...
EXTRACT PORA_1 starting
GGSCI (host01) 14> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 02:29:33 00:00:05
EXTRACT RUNNING PORA_1 00:00:00 00:06:07
此时就可以在源端和目标端看到各自的trail文件
源端：
[oracle@host01 ~]$ ll /u01/app/ogg/11.2.0/dirdat/
total 4
-rw-rw-rw- 1 oracle oinstall 1046 Aug 1 15:31 aa000000
目标端：
[oracle@host02 ~]$ ll /u01/app/ogg/11.2.0/dirdat/
total 0
-rw-rw-rw- 1 oracle oinstall 0 Aug 1 15:32 pa000000
5、配置目标端同步进程RORA_1
（host02）
GGSCI (host02) 1> ADD REPLICAT RORA_1 exttrail /u01/app/ogg/11.2.0/dirdat/pa checkpointtable ogg.GGSCHKPT
REPLICAT added.
编辑同步进程参数：
GGSCI (host02) 2> EDIT PARAMS RORA_1
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS ASS
UMETARGETDEFS
DISCARDFILE /u01/app/ogg/11.2.0/dirrpt/RORA_aa.DSC, PURGE
MAP scott.*, TARGET scott.*;
GGSCI (host02) 3> info rora_1
REPLICAT RORA_1 Initialized 2021-08-01 15:38 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:03:01 ago)
Log Read Checkpoint File /u01/app/ogg/11.2.0/dirdat/pa000000
First Record RBA 0
启动进程：
GGSCI (host02) 4> start rora_1
Sending START request to MANAGER ...
REPLICAT RORA_1 starting
GGSCI (host02) 5> info rora_1
REPLICAT RORA_1 Last Started 2021-08-01 15:41 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint File /u01/app/ogg/11.2.0/dirdat/pa000000
First Record RBA 0
6、验证同步，查看进程
host01:
GGSCI (host01) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:01
EXTRACT RUNNING PORA_1 00:00:00 00:00:04
SQL> select count(*) from empnew;
COUNT(*)
----------
14
SQL> delete from empnew where empno=7934;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from empnew;
COUNT(*)
----------
13
host02:
GGSCI (host02) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RORA_1 00:00:00 00:00:06
SQL> select count(*) from empnew;
COUNT(*)
----------
14
SQL> select count(*) from empnew;
COUNT(*)
----------
13
插入操作：
host01:
SQL> insert into empnew values(8888,'Damon','DBA',7788,sysdate,800,100,10);
1 row created.
SQL> insert into empnew values(8881,'Damon','DBA',7788,sysdate,800,100,10);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*)from empnew;
COUNT(*)
----------
15
host02:
SQL> select count(*) from empnew;
COUNT(*)
----------
15
到此为止，单表同步完成，能看到实际同步效果。
后记：1、目前对ogg认识水平属于刚入门，还有许多特性未掌握 如：一对多 多对多 单向，双向复制等；
2、目前感觉ogg只能在表级别进行同步，不能做到所有元素的同步，使用局限性比较大，同时配置时比较负载，排错难；
3、上面列出的知识点，将在后面文章中写出来，如有不对的地方请指出，谢谢。
4、本文参考了https://blog.csdn.net/jolly10/article/details/82704692作者的。
2021-7-31 hfzhouji
1591
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
