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

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

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

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

部署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, 逻辑备份/恢复 分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>