Oracle GoldenGate 针对表没有主键或唯一索引的解决方案

http://blog.itpub.net/28258625/viewspace-2060713

This knowledge document describes a best practice method for handling tables without primary keys or unique indexes when using Oracle GoldenGate to replicate transactional data between Oracle databases.

There is a  change log  at the end of this document.

In This Document

This document is divided into the following sections:

• Section 1: Overview
• Section 2: Configuring Tables without PKs or UIs
• Section 3: References

• Appendix A: Sample Table Configuration

Section 1:  Overview

1.1 Solution Summary

In order to maintain data integrity when replicating transactional data, Oracle GoldenGate will use primary key columns or unique index columns to uniquely identify a row when issuing update or delete statements against the target database. If no primary keys or unique indexes exist on the table being replicated, Oracle GoldenGate will use all columns to uniquely identify a row.

It is perfectly acceptable to use all columns to uniquely identify a row under the following conditions:

• A logical key column cannot be defined for the table using the KEYCOLS parameter.
• No duplicate rows exist in the table
• Table contains a small number of rows, so full table lookups on the target database are minimal
• Table DML activity is very low, so "all column" table supplemental log groups do not negatively impact the source database redo logs

If the table being replicated does not meet all of the conditions listed above, it is recommended to add a column to the table with a SYS_GUID default value to uniquely identify the row. The next section describes the detailed steps on how to configure a table without a primary key or unique index in order to uniquely identify each row.

1.2 Required Software Components

Section 2:  Configuring Tables without PKs or UIs

2.1 Configure Table(s) in Source Database

Before instantiating the target database from a copy of source, perform. the following steps to the table(s) without primary keys or unique indexes.

Step 1 - Add Column to Table

Issue the following command to add the column to the table.

Replace <table_name> with the table name being modified.