Shulou(Shulou.com)06/01 Report--

Learn about 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, report ORA-01403: no data found because the source side needs to insert and update a piece of data, the destination side already exists, or the source side wants to delete a piece of data, and the destination side has deleted it. The destination will report an error and hang up. Generally speaking, what we deal with is to determine which data is the problem first. if the number is small, you can delete or add this data at the target end, and if there are a large number of designs, skip the log application of this section (for example, insert or delete 10 entries into or delete a table) and proceed to the next application. If you can't determine how many logs to skip, or if it's troublesome, initialize a single table directly.

Let's do a simple experiment to skip the log.

Source configuration:

GGSCI (db1) 6 > info all

Program Status Group Lag 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 >

Destination side configuration:

GGSCI (db2) 4 > info all

Program Status Group Lag 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, the test OGG, the configuration is relatively simple, hehe. )

You can see several synchronized tables from the configuration, so we will experiment with the liuliu table.

The data on the source side and the destination side are the same:

Delete a piece of data on the destination side:

Info all is executed on the source side and the target side, and the main program and subroutine are normal. No problem.

Let's delete the data with an id of 2 on the source side:

At this point, both sides execute info all again, and query the status:

There is no problem with the source side, but the destination side becomes ABENDED:

GGSCI (db2) 8 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED REPFPZX 00:00:04 00:00:35

The following is the process of problem analysis and handling.

First of all, you have to query the configuration on both sides (I have posted it) to make sure that the error log file is

/ u01/goldengate/discard/rep2_discard.dsc

Go to this directory and view the log

[oracle@db2 discard] $pwd

/ u01/goldengate/discard

[oracle@db2 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

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 indicates that there was an error "ORA-01403: no data found" when deleting the data with ID 2 in the DB_FPZX.LIULIU table. At this time, you should check whether the data in the target database has been deleted (I deleted it here, of course).

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

Needless to say. There must be no such numbers in the target library, because they are already ABENDED.

The dump file is analyzed below:

Target end

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 51V 36.385195 RBA 1259

The query shows that the file used by repfpzx at this time is / dirdat/fp000006, so let's use the logdump tool to open this file.

[oracle@db2 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, 2012, Oracle and/or its affiliates. All rights reserved.

Logdump 36 > open. / dirdat/fp000006

Current LogTrail is / u01/goldengate/dirdat/fp000006

Logdump 37 > 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. three hundred and fifty five

Records/Trans... 1

Files/Trans. one

The Count command displays information. Since there are few operations in this file, you can see that the records include one delete and six inserts. A delete is to delete a record with an id of 2. Six inserts means that I insert one more time in addition to inserting 2, 3, 4, 5, 6 (I previously inserted a record with an id of 2). Of course, these data are useless. Over time, there are so many delete and insert counts that it is impossible and unnecessary to find out who they are.

Let's look at the details:

Logdump 38 > pos 1259-pos + RBA number, jump directly to this record, why jump to 1259 here? Because the query result of the info repfpzx command tells us that 1259 is the transaction he is currently dealing with.

Reading forward from RBA 1259

Logdump 39 > sfh-the most recent valid record after looking for this number. N stands for next, the next transaction.

Let's look at the picture and tell the story:

Marked with a red line, is the operation performed by this transaction

Marked by the × × horizontal line, it is the BRA of this transaction, so it can be seen that the BRA is not all + 1, but jumping!

It's marked with a red arrow, do I have to say it? That is, the key data, ah, 2, 3, 4, 4, are all ID!

Thus, a transaction with a RBA of 1259 is a delete operation, which involves data with an id of 2 in the db_fpzx.liuliu table. The result is the same as when we look at the dsc file. (nonsense)

Note: some key commands about the logdump tool:

Pos jumps to a RBA. This RBA does not need to exist and needs to be used with sfh.

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 our destination data has deleted this data, we need to skip 1259 and recover it directly from 1383.

(the other is to add this data manually, start the process, and 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 changes to the STOPPED state, and start is fine.

PS: I thought that before executing the above command, I would insert data with an ID of 4 into the target database, and OGG would insert me into the source database.

Insert into liuliu (id,name,www) values (2) Liuliu222')

Insert into liuliu (id,name,www) values (3) Liuliu33')

First "restore" to the target library, and then in the

Insert into liuliu (id,name,www) values (4) Liuliu444')

ABENDED again in this sentence, and then the data in the target database should be 1, 2, 3, 4, no 5, 6.

I then execute Alter replicat repfpzx (process name), extrba (RBA number of ID 5), restart the process, and 5 and 6 can enter the target library.

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

Then execute:

Alter replicat repfpzx,extrba 1383

The situation I wanted didn't happen on the target side:

Instead, there are still only 1 and 4 pieces of data, and the process is not up.

Viewing the dsc file shows:

…… Omit.

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 (: a0jinghua1jinga2jinghongzhua3jinghua4)

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

It feels like OGG inserts these statements.

Insert into liuliu (id,name,www) values (2) Liuliu222')

Insert into liuliu (id,name,www) values (3) Liuliu33')

Insert into liuliu (id,name,www) values (4) Liuliu444')

Insert into liuliu (id,name,www) values (5) Liuliu555')

Insert into liuliu (id,name,www) values (6) Liuliu 666')

Implemented as a whole, as long as one of them is not in the past, the others will not be enough.

Is it because I submitted once in the same session? If I submit one item at a time, I wonder if there will be the kind of result I want?

Let's do the experiment later.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.