分类目录归档：GoldenGate

ogg同步部分列配置

发表于 2015 年 9 月 6 日由惜分飞

自从2010年后,基本上没有玩ogg了,最近有客户需求,a库在内网,b库在外网,希望同步a库中几个基础业务表的每个表的几个字段同步到b库中,采用a–>c–>b的方式来实现同步(c同时接通内外网),ogg 本身同步不难,关键是自己好多年没有玩,而且这次是只要同步部分列的情况,因此做了一个同步表部分列的一个demo测试
源端数据库准备
启动归档模式，开启强制日志和辅助日志，创建测试用户/表，ogg用户

SQL> create user xifenfei identified by xifenfei;

User created.

SQL> grant dba to xifenfei;

Grant succeeded.

SQL> conn xifenfei/xifenfei
Connected.
SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> alter table t_xifenfei add constraint pk_t_xifenfei primary key(object_id); 

Table altered.


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
Current log sequence           11

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  901775360 bytes
Fixed Size                  2024944 bytes
Variable Size             239077904 bytes
Database Buffers          658505728 bytes
Redo Buffers                2166784 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database force logging;

Database altered.

SQL>  alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> create user ogg identified by oracle;

User created.

SQL> grant dba to ogg;

Grant succeeded.

配置mgr进程

[oracle@xffdbrh5 ogg]$ export PATH=/u01/ogg:$PATH
[oracle@xffdbrh5 ogg]$ export LD_LIBRARY_PATH=/u01/ogg:$ORACLE_HOME/lib

[oracle@xffdbrh5 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_02 16363018 OGGCORE_11.2.1.0.6_PLATFORMS_130301.1500_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Mar  1 2013 19:04:05

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


GGSCI (xffdbrh5) 1> create subdirs

Creating subdirectories under current directory /u01/ogg

Parameter files                /u01/ogg/dirprm: already exists
Report files                   /u01/ogg/dirrpt: created
Checkpoint files               /u01/ogg/dirchk: created
Process status files           /u01/ogg/dirpcs: created
SQL script files               /u01/ogg/dirsql: created
Database definitions files     /u01/ogg/dirdef: created
Extract data files             /u01/ogg/dirdat: created
Temporary files                /u01/ogg/dirtmp: created
Stdout files                   /u01/ogg/dirout: created

GGSCI (xffdbrh5) 2> edit param mgr
port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

GGSCI (xffdbrh5) 3> dblogin userid ogg, password oracle
Successfully logged into database.

GGSCI (xffdbrh5) 4> add checkpointtable ogg.ggs_checkpoint

Successfully created checkpoint table ogg.ggs_checkpoint.

GGSCI (xffdbrh5) 5> EDIT PARAMS ./GLOBALS
ogg.ggs_checkpoint


GGSCI (xffdbrh5) 6> start mgr

Manager started.


GGSCI (xffdbrh5) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

配置extract进程

GGSCI (xffdbrh5) 3> dblogin userid ogg, password oracle
Successfully logged into database.

GGSCI (xffdbrh5) 4> add trandata xifenfei.t_xifenfei

Logging of supplemental redo data enabled for table XIFENFEI.T_XIFENFEI.

GGSCI (xffdbrh5) 5>  add extract ext_1, tranlog, begin now, threads 1
EXTRACT added.


GGSCI (xffdbrh5) 6>  add EXTTRAIL ./dirdat/r1, extract ext_1,megabytes 100
EXTTRAIL added.

GGSCI (xffdbrh5) 7> edit param ext_1
EXTRACT ext_1
userid ogg,password oracle
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext_1.dsc,APPEND,MEGABYTES 1024
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS DISABLESUPPLOGCHECK   --bug 16857778
TABLE xifenfei.t_xifenfei, COLS (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID);

GGSCI (xffdbrh5) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXT_1       00:00:00      00:00:22    

GGSCI (xffdbrh5) 9> start ext_1

Sending START request to MANAGER ...
EXTRACT EXT_1 starting


GGSCI (xffdbrh5) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT_1       00:01:18      00:00:00

配置pump data进程

GGSCI (xffdbrh5) 1> edit param dpe_1


extract dpe_1
dynamicresolution
passthru
rmthost 192.168.137.251, mgrport 7839, compress
rmttrail ./dirdat/t1
numfiles 5000
TABLE xifenfei.t_xifenfei;


GGSCI (xffdbrh5) 2> start dpe_1

Sending START request to MANAGER ...
EXTRACT DPE_1 starting


GGSCI (xffdbrh5) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPE_1       00:00:00      00:16:47    
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:07

目标端数据库准备

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 13 00:40:19 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user ogg identified by oracle;

User created.

SQL> grant dba to ogg;

Grant succeeded.

SQL> create user xff identified by xifenfei;

User created.

SQL> grant dba to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> create  database link syc_data
  2    connect to ogg identified by oracle
  3    using '(DESCRIPTION =
  4    (ADDRESS_LIST =
  5    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.252)(PORT = 1521))
  6    )
  7    (CONNECT_DATA =
  8    (SERVER = DEDICATED)
  9    (SERVICE_NAME = ora10g)
 10    )
 11    )';

Database link created.

SQL> select count(*) from xifenfei.t_xifenfei@syc_data;

  COUNT(*)
----------
      9917

SQL> SELECT CURRENT_SCN FROM V$DATABASE@syc_data;

CURRENT_SCN
-----------
     793069

SQL> create table xff.t_xff as select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 2 > OBJECT_ID from xifenfei.t_xifenfei@syc_data AS OF SCN  793069;

Table created.

SQL> alter table xff.t_xff add constraint pk_t_xff primary key(object_id); 

Table altered.

目标端mgrp配置

[oracle@xifenfei ogg]$export LD_LIBRARY_PATH=/home/oracle/amdu:$ORACLE_HOME/lib:/u01/oracle/oradata/ogg
[oracle@xifenfei ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (xifenfei) 8> edit param mgr
port 7839
DYNAMICPORTLIST 7840-7850
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
autorestart extract *, waitminutes 1, retries 60
autorestart replicat *, waitminutes 1, retries 60
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

GGSCI (xifenfei) 12> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           


GGSCI (xifenfei) 13> create subdirs

Creating subdirectories under current directory /u01/oracle/oradata/ogg

Parameter files                /u01/oracle/oradata/ogg/dirprm: already exists
Report files                   /u01/oracle/oradata/ogg/dirrpt: already exists
Checkpoint files               /u01/oracle/oradata/ogg/dirchk: already exists
Process status files           /u01/oracle/oradata/ogg/dirpcs: already exists
SQL script files               /u01/oracle/oradata/ogg/dirsql: already exists
Database definitions files     /u01/oracle/oradata/ogg/dirdef: already exists
Extract data files             /u01/oracle/oradata/ogg/dirdat: already exists
Temporary files                /u01/oracle/oradata/ogg/dirtmp: already exists
Stdout files                   /u01/oracle/oradata/ogg/dirout: already exists

GGSCI (xifenfei) 2> dblogin userid ogg, password oracle
Successfully logged into database.

GGSCI (xifenfei) 3> add checkpointtable ogg.ggs_checkpoint  

Successfully created checkpoint table ogg.ggs_checkpoint.

GGSCI (xifenfei) 4> EDIT PARAMS ./GLOBALS

checkpointtable ogg.ggs_checkpoint

GGSCI (xifenfei) 5> start mgr

Manager started.

GGSCI (xifenfei) 6> add replicat rep_1,exttrail ./dirdat/t1,checkpointtable ogg.ggs_checkpoint
REPLICAT added.


GGSCI (xifenfei) 7> edit params rep_1    


REPLICAT rep_1
USERID ogg,PASSWORD oracle
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
assumetargetdefs
DISCARDFILE ./dirrpt/rep_1.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 3:00
ALLOWNOOPUPDATES
MAP xifenfei.t_xifenfei, TARGET xff.t_xff;

GGSCI (xifenfei) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     REP_1       00:00:00      00:01:45    


GGSCI (xifenfei) 9> start rep_1,aftercsn 793069

Sending START request to MANAGER ...
REPLICAT REP_1 starting


GGSCI (xifenfei) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP_1       00:00:00      00:00:01

测试数据库同步

--源端库
SQL> desc t_XIFENFEI
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL 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> update t_XIFENFEI set owner='www.xifenfei.com' where rownum<100;

99 rows updated.

SQL> commit;

Commit complete.

--目标端库
SQL> desc xff.t_xff
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER

SQL> select count(*) from xff.t_xff where owner='www.xifenfei.com';

  COUNT(*)
----------
        99

--源端库
SQL> delete from t_XIFENFEI where  owner='www.xifenfei.com';

99 rows deleted.

SQL> commit;

Commit complete.

--目标端
SQL> select count(*) from xff.t_xff where owner='www.xifenfei.com';

  COUNT(*)
----------
         0

--源端库
SQL> insert into xifenfei.t_xifenfei(owner,object_id) values('www.xifenfei.com',1);

1 row created.

SQL> commit;

Commit complete.

--目标端库
SQL>  select count(*) from xff.t_xff where owner='www.xifenfei.com';

  COUNT(*)
----------
         1

SQL> select * from xff.t_xff where owner='www.xifenfei.com';

OWNER                OBJECT_NAME         SUBOBJECT_NAME                  OBJECT_ID
-------------------- ------------------- ------------------------------ ----------
www.xifenfei.com                                                                 1

这里实现部分列同步,主要在extract端使用COLS捕获需要列,使用ctas结合dblink,flashback query实现表测试后.

发表在 GoldenGate | 标签为 cols, goldengate, ogg, 部分列 | 评论关闭

利用impdp结合network_link+FLASHBACK_TIME初始化ogg同步数据

发表于 2011 年 12 月 6 日由惜分飞

部署ogg，需要两边初始化数据，但是因为业务不能停止。所以考虑到使用ogg init功能或者impdp。考虑到数据量比较大，自己对ogg init不是很有信心，所以选择了使用impdp结合network_link+FLASHBACK_TIME处理
一、处理思路
1、选择时间点(尚未达到时间点)
选择一个时间点，用于重设ogg同步时间点，impdp导入时间点
要求:ogg尚未加载到该时间点(在该时间点之前停止ogg相关ext/pump/replicat进程)

2、开始导入指定时间点数据(该时间点已经达到后)

[oracle@srtcreen OGG]$ impdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 13:03:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\"

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 06 December, 2011 13:04:34

Copyright (c) 2003, 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
Starting "SRT_CREEN"."SYS_IMPORT_TABLE_01":  srt_creen/******** directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME="to_timestamp('2011-12-06 13:03:00','yyyy-mm-dd hh24:mi:ss')" 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 824.5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
    . . imported "SRT_CREEN"."TAB_CS_CALLLOG"               3289293 rows
    . . imported "SRT_CREEN"."TAB_CS_CALL_COMING"           1218843 rows
 . . imported "SRT_CREEN"."TAB_CS_CALL_PICKUP"           1092937 rows
. . imported "SRT_CREEN"."TAB_CS_QUEUES"                   4614 rows
. . imported "SRT_CREEN"."TAB_CS_USER"                      458 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SRT_CREEN"."SYS_IMPORT_TABLE_01" successfully completed at 13:12:37

3、重设同步时间点，开启进程

alter ext_1,begin 2011-12-06 13:03:00
alter ext_2,begin 2011-12-06 13:03:00
alter ext_3,begin 2011-12-06 13:03:00
alter ext_4,begin 2011-12-06 13:03:00
alter ext_5,begin 2011-12-06 13:03:00

alter p_1,begin 2011-12-06 13:03:00
alter p_2,begin 2011-12-06 13:03:00
alter p_3,begin 2011-12-06 13:03:00
alter p_4,begin 2011-12-06 13:03:00
alter p_5,begin 2011-12-06 13:03:00
start *

alter r_1,begin 2011-12-06 13:03:00
alter r_2,begin 2011-12-06 13:03:00
alter r_3,begin 2011-12-06 13:03:00
alter r_4,begin 2011-12-06 13:03:00
alter r_5,begin 2011-12-06 13:03:00
start *

4、验证同步情况

--源端
GGSCI (tykf) 23> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:05    
EXTRACT     RUNNING     EXT_2       00:00:00      00:00:05    
EXTRACT     RUNNING     EXT_3       00:00:00      00:00:07    
EXTRACT     RUNNING     EXT_4       00:00:00      00:00:07    
EXTRACT     RUNNING     EXT_5       00:00:00      00:00:07    
EXTRACT     RUNNING     P_1         00:00:00      00:00:06    
EXTRACT     RUNNING     P_2         00:00:00      00:00:06    
EXTRACT     RUNNING     P_3         00:00:00      00:00:06    
EXTRACT     RUNNING     P_4         00:00:00      00:00:06    
EXTRACT     RUNNING     P_5         00:00:00      00:00:06    

GGSCI (tykf) 24> stats p_3

Sending STATS request to EXTRACT P_3 ...

Start of Statistics at 2011-12-06 13:15:46.

DDL replication statistics (for all trails):

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

Output to /opt/OGG/dirdat/U3:

Extracting from CSCNEW.TAB_CS_CALL_PICKUP to CSCNEW.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Daily statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Hourly statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Latest statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

End of Statistics.



--目标端
GGSCI (srtcreen) 11> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_1         00:00:00      00:00:06    
REPLICAT    RUNNING     R_2         00:00:00      00:00:06    
REPLICAT    RUNNING     R_3         00:00:00      00:00:08    
REPLICAT    RUNNING     R_4         00:00:00      00:00:04    
REPLICAT    RUNNING     R_5         00:00:00      00:00:02    


GGSCI (srtcreen) 12> stats r_3

Sending STATS request to REPLICAT R_3 ...

Start of Statistics at 2011-12-06 13:15:35.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   0.00
        Mapped operations                            0.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 CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Daily statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Hourly statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Latest statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

End of Statistics.

二、配置过程遇到问题
1、impdp报无权限

[oracle@srtcreen OGG]$ expdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 11:00:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\" dumpfile=a.dmp

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 06 December, 2011 11:15:04

Copyright (c) 2003, 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
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

srt_creen用户需要有imp_full_database权限
dblink中用户需要exp_full_database权限

2、目标端进程报OGG-01003

Opened trail file /opt/OGG/dirdat/U3000002 at 2011-12-06 12:34:47

Switching to next trail file /opt/OGG/dirdat/U3000003 at 2011-12-06 12:35:21 due to EOF, with current RBA 1028
Opened trail file /opt/OGG/dirdat/U3000003 at 2011-12-06 12:35:21

Processed extract process graceful restart record at seq 3, rba 993.
Processed extract process graceful restart record at seq 3, rba 1051.

MAP resolved (entry CSCNEW.TAB_CS_CALL_PICKUP):
  MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
Using following columns in default map by name:
  PICKUP_ID, CALL_ID, CALL_SERIAL, USER_ID, PICKUP_TIME, CALL_RESULT, 
  FAIL_REASON, CALL_TIME, CALL_DURA, END_TIME

Using the following key columns for target table SRT_CREEN.TAB_CS_CALL_PICKUP: CALL_ID.


2011-12-06 12:35:22  WARNING OGG-00869  OCI Error ORA-01407: cannot update ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDAT
E "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CAL
L_TIME" = :a7,"CALL_DURA" = :a8,"END_T>.

2011-12-06 12:35:23  WARNING OGG-01004  Aborted grouped transaction on 'SRT_CREEN.TAB_CS_CALL_PICKUP', Database error 1407 (OCI Error ORA-01407: cannot updat
e ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDATE "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = 
:a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CALL_TIME" = :a7,"CALL_DURA" = :a8,"END_T>).

2011-12-06 12:35:23  WARNING OGG-01003  Repositioning to rba 1111 in seqno 3.

部署ogg时配置relicat错误:
非MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
而是MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (PICKUP_ID);

3、update同步失败

GGSCI (srtcreen) 12> stats r_3

Sending STATS request to REPLICAT R_3 ...

Start of Statistics at 2011-12-06 12:37:49.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   0.00
        Mapped operations                            0.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 CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Daily statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Hourly statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Latest statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

End of Statistics.

源端忘记执行add trandata 同步表

add trandata CSCNEW.TAB_CS_USER
add trandata  CSCNEW.TAB_CS_QUEUES
add trandata  CSCNEW.TAB_CS_CALL_PICKUP
add trandata CSCNEW.TAB_CS_CALL_COMING
add trandata CSCNEW.TAB_CS_CALLLOG

发表在 GoldenGate, 逻辑备份/恢复 | 评论关闭

时间不同步导致ogg部署异常

发表于 2011 年 12 月 2 日由惜分飞

一、错误检查

[oracle@srtcreen ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700 
Linux, x64, 64bit (optimized), Oracle 10g on Jul 29 2011 19:43:29

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



GGSCI (srtcreen) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_1         00:00:00      00:00:07    
REPLICAT    RUNNING     R_2         00:00:00      00:00:05    
REPLICAT    ABENDED     R_3         19:19:34      00:17:33    
REPLICAT    STOPPED     R_4         00:00:00      19:44:24    
REPLICAT    STOPPED     R_5         00:00:00      19:44:13    

GGSCI (srtcreen) 2> view report r_3


***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
     Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700 
   Linux, x64, 64bit (optimized), Oracle 10g on Sep 13 2011 21:33:03
 
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2011-12-02 16:36:58
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri Apr 2 14:58:14 EDT 2010, Release 2.6.18-194.el5
Node: srtcreen
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: 13398

Description: 

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
replicat r_3
ASSUMETARGETDEFS
HANDLECOLLISIONS
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid it1ogg, password ********
--file for dicarded transaction --
discardfile /opt/OGG/discard/R_3.txt, append, megabytes 100
DDL
MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);


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 = "AMERICAN_AMERICA.ZHS16GBK" 
NLS_LANGUAGE     = "AMERICAN" 
NLS_TERRITORY    = "AMERICA" 
NLS_CHARACTERSET = "ZHS16GBK" 

For further information on character set settings, please refer to user manual.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************

Opened trail file /opt/OGG/dirdat/U3000000 at 2011-12-02 16:36:58

MAP resolved (entry CSCNEW.TAB_CS_CALL_PICKUP):
  MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
Using following columns in default map by name:
  PICKUP_ID, CALL_ID, CALL_SERIAL, USER_ID, PICKUP_TIME, CALL_RESULT, 
  FAIL_REASON, CALL_TIME, CALL_DURA, END_TIME

Using the following key columns for target table SRT_CREEN.TAB_CS_CALL_PICKUP: CALL_ID.


2011-12-02 16:36:58  WARNING OGG-00869  OCI Error ORA-01407: cannot update ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDAT
E "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CAL
L_TIME" = :a7,"CALL_DURA" = :a8,"END_T>.

2011-12-02 16:36:58  WARNING OGG-01004  Aborted grouped transaction on 'SRT_CREEN.TAB_CS_CALL_PICKUP', Database error 1407 (OCI Error ORA-01407: cannot updat
e ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDATE "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = 
:a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CALL_TIME" = :a7,"CALL_DURA" = :a8,"END_T>).

2011-12-02 16:36:58  WARNING OGG-01003  Repositioning to rba 924 in seqno 0.

2011-12-02 16:36:59  WARNING OGG-01154  SQL error 1407 mapping CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP OCI Error ORA-01407: cannot update (
"SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDATE "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2
,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CALL_TIME" = :a7,"CALL_DURA" = :a8,"END_T>.

2011-12-02 16:36:59  WARNING OGG-01003  Repositioning to rba 924 in seqno 0.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Last record for the last committed transaction is the following: 
___________________________________________________________________
Trail name :  /opt/OGG/dirdat/U3000000
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04) 
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41) 
RecLength  :   157 (x009d)    IO Time    : 2011-12-01 21:17:24.084108  
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :       3225       AuditPos   : 42227728
Continued  :     N  (x00)     RecCount   :     1  (x01)

2011-12-01 21:17:24.084108 FieldComp          Len   157 RBA 924
Name: CSCNEW.TAB_CS_CALL_PICKUP
___________________________________________________________________

Reading /opt/OGG/dirdat/U3000000, current RBA 924, 0 records

Report at 2011-12-02 16:36:59 (activity since 2011-12-02 16:36:58)

From Table CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP:
       #                   inserts:         0
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         1


DDL replication statistics:

                    Operations:         0
             Mapped operations:         0
           Unmapped operations:         0
              Other operations:         0
           Excluded operations:         0
                        Errors:         0
                Retried errors:         0
              Discarded errors:         0
                Ignored errors:         0




Last log location read:
     FILE:      /opt/OGG/dirdat/U3000000
     SEQNO:     0
     RBA:       924
     TIMESTAMP: 2011-12-01 21:17:24.084108
     EOF:       NO
     READERR:   0

2011-12-02 16:36:59  ERROR   OGG-01668  PROCESS ABENDING.

--发现奇怪现象，我2011-12-02早上过来检查这个，发现时间竟然显示2011-12-02 16:36:59，第一反应系统时间错误,继续检查

GGSCI (srtcreen) 2> info r_2

REPLICAT   R_2       Last Started 2011-12-01 21:09   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File /opt/OGG/dirdat/U2000000
                     2011-12-02 17:01:29.927591  RBA 6234

GGSCI (srtcreen) 3> exit
[oracle@srtcreen OGG]$ cd dirdat
[oracle@srtcreen dirdat]$ ll
total 396
-rw-rw-rw- 1 oracle oinstall      0 Dec  1 21:10 U1000000
-rw-rw-rw- 1 oracle oinstall   5984 Dec  2 05:36 U2000000
-rw-rw-rw- 1 oracle oinstall 392258 Dec  2 16:52 U3000000
[oracle@srtcreen dirdat]$ stat U3000000
  File: `U3000000'
  Size: 392258          Blocks: 776        IO Block: 4096   regular file
Device: fd00h/64768d    Inode: 213844220   Links: 1
Access: (0666/-rw-rw-rw-)  Uid: (  501/  oracle)   Gid: (  501/oinstall)
Access: 2011-12-02 16:36:59.000000000 +0800
Modify: 2011-12-02 16:52:55.000000000 +0800
Change: 2011-12-02 16:52:55.000000000 +0800

###############查看源端数据库服务器时间#####################
[oracle@tykf ~]$ date
Fri Dec  2 08:50:10 CST 2011
[oracle@tykf ~]$ 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 (tykf) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:05    
EXTRACT     RUNNING     EXT_2       00:00:00      00:00:01    
EXTRACT     RUNNING     EXT_3       00:00:00      00:00:05    
EXTRACT     STOPPED     EXT_4       00:00:00      21:59:56    
EXTRACT     STOPPED     EXT_5       00:00:00      21:59:28    
EXTRACT     RUNNING     P_1         00:00:00      00:00:04    
EXTRACT     RUNNING     P_2         00:00:00      00:00:04    
EXTRACT     RUNNING     P_3         00:00:00      00:00:05    
EXTRACT     STOPPED     P_4         00:00:00      21:56:42    
EXTRACT     STOPPED     P_5         00:00:00      21:56:11    


GGSCI (tykf) 2> info ext_3

EXTRACT    EXT_3     Last Started 2011-12-01 13:11   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2011-12-02 08:50:06  Seqno 3233, RBA 32267264


GGSCI (tykf) 3> info p_3

EXTRACT    P_3       Last Started 2011-12-01 13:11   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  File /opt/OGG/dirdat/extract/A3000000
                     2011-12-02 08:50:15.000000  RBA 393705

--ext_3和p_3是目标端r_3的对应进程
#######################################################################

果然是系统时间错误，源端和目标端相差了近八个小时

二、更正目标端时间

[oracle@srtcreen dirdat]$ date
Fri Dec  2 16:55:55 CST 2011
[oracle@srtcreen OGG]$ su - root
Password: 
[root@srtcreen ~]# date -s 08:58:20
Fri Dec  2  08:58:20 CST
[root@srtcreen ~]# clock -w
[root@srtcreen ~]# date
Fri Dec  2  08:58:28 CST

[root@srtcreen ~]# su - oracle
[oracle@srtcreen dirdat]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700 
Linux, x64, 64bit (optimized), Oracle 10g on Jul 29 2011 19:43:29

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



GGSCI (srtcreen) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_1         00:00:00      unknown     
REPLICAT    RUNNING     R_2         00:00:00      unknown     
REPLICAT    ABENDED     R_3         19:19:34      unknown     
REPLICAT    STOPPED     R_4         00:00:00      11:54:31    
REPLICAT    STOPPED     R_5         00:00:00      11:54:20    
[oracle@srtcreen OGG]$ cd dirdat
[oracle@srtcreen dirdat]$ ll
total 412
-rw-rw-rw- 1 oracle oinstall      0 Dec  1 21:10 U1000000
-rw-rw-rw- 1 oracle oinstall   6485 Dec  2  2011 U2000000
-rw-rw-rw- 1 oracle oinstall 407519 Dec  2 09:04 U3000000

最新写到目标端的数据已经是当前修改时间(这个是系统时间，肯定会修改过来)
出现Time Since Chkpt unknown，处理方法见ogg中Time Since Chkpt显示unknown解决

三、重设r_3时间点

GGSCI (srtcreen) 7> alter r_3,begin 2011-12-02 09:00:00
REPLICAT altered.


GGSCI (srtcreen) 9> start r_3

Sending START request to MANAGER ...
REPLICAT R_3 starting


GGSCI (srtcreen) 10> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_1         00:00:00      00:00:05      
REPLICAT    RUNNING     R_2         00:00:00      00:00:02     
REPLICAT    ABENDED     R_3         unknown       00:00:00    
REPLICAT    STOPPED     R_4         00:00:00      11:55:40    
REPLICAT    STOPPED     R_5         00:00:00      11:55:29    


GGSCI (srtcreen) 11> view report r_3


***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
     Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700 
   Linux, x64, 64bit (optimized), Oracle 10g on Sep 13 2011 21:33:03
 
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2011-12-02 09:09:31
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri Apr 2 14:58:14 EDT 2010, Release 2.6.18-194.el5
Node: srtcreen
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: 13629

Description: 

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
replicat r_3
ASSUMETARGETDEFS
HANDLECOLLISIONS
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid it1ogg, password ********
--file for dicarded transaction --
discardfile /opt/OGG/discard/R_3.txt, append, megabytes 100
DDL
MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);


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 = "AMERICAN_AMERICA.ZHS16GBK" 
NLS_LANGUAGE     = "AMERICAN" 
NLS_TERRITORY    = "AMERICA" 
NLS_CHARACTERSET = "ZHS16GBK" 

For further information on character set settings, please refer to user manual.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************

Opened trail file /opt/OGG/dirdat/U3000000 at 2011-12-02 09:09:32

MAP resolved (entry CSCNEW.TAB_CS_CALL_PICKUP):
  MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
Using following columns in default map by name:
  PICKUP_ID, CALL_ID, CALL_SERIAL, USER_ID, PICKUP_TIME, CALL_RESULT, 
  FAIL_REASON, CALL_TIME, CALL_DURA, END_TIME

Using the following key columns for target table SRT_CREEN.TAB_CS_CALL_PICKUP: CALL_ID.


2011-12-02 09:09:32  WARNING OGG-00869  OCI Error ORA-01407: cannot update ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDAT
E "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CAL
L_TIME" = :a7,"CALL_DURA" = :a8,"END_T>.

2011-12-02 09:09:32  WARNING OGG-01004  Aborted grouped transaction on 'SRT_CREEN.TAB_CS_CALL_PICKUP', Database error 1407 (OCI Error ORA-01407: cannot updat
e ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDATE "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = 
:a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CALL_TIME" = :a7,"CALL_DURA" = :a8,"END_T>).

2011-12-02 09:09:32  WARNING OGG-01003  Repositioning to rba 375833 in seqno 0.

2011-12-02 09:09:32  WARNING OGG-01154  SQL error 1407 mapping CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP OCI Error ORA-01407: cannot update (
"SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDATE "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2
,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CALL_TIME" = :a7,"CALL_DURA" = :a8,"END_T>.

2011-12-02 09:09:32  WARNING OGG-01003  Repositioning to rba 375833 in seqno 0.

Source Context :
  SourceModule            : [er.main]
  SourceID                : [/scratch/angorant/view_storage/angorant_ogg_12978807_x64/oggcore/OpenSys/src/app/er/rep.c]
  SourceFunction          : [take_rep_err_action]
  SourceLine              : [16134]
  ThreadBacktrace         : [8] elements
                          : [/opt/OGG/replicat(CMessageContext::AddThreadContext()+0x26) [0x5ef8b6]]
                          : [/opt/OGG/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x5e6382]]
                          : [/opt/OGG/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, DBString<777> const&, DBString<777> const&, CMessageFactory::Me
ssageDisposition)+0x9b) [0x5c4bcb]]
                          : [/opt/OGG/replicat [0x81ac2f]]
                          : [/opt/OGG/replicat [0x8f73e2]]
                          : [/opt/OGG/replicat(main+0x84b) [0x50764b]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x3e2f41d994]]
                          : [/opt/OGG/replicat(__gxx_personality_v0+0x1da) [0x4e3c2a]]

2011-12-02 09:09:32  ERROR   OGG-01296  Error mapping from CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Last record for the last committed transaction is the following: 
___________________________________________________________________
Trail name :  /opt/OGG/dirdat/U3000000
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04) 
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41) 
RecLength  :   157 (x009d)    IO Time    : 2011-12-02 11:52:49.559112  
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :       3233       AuditPos   : 8194064
Continued  :     N  (x00)     RecCount   :     1  (x01)

2011-12-02 11:52:49.559112 FieldComp          Len   157 RBA 375833
Name: CSCNEW.TAB_CS_CALL_PICKUP
___________________________________________________________________

Reading /opt/OGG/dirdat/U3000000, current RBA 375833, 0 records

Report at 2011-12-02 09:09:32 (activity since 2011-12-02 09:09:32)

From Table CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP:
       #                   inserts:         0
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         1


DDL replication statistics:

                    Operations:         0
             Mapped operations:         0
           Unmapped operations:         0
              Other operations:         0
           Excluded operations:         0
                        Errors:         0
                Retried errors:         0
              Discarded errors:         0
                Ignored errors:         0




Last log location read:
     FILE:      /opt/OGG/dirdat/U3000000
     SEQNO:     0
     RBA:       375833
     TIMESTAMP: 2011-12-02 11:52:49.559112
     EOF:       NO
     READERR:   0


2011-12-02 09:09:32  ERROR   OGG-01668  PROCESS ABENDING.
--这里可以看出我重设的当前的时间点对应的io time为2011-12-02 11:52:49.559112,而现在尚未到这个时间，进程启动失败

GGSCI (srtcreen) 2> alter r_3,begin 2011-12-02 18:00:00
REPLICAT altered.
--重设时间到今天早上我重设时间之前的错误时间之后

GGSCI (srtcreen) 3> start r_3

Sending START request to MANAGER ...
REPLICAT R_3 starting


GGSCI (srtcreen) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_1         00:00:00      00:00:06    
REPLICAT    RUNNING     R_2         00:00:00      00:00:01    
REPLICAT    RUNNING     R_3         unknown       00:00:08    
REPLICAT    RUNNING     R_4         00:00:00      00:00:09    
REPLICAT    RUNNING     R_5         00:00:00      00:00:09    


GGSCI (srtcreen) 5> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_1         00:00:00      00:00:09    
REPLICAT    RUNNING     R_2         00:00:00      00:00:03    
REPLICAT    RUNNING     R_3         00:00:00      00:00:00    
REPLICAT    RUNNING     R_4         00:00:00      00:00:02    
REPLICAT    RUNNING     R_5         00:00:00      00:00:01    


GGSCI (srtcreen) 6> stats r_3

Sending STATS request to REPLICAT R_3 ...

No active replication maps
DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   0.00
        Mapped operations                            0.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
--进程启动，工作正常

三、补充说明
1、部署ogg前需要核对两边时间是否相同，为了避免不必要的麻烦，建议配置ntp同步时间
2、这里因为我们这要求该同步上线无严格时间要求，所以通过重设r_3时间点实现工作正常，如果事情比较紧急，建议删除trail文件，重建pump和replicat进程
3、这里说明ogg同步也还不是和时间一点关系都没有

发表在 GoldenGate | 一条评论

17813235971

QQ 咨询

分类目录归档：GoldenGate

ogg同步部分列配置

利用impdp结合network_link+FLASHBACK_TIME初始化ogg同步数据

时间不同步导致ogg部署异常

文章分类

最近发表

分类目录归档：GoldenGate

ogg同步部分列配置

利用impdp结合network_link+FLASHBACK_TIME初始化ogg同步数据

时间不同步导致ogg部署异常

标签云

文章分类

最近发表