dbms_logstdby.skip Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data Guard Skipping a Table

The SQL Apply operation can be set to skip one or more tables from the set of objects maintained by the log apply service. This will be useful if there is a need to modify a subset of objects in the logical Oracle instance independently of the primary database.

The Database Guard should be set to STANDBY for the log apply service to maintain only a subset of objects from the primary database. The following steps are required to set a table to be skipped by the log apply service. As an example, the table ITEM in the REPORTS schema will be excluded from the list of tables maintained by the log apply service:

• Stop the log apply service using:

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

• Set the Database Guard to STANDBY using:

ALTER DATABASE GUARD STANDBY;

• Use the dbms_logstdby.skip  procedure to DML on the table:

EXEC DBMS_LOGSTDBY.SKIP('DML','REPORTS','ITEM');

• Start the log apply service using:

ALTER DATABASE START LOGICAL STANDBY APPLY;