goldengate 生产端队列文件不自动清除
生产端环境hp-ux 11.31
database oracle 10.2.0.4
goldengate 11.2.1.0.4
GGSCI (qhpmsdb1) 8> view params mgr
PORT 7839
DYNAMICPORTLIST 7840-7914
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
USERID goldengate, PASSWORD AACAAAAAAAAAAAIAHBCCSJVDMAOGHIOH, ENCRYPTKEY default
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 5
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (qhpmsdb1) 9> view params extsa
EXTRACT extsa
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate, PASSWORD AACAAAAAAAAAAAIAHBCCSJVDMAOGHIOH, ENCRYPTKEY default
DDL &
INCLUDE MAPPED OBJTYPE 'table' &
INCLUDE MAPPED OBJTYPE 'index' &
EXCLUDE OPTYPE COMMENT
DDLOPTIONS ADDTRANDATA NOCROSSRENAME REPORT
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extsa.dsc,APPEND,MEGABYTES 1024
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 3m
EXTTRAIL ./dirdat/sa
--TRANLOGOPTIONS EXCLUDEUSER USERNAME
FETCHOPTIONS NOUSESNAPSHOT
TRANLOGOPTIONS altarchivelogdest primary instance pmsqh1 /oralog1, altarchivelogdest instance pmsqh2 /oralog2
tableexclude MW_APP.MWV_UD_PD_SB_SBTJ --------THIS IS MATERIALIZED VIEW
tableexclude MW_APP.MWV_UD_PD_SB_ZYSBTJ --------THIS IS MATERIALIZED VIEW
tableexclude MW_APP.STU_VIEW --------THIS IS MATERIALIZED VIEW
tableexclude MW_APP.MWV_UD_PD_SB_GTTJ --------THIS IS MATERIALIZED VIEW
tableexclude MW_APP.MWV_UD_PD_SB_SBTJ_SBSL --------THIS IS MATERIALIZED VIEW
tableexclude MW_APP.MWV_UD_BZQX_QUERY --------THIS IS MATERIALIZED VIEW
tableexclude MW_APP.MWV_UD_PD_BZQX_QUERY --------THIS IS MATERIALIZED VIEW
tableexclude MW_APP.MWV_JGDX_BM_SSDS --------THIS IS MATERIALIZED VIEW
table MW_SYS.*;
table COM.*;
table MW_RTM.*;
table MW_APP.*;
table GEPSTAR.*;
table JCAS_DJMUSER.*;
table JCAS_DMSUSER.*;
GGSCI (qhpmsdb1) 10> view params dpesa
EXTRACT dpesa
PASSTHRU
RMTHOST 10.200.115.24, MGRPORT 7839, compress
RMTTRAIL ./dirdat/sa
table MW_SYS.*;
table COM.*;
table MW_RTM.*;
table MW_APP.*;
table GEPSTAR.*;
table JCAS_DJMUSER.*;
table JCAS_DMSUSER.*;
GGSCI (qhpmsdb1) 11> view report mgr
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 11.2.1.0.4 14636914 OGGCORE_11.2.1.0.4_PLATFORMS_121007.2020
HP/UX, IA64, 64bit (optimized), Oracle 10g on Oct 8 2012 02:10:39
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2014-10-13 18:11:11
***********************************************************************
Operating System Version:
HP-UX
Version U, Release B.11.31
Node: qhpmsdb1
Machine: ia64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : 2147483648 2147483648
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 12854
Parameters...
PORT 7839
DYNAMICPORTLIST 7840-7914
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
USERID goldengate, PASSWORD AACAAAAAAAAAAAIAHBCCSJVDMAOGHIOH, ENCRYPTKEY default
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 5
2014-10-13 18:11:11 WARNING OGG-00959 PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 5 (MINKEEPFILES option not used.).
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
***********************************************************************
** Run Time Messages **
***********************************************************************
2014-10-13 18:11:11 INFO OGG-00983 Manager started (port 7839).
2014-10-13 18:11:11 INFO OGG-00953 Purging log history from goldengate.GGS_DDL_HIST older than 2014-10-03 17:11:11.806266: 0 ro
ws deleted from goldengate.GGS_DDL_HIST.
2014-10-13 18:11:11 INFO OGG-00953 Purging log history from goldengate.GGS_DDL_HIST_ALT older than 2014-10-03 17:11:11.813925:
0 rows deleted from goldengate.GGS_DDL_HIST_ALT.
2014-10-13 18:11:11 INFO OGG-00953 Purging log history from goldengate.GGS_MARKER older than 2014-10-03 17:11:11.818161: 0 rows
deleted from goldengate.GGS_MARKER.
2014-10-13 18:11:16 INFO OGG-00963 Command received from GGSCI on host qhpmsdb1:53019 (START EXTRACT DPESA ).
2014-10-13 18:11:16 INFO OGG-00975 EXTRACT DPESA starting.
2014-10-13 18:11:16 INFO OGG-00963 Command received from GGSCI on host qhpmsdb1:53020 (START EXTRACT EXTSA ).
2014-10-13 18:11:16 INFO OGG-00975 EXTRACT EXTSA starting.
2014-10-13 19:11:12 INFO OGG-00953 Purging log history from goldengate.GGS_DDL_HIST older than 2014-10-03 18:11:12.431058: 0 ro
ws deleted from goldengate.GGS_DDL_HIST.
2014-10-13 19:11:12 INFO OGG-00953 Purging log history from goldengate.GGS_DDL_HIST_ALT older than 2014-10-03 18:11:12.576344:
0 rows deleted from goldengate.GGS_DDL_HIST_ALT.
2014-10-13 19:11:12 INFO OGG-00953 Purging log history from goldengate.GGS_MARKER older than 2014-10-03 18:11:12.581641: 0 rows
deleted from goldengate.GGS_MARKER.
2014-10-13 20:11:13 INFO OGG-00953 Purging log history from goldengate.GGS_DDL_HIST older than 2014-10-03 19:11:13.510841: 0 ro
ws deleted from goldengate.GGS_DDL_HIST.
2014-10-13 20:11:13 INFO OGG-00953 Purging log history from goldengate.GGS_DDL_HIST_ALT older than 2014-10-03 19:11:13.598759:
0 rows deleted from goldengate.GGS_DDL_HIST_ALT.
2014-10-13 20:11:13 INFO OGG-00953 Purging log history from goldengate.GGS_MARKER older than 2014-10-03 19:11:13.603526: 0 rows
deleted from goldengate.GGS_MARKER.
GGSCI (qhpmsdb1) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPESA 00:00:05 00:00:04
EXTRACT RUNNING EXTSA 00:00:02 00:00:08
GGSCI (qhpmsdb1) 13> info *
EXTRACT DPESA Last Started 2014-10-13 18:11 Status RUNNING
Checkpoint Lag 00:00:05 (updated 00:00:06 ago)
Log Read Checkpoint File ./dirdat/sa016743
2014-10-14 08:44:23.000000 RBA 27167988
EXTRACT EXTSA Last Started 2014-10-13 18:11 Status RUNNING
Checkpoint Lag 00:00:02 (updated 00:00:00 ago)
Log Read Checkpoint Oracle Redo Logs
2014-10-14 08:44:30 Thread 1, Seqno 34655, RBA 76340128
SCN 3044.1362265301 (13075242714325)
Log Read Checkpoint Oracle Redo Logs
2014-10-14 08:44:31 Thread 2, Seqno 21598, RBA 46497328
SCN 3044.1362265315 (13075242714339)
GGSCI (qhpmsdb1) 14> info exttrail *
Extract Trail: ./dirdat/sa
Extract: DPESA
Seqno: 276
RBA: 27436466
File Size: 200M
Extract Trail: ./dirdat/sa
Extract: EXTSA
Seqno: 16743
RBA: 27595407
File Size: 200M
GGSCI (qhpmsdb1) 15> info exttrail ./dirdat/sa
Extract Trail: ./dirdat/sa
Extract: DPESA
Seqno: 276
RBA: 27748010
File Size: 200M
Extract Trail: ./dirdat/sa
Extract: EXTSA
Seqno: 16743
RBA: 27629928
File Size: 200M
GGSCI (qhpmsdb1) 16> info exttrail /goldengate/dirdat/sa
我已经在生产库上实施，并且解决了，我这种情况下生产端队列文件不自动删除的问题。（还有另一种情况是本论坛的管理员发表的队列文件不自动删除 http://t.askmaclean.com/forum.php?mod=viewthread&tid=3302&extra=page%3D2%26orderby%3Dlastpost ）
我碰到的情况导致原因的原因是：
OGG的mgr进程删除trail文件的判断逻辑为：
检查整个复制环境（包括源头和目的端）中的相同trail文件名的trail，获得最小的那个trail文件号，在此号之前的那些trail文件就会被OGG的mgr进程的PURGEOLDEXTRACTS 参数正常删除掉。
GGSCI (qhpmsdb1) 14> info exttrail *
Extract Trail: ./dirdat/sa
Extract: DPESA
Seqno: 276
RBA: 27436466
File Size: 200M
Extract Trail: ./dirdat/sa
Extract: EXTSA
Seqno: 16743
RBA: 27595407
File Size: 200M
查询发现投递进程DPESA在目标端生成的trail文件号是276，抽取进程EXTSA在生产端生成的trail文件是16743. 根据 OGG的mgr进程删除trail文件的判断逻辑方法。mgr获得最小的trail文件号是276，生产端没有比276小的trail文件了，因此mgr不会去删除trail文件。
解决办法：
1.使用不同的trail文件名字
2.如果已使用相同的trail文件名，需要投递进程DPESA在目标端生成的trail文件号，要与生产端抽取进程生成的trail文件号大小接近即可。
解决步骤：
1.
stop dpesa
2.
info dpesa
EXTRACT DPESA Last Started 2014-10-13 18:11 Status RUNNING
Checkpoint Lag 00:00:05 (updated 00:00:06 ago)
Log Read Checkpoint File ./dirdat/sa016743
2014-10-14 08:44:23.000000 RBA 27167988
3.
dblogin userid ogg, passwd ogg
4.
delete dpesa
5.
add extract dpesa,exttrailsource ./dirdat/sa
add rmttrail ./dirdat/sa, EXTRACT dpesa, MEGABYTES 200, seqno 16743 ---这里16743，是为了投递进程在目标端生成16743的trail文件，可以用 info dpesa detail 查询
6.
alter dpesa, extseqno 16743, extrba 27167988 ----这里的16743是原来投递进程的检查点位置
7.
start dpesa
8.(目标端执行)
stop repsa
alter repsa, extseqno 16743, extrba 0
start repsa
上面8个步骤基本解决问题了。
参考内容： http://blog.csdn.net/msdnchina/article/details/38346435
页:
[1]