Goldengate can’t extract data from compressed table

OGG目前不支持对10g以后压缩表(compressed table)的extract,若挖掘到压缩表相关的DML纪录则extract会abend:

SQL> alter table sales compress 2;
Table altered.

SQL>  update sales set  AMOUNT_SOLD= AMOUNT_SOLD +1 where rownum<200;
199 rows updated.

SQL> commit;
Commit complete.

extract report:
***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
2010-12-12 23:08:44  INFO    OGG-01517  Position of first record processed Sequence 51, 
RBA 12853264, SCN 0.53443895, 2010-12-12 下午11:08:42.
TABLE resolved (entry CLINIC.SALES):
  Table CLINIC.SALES;

Source Context :
  SourceModule            : [ggdb.ora.dbx]
  SourceID                : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34093]/perforce/src/gglib/ggdbora/dbxoci.c]
  SourceFunction          : [get_object_attributes]
  SourceLine              : [5122]
  ThreadBacktrace         : [20] elements
                          : [/home/maclean/gg/extract(CMessageContext::AddThreadContext()+0x26) [0x7f6596]]
                          : [/home/maclean/gg/extract(CMessageFactory::CreateMessage(
CSourceContext*, unsigned int, ...)+0x7b2) [0x7ed032]]
                          : [/home/maclean/gg/extract(_MSG_ERR_ORACLE_COMPRESSED_TABLE
(CSourceContext*, DBString<777> const&, CMessageFactory::MessageDisposition)+0x92) [0x79c842]]
                          : [/home/maclean/gg/extract(gl_get_odbc_table_def(file_def*, short, char*)+0x6a4) [0x6a7ee4]]
                          : [/home/maclean/gg/extract [0x54e03e]]
                          : [/home/maclean/gg/extract [0x54f078]]
                          : [/home/maclean/gg/extract(get_map_entry(char*, int, __wc*, int)+0x841) [0x550cf1]]
                          : [/home/maclean/gg/extract [0x62ec62]]
                          : [/home/maclean/gg/extract(WILDCARD_check_table(char const*, char const*,
 int, unsigned int*, int, unsigned int, DBString<777>*, int)+0x320) [0x630560]]
                          : [/home/maclean/gg/extract(REDOORA_lookup_table(file_def**, log_context_t*, int)+0x1398) [0x6ce678]]
                          : [/home/maclean/gg/extract [0x70b81c]]
                          : [/home/maclean/gg/extract(REDOORAOUT_get_committed_record(objectpool*, 
cache_object*, log_context_t*, chkpt_context_t*, short, int, int, int*, char*, int*, int*, int*, char*, 
short*, int*, char*)+0x449) [0x713619]]
                          : [/home/maclean/gg/extract [0x6fc77e]]
                          : [/home/maclean/gg/extract(REDOORAOPS_process_ops
(subrec_info_t*, objectpool*, objectpool*, cache_object**, log_context_t*, redo_thread_t*, 
chkpt_context_t*, long, int, int, int, char*, int*, int*, char*, short*, int*, char*)+0x5fa) [0x700bca]]
                          : [/home/maclean/gg/extract(REDO_read(char*, int*, long*, char*, short*, int, char*)+0xb6a) [0x6ca6fa]]
                          : [/home/maclean/gg/extract(READ_EXTRACT_RECORD(chkpt_context_t*, 
short, char*, __std_rec_hdr*, int*, int*, long*, short, short, char*, short*, char*, short*, 
long*, char*)+0x344) [0x4f0824]]
                          : [/home/maclean/gg/extract [0x56ab17]]
                          : [/home/maclean/gg/extract(main+0xfaa) [0x56fc3a]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x39f1c1d994]]
                          : [/home/maclean/gg/extract(__gxx_personality_v0+0x1f2) [0x4e5ada]]

2010-12-12 23:10:11  ERROR   
OGG-01433  Failed to validate table CLINIC.SALES. 
The table is compressed and extract will not be able to extract data from Oracle logs.

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.

配置GoldenGate同步DDL语句(3)

在配置GoldenGate同步DDL语句(2)中我们针对多种DDL语句在GoldenGate环境中的同步进行了测试,但在默认情况下replicat在复制DDL语句失败时不会在replicat report或者ggserr.log等日志文件中产生该DDL失败的具体信息,这就会导致我们在发现DDL同步有error的情况下无法了解失败的原因。之后又去翻官方的文档,在中找到了DDLOPTIONS参数:

Use the DDLOPTIONS parameter to configure aspects of DDL processing other than filtering
and string substitution. You can use multiple DDLOPTIONS statements, but using one is
recommended. If using multiple DDLOPTIONS statements, make each of them unique so that
one does not override the other. Multiple DDLOPTIONS statements are executed in the order
listed.

这个参数可用的选项很多,我们需要用到的是REPORT选项:

Valid for Extract and Replicat (Oracle and Teradata).
Controls whether or not expanded DDL processing
information is written to the report file. The default of
NOREPORT reports basic DDL statistics. REPORT adds the
parameters being used and a step-by-step history of the
operations that were processed

在replicat的参数文件中添加上”DDLOPTIONS REPORT”,可以让replicat在同步DDL语句时若出现问题,将该问题的详细情况记录到该replicat的report 文件中,以便找出DDL复制失败的root cause。

GGSCI (rh3.oracle.com) 9> view params rep1

replicat rep1
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/rep1.log,append,megabytes 10
-- Support DDL here
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLOPTIONS REPORT
map sender.* , target receiver.*;

GGSCI (rh3.oracle.com) 10> stop replicat rep1

Sending STOP request to REPLICAT REP1 ...
Request processed.

GGSCI (rh3.oracle.com) 11> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting

在源端创建一外部表
SQL> CREATE TABLE extgg
  2  ORGANIZATION EXTERNAL
  3  (TYPE oracle_datapump
  4  DEFAULT DIRECTORY dgg
  5  LOCATION ('extgg.dat'))
  6  AS
  7  select * from dba_objects;

Table created.

之后检查replicat的report信息可以发现DDL复制失败的详细SQL语句等信息:

GGSCI (rh3.oracle.com) 12> view report rep1
***********************************************************************
                 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-10 05:54:32
***********************************************************************
...............
2010-12-09 09:10:13  INFO    OGG-01408  Restoring current schema for DDL operation to [MACLEAN].
2010-12-09 09:10:13  INFO    OGG-00482  DDL found, operation [CREATE
GLOBAL TEMPORARY TABLE "SENDER"."SYS_TEMP_0FD9D6609_2B3F4ED" ("OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" VARCHAR2(30),"OBJECT_ID"
NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT_TYPE" VARCHAR2(19),"CREATED" DATE,"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHA R2(19),"STATUS" VARCHAR2(7),"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),"SECONDARY" VARCHAR2(1) ) IN_MEMORY_METADATA
CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950
921 ) NOPARALLEL  (size 456)].

2010-12-09 09:10:13  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation
[CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME"
VARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT_TYPE"
VARCHAR2(19),"CREATED" DATE,"LAST_DDL_TIME" DATE,"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),"SECONDARY" VARCHAR2(1) ) IN_MEMORY_METADATA CURSOR_SPECIF
IC_SEGMENT STORAGE (OBJNO 4254950921 ) NOPARALLEL  (size 458)].

2010-12-09 09:10:13  INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [CREATE],
objtype [TABLE], objowner [RECEIVER], objname [SYS_TEMP_0FD9D6609_2B3F4E
D].
2010-12-09 09:10:13  INFO    OGG-01407  Setting current schema for DDL operation to [SYS].
2010-12-09 09:10:13  INFO    OGG-00484  Executing DDL operation.
2010-12-09 09:10:18  INFO    OGG-00495  DDL error ignored for next retry: error code
[DEFAULT],
filter [include all (default)], error text [Error code [900],
ORA-00900: invalid SQL statement, SQL CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" V
ARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT], retry [1].

2010-12-09 09:10:18  INFO    OGG-01407  Setting current schema for DDL operation to [SYS].
2010-12-09 09:10:18  INFO    OGG-00484  Executing DDL operation trying again due to RETRYOP parameter.
2010-12-09 09:10:23  INFO    OGG-00495  DDL error ignored for next retry: error code [DEFAULT],
filter [include all (default)], error text
[Error code [900], ORA-00900
: invalid SQL statement, SQL CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" V
ARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT], retry [2].
2010-12-09 09:10:23  INFO    OGG-01407  Setting current schema for DDL operation to [SYS].

另外需要注意的是如create table as select这样的建表DDL语句应该被杜绝使用,因为在goldengate复制环境中create table as select语句仍会以DDL形式被应用,这就导致源端和目标的端的数据不一致,如:

/* 源端中dba_object视图共有52046行数据 */
SQL> select count(*) from dba_objects;
  COUNT(*)
----------
     52046

SQL> create table ddlproblem as select * from dba_objects;
Table created.

SQL> select count(*) from ddlproblem;

  COUNT(*)
----------
     52047

而在目标端的复制情况:
SQL> desc ddlproblem
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> select count(*) from ddlproblem;

  COUNT(*)
----------
     51008

/* 因为replicat在目标端仅是简单的执行"create table as select"的建表DDL语句,
    而源端和目标端中这个被select的对象中的数据并不一致,因而CTAS操作只会导致
    2端的数据不一致
*/

配置GoldenGate同步DDL语句(2)

接下来我们会测试更多不同DDL语句的同步,这些我能想到的DDL语句包括:

  • CREATE USER
  • CREATE TABLESPACE
  • CREATE PROCEDURE
  • CREATE PROFILE
  • CREATE DIRECTORY
  • CREATE EXTERNAL TABLE 等等

但在这之前我们需要修改我们在GoldenGate同步DDL语句(1)中配置的extract和replicat的参数文件:

GGSCI (rh2.oracle.com) 2> view params load1

extract load1
userid maclean, password AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD,encryptkey default
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTTRAIL /s01/rmt/ma
DDL INCLUDE ALL
Table sender.*;

/*源端将 DLL INCLUDE MAPPED 修改为DLL INCLUDE ALL,
   不同于MAPPED,INCLUDE ALL意味包含所有DDL语句  
   MAPPED applies INCLUDE or EXCLUDE to DDL operations that are of
   MAPPED scope. MAPPED filtering is performed before filtering that is
   specified with other DDL parameter options
   ALL applies INCLUDE or EXCLUDE to DDL operations of all scopes.
 */
GGSCI (rh2.oracle.com) 4> stop extract load1
Sending STOP request to EXTRACT LOAD1 ...
Request processed.

GGSCI (rh2.oracle.com) 5> start extract load1
Sending START request to MANAGER ...
EXTRACT LOAD1 starting


GGSCI (rh3.oracle.com) 3> view params rep1

replicat rep1
userid maclean,password maclean
ASSUMETARGETDEFS 
discardfile /s01/discard/rep1.log,append,megabytes 10
-- Support DDL here 
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
map sender.* , target receiver.*;

/* 目标端也需要将原来的DDL 补充为DDL INCLUDE ALL以及加上
    DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5 */

/* DDLERROR参数指定了出现DDL同步错误时的处理方式,此处为以5秒(RETRYDELAY)
    为间隔重试3次(MAXRETRIES),若该DDL仍报错,则忽略该DDL
*/

GGSCI (rh3.oracle.com) 4> stop replicat rep1

Sending STOP request to REPLICAT REP1 ...
Request processed.

GGSCI (rh3.oracle.com) 5> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

接下来我们要具体测试多种DDL语句的gg同步情况:

SQL> create user guser identified by guser;
User created.

源端的$ORACLE_BASE/admin/$SID/udump下的ggs_ddl_trace.log,纪录了该create user ddl语句:
 Start of log for DDL sequence [137], v[ $Id: //depot/releases/OpenSys/v11.1.1.0/redist/SQLScripts
/ddl_setup.sql#2 $ ] trace level [0], owner schema of DDL package [MACLEAN], objtype [USER] name [.GUSER]
SESS 4294967295-2010-12-07 22:21:06 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [USER] name [.GUSER]
SESS 4294967295-2010-12-07 22:21:06 : DDL : DDL operation [create user guser identified by ***** ]
, sequence [137], DDL type [CREATE] USER, real object type [USER], va
lidity [], object ID [], object [.GUSER], real object [.GUSER], base object schema [], base object name [], logged as [SYS]
SESS 4294967295-2010-12-07 22:21:06 : DDL : Start SCN found [45484306]
End of log for DDL sequence [137], no DDL history metadata recorded for this DDL operation

目标端同步情况:
SQL>  select username from dba_users where created>=all(select created from dba_users);
USERNAME
------------------------------
GUSER

/* 可以看到create user语句复制成功 */

SQL> show parameter db_create_file_dest 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /s01/10gdb/oradata

SQL> create tablespace gtb datafile size 10M;
Tablespace created.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /s01/oradata

SQL> select file_name from dba_data_files where tablespace_name='GTB';

FILE_NAME
--------------------------------------------------------------------------------
/s01/oradata/CLINICB/datafile/o1_mf_gtb_6hxjpstg_.dbf

/* 在远端和目标端都启用了10g中自动管理数据文件创建的前提下,
    创建表空间的DDL语句被复制成功
*/


/* 若我们在源端创建某表,且为该表指定一个目标端不存在的表空间时,
    create table语句会如何呢? 
*/
SQL> conn sender/sender
Connected.
SQL> create table rbf (t1 int) tablespace bf;
Table created.

可以在目标端的replicat report中看到以下4次尝试,其后三次应为我们之前定义的MAXRETRIES:
2010-12-08 07:37:43  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-08 07:37:48  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-08 07:37:53  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-08 07:37:58  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-08 07:37:58  INFO    OGG-01408  Restoring current schema for DDL operation to [MACLEAN].

GGSCI (rh3.oracle.com) 21>  stats replicat rep1

Sending STATS request to REPLICAT REP1 ...

No active replication maps
DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   4.00
        Mapped operations                            2.00
        Unmapped operations                          0.00
        Other operations                             2.00
        Excluded operations                          0.00
        Errors                                       8.00
        Retried errors                               6.00
        Discarded errors                             0.00
        Ignored errors                               2.00

/* 虽然可以从stats replicat的输出中看到Errors和Retried errors数,
    但在replicat的report和ggserr日志中都没有纪录DDL同步失败的具体信息,准备提SR了!
*/

/* 在源端创建以下简单的存储过程 */
SQL> conn sender/sender
SQL> create or replace procedure progg as 
  2  i  int;
  3  begin
  4  select t1 into i from tddl where rownum=1;
  5  dbms_output.put_line(i);
  6  end;
  7  /
Procedure created.

SQL> conn receiver/receiver
Connected.

SQL> select t1 from tddl;

        T1
----------
         1
         2

SQL> set serveroutput on;
SQL> exec progg;
1

PL/SQL procedure successfully completed.

/* 因为目标端存在该存储过程的依赖条件,所以复制后编译成功;
    否则复制可以成功但该对象状态为INVALID */
SQL> drop procedure progg;
Procedure dropped.
SQL> drop table tddl;
Table dropped.

在源端再次创建该procedure后:

SQL> col object_name for a30 
SQL> select object_name,status  from dba_objects where object_name='PROGG';

OBJECT_NAME                    STATUS
------------------------------ -------
PROGG                          INVALID

/* 在源端创建profile配置文件 */
SQL> create profile  gg_profile limit sessions_per_user  2;               
Profile created.

SQL> select RESOURCE_NAME,LIMIT from dba_profiles where profile='GG_PROFILE' and limit!='DEFAULT';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
SESSIONS_PER_USER                2

/*在目标端配置文件信息同步成功 */

/* 在源端创建文件夹directory对象 */
SQL> create directory dgg as '/s01/dump';
Directory created.

SQL> col directory_name for a3
SQL> col directory_path for a20
SQL> select directory_name,directory_path from dba_directories where directory_name='DGG';

DIR DIRECTORY_PATH
--- --------------------
DGG /s01/dump
/*在文件系统上都存在/s01/dump文件夹的前提下create directory ddl同步成功*/

/*在源端创建type为oracle_datapump的外部表 */
SQL> CREATE TABLE extgg
  2  ORGANIZATION EXTERNAL
  3  (TYPE oracle_datapump
  4  DEFAULT DIRECTORY dgg
  5  LOCATION ('extgg.dat'))
  6  AS
  7  select * from dba_objects;

Table created.

在目标端replicat中出现多次retry纪录:
2010-12-08 08:13:09  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-08 08:13:14  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-08 08:13:19  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-08 08:13:24  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-08 08:13:24  INFO    OGG-01408  Restoring current schema for DDL operation to [MACLEAN].

/* 同时ddl Errors数目增加 */
GGSCI (rh3.oracle.com) 34> stats replicat rep1
Sending STATS request to REPLICAT REP1 ...
No active replication maps
DDL replication statistics:
*** Total statistics since replicat started     ***
        Operations                                  11.00
        Mapped operations                            6.00
        Unmapped operations                          0.00
        Other operations                             5.00
        Excluded operations                          0.00
        Errors                                      33.00
        Retried errors                              24.00
        Discarded errors                             0.00
        Ignored errors                               9.00

/* 无疑是创建外部表的DDL语句复制失败,当因为目前找不到相关的日志所以无法进一步分析了 */

How to create a GoldenGate uni-directional target database in a production database zero downtime

Problem Description: W2 have a production database and need to configure a GoldenGate uni-directional replication in real time without downtime. Is there a best practice, white paper, knowledge document would help us in desiging the transition? We can start with a pair of database which is Data Guard Physical replication, or start with a brande new target database.

Generic steps provided by Metalink:

Implementing Oracle GoldenGate


0) Install OGG on the source and target systems
1) Establish what tables are to be replicated from the source and what tables they will be mapped to on the target.
2) Run defgen against the source tables and ftp the created efs file (call it defs.def for example) to the
dirdef subdirectory on the target install.
3) Create an ongoing capture extract to capture changes to the archive / redo logs.
4) Add the extract via ggsci
5) add the exttrail via ggsci
6) Create a datapump to read the local trail and write a remote trail to the target machine.
7) Start the local capture and datapump
8) Create an ingoing replicat. Do not start it.


9) Initial Load the target database
This can be done with native or local utilities
or with GoldenGate as follows

Begin Initial Load:
Much of what you will do depends on having enough resources and space.
Divide up your tables so that you can create several (say 10) extracts and their datapumps.
Create a sourceisfile extract for each grouping of tables. They can be run in parallel if you have the horsepower.
Create an initial load replicat for each extract/datapump thread.


There are 3 ways I recommend to do the initial load with OGG
First, do the normal prescribed manner writing to the target sode over TCP
or
Second, write to local disks and ftp the written trails to the target side.
Run the initial load replicats against the trail files. The ftp method is fatser.
or, if you have disks connected to both source and target databases, there is a fatser method.
This method is especially useful for vary large databases.


Begin initial load method
Have your sourceisfile extracts write their trails to shared disks.
You can start the initial load replicats while their trails are still being written. This reduces overall time.
You are loading the target at the same time you are unloading the source.
When all initial load replicats are finished, they can be stopped.
End Initial Load




Start the ongoing replicat(s) with HANDLECOLLISIONS turned on. When the replicats catch up, stop them and
remove HANDLECOLLISIONS, and restart them.
You should be migrated.
You can divide up your tables into as many extracts as you have memory and CPU power to do so.
If you use the ftp method, you will need at least 1.3 times the amount of data in your database on the
source and on the target for intermediate trails.
If you use the shared disk method, and start the initial load replicats while extraction is going on, you can
get by with a lot less space

N.B.
Creating initial load trails for this size of data requires a special technique.
Use the options megabytes 2000, maxtrails 900 for the extfile. Make the trail file name two characters.
This will cause the initial load trails to look just like normal trails.
Your initial load replicats will look just like regular replicats because they will be reading a trail of files, not a single file.

Goldengate各build与Oracle数据库版本间的兼容性

因为在Edelivery.oracle.com上找不到Goldengate for Oracle 8i的build,所以一度猜想是不是能用GG for Oracle 9i对8i做extract。之后在metalink上发现了这个文档《Compatibility between OGG builds and Oracle database Versions》,明确介绍了ogg的build需要与数据库版本一致,即:

针对extract来说,Oracle数据库版本应与Oracle Golden Gate的build信息完全一致

举例而言

OGG 10g build(如Oracle GoldenGate V11.1.1.0.0 for Oracle 10g):该build可以在Oracle 10.1或10.2版本的数据库上做extract
OGG oracle 10.1 build:该build仅可以为Oracle 10.1版本的数据库做extract,其他版本均不可以
OGG oracle 10.2 build:该build仅可以为Oracle 10.2版本的数据库做extract,其他版本均不可以

针对replicat来说,数据库主版本号(major database version)应与OGG的build一致

举例而言

OGG 10g build:可以针对Oracle 10.1和10.2版本的数据库做replicat操作,其他版本均不可以
OGG oracle 10.1 build:可以针对Oracle 10.1和10.2版本的数据库做replicat操作,其他版本均不可以
OGG oracle 10.2 build:可以针对Oracle 10.1和10.2版本的数据库做replicat操作,其他版本均不可以

其他9i或者11g的OGG build与Oracle database版本间的兼容性也如上述关系一般。
该文档[1086154.1]还指出以上版本要求是因为各数据库版本间可能存在不同的data dictionary calls,而OGG中这部分代码是固定写死的:
“The reason behind these requirements is that the data dictionary calls may be different from one version of the database to the other and our code is database version specific”;
从这个文档来分析,不是因为Oracle各版本所产生的日志文件的格式存在差异而导致OGG无法做extract操作,那么如果在一个10g的数据库中设置了一个较低的compatible兼容性参数也不会导致OGG for Oracle 10g无法正确抽取日志文件了。
上述观点仍需测试,to be continued…….

配置GoldenGate同步DDL语句(1)

在配置Goldengate同步DDL语句前,要求在数据库内完成一系列的预安装先题步骤;其中包括建立合适的Goldengate用户,如:

create user maclean identified by maclean;
grant dba to maclean;

/* 之后maclean用户将用以配置extract和pump */

之后我们需要执行一系列同步DLL语句要用到的GoldenGate脚本,这些脚本存放在GG的安装目录下:

[maclean@rh2 gg]$ pwd
/home/maclean/gg
[maclean@rh2 gg]$ ls -l dd*.sql
-r--r--r-- 1 maclean oinstall   1059 2010-03-12 ddl_cleartrace.sql
-r--r--r-- 1 maclean oinstall   4189 2010-03-12 ddl_ddl2file.sql
-r--r--r-- 1 maclean oinstall    746 2010-03-12 ddl_disable.sql
-r--r--r-- 1 maclean oinstall    692 2010-03-12 ddl_enable.sql
-r--r--r-- 1 maclean oinstall    388 2010-03-12 ddl_nopurgeRecyclebin.sql
-r--r--r-- 1 maclean oinstall  12424 2010-05-21 ddl_ora10.sql
-r--r--r-- 1 maclean oinstall   3863 2010-05-21 ddl_ora10upCommon.sql
-r--r--r-- 1 maclean oinstall  11064 2010-03-12 ddl_ora11.sql
-r--r--r-- 1 maclean oinstall  12365 2010-03-12 ddl_ora9.sql
-r--r--r-- 1 maclean oinstall   1026 2010-03-12 ddl_pin.sql
-r--r--r-- 1 maclean oinstall   1227 2010-03-12 ddl_purgeRecyclebin.sql
-r--r--r-- 1 maclean oinstall   3686 2010-05-13 ddl_remove.sql
-r--r--r-- 1 maclean oinstall    425 2009-06-29 ddl_session1.sql
-r--r--r-- 1 maclean oinstall   1053 2009-06-23 ddl_session.sql
-r-xr-xr-x 1 maclean oinstall 228606 06-29 13:48 ddl_setup.sql
-r--r--r-- 1 maclean oinstall   8872 2010-03-12 ddl_status.sql
-r--r--r-- 1 maclean oinstall   2506 2010-03-12 ddl_staymetadata_off.sql
-r--r--r-- 1 maclean oinstall   2501 2010-03-12 ddl_staymetadata_on.sql
-r--r--r-- 1 maclean oinstall   2955 2010-03-12 ddl_tracelevel.sql
-r--r--r-- 1 maclean oinstall   2543 2010-03-12 ddl_trace_off.sql
-r--r--r-- 1 maclean oinstall   2862 2010-03-12 ddl_trace_on.sql

/* 执行goldengate ddl同步安装脚本要求以SYSDBA身份登录 */

[maclean@rh2 gg]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Dec 6 18:01:46 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set recyclebin=off;
System altered.

/* 同步DDL要求我们关闭10g中的回收站特性 */

SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:maclean
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to MACLEAN
MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

/* 以SYSDBA 登陆数据库并执行当前GG安装目录下的market_setup脚本,该脚本用以建立一个DDL标记表
   包括以下列:
   seqNo NUMBER NOT NULL, -- sequence number
   fragmentNo NUMBER NOT NULL, -- fragment number (message divided into fragments)
   optime CHAR(19) NOT NULL, -- time of operation
   TYPE VARCHAR2 (100) NOT NULL, -- type of marker
   SUBTYPE VARCHAR2 (100) NOT NULL, -- subtype of marker
   marker_text VARCHAR2 (4000) NOT NULL,
   -- fragment text (message divided into fragments numbered with fragmentNo)
*/

SQL> @ddl_setup

GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...
Checking user sessions...

Check complete.

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:maclean

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP

Working, please wait ...
Spooling to file ddl_setup_spool.txt
Using MACLEAN as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait ...

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to MACLEAN

DDLORA_GETTABLESPACESIZE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

CLEAR_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED
STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
0

LOCATION OF DDL TRACE FILE
--------------------------------------------------------------------------------
/s01/10gdb/admin/clinica/udump/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

/* 以INITIALSETUP选项运行ddl_setup.sql 将在数据库中创建捕获DDL语句的Trigger等必要组件 */

SQL> @role_setup

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to
change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:maclean
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where  is the user assigned to the GoldenGate processes.

/* role_setup脚本用以建立GGS_GGSUSER_ROLE角色 */

我们需要将该GGS_GGSUSER_ROLE授予给extract group参数中定义的userid用户
SQL> grant GGS_GGSUSER_ROLE to maclean;
Grant succeeded.

SQL> @ddl_enable
Trigger altered.

/*ddl_enable.sql将正式enable ddl捕获触发器,即:ALTER TRIGGER sys .&ddl_trigger_name ENABLE; */

SQL> @?/rdbms/admin/dbmspool
Package created.
Grant succeeded.
View created.
Package body created.

/* 执行dbmspool包将在数据库中创建DBMS_SHARED_POOL包,之后需要用到*/

SQL> @ddl_pin
Enter value for 1: maclean
PL/SQL procedure successfully completed.
Enter value for 1: maclean
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

/* ddl_pin.sql通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中,
   以保证这些对象不要reload,提升性能
*/

以上脚本都运行完成后,DDL语句同步的先题条件就达成了。接着我们要来配置GG部分的extract和replicat:

[maclean@rh2 gg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.

GGSCI (rh2.oracle.com) 1> add extract load1,tranlog,begin now
EXTRACT added.

GGSCI (rh2.oracle.com) 2> add rmttrail /s01/rmt/ma,megabytes 100,extract load1
RMTTRAIL added.

GGSCI (rh2.oracle.com) 5> encrypt password maclean
No key specified, using default key...

Encrypted password:  AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD

GGSCI (rh2.oracle.com) 16> start extract load1

Sending START request to MANAGER ...
EXTRACT LOAD1 starting

GGSCI (rh2.oracle.com) 19> view params load1

extract load1
userid maclean, password AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD,encryptkey default
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTTRAIL /s01/rmt/ma
DDL INCLUDE MAPPED
Table sender.*;

/* 以上为源端的extract load1的配置,采用了DDL INCLUDE MAPPED的DDL同步方式 */

GGSCI (rh3.oracle.com) 59> add replicat rep1,exttrail /s01/rmt/ma,begin now,checkpointtable maclean.checkpoint
REPLICAT added.

GGSCI (rh3.oracle.com) 62> view params rep1

replicat rep1
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/rep1.log,append,megabytes 10
-- Support DDL here
DDL
map sender.* , target receiver.*;

GGSCI (rh3.oracle.com) 60> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (rh3.oracle.com) 63> info all          

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:07    

/* 以上为目标端的replicat rep1配置,DDL语句将由sender用户映射到receiver用户 */

接下来我们对具体的DDL同步进行测试:

/ * 在源端建立测试用表tddl */

SQL> conn sender/sender
Connected.
SQL> create table tddl (t1 int);
Table created.

REP1.rep报告文件出现以下信息:
Opened trail file /s01/rmt/ma000000 at 2010-12-07 03:50:19
2010-12-07 03:52:13  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-07 03:52:13  INFO    OGG-01408  Restoring current schema for DDL operation to [MACLEAN].

SQL> conn receiver/receiver;
Connected.

SQL> desc tddl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                                 NUMBER(38)

 /* create table的DDL语句被正确同步了 */

/* 可以从gg管理者名下的ggs_marker表看到捕获到的建表语句
   似乎可以分为DDL正文和变量2段
*/

SQL> set linesize 65 pagesize 1400;
SQL> select marker_text
  2    from ggs_marker
  3   where seqno =
       (select seqno from ggs_marker where marker_text like '%tddl%');
  4
MARKER_TEXT
-----------------------------------------------------------------
,C1='create table tddl \(t1 int\) ',
,C5='16',,B2='',,G4='',,B3='SENDER',,B4='TDDL',,C12='',,C13='',,B
5='TABLE',,B6='CREATE',,B7='16',,B8='MACLEAN.GGS_DDL_HIST',,B9='S
ENDER',,C7='10.2.0.4.0',,C8='10.2.0.3',,C9='',,C10='1',,C11='clin
ica',,G3='NONUNIQUE',,C14='NO',,C17('1')='NLS_LANGUAGE',,C18('1')
='AMERICAN',,C17('2')='NLS_TERRITORY',,C18('2')='AMERICA',,C17('3
')='NLS_CURRENCY',,C18('3')='$',,C17('4')='NLS_ISO_CURRENCY',,C18
('4')='AMERICA',,C17('5')='NLS_NUMERIC_CHARACTERS',,C18('5')='.\,
',,C17('6')='NLS_CALENDAR',,C18('6')='GREGORIAN',,C17('7')='NLS_D
ATE_FORMAT',,C18('7')='DD-MON-RR',,C17('8')='NLS_DATE_LANGUAGE',,
C18('8')='AMERICAN',,C17('9')='NLS_SORT',,C18('9')='BINARY',,C17(
'10')='NLS_TIME_FORMAT',,C18('10')='HH.MI.SSXFF AM',,C17('11')='N
LS_TIMESTAMP_FORMAT',,C18('11')='DD-MON-RR HH.MI.SSXFF AM',,C17('
12')='NLS_TIME_TZ_FORMAT',,C18('12')='HH.MI.SSXFF AM TZR',,C17('1
3')='NLS_TIMESTAMP_TZ_FORMAT',,C18('13')='DD-MON-RR HH.MI.SSXFF A
M TZR',,C17('14')='NLS_DUAL_CURRENCY',,C18('14')='$',,C17('15')='
NLS_COMP',,C18('15')='BINARY',,C17('16')='NLS_LENGTH_SEMANTICS',,
C18('16')='BYTE',,C17('17')='NLS_NCHAR_CONV_EXCP',,C18('17')='FAL
SE',,C19='17',

SQL> conn sender/sender
Connected.
SQL> insert into tddl values(&a);
Enter value for a: 1
old   1: insert into tddl values(&a)
new   1: insert into tddl values(1)

1 row created.

SQL> /
Enter value for a: 2
old   1: insert into tddl values(&a)
new   1: insert into tddl values(2)

1 row created.

SQL> commit;

Commit complete.

SQL>  alter table tddl add tclob clob default 'I am testing';
Table altered.

/* 我们来看看添加有默认值的clob字段这样的DDL语句是否能够同步成功 */

SQL> conn receiver/receiver
Connected.
SQL> set linesize 100 pagesize 1400;
SQL> select * from tddl;

        T1 TCLOB
---------- --------------------------------------------------------------------------------
         1 I am testing
         2 I am testing
/* 以上测试表明gg成功复制了添加有默认值的clob字段这样的DDL语句 */

我们可以从源端的$ORACLE_BASE/admin/$sid/udump目录下的ggs_ddl_trace.log日志文件中找到以下记录:
SESS 154688-2010-12-06 19:08:42 : DDL : ************************* 
Start of log for DDL sequence [18], v[ $Id: //depot/releases/OpenSys/v11.1.1.0/redist/SQLScripts/ddl_
setup.sql#2 $ ] trace level [0], owner schema of DDL package [MACLEAN], objtype [TABLE] 
name [SENDER.TDDL]
SESS 154688-2010-12-06 19:08:42 : DDLTRACE1 : Before Trigger: point in 
execution = [1.0], objtype [TABLE] name [SENDER.TDDL]
SESS 154688-2010-12-06 19:08:42 : DDL : DDL operation 
[ alter table tddl add tclob clob default 'I am testing' ], sequence [18], 
DDL type [ALTER] TABLE, real object ty
pe [TABLE], validity [VALID], object ID [57772], object [SENDER.TDDL], 
real object [SENDER.TDDL], base object schema [], base object name [], logged as [SENDER]
SESS 154688-2010-12-06 19:08:42 : DDL : Start SCN found [45357217]
SESS 154688-2010-12-06 19:08:42 : DDL : ------------------------- End of log for DDL sequence [18]

之前有客户选用了Quest的shareplex复制软件来achive一个上海到北京的live standby环境,所以有机会参加了几次shareplex的切换演练,虽然我对shareplex十分陌生(直到现在也是)。在实际切换期间也多次听到shareplex的工程师反复强调复制环境中不能有BLOB/CLOB等大对象,也不能出现DDL语句(shareplex不能复制LOB和DDL吗,未经证实);可能该环境中的确很少变更,所以客户方的经理并不concern这一点:”我们不做DDL就是了嘛!” 这个复制环境因为数据库最初设计的时候没有考虑用主键或unique index且个别主用业务表上有巨多列(多于200个列)的原因导致后来add上追加日志后出现重做日志产生量水涨船高的问题。

不过相比起goldengate有点惊人的价格来说(如果你需要data repair功能,记得你还需要另外购买veridata),似乎shareplex对于中国客户还是有着不少的优势。 shareplex还是goldengate, 这是个问题。

Goldengate一个令人郁闷的小细节

晚上有兴趣测试了下Goldengate的initial load功能,通过initial load+change sync可以很容易做到数据迁移data migration;不过发现一个令人很郁闷的细节,就是replicat的params中定义map参数时TARGET到前面一个逗号间要留一个空格space:

MAP table spec, TARGET table spec     
/*TARGET到前面一个逗号间要留一个空格space*/
[, DEF ]
[, TARGETDEF ]
[, COLMAP ()]
[, EVENTACTIONS ()]
[, EXCEPTIONSONLY]
[, EXITPARAM “”]
[, FILTER ()]
[, HANDLECOLLISIONS | NOHANDLECOLLISIONS]
[, INSERTALLRECORDS]
[, INSERTAPPEND | NOINSERTAPPEND]
[, KEYCOLS ()]
[, REPERROR ( , )]
[, SQLEXEC ()]
[, TRIMSPACES | NOTRIMSPACES]
[, WHERE ()]
;

如果map参数中target和前面的逗号间没有空格会出现invaild option for map错误:

GGSCI (rh2.oracle.com) 32> view params init1
extract init1
userid ggate,password ggate
RMTHOST rh3.oracle.com,MGRPORT 7809
RMTTASK replicat,GROUP  init3
table sh.products;

GGSCI (rh3.oracle.com) 40> view params init3

replicat init3
userid ggate,password ggate
ASSUMETARGETDEFS
map sh.products,target sh.products;

GGSCI (rh2.oracle.com) 33> start extract init1

Sending START request to MANAGER ...
EXTRACT INIT1 starting

GGSCI (rh3.oracle.com) 41> view report init3
..............
MAP resolved (entry SH.PRODUCTS):
  map SH.PRODUCTS, sh.products;

Source Context :
  SourceModule            : [er.main]
  SourceID                : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34093]/perforce/src/app/er/rep.c]
  SourceFunction          : [get_map_entry]
  SourceLine              : [8573]
  ThreadBacktrace         : [11] elements
                          : [./replicat(CMessageContext::AddThreadContext()+0x26) [0x5d9516]]
                          : [./replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x5cffb2]]
                          : [./replicat(_MSG_ERR_STARTUP_PARAMERROR_INVALID_OPTION_VALUE
(CSourceContext*, char const*, char const*, CMessageFactory::MessageDisposition)
+0x9b) [0x59086b]]
                          : [./replicat(get_map_entry(char*, int, __wc*, int)+0x284d) [0x4ee66d]]
                          : [./replicat [0x5357d5]]
                          : [./replicat(WILDCARD_check_table(char const*, char const*, int, 
unsigned int*, int, unsigned int, DBString<777>*, int)+0x15e) [0x536fce]]
                          : [./replicat(REP_find_source_file_wc(char const*, unsigned int, DBString<777>*, int)+0x64f) [0x8a1caf]]
                          : [./replicat [0x8a9b04]]
                          : [./replicat(main+0x227a) [0x4f50da]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x340d01d994]]
                          : [./replicat(__gxx_personality_v0+0x1e2) [0x4d86ba]]

2010-12-01 08:58:30  ERROR   OGG-00212  Invalid option for MAP: sh.products.

/*我们来加上空格*/

GGSCI (rh3.oracle.com) 43> view params init3
replicat init3
userid ggate,password ggate
ASSUMETARGETDEFS
map sh.products, target sh.products;

GGSCI (rh3.oracle.com) 43> view report init3
MAP resolved (entry SH.PRODUCTS):
  map SH.PRODUCTS, target sh.products;

2010-12-01 09:00:30  WARNING OGG-00869  No unique key is defined for table PRODUCTS. 
All viable columns will be used to represent the key, but may not guarantee uniqueness.  
KEYCOLS may be used to define the key.
Using following columns in default map by name:
  PROD_ID, PROD_NAME, PROD_DESC, PROD_SUBCATEGORY, PROD_SUBCATEGORY_ID, 
  PROD_SUBCATEGORY_DESC, PROD_CATEGORY, PROD_CATEGORY_ID, 
  PROD_CATEGORY_DESC, PROD_WEIGHT_CLASS, PROD_UNIT_OF_MEASURE, 
  PROD_PACK_SIZE, SUPPLIER_ID, PROD_STATUS, PROD_LIST_PRICE, 
  PROD_MIN_PRICE, PROD_TOTAL, PROD_TOTAL_ID, PROD_SRC_ID, 
  PROD_EFF_FROM, PROD_EFF_TO, PROD_VALID

/* 加上空格后初始化成功*/

Goldengate单向大事务复制性能测试

Goldengate单向数据复制,为了图省事没有配pump,只配了source的extract和target的replicat;实际操作发现gg对大事务的支持还是比streams好一些,streams碰上大事务就只看到SPILL MESSAGES了:

/*源端配置信息*/
GGSCI (rh2.oracle.com) 1> view params mgr

PORT 7809


GGSCI (rh2.oracle.com) 2> view params load1


--extract group--
extract load1
--connection to database--
userid ggate, password ggate
--hostname and port for trail--
rmthost rh3.oracle.com, mgrport 7809
--path and name for trail--
rmttrail /s01/gg
ddl include mapped objname sender.*
table sender.*;

GGSCI (rh2.oracle.com) 4> stats extract load1

Sending STATS request to EXTRACT LOAD1 ...

Start of Statistics at 2010-11-29 17:44:41.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                   1.00
        Mapped operations                            1.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00

Output to /s01/gg:

Extracting from GGATE.GGS_MARKER to GGATE.GGS_MARKER:

*** Total statistics since 2010-11-29 16:22:25 ***

        No database operations have been performed.

*** Daily statistics since 2010-11-29 16:22:25 ***

        No database operations have been performed.

*** Hourly statistics since 2010-11-29 17:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2010-11-29 16:22:25 ***

        No database operations have been performed.

Extracting from SENDER.ABC to SENDER.ABC:

*** Total statistics since 2010-11-29 16:22:25 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                        2621440.00

*** Daily statistics since 2010-11-29 16:22:25 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                        2621440.00

*** Hourly statistics since 2010-11-29 17:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2010-11-29 16:22:25 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                        2621440.00

End of Statistics.

GGSCI (rh2.oracle.com) 5> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     LOAD1       00:00:00      00:00:08    

/*目标端配置信息*/

GSCI (rh3.oracle.com) 1> view params mgr

PORT 7809
USERID ggate, PASSWORD ggate
PURGEOLDEXTRACTS /s01/gg


GGSCI (rh3.oracle.com) 2> view params rep1

--Replicat group --
replicat rep1
--source and target definitions
ASSUMETARGETDEFS
--target database login --
userid ggate, password ggate
--file for dicarded transaction --
discardfile /s01/discard.txt, append, megabytes 10
--ddl support
DDL
--Specify table mapping ---
map sender.*, target receiver.*;

GGSCI (rh3.oracle.com) 3> stats replicat rep1

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2010-11-30 02:44:16.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   1.00
        Mapped operations                            1.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00
        Errors                                       0.00
        Retried errors                               0.00
        Discarded errors                             0.00
        Ignored errors                               0.00

Replicating from SENDER.ABC to RECEIVER.ABC:

*** Total statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                        2621440.00

*** Daily statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                        2621440.00

*** Hourly statistics since 2010-11-30 02:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                        2621440.00

End of Statistics.

GGSCI (rh3.oracle.com) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP1        00:00:00      00:00:01   

接着尝试在源库端sender模式下产生一个大事务操作,观察目标段数据同步情况*/

SQL> delete sender.abc;
2621440 rows deleted.

SQL> commit;
Commit complete.
/* commit操作完成前,EXTRACT进程并不会抽取日志 */

Goldengate日志ggserror.log的相关记录:
010-11-29 17:49:49  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000029.
2010-11-29 17:49:50  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000030.
2010-11-29 17:49:51  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000031.
2010-11-29 17:49:53  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000032.
2010-11-29 17:49:54  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000033.
2010-11-29 17:49:55  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000034.
2010-11-29 17:49:56  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000035.
2010-11-29 17:49:57  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000036.
2010-11-29 17:49:58  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000037.
2010-11-29 17:49:59  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000038.
2010-11-29 17:50:00  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000039.
2010-11-29 17:50:01  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000040.
2010-11-29 17:50:03  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000041.
2010-11-29 17:50:04  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000042.
2010-11-29 17:50:05  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000043.
2010-11-29 17:50:06  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000044.
2010-11-29 17:50:07  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000045.
2010-11-29 17:50:08  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000046.
2010-11-29 17:50:09  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000047.
2010-11-29 17:50:10  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000048.
2010-11-29 17:50:11  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000049.
2010-11-29 17:50:13  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000050.
2010-11-29 17:50:14  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000051.
2010-11-29 17:50:15  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000052.
2010-11-29 17:50:16  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000053.
2010-11-29 17:50:17  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, load1.prm:  Rolling over remote file /s01/gg000054.

/*产生了26个trail文件*/

/*同时备库端开始陆续应用日志*/
REP1.rpt是rep1 replicat复制进程的文本报告,出现相关内容:
Switching to next trail file /s01/gg000029 at 2010-11-30 02:47:54 due to EOF, with current RBA 9999949
Opened trail file /s01/gg000029 at 2010-11-30 02:47:54

Switching to next trail file /s01/gg000030 at 2010-11-30 02:49:53 due to EOF, with current RBA 9999925
Opened trail file /s01/gg000030 at 2010-11-30 02:49:53

GGSCI (rh3.oracle.com) 6> stats replicat rep1

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2010-11-30 02:52:20.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   1.00
        Mapped operations                            1.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00
        Errors                                       0.00
        Retried errors                               0.00
        Discarded errors                             0.00
        Ignored errors                               0.00

Replicating from SENDER.ABC to RECEIVER.ABC:

*** Total statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                           208150.00
        Total discards                               0.00
        Total operations                        2829590.00

*** Daily statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                           208150.00
        Total discards                               0.00
        Total operations                        2829590.00

*** Hourly statistics since 2010-11-30 02:00:00 ***
        Total inserts                                0.00
        Total updates                                0.00
        Total deletes                           208150.00
        Total discards                               0.00
        Total operations                        208150.00

*** Latest statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                           208150.00
        Total discards                               0.00
        Total operations                        2829590.00

End of Statistics.


GGSCI (rh3.oracle.com) 7> !
stats replicat rep1

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2010-11-30 02:52:26.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   1.00
        Mapped operations                            1.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00
        Errors                                       0.00
        Retried errors                               0.00
        Discarded errors                             0.00
        Ignored errors                               0.00

Replicating from SENDER.ABC to RECEIVER.ABC:

*** Total statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                           210767.00
        Total discards                               0.00
        Total operations                        2832207.00

*** Daily statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                           210767.00
        Total discards                               0.00
        Total operations                        2832207.00

*** Hourly statistics since 2010-11-30 02:00:00 ***
        Total inserts                                0.00
        Total updates                                0.00
        Total deletes                           210767.00
        Total discards                               0.00
        Total operations                        210767.00

*** Latest statistics since 2010-11-30 01:21:09 ***
        Total inserts                           2621440.00
        Total updates                                0.00
        Total deletes                           210767.00
        Total discards                               0.00
        Total operations                        2832207.00

End of Statistics.


/*由以上对replicat进程的统计信息可知其正在应用delete操作*/

Switching to next trail file /s01/gg000031 at 2010-11-30 02:53:26 due to EOF, with current RBA 9999925
Opened trail file /s01/gg000031 at 2010-11-30 02:53:26

Switching to next trail file /s01/gg000032 at 2010-11-30 02:58:28 due to EOF, with current RBA 9999925
Opened trail file /s01/gg000032 at 2010-11-30 02:58:28

[maclean@rh3 s01]$ ls -lh gg00003[1-2]
-rw-rw-rw- 1 maclean oinstall 9.6M Nov 30 02:47 gg000031
-rw-rw-rw- 1 maclean oinstall 9.6M Nov 30 02:47 gg000032

/*由上列trace信息推断,以这样一台双核cpu主频为2.2GHZ的pc机为例,gg应用一个大小为9.6M的trail文件耗时也要将近5分钟(当然这并不准确)。


/*从进程argument可以看到replicate和extract进程的本质是调用了多个配置文件的$GGATE/extract和$GGATE/replicat*/
[maclean@rh3 ~]$ ps -ef|grep repl
maclean   7817  7476  3 01:18 ?        00:03:29 
/home/maclean/gg/replicat PARAMFILE /home/maclean/gg/dirprm/rep1.prm 
REPORTFILE /home/maclean/gg/dirrpt/REP1.rpt 
PROCESSID REP1 USESUBDIRS

[maclean@rh2 dirrpt]$ ps -ef|grep ex
maclean    544 32432  1 16:21 ?        00:01:12 
/home/maclean/gg/extract PARAMFILE /home/maclean/gg/dirprm/load1.prm 
REPORTFILE /home/maclean/gg/dirrpt/LOAD1.rpt 
PROCESSID LOAD1 USESUBDIRS

沪ICP备14014813号-2

沪公网安备 31010802001379号