OGG Single-table initialization procedure

know Logdump analysis tools and common commands:http://book.51cto.com/art/201202/319253.htm

Http://www.killdb.com/2012/09/01/goldengate-%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%974-logdump.html

In Ogg, reported ora-01403:no data found, the reason is generally the source to insert, update a data, the target has already existed, or the source to delete a piece of data, the target has been deleted. The target will get an error and then hang up. Generally we deal with, is to determine the data is the problem, if the number of small, delete or add this data on the target, if the number of design more, skip this section of the log application (such as a table for 10 insertions or deletions) directly to the next paragraph of the application. If you can't tell how many logs to skip, or if you're having trouble, simply initialize the single table.

The following is a simple experiment that skips logs.

Source-side configuration:

Ggsci (DB1) 6> info All

Program Status Group lags at chkpt time Since chkpt

MANAGER RUNNING

EXTRACT RUNNING extfpzx 00:00:00 00:00:03

Ggsci (DB1) 7> view params extfpzx

Extract EXTFPZX

UserID Ogg,password ogg

Rmthost 192.168.25.101,mgrport 7809

Rmttrail/u01/goldengate/dirdat/fp

DDL include mapped objname db_fpzx.*;

Table Db_fpzx.liuliu;

Table db_fpzx.eee;

Table db_fpzx.eee1;

Ggsci (DB1) 8>

Target-side configuration:

Ggsci (DB2) 4> info All

Program Status Group lags at chkpt time Since chkpt

MANAGER RUNNING

Replicat RUNNING repfpzx 00:00:00 00:00:00

Ggsci (DB2) 5> view params repfpzx

Replicat REPFPZX

USERID Ogg,password ogg

DISCARDFILE/U01/GOLDENGATE/DISCARD/REP2_DISCARD.DSC, append, megabytes 10

DDL INCLUDE MAPPED

Ddlerror DEFAULT IGNORE RETRYOP

Assumetargetdefs

Map Db_fpzx.liuliu, Target Db_fpzx.liuliu;

Map db_fpzx.eee, Target db_fpzx.eee;

Map db_fpzx.eee1, Target db_fpzx.eee1;

Mapexclude Db_fpzx.liu123,tableexclude db_fpzx.liu123;

Ggsci (DB2) 6>

(Sorry, do the test Ogg, the configuration is relatively simple, hey.) ）

From the configuration can see the synchronization of several tables, we use liuliu this table to do the experiment.

The source and destination data are the same:

Delete a single piece of data on the target side:

The source and target side execute info All, both the main program and the subroutine are normal. No problem.

Below we delete the data with ID 2 on the source side:

At this time, the two sides execute the info all, query status:

The source side is no problem, but the target end becomes abended:

Ggsci (DB2) 8> info All

Program Status Group lags at chkpt time Since chkpt

MANAGER RUNNING

Replicat abended repfpzx 00:00:04 00:00:35

The following is the problem analysis and processing process

First you have to query both ends of the configuration (I have posted) to determine if the error log file is

/u01/goldengate/discard/rep2_discard.dsc

Go to this directory to view the log

[Email protected] discard]$ pwd

/u01/goldengate/discard

[Email protected] discard]$ more REP2_DISCARD.DSC

Oracle GoldenGate Delivery for Oracle process started, group repfpzx discard file opened:2016-11-25 15:40:10

Current time:2016-11-25 15:51:40

Discarded record from action ABEND on error 1403

OCI Error ORA-01403: No data found, SQL <delete from "Db_fpzx". " Liuliu "WHERE" ID =:b0>

Aborting transaction on/u01/goldengate/dirdat/fp beginning at Seqno 6 RBA 1259

Error at Seqno 6 RBA 1259

Problem replicating db_fpzx. Liuliu to DB_FPZX. Liuliu

Record not found

Mapping problem with delete record (target format) ...

*

ID = 2

*

Process abending:2016-11-25 15:51:40

The log clearly points to the deletion of DB_FPZX. Liuliu the data in the table ID 2, out of the wrong "ORA-01403: No data found", this time should be checked to see if this data is deleted in the target library (I am of course deleted)

Let's add a few more data to the source side:

Don't say much. There must be no such number in the target library because it has been abended.

The dump file is analyzed below:

Target side

Ggsci (DB2) 10> info REPFPZX

Replicat repfpzx last Started 2016-11-25 15:40 Status abended

Checkpoint Lag 00:00:04 (updated 00:17:23 ago)

Log Read Checkpoint file/u01/goldengate/dirdat/fp000006

2016-11-25 15:51:36.385195 RBA 1259

Query shows repfpzx the file is/dirdat/fp000006, let's use Logdump tool to open this file to see

[Email protected] goldengate]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle

Version 11.2.1.0.1 oggcore_11.2.1.0.1_platforms_120423.0230

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

Logdump >open./dirdat/fp000006

Current Logtrail is/u01/goldengate/dirdat/fp000006

Logdump PNS >count

LOGTRAIL/U01/GOLDENGATE/DIRDAT/FP000006 has 9 records

Total Data Bytes 1347

AVG Bytes/record 149

Delete 1

Insert 6

Restartok 1

Others 1

Before Images 1

After Images 7

Average of 5 transactions

Bytes/trans ..... 355

Records/trans ... 1

Files/trans ..... 1

The Count command displays information. Because this file operation is relatively small, you can see that the records inside include a delete, 6 times insert. Deleting a record with an ID of 2 is deleted at a time. 6 Insertions It's me. There is also an insert (I previously inserted a record with ID 2) in addition to inserting 2,3,4,5,6. Of course, this data is not useful, the time is long the delete and insert count is too much, there is no need to find out who is.

Let's look at the details below:

Logdump >pos 1259 ----pos + RBA, jump directly to this record, why jump to 1259 here? Because the info repfpzx command query results tell us that 1259 is the transaction that he is currently working on.

Reading forward from RBA 1259

Logdump >sfh ----Looking for this number, a recent valid record. n represents next, the next transaction.

The following is a look at the picture to tell the story link:

The red line is marked with the operation of this transaction

The Yellow Line callout, is the bra of this transaction, this shows, bra not every +1, but jump!

The Red Arrows, do you want me to say it? Is the key data Ah 2,3,4, not all ID it!

Thus, a transaction with a RBA of 1259 is a delete operation that involves data with ID 2 in the Db_fpzx.liuliu table. And we look at the DSC file as the result. (Nonsense ~ ~ ~)

Note: Some key commands about the Logdump tool:

Pos jumps to a certain RBA, this RBA need not necessarily exist, need to cooperate with SFH use

Sfh jumps to the next valid RBA of the current RBA

POS EOF jumps to the end of the file

Pos Reverse Reverse Read

Now that we have deleted this data from our target data, we need to skip 1259 and recover directly from 1383.

(The other is to manually add this data, start the process, the log will naturally delete it, and then proceed to the next step)

The command is:

Alter replicat repfpzx (process name), Extrba 1383

After execution, the process becomes stopped state, and start it is OK.

PS: I thought I would insert the ID 4 data into the target library before executing the above command, and OGG would insert the

Insert into Liuliu (ID,NAME,WWW) VALUES (2, ' Liu ', ' liuliu222 ');

Insert into Liuliu (ID,NAME,WWW) VALUES (3, ' Liu ', ' liuliu333 ');

First "revert" to the target library, then

Insert into Liuliu (ID,NAME,WWW) VALUES (4, ' Liu ', ' liuliu444 ');

This sentence is abended again, then the data in the target library should be 1, 2, 3, 4, not 5, 6.

I'll do the alter REPLICAT REPFPZX (process name), Extrba (the RBA number with ID 5), restart the process, 5, 6 to get into the target library.

But to my surprise, I inserted the data on the target side:

After execution:

Alter Replicat Repfpzx,extrba 1383

The target does not appear the situation I want:

Instead, there are still only 1 and 42 data, and the process is not up.

View DSC file Display:

...... Omitted......

Current time:2016-11-25 16:44:17

Discarded record from action ABEND on Error 1

OCI Error ORA-00001: Unique constraint (DB_FPZX. sys_c0010982) violated (status = 1). INSERT into "db_fpzx". " Liuliu "(" ID "," Age "," NAME "," EEE "," WWW ") V

Alues (: A0,:A1,:A2,:A3,:A4)

Aborting transaction on/u01/goldengate/dirdat/fp beginning at Seqno 6 RBA 1383

Error at seqno 6 RBA 1697

Problem replicating db_fpzx. Liuliu to DB_FPZX. Liuliu

Mapping problem with insert record (target format) ...

*

ID = 4

Age = NULL

NAME = Liu

EEE = NULL

WWW = liuliu444

*

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

Operation discarded from Seqno 6 RBA 1383

Aborted insert from DB_FPZX. Liuliu to DB_FPZX. Liuliu (target format) ...

*

ID = 2

Age = NULL

NAME = Liu

EEE = NULL

WWW = liuliu222

*

Operation discarded from Seqno 6 RBA 1551

Aborted insert from DB_FPZX. Liuliu to DB_FPZX. Liuliu (target format) ...

*

ID = 3

Age = NULL

NAME = Liu

EEE = NULL

WWW = liuliu333

*

Process abending:2016-11-25 16:44:17

Oracle GoldenGate Delivery for Oracle process started, group repfpzx discard file opened:2016-11-25 16:52:14

Feeling OGG will these insert statements

Insert into Liuliu (ID,NAME,WWW) VALUES (2, ' Liu ', ' liuliu222 ');

Insert into Liuliu (ID,NAME,WWW) VALUES (3, ' Liu ', ' liuliu333 ');

Insert into Liuliu (ID,NAME,WWW) VALUES (4, ' Liu ', ' liuliu444 ');

Insert into Liuliu (ID,NAME,WWW) VALUES (5, ' Liu ', ' liuliu555 ');

Insert into Liuliu (ID,NAME,WWW) VALUES (6, ' Liu ', ' liuliu666 ');

As a whole execution, as long as there is no past, the others will not be enough.

Is it because I was the one who submitted it in the same session? If I submit a piece by line, do not know whether it will appear the kind of results I want?

Go back and do the experiment.

OGG Single-table initialization procedure