49
DBMS_LOGSTDBY

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

See Also:

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

This chapter contains the following topics:

Using DBMS_LOGSTBY

Overview

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

Allow controlled access to tables in the standby database that may require maintenance
Control how transactions are applied to the logical standby database
Provide a way to skip applying archived redo log files to selected tables or entire schemas in the standby database, and describe how exceptions should be handled
Manage initialization parameters used by log apply services
Ensure supplemental logging is enabled properly and build the LogMiner dictionary

Operational Notes

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 ('DML', 'oe', 'test', 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 ('DML', 'OE', 'TEST', null);

Summary of DBMS_LOGSTDBY Subprograms

Table 49-1 DBMS_LOGSTDBY Package Subprograms

Subprogram	Description
APPLY_SET Procedure	Sets the values of specific initialization parameters that configure and maintain log apply services
APPLY_UNSET Procedure	Resets the value of specific initialization parameters to the system default values
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
SKIP Procedure	Specifies which database operations that are performed on the primary database should not be applied to the logical standby database
SKIP_ERROR Procedure	Specifies criteria to follow if an error is encountered, with the result that you can choose to stop log apply services or ignore the error
SKIP_TRANSACTION Procedure	Specifies transaction identification information to skip (ignore) applying specific transactions to the logical standby database
UNSKIP Procedure	Modifies the options set in the `SKIP` procedure
UNSKIP_ERROR Procedure	Modifies the options set in the `SKIP_ERROR` procedure
UNSKIP_TRANSACTION Procedure	Modifies the options set in the `SKIP_TRANSACTION` procedure

APPLY_SET Procedure

Use this procedure to set and modify the values of initialization parameters that configure and manage log apply services 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 49-2 APPLY_SET Procedure Parameters

Parameter	Description
`MAX_SGA`	Number of megabytes for the system global area (SGA) that SQL Apply will use to cache change records. The default value is 30 megabytes or less if the `SHARED_POOL_SIZE` initialization parameter is set to a small value. In most cases 30 megabytes is sufficient. Note: In Oracle9i, the parameter default was one quarter of the value set for the `SHARED_POOL_SIZE` initialization parameter.
`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. Because the correct value for this parameter is really a function of the workload, it is best to explicitly set the parameter, using a reasonable value that will work in most cases. Note: In Oracle9i, the `MAX_SERVERS` parameter defaulted to the value of the `PARALLEL_MAX_SERVERS` initialization parameter.
`MAX_EVENTS_RECORDED`	Number of events that will be stored in the `DBA_LOGSTDBY_EVENTS` table, which stores logical standby event information.
`TRANSACTION_CONSISTENCY`	Level of transaction consistency maintained between the primary and standby databases. Specify one of the following values: `FULL`: Transactions are applied to the logical standby database in the exact order in which they were committed on the primary database. (Transactions are applied in commit SCN order.) This is the default parameter setting. `READ_ONLY`: Transactions are committed out of order (which provides better performance), but SQL `SELECT` statements executed on the standby database always return consistent results based on the last consistent SCN known to SQL apply. `NONE`: 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. Specify one of the following values: `TRUE`: Skipped errors are recorded in the `DBA_LOGSTDBY_EVENTS` table. This is the default parameter setting. `FALSE`: Skipped errors are not recorded in the `DBA_LOGSTDBY_EVENTS` table.
`RECORD_SKIP_DDL`	Controls whether skipped DDL statements are recorded in the `DBA_LOGSTDBY_EVENTS` table. Specify one of the following values: `TRUE`: Skipped DDL statements are recorded in the `DBA_LOGSTDBY_EVENTS` table. This is the default parameter setting. `FALSE`: Skipped DDL statements are not recorded in the `DBA_LOGSTDBY_EVENTS` table.
`RECORD_APPLIED_DDL`	Controls whether DDL statements that have been applied to the logical standby database are recorded in the `DBA_LOGSTDBY_EVENTS` table. 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. This is the default parameter setting. `FALSE`: Indicates that applied DDL statements are not recorded.
`APPLY_SERVERS`	Controls the number of parallel execution servers used to apply changes. See Oracle Data Guard Concepts and Administration for an explanation of the logical standby processes.
`PREPARE_SERVERS`	Controls the number of parallel execution servers used to prepare changes. See Oracle Data Guard Concepts and Administration for an explanation of the logical standby processes.

Usage Notes

Although the default values provided by the system for initialization parameters are adequate for most applications, you might want to use the APPLY_SET procedure when you need to perform tuning and maintenance tasks. For example, use the APPLY_SET procedure when you want to override default initialization parameter values to tune log apply services.
Log apply services must not be applying archived redo log data to the standby database when you modify initialization parameters with the APPLY_SET procedure. The initialization parameter values that you set using this procedure do not become active until you start log apply services.
Use the APPLY_UNSET Procedure to reverse (undo) the actions of the APPLY_SET procedure.
For the TRANSACTION_CONSISTENCY parameter:
- The FULL option (the default) is recommended when the logical standby database is used for generic reporting applications. This option results in the lowest performance.
- The READ_ONLY option is recommended when the logical standby database is used for read-only reporting.
  
  SQL Apply periodically refreshes an SCN maintained in SGA that represents a consistent state. Queries executed on the standby database, automatically use Oracle Flashback to the maintained SCN. This is beneficial when the logical standby database is being used to generate reports. Any Oracle Flashback restrictions apply to this option.
- The NONE option works well as long as applications that are reading the logical standby database make no assumptions about transaction order. The NONE option is also useful when the logical standby database is in catch-up mode.
  
  The NONE option results in the best performance of the three options for the TRANSACTION_CONSISTENCY parameter. However, this setting might give you inconsistent results on the standby database. If applications that are reading the logical standby database make no assumptions about transaction order, this option works well. For example:
  - On the primary database, one transaction added a new customer and a second transaction added a new order for that customer.
  - On the standby database, those transactions may be reversed. The order for the new customer might be added first. If you then run a reporting application on the standby database that expects to find a customer for the new order, the reporting application might fail because constraints are not checked and triggers are not fired.

Examples

If parallel queries are routinely being performed by applications, a certain number of parallel query servers should be reserved for those queries. To allocate 30 parallel query servers for logical standby log apply services, enter the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', 30);

Thus, if the PARALLEL_MAX_SERVERS parameter is set to 50, 30 servers will be available for logical standby processing and 20 parallel query servers will be allocated for parallel query processing.

Note:

If log apply services cannot allocate the parallel query server it requires because parallel queries are currently being processed, log apply services may stop. If this happens, start log apply services again.

APPLY_UNSET Procedure

Use the APPLY_UNSET procedure to reverse or undo the settings that you made with the APPLY_SET procedure. The APPLY_UNSET procedure resets the specified initialization parameter value to the system default value. The initialization parameter default value does not become active until log apply services are started.

Syntax

DBMS_LOGSTDBY.APPLY_UNSET (
     parameter          IN VARCHAR);

Parameters

Table 49-3 APPLY_UNSET Procedure Parameters

Parameter	Description
`MAX_SGA`	Number of megabytes for the system global area (SGA) allocation for log apply services cache. The default value is one quarter of the value set for the `SHARED_POOL_SIZE` initialization parameter.
`MAX_SERVERS`	Number of parallel query servers specifically reserved for log apply services. By default, log apply services use all available parallel query servers to read the log files and apply changes. See Oracle Database Reference for more information about parallel query servers.
`MAX_EVENTS_RECORDED`	Number of events that will be stored in the `DBA_LOGSTDBY_EVENTS` table, which stores logical standby event information.
`TRANSACTION_CONSISTENCY`	Level of transaction consistency maintained between the primary and standby databases. Specify one of the following values: `FULL`: Transactions are applied to the logical standby database in the exact order in which they were committed on the primary database. (Transactions are applied in commit SCN order.) This is the default parameter setting. `READ_ONLY`: Transactions are committed out of order (which provides better performance), but SQL `SELECT` statements executed on the standby database always return consistent results based on the last consistent SCN known to SQL apply. `NONE`: 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. Specify one of the following values: `TRUE`: Skipped errors are recorded in the `DBA_LOGSTDBY_EVENTS` table. This is the default parameter setting. `FALSE`: Skipped errors are not recorded in the `DBA_LOGSTDBY_EVENTS` table.
`RECORD_SKIP_DDL`	Controls whether skipped DDL statements are recorded in the `DBA_LOGSTDBY_EVENTS` table. Specify one of the following values: `TRUE`: Skipped DDL statements are recorded in the `DBA_LOGSTDBY_EVENTS` table. This is the default parameter setting. `FALSE`: Skipped DDL statements are not recorded in the `DBA_LOGSTDBY_EVENTS` table.
`RECORD_APPLIED_DDL`	Controls whether DDL statements that have been applied to the logical standby database are recorded in the `DBA_LOGSTDBY_EVENTS` table. 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. This is the default parameter setting. `FALSE`: Indicates that applied DDL statements are not recorded.
`APPLY_SERVERS`	Controls the number of parallel execution servers used to apply changes. See Oracle Data Guard Concepts and Administration for an explanation of the logical standby processes.
`PREPARE_SERVERS`	Controls the number of parallel execution servers used to prepare changes. See Oracle Data Guard Concepts and Administration for an explanation of the logical standby processes.

Usage Notes

Log apply services must not be applying archived redo log data to the standby database when you modify initialization parameters with the APPLY_UNSET procedure.
Use the APPLY_SET procedure to set the values of initialization parameters.

Examples

To unset the number of parallel query servers for log apply services, enter the following statement:

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

Assuming that the PARALLEL_MAX_SERVERS initialization parameter is set to 50, this statement will result in 50 parallel query servers being available for parallel query processing. This is because, by default, log apply services use all available parallel query servers to read the log files and apply changes.

Note:

BUILD Procedure

Use this procedure on the primary database to preserve important metadata (LogMiner dictionary) information in the redo logs. If supplemental logging has not been set correctly, this procedure sets it up and enables it automatically.

Syntax

DBMS_LOGSTDBY.BUILD;

Usage Notes

Supplemental log information includes extra information in the archived redo logs that helps log apply services to uniquely identify and correctly maintain tables in a logical standby database.
LogMiner dictionary information allows log apply services to interpret data in the redo logs.
The ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE statement also performs this functionality.

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.

Use the INSTANTIATE_TABLE procedure to:

Add a table to a standby database
Re-create a table in a standby database

Syntax

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

Parameters

Table 49-4 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.

Usage Notes

Use this procedure to create and populate a table in a way that keeps the data on the standby database transactionally consistent with the primary database.
This procedure assumes that the metadata has been maintained correctly.
This table is not safe until the redo log that was current on the primary database at the time of execution is applied to the standby database.

Examples

Enter this statement to create and populate a new table on the standby database.

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE (
    'myschema', 'mytable', 'mydblink');

SKIP Procedure

Use the SKIP procedure to define filters that prevent the application of SQL statements on the logical standby database.

By default, all SQL statements executed on a primary database are applied to a logical standby database. If only a subset of activity on a primary database is of interest for application to the standby database, you can use the SKIP procedure to define filters that prevent the application of SQL statements on the logical standby database. While skipping (ignoring) SQL statements is the primary goal of filters, it is also possible to associate a stored procedure with a DDL filter so that runtime determinations can be made whether to skip the statement, execute this statement, or execute a replacement statement.

Syntax

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

Parameters

Table 49-5 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 49-6 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_nam`e	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 log apply services 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 log apply services to perform one of the following: execute the statement, skip the statement, or execute a replacement statement. Log apply services 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 log apply services 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 `OUT NEW_STATEMENT VARCHAR2` -- The statement to execute in place of the original statement. Use of this option requires that `SKIP_ACTION` be set to `SKIP_ACTION_REPLACE`. Otherwise, set this option to `NULL`.
`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. See Oracle Database SQL Reference for more information about pattern matching.

Usage Notes

Caution:

Atomic execution cannot be guaranteed if hardware or software failures stop log apply services. In a failure situation, a statement maybe executed more than once.

These stored procedures are not supported with DBMS_LOGSTDBY.SKIP('DML'...). If multiple wildcards match a given database statement object defined by the stmt parameter, only one of the matching stored procedures will be called (alphabetically, by procedure).

Use the SKIP procedure with caution, particularly when skipping DDL statements. If a CREATE TABLE statement is skipped, for example, you must also specify other DDL statements that refer to that table in the SKIP procedure. Otherwise, the statements will fail and cause an exception. When this happens, log apply services stop running.
Before calling the SKIP procedure, log apply services must be halted. Do this by issuing an ALTER DATABASE STOP LOGICAL STANDBY APPLY statement. Once all desired filters have been specified, issue an ALTER DATABASE START LOGICAL STANDBY APPLY statement to start log apply services using the new filter settings.
See the UNSKIP Procedure for information about reversing (undoing) the settings of the SKIP procedure.

Skip Statement Options

Table 49-6 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 49-6 Supported Values for stmt Parameter

Keyword	Associated SQL Statements
`NON_SCHEMA_DDL`	All DDL that does not pertain to a particular schema
`SCHEMA_DLL`	All DDL that pertains to a particular schema
`DML`	Sequence operations such as `sequence.nextval`
`CLUSTER`	`CREATE CLUSTER AUDIT CLUSTER DROP CLUSTER TRUNCATE CLUSTER`
`CONTEXT`	`CREATE CONTEXT DROP CONTEXT`
`DATABASE LINK`	`CREATE DATABASE LINK DROP DATABASE LINK`
`DIMENSION`	`CREATE DIMENSION ALTER DIMENSION DROP DIMENSION`
`DIRECTORY`	`CREATE DIRECTORY DROP DIRECTORY`
`INDEX`	`CREATE INDEX ALTER INDEX DROP INDEX`
`PROCEDURE^{Foot 1}`	`CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE DROP FUNCTION DROP LIBRARY DROP PACKAGE DROP PROCEDURE`
`PROFILE`	`CREATE PROFILE ALTER PROFILE DROP PROFILE`
`PUBLIC DATABASE LINK`	`CREATE PUBLIC DATABASE LINK DROP PUBLIC DATABASE LINK`
`PUBLIC SYNONYM`	`CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM`
`ROLE`	`CREATE ROLE ALTER ROLE DROP ROLE SET ROLE`
`ROLLBACK STATEMENT`	`CREATE ROLLBACK SEGMENT ALTER ROLLBACK SEGMENT DROP ROLLBACK SEGMENT`
`SEQUENCE`	`CREATE SEQUENCE DROP SEQUENCE`
`SESSION`	Log-ons
`SYNONYM`	`CREATE SYNONYM DROP SYNONYM`
`SYSTEM AUDIT`	`AUDIT` SQL_statements `NOAUDIT` SQL_statements
`SYSTEM GRANT`	`GRANT` system_privileges_and_roles `REVOKE` system_privileges_and_roles
`TABLE`	`CREATE TABLE DROP TABLE TRUNCATE TABLE`
`TABLESPACE`	`CREATE TABLESPACE DROP TABLESPACE TRUNCATE TABLESPACE`
`TRIGGER`	`CREATE TRIGGER ALTER TRIGGER` with `ENABLE` and `DISABLE` clauses `DROP TRIGGER ALTER TABLE` with `ENABLE ALL TRIGGERS` clause `ALTER TABLE` with `DISABLE ALL TRIGGERS` clause
`TYPE`	`CREATE TYPE CREATE TYPE BODY ALTER TYPE DROP TYPE DROP TYPE BODY`
`USER`	`CREATE USER ALTER USER DROP USER`
`VIEW`	`CREATE VIEW DROP VIEW`

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

Exceptions

Table 49-7 DBMS_LOGSTDBY.SKIP Procedure Exceptions

Exception Description

Exception	Description
`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-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.

Examples

The following example shows how to use the SKIP procedure to skip (ignore) a schema on the logical standby database.

Example 1 Skip a Schema

To skip changes for a given schema, you must prevent log apply services from creating new objects in the schema and from modifying existing objects in the schema. In addition, the tablespace that supports the schema must not change. The following example demonstrates this using the SKIP procedure in a situation where schema smith has some number of tables defined in tablespace bones that we wish to ignore.

BEGIN
DBMS_LOGSTDBY.SKIP('SCHEMA_DDL', 'SMITH', '%', null);
DBMS_LOGSTDBY.SKIP('DML', 'SMITH', '%', null);
DBMS_LOGSTDBY.SKIP('TABLESPACE', null, null, 'SMITH.PROTECT_BONES');

END;

In the previous example, wildcards were used for the object_name parameter to indicate that the filter applies to all objects. In the last call to the SKIP procedure, the PROTECT_BONES procedure was supplied so that TABLESPACE could prevent tablespace operations on BONES. The following example is the definition for the PROTECT_BONES procedure:

CREATE OR REPLACE PROCEDURE PROTECT_BONES (statement      IN  VARCHAR2,
                                           statement_type IN  VARCHAR2,
                                           schema         IN  VARCHAR2,
                                           name           IN  VARCHAR2,
                                           xidusn         IN  NUMBER,
                                           xidslt         IN  NUMBER,
                                           xidsqn         IN  NUMBER,
                                           skip_action    OUT NUMBER,
                                           new_statement  OUT VARCHAR2) AS
    BEGIN
       -- Init
       new_statement := NULL;

       -- Guaranteed to be either CREATE, DROP, or TRUNCATE TABLESPACE
       IF statement LIKE '%TABLESPACE BONES%'
       THEN
           -- Skip the statement
           skip_action := DBMS_LOGSTDBY.SKIP_ACTION_SKIP;
       ELSE
           -- Apply the statement
           skip_action := DBMS_LOGSTDBY.SKIP_ACTION_APPLY;
       END IF;
    END protect_bones;

SKIP_ERROR Procedure

Upon encountering an error, the logical standby feature uses the criteria contained in this procedure to determine if the error should cause log apply services 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 49-8 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 since keywords, generally defined by the database object, identify all SQL statements that operate on the specified object. Table 49-6 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 log apply services 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 log apply services to perform one of the following: execute the statement, skip the statement, or execute a replacement statement. Log apply services call 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. See Oracle Database SQL Reference for more information about pattern matching.

Usage Notes

A stored procedure provided to the SKIP_ERROR procedure is called when log apply services encounter an error that could shut down the application of redo logs to the standby database.

Running this stored procedure affects the error being written in the STATUS column of the DBA_LOGSTDBY_EVENTS table. The STATUS_CODE column remains unchanged. If the stored procedure is to have no effect, that is, apply will be stopped, then the NEW_ERROR is written to the events table. To truly have no effect, set NEW_ERROR to ERROR in the procedure.

If the stored procedure requires that a shutdown be avoided, then you must set NEW_ERROR to NULL.

Example

DBMS_LOGSTDBY.SKIP_ERROR('DDL', 'joe', 'apptemp', null);

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.

You may want to use the SKIP_TRANSACTION procedure to:

Skip a DDL transaction that has already failed and that might otherwise cause log apply services to stop.
Skip a DDL transaction that may logically corrupt data.

Syntax

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

Parameters

Table 49-9 describes the parameters for the SKIP_TRANSACTION procedure.

Table 49-9 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 log apply services stop 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 log apply services to ignore.

CAUTION:

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.

Note:

Do not let the primary and logical standby databases diverge when skipping transactions. If possible, you should manually execute a compensating transaction in place of the skipped transaction.

View the last statement in DBA_LOGSTDBY_EVENTS to determine the reason that log apply services stopped processing transactions to the logical standby database. Examine the statement and error condition provided.
Use the DBA_LOGSTDBY_SKIP_TRANSACTION view to list the transactions that are going to be skipped by log apply services.
Also, see the ALTER DATABASE START LOGICAL STANDBY SKIP FAILED TRANSACTION statement in Oracle Database SQL Reference.

UNSKIP Procedure

This procedure reverses the actions of the SKIP procedure by finding the record, matching all the parameters, and removing the record from the system table. The match must be exact, and multiple skip actions can be undone only by a matching number of unskip actions. You cannot undo multiple skip actions using wildcard characters.

Syntax

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

Parameters

Table 49-10 UNSKIP 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 49-6 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_nam`e	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 log apply services 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 log apply services to perform one of the following: execute the statement, skip the statement, or execute a replacement statement. Log apply services 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 log apply services 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 `OUT NEW_STATEMENT VARCHAR2` -- The statement to execute in place of the original statement. Use of this option requires that `SKIP_ACTION` be set to `SKIP_ACTION_REPLACE`. Otherwise, set this option to `NULL`.
`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. See Oracle Database SQL Reference for more information about pattern matching.

Usage Notes

CAUTION:

If DML changes for a table have been skipped, you must follow the call to the UNSKIP procedure with a call to a procedure that will import the affected table.

UNSKIP_ERROR Procedure

This procedure reverses or undoes the actions of the SKIP_ERROR procedure by finding the record, matching all the parameters, and removing the record from the system table. The match must be exact, and multiple skip actions can be undone only by a matching number of unskip actions. You cannot undo multiple skip actions with just one unskip procedure call.

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 49-8 for complete parameter information.

Example

DBMS_LOGSTDBY.UNSKIP_ERROR;

UNSKIP_TRANSACTION Procedure

This procedure reverses the actions of the SKIP_TRANSACTION procedure. The match must be exact, and multiple skip transaction actions can be undone only by a matching number of unskip transaction actions. You cannot undo multiple skip transaction actions using wildcard characters.

Syntax

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

Parameters

Table 49-11 describes the parameters for the UNSKIP_TRANSACTION procedure.

Table 49-11 DBMS_LOGSTDBY.UNSKIP_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

Use the DBA_LOGSTDBY_SKIP_TRANSACTION view to list the transactions that are going to be skipped by log apply services.

49 DBMS_LOGSTDBY

Using DBMS_LOGSTBY

Overview

Operational Notes

Summary of DBMS_LOGSTDBY Subprograms

Table 49-1 DBMS_LOGSTDBY Package Subprograms

Syntax

Parameters

Table 49-2 APPLY_SET Procedure Parameters

Usage Notes

Examples

Syntax

Parameters

Table 49-3 APPLY_UNSET Procedure Parameters

Usage Notes

Examples

Syntax

Usage Notes

Syntax

Parameters

Table 49-4 INSTANTIATE_TABLE Procedure Parameters

Usage Notes

Examples

Syntax

Parameters

Table 49-5 SKIP Procedure Parameters

Usage Notes

Table 49-6 Supported Values for stmt Parameter

Exceptions

Table 49-7 DBMS_LOGSTDBY.SKIP Procedure Exceptions

Examples

Example 1 Skip a Schema

Syntax

Parameters

Table 49-8 SKIP_ERROR Procedure Parameters

Usage Notes

Example

Syntax

Parameters

Table 49-9 SKIP_TRANSACTION Procedure Parameters

Usage Notes

Syntax

Parameters

Table 49-10 UNSKIP Procedure Parameters

Usage Notes

Syntax

Parameters

Example

Syntax

Parameters

Table 49-11 DBMS_LOGSTDBY.UNSKIP_TRANSACTION Procedure Parameters

Usage Notes

49
DBMS_LOGSTDBY