DBMS_LOGSTDBY, 2 of 2

Summary of DBMS_LOGSTDBY Subprograms

Table 29-1 describes the procedures of the DBMS_LOGSTDBY package.

Table 29-1 DBMS_LOGSTDBY Package Subprograms

Subprograms	Description
APPLY_SET Procedure	Allows you to set the values of specific initialization parameters to 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
GUARD_BYPASS_OFF Procedure	Re-enables the database guard that you bypassed previously with the GUARD_BYPASS_ON procedure
GUARD_BYPASS_ON Procedure	Allows the current session to bypass the database guard so that tables in a logical standby database can be modified
INSTANTIATE_TABLE Procedure	Creates and populates a table in the standby database from a corresponding table in the primary database
SKIP Procedure	Allows you to specify what database operations that are done on the primary database will not be applied to the logical standby database
SKIP_ERROR Procedure	Specifies criteria to follow if an error is encountered. You can choose to stop log apply services or ignore the error
SKIP_TRANSACTION Procedure	Specifyies 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. Log apply services cannot be running when you use this procedure.

Syntax

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

Parameters

Table 29-2 describes the parameters for the APPLY_SET procedure.

Table 29-2 DBMS_LOGSTDBY.APPLY_SET Procedure Parameters

Parameter	Description
`APPLY_DELAY`	Specifies an apply delay interval (in minutes) to the managed recovery operation on the standby database. Use the `APPLY_DELAY` parameter with the `APPLY_UNSET` procedure after a failover scenario, when the primary database is not expected to return.
`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 Oracle9i 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 option results in the lowest performance. This is the default parameter setting. `NONE`: Transactions are applied out of order. This results in the best performance of the three modes. 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 which expects to find a customer for the new order, the reporting application might fail because constraints are not checked and triggers are not fired. `READ_ONLY`: Transactions are committed out of order (which provides better performance), but periodically enforced in order apply. SQL `SELECT` statements, executed on the standby database, always return consistent results based on the last consistent SCN known to the apply engine. The apply engine periodically refreshes an SCN maintained in SGA which 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 mode.
`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.

Exceptions

Table 29-3 describes the exceptions for the APPLY_SET procedure.

Table 29-3 DBMS_LOGSTDBY.APPLY_SET Procedure Exceptions

Exception	Description
`ORA-16104`	Invalid option
`ORA-16103`	Logical standby database must be stopped

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.
When a primary database is no longer available after failover, use the DBMS_LOGSTDBY.APPLY_UNSET('APPLY_DELAY') procedure to remove the setting provided by the initialization parameter file.
Use the APPLY_UNSET Procedure to reverse (undo) the actions of the APPLY_SET procedure.

Example

If parallel queries are routinely being performed by applications, a certain number of parallel 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 you start log apply services while a parallel query is running, you may get an error.

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

The APPLY_UNSET procedure supports the same initialization parameters shown for the APPLY_SET procedure.

See Also:

Table 29-2 for the APPLY_SET procedure parameters

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.

Example

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:

If you start log apply services while a parallel query is running, you may get an error.

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;

Parameters

None.

Exceptions

None.

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.

GUARD_BYPASS_OFF Procedure

Use the GUARD_BYPASS_OFF procedure to re-enable the database guard that you bypassed previously with the GUARD_BYPASS_ON Procedure procedure.

Syntax

DBMS_LOGSTDBY.GUARD_BYPASS_OFF;

Parameters

None.

Exceptions

None.

Usage Notes

See the GUARD_BYPASS_ON Procedure procedure for information about bypassing the database guard and performing maintenance on a table in the logical standby database.

Example

Enter the following statement to return the current session to the state it was in before the GUARD_BYPASS_ON Procedure was executed.

SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;

Typically, you need to use this command only after executing the GUARD_BYPASS_ON Procedure to bypass the database guard.

GUARD_BYPASS_ON Procedure

By default, tables in a logical standby database are protected from modifications. However, you can use the GUARD_BYPASS_ON procedure to bypass the database guard and make modifications to the logical standby database. For example, to perform maintenance or correct problems on a table in the logical standby database. Applications should not execute transactions against the database when you use this procedure, because triggers are not run and constraints are not checked.

Syntax

DBMS_LOGSTDBY.GUARD_BYPASS_ON;

Parameters

None.

Exceptions

None.

Usage Notes

This procedure affects the current session only.
When you bypass the database guard with the GUARD_BYPASS_ON procedure, triggers are not run and constraints are not checked.
Do not allow applications to execute when the use the GUARD_BYPASS_ON procedure to bypass the database guard. This environment is intended only for maintenance reasons, such as to correct problems or to perform maintenance such as rebuilding indexes or refreshing materialized views.

Example

Enter the following statement to allow modifications to tables in the logical standby database.

SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;

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 (
     table_name          IN VARCHAR2,
     schema_name         IN VARCHAR2,
     dblink              IN VARCHAR2);

Parameters

Table 29-4 describes the parameters for the INSTANTIATE_TABLE procedure.

Table 29-4 DBMS_LOGSTDBY.INSTANTIATE_TABLE Procedure Parameters

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

Exceptions

None.

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.

Example

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

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 replication, the SKIP procedure defines 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 filter so that runtime determinations can be made whether to skip the statement, execute this statement, or execute a replacement statement.

Before calling this procedure, log apply services must be halted. This is done 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.

Syntax

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

Parameters

Table 29-5 describes the parameters for the SKIP procedure.

Table 29-5 DBMS_LOGSTDBY.SKIP Procedure Parameters

Parameter	Description
`statement_option`	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 29-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 `statement_option` 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 `statement_option`. 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 `statement_option`, `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 `statement_option` 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`.

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 statement_option parameter, only one of the matching stored procedures will be called (alphabetically, by procedure).

Skip Statement Options

Table 29-6 lists the supported values for the statement_option 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 29-6 Supported Values for statement_option 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`	Logons
`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 29-7 describes an exception for the SKIP procedure.

Table 29-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.

Usage Notes

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.
See the UNSKIP Procedure for information about reversing (undoing) the settings of the SKIP procedure.

Example

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 (
     statement_option          IN VARCHAR2,
     schema_name               IN VARCHAR2,
     object_name               IN VARCHAR2,
     proc_name                 IN VARCHAR2);

Parameters

Table 29-8 describes the parameters for the SKIP_ERROR procedure.

Table 29-8 DBMS_LOGSTDBY.SKIP_ERROR Procedure Parameters

Parameter	Description
`statement_option`	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 29-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 `statement_option` 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 `statement_option`. 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 `statement_option`, `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 VARCHAR(4000)` -- The first 4K of the statement `IN STATEMENT_TYPE VARCHAR2` -- The `statement_option` 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

Exceptions

None.

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 truely 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 transaction that has already failed and that might otherwise cause log apply services to stop.
Skip a transaction that may logically corrupt data.

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.

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.

Syntax

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

Parameters

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

Table 29-9 DBMS_LOGSTDBY.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

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.

Exceptions

None.

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 (
     statement_option          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 29-5 for complete parameter information.

Exceptions

None.

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 (
     statement_option          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 29-8 for complete parameter information.

Exceptions

None.

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 29-10 describes the parameters for the UNSKIP_TRANSACTION procedure.

Table 29-10 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.

Exceptions

None.