转载

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命令行下登录到数据库管理用户为其它的用户开启附加日志；1.2.2.1 在GGSCI命令行下使用DBLOGIN命令登录到数据库管理用户:

GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner Successfully logged into database.

1.2.2.2 为其它用户启用附加日志:

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指定的键；必须在表没有主键或唯一索引的情况下启用表级别的附加日志。1.2.3.1 在GGSCI命令行下使用dblogin登录到数据库管理用户

GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner Successfully logged into database.

1.2.3.2 为表启用附加日志

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操作发送到目标端，目标端OGG 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)和检查点)保障了数据的可恢复性和一致性，为同步的数据实时性和准确性提供了准确性和可稽查性。

正文到此结束