Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the example analysis of handlecollisions in ogg. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Handlecollisions

Experimental environment parameters

GGSCI (pc6 as ogg@hyyk) 11 > edit params ext_s1

Extract ext_s1

Setenv (NLS_LANG= "AMERICAN_AMERICA.AL32UTF8")

Setenv (ORACLE_SID= "hyyk")

Userid ogg,password oracle

Gettruncates

Exttrail / u01/app/oggs/dirdat/ss

Table sender.*

GGSCI (pc6 as ogg@hyyk) 13 > edit params pump_s1

Extract pump_s1

Passthru

Userid ogg,password oracle

Rmthost 192.168.1.80,mgrport 7809

Rmttrail / u01/app/oggd/dirdat/sd

Table sender.*

GGSCI (ogg-80) 5 > edit params rep_s1

Replicat rep_s1

Setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Setenv (ORACLE_SID='ogg')

Userid ogg,password oracle

-- handlecollisions

ASSUMETARGETDEFS

-- SOURCEDEFS / u01/app/oggd/dirdef/test.def

DISCARDFILE. / dirrpt/rorabb.dsc,PURGE

Userid ogg,password oracle

Map sender.tb21,target receiver.tb21

1. Create a table for both source and destination (of course, both source and destination must have primary keys)

Source:

Create table tb21 (col1 int primary key, col2 int)

Insert into tb21 values (1 dint 1)

Insert into tb21 values (2Jing 1)

Insert into tb21 values (3Phone1)

Insert into tb21 values (4Jing 1)

Commit

View data

SENDER@hyyk > select * from tb21

COL1 COL2

--

1 1

2 1

3 1

4 1

Goal:

Create table tb21 (col1 int primary key, col2 int)

Insert into tb21 values (1 dint 1)

Commit

View data

RECEIVER@ogg > select * from tb21

COL1 COL2

--

1 1

Add additional logs to join the replication link

GGSCI (pc6 as ogg@hyyk) 8 > add trandata sender.tb21

Logging of supplemental redo data enabled for table SENDER.TB21.

TRANDATA for scheduling columns has been added on table 'SENDER.TB21'.

TRANDATA for instantiation CSN has been added on table 'SENDER.TB21'.

2. Test delete

No handlecollisions

Source:

Delete from tb21 where col1=2

Commit

The destination side views the process status

GGSCI (ogg-80) 24 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED REP_S1 00:00:04 00:00:02

Replication process abend,2018-03-18 11:09:38 ERROR OGG-01296 Error mapping from SENDER.TB21 to RECEIVER.TB21.

Can be set on the target: add handlecollisions (or start rep2, skiptransaction) can skip, use stats rep2 to see

GGSCI (ogg-80) 32 > stats rep_s1

Sending STATS request to REPLICAT REP_S1...

Start of Statistics at 2018-03-18 11:13:44.

Replicating from SENDER.TB21 to RECEIVER.TB21:

* Total statistics since 2018-03-18 11:13:34 * *

Total inserts 0.00

Total updates 0.00

Total deletes 1.00

Total discards 0.00

Total operations 1.00

Total delete collisions 1.00

The delete here becomes delete collisions.

3. Test update

If this record does not exist on the target

(1) when the update key value:

Source:

Update tb21 set col1=5 where col1=3

Commit

SENDER@hyyk > select * from tb21

COL1 COL2

--

1 1

5 1

4 1

When there is no handlecollisions, abend

When there is handlecollisions, it becomes insert;, but you need to add: source side: FETCHOPTIONS FETCHPKUPDATECOLS (mirror the captured complete log into trail and convert it to insert a complete record of target, which is equivalent to fetchcol (*))

Target side view

RECEIVER@ogg > select * from tb21

COL1 COL2

--

5 1

1 1

We use logdump to view trail

2018-03-18 11:20:44.000.000 GGSUnifiedPKUpdate Len 31 RBA 1830

Name: SENDER.TB21 (TDR Index: 1)

After Image: Partition 12 G s

0000 0009 0000 0005 0000 0001 3300 0000 0500 0000 | .3.

0135 0001 0005 0000 0001 31 | .5.1

Before Image Len 13 (x0000000d)

BeforeColumnLen 9 (x00000009)

Column 0 (x0000), Len 5 (x0005)

After Image Len 18 (x00000012)

Column 0 (x0000), Len 5 (x0005)

Column 1 (x0001), Len 5 (x0005)

(2) when update is not a key value:

Source:

Update tb21 set col2=4 where col1=4

Commit

SENDER@hyyk > select * from tb21

COL1 COL2

--

1 1

6 5

5 1

4 4

Handlecollisions is added.

RECEIVER@ogg > select * from tb21

COL1 COL2

--

4 4

5 1

1 1

6 5

If there is no response with ogg11g plus handlecollisions, the data will be inconsistent.

It can be solved by adding an INSERTMISSINGUPDATES parameter to the target side.

4. Test insert

When the competition of source-side insert conflicts with the destination, the source-side will prevail.

The target first inserts a line:

Insert into tb21 values (6Jing 1)

Commit

RECEIVER@ogg > select * from tb21

COL1 COL2

--

5 1

1 1

6 1

Then the source side inserts:

Insert into tb21 values (6 and 5)

SENDER@hyyk > select * from tb21

COL1 COL2

--

1 1

6 5

5 1

4 1

Add handlecollisions

RECEIVER@ogg > select * from tb21

COL1 COL2

--

5 1

1 1

6 5

Depending on the record of the final target, it will actually be based on the source side.

After reading the above, do you have any further understanding of the sample analysis of handlecollisions in ogg? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.