Rely constraint and ROWID Tips

Relying on Unique Identification of Rows in Data Guard Tables

In the physical standby database, the recovery is achieved using the rowid of segments. Since a logical standby database is not structurally exact as the primary database, the rowid between the primary and the Oracle instance will not be the same. For a logical standby database, the SQL apply service must use some other mechanism to uniquely identify a row in tables.

Usually, the primary key and non-null unique indexes are the best available data structure that can be used to identify a row in a table. In general, it is good practice for a database design to have a primary key or unique index on tables; however, this may not always be the case in any database.

Although having a primary key or unique index on tables will assist in recovery on a logical standby site, it is not an absolute requirement for logical Oracle instance setup. The SQL apply service can use other scalar columns from the tables to determine the uniqueness of rows.

Normally, when a row is updated in a database, its old and new values, along with the rowid, are written in the redo log. The redo log does not contain any information about the primary key or unique key for that row. Since a logical standby database depends on such an identifier to uniquely identify a row, Oracle introduced a Supplemental Logging mechanism for logical standby databases.

ROWID and supplemental logging

When the supplemental logging is active on a database, the redo logs contain other columns from tables to uniquely identify a row. If the table has a primary key or unique index defined, the only columns involved in the primary key or unique index will be registered in the redo logs along with the actual column(s) that has changed.

If the table does not have any primary keys or unique index defined, Oracle will write all scalar columns from the table to identify the row. This may significantly increase the size of redo logs and will impact the log apply services on the logical standby site.

One way to resolve this issue is to define a disabled primary key RELY constraint on those tables that do not have a primary key/unique index defined. Creating a disabled primary key will not add any extra overhead in maintaining the primary key on the primary database, yet it will minimize the information required in redo logs for logical Oracle instance recovery.

WARNING – Before creating the Primary Key RELY constraint, it is vital that the DBA understand the application data very well. Alternatively, the application developers can help with constraints definitions.

Putting a database in Supplemental Logging mode is a requirement for a logical standby database.  Even if all the tables in the database have a primary key defined, supplemental logging will have to be activated so that the primary key columns will be written to the redo logs along with the updated columns.