Performing DDL on a Logical Standby Database

This section describes how to add an index to a table maintained through SQL apply operations.

By default, only accounts with SYS privileges can modify the database while the database guard is set to ALL or STANDBY. If you are logged in as SYSTEM or another privileged account, you will not be able to issue DDL statements on the logical standby database without first bypassing the database guard for the session.

The following example shows how to stop log apply services, bypass the database guard, execute SQL statements on the logical standby database, and then reenable the guard:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.

SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;
PL/SQL procedure successfully completed.

SQL> ALTER TABLE SCOTT.EMP ADD CONSTRAINT EMPID UNIQUE (EMPNO);
Table altered.

SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
PL/SQL procedure successfully completed.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
Database altered.

This sample procedure could be used to execute other DDL statements. Oracle Corporation recommends that you do not perform. DML operations while the database guard bypass is enabled. This will introduce deviations between the primary and standby databases that will make it impossible for the logical standby database to be maintained. It is unlikely that you will be able to modify rows in a table in such a way that the logical standby database can incrementally maintain the rows.

9.1.3.2 Modifying Tables That Are Not Maintained by SQL Apply

Sometimes, a reporting application must collect summary results and store them temporarily or track the number of times a report was run. Although the main purpose of an application is to perform. reporting activities, the application might need to issue DML (insert, update, and delete) operations on a logical standby database. It might even need to create or drop tables.

You can set up the database guard to allow reporting operations to modify data as long as the data is not being maintained through SQL apply operations. To do this, you must:

Specify the set of tables on the logical standby database to which an application can write data by executing the DBMS_LOGSTDBY.SKIP procedure. Skipped tables are not maintained through SQL apply operations.
Set the database guard to protect only standby tables. This setting describes the list of tables that the logical standby database is maintaining. The list cannot include the tables to which your application will be writing.

In the following example, it is assumed that the tables to which the report is writing are also on the primary database.

The example stops SQL apply operations, skips the tables, and then restarts SQL apply operations so that changes can be applied to the logical standby database. The reporting application will be able to write to MYTABLES% in MYSCHEMA. They will no longer be maintained through SQL apply operations.

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.

SQL> EXECUTE DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','MYSCHEMA','MYTABLES%');
PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','MYSCHEMA','MYTABLES%');
PL/SQL procedure successfully completed.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
Database altered.

The example then queries the DBA_LOGSTDBY_PARAMETERS view to verify the logical standby database is updated. Verification can take a while so you might need to repeat the query until no rows are returned, as shown in the following example:

SQL> SELECT NAME FROM DBA_LOGSTDBY_PARAMETERS WHERE NAME = 'EVALUATE_SKIP';
no rows selected

Finally, the example sets the database guard to allow updates to the tables.

SQL> ALTER DATABASE GUARD STANDBY;
Database altered.

9.1.4 Handling Triggers and Constraints on a Logical Standby Database

Triggers and constraints are enabled on the standby database but they are not executed. For triggers and constraints on tables maintained through SQL apply operations, constraints are evaluated on the primary database and do not need to be re-evaluated on the logical standby database. The effects of the triggers executed on the primary database are logged and applied on the standby database. Triggers will be fired and constraints will be evaluated on tables not maintained through SQL apply operations.

9.1.5 Skipping SQL Apply Operations on a Logical Standby Database

If only a subset of activity on a primary database is of interest on the standby database, use the DBMS_LOGSTDBY.SKIP procedure to define filters that prevent log apply services from issuing the SQL statements on the logical standby database. (See for information about SQL statements that are skipped automatically.)

Tables continue applying SQL statements after filtering out unsupported datatypes or statements automatically. However, you must use the DBMS_LOGSTDBY.SKIP procedure to skip tables that you do not want to apply to the logical standby database. The following list shows typical examples of the types of SQL statements that can be filtered or skipped so that they are not applied on the logical standby database:

DML or DDL changes for tables
CREATE, ALTER, or DROP INDEX DDL statements
CREATE, ALTER, DROP, or TRUNCATE TABLE statements
CREATE, ALTER, or DROP TABLESPACE statements
CREATE or DROP VIEW statements

demonstrates how to skip all SQL apply operations that reference the EMP table in a logical standby database.

Example 9-1 Skipping a Table in a Logical Standby Database

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('SCHEMA_DDL', 'SCOTT', 'EMP', NULL);
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML', 'SCOTT', 'EMP', NULL);
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

In addition to skipping DML and DDL statements for schema and non-schema operations, you can also skip specific DML and DDL operations as well. shows how to skip ALTER TABLESPACE and CREATE TABLESPACE for non-schema DDL operations.

Example 9-2 Skipping ALTER or CREATE TABLESPACE Statements

SQL> EXEC DBMS_LOGSTDBY.SKIP(`CREATE TABLESPACE', NULL, NULL, NULL);
SQL> EXEC DBMS_LOGSTDBY.SKIP(`ALTER TABLESPACE', NULL, NULL, NULL);

SQL> COLUMN ERROR FORMAT a5;
SQL> COLUMN STATEMENT_OPT FORMAT a20;
SQL> COLUMN OWNER FORMAT a10
SQL> COLUMN NAME FORMAT a15;
SQL> COLUMN PROC FORMAT a20;
SQL> SELECT * FROM DA_LOGSTDBY_SKIP;

ERROR STATEMENT_OPT     OWNER      NAME            PROC
----- ----------------- ---------- --------------- --------------------
N     CREATE TABLESPACE
N     ALTER TABLESPACE

Use the SKIP procedure with caution, particularly when skipping DDL statements. If a CREATE TABLE statement is skipped, for example, you must also skip any other DDL statements that refer to that table. Otherwise, these statements will fail and cause an exception. When this happens, the SQL apply services stop running and will need to be manually restarted.

oracle9204(9i)_logical standby_ddl_related