DDL for Data Guard Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

 Managing Tables in Logical Standby Databases

This section will detail the requirements and processes for altering a table in a logical standby database. In particular, information will be provided on performing DDL on a table that is maintained by the SQL Apply operation and the re-building or adding of a table to a logical Oracle instance from the primary database. Also described in this section is the SKIP and UNSKIP procedure of the dbms_logstdby package that can be used to add and remove tables from the set of objects maintained by the SQL Apply operation.

Oracle Data Guard Executing DDL Statements on Tables

Performing DDL statements on tables maintained by the SQL Apply operation is a four step process. The Database Guard will have to be bypassed before any statement can be issued against the tables. The DBA should be logged in as a user having privilege to alter objects when the guard is set to ALL or STANDBY. The following example shows the step-by-step process used to add a column to the existing table ADDRESS in the REPORTS schema:

• Stop the log apply service using:

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

• Bypass the Database Guard using the dbms_logstdby Package:

EXEC DBMS_LOGSTDBY.GUARD_BYPASS_ON;

• Issue the DDL statement. For example:

ALTER TABLE ADDRESS ADD
AREA_CODE VARCHAR2(10);

• Enable the Database Guard using:

EXEC DBMS_LOGSTDBY.GUARD_BYPASS_OFF;

• Start the log apply service using:

ALTER DATABASE START LOGICAL STANDBY APPLY;