联系:手机/微信(+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