在target端因为磁盘空间满replicat ABANDING,清理磁盘空间后,再次start rep1 ,报错:找不到上一个trace文件,此时,“杀招”出现了。
alter replicat rep1 extseqno 0 extrba 0;
然后在检查点检查中一直:
Current Checkpoint Detail:
Read Checkpoint #1
GGS Log Trail
Startup Checkpoint (starting position in the data source):
Sequence #: 0
RBA: 0
Timestamp: Not Available
Extract Trail: /oracle/oggrep/dirdat/lt
Current Checkpoint (position of last record read in the data source):
Sequence #: 0
RBA: 0
Timestamp: Not Available
Extract Trail: /oracle/oggrep/dirdat/lt
GGSCI (xxxdb1) 34> info rep1,detail
REPLICAT REP1 Last Started 2015-01-21 10:49 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint File /oracle/oggrep/dirdat/lt000000
First Record RBA 0
Extract Source Begin End
/oracle/oggrep/dirdat/lt000000 * Initialized * First Record
/oracle/oggrep/dirdat/lt000000 * Initialized * First Record
/oracle/oggrep/dirdat/lt000000 * Initialized * First Record
/oracle/oggrep/dirdat/lt001385 2015-01-11 23:51 2015-01-11 23:51
/oracle/oggrep/dirdat/lt001385 2015-01-11 23:51 2015-01-11 23:51
/oracle/oggrep/dirdat/lt001385 2015-01-11 23:51 2015-01-11 23:51
/oracle/oggrep/dirdat/lt001385 2015-01-11 23:51 2015-01-11 23:51
/oracle/oggrep/dirdat/lt001385 2015-01-11 23:51 2015-01-11 23:51
/oracle/oggrep/dirdat/lt001385 2015-01-11 23:51 2015-01-11 23:51
主要是trail文件没有了,不然可以再次alter replicat rep1 extseqno XXX extrba XXX; 试试的!
没办法,重新初始化了
ogg重新初始化一、在源端导出数据
SQL>select to_char(current_scn) from v$database; 查询当前scn
2719112586
导出数据
[oracle@yyghdb odc]$expdp odc/odc directory=odc_dir schemas=BOOKPLAT dumpfile=BOOKPLAT2.dmp flashback_scn=2719112586 logfile=BOOKPL[oracle@yyghdb odc]$ expdp odc/odc directory=odc_dir
二、目标端导入数据
drop user BOOKPLAT cascade
删除原有的同步用户
impdp odc/odc@zjhorcl1 directory=ODC_DIR dumpfile=bookplat2.dmp schemas=BOOKPLAT logfile=bookplat2.log
重新导入同步用户
Select trigger_name from dba_triggers where owner='BOOKPLAT' and status='ENABLED';
TRIG_DUP
查询同步用户触发器
alter trigger BOOKPLAT.TRIG_DUP disable;
禁用该触发器
移除JOB:
select job,log_user,schema_user from dba_jobs;
exec dbms_ijob.remove(<that job id>);
select * from dba_scheduler_jobs;
禁用:
DBMS_SCHEDULER.DISABLE('job1, job2, job3, sys.jobclass1, sys.jobclass2');
三、设置复制进程位置,并启动
查看dirdat下trail文件日期,找出数据导出时间前的trail文件
ls -rtl
total 3743578
-rw-rw-rw- 1 oracle oinstall 99999967 Apr 28 03:01 ws000686
-rw-rw-rw- 1 oracle oinstall 99999725 Apr 28 04:37 ws000687
-rw-rw-rw- 1 oracle oinstall 99999993 Apr 28 15:01 ws000688
设置复制进程位置,让复制进程从000687开始复制
/*在该步骤时要尽量选取靠近SCN时间的trail文件*/
alter ws_repc extseqno 000687 extrba 0
alter ws_rep4 extseqno 000687 extrba 0
/*===extseqno 指的就是trail文件的文件=====*/
用atcsn启动复制进程
start ws_rep4 atcsn 2719112586
start ws_repc atcsn 2719112586
查看进程状态,同步完成
GGSCI (ehrdb1) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING WS_REP4 00:00:00 30:33:17
REPLICAT RUNNING WS_REPC 00:00:00 03:15:02
在插入的时候会报以下错误:重复插入
2015-01-21 13:51:15 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep1.prm: OCI Error ORA-00001: unique constraint (AAAA.PK_WS_BUSINESS_LOG) violated (status = 1). INSERT INTO "AAAA"."TBL_WS_BUSINESS_LOG" ("PRIMARY_KEY","TRANS_ID","TRANS_DATE","TRANS_TIME","ORDER_STATE","EXTERNAL_KEY","BATCH_NO","SEQ_NO","BUSINESS_TYPE","PROV_CODE","CHANNEL","NODE_NO","NODE_NAME","OPE_NO","OPE_NAME","CARD_TYPE","CARD_NO","CARD_BIN","TXN_SCRAPE_PIN","TRANS_PIN","CVV","CVV2","SETTLE_DT","PAY_TYPE","PAYABLE_AMOUNT","REALITY_AMOUNT","AMT","OIL_CARD_NO","OIL_TER_SEQ_NO","COMMODITY_TP","COMMODITY_SMALL_TP","COMMODITY_ID","RESERVE","PRODUCTID","TOTALQUANTITY","REALITYAMOUNT","KEYID","MESSAGEKEY","PINKEY","MESSAGEKEYCHECKVALUE","PINKEYCHECKVALUE","REMARKS","RESP_CODE","RE_CEN_SEQ_NO","BALANCE","VAILDDATE","CREATE_TIME","MODIFY_TIME","STATUS","TRANS_SRC") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a17,:a18,:a19,:a20,:a21,:a22,:a23,:a24,:a25,:a26,:a27,:a28,:a29,:a30,:a31,:a32,:a33,:a34,:a35,:a36,:a37,:a38,:a39,:a40,:a41,:a42,:a43,:a44,:a45,:a46,:a47,:a48,:a49).
解决方法:
1 . 尝试加入 handlecollisions 参数解决该问题
2. 尝试手动修复 target 上的数据
3. 尝试加入REPERROR 参数 ignore这个错误
来自Maclean Liu 刘相兵
我直接使用handlecollisions,在追踪到最新的trail文件之前停止ogg,然后删除该参数。重启ogg
其中对checkpointtable的理解存在误区,导致自动purge时失败,手工删除,没文化太可怕!!!