Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › OGG-00869 OCI Error ORA-00001: unique constraint vio ...
发新帖
dai_xuej

82

积分

0

好友

0

主题

OGG-00869 OCI Error ORA-00001: unique constraint violated (status = 1)

1#
发表于 2012-5-9 13:30:38 | 查看: 20063| 回复: 15
1、生产端ogg版本信息：
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
HP/UX, IA64, 64bit(optimized), oracle 10g on Apr 22 2011 00:59:18
2、容灾端ogg版本信息：
[oracle@YZGJJ ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 11g on Oct  4 2011 23:49:46
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
3、GGSCI (YZGJJ) 1> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     RUNNING     MIDEXT      00:00:00      00:00:05   
REPLICAT    ABENDED     RORA_001    00:00:00      02:31:25   

GGSCI (YZGJJ) 2>
4、view report rora_001信息如下：
2012-05-09 10:49:33  INFO    OGG-01020  Processed extract process RESTART_ABEND record at seq 40, rba 912 (aborted 0 records).

MAP resolved (entry HFMIS.PMLSJX):
  map HFMIS.PMLSJX, target yzgjjgeter.PMLSJX;
Using following columns in default map by name:
  XH, PMACCOUNT, OCID, SCJXRQ, JXRQ, PMBJYE, LX, FX, FL, YQLX, YQBJ,
  YSBJ, PMLL, PMMONTH, PMBJ, NLX, NFX, NFL, NYQLX, NYQBJ, NYSBJ, NPMLL,
  NPMMONTH, NPMBJ

Using the following key columns for target table YZGJJGETER.PMLSJX: PMACCOUNT, XH.


2012-05-09 10:49:35  WARNING OGG-00869  OCI Error ORA-00001: unique constraint (YZGJJGETER.PK_FSUBBOOKNO) violated (status = 1), SQL <INSERT INTO "YZGJJGETER"."PMLSJX"
("XH","PMACCOUNT","OCID","SCJXRQ","JXRQ","PMBJYE","LX","FX","FL","YQLX","YQBJ","YSBJ","PMLL","PMMONTH","PMBJ","NLX","NFX","NFL","NYQLX","NYQBJ","NYSBJ","NPMLL","NPMMO>.

2012-05-09 10:49:35  WARNING OGG-01004  Aborted grouped transaction on 'YZGJJGETER.PMLSJX', Database error 1 (OCI Error ORA-00001: unique constraint (YZGJJGETER.PK_FSUB
BOOKNO) violated (status = 1), SQL <INSERT INTO "YZGJJGETER"."PMLSJX" ("XH","PMACCOUNT","OCID","SCJXRQ","JXRQ","PMBJYE","LX","FX","FL","YQLX","YQBJ","YSBJ","PMLL","PMMO
NTH","PMBJ","NLX","NFX","NFL","NYQLX","NYQBJ","NYSBJ","NPMLL","NPMMO>).

2012-05-09 10:49:35  WARNING OGG-01003  Repositioning to rba 975 in seqno 40.

2012-05-09 10:49:35  WARNING OGG-01154  SQL error 1 mapping HFMIS.PMLSJX to YZGJJGETER.PMLSJX OCI Error ORA-00001: unique constraint (YZGJJGETER.PK_FSUBBOOKNO) violated
(status = 1), SQL <INSERT INTO "YZGJJGETER"."PMLSJX" ("XH","PMACCOUNT","OCID","SCJXRQ","JXRQ","PMBJYE","LX","FX","FL","YQLX","YQBJ","YSBJ","PMLL","PMMONTH","PMBJ","NLX
","NFX","NFL","NYQLX","NYQBJ","NYSBJ","NPMLL","NPMMO>.

2012-05-09 10:49:35  WARNING OGG-01003  Repositioning to rba 975 in seqno 40.

Source Context :
  SourceModule            : [er.main]
  SourceID                : [/scratch/pradshar/view_storage/pradshar_bugdbrh40_12927937/oggcore/OpenSys/src/app/er/rep.c]
  SourceFunction          : [take_rep_err_action]
  SourceLine              : [16148]
  ThreadBacktrace         : [8] elements
                          : [/home/oracle/ogg/replicat(CMessageContext::AddThreadContext()+0x26) [0x5f2ac6]]
                          : [/home/oracle/ogg/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x5e9562]]
                          : [/home/oracle/ogg/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, DBString<777> const&, DBString<777> const&, CMessageFactory::Messa
geDisposition)+0x9b) [0x5c79bb]]
                          : [/home/oracle/ogg/replicat [0x832a3a]]
                          : [/home/oracle/ogg/replicat [0x910279]]
                          : [/home/oracle/ogg/replicat(main+0x84b) [0x5081ab]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x38bd21d994]]
                          : [/home/oracle/ogg/replicat(__gxx_personality_v0+0x1da) [0x4e479a]]

2012-05-09 10:49:35  ERROR   OGG-01296  Error mapping from HFMIS.PMLSJX to YZGJJGETER.PMLSJX.
2#
发表于 2012-5-9 13:33:03

rora_001.prm如下

[oracle@YZGJJ dirprm]$ cat rora_001.prm
replicat rora_001
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ggs, password ggs
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
CHECKPOINTSECS 40
--assumetargetdefs
sourcedefs ./dirdef/source_001.def
DISCARDFILE ./dirrpt/rept1.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
ALLOWNOOPUPDATES
map HFMIS.CHKERROR, target yzgjjgeter.CHKERROR;
map HFMIS.CHKITEM, target yzgjjgeter.CHKITEM;
.........................................................................................

回复 只看该作者 道具 举报

3#
发表于 2012-5-9 14:57:08

DISCARDFILE如下：

[oracle@YZGJJ dirrpt]$ cat rept1111111.dsc
Oracle GoldenGate Delivery for Oracle process started, group RORA_001 discard file opened: 2012-05-09 14:41:21

Current time: 2012-05-09 14:41:21
Discarded record from action ABEND on error 1

OCI Error ORA-00001: unique constraint (YZGJJGETER.PK_FSUBBOOKNO) violated (status = 1), SQL <INSERT INTO "YZGJJGETER"."PMLSJX" ("XH","PMACCOUNT","OCID","SCJXRQ","JXRQ","PMBJYE","LX","FX","FL","YQLX","YQBJ","YSBJ","PMLL","PMMONTH","PMBJ","NLX","NFX","NFL","NYQLX","NYQBJ","NYSBJ","NPMLL","NPMMO>
Aborting transaction on ./dirdat/t1 beginning at seqno 40 rba 975
                         error at seqno 40 rba 2352
Problem replicating HFMIS.PMLSJX to YZGJJGETER.PMLSJX
Mapping problem with insert record (target format)...
*
XH = 162143
PMACCOUNT = 33100001037
OCID = 331
SCJXRQ = 2012-04-16 00:00:00
JXRQ = 2012-04-25 00:00:00
PMBJYE = 64127.59
LX = 0
FX = 0
FL = 0
YQLX = 0
YQBJ = 0
YSBJ = 64902.30
PMLL = 3.675000
PMMONTH = 780.00
PMBJ = 555.56
NLX = 0
NFX = 0
NFL = 0
NYQLX = 0
NYQBJ = 0
NYSBJ = 0
NPMLL = 0
NPMMONTH = 0
NPMBJ = 0
*

Continuing to discard records up to the last discarded record from action ABEND

Operation discarded from seqno 40 rba 975
Aborted insert from HFMIS.PMLSJX to YZGJJGETER.PMLSJX (target format)...
*
XH = 162143
PMACCOUNT = 33100098414
OCID = 331
SCJXRQ = 2012-04-16 00:00:00
JXRQ = 2012-04-25 00:00:00
PMBJYE = 334190.21
LX = 0
FX = 0
FL = 0
YQLX = 0
YQBJ = 0
YSBJ = 343008.62
PMLL = 3.583300
PMMONTH = 1840.76
PMBJ = 1005.75
NLX = 0
NFX = 0
NFL = 0
NYQLX = 0
NYQBJ = 0
NYSBJ = 0
NPMLL = 0
NPMMONTH = 0
NPMBJ = 0
*
Operation discarded from seqno 40 rba 1453
Aborted insert from HFMIS.PMLSJX to YZGJJGETER.PMLSJX (target format)...
*
XH = 162143
PMACCOUNT = 331120001213
OCID = 331
SCJXRQ = 2012-04-16 00:00:00
JXRQ = 2012-04-25 00:00:00
PMBJYE = 16999.95
LX = 0
FX = 0
FL = 0
YQLX = 0
YQBJ = 0
YSBJ = 17500.00
PMLL = 3.525000
PMMONTH = 500.00
PMBJ = 500.00
NLX = 0
NFX = 0
NFL = 0
NYQLX = 0
NYQBJ = 0
NYSBJ = 0
NPMLL = 0
NPMMONTH = 0
NPMBJ = 0
*
Operation discarded from seqno 40 rba 1903
Aborted insert from HFMIS.PMLSJX to YZGJJGETER.PMLSJX (target format)...
*
XH = 162143
PMACCOUNT = 33100000265
OCID = 331
SCJXRQ = 2012-04-16 00:00:00
JXRQ = 2012-04-25 00:00:00
PMBJYE = 29391.80
LX = 0
FX = 0
FL = 0
YQLX = 0
YQBJ = 0
YSBJ = 29771.70
PMLL = 3.675000
PMMONTH = 837.38
PMBJ = 666.67
NLX = 0
NFX = 0
NFL = 0
NYQLX = 0
NYQBJ = 0
NYSBJ = 0
NPMLL = 0
NPMMONTH = 0
NPMBJ = 0
*
Process Abending : 2012-05-09 14:41:21

[oracle@YZGJJ dirrpt]$

回复 只看该作者 道具 举报

4#
发表于 2012-5-9 15:16:19
action plan:

请贴出 YZGJJGETER.PMLSJX 表的DDL 定义

在replicate 端确认 存在相关的记录 ， 贴出以下SQL的输出


select  ora_rowscn,scn_to_timestamp(ora_rowscn) , XH,PMACCOUNT from YZGJJGETER.PMLSJX where
XH = 162143 and PMACCOUNT = 33100000265 and OCID = 331

回复 只看该作者 道具 举报

5#
发表于 2012-5-9 15:28:08

查看目标表发现了这些重复的记录，有没好的解决办法？

XH=162143 and PMACCOUNT=33100098414;
XH=162143 and PMACCOUNT=331120001213;
XH=162143 and PMACCOUNT=33100000265;
XH=162143 and PMACCOUNT=33100001037;

回复 只看该作者 道具 举报

6#
发表于 2012-5-9 15:31:37

查询后的输出

SQL> select  ora_rowscn,scn_to_timestamp(ora_rowscn) , XH,PMACCOUNT from YZGJJGETER.PMLSJX where
  2  XH = 162143 and PMACCOUNT = 33100000265 and OCID = 331;
ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                        XH PMACCOUNT
---------- --------------------------------------------------------------------------- ---------- ------------------------------------------------------------------------------------------------------
1379576080 09-MAY-12 03.07.47.000000000 PM                                                 162143 33100000265

回复 只看该作者 道具 举报

7#
发表于 2012-5-9 15:39:25

上面那个不准确

XH=162143 and PMACCOUNT=33100098414;
XH=162143 and PMACCOUNT=331120001213;
XH=162143 and PMACCOUNT=33100000265;
XH=162143 and PMACCOUNT=33100001037;
我看到目标表中有这几条重复记录，我做了先把这些重记录备份到一个表，之后从目标表中删除，但发现删了一条又出现一条，感觉这条路走不通。又把这4条记录插了回来，所以：
SQL> select  ora_rowscn,scn_to_timestamp(ora_rowscn) , XH,PMACCOUNT from YZGJJGETER.PMLSJX where
  2  XH = 162143 and PMACCOUNT = 33100000265 and OCID = 331;
输出是：
ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                        XH PMACCOUNT
---------- --------------------------------------------------------------------------- ---------- ------------------------------------------------------------------------------------------------------
1379576080 09-MAY-12 03.07.47.000000000 PM                                                 162143 33100000265

回复 只看该作者 道具 举报

8#
发表于 2012-5-9 15:43:23

这个结果是准确的

SQL> select  ora_rowscn,scn_to_timestamp(ora_rowscn) , XH,PMACCOUNT from YZGJJGETER.PMLSJX where
  2  XH=162143 and PMACCOUNT=31100005864 and OCID = 331;

ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                        XH PMACCOUNT
---------- --------------------------------------------------------------------------- ---------- ------------------------------------------------------------------------------------------------------
1379574652 09-MAY-12 03.00.28.000000000 PM                                                 162143 31100005864

回复 只看该作者 道具 举报

9#
发表于 2012-5-9 16:01:57

表的DDL 定义

CREATE TABLE "YZGJJGETER"."PMLSJX"
   (    "XH" NUMBER(*,0) NOT NULL ENABLE,
        "PMACCOUNT" VARCHAR2(75) NOT NULL ENABLE,
        "OCID" CHAR(9) NOT NULL ENABLE,
        "SCJXRQ" DATE NOT NULL ENABLE,
        "JXRQ" DATE NOT NULL ENABLE,
        "PMBJYE" NUMBER(18,2) NOT NULL ENABLE,
        "LX" NUMBER(18,2) NOT NULL ENABLE,
        "FX" NUMBER(18,2) NOT NULL ENABLE,
        "FL" NUMBER(18,2) NOT NULL ENABLE,
        "YQLX" NUMBER(18,2),
        "YQBJ" NUMBER(18,2),
        "YSBJ" NUMBER(18,2) NOT NULL ENABLE,
        "PMLL" NUMBER(18,6) NOT NULL ENABLE,
        "PMMONTH" NUMBER(18,2) NOT NULL ENABLE,
        "PMBJ" NUMBER(18,2) NOT NULL ENABLE,
        "NLX" NUMBER(18,2) NOT NULL ENABLE,
        "NFX" NUMBER(18,2) NOT NULL ENABLE,
        "NFL" NUMBER(18,2) NOT NULL ENABLE,
        "NYQLX" NUMBER(18,2),
        "NYQBJ" NUMBER(18,2),
        "NYSBJ" NUMBER(18,2) NOT NULL ENABLE,
        "NPMLL" NUMBER(18,6),
        "NPMMONTH" NUMBER(18,2) NOT NULL ENABLE,
        "NPMBJ" NUMBER(18,2) NOT NULL ENABLE,
         CONSTRAINT "PK_FSUBBOOKNO" PRIMARY KEY ("PMACCOUNT", "XH")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTIC
S
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO
OL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFA
ULT)
  TABLESPACE "YZGJJDB"  ENABLE,
         SUPPLEMENTAL LOG GROUP "GGS_PMLSJX_81298" ("XH", "PMACCOUNT") AL
WAYS
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAUL
T)
  TABLESPACE "YZGJJDB"
;

回复 只看该作者 道具 举报

10#
发表于 2012-5-9 17:06:24
1 . 尝试加入 handlecollisions 参数解决该问题

2.  尝试手动修复 target 上的数据

3. 尝试加入REPERROR  参数 ignore这个错误

回复 只看该作者 道具 举报

11#
发表于 2012-5-9 17:16:31

以下是之前生产端报错的处理过程，生产端的处理不妥当？

GGSCI (yzgjjhp2) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     ABENDED     EORA_001    51:12:27      262:44:55   
EXTRACT     RUNNING     PORA_001    00:00:00      00:00:09
GGSCI (yzgjjhp2) 2> view report eora_001
***********************************************************************
                 Oracle GoldenGate Capture for Oracle
        Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
  HP/UX, IA64, 64bit (optimized), Oracle 10g on Apr 22 2011 01:27:04

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2012-05-08 09:37:24
***********************************************************************

Operating System Version:
HP-UX
Version U, Release B.11.31
Node: yzgjjhp2
Machine: ia64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :   4294967296   4294967296
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 13417

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
extract eora_001
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ggs,password ******************************************* DEFAULT
--TRANLOGOPTIONS ARCHIVEDLOGONLY
--TRANLOGOPTIONS ALTARCHIVELOGDEST /oradata03/archive
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS altarchivelogdest primary instance hfmis1 /home/ogg/archlog1, altarchivelogdest instance hfmis2 /ogg/archlog2
WARNLONGTRANS 2h,CHECKINTERVAL 3m
DYNAMICRESOLUTION
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extya.dsc,APPEND,MEGABYTES 1024
exttrail ./dirdat/t1
table HFMIS.CHKERROR;
..............................................
Bounded Recovery Parameter:
BRINTERVAL = 4HOURS
BRDIR      = /ogg

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                                8G
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):          16G
CACHESIZEMAX (strict force to disk):  13.99G

2012-05-08 09:37:24  INFO    OGG-01639  BOUNDED RECOVERY:  ACTIVE: for object pool 1: p20903_Redo Thread 1.

2012-05-08 09:37:24  INFO    OGG-01640  BOUNDED RECOVERY: recovery start XID: 7.28.24351.

2012-05-08 09:37:24  INFO    OGG-01641  BOUNDED RECOVERY: recovery start position: SeqNo: 3966, RBA: 54761488, SCN: 0.1379135368 (1379135368), Timestamp: 2012-04-24 18:01:20.000000.

2012-05-08 09:37:24  INFO    OGG-01642  BOUNDED RECOVERY: recovery end position: SeqNo: 3966, RBA: 123728896, SCN: 0.1379331976 (1379331976), Timestamp: 2012-04-25 09:58:25.000000.

2012-05-08 09:37:24  INFO    OGG-01639  BOUNDED RECOVERY:  ACTIVE: for object pool 2: p20903_Redo Thread 2.

2012-05-08 09:37:24  INFO    OGG-01640  BOUNDED RECOVERY: recovery start XID: 0.0.0.

2012-05-08 09:37:24  INFO    OGG-01641  BOUNDED RECOVERY: recovery start position: SeqNo: 5517, RBA: 123323408, SCN: 0.1379131645 (1379131645), Timestamp: 2012-04-24 17:56:08.000000.

2012-05-08 09:37:24  INFO    OGG-01642  BOUNDED RECOVERY: recovery end position: SeqNo: 5517, RBA: 123728896, SCN: 0.1379131645 (1379131645), Timestamp: 2012-04-24 17:56:08.000000.

2012-05-08 09:37:24  INFO    OGG-01579  BOUNDED RECOVERY: VALID BCP: CP.EORA_001.000000066.

2012-05-08 09:37:24  INFO    OGG-01629  BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 18.

Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"

2012-05-08 09:37:24  INFO    OGG-00546  Default thread stack size: 262144.

2012-05-08 09:37:24  INFO    OGG-00547  Increasing thread stack size from 262144 to 1048576.

2012-05-08 09:37:24  INFO    OGG-01513  Positioning to (Thread 1) Sequence 3966, RBA 54761488, SCN 0.1379135368.

2012-05-08 09:37:24  INFO    OGG-01516  Positioned to (Thread 1) Sequence 3966, RBA 54761488, SCN 0.1379135368, Apr 24, 2012 6:01:20 PM.

2012-05-08 09:37:24  INFO    OGG-01513  Positioning to (Thread 2) Sequence 5517, RBA 123323408, SCN 0.1379131645.

2012-05-08 09:37:24  INFO    OGG-01516  Positioned to (Thread 2) Sequence 5517, RBA 123323408, SCN 0.1379131645, Apr 24, 2012 5:56:08 PM.

2012-05-08 09:37:24  INFO    OGG-01517  Position of first record processed for Thread 1, Sequence 3966, RBA 54761488, SCN 0.1379135368, Apr 24, 2012 6:01:20 PM.

2012-05-08 09:37:24  INFO    OGG-01517  Position of first record processed for Thread 2, Sequence 5517, RBA 123323408, SCN 0.1379131611, Apr 24, 2012 5:56:08 PM.

2012-05-08 09:37:24  INFO    OGG-01644  BOUNDED RECOVERY: COMPLETE: for object pool 2: p20903_Redo Thread 2 at SeqNo: 5518, RBA: 2064, SCN: 0.1379131647 (1379131647).

2012-05-08 09:37:24  INFO    OGG-01056  Recovery initialization completed for target file ./dirdat/t1000003, at RBA 4965566, CSN 1379246523.

Source Context :
  SourceModule            : [er.main]
  SourceID                : [/scratch/aime1/adestore/views/aime1_staoi06/oggcore/OpenSys/src/app/er/rep.c]
  SourceFunction          : [main]
  SourceLine              : [26195]

2012-05-08 09:37:24  ERROR   OGG-01476  The previous run abended due to an out of order transaction. Issue ALTER ETROLLOVER to advance the output trail sequence past the current trail sequence number, then re
start. Then, use ALTER EXTSEQNO on the subsequent pump EXTRACT, or REPLICAT, process group to start reading from the new trail file created by ALTER ETROLLOVER; the downstream process will not automatically s
witch to the new trail file.

2012-05-08 09:37:25  ERROR   OGG-01668  PROCESS ABENDING.


发现生产端这个故障时做了如下外理：
1、alter extract EORA_001, etrollover
2、start eora_001
3、info all
当时看到抓取进程正常启动了，而且抓到了数据，但是发现pump PORA_001，没有active map,于是
5、stop PORA_001
6、start PORA_001
但还是没有数据变化，于是
7、view report pora_001
以下是输出：
2012-05-09 10:13:54  INFO    OGG-01226  Socket buffer size set to 27985 (flush size 27985).

2012-05-09 10:13:54  INFO    OGG-01055  Recovery initialization completed for target file ./dirdat/t1000038, at RBA 1044.

2012-05-09 10:13:54  INFO    OGG-01478  Output file ./dirdat/t1 is using format RELEASE 10.4/11.1.

2012-05-09 10:13:54  INFO    OGG-01026  Rolling over remote file ./dirdat/t1000039.

2012-05-09 10:13:54  INFO    OGG-01053  Recovery completed for target file ./dirdat/t1000039, at RBA 1004.

2012-05-09 10:13:54  INFO    OGG-01057  Recovery completed for all targets.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************

Opened trail file ./dirdat/t1000003 at 2012-05-09 10:13:54

2012-05-09 10:13:58  WARNING OGG-01519  Waiting at EOF on input trail file ./dirdat/t1000003, which is not marked as complete; but succeeding trail file ./dirdat/t1000004 exists. If ALTER ETROLLOVER has been
performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader.

2012-05-09 10:14:04  INFO    OGG-01021  Command received from GGSCI: STATS.

2012-05-09 10:14:08  WARNING OGG-01519  Waiting at EOF on input trail file ./dirdat/t1000003, which is not marked as complete; but succeeding trail file ./dirdat/t1000004 exists. If ALTER ETROLLOVER has been
performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader.

2012-05-09 10:14:29  WARNING OGG-01519  Waiting at EOF on input trail file ./dirdat/t1000003, which is not marked as complete; but succeeding trail file ./dirdat/t1000004 exists. If ALTER ETROLLOVER has been
performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader.

2012-05-09 10:15:09  WARNING OGG-01519  Waiting at EOF on input trail file ./dirdat/t1000003, which is not marked as complete; but succeeding trail file ./dirdat/t1000004 exists. If ALTER ETROLLOVER has been
performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader.
看到 OGG-01519执行了如下操作：
8.1 stop PORA_001
8.2 alter PORA_001, etrollover
8.3 start PORA_001
之后stats pora_001还是没有active map
9、view report  pora_001
还是发现报：WARNING OGG-01519  Waiting at EOF on input trail file ./dirdat/t1000003
10.1  stop pora_001
10.2  alter extract pora_001， extseqno 3,extrba 0
10.3 start pora_001
再stats pora_001发现有数据更新

回复 只看该作者 道具 举报

12#
发表于 2012-5-9 17:30:15

之前生产端报错的处理过程，生产端的处理不妥当？接上

再回到容灾端发现入库rora_001 abended报错如下：
2012-05-09 10:47:21  WARNING OGG-01519  Oracle GoldenGate Delivery for Oracle, rora_001.prm:  Waiting at EOF on input trail file ./dirdat/t1000039, which is not marked
as complete; but succeeding trail file ./dirdat/t1000040 exists. If ALTER ETROLLOVER has been performed on source extract, ALTER EXTSEQNO must be performed on each co
rresponding downstream reader.
11.1 stop rora_001
11.2 alter replicate rora_001， extseqno 40,extrba 0
11.3 start rora_001
12、GGSCI (YZGJJ) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     MIDEXT      00:00:00      00:00:01   
REPLICAT    ABENDED     RORA_001    00:00:00      06:32:23   
发现rora_001 ABENDED查看报错就看到了贴子开始的
WARNING OGG-00869  OCI Error ORA-00001: unique constraint (YZGJJGETER.PK_FSUBBOOKNO) violated (status = 1), SQL <INSERT INTO "YZGJJGETER"."PMLSJX"
("XH","PMACCOUNT","OCID","SCJXRQ","JXRQ","PMBJYE","LX","FX","FL","YQLX","YQBJ","YSBJ","PMLL","PMMONTH","PMBJ","NLX","NFX","NFL","NYQLX","NYQBJ","NYSBJ","NPMLL","NPMMO>.
2012-05-09 10:49:35  WARNING OGG-01004  Aborted grouped transaction on 'YZGJJGETER.PMLSJX', Database error 1 (OCI Error ORA-00001: unique constraint (YZGJJGETER.PK_FSUB
BOOKNO) violated (status = 1), SQL <INSERT INTO "YZGJJGETER"."PMLSJX" ("XH","PMACCOUNT","OCID","SCJXRQ","JXRQ","PMBJYE","LX","FX","FL","YQLX","YQBJ","YSBJ","PMLL","PMMO
NTH","PMBJ","NLX","NFX","NFL","NYQLX","NYQBJ","NYSBJ","NPMLL","NPMMO>).

回复 只看该作者 道具 举报

13#
发表于 2012-5-10 14:28:23
你这个地方有问题啊：
33.jpg
这个地方应该是 4

回复 只看该作者 道具 举报

14#
发表于 2012-5-10 15:34:11
1.jpg
2.jpg
这个  -1  是怎么来的？

回复 只看该作者 道具 举报

15#
发表于 2012-5-10 19:47:52

回楼上的

我的理解 -1是指的 ora-00001 ，即忽略掉重复数据的插入，不知正确否？

回复 只看该作者 道具 举报

16#
发表于 2012-5-10 20:05:08

goldengate reperror参数

本贴中故障的最后外理方案是先加了如下参数：
Reperror (default, abend)
Reperror (-1, ignore)
然后:
start replicate
但是replicate 没runing多久就abend了，view report发现了1403
在确定了各个表上有主键的情况下加了 HANDLECOLLISIONS 参数
start repilcate 后注意检查，discardfile当中之前入不了库的记录都成功入库，注意discardfile中因为没有原记录而不能执行的
update会变成insert 去执行，因不能找到原记录的delete会是变成什么呢？

discardfile当中之前入不了库的记录都成功入库并且replicate运行平稳后，stop replicate 去除之前加入的 reperror及HANDLECOLLISIONS 参数，然后start replicate

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 16:19 , Processed in 0.073456 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569