11g新特性:Streams同步捕获

11g中引入了新的Streams特性:Synchronous Capture同步捕获。不同于传统Streams的捕获机制,Synchronous Capture同步捕获不基于归档日志或者在线重做日志来捕捉数据库中的变化。取而代之,同步捕获使用另一种内部机制来捕捉数据库中的变化并对这些捕获的变化实施格式化,最终形成一条LCR(Logical change Record)。这些由同步捕获捕捉到得记录被称为persistent LCRs。同步捕捉总是将记录入列(enqueue)到一个提交时队列中(commit-time queue)以保证事务的顺序准确。

即便我们采用了同步捕捉来替代传统的捕捉方式,propagation和apply并不会有什么不同,这我们无需担忧。

同步捕获可以捕捉由UPDATE、INSERT、DELETE和MERGE四种DML语句产生的DML变化。因为其不依赖于重做和归档的特性,其甚至可以被用在非归档模式下,并且不要求打开任何追加日志。

同步捕捉可能适用于以下几种情景中:

  1. 无法使用基于重做(redo-based)的传统捕获方式(例如在Oracle database Standards Edition中)
  2. 无法使用重做或基于日志挖掘相关的功能,但可以用到其他Streams进程
  3. 要求在用户事务发生的同时执行捕获
  4. 捕获的变化被要求存放在磁盘上的队列中
  5. 克隆较少更新的表上的数据

同步捕获支持对以下数据类型列的DML变更捕捉:

  • VARCHAR2
  • NVARCHAR2
  • NUMBER
  • FLOAT
  • DATE
  • BINARY_FLOAT
  • BINARY_DOUBLE
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
  • RAW
  • CHAR
  • NCHAR
  • UROWID

需要注意的是不同于传统捕获方式,同步捕获不能使用如ADD_SCHEMA_RULES或ADD_GLOBAL_RULES存储过程建立。同步捕获仅能通过ADD_TABLE_RULES建立白名规则(table rule with a positive rule set),而不支持建立黑名规则(negative rule set)。

下面我们通过实例来体验一下该同步捕获新特性。

一、首先我们需要配置Streams所要求的软件环境和前提条件,这包括:

1.确保合理设置Streams相关的初始化参数,可以参考<How to setup Oracle Streams Bi-Directional>一文中Streams推荐的初始化参数的配置

2.在各个数据库中创建Streams管理员用户(一般为strmadmin)

[oracle@rh2 ~]$ export ORACLE_SID=MACLEAN
[oracle@rh2 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 6 15:54:51 2011

create user STRMADMIN identified by STRMADMIN
default tablespace USERS
quota unlimited on USERS;

Grant DBA TO STRMADMIN

begin
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
end;
/

GRANT SELECT ANY DICTIONARY TO STRMADMIN;
grant insert any table TO STRMADMIN;
grant update any table TO STRMADMIN;
grant delete any table TO STRMADMIN;
grant select any table TO STRMADMIN;
grant alter any table TO STRMADMIN;

grant read on directory data_pump_dir to strmadmin;
grant write on directory data_pump_dir to strmadmin;

3.在源端数据库(source database)以STRMADMIN使用sqlplus登录,并创建以STRMADMIN用户身份连接到目标端数据库中的数据库连接(database link):

SQL> conn strmadmin/STRMADMIN
Connected.

SQL> create database link DW connect to strmadmin identified by STRMADMIN using 'DW';
Database link created.

二、正式开始配置使用同步捕获的Streams
1.进一步确认以上配置的初始化参数、用户权限及数据库连接均已经到位

2.创建示例使用的用户模式以及模式下的示例用表,该步骤在源端和目标端都需要执行:

SQL> create  user user1 identified by user1  default tablespace  users  temporary tablespace temp;
User created.

SQL> grant connect,resource to user1;
Grant succeeded.

SQL> conn user1/user1
Connected.

SQL> create table TAB1(  empid number primary key,  name varchar2(10), comments varchar2(10) );
Table created.

3.以Streams管理员身份登录到源端数据库和目标端数据库中,执行以下创建队列(queue)的操作:

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.sync_queue_table',
queue_name => 'strmadmin.sync_cap_queue',
queue_user => 'user1');
END;
/

4.在目标端数据库中创建apply队列:

BEGIN
 DBMS_STREAMS_ADM.SET_UP_QUEUE(
 queue_table => 'strmadmin.sync_queue_table',
 queue_name => 'strmadmin.sync_apply_queue',
 queue_user => 'user1');
END;
/

5.在目标端数据库创建apply进程,但暂时不启动它:

BEGIN
 DBMS_APPLY_ADM.CREATE_APPLY(
 queue_name => 'strmadmin.sync_apply_queue',
 apply_name => 'sync_apply',
 apply_captured => FALSE);
END;
 /

6.在目标端数据库中为apply进程添加应用规则,该规则要求SYNC_APPLY应用进程将SYNC_APPLY_QUEUE队列中出现的所有DML变更apply到USER1.TAB1表上。

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'user1.tab1',
  streams_type => 'apply',
  streams_name => 'sync_apply',
  queue_name => 'strmadmin.sync_apply_queue',
  source_database => 'MACLEAN');                     /*此处填入源端数据库的global name */
  END;
  /

7.在源端数据库中创建propagation进程将捕获到的变更传播到目标端数据库中。使用ADD_TABLE_PROPAGATION_RULES存储过程创建该 propagation及其白名规则(positive rule set)。在源端数据库中以STRMADMIN身份登录,创建该propagation:

BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
 table_name => 'user1.tab1',
 streams_name => 'sync_prop',
 source_queue_name => 'strmadmin.sync_cap_queue',
 destination_queue_name => 'strmadmin.sync_apply_queue@DW',          /*@后面跟上连接到目标端数据库的dblink名字 */
 source_database => 'MACLEAN',                            /* 此处填入源端数据库的global name */
 queue_to_queue => TRUE);
END;
/

8.在源端数据库中创建同步捕获进程(synchronous capture),且仅捕捉USER1.TAB1表上的变更:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'user1.tab1',
  streams_type => 'sync_capture',
  streams_name => 'sync_capture',
  queue_name => 'strmadmin.sync_cap_queue');
END;
/

9.为目标端数据库中的USER1.TAB1表设置实例化SCN号(instantiation SCN):

DECLARE
  iscn NUMBER; -- Variable to hold instantiation SCN value
  BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DW(            /*@后面跟上连接到目标端数据库的dblink名字*/
  source_object_name => 'USER1.TAB1',
  source_database_name => 'MACLEAN',                                        /* 此处填入源端数据库的global name */
  instantiation_scn => iscn); 
END;

10.在目标端数据库中启动apply:

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
 apply_name => 'sync_apply');
END;
/

因为同步捕获进程会被隐式地启动,所以我们在这里无需去手动启动sync_capture进程。

同时我们无法从dba_capture视图中找到同步捕获进程的信息,因为它们记录在dba_sync_capture视图中:

SQL> set linesize 200 

SQL> select state , capture_name from v$streams_capture;

no rows selected

SQL> select status from dba_capture;

no rows selected

SQL> select capture_name, queue_name, rule_set_name, capture_user
  2  from dba_sync_capture;

CAPTURE_NAME                   QUEUE_NAME                     RULE_SET_NAME                  CAPTURE_USER
------------------------------ ------------------------------ ------------------------------ ------------------------------
SYNC_CAPTURE                   SYNC_CAP_QUEUE                 RULESET$_8                     STRMADMIN

可以通过以下SQL语句了解同步捕获所涉及的表名:

col streams_name for a20
col rule_name for a20

SELECT r.STREAMS_NAME,
       r.RULE_NAME,
       r.SUBSETTING_OPERATION,
       t.TABLE_OWNER,
       t.TABLE_NAME,
       t.ENABLED
  FROM DBA_STREAMS_TABLE_RULES r, DBA_SYNC_CAPTURE_TABLES t
 WHERE r.STREAMS_TYPE = 'SYNC_CAPTURE'
   AND r.TABLE_OWNER = t.TABLE_OWNER
   AND r.TABLE_NAME = t.TABLE_NAME;

STREAMS_NAME         RULE_NAME            SUBSET TABLE_OWNER                    TABLE_NAME                     ENA
-------------------- -------------------- ------ ------------------------------ ------------------------------ ---
SYNC_CAPTURE         TAB17                       USER1                          TAB1                           YES

也可以通过以下SQL语句查询了解那些列上不支持使用同步捕获(synchronous captures):

SELECT OWNER, TABLE_NAME, COLUMN_NAME, SYNC_CAPTURE_REASON
  FROM DBA_STREAMS_COLUMNS
 WHERE SYNC_CAPTURE_VERSION IS NULL;

OWNER                          TABLE_NAME                     COLUMN_NAME                    SYNC_CAPTURE_REASON
------------------------------ ------------------------------ ------------------------------ ---------------------------------------
IX                             AQ$_ORDERS_QUEUETABLE_T        MSGID                          AQ queue table
IX                             AQ$_ORDERS_QUEUETABLE_H        HINT                           AQ queue table
IX                             AQ$_ORDERS_QUEUETABLE_I        HINT                           AQ queue table
IX                             AQ$_ORDERS_QUEUETABLE_I        MSGID                          AQ queue table
IX                             AQ$_ORDERS_QUEUETABLE_I        MSG_LOCAL_ORDER_NO             AQ queue table
IX                             AQ$_STREAMS_QUEUE_TABLE_T      ACTION                         AQ queue table
IX                             AQ$_STREAMS_QUEUE_TABLE_H      NAME                           AQ queue table

11.测试并验证数据复制的情况

SQL> conn user1/user1
Connected.

SQL>  insert into tab1 values(8,'MACLEAN','PERFECT');
1 row created.

SQL> commit;
Commit complete.

/* 连接到目标端数据库并查询TAB1表 */

SQL> conn user1/user1@DW
Connected.

SQL> select * from tab1; 

     EMPID NAME       COMMENTS
---------- ---------- ----------
         8 MACLEAN    PERFECT

12.停止并清理Streams环境;注意以下步骤会将已有的Streams配置移除,因此在执行前应当保证数据库中没有配置其他Streams:
exec dbms_apply_adm.stop_apply('sync_apply');                                   /*目标端数据库 */
exec dbms_capture_adm.drop_capture('sync_capture',true);                   /*源端数据库    */
exec dbms_apply_adm.drop_apply('sync_apply',true);                            /*目标端数据库 */
exec dbms_streams_adm.remove_streams_configuration;                      /* both */

同时删除Streams管理员和示例所用用户模式:
drop user user1 cascade;
drop user strmadmin cascade;

Goldengate实现在线数据迁移

Goldengate的一大卖点就是可以实现在线的数据迁移,这意味着在整个过程中我们无需关闭数据库,甚至于无需停止应用。在<How to create a GoldenGate uni-directional target database in a production database zero downtime>中,大致介绍了实现零下线时间数据迁移所涉及的步骤,包括:

  1. 在source与target主机上完成OGG的初始化配置
  2. 创建extract及exttrail
  3. 创建pump
  4. 启动本地extract和pump
  5. 创建带有HANDLECOLLISIONS参数的replicat,但不启动它
  6. 完成数据的initial load即数据初始化
  7. 启动replicat
  8. 当replicat追上extract后取消replicat的HANDLECOLLISIONS参数

接下来我们通过以下实例来实践OGG的在线数据迁移:

SQL> conn clinic/clinic
Connected.

/* 以clinic为应用用户 */

SQL> create table tv (t1 int primary key,t2 int,t3 varchar2(30));
Table created.

/* 为别在source和target创建该测试用表,在这里我们先不考虑同步DDL*/

SQL> create sequence seqt1 start with 1 increment by 1;
Sequence created.

declare
  rnd number(9,2);
begin
   for i in 1..100000 loop
     insert into tv values(seqt1.nextval,i*dbms_random.value,'MACLEAN IS TESTING');
     commit;
   end loop;
end;
PL/SQL procedure successfully completed.

/* 在源端随机生成10w行数据 */

begin
   loop
    delete from tv where rownum=1;
     commit;
     insert into tv values(seqt1.nextval,200000*dbms_random.value,'MACLEAN IS UPDATING');
     commit;
	 insert into tv values(seqt1.nextval,300000*dbms_random.value,'MACLEAN IS UPDATING');
	 commit;
	update tv set t2=t2+10 where rownum=1;
	commit;
     dbms_lock.sleep(1);
     end loop;
end;

/* 以上匿名块可以造成简易的数据变化,它会在每1秒钟删除/更新/插入TV表的部分数据
    我们打开多个窗口执行以上匿名块,以模拟在线业务
*/

以上为测试前提工作,接下来我们正式配置OGG FOR ZERO DOWNTIME MIGRATION:

GGSCI (rh2.oracle.com) 3> dblogin userid maclean
Password:
Successfully logged into database.
GGSCI (rh2.oracle.com) 5> add trandata clinic.tv
Logging of supplemental redo data enabled for table CLINIC.TV.
/* 通过add trandata命令为tv表加上追加日志 */
GGSCI (rh2.oracle.com) 6> add extract load2,tranlog,begin now
EXTRACT added.
GGSCI (rh2.oracle.com) 7> add rmttrail /s01/new/ze,megabytes 200,extract load2
RMTTRAIL added.
GGSCI (rh2.oracle.com) 10> view params load2
extract load2
userid maclean, password AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD,encryptkey default
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTTRAIL /s01/new/ze
Table clinic.tv;
/* 为了让整个过程更简洁,我们在这里不适用pump */
GGSCI (rh2.oracle.com) 9> start load2
Sending START request to MANAGER ...
EXTRACT LOAD2 starting
GGGSCI (rh2.oracle.com) 11> stats load2
Sending STATS request to EXTRACT LOAD2 ...
Start of Statistics at 2010-12-11 20:00:44.
Output to /s01/new/ze:
Extracting from CLINIC.TV to CLINIC.TV:
*** Total statistics since 2010-12-11 20:00:32 ***
Total inserts                              370.00
Total updates                              185.00
Total deletes                              185.00
Total discards                               0.00
Total operations                           740.00
*** Daily statistics since 2010-12-11 20:00:32 ***
Total inserts                              370.00
Total updates                              185.00
Total deletes                              185.00
Total discards                               0.00
Total operations                           740.00
*** Hourly statistics since 2010-12-11 20:00:32 ***
Total inserts                              370.00
Total updates                              185.00
Total deletes                              185.00
Total discards                               0.00
Total operations                           740.00
*** Latest statistics since 2010-12-11 20:00:32 ***
Total inserts                              370.00
Total updates                              185.00
Total deletes                              185.00
Total discards                               0.00
Total operations                           740.00
End of Statistics.
/* 以上完成了extract的配置和启动,接下来我们配置target上的replicat */
GGSCI (rh3.oracle.com) 39> add replicat rep2,begin now,exttrail /s01/new/ze,checkpointtable maclean.checkpoint
REPLICAT added.
GGSCI (rh3.oracle.com) 42> view params rep2
replicat rep2
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/clinic.log,append,megabytes 10
HANDLECOLLISIONS
map clinic.tv, target clinic.tv;
/* 注意为replicat加上HANDLECOLLISIONS参数,
用以处理可能的deplicate records和missed records;
该参数的具体定义见Reference文档
*/ 
/* 注意在配置完replicat后,不要去启动它;直到完成initial load才能启动*/
/* 接下来要配置initial load使用的extract和replicat */
GGSCI (rh2.oracle.com) 14> add extract einit,sourceistable
EXTRACT added.
/* 以sourceistable选项创建extract */
GGSCI (rh2.oracle.com) 17> view params einit
extract einit
SOURCEISTABLE
userid maclean, password maclean
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTFILE /s01/new/clinictv
Table clinic.tv;
/* 之后我们需要在命令行界面下调用该extract */
[maclean@rh2 gg]$ pwd
/home/maclean/gg
[maclean@rh2 gg]$ extract paramfile dirprm/einit.prm
2010-12-11 20:03:06  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 15:39:00
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-12-11 20:03:06
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Mon Mar 29 22:10:29 EDT 2010, Release 2.6.18-194.el5
Node: rh2.oracle.com
Machine: x86_64
soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited
Process id: 21399
Description:
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
extract einit
SOURCEISTABLE
2010-12-11 20:03:06  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
userid maclean, password *******
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTFILE /s01/new/clinictv
Table clinic.tv;
Using the following key columns for source table CLINIC.TV: T1.
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                                8G
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):          16G
CACHESIZEMAX (strict force to disk):  13.99G
Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Database Language and Character Set:
NLS_LANG environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
2010-12-11 20:03:06  INFO    OGG-01478  Output file /s01/new/clinictv is using format RELEASE 10.4/11.1.
2010-12-11 20:03:11  INFO    OGG-01226  Socket buffer size set to 27985 (flush size 27985).
Processing table CLINIC.TV
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Report at 2010-12-11 20:03:12 (activity since 2010-12-11 20:03:06)
Output to /s01/new/clinictv:
From Table CLINIC.TV:
#                   inserts:    100780
#                   updates:         0
#                   deletes:         0
#                  discards:         0
/* 以上extract操作在target上RMTFILE指定的路径生成了导出文件*/
[maclean@rh3 new]$ pwd
/s01/new
[maclean@rh3 new]$ file clinictv
clinictv: data
[maclean@rh3 new]$ strings clinictv |head -20
uri:rh2:oracle:com:6
/s01/new/clinictv7
Linux1
rh2.oracle.com2
2.6.18-194.el53
##1 SMP Mon Mar 29 22:10:29 EDT 20104
x86_642
CLINICA2
clinica3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
EINIT1
Version 11.1.1.0.0 Build 0784
CLINIC.TV
MACLEAN IS TESTINGZ
CLINIC.TV
MACLEAN IS TESTINGZ
/* 接下来我们要在target上配置initial load使用的replicat,以导入之前的EXTFILE */
GGSCI (rh3.oracle.com) 12> add replicat rinit,specialrun
REPLICAT added.
GGSCI (rh3.oracle.com) 17> view params rinit
replicat rinit
SPECIALRUN
userid maclean,password maclean
EXTFILE /s01/new/clinictv
ASSUMETARGETDEFS
map clinic.tv, target clinic.tv;
/ *之后我们在命令行上调用该replicat */
[maclean@rh3 gg]$ replicat paramfile dirprm/rinit.prm 
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 15:58:11
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-12-11 20:06:14
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Mon Mar 29 22:22:00 EDT 2010, Release 2.6.18-194.el5xen
Node: rh3.oracle.com
Machine: x86_64
soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited
Process id: 18554
Description: 
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
replicat rinit
SPECIALRUN
userid maclean,password *******
EXTFILE /s01/new/clinictv
ASSUMETARGETDEFS
map clinic.tv, target clinic.tv;
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                              512M
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):           1G
CACHESIZEMAX (strict force to disk):    881M
Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Database Language and Character Set:
NLS_LANG environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1" 
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
Opened trail file /s01/new/clinictv at 2010-12-11 20:06:14
MAP resolved (entry CLINIC.TV):
map CLINIC.TV, target clinic.tv;
Using following columns in default map by name:
T1, T2, T3
Using the following key columns for target table CLINIC.TV: T1.
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name :  /s01/new/clinictv
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    52 (x0034)    IO Time    : 2010-12-11 20:03:10.969955
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :          0       AuditPos   : 0
Continued  :     N  (x00)     RecCount   :     1  (x01)
2010-12-11 20:03:10.969955 Insert             Len    52 RBA 11244940
Name: CLINIC.TV
___________________________________________________________________
Reading /s01/new/clinictv, current RBA 11245052, 100780 records
Report at 2010-12-11 20:06:54 (activity since 2010-12-11 20:06:14)
From Table CLINIC.TV to CLINIC.TV:
#                   inserts:    100780
#                   updates:         0
#                   deletes:         0
#                  discards:         0
Last log location read:
FILE:      /s01/new/clinictv
RBA:       11245052
TIMESTAMP: 2010-12-11 20:03:10.969955
EOF:       NO
READERR:   400
SQL> select count(*) from tv;
COUNT(*)
----------
100780
/* 以上完成了initial load所要求的工作,接着我们打开同步使用的replicat*/
GGSCI (rh3.oracle.com) 3> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (rh3.oracle.com) 7> lag rep2
Sending GETLAG request to REPLICAT REP2 ...
Last record lag: 4 seconds.
At EOF, no more records to process.
GGSCI (rh3.oracle.com) 9> stats rep2
Sending STATS request to REPLICAT REP2 ...
Start of Statistics at 2010-12-11 20:10:15.
Replicating from CLINIC.TV to CLINIC.TV:
*** Total statistics since 2010-12-11 20:09:54 ***
Total inserts                              204.00
Total updates                              102.00
Total deletes                              102.00
Total discards                               0.00
Total operations                           408.00
*** Daily statistics since 2010-12-11 20:09:54 ***
Total inserts                              204.00
Total updates                              102.00
Total deletes                              102.00
Total discards                               0.00
Total operations                           408.00
*** Hourly statistics since 2010-12-11 20:09:54 ***
Total inserts                              204.00
Total updates                              102.00
Total deletes                              102.00
Total discards                               0.00
Total operations                           408.00
*** Latest statistics since 2010-12-11 20:09:54 ***
Total inserts                              204.00
Total updates                              102.00
Total deletes                              102.00
Total discards                               0.00
Total operations                           408.00
End of Statistics.
/* 当replicat追上extract后,需要取消HANDLECOLLISIONS参数 */
GGSCI (rh3.oracle.com) 11> view params rep2
replicat rep2
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/clinic.log,append,megabytes 10
map clinic.tv, target clinic.tv;
GGSCI (rh3.oracle.com) 12> stop rep2
Sending STOP request to REPLICAT REP2 ...
Request processed.
GGSCI (rh3.oracle.com) 13> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting

接下来停止模拟的在线业务,进行数据对比。首先使用Goldengate veridata创建group及compare pair等配置:


接着生成job,并执行:

Job运行的结果界面:

对比发现同步正常的报告:

***********************************************************************
Oracle GoldenGate Veridata Server
Version 3.0.0.6
Build 002
Copyright (C) 2004, 2009, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-12-11 20:15:58
***********************************************************************
Unexpected Windows major version
Process id: 6340, Thread id : 6392
Group: clinic (1) Compare Pairs
Performing 1 comparisons ...
Compare pair: TV=TV
2010-12-11 20:15:58. Submitted (1 of 1) Compare Pair (TV=TV) RunId (runid=(1007, 1, 1)).
2010-12-11 20:16:00. Completed in-sync. Compare Pair (TV=TV). (0) Remaining
Compare Pairs with Errors: 0
Compare Pairs with Out-Of-Sync: 0
Compare Pairs without Out-Of-Sync: 1
Compare Pairs Cancelled: 0
Group terminated normally.

接着我们在target端执行一些人工修改数据的SQL,以达到不同步的效果:

begin
loop
delete from tv where rownum=1;
commit;
insert into tv values(seqt1.nextval,200000*dbms_random.value,'MACLEAN IS UPDATING');
commit;
insert into tv values(seqt1.nextval,300000*dbms_random.value,'MACLEAN IS UPDATING');
commit;
update tv set t2=t2+10 where rownum=1;
commit;
dbms_lock.sleep(1);
end loop;
end;
/
/* 在源端打开模拟的应用程序 */
/* 在target插入一条额外的纪录 */
SQL> insert into tv values(99993242,1213,'OUT OF SYNC');
1 row created.
SQL> commit;
Commit complete.

再次运行Goldengate veridata中相关的job,可以在job相关的日志中看到详细的out-of-sync纪录信息:

Profile: newp
General:
***********************************************************************
Out-Of-Sync Output Format: binary
Maximum Size of Each
Out-Of-Sync XML Chunk (Rows): 500
Output in-sync rows to OOS
file: false
Output in-sync after in-flight
rows to OOS file: true
Report in-sync rows to report
file: false
Report in-sync after in-flight
rows to report file: false
Sorting Method:
Sort Data Using: database
Maximum Memory Usage (MB): 50
Number Of Concurrent Threads: 4
Temporary Storage Directory
for Source Data:
Temporary Storage Directory
for Target Data:
NSort Memory Retry Limit: 0
NSort Memory Retry Wait Interval: 0
Initial Compare (General):
***********************************************************************
Max Concurrent Comparison
Threads: 4
Terminate when Maximum Records
Out-Of-Sync: 100000
Output Out-Of-Sync Record
Details to Report File: false
Update Report file Every
(seconds): 0
Update Report file Every
Threads: 0
Terminate when Maximum Records
Out-Of-Sync: 100000
Output Out-Of-Sync Record
Details to Report File: false
Update Report file Every
(seconds): 0
Update Report file Every
(records): 0
Limit Number of Input Rows: 0
Delta processsing enabled: false
Initial Compare (Event Reporting):
***********************************************************************
Generate Messages: both
Generate Warning Messages For
Out-Of-Sync Rows After
(differences): 9999
Initial Compare (Agent):
***********************************************************************
Use Static Listening Port For
For Agent During Row Hash
On Source: 0
Use Static Listening Port For
For Agent During Row Hash
On Target: 0
Initial Compare (NonStop Process):
***********************************************************************
Source Process Name Starting
With:
Source Process CPU Number: -1
Source Process Priority: 0
Target Process Name Starting
With:
Target Process CPU Number: -1
Target Process Priority: 0
Confirm-Out-Of-Sync (General):
***********************************************************************
Perform Confirm Out-Of-Sync
Step: true
Run Concurrently With
Initial Compare: true
Delay Confirm-Out-Of-Sync
By (seconds): 0
Terminate when Maximum Records
Out-Of-Sync: 100000
Output Out-Of-Sync Record
Details to Report File: true
Update Report file
Every (seconds): 0
Update Report file
Every (records): 0
Confirm-Out-Of-Sync (Event Reporting):
***********************************************************************
Generate Messages: both
Generate Warning Messages For
For Out-Of-Sync Rows
After (differences): 9999
Confirm-Out-Of-Sync (Agent):
***********************************************************************
Use Static Listening Port For
Agent During Row Hash
On Source: 0
Use Static Listening Port For
Agent During Row Hash
On Target: 0
Confirm-Out-Of-Sync (NonStop Process):
***********************************************************************
Source Process Name Starting
With:
Source Process CPU Number: -1
Source Process Priority: 0
Target Process Name Starting
With:
Target Process CPU Number: -1
Target Process Priority: 0
Starting  Veriagt session for source rowhash at 2010-12-11 20:32:25.
Initial Compare Source Agent Information:
ORACLE_HOME : /s01/10gdb
NLS_LANG (from environment) :
ORA_SDTZ :
NLS_LANG (from DBMS) : AMERICAN_AMERICA.WE8ISO8859P1
NLS_LENGTH_SEMANTICS : BYTE
SESSIONTIMEZONE : -05:00
Process ID : 21473
Operating System : Linux
Architecture : x86_64
Report File : /home/maclean/veridata/agent/dirver/report/maclean_PC_4150_000010110000000100000001___source_rh.rpt
Trace File : /home/maclean/veridata/agent/dirver/trace/maclean_PC_4150_000010110000000100000001___source_rh.trc
Starting  Veriagt session for target rowhash at 2010-12-11 20:32:26.
Initial Compare Target Agent Information:
ORACLE_HOME : /s01/db_1
NLS_LANG (from environment) :
ORA_SDTZ :
NLS_LANG (from DBMS) : AMERICAN_AMERICA.WE8ISO8859P1
NLS_LENGTH_SEMANTICS : BYTE
SESSIONTIMEZONE : +08:00
Process ID : 18639
Operating System : Linux
Architecture : x86_64
Report File : /home/maclean/veridata/agent/dirver/report/maclean_PC_4150_000010110000000100000001___target_rh.rpt
Trace File : /home/maclean/veridata/agent/dirver/trace/maclean_PC_4150_000010110000000100000001___target_rh.trc
Processing first rowhash block from source at 2010-12-11 20:32:26.
Processing first rowhash block from target at 2010-12-11 20:32:27.
Comparing first row at 2010-12-11 20:32:27.
Starting  Veriagt session for source COOS at 2010-12-11 20:32:27.
Performance Statistics for source Rowhash at 2010-12-11 20:32:27.
rows: 103959
duration (secs): 00:00:02
rows/sec: 51979.50
row bytes: 4345147
row bytes/sec: 2172573
bytes/row: 41
rh bytes/row: 23
rows skipped: 0
blocks skipped: 0
hash comp rate: 0.55
total comp rate: 0.23
pct time fetching: 15.44
pct time waiting: 5.91
time until first row: 00:00:00
ipc msgs: 12
ipc bytes: 2809974
bytes/msg: 234164
compressed bytes/msg: 99262
bytes/sec: 1404987
compressed bytes/sec: 595572
msg compression ratio: 0.42
*** Summarizing Initial Row Comparison Step ***
Time: 2010-12-11 20:32:27.
Elapsed Time: 00:00:00
Comparisons performed: 103961
Rows per second: 169593
Rows out-of-sync: 11
inserts: 9
updates: 0
deletes: 2
Performance Statistics for target Rowhash at 2010-12-11 20:32:27.
rows: 103952
duration (secs): 00:00:01
rows/sec: 103952.00
row bytes: 4344826
row bytes/sec: 4344826
bytes/row: 41
rh bytes/row: 23
rows skipped: 0
blocks skipped: 0
hash comp rate: 0.55
total comp rate: 0.23
pct time fetching: 20.51
pct time waiting: 17.15
time until first row: 00:00:00
ipc msgs: 12
ipc bytes: 2809779
bytes/msg: 234148
compressed bytes/msg: 99256
bytes/sec: 2809779
compressed bytes/sec: 1191076
msg compression ratio: 0.42
COOS source Agent Information:
ORACLE_HOME : /s01/10gdb
NLS_LANG (from environment) :
ORA_SDTZ :
NLS_LANG (from DBMS) : AMERICAN_AMERICA.WE8ISO8859P1
NLS_LENGTH_SEMANTICS : BYTE
SESSIONTIMEZONE : -05:00
Process ID : 21475
Operating System : Linux
Architecture : x86_64
Report File : /home/maclean/veridata/agent/dirver/report/maclean_PC_4150_000010110000000100000001___source_coos.rpt
Trace File : /home/maclean/veridata/agent/dirver/trace/maclean_PC_4150_000010110000000100000001___source_coos.trc
Starting  Veriagt session for target COOS at 2010-12-11 20:32:28.
COOS target Agent Information:
ORACLE_HOME : /s01/db_1
NLS_LANG (from environment) :
ORA_SDTZ :
NLS_LANG (from DBMS) : AMERICAN_AMERICA.WE8ISO8859P1
NLS_LENGTH_SEMANTICS : BYTE
SESSIONTIMEZONE : +08:00
Process ID : 18641
Operating System : Linux
Architecture : x86_64
Report File : /home/maclean/veridata/agent/dirver/report/maclean_PC_4150_000010110000000100000001___target_coos.rpt
Trace File : /home/maclean/veridata/agent/dirver/trace/maclean_PC_4150_000010110000000100000001___target_coos.trc
Persistent out of sync row (optype: insert)
source row values:
T1  K     0     6  107913
GGROWHASH        1     8  0x0E8563AC22AA9C0F
T2  H     2     5  55512
T3  H     3    19  MACLEAN IS UPDATING
Persistent out of sync row (optype: insert)
source row values:
T1  K     0     6  107914
GGROWHASH        1     8  0xAAB002BF5C1B3233
T2  H     2     6  255834
T3  H     3    19  MACLEAN IS UPDATING
Persistent out of sync row (optype: insert)
source row values:
T1  K     0     6  107917
GGROWHASH        1     8  0xC0723BE324DC032D
T2  H     2     6  197710
T3  H     3    19  MACLEAN IS UPDATING
Persistent out of sync row (optype: insert)
source row values:
T1  K     0     6  107918
GGROWHASH        1     8  0x8A63DA5F06C3A12C
T2  H     2     6  194628
T3  H     3    19  MACLEAN IS UPDATING
Persistent out of sync row (optype: delete)
target row values:
T1  K     0     8  99993242
GGROWHASH        1     8  0x958B6304CEC4BAD3
T2  H     2     4  1213
T3  H     3    11  OUT OF SYNC
*** Summarizing Confirm Out-of-Sync Step ***
Time: 2010-12-11 20:32:29.
Elapsed Time: 00:00:01
Rows confirmed: 11
Rows per second: 11
*** Summarizing Persistently Out-of-Sync Rows ***
Rows out-of-sync: 5
inserts: 4
updates: 0
deletes: 1
*** Summarizing In-Sync-after-In-Flight Rows ***
Rows in-sync-after-in-flight: 6
inserts: 5
updates: 0
deletes: 1
*** Summarizing Still-Changing Rows ***
Rows in-flight: 0
inserts: 0
updates: 0
deletes: 0
Comparison terminated normally.

沪ICP备14014813号

沪公网安备 31010802001379号