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:

ALTER DATABASE GUARD STANDBY;

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

Select
   GUARD_STATUS
From
   V$DATABASE;

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.