目录
阅读导航：
1 在源端和目标端配置数据库支持GoldenGate
2 在源端和目标端创建和配置GoldenGate实例
3 在源端创建和配置主抽取进程(Primary Extract)
4 在源端创建和配置Data Pump进程(Secondly Extract)
5 在目标端创建和配置Replicat进程
一、配置数据库支持GoldenGate（源端、目标端都要执行）
1. OGG用户和权限分配
GoldenGate需要从在线日子或归档日志抽取捕获系统的变更数据信息，这些信息可能来源于业务用户，可能来源于系统用户，
为了使GoldenGate能够抽取这些数据应为GoldenGate创建独立的用户和分配必要的权限以满足系统运行需求，
这些权限包括读取业务用户表数据的权限、读取系统表的权限、执行某个系统包的权限等，以下创建GoldenGate用户和角色
源端用户：ogg_src
目标端用户：ogg_trg
源端和目标端角色：ogg_role。
1.1 ogg_role权限一样(源端和目标端角色名称一样):
SQL> create role ogg_role; SQL> grant CREATE SESSION,ALTER SESSION,ALTER SYSTEM,RESOURCE,SELECT ANY DICTIONARY,FLASHBACK ANY TABLE,SELECT ANY TABLE, SELECT ANY TRANSACTION,insert any table,update any table,drop any table,CREATE TABLE to ogg_role; SQL> grant SELECT on dba_clusters to ogg_role; SQL> grant SELECT on V_$DATABASE to ogg_role; SQL> grant select on sys.logmnr_buildlog to ogg_role; SQL> grant EXECUTE on DBMS_FLASHBACK to ogg_role; SQL> grant execute on DBMS_CAPTURE_ADM to ogg_role; SQL> grant execute on DBMS_STREAMS to ogg_role; SQL> grant EXECUTE_CATALOG_ROLE to ogg_role;
1.2 源端用户(创建OGG专用用户，为实现ogg同步做准备，与业务用户分开；此用户可以做测试）：
[oracle@ogg1 ogg]$ sqlplus / as sysdba SQL> create tablespace tbs_ogg datafile '/data/u01/app/oracle/oradata/tbs_ogg01.dbf' size 10M autoextend on next 10M; SQL> create user ogg_src identified by ogg_src default tablespace tbs_ogg; SQL> grant dba to ogg_src; SQL> grant ogg_role to ogg_src;
1.3 目标端用户(创建OGG专用用户，为实现ogg同步做准备，与业务用户分开；此用户可以做测试）：
[oracle@ogg2 ogg]$ sqlplus / as sysdba SQL> create tablespace tbs_ogg datafile '/data/u01/app/oracle/oradata/tbs_ogg01.dbf' size 10M autoextend on next 10M; SQL> create user ogg_trg identified by ogg_trg default tablespace tbs_ogg; SQL> grant dba to ogg_trg; SQL> grant ogg_role to ogg_trg;
1. 4 源端用户测试表和数据:
create table ogg_src.togg(id primary key,name,type,CREATED,update_date) as select object_id,object_name,object_type,CREATED,sysdate from dba_objects where rownum<1001
1.5 将database的archivelog日志开启；
SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; SQL> archive log list;
2.数据库附加日志
附加日志级别分为:
数据库级别的附加日志(必须附加日志)
用户级别的附加日志(当使用OGG DDL抽取功能时要启用该级别)
表级别的附加日志(必须附加日志，除非你启用了用户级别的附加日志)
2.1 数据库级别的附加日志（启用最低数据库级补充日志记录）
Oracle强烈建议将Oracle源数据库置于强制日志记录模式。强制日志记录模式强制记录所有事务和负载，从而覆盖任何用户或存储设置。这可确保不会错过Extract配置中的源数据。
此外，使用Oracle GoldenGate时，Oracle源数据库需要最少的补充日志记录（数据库级选项）。这会将行链接信息（如果存在）添加到重做日志以进行更新操作。
注意：强烈建议不要使用数据库级主键（PK）和唯一索引（UI）日志记录，因为它会在复制之外的表上创建过多的额外开销。除非出于业务目的需要这些日志记录选项，否则只需在数据库级别启用最少的补充日志记录并强制记录Oracle GoldenGate。
因为GoldenGate需要抽取捕获变更数据信息和元数据信息，这些信息需要记录日志，并且因为日志总是持续增长的，这些日志增长的信息对于GoldenGate是必须记录的，所以必须在GoldenGate进程启动前开启数据库级别的附加日志；
检查数据库是否开启附加日志:
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database; SUPPLEME FOR -------- --- NO NO
开启数据库级别的附加日志（源端开启）:
SQL>alter database add supplemental log data; SQL>ALTER DATABASE FORCE LOGGING; SQL>alter system set enable_goldengate_replication=true scope=both; --（源端和目标端都要开启） SQL>alter system switch logfile; SQL> SELECT supplemental_log_data_min, force_logging FROM v$database; SUPPLEME FOR -------- --- YES YES
2.2用户级别的附加日志（源端开启）
Oracle强烈建议使用用户级日志记录而不是表级日志记录，因为它确保在满足通配符规范时捕获添加到用户的任何新表。
开启用户级别的附加日志的管理用户必须具有执行DBMS_CAPTURE_ADM包的权限；可以在GoldenGate GGSCI命令行下登录到数据库管理用户为其它的用户开启附加日志；
在GGSCI命令行下使用DBLOGIN命令登录到数据库管理用户
GGSCI (ORACLEOGG1) 1> dblogin userid ogg_src,password ogg_src Successfully logged into database.
为用户启用附加日志
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 3> add schematrandata ogg_src 2019-07-19 09:28:49 INFO OGG-01788 SCHEMATRANDATA has been added on schema "ogg_src". 2019-07-19 09:28:49 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "ogg_src". 2019-07-19 09:28:49 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "ogg_src". 2019-07-19 09:28:51 INFO OGG-10471 ***** Oracle Goldengate support information on table OGG_SRC.TOGG ***** Oracle Goldengate support native capture on table OGG_SRC.TOGG. Oracle Goldengate marked following column as key columns on table OGG_SRC.TOGG: ID.
取消用户附加日志
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 4> delete schematrandata ogg_src 2019-07-19 09:30:53 INFO OGG-01792 SCHEMATRANDATA has been deleted on schema "ogg_src". 2019-07-19 09:30:53 INFO OGG-01979 SCHEMATRANDATA for scheduling columns has been deleted on schema "ogg_src".
2.3表级别的附加日志（源端开启）
在没有启用用户级别的附加日志的情况下，表级别的主键或唯一索引附加日志对于GoldenGate是必须的；
在某些情况下即使你启用了用户级别的附加日志，你也可以启用表级别的附加日志使主键附加日志替代每一个在用户级别为GoldenGate指定的键；
必须在表没有主键或唯一索引的情况下启用表级别的附加日志。
在GGSCI命令行下使用dblogin登录到数据库管理用户
GGSCI (ORACLEOGG1) 1> dblogin userid ogg_src,password ogg_src Successfully logged into database.
为表启用附加日志
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 3> add trandata ogg_src.togg Logging of supplemental redo data enabled for table ogg_src.TOGG. TRANDATA for scheduling columns has been added on table 'ogg_src.TOGG'.
注意:仅当该表有主键或唯一索引时使用上面的命令启用表级别的附加日志，如果表中没有主键或唯一索引时则必须指定一个或多个或者全部列做为主键，在GoldenGate这些键的作用是过滤重复的数据。
SQL>create table ogg_src.togg_nokey(id,name,type,CREATED,update_date) as select object_id,object_name,object_type,CREATED,sysdate from dba_objects where rownum<1001 Table created.
在没有主键并且没有指定主键时启用表级别附加日志，默认所有列做为主键:
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 7> add trandata ogg_src.togg_nokey 2019-07-19 09:55:18 WARNING OGG-06439 No unique key is defined for table TOGG_NOKEY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. 2019-07-19 09:55:18 INFO OGG-15130 No key found for table OGG_SRC.TOGG_NOKEY. All viable columns will be logged. 2019-07-19 09:55:18 INFO OGG-15132 Logging of supplemental redo data enabled for table OGG_SRC.TOGG_NOKEY. 2019-07-19 09:55:18 INFO OGG-15133 TRANDATA for scheduling columns has been added on table OGG_SRC.TOGG_NOKEY. 2019-07-19 09:55:18 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table OGG_SRC.TOGG_NOKEY. 2019-07-19 09:55:18 INFO OGG-10471 ***** Oracle Goldengate support information on table OGG_SRC.TOGG_NOKEY ***** Oracle Goldengate support native capture on table OGG_SRC.TOGG_NOKEY. Oracle Goldengate marked following column as key columns on table OGG_SRC.TOGG_NOKEY: ID, NAME, TYPE, CREATED, UPDATE_DATE No unique key is defined for table OGG_SRC.TOGG_NOKEY.
删除表级别附加日志（示例）
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 8> delete trandata ogg_src.togg_nokey 2019-07-19 09:56:14 INFO OGG-15142 Logging of supplemental redo log data disabled for table OGG_SRC.TOGG_NOKEY. 2019-07-19 09:56:14 INFO OGG-15139 TRANDATA for scheduling columns has been disabled on table OGG_SRC.TOGG_NOKEY.
在没有主键并且指定主键列的情况下启用表级别附加日志:
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 9> add trandata ogg_src.togg_nokey,cols (ID),nokey 2019-07-19 09:58:57 WARNING OGG-06439 No unique key is defined for table TOGG_NOKEY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. 2019-07-19 09:58:58 INFO OGG-15132 Logging of supplemental redo data enabled for table OGG_SRC.TOGG_NOKEY. 2019-07-19 09:58:58 INFO OGG-15133 TRANDATA for scheduling columns has been added on table OGG_SRC.TOGG_NOKEY. 2019-07-19 09:58:58 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table OGG_SRC.TOGG_NOKEY. 2019-07-19 09:58:58 INFO OGG-10471 ***** Oracle Goldengate support information on table OGG_SRC.TOGG_NOKEY ***** Oracle Goldengate support native capture on table OGG_SRC.TOGG_NOKEY. Oracle Goldengate marked following column as key columns on table OGG_SRC.TOGG_NOKEY: ID, NAME, TYPE, CREATED, UPDATE_DATE No unique key is defined for table OGG_SRC.TOGG_NOKEY.
二、配置GoldenGate
1.MGR进程(源端、目标端在安装OGG时，已经安装了MGR)
MGR进程管理启动Oracle GoldenGate进程、启动动态进程、分配端口给GoldenGate进程、管理trail file、创建事件，错误和诊断报告工作，必须在第一时间启动；当某些原因导致GoldenGate崩溃或重启机器时，默认情况MGR是没有启动
[oracle@ORACLEOGG1 ogg_src]$ gssci GGSCI (ORACLEOGG1) 1> info mgr Manager is DOWN!
1.1配置GoldenGate MGR进程（源端和目标端都需要配置，源端端口配置7809，目标端端口配置7909）
可以通过直接编辑GoldenGate_home/dirprm/mgr.prm文件或进入GGSCI命令行后键入edit param mgr命令回车进入MGR配置文件vi编辑界面;
源端：
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) > edit param mgr PORT 7809 DYNAMICPORTLIST 7810-7820 AUTOSTART EXTRACT * AUTORESTART EXTRACT *,RETRIES 4, WAITMINUTES 4 PURGEOLDEXTRACTS /data/u01/app/oracle/product/ogg_src/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 10 --清除老化/过期数据 STARTUPVALIDATIONDELAY 5
目标端：
GGSCI (ORACLEOGG1 as ogg_trg@orclogg2) > edit param mgr PORT 7909 DYNAMICPORTLIST 7910-7920 AUTOSTART REPLICAT * AUTORESTART REPLICAT *,RETRIES 4, WAITMINUTES 4 PURGEOLDEXTRACTS /data/u01/app/oracle/product/ogg_trg/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 10 --清除老化/过期数据 STARTUPVALIDATIONDELAY 5
| 参数 | 说明 |
|---|---|
| PORT | 表示MGR进程端口号 |
| DYNAMICPORTLIST | 表示MGR进程动态为其它进程如Extract进程、Replicat进程分配的端口，可以是具体端口号或区间值 |
| AUTOSTART、AUTORESTART | 表示当MGR进程启动后失败时自动启动或重启的GoldenGate进程 |
1.2 启动mgr进程
GGSCI (ORACLEOGG1) 2> start mgr Manager started. GGSCI (ORACLEOGG1) 3> info mgr Manager is running (IP port sywu.7809, Process ID 17400).
启动原理:通过读取GoldenGate_home/dirprm/mgr.prm文件，然后根据该文件的配置信息启动进程分配端口号，如果该进程启动失败，首先请检查预使用的端口是否被占用:
netstat -lntup|grep 7809 tcp 0 0 :::7809 :::* LISTEN 32426/./mgr
然后检查相关的配置文件或重新配置MGR。
2.在源端创建和配置Extract进程
创建和配置Extract进程的工作有:
（1）创建和配置主抽取进程(Primary Extract)
（2）创建和配置Data Pump进程(Secondly Extract)
2.1 创建主抽取进程(Primary Extract)
进入GGSCI命令行使用add extract 命令创建主抽取进程
[oracle@ORACLEOGG1 ogg_src]$ ggsci GGSCI (ORACLEOGG1) 1> dblogin userid ogg_src,password ogg_src GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 2> add extract esydb001,tranlog,begin now EXTRACT added.
因为主进程的作用是抽取捕获系统变更数据并将这些数据保存到trail文件里，所以必须为其配置trail文件目录和trail文件名的两个字符名，trail文件名共11个字符，其余9个字符由GoldenGate系列填充;
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 14> ADD EXTTRAIL /data/u01/app/oracle/product/ogg_src/dirdat/es, EXTRACT esydb001 EXTTRAIL added.
主进程需要注册：
GGSCI (ORACLEOGG1) 11> dblogin userid ogg_src,password ogg_src Successfully logged into database. GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 12> register extract esydb001 database
2.1.1配置主抽取进程(Primary Extract)参数
GGSCI (ORACLEOGG1 as ogg_src@orclogg1) 15> edit param esydb001 extract esydb001 SETENV(ORACLE_SID="orclogg1") SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg_src, password ogg_src EXTTRAIL /data/u01/app/oracle/product/ogg_src/dirdat/es table ogg_src.togg;
userid指定GoldenGate 抽取用户的用户名和密码；
SETENV 设置环境变量，如实例名、数据库字符集；
table 指定抽取的用户和表名，如果指定多个以相同字符开头或结尾的表名，可以使用“前缀* ”的方式代替，这里仅以ogg_owner.togg表为测试案例;
2.2 创建和配置Data Pump进程(Secondly Extract)
添加Data Pump Extract进程时要注意，如果源端和目标端OGG物理路径都相同，可以按照常规方式处理。
2.2.1 创建Data Pump Extract进程
GGSCI (ORACLEOGG1) 1> ADD EXTRACT PSYDB001, EXTTRAILSOURCE /data/u01/app/oracle/product/ogg_src/dirdat/es EXTRACT added.
EXTTRAILSOUCE 指定源端的trail路径，必须包含两个字符，这个路径和主抽取进程(Primary Extract)中指定的trail目录和trail文件命名必须相同，因为Data Pump进程要读取主抽取进程生成的trail文件;
2.2.2 配置Data Pump Extract进程
GGSCI (ORACLEOGG1) 5> edit param psydb001 extract psydb001 SETENV(ORACLE_SID="orclogg1") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg_src,password ogg_src RMTHOST ogg_trg,mgrport 7909 --注意：此处ogg_trg需要在/etc/hosts设置对应目标端IP地址 RMTTRAIL /data/u01/app/oracle/product/ogg_trg/dirdat/ps --这里指定目标端trail文件位置，同样包含两个字符 table ogg_src.togg;
RMTHOST 指定目标端地址和端口等信息；
RMTTRAIL 指定目标端保存trail文件的目录和两个字符文件名；
TABLE 指定同步的表，配置的方式同在主抽取进程(Primary Extract)的配置一样，但这里多了许多额外的功能，比如实现数据过滤和其它复杂操作；
2.2.3 将目标端trail文件添加到队列中
源端可以配置多个主抽取进程，也可以配置多个Data Pump进程，但必须为每个要同步的目标端配置一个Data Pump进程；
GGSCI (ORACLEOGG1) 1> ADD RMTTRAIL /data/u01/app/oracle/product/ogg_trg/dirdat/ps, EXTRACT PSYDB001 RMTTRAIL added.
2.3在目标端配置Replicat
目标端需要做的工作:
（1）创建和配置Checkpoint Table
（2）创建和配置Replicat进程
2.3.1 创建和配置Checkpoint Table
目标端Replicat进程监控Checkpoint Table，每一条接收到的checkpoint数据由Collector后台进程写入到Checkpoint Table，因为对于事务来说每一条checkpoint数据要么执行成功要么执行失败，所以Replicat进程确保每一条checkpoint事务数据只应用一次，即使其它进程错误或者数据库错误。
在目标端GSSCI命令行下使用dblogin登录到目标端OGG管理用户
[oracle@ORACLEOGG2 ogg_trg]$ ggsci GGSCI (ORACLEOGG2) 1> dblogin userid ogg_trg,password ogg_trg
创建checkpoint table
GGSCI (ORACLEOGG2 as ogg_trg@orclogg2) 2> add checkpointtable ogg_trg.ogg_chk Successfully created checkpoint table ogg_trg.ogg_chk.
将checkpoint table 定义在GoldenGate配置文件里
GGSCI (ORACLEOGG2) 1> edit param ./GLOBALS GGSCHEMA OGG_TRG CHECKPOINTTABLE OGG_TRG.OGG_CHK
首次编辑GoldenGate配置文件时，GLOBALS文件以大写命名并且没有扩展名，创建在GoldenGate_home根目录。
2.3.2 创建和配置Replicat进程
当GoldenGate运行时会产生一些与同步数据不相关的文件，这些文件记录了GoldenGate进程错误、数据库错误、GoldenGate操作等信息，这个文件叫做Discard file，配置Replicat进程时由DISCARDFILE参数定义，这个参数是可选的，但推荐创建相应的目录并配置，方便诊断GoldenGate错误问题；
创建Discard file目录
[oracle@ORACLEOGG2 ogg_trg]$$ mkdir /data/u01/app/oracle/product/ogg_trg/discrd [oracle@ORACLEOGG2 ogg_trg]$ chmod 775 /data/u01/app/oracle/product/ogg_trg/discrd/
目标端创建Replicat进程
GGSCI (ORACLEOGG2) 1> add replicat rsydb001,exttrail /data/u01/app/oracle/product/ogg_trg/dirdat/ps,checkpointtable OGG_TRG.OGG_CHK REPLICAT added.
编辑Replicat进程配置文件
GGSCI (ORACLEOGG2) 2> edit param rsydb001 REPLICAT rsydb001 SETENV(ORACLE_SID="orclogg2") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg_trg,password ogg_trg DISCARDFILE /data/u01/app/oracle/product/ogg_trg/discrd/reptr.desc,append,megabytes 512 ALLOWNOOPUPDATES ASSUMETARGETDEFS MAP OGG_SRC.TOGG,target OGG_TRG.TOGG;
到这里简单的源端和目标端的MGR、Extract、Replicat进程就配置完了，测试配置文件是否错误，进程启动是否正常，如果进程有问题就使用“view report 进程名”查看错误；不要将所有进程启动，因为有些工作还没有完成。
三、初始化数据（目标端执行）
为什么要初始化数据？
因为DML操作包括INSERT、UPDATE、DELETE、SELECT操作，而在这些操作中UPDATE、DELETE操作Redo只记录了变更的数据列以及行ID(ROWID)，GoldenGate抽取数据后将其转换为自己的格式发送都目标端。
假设源端有100条数据，从08:00 AM开始同步到目标端，而在同步开始前目标端没有初始化数据(目标端为空数据)，那么08:00 AM后事物产生的UPDATE、DELETE DML操作发送到目标端，目标端GoldenGate Replicat进程会因为找不到数据而报错从而导致Replicat进程崩溃停止(ABENDED)，所以这就需要我们在同步前初始化数据，初始化完后再同步，这样大大降低错误率。
同步数据的方式可以通过DBLINK、EXP/IMP、SQLLDR或者表空间迁移等方式同步；我初始的数据是在dba_objects表中复制，所以目标端也如此创建即可。
create table ogg_trg.togg(id primary key,name,type,CREATED,update_date) as select object_id,object_name,object_type,CREATED,sysdate from dba_objects where rownum<1001
初始化数据链接：
https://www.topunix.com/post-392.html（OGG介绍——初始化数据——基于SCN的初始化数据）
四、使用GoldenGate
数据初始化后，分别启动
源端：MGR进程、主抽取进程(Primary Extract)、Data Pump进程(Secondly Extract)
目标端：MGR进程、Replicat进程；
4.1 启动源端GoldenGate进程
GGSCI (ORACLEOGG1) 40> start mgr Manager started. GGSCI (ORACLEOGG1) 41> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESYDB001 00:00:00 00:00:09 EXTRACT RUNNING PSYDB001 00:00:00 00:00:09
4.2启动目标端GoldenGate进程
GGSCI (ORACLEOGG2) 56> start mgr Manager started. Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RSYDB001 00:00:00 00:00:08
4.3 进程重启完毕，观察源端和目标端trail文件生成情况
GGSCI (ORACLEOGG1) 67> info ESYDB001,detail EXTRACT ESYDB001 Last Started 2019-06-21 14:34 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:01 ago) Process ID 25820 Log Read Checkpoint Oracle Redo Logs 2019-06-21 15:07:28 Seqno 12, RBA 111716864 SCN 0.2076784 (2076784) Target Extract Trails: Trail Name Seqno RBA Max MB Trail Type /data/u01/app/oracle/product/ogg_src/dirdat/es 16 2404 500 EXTTRAIL Extract Source Begin End /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:34 2019-06-21 15:07 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:25 2019-06-21 14:34 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:25 2019-06-21 14:34 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:21 2019-06-21 14:25 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:21 2019-06-21 14:25 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:02 2019-06-21 14:21 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:02 2019-06-21 14:21 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:00 2019-06-21 14:02 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 14:00 2019-06-21 14:02 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 13:58 2019-06-21 14:00 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-21 13:58 2019-06-21 14:00 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 16:47 2019-06-21 13:58 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 16:47 2019-06-21 13:58 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 16:46 2019-06-19 16:48 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 16:46 2019-06-19 16:48 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 15:45 2019-06-19 16:47 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 15:45 2019-06-19 16:47 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 15:15 2019-06-19 15:46 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 15:15 2019-06-19 15:46 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:53 2019-06-19 15:16 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:53 2019-06-19 15:16 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:49 2019-06-19 14:54 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:49 2019-06-19 14:54 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:44 2019-06-19 14:50 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:44 2019-06-19 14:50 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:42 2019-06-19 14:44 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:42 2019-06-19 14:44 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:41 2019-06-19 14:43 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 14:41 2019-06-19 14:43 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 13:52 2019-06-19 14:42 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 13:52 2019-06-19 14:42 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 09:25 2019-06-19 13:52 /data/u01/app/oracle/oradata/ORCLOGG1/onlinelog/o1_mf_3_gjg4hc1j_.log 2019-06-19 09:25 2019-06-19 13:52 Not Available * Initialized * 2019-06-19 09:25 Not Available * Initialized * 2019-06-19 09:25 Not Available * Initialized * 2019-06-19 09:25 Current directory /data/u01/app/oracle/product/ogg_src Report file /data/u01/app/oracle/product/ogg_src/dirrpt/ESYDB001.rpt Parameter file /data/u01/app/oracle/product/ogg_src/dirprm/esydb001.prm Checkpoint file /data/u01/app/oracle/product/ogg_src/dirchk/ESYDB001.cpe Process file Error log /data/u01/app/oracle/product/ogg_src/ggserr.log GGSCI (ORACLEOGG2) 68> info PSYDB001,detail EXTRACT PSYDB001 Last Started 2019-06-21 14:35 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:06 ago) Process ID 25828 Log Read Checkpoint File /data/u01/app/oracle/product/ogg_src/dirdat/es000000016 2019-06-21 14:54:55.000000 RBA 2404 Target Extract Trails: Trail Name Seqno RBA Max MB Trail Type /data/u01/app/oracle/product/ogg_trg/dirdat/ps 3 2537 500 RMTTRAIL Extract Source Begin End /data/u01/app/oracle/product/ogg_src/dirdat/es000000016 2019-06-21 14:26 2019-06-21 14:54 /data/u01/app/oracle/product/ogg_src/dirdat/es000000015 2019-06-21 13:55 2019-06-21 14:26 /data/u01/app/oracle/product/ogg_src/dirdat/es000000010 2019-06-19 16:39 2019-06-21 13:55 /data/u01/app/oracle/product/ogg_src/dirdat/es000000008 2019-06-19 16:39 2019-06-19 16:39 /data/u01/app/oracle/product/ogg_src/dirdat/es000000008 * Initialized * 2019-06-19 16:39 /data/u01/app/oracle/product/ogg_src/dirdat/es000000000 * Initialized * First Record Current directory /data/u01/app/oracle/product/ogg_src Report file /data/u01/app/oracle/product/ogg_src/dirrpt/PSYDB001.rpt Parameter file /data/u01/app/oracle/product/ogg_src/dirprm/psydb001.prm Checkpoint file /data/u01/app/oracle/product/ogg_src/dirchk/PSYDB001.cpe Process file Error log /data/u01/app/oracle/product/ogg_src/ggserr.log REPLICAT RSYDB001 Last Started 2019-06-19 16:48 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Process ID 1183 Log Read Checkpoint File /data/u01/app/oracle/product/ogg_trg/dirdat/ps000000003 2019-06-21 14:34:57.054950 RBA 1689 Current Log BSN value: (requires database login) Last Committed Transaction CSN value: (requires database login) Extract Source Begin End /data/u01/app/oracle/product/ogg_trg/dirdat/ps000000003 2019-06-19 16:39 2019-06-21 14:34 /data/u01/app/oracle/product/ogg_trg/dirdat/ps000000000 * Initialized * 2019-06-19 16:39 /data/u01/app/oracle/product/ogg_trg/dirdat/ps000000000 * Initialized * First Record Current directory /data/u01/app/oracle/product/ogg_trg Report file /data/u01/app/oracle/product/ogg_trg/dirrpt/RSYDB001.rpt Parameter file /data/u01/app/oracle/product/ogg_trg/dirprm/rsydb001.prm Checkpoint file /data/u01/app/oracle/product/ogg_trg/dirchk/RSYDB001.cpr Checkpoint table OGG_TRG.OGG_CHK Process file Error log /data/u01/app/oracle/product/ogg_trg/ggserr.log
4.4 源端进程信息
GGSCI (ORACLEOGG1) 68> stats esydb001 Sending STATS request to EXTRACT ESYDB001 ... No active extraction maps. GGSCI (ORACLEOGG1) 69> stats psydb001 Sending STATS request to EXTRACT PSYDB001 ... No active extraction maps.
4.5 目标端进程信息
GGSCI (ORACLEOGG2) 68> stats rsydb001 Sending STATS request to REPLICAT RSYDB001 ... No active replication maps.
五、数据增删模拟测试
5.1查看源端和目标端的表数据
SQL> select count(*) from ogg_src.togg; COUNT(*) ---------- 1008 SQL> select count(*) from ogg_trg.togg; COUNT(*) ---------- 1008
5.1在源端产生DML操作
SQL> insert into ogg_src.togg(id,name,type) values(1013,'sywu','user'); 1 row created. Commit complete.
5.2 查看源端进程状态(esydb001,psydb001)
GGSCI (ORACLEOGG1) 72> stats esydb001 Sending STATS request to EXTRACT ESYDB001 ... Start of Statistics at 2019-06-21 14:55:28. Output to /data/u01/app/oracle/product/ogg_src/dirdat/es: Extracting from OGG_SRC.TOGG to OGG_SRC.TOGG: *** Total statistics since 2019-06-21 14:54:56 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2019-06-21 14:54:56 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2019-06-21 14:54:56 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2019-06-21 14:54:56 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. GGSCI (ORACLEOGG1) 73> stats psydb001 Sending STATS request to EXTRACT PSYDB001 ... Start of Statistics at 2019-06-21 14:57:37. Output to /data/u01/app/oracle/product/ogg_trg/dirdat/ps: Extracting from OGG_SRC.TOGG to OGG_SRC.TOGG: *** Total statistics since 2019-06-21 14:54:58 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2019-06-21 14:54:58 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2019-06-21 14:54:58 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2019-06-21 14:54:58 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 End of Statistics.
5.3 查看目标端进程状态
GGSCI (ORACLEOGG2) 13> stats rsydb001 Sending STATS request to REPLICAT RSYDB001 ... Start of Statistics at 2019-06-21 14:58:51. Replicating from OGG_SRC.TOGG to OGG_TRG.TOGG: *** Total statistics since 2019-06-21 14:55:00 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2019-06-21 14:55:00 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2019-06-21 14:55:00 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2019-06-21 14:55:00 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 End of Statistics.
5.4 数据稽查（源端、目标端）
源端： SQL> select count(*) from ogg_src.togg; COUNT(*) ---------- 1009 SQL> select * from ogg_src.togg where id =1013; ID NAME TYPE -------------------------------------- 1013 sywu user 目标端： SQL> select count(*) from ogg_trg.togg; COUNT(*) ---------- 1009 SQL> select * from ogg_trg.togg where id =1013; ID NAME TYPE -------------------------------------- 1013 sywu user
六、总结
GoldenGate通过自身的数据加工、处理、发送、应用以及借用RDBMS数据库系统的优势保证数据的可同步性和准确性，并在错误和故障发生的情况下以RDBMS系统特有的特性(如Oracle 内部同步变更号(SCN)和检查点)保障了数据的可恢复性和一致性，为同步的数据实时性和准确性提供了准确性和可稽查性。
文章评论