Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Rely constraint and ROWID Tips

Oracle Database Tips by Donald BurlesonDecember 3, 2015

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.

The above text is an excerpt from the book: Oracle Data Guard Handbook

Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.