Additional DBMS_LOGSTDBY Procedures

 < Day Day Up > 

So far, we have seen how to use DBMS_LOGSTDBY to instantiate a table and to set and unset SQL Apply initialization parameters. But this package contains quite a bit more than just those two procedures. Let's look at some of the more popular procedures that you are likely to use in your day-to-day management of a logical standby.


By default, all SQL statements and objects not owned by sys are maintained on the logical standby. In some cases, you might want to skip specific objects or schemas. For instance, if you have a development schema that developers use to develop new applications, you might not want those changes applied to the logical standby. Or, you might have a table that is used as a scratch pad, which doesn't need to be maintained on the logical standby. Skipping objects that are of no interest has the side effect of increasing performance.

To skip a schema or object or type of statement, we use the DBMS_LOGSTDBY.SKIP procedure. That procedure is defined as

DBMS_LOGSTDBY.SKIP ( statement_option          IN VARCHAR2, schema_name               IN VARCHAR2, object_name               IN VARCHAR2, proc_name                 IN VARCHAR2);

A full list of valid values for the STATEMENT_OPTION parameter can be obtained in PL/SQL Packages and Types Reference 10g Release 1 manual.

The following example shows how to skip a specific table for a certain schema:

  1. Stop logical apply.

    alter database stop logical standby apply;
  2. Define which table to skip using the SKIP procedure:

    exec dbms_logstdby.skip('SCHEMA_DDL','MTSMITH','TEST1',null); exec dbms_logstdby.skip('DML','MTSMITH','TEST1',null);
  3. Restart logical apply.

That covers how to skip a table, but what about a whole schema? We can use wildcards to indicate that the filter applies to all objects in a certain schema. For example, to skip all tables in the MTSMITH schema:

exec dbms_logstdby.skip('SCHEMA_DDL','MTSMITH','%',null); exec dbms_logstdby.skip('DML','MTSMITH','%',null);

To undo the skips that you have defined, you simply use the DBMS_LOGSTDBY .UNSKIP procedure. This procedure takes the same parameters with the exception of the PROC_NAME, which doesn't exist.


Remember that the SQL Apply engine will stop whenever it receives an error while applying any SQL statements. In some rare cases, there may be a class of errors that you are willing to ignore. You can instruct the SQL Apply engine to ignore a class of errors and continue apply by using the DBMS_LOGSTDBY.SKIP_ERROR procedure. The procedure is defined as

DBMS_LOGSTDBY.SKIP_ERROR ( statement_option          IN VARCHAR2, schema_name               IN VARCHAR2, object_name               IN VARCHAR2, proc_name                 IN VARCHAR2);

To have the SQL Apply engine skip any nonschema DDL against a particular table, define the skip as follows:

  1. Stop logical standby apply.

    alter database stop logical standby apply;
  2. Define what type of errors should be skipped using the SKIP_ERROR procedure.

    exec dbms_logstdby.skip_error('NON_SCHEMA_DDL','MTSMITH','%',null);

  3. Restart logical apply.

Once again, to unskip any skip errors that you have defined, simply use the DBMS_LOGSTDBY.UNSKIP_ERROR procedure with the same parameters minus PROC_NAME.


New to Oracle Database 10g is the ability to start the SQL Apply engine and have it skip the last failed transaction, provided that we have issued a compensating transaction. In previous releases, users had to skip a specific failed statement using the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure. This procedure will still be of some use in Oracle Database 10g, and we would be remiss for not mentioning it. The following example shows how to skip a specific statement that caused SQL Apply to stop. The procedure is defined as


The steps to skip a transaction are

  1. Obtain the XIDUSN, XIDSLT, AND XIDSQN values for the failing transaction by using the DBA_LOGSTDBY_EVENTS view:


  2. Temporarily bypass the database guard so you can make a compensating transaction to the logical standby database.

    alter session disable guard;
  3. Execute the compensating transaction.

  4. Enable the database guard.

    alter session enable guard;
  5. Skip the failed DDL statement by using the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure.

    exec dbms_logstdby.skip_transaction(4,17,233);
  6. Restart the SQL Apply engine.

 < Day Day Up > 

Oracle Database 10g. High Availablity with RAC Flashback & Data Guard
Oracle Database 10g. High Availablity with RAC Flashback & Data Guard
ISBN: 71752080
Year: 2003
Pages: 134 © 2008-2017.
If you may any questions please contact us: