Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

56 DBMS_LOGSTDBY

The DBMS_LOGSTDBY package provides subprograms for configuring and managing the logical standby database environment.

See Also:

Oracle Data Guard Concepts and Administration for more information about SQL Apply and logical standby databases

This chapter contains the following topics:


Using DBMS_LOGSTDBY

This section contains topics which relate to using the DBMS_LOGSTDBY package.


Overview

The DBMS_LOGSTDBY package helps you manage the SQL Apply (logical standby database) environment. The subprograms in the DBMS_LOGSTDBY package help you to accomplish the following main objectives:


Operational Notes

Case Sensitivity

Ensure you use the correct case when supplying schema and table names to the DBMS_LOGSTDBY package. For example, the following statements show incorrect and correct syntax for a SKIP procedure that skips changes to OE.TEST.

Incorrect statement:

EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'oe', -   object_name => 'test', proc_name => null);

Because the names are specified with lowercase characters, the transactions that update these columns will still be applied to the logical standby database.

Correct statement:

EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'OE', -   object_name => 'TEST', proc_name => null);

Privileges and Security

A prototype role, LOGSTDBY_ADMINISTRATOR, is created by default with RESOURCE, and EXECUTE on DBMS_LOGSTDBY privileges. If you choose to use this role, consider granting ALTER DATABASE and ALTER SESSION privileges to the role so that the grantee can start and stop SQL Apply and can enable and disable the database guard. Oracle recommends using an account with DBA privileges to perform administration tasks on logical standby databases.

The six procedures associated with skipping transactions (SKIP and UNSKIP, SKIP_ERROR and UNSKIP_ERROR, and SKIP_TRANSACTION and UNSKIP_TRANSACTION) all require DBA privileges to execute because their scope may contain wildcard schemas. Oracle recommends that where SKIP procedures are specified, these be owned by a secure account with appropriate privileges on the schemas they act on (for example, SYS).


Deprecated Subprograms

The transaction_consistency parameter of the APPLY_SET Procedure is being deprecated with this release of the Oracle Database. The transaction_consistency parameter is being replaced by the preserve_commit_order parameter.


Summary of DBMS_LOGSTDBY Subprograms

Table 56-1 DBMS_LOGSTDBY Package Subprograms

Subprogram Description
APPLY_SET Procedure
Sets the values of various parameters that configure and maintain SQL Apply
APPLY_UNSET Procedure
Restores the default values of various parameters that configure and maintain SQL Apply
BUILD Procedure
Ensures supplemental logging is enabled properly and builds the LogMiner dictionary
INSTANTIATE_TABLE Procedure
Creates and populates a table in the standby database from a corresponding table in the primary database
PREPARE_FOR_NEW_PRIMARY Procedure
Used after a failover, this procedure ensures a local logical standby database that was not involved in the failover has not processed more redo than the new primary database and reports the set of archive redo log files that must be replaced to ensure consistency
PURGE_SESSION Procedure
Identifies the archived redo log files that have been applied to the logical standby database and are no longer needed by SQL Apply
REBUILD Procedure Records relevant metadata (including the LogMiner Multiversioned Data Dictionary) in the redo stream in case a database that has recently changed its role to a primary database following a failover operation fails to do so during the failover process
SET_TABLESPACE Procedure
Moves metadata tables required by SQL Apply to the user-specified tablespace. By default, the metadata tables are created in the SYSAUX tablespace.
SKIP Procedure
Specifies rules that control database operations that should not be applied to the logical standby database
SKIP_ERROR Procedure
Specifies rules regarding what action to take upon encountering errors
SKIP_TRANSACTION Procedure
Specifies transactions that should not be applied on the logical standby database. Be careful in using this procedure, because not applying specific transactions may cause data corruption at the logical standby database.
UNSKIP Procedure
Deletes rules specified by the SKIP procedure
UNSKIP_ERROR Procedure
Deletes rules specified by the SKIP_ERROR procedure
UNSKIP_TRANSACTION Procedure
Deletes rules specified by the SKIP_TRANSACTION procedure


APPLY_SET Procedure

Use this procedure to set values of parameters that configure and manage SQL Apply in a logical standby database environment. SQL Apply cannot be running when you use this procedure.

Syntax

DBMS_LOGSTDBY.APPLY_SET (
     parameter          IN VARCHAR,
     value              IN VARCHAR);

Parameters

Table 56-2 APPLY_SET Procedure Parameters

Parameter Description
LOG_AUTO_DELETE Automatically deletes archived redo log files once they have been applied on the logical standby database. Set to TRUE to enable automatic deletion of archived redo log files, and FALSE to disable automatic deletion.The default value is TRUE.
MAX_SGA Number of megabytes from shared pool in System Global Area (SGA) that SQL Apply will use. The default value is 30 megabytes or one quarter of the value set for SHARED_POOL_SIZE, whichever is lower.
MAX_SERVERS Number of parallel query servers that SQL Apply uses to read and apply redo. It defaults to the value of the PARALLEL_MAX_SERVERS initialization parameter or 9, whichever is lower.
MAX_EVENTS_RECORDED Number of recent events that will be visible through the DBA_LOGSTDBY_EVENTS view. To record all events encountered by SQL Apply, use the DBMS_LOGSTDBY.MAX_EVENTS constant as the number value.
PRESERVE_COMMIT_ORDER TRUE: Transaction are applied to the logical standby database in the exact order in which they were committed on the primary database. This is the default parameter setting.

FALSE: Transactions are applied out of order from how they were committed on the primary database, and no attempt is made to provide read-consistent results.

Regardless of the level chosen, modifications done to the same row are always applied in the same order as they happened in the primary database. See the Usage Notes for details and recommendations.

RECORD_SKIP_ERRORS Controls whether skipped errors (as described by the SKIP_ERROR procedure) are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log. Specify one of the following values:

TRUE: Skipped errors are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log. This is the default parameter setting.

FALSE: Skipped errors are not recorded in the DBA_LOGSTDBY_EVENTS table and the alert log.

RECORD_SKIP_DDL Controls whether skipped DDL statements are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log. Specify one of the following values:

TRUE: Skipped DDL statements are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log. This is the default parameter setting.

FALSE: Skipped DDL statements are not recorded in the DBA_LOGSTDBY_EVENTS table and the alert log.

RECORD_APPLIED_DDL Controls whether DDL statements that have been applied to the logical standby database are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log. Specify one of the following values:

TRUE: Indicates that DDL statements applied to the logical standby database are recorded in the DBA_LOGSTDBY_EVENTS table and the alert log.

FALSE: Indicates that applied DDL statements are not recorded. This is the default parameter setting.

APPLY_SERVERS Controls the number of APPLIER processes (parallel execution servers) used to apply changes
PREPARE_SERVERS Controls the number of PREPARER processes (parallel execution servers) used to prepare changes

Exceptions

Table 56-3 APPLY_SET Procedure Exceptions

Exception Description
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16104 invalid Logical Standby option requested
ORA-16236 Logical Standby metadata operation in progress

Usage Notes

Examples

To record DDLs in the DBA_LOGSTDBY_EVENTS view and in the alert log, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('RECORD_APPLIED_DDL', TRUE);

APPLY_UNSET Procedure

Use the APPLY_UNSET procedure to restore the default values of the parameters that you changed with the APPLY_SET procedure.

Syntax

DBMS_LOGSTDBY.APPLY_UNSET (
     parameter          IN VARCHAR);

Parameters

The parameter information for the APPLY_UNSET procedure is the same as that described for the APPLY_SET procedure. See Table 56-2 for complete parameter information.

Exceptions

Table 56-4 APPLY_UNSET Procedure Exceptions

Exception Description
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16104 invalid Logical Standby option requested
ORA-16236 Logical Standby metadata operation in progress

Usage Notes

Examples

If you previously specified that applied DDLs show up in the DBA_LOGSTDBY_EVENTS view and the alert log, you can restore the default behavior of SQL Apply regarding applied DDL statements with the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('RECORD_APPLIED_DDL');


BUILD Procedure

Use this procedure on the primary database to record relevant metadata (LogMiner dictionary) information in the redo log, which will subsequently be used by SQL Apply. This procedure will enable database-wide primary- and unique-key supplemental logging, if necessary.

Syntax

DBMS_LOGSTDBY.BUILD;

Usage Notes

Examples

To build the LogMiner dictionary in the redo stream of the primary database and to record additional information so that a logical standby database can be instantiated, issue the following SQL statement at the primary database

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

INSTANTIATE_TABLE Procedure

This procedure creates and populates a table in the standby database from a corresponding table in the primary database. The table requires the name of the database link (dblink) as an input parameter. If the table already exists in the logical standby database, it will be dropped and re-created based on the table definition at the primary database. This procedure only brings over the data associated with the table, and not the associated indexes and constraints.

Use the INSTANTIATE_TABLE procedure to:

Syntax

DBMS_LOGSTDBY.INSTANTIATE_TABLE (
     schema_name         IN VARCHAR2,
     table_name          IN VARCHAR2,
     dblink              IN VARCHAR2);

Parameters

Table 56-5 INSTANTIATE_TABLE Procedure Parameters

Parameter Description
schema_name Name of the schema
table_name Name of the table to be created or re-created in the standby database
dblink Name of the database link account that has privileges to read and lock the table in the primary database

Exceptions

Table 56-6 INSTANTIATE_TABLE Procedure Exceptions

Exception Description
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16236 Logical Standby metadata operation in progress

Usage Notes

Examples

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE (-
     SCHEMA_NAME => 'HR', TABLE_NAME => 'EMPLOYEES', -
     DBLINK => 'INSTANTIATE_TBL_LINK');

PREPARE_FOR_NEW_PRIMARY Procedure

The PREPARE_FOR_NEW_PRIMARY procedure must be invoked at a logical standby database following a failover if that standby database was not the target of the failover operation. Such a standby database must process the exact same set of redo logs processed at the new primary database. This routine ensures that the local logical standby database has not processed more redo than the new primary database and reports the set of archive logs that must be replaced to ensure consistency. The set of replacement logs will be reported in the alert.log. These logs must be copied to the logical standby and registered using the ALTER DATABASE REGISTER LOGICAL LOGFILE statement.

Syntax

DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY (
           FORMER_STANDBY_TYPE         IN VARCHAR2,
           DBLINK                      IN VARCHAR2);

Parameters

Table 56-7 PREPARE_FOR_NEW_PRIMARY Procedure Parameters

Parameter Description
FORMER_STANDBY_TYPE The type of standby database that was the target of the failover operation to become the new primary database. Valid values are 'PHYSICAL' if the new primary was formerly a physical standby, and 'LOGICAL' if the new primary database was formerly a logical standby database.
DBLINK The name of a database link to the new primary database

Exceptions

Table 56-8 PREPARE_FOR_NEW_PRIMARY Procedure Exceptions

Exception Description
ORA-16104 Invalid Logical Standby option.
ORA-16109 Failed to apply log data from previous primary.

Usage Notes

Examples

SQL> EXECUTE DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY (  -
                FORMER_STANDBY_TYPE => 'LOGICAL',    -
                DBLINK => 'dblink_to_newprimary'); 

PURGE_SESSION Procedure

Identifies all archived redo log files that have been applied to the logical standby database and are no longer needed by SQL Apply. Once identified, you can issue operating system commands to delete some or all of the unnecessary archived redo log files.

Syntax

DBMS_LOGSTDBY.PURGE_SESSION;

Exceptions

Table 56-9 PURGE_SESSION Procedure Exceptions

Exception Description
ORA-01309 Invalid session

Usage Notes

Example

To identify and remove unnecessary files:

  1. Enter the following statement on the logical standby database:

    SQL> EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
    
    
  1. Query the DBA_LOGMNR_PURGED_LOG view to list the archived redo log files that can be removed:

    SQL> SELECT * FROM DBA_LOGMNR_PURGED_LOG;
    
    FILE_NAME
       ------------------------------------
       /boston/arc_dest/arc_1_40_509538672.log
       /boston/arc_dest/arc_1_41_509538672.log
       /boston/arc_dest/arc_1_42_509538672.log
       /boston/arc_dest/arc_1_43_509538672.log
       /boston/arc_dest/arc_1_44_509538672.log
       /boston/arc_dest/arc_1_45_509538672.log
       /boston/arc_dest/arc_1_46_509538672.log
       /boston/arc_dest/arc_1_47_509538672.log
    
    
  1. Use operating system-specific commands to delete archived redo log files from the file system.


REBUILD Procedure

This procedure is used if a database that has recently changed its role to a primary database following a failover operation fails to record relevant metadata (including the LogMiner Multiversioned Data Dictionary) in the redo stream required for other logical standby databases.

Syntax

DBMS_LOGSTDBY.REBUILD;

Usage Notes

Examples

SQL> EXECUTE DBMS_LOGSTDBY.REBUILD;

SET_TABLESPACE Procedure

Moves metadata tables required by SQL Apply to the user-specified tablespace. By default, the metadata tables are created in the SYSAUX tablespace.

Syntax

DBMS_LOGSTDBY.SET_TABLESPACE(
           NEW_TABLESPACE IN VARCHAR2)

Parameters

Table 56-10 SET_TABLE SPACE Procedure Parameters

Parameter Description
NEW_TABLESPACE Name of the new tablespace where metadata tables will reside.

Exceptions

Table 56-11 SET_TABLESPACE Procedure Exceptions

Exception Description
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16236 Logical Standby metadata operation in progress

Examples

To move metadata tables to a new tablespace named LOGSTDBY_TBS, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.SET_TABLESPACE (new_tablespace => 'LOGSTDBY_TBS');

SKIP Procedure

The SKIP procedure can be used to define rules that will be used by SQL Apply to skip the application of certain changes to the logical standby database. For example, the SKIP procedure can be used to skip changes to a subset of tables in the logical standby database. It can also be used to specify DDL statements that should not be applied at the logical standby database or should be modified before they are applied in the logical standby database. One reason why a DDL statement may need to be modified is to accommodate a different directory structure on the logical standby database.

Syntax

DBMS_LOGSTDBY.SKIP (
     stmt                      IN VARCHAR2,
     schema_name               IN VARCHAR2 DEFAULT NULL,
     object_name               IN VARCHAR2 DEFAULT NULL,
     proc_name                 IN VARCHAR2 DEFAULT NULL,
     use_like                  IN BOOLEAN DEFAULT TRUE,
     esc                       IN CHAR1 DEFAULT NULL);

Parameters

Table 56-12 SKIP Procedure Parameters

Parameter Description
stmt Either a keyword that identifies a set of SQL statements or a specific SQL statement. The use of keywords simplifies configuration since keywords, generally defined by the database object, identify all SQL statements that operate on the specified object. Table 56-13 shows a list of keywords and the equivalent SQL statements, either of which is a valid value for this parameter.
schema_name The name of one or more schemas (wildcards are permitted) associated with the SQL statements identified by the stmt parameter. If not applicable, this value must be set to NULL.
object_name The name of one or more objects (wildcards are permitted) associated with the SQL statements identified by the stmt. If not applicable, this value must be set to NULL.
proc_name Name of a stored procedure to call when SQL Apply determines that a particular statement matches the filter defined by the stmt, schema_name, and object_name parameters. Specify the procedure in the following format:

'"schema"."package"."procedure"'

This procedure returns a value that directs SQL Apply to perform one of the following: execute the statement, skip the statement, or execute a replacement statement.

SQL Apply calls the stored procedure with the following call signature:

  • IN STATEMENT VARCHAR2 -- The SQL statement that matches the filter

  • IN STATEMENT_TYPE VARCHAR2 -- The stmt of the filter

  • IN SCHEMA VARCHAR2 -- The schema_name of the filter, if applicable

  • IN NAME VARCHAR2 -- The object_name of the filter, if applicable

  • IN XIDUSN NUMBER -- Transaction ID part 1

  • IN XIDSLT NUMBER -- Transaction ID part 2

  • IN XIDSQN NUMBER -- Transaction ID part 3

  • OUT SKIP_ACTION NUMBER -- Action to be taken by SQL Apply upon completion of this routine. Valid values are:

    SKIP_ACTION_APPLY -- Execute the statement

    SKIP_ACTION_SKIP -- Skip the statement

    SKIP_ACTION_REPLACE -- Execute the replacement statement supplied in the NEW_STATEMENT output parameter

use_like Allows pattern matching to isolate the tables that you want to skip on the logical standby database. The use_like parameter matches a portion of one character value to another by searching the first value for the pattern specified by the second, and calculates strings using characters as defined by the input character set. This parameter follows the same rules for pattern matching described in the Oracle Database SQL Reference.
esc Identifies an escape character (such as the character "/") that you can use for pattern matching. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character. SeeOracle Database SQL Reference for more information about pattern matching.

Usage Notes

Skip Statement Options

Table 56-13 lists the supported values for the stmt parameter of the SKIP procedure. The left column of the table lists the keywords that may be used to identify the set of SQL statements to the right of the keyword. Any of the SQL statements in the right column, however, are also valid values. Note that keywords are generally defined by database object.

Table 56-13 Supported Values for the stmt Parameter

Keyword Associated SQL Statements
NON_SCHEMA_DDL All DDL that does not pertain to a particular schema

Note: SCHEMA_NAME and OBJECT_NAME must be null

SCHEMA_DDL All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns)

Note: SCHEMA_NAME and OBJECT_NAME must not be null

DML Includes DML statements on a table (for example: INSERT, UPDATE, and DELETE)
CLUSTER
AUDIT CLUSTER
CREATE CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER
CONTEXT
CREATE CONTEXT
DROP CONTEXT
DATABASE LINK
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP DATABASE LINK
DROP PUBLIC DATABASE LINK
DIMENSION
ALTER DIMENSION
CREATE DIMENSION
DROP DIMENSION
DIRECTORY
CREATE DIRECTORY
DROP DIRECTORY
INDEX
ALTER INDEX
CREATE INDEX
DROP INDEX
PROCEDUREFoot 1 
ALTER FUNCTION
ALTER PACKAGE
ALTER PACKAGE BODY
ALTER PROCEDURE
CREATE FUNCTION
CREATE LIBRARY
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PROCEDURE
DROP FUNCTION
DROP LIBRARY
DROP PACKAGE
DROP PACKAGE BODY
DROP PROCEDURE
PROFILE
ALTER PROFILE
CREATE PROFILE
DROP PROFILE
ROLE
ALTER ROLE
CREATE ROLE
DROP ROLE
SET ROLE
ROLLBACK STATEMENT
ALTER ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
SEQUENCE
ALTER SEQUENCE
CREATE SEQUENCE
DROP SEQUENCE
SYNONYM
CREATE PUBLIC SYNONYM
CREATE SYNONYM
DROP PUBLIC SYNONYM
DROP SYNONYM
TABLE
ALTER TABLE
CREATE TABLE
DROP TABLE
TABLESPACE
CREATE TABLESPACE
DROP TABLESPACE
TRUNCATE TABLESPACE
TRIGGER
ALTER TRIGGER
CREATE TRIGGER
DISABLE ALL TRIGGERS
DISABLE TRIGGER
DROP TRIGGER
ENABLE ALL TRIGGERS
ENABLE TRIGGER
TYPE
ALTER TYPE
ALTER TYPE BODY
CREATE TYPE
CREATE TYPE BODY
DROP TYPE
DROP TYPE BODY
USER
ALTER USER
CREATE USER
DROP USER
VIEW
CREATE VIEW
DROP VIEW

Footnote 1 Java schema objects (sources, classes, and resources) are considered the same as procedure for purposes of skipping (ignoring) SQL statements.

Exceptions

Table 56-14 DBMS_LOGSTDBY.SKIP Procedure Exceptions

Exception Description
ORA-01031 Insufficient privileges:
  • Procedure used INVOKER rights

  • Procedure needs DBA privileges

ORA-16103 Logical standby apply must be stopped to allow this operation.
ORA-16104 Invalid logical standby option requested.
ORA-16203 "Unable to interpret SKIP procedure return values."

Indicates that a SKIP procedure has either generated an exception or has returned ambiguous values. You can identify the offending procedure by examining the DBA_LOGSTDBY_EVENTS view.

ORA-16236 Logical standby metadata operation in progress.

Examples


Example 1 Skipping all DML and DDL changes made to a schema

The following example shows how to specify rules so that SQL Apply will skip both DDL and DML statements made to the HR schema.

SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'SCHEMA DDL', -
     schema_name => 'HR', -
     table_name => '%', -
     proc_name => null);
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DML', -
     schema_name => 'HR', -
     table_name => '%', -
     proc_name => null);

Example 2 Creating a procedure to handle different file system organization

For example, if the file system organization in the logical standby database is different than that in the primary database, you can write a SKIP procedure to handle DDL statements with file specifications transparently.The following procedure can handle DDL statements as long as you follow a specific naming convention for the file specification string.

  1. Create the SKIP procedure to handle tablespace DDL statements:

    CREATE OR REPLACE PROCEDURE sys.handle_tbs_ddl (
    
      old_stmt  IN  VARCHAR2,
      stmt_typ  IN  VARCHAR2,
      schema    IN  VARCHAR2,
      name      IN  VARCHAR2,
      xidusn    IN  NUMBER,
      xidslt    IN  NUMBER,
      xidsqn    IN  NUMBER,
      action    OUT NUMBER,
      new_stmt  OUT VARCHAR2
    ) AS
    BEGIN
     
    -- All primary file specification that contains a directory
    -- /usr/orcl/primary/dbs
    -- should go to /usr/orcl/stdby directory specification
     
     
      new_stmt = replace(old_stmt,
                         '/usr/orcl/primary/dbs',
                         '/usr/orcl/stdby');
     
      action := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
     
    EXCEPTION
      WHEN OTHERS THEN
        action := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
        new_stmt := NULL;
    END handle_tbs_ddl;
    
    
  2. Register the SKIP procedure with SQL Apply:

    SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', -
                 proc_name => 'SYS.HANDLE_TBS_DDL');
    

SKIP_ERROR Procedure

Upon encountering an error, the logical standby database uses the criteria contained in this procedure to determine if the error should cause SQL Apply to stop. All errors to be skipped are stored in system tables that describe how exceptions should be handled.

Syntax

DBMS_LOGSTDBY.SKIP_ERROR (
     stmt                      IN VARCHAR2,
     schema_name               IN VARCHAR2,
     object_name               IN VARCHAR2,
     proc_name                 IN VARCHAR2,
     use_like                  IN BOOLEAN,
     esc                       IN CHAR1);

Parameters

Table 56-15 SKIP_ERROR Procedure Parameters

Parameter Description
stmt Either a keyword that identifies a set of SQL statements or a specific SQL statement. The use of keywords simplifies configuration because keywords, generally defined by the database object, identify all SQL statements that operate on the specified object. Table 56-13 shows a list of keywords and the equivalent SQL statements, either of which is a valid value for this parameter.
schema_name The name of one or more schemas (wildcards are permitted) associated with the SQL statements identified by the stmt parameter. If not applicable, this value must be set to NULL.
object_name The name of one or more objects (wildcards are permitted) associated with the SQL statements identified by the stmt. If not applicable, this value must be set to NULL.
proc_name Name of a stored procedure to call when SQL Apply determines a particular statement matches the filter defined by the stmt, schema_name, and object_name parameters. Specify the procedure in the following format:

'"schema"."package"."procedure"'

This procedure returns a value that directs SQL Apply to perform one of the following: execute the statement, skip the statement, or execute a replacement statement.

SQL Apply calls the stored procedure with the following call signature:

  • IN STATEMENT VARCHAR(4000) -- The first 4K of the statement

  • IN STATEMENT_TYPE VARCHAR2 -- The stmt of the filter

  • IN SCHEMA VARCHAR2 -- The schema_name of the filter, if applicable

  • IN NAME VARCHAR2 -- The object_name of the filter, if applicable

  • IN XIDUSN NUMBER -- Transaction ID part 1

  • IN XIDSLT NUMBER -- Transaction ID part 2

  • IN XIDSQN NUMBER -- Transaction ID part 3

  • IN ERROR VARCHAR(4000) -- Text of error to be recorded (optional)

  • OUT NEW_ERROR VARCHAR(4000) -- Null or modified error text

use_like Allows pattern matching to isolate the tables that you want to skip on the logical standby database. The use_like parameter matches a portion of one character value to another by searching the first value for the pattern specified by the second, and calculates strings using characters as defined by the input character set. This parameter follows the same rules for pattern matching described in the Oracle Database SQL Reference.
esc Identifies an escape character (such as the characters "%" or "_") that you can use for pattern matching. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character. SeeOracle Database SQL Reference for more information about pattern matching.

Usage Notes

Exceptions

Table 56-16 SKIP_ERROR Procedure Exceptions

Exception Description
ORA-01031 Insufficient privileges:
  • Procedure used INVOKER rights

  • Procedure needs DBA privileges

ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16104 invalid Logical Standby option requested
ORA-16236 Logical Standby metadata operation in progress

Examples

To skip errors on GRANT statements on SYS or HR schemas, define a procedure handle_error_ddl and register it. In the following example, assume that handle_error_ddl is a free-standing procedure in the SYS schema.

  1. Create the error-handler procedure:

    CREATE OR REPLACE PROCEDURE sys.handle_error_ddl (
      old_stmt    IN  VARCHAR2,
      stmt_type   IN  VARCHAR2,
      schema      IN  VARCHAR2,
      name        IN  VARCHAR2,
      xidusn      IN  NUMBER,
      xidslt      IN  NUMBER,
      xidsqn      IN  NUMBER,
      error       IN  VARCHAR2,
      new_stmt    OUT VARCHAR2
    ) AS
    
    BEGIN
      -- Default to what we already have
      new_stmt := old_stmt;
    
      -- Ignore any GRANT errors on SYS or HR schemas
      IF INSTR(UPPER(old_stmt),'GRANT') > 0
      THEN
        IF schema IS NULL
        OR (schema IS NOT NULL AND
              (UPPER(schema) = 'SYS'  OR UPPER(schema) = 'HR' )
        THEN
          new_stmt := NULL;
          -- record the fact that we just skipped an error on 'SYS' or 'HR' schemas
          -- code not shown here
        END IF;
      END IF;
    
    END handle_error_ddl;
    /
    
    
  2. Register the error handler with SQL Apply:

    SQL> EXECUTE DBMS_LOGSTDBY.SKIP_ERROR ( -
         statement => 'NON_SCHEMA_DDL', -
         schema_name => NULL, -
         object_name => NULL, -
         proc_name => 'SYS.HANDLE_ERROR_DDL');
    

SKIP_TRANSACTION Procedure

This procedure provides a way to skip (ignore) applying transactions to the logical standby database. You can skip specific transactions by specifying transaction identification information.

Syntax

DBMS_LOGSTDBY.SKIP_TRANSACTION (
     XIDUSN                 IN NUMBER,
     XIDSLT NUMBER          IN NUMBER,
     XIDSQN NUMBER          IN NUMBER);

Parameters

Table 56-17 SKIP_TRANSACTION Procedure Parameters

Parameter Description
XIDUSN NUMBER Transaction ID undo segment number of the transaction being skipped
XIDSLT NUMBER Transaction ID slot number of the transaction being skipped
XIDSQN NUMBER Transaction ID sequence number of the transaction being skipped

Usage Notes

If SQL Apply stops due to a particular transaction (for example, a DDL transaction), you can specify that transaction ID and then continue to apply. You can call this procedure multiple times for as many transactions as you want SQL Apply to ignore.

CAUTION:

SKIP_TRANSACTION is an inherently dangerous operation. Do not invoke this procedure unless you have examined the transaction in question through the V$LOGMNR_CONTENTS view and have taken compensating actions at the logical standby database. SKIP_TRANSACTION is not the appropriate procedure to invoke to skip DML changes to a table.

To skip a DML failure, use a SKIP procedure, such as SKIP('DML','MySchema','MyFailed Table'). Using the SKIP_TRANSACTION procedure for DML transactions may skip changes for other tables, thus logically corrupting them.

Exceptions

Table 56-18 SKIP_TRANSACTION Procedure Exceptions

Exception Description
ORA-01031 Need DBA privileges
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16104 invalid Logical Standby option requested

Examples

To skip a DDL transaction with (XIDUSN, XIDSLT, XIDSQN) of (1.13.1726) you can register a rule as shown in the following example:

SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION (- 
     XIDUSN => 1, XIDSLT => 13, XIDSQN => 1726);

UNSKIP Procedure

Use the UNSKIP procedure to delete rules specified earlier with the SKIP procedure. The parameters specified in the UNSKIP procedure must match exactly for it to delete an already-specified rule.

Syntax

DBMS_LOGSTDBY.UNSKIP (
     stmt                      IN VARCHAR2,
     schema_name               IN VARCHAR2,
     object_name               IN VARCHAR2);

Parameters

The parameter information for the UNSKIP procedure is the same as that described for the SKIP procedure. See Table 56-12 for complete parameter information.

Exceptions

Table 56-19 UNSKIP Procedure Exceptions

Exception Description
ORA-01031 Need DBA privileges
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16104 invalid Logical Standby option requested

Usage Notes

CAUTION:

If DML changes for a table have been skipped and not compensated for, you must follow the call to the UNSKIP procedure with a call to the INSTANTIATE_TABLE procedure to synchronize this table with those maintained by SQL Apply.


UNSKIP_ERROR Procedure

Use the UNSKIP_ERROR procedure to delete rules specified earlier with the SKIP_ERROR procedure. The parameters specified in the UNSKIP_ERROR procedure must match exactly for the procedure to delete an already-specified rule.

Syntax

DBMS_LOGSTDBY.UNSKIP_ERROR (
     stmt                      IN VARCHAR2,
     schema_name               IN VARCHAR2,
     object_name               IN VARCHAR2);

Parameters

The parameter information for the UNSKIP_ERROR procedure is the same as that described for the SKIP_ERROR procedure. See Table 56-15 for complete parameter information.

Exceptions

Table 56-20 UNSKIP_ERROR Procedure Exceptions

Exception Description
ORA-01031 Need DBA privileges
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16104 invalid Logical Standby option requested

Usage Notes

Example

To remove a handler that was previously registered with SQL Apply from getting called on encountering an error, you can issue the following statement:

DBMS_LOGSTDBY.UNSKIP_ERROR ( -
      statement => 'NON_SCHEMA_DDL', -
      schema_name => NULL, -
      object_name => NULL);

UNSKIP_TRANSACTION Procedure

Use the UNSKIP_TRANSACTION procedure to delete rules specified earlier with the SKIP_TRANSACTION procedure. The parameters specified in the UNSKIP_TRANSACTION procedure must match exactly for the procedure to delete an already-specified rule.

Syntax

DBMS_LOGSTDBY.UNSKIP_TRANSACTION (
     XIDUSN           NUMBER,
     XIDSLT           NUMBER,
     XIDSQN           NUMBER);

Parameters

Table 56-21 UNSKIP_TRANSACTION Procedure Parameters

Parameter Description
XIDUSN Transaction ID undo segment number of the transaction being skipped
XIDSLT Transaction ID slot number of the transaction being skipped
XIDSQN Transaction ID sequence number of the transaction being skipped

Exceptions

Table 56-22 UNSKIP_TRANSACTION Procedure Exceptions

Exception Description
ORA-01031 Need DBA privileges
ORA-16103 Logical Standby apply must be stopped to allow this operation
ORA-16104 invalid Logical Standby option requested

Usage Notes

Examples

To remove a rule that was originally specified to skip the application of a transaction with (XIDUSN, XIDSLT, XIDSQN) of (1.13.1726) issue the following statement:

SQL> DBMS_LOGSTDBY.UNSKIP_TRANSACTION (XIDUSN => 1, XIDSLT => 13, XIDSQN => 1726);