联系：手机/微信(+86 17813235971) QQ(107644445)
标题：利用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