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 







Data Guard Creating a Logical Standby Database

Oracle Database Tips by Donald BurlesonDecember 3, 2015


Creating a Logical Standby Database

The logical Oracle instance is a new feature of Oracle9i Data Guard technology. It has been introduced to cater to the demands of DBAs to have a Oracle instance that can simultaneously exist in query and recovery mode. As the name suggests, a logical Oracle instance is not a physical replica of the primary database in that it is not structurally the same as the primary database.

Instead, it is a complete or partial set of schema objects from the primary database. A logical Oracle instance can contain schema objects of its own that are not present in the primary database.


Creating a Logical Standby Database

Logical standby databases are recovered using the LogMiner technology of Oracle RDBMS. The core of logical standby database recovery is the archived redo log file from the primary database. These log files are converted into SQL statements using LogMiner and are applied to the logical Oracle instance using SQL apply service. Like physical standby databases, logical standby databases can participate in switchover and failover operations.

Because a logical database can be put in read-only and recovery mode simultaneously, data available in a logical Oracle instance is always current.  As a result, a logical Oracle instance is a much better option for reporting than is a physical standby database; however, a logical Oracle instance has many limitations which may outweigh its reporting capabilities in some scenarios.

Limitations of the Logical Standby Database

The following limitations apply to logical standby databases:

A logical Oracle instance does not support LONG, LONG RAW, BFILE, ROWID, UROWID, NCLOB and any of the collection data types. If the application contains any of these types, the usability of a logical Oracle instance in that environment should be evaluated. The script, unsup_objects.sql, from the code depot can be used to find the tables in the database that cannot be supported in a logical standby database.

Also see checking log standby unsupported features

Limitations of the Logical Standby Database

  • log_parallelism should be set to 1. What does it mean?  The log_parallelism parameter governs the concurrency of the redo allocation latch. If the database is experiencing contention on the redo allocation latch, set this parameter to a higher value to allow a parallel generation of redo. Usually, this contention is observed on high-end servers in the insert/update intensive databases. If the database is not too huge, and the database is not running on a 16 or more processor machine, this limitation should not be a problem.

  • There are few DDL statements that will be skipped on a logical Oracle instance during the SQL apply operation. Most of these operations are at system or database level. Skipping these statements should not cause any inconsistency in the application data.

Due to these limitations, it is likely that a logical Oracle instance will not make an attractive option for reporting and an even less attractive option for failover operations. Please note that in order to run a logical standby database in this environment, the database software will have to be upgraded to at least the patchset level.

Requirements for the Logical Standby Database

In order to create a logical standby database, some preparatory work on the primary database has to be completed before it can be used for a logical standby database.

Archive Logging and Force Logging

As explained earlier and in much detail in the "Physical Standby Database" section, the core of the standby database are the archived redo logs from the primary database. The primary database must run in the archive log mode and the FORCE LOGGING should be enabled on the primary database to capture all changes made within the database.

See using the reply constraint

Unique Identification of Rows in Tables

In a sample database, there is a schema called REPORTS, which has two tables without any primary keys or unique indexes. The output from script not_unique.sql reveals this:

OWNER                          TABLE_NAME                     B
------------------------------ ------------------------------ -
REPORTS                        ITEMLIST                       N
REPORTS                        ORDERS                         N

The value 'N' in the Bad_Column column shows that the table can be maintained in a logical Oracle instance even though it does not have a primary key/unique index.

If any of the tables contain a CLOB or BLOB, which is supported data type for a logical standby database, but does not have a primary key/unique index defined, the value of Bad_Column will be 'Y'. This means that a primary key/unique index or a disabled primary key RELY constraint needs to be defined using scalar column, i.e. non CLOB or BLOB columns for this table to be maintained in a logical Oracle instance environment.

The primary database should be put in supplemental logging mode before creating a logical standby database. Use the following statement to enable supplemental logging:


Unique Identification of Rows and Tables

This can be verified from the v$database view using the following query:


The following sample output shows the supplemental logging is enabled for a primary key and unique index:

--- ---

After enabling the supplemental logging on the primary database, use the alter system command to switch to a redo log file. This is required because a logical Oracle instance does not support archived redo logs that contain redo information with and without supplemental logging.



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.