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.ph ... 6orderby%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