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 Schema Object tips

Oracle Database Tips by Donald BurlesonDecember 9, 2015


Oracle Data Guard Managing Schema Objects

A logical Oracle instance can contain schema objects that are not present in the primary database. Additionally, the DBA might want to create some supporting data structures such as index or materialized views in a logical Oracle instance to speed up the reporting queries. In order to alter or create a schema object in logical standby database, the appropriate user access should be instated. The database guard controls the user access in a logical standby database. The access levels that can be established are as follows:

  • NONE - In this mode, the logical database is not protected by database guard.  Any user can alter any objects in the database as long as the SQL Apply operation is not running.


  • STANDBY - In this mode, only users with SYS privilege can modify the objects maintained by the log apply service. All users subject to the usual security policies can modify other schema objects.

  • ALL - In this mode, only users with SYS privilege can modify any object in the database.

TIP - Users cannot issue any DDL or DML statement when the log apply service is running on the standby database. Once the SQL Apply operation starts, the DATABASE GUARD mode changes to ALL by default

The ALTER DATABASE GUARD statement can be used to set the access control on the database. For example, the following statement will set the access control to STANDBY:


The v$database view can be queried to see if DATABASE GUARD is on, and the access control level is as shown below:


In addition to controlling the user access to objects in the database, the access of the SQL Apply operation to objects and statements in the logical Oracle instance can be controlled. An object or a DDL statement can be set so that it will be skipped by the SQL Apply operations. The skipping and un-skipping of schema objects are achieved by using the dbms_logstdby package.

Triggers and constraints in the logical Oracle instance do not behave the same way as they do in the primary database. The triggers on tables are never executed in the Oracle instance because they have already been executed in the primary database and the data to be applied on the standby site is the combined result of the direct DML and the data modified by the trigger.

Similarly, constraints are evaluated on the primary database and the data is posted only if it satisfies the constraints. To reduce the amount of work performed by the log apply service, the constraints are not re-evaluated on the logical standby site. As a result, if one of the tables from a referential integrity chain is skipped, the result may be inconsistency in the data on the logical standby database. The SQL Apply operation will not complain about the inconsistency, but the reports using these tables will not be correct.



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.