Oracle GoldenGate 二、配置和使用
配置和使用GoldenGate的步骤
- 1 在源端和目标端配置数据库支持GoldenGate
- 2 在源端和目标端创建和配置GoldenGate实例
- 3 在源端创建和配置主抽取进程(Primary Extract)
- 4 在源端创建和配置Data Pump进程(Secondly Extract)
- 5 在目标端创建和配置Replicat进程
1 配置数据库支持GoldenGate
1.1 OGG用户和权限分配
GoldenGate需要从在线日子或归档日志抽取捕获系统的变更数据信息，这些信息可能来源于业务用户，可能来源于系统用户，为了使GoldenGate能够抽取这些数据应为GoldenGate创建独立的用户和分配必要的权限以满足系统运行需求，这些权限包括读取业务用户表数据的权限、读取系统表的权限、执行某个系统包的权限等，以下脚步创建GoldenGate用户ogg_owner(源用户)、ogg_trg(目标用户)和GoldenGate角色ogg_role:
[oracle@sywu ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 21 14:11:04 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@sydb>create tablespace tbs01
datafile '+oradata'
size 10m
autoextend on
uniform size 2m
/
create user ogg_owner identified by ogg_owner default tablespace tbs01 quota unlimited on tbs01
/
create user ogg_trg identified by ogg_trg default tablespace tbs01 quota unlimited on tbs01
/
create role ogg_role
/
为易管理和维护统一将权限赋予角色ogg_role:
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;
grant SELECT on dba_clusters to ogg_role;
grant SELECT on V_$DATABASE to ogg_role;
grant select on sys.logmnr_buildlog to ogg_role;
grant EXECUTE on DBMS_FLASHBACK to ogg_role;
grant execute on DBMS_CAPTURE_ADM to ogg_role;
grant execute on DBMS_STREAMS to ogg_role;
grant EXECUTE_CATALOG_ROLE to ogg_role;
然后再将ogg_role 角色赋予ogg_owner(源用户)和ogg_trg(目标用户):
grant ogg_role to ogg_owner;
grant ogg_role to ogg_trg;
源用户测试表和数据:
SYS@sydb>create table ogg_owner.togg(id primary key,name,type,CREATED,update_date)
2 as
3 select object_id,object_name,object_type,CREATED,sysdate from dba_objects
4 where rownum<1001
5 /
Table created.
Elapsed: 00:00:00.28
1.2 数据库附加日志
附加日志级别分为:
- 数据库级别的附加日志(必须附加日志)
- 用户级别的附加日志(当使用GoldenGate DDL抽取功能时要启用该级别)
- 表级别的附加日志(必须附加日志，除非你启用了用户级别的附加日志)
1.2.1 数据库级别的附加日志
因为GoldenGate需要抽取捕获变更数据信息和元数据信息，这些信息需要记录日志，并且因为日志总是持续增长的，这些日志增长的信息对于GoldenGate是必须记录的，所以必须在GoldenGate进程启动前开启数据库级别的附加日志；
检查数据库是否开启附加日志:
SYS@sydb>SELECT supplemental_log_data_min, force_logging FROM v$database;
SUPPLEME FOR
-------- ---
NO NO
开启数据库级别的附加日志:
SYS@sydb>alter database add supplemental log data;
SYS@sydb>ALTER DATABASE FORCE LOGGING;
SYS@sydb>SELECT supplemental_log_data_min, force_logging FROM v$database;
SUPPLEME FOR
-------- ---
YES YES
Elapsed: 00:00:00.00
SYS@sydb>alter system switch logfile;
System altered.
Elapsed: 00:00:00.09
1.2.2 用户级别的附加日志
开启用户级别的附加日志的管理用户必须具有执行DBMS_CAPTURE_ADM包的权限；可以在GoldenGate GGSCI命令行下登录到数据库管理用户为其它的用户开启附加日志；
在GGSCI命令行下使用DBLOGIN命令登录到数据库管理用户
GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner
Successfully logged into database.
为用户启用附加日志
GGSCI (sywu as ogg_owner@sydb) 2> add schematrandata sywu
2015-08-24 14:56:26 INFO OGG-01788 SCHEMATRANDATA has been added on schema sywu.
2015-08-24 14:56:27 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema sywu.
取消用户附加日志
GGSCI (sywu as ogg_owner@sydb) 6> delete schematrandata sywu
2015-08-25 20:14:49 INFO OGG-01792 SCHEMATRANDATA has been deleted on schema sywu.
2015-08-25 20:14:49 INFO OGG-01979 SCHEMATRANDATA for scheduling columns has been deleted on schema sywu.
1.2.3 表级别的附加日志
在没有启用用户级别的附加日志的情况下表级别的主键或唯一索引附加日志对于GoldenGate是必须的；在某些情况下即使你启用了用户级别的附加日志，你也可以启用表级别的附加日志使主键附加日志替代每一个在用户级别为GoldenGate指定的键；必须在表没有主键或唯一索引的情况下启用表级别的附加日志。
在GGSCI命令行下使用dblogin登录到数据库管理用户
GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner
Successfully logged into database.
为表启用附加日志
GGSCI (sywu as ogg_owner@sydb) 3> add trandata ogg_owner.togg
Logging of supplemental redo data enabled for table OGG_OWNER.TOGG.
TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG'.
注意:仅当该表有主键或唯一索引时使用上面的命令启用表级别的附加日志，如果表中没有主键或唯一索引时则必须指定一个或多个或者全部列做为主键，在GoldenGate这些键的作用是过滤重复的数据。
SYS@sydb>create table ogg_owner.togg_nokey(id,name,type,CREATED,update_date)
2 as
3 select object_id,object_name,object_type,CREATED,sysdate from dba_objects
where rownum<1001
5 /
Table created.
Elapsed: 00:00:00.11
在没有主键并且没有指定主键时启用表级别附加日志，默认所有列做为主键:
GGSCI (sywu as ogg_owner@sydb) 4> add trandata ogg_owner.togg_nokey
2015-08-24 16:05:01 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.
Logging of supplemental redo data enabled for table OGG_OWNER.TOGG_NOKEY.
TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG_NOKEY'.
在没有主键并且指定主键列的情况下启用表级别附加日志:
GGSCI (sywu as ogg_owner@sydb) 26> add trandata ogg_owner.togg_nokey,cols (ID),nokey
2015-08-24 16:33:19 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.
Logging of supplemental redo data enabled for table OGG_OWNER.TOGG_NOKEY.
TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG_NOKEY'.
2 配置GoldenGate
2.1 GoldenGate MGR进程
MGR进程管理启动Oracle GoldenGate进程、启动动态进程、分配端口给GoldenGate进程、管理trail file、创建事件，错误和诊断报告工作，必须在第一时间启动；当某些原因导致GoldenGate崩溃或重启机器时，默认情况MGR是没有启动
[oracle@sywu ogg_src]$ tggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (sywu) 1> info mgr
Manager is DOWN!
2.1.1 配置GoldenGate MGR进程
可以通过直接编辑GoldenGate_home/dirprm/mgr.prm文件或进入GGSCI命令行后键入edit param mgr命令回车进入MGR配置文件vi编辑界面;
GGSCI (sywu as ogg_owner@sydb) 10> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART ER E*
AUTORESTART ER P*,RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
| 参数 | 说明 |
|---|---|
| PORT | 表示MGR进程端口号 |
| DYNAMICPORTLIST | 表示MGR进程动态为其它进程如Extract进程、Replicat进程分配的端口，可以是具体端口号或区间值 |
| AUTOSTART、AUTORESTART | 表示当MGR进程启动后失败时自动启动或重启的GoldenGate进程 |
2.1.2 启动mgr进程
GGSCI (sywu) 2> start mgr
Manager started.
GGSCI (sywu) 3> info mgr
Manager is running (IP port sywu.7909, Process ID 17400).
启动原理:通过读取GoldenGate_home/dirprm/mgr.prm文件，然后根据该文件的配置信息启动进程分配端口号，如果该进程启动失败，首先请检查预使用的端口是否被占用:
netstat -lntup|grep 7809
tcp 0 0 :::7809 :::* LISTEN 32426/./mgr
然后检查相关的配置文件或重新配置MGR。
2.2 在源端创建和配置Extract进程
创建和配置Extract进程的工作有:
- 创建和配置主抽取进程(Primary Extract)
- 创建和配置Data Pump进程(Secondly Extract)
2.2.1 创建主抽取进程(Primary Extract)
进入GGSCI命令行使用add extract 命令创建主抽取进程
[oracle@sywu ~]$ sggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (sywu as ogg_owner@sydb) 13> add extract esydb001,tranlog,begin now
EXTRACT added.
因为主进程的作用是抽取捕获系统变更数据并将这些数据保存到trail文件里，所以必须为其配置trail文件目录和trail文件名的两个字符名，trail文件名共8个字符，其余6个字符由GoldenGate系列填充;
GGSCI (sywu as ogg_owner@sydb) 14> ADD EXTTRAIL /u01/app/product/ogg_src/dirdat/es, EXTRACT esydb001
EXTTRAIL added.
2.2.1.1 配置主抽取进程(Primary Extract)参数
GGSCI (sywu as ogg_owner@sydb) 15> edit param esydb001
extract esydb001
SETENV(ORACLE_SID="sydb")
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg_owner, password ogg_owner
EXTTRAIL /u01/app/product/ogg_src/dirdat/es
table ogg_owner.togg;
userid指定GoldenGate 抽取用户的用户名和密码；
SETENV 设置环境变量，如实例名、数据库字符集；
table 指定抽取的用户和表名，如果指定多个以相同字符开头或结尾的表名，可以使用“前缀* ”的方式代替，这里仅以ogg_owner.togg表为测试案例;
2.2.2 创建和配置Data Pump进程(Secondly Extract)
添加Data Pump Extract进程时要注意，如果源端和目标端OGG物理路径都相同，可以按照常规方式处理，但是如果不相同，就像我本例的一样源端和目标端都在同一台机器上，但GoldenGate物理路径不相同，请按如下方式处理
2.2.2.1 创建Data Pump Extract进程
GGSCI (sywu) 26> ADD EXTRACT PSYDB001, EXTTRAILSOURCE /u01/app/product/ogg_src/dirdat/es
EXTRACT added.
EXTTRAILSOUCE 指定源端的trail路径，必须包含两个字符，这个路径和主抽取进程(Primary Extract)中指定的trail目录和trail文件命名必须相同，因为Data Pump进程要读取主抽取进程生成的trail文件;
2.2.2.2 配置Data Pump Extract进程
edit param psydb001
extract psydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg_owner,password ogg_owner
RMTHOST sywu,mgrport 7909
RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps --这里指定目标端trail文件位置，同样包含两个字符
table ogg_owner.togg;
RMTHOST 指定目标端地址和端口等信息；
RMTTRAIL 指定目标端保存trail文件的目录和两个字符文件名；
TABLE 指定同步的表，配置的方式同在主抽取进程(Primary Extract)的配置一样，但这里多了许多额外的功能，比如实现数据过滤和其它复杂操作；
2.2.2.3 将目标端trail文件添加到队列中
源端可以配置多个主抽取进程，也可以配置多个Data Pump进程，但必须为每个要同步的目标端配置一个Data Pump进程；
GGSCI (sywu) 10> ADD RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps, EXTRACT PSYDB001
RMTTRAIL added.
2.3 在目标端配置Replicat
目标端需要做的工作:
- 创建和配置Checkpoint Table
- 创建和配置Replicat进程
2.3.1 创建和配置Checkpoint Table
目标端Replicat进程监控Checkpoint Table，每一条接收到的checkpoint数据由Collector后台进程写入到Checkpoint Table，因为对于事务来说每一条checkpoint数据要么执行成功要么执行失败，所以Replicat进程确保每一条checkpoint事务数据只应用一次，即使其它进程错误或者数据库错误。
在目标端GSSCI命令行下使用dblogin登录到目标端OGG管理用户
[oracle@sywu ogg_src]$ tggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (sywu) 1> dblogin userid ogg_trg,password ogg_trg
Successfully logged into database.
创建checkpoint table
GGSCI (sywu as ogg_trg@sydb) 2> add checkpointtable ogg_trg.ogg_chk
Successfully created checkpoint table ogg_trg.ogg_chk.
将checkpoint table 定义在GoldenGate配置文件里
GGSCI (sywu) 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@sywu ogg_trg]$ mkdir /u01/app/product/ogg_trg/discrd
[oracle@sywu ogg_trg]$ chmod 775 /u01/app/product/ogg_trg/discrd/
目标端创建Replicat进程
GGSCI (sywu) 27> add replicat rsydb001,exttrail /u01/app/product/ogg_trg/dirdat/ps,checkpointtable OGG_TRG.OGG_CHK
REPLICAT added.
编辑Replicat进程配置文件
REPLICAT rsydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg_trg,password ogg_trg
DISCARDFILE /u01/app/product/ogg_trg/discrd/reptr.desc,append,megabytes 512
ALLOWNOOPUPDATES
ASSUMETARGETDEFS
MAP OGG_OWNER.TOGG,target OGG_TRG.TOGG;
到这里简单的源端和目标端的MGR、Extract、Replicat进程就配置完了，测试配置文件是否错误，进程启动是否正常，如果进程有问题就使用“view report 进程名”查看错误；不要将所有进程启动，因为有些工作还没有完成。
3 初始化数据
为什么要初始化数据？
因为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或者表空间迁移等方式同步；我的测试都在同一个库所以就直接创建表了。
SYS@sydb>create table ogg_trg.togg as select * from ogg_owner.togg
2 /
Table created.
Elapsed: 00:00:00.24
4 使用GoldenGate
数据初始化后，分别启动源端MGR进程、主抽取进程(Primary Extract)、Data Pump进程(Secondly Extract)以及目标端MGR进程、Replicat进程；
启动源端GoldenGate进程
[oracle@sywu ~]$ sggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (sywu) 1> start mgr
Manager started.
GGSCI (sywu) 2> start PSYDB001
Sending START request to MANAGER ...
EXTRACT PSYDB001 starting
GGSCI (sywu) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ESYDB001 00:00:00 00:00:02
EXTRACT RUNNING PSYDB001 00:00:00 12:41:34
启动目标端GoldenGate进程
[oracle@sywu ~]$ tggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (sywu) 1> start mgr
Manager started.
GGSCI (sywu) 2> start RSYDB001
Sending START request to MANAGER ...
REPLICAT RSYDB001 starting
GGSCI (sywu) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RSYDB001 00:00:00 00:00:07
进程都启动完了，此时观察源端和目标端trail文件生成情况
[oracle@sywu ~]$ ls -ltr /u01/app/product/ogg_src/dirdat
total 324
-rw-r-----. 1 oracle oinstall 1418 Aug 26 09:42 es000000
-rw-r-----. 1 oracle oinstall 1481 Aug 26 09:48 es000001
-rw-r-----. 1 oracle oinstall 1481 Aug 26 09:50 es000002
-rw-r-----. 1 oracle oinstall 1481 Aug 26 09:52 es000003
-rw-r-----. 1 oracle oinstall 1481 Aug 26 09:52 es000004
[oracle@sywu ~]$ ls -ltr /u01/app/product/ogg_trg/dirdat
total 324
-rw-r-----. 1 oracle oinstall 193469 Aug 25 13:00 ps000000
-rw-r-----. 1 oracle oinstall 891 Aug 25 13:00 ps000001
-rw-r-----. 1 oracle oinstall 1652 Aug 25 13:07 ps000002
-rw-r-----. 1 oracle oinstall 891 Aug 25 13:07 ps000003
-rw-r-----. 1 oracle oinstall 105884 Aug 25 13:49 ps000004
-rw-r-----. 1 oracle oinstall 891 Aug 25 13:49 ps000005
-rw-r-----. 1 oracle oinstall 2095 Aug 26 09:54 ps000006
-rw-r-----. 1 oracle oinstall 1201 Aug 26 09:54 ps000007
-rw-r-----. 1 oracle oinstall 2032 Aug 26 09:54 ps000008
源端进程信息
GGSCI (sywu) 4> info ESYDB001,detail
EXTRACT ESYDB001 Last Started 2015-08-26 09:52 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Process ID 14619
Log Read Checkpoint Oracle Redo Logs
2015-08-26 09:58:11 Seqno 13, RBA 1942016
SCN 0.575310 (575310)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/u01/app/product/ogg_src/dirdat/es 4 1481 100 EXTTRAIL
Extract Source Begin End
/u01/oradata/sydb/redo01.log 2015-08-26 09:52 2015-08-26 09:58
/u01/oradata/sydb/redo01.log 2015-08-26 09:49 2015-08-26 09:52
/u01/oradata/sydb/redo01.log 2015-08-26 09:43 2015-08-26 09:49
/u01/oradata/sydb/redo01.log 2015-08-26 09:40 2015-08-26 09:43
/u01/oradata/sydb/redo01.log 2015-08-25 20:43 2015-08-26 09:40
Not Available * Initialized * 2015-08-25 20:43
Not Available * Initialized * 2015-08-25 20:43
Current directory /u01/app/product/ogg_src
Report file /u01/app/product/ogg_src/dirrpt/ESYDB001.rpt
Parameter file /u01/app/product/ogg_src/dirprm/esydb001.prm
Checkpoint file /u01/app/product/ogg_src/dirchk/ESYDB001.cpe
Process file /u01/app/product/ogg_src/dirpcs/ESYDB001.pce
Error log /u01/app/product/ogg_src/ggserr.log
GGSCI (sywu) 5> info PSYDB001,detail
EXTRACT PSYDB001 Last Started 2015-08-26 09:52 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 14648
Log Read Checkpoint File /u01/app/product/ogg_src/dirdat/es000004
2015-08-26 09:52:50.035063 RBA 1481
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/u01/app/product/ogg_trg/dirdat/ps 0 0 100 EXTTRAIL
Extract Source Begin End
/u01/app/product/ogg_src/dirdat/es000004 * Initialized * 2015-08-26 09:52
/u01/app/product/ogg_src/dirdat/es000000 * Initialized * First Record
/u01/app/product/ogg_src/dirdat/es000000 * Initialized * First Record
/u01/app/product/ogg_src/dirdat/es000000 * Initialized * First Record
/u01/app/product/ogg_src/dirdat/es000000 * Initialized * First Record
Current directory /u01/app/product/ogg_src
Report file /u01/app/product/ogg_src/dirrpt/PSYDB001.rpt
Parameter file /u01/app/product/ogg_src/dirprm/psydb001.prm
Checkpoint file /u01/app/product/ogg_src/dirchk/PSYDB001.cpe
Process file /u01/app/product/ogg_src/dirpcs/PSYDB001.pce
Error log /u01/app/product/ogg_src/ggserr.log
GGSCI (sywu) 6> stats ESYDB001
Sending STATS request to EXTRACT ESYDB001 ...
No active extraction maps.
GGSCI (sywu) 7> stats PSYDB001
Sending STATS request to EXTRACT PSYDB001 ...
No active extraction maps.
目标端进程信息
GGSCI (sywu) 5> info RSYDB001,detail
REPLICAT RSYDB001 Last Started 2015-08-26 09:55 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 15105
Log Read Checkpoint File /u01/app/product/ogg_trg/dirdat/ps000008
First Record RBA 2032
Current Log BSN value: (requires database login)
Last Committed Transaction CSN value: (requires database login)
Extract Source Begin End
/u01/app/product/ogg_trg/dirdat/ps000008 2015-08-26 09:52 First Record
/u01/app/product/ogg_trg/dirdat/ps000008 2015-08-25 13:58 2015-08-26 09:52
/u01/app/product/ogg_trg/dirdat/ps000006 2015-08-25 13:49 2015-08-25 13:58
/u01/app/product/ogg_trg/dirdat/ps000006 2015-08-25 13:49 2015-08-25 13:49
/u01/app/product/ogg_trg/dirdat/ps000006 2015-08-25 13:49 2015-08-25 13:49
/u01/app/product/ogg_trg/dirdat/ps000006 * Initialized * 2015-08-25 13:49
/u01/app/product/ogg_trg/dirdat/ps000006 * Initialized * First Record
/u01/app/product/ogg_trg/dirdat/ps000000 * Initialized * First Record
/u01/app/product/ogg_trg/dirdat/ps000000 * Initialized * First Record
/u01/app/product/ogg_trg/dirdat/ps000000 * Initialized * First Record
/u01/app/product/ogg_trg/dirdat/ps000000 * Initialized * First Record
Current directory /u01/app/product/ogg_trg
Report file /u01/app/product/ogg_trg/dirrpt/RSYDB001.rpt
Parameter file /u01/app/product/ogg_trg/dirprm/rsydb001.prm
Checkpoint file /u01/app/product/ogg_trg/dirchk/RSYDB001.cpr
Checkpoint table OGG_TRG.OGG_CHK
Process file /u01/app/product/ogg_trg/dirpcs/RSYDB001.pcr
Error log /u01/app/product/ogg_trg/ggserr.log
GGSCI (sywu) 6> stats RSYDB001
Sending STATS request to REPLICAT RSYDB001 ...
No active replication maps.
在源端产生DML操作
OGG_OWNER@sydb>insert into togg(id,name)values(1003,'tt') ;
1 row created.
Elapsed: 00:00:00.00
OGG_OWNER@sydb>insert into togg(id,name,type)values(1004,'sywu','user');
1 row created.
Elapsed: 00:00:00.01
OGG_OWNER@sydb>commit;
Commit complete.
Elapsed: 00:00:00.01
再次查看源端进程状态
GGSCI (sywu) 10> stats ESYDB001
Sending STATS request to EXTRACT ESYDB001 ...
Start of Statistics at 2015-08-26 10:04:54.
Output to /u01/app/product/ogg_src/dirdat/es:
Extracting from OGG_OWNER.TOGG to OGG_OWNER.TOGG:
*** Total statistics since 2015-08-26 10:04:33 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-08-26 10:04:33 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-08-26 10:04:33 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2015-08-26 10:04:33 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
GGSCI (sywu) 11> stats PSYDB001
Sending STATS request to EXTRACT PSYDB001 ...
Start of Statistics at 2015-08-26 10:04:58.
Output to /u01/app/product/ogg_trg/dirdat/ps:
Extracting from OGG_OWNER.TOGG to OGG_OWNER.TOGG:
*** Total statistics since 2015-08-26 10:04:35 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-08-26 10:04:35 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-08-26 10:04:35 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2015-08-26 10:04:35 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
再次查看目标端进程状态
GGSCI (sywu) 7> stats RSYDB001
Sending STATS request to REPLICAT RSYDB001 ...
Start of Statistics at 2015-08-26 10:05:03.
Replicating from OGG_OWNER.TOGG to OGG_TRG.TOGG:
*** Total statistics since 2015-08-26 10:04:45 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-08-26 10:04:45 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-08-26 10:04:45 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2015-08-26 10:04:45 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
数据稽查
SYS@sydb>select count(*) from ogg_owner.togg;
COUNT(*)
----------
1002
Elapsed: 00:00:00.00
SYS@sydb>select count(*) from ogg_trg.togg;
COUNT(*)
----------
1002
Elapsed: 00:00:00.05
SYS@sydb>column name format a10
SYS@sydb>select id,name,type from ogg_owner.togg where name='sywu';
ID NAME TYPE
---------- ---------- -------------------
1004 sywu user
Elapsed: 00:00:00.00
SYS@sydb>select id,name,type from ogg_trg.togg where name='sywu';
ID NAME TYPE
---------- ---------- -------------------
1004 sywu user
Elapsed: 00:00:00.01
5 总结
GoldenGate通过自身的数据加工、处理、发送、应用以及借用RDBMS数据库系统的优势保证数据的可同步性和准确性，并在错误和故障发生的情况下以RDBMS系统特有的特性(如Oracle 内部同步变更号(SCN)和检查点)保障了数据的可恢复性和一致性，为同步的数据实时性和准确性提供了准确性和可稽查性。
--The end(2015-08-26)