86 DBMS_LOGSTDBY

Syntax

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

Parameters

If a parameter is changed while SQL Apply is running, the change will take effect at some point in the future. In such a case, an informational row is inserted into the DBA_LOGSTDBY_EVENTS view at the time the parameter change takes effect.

Additionally, if you are modifying a parameter while SQL Apply is running on an Oracle RAC configuration, you must be connected to the same instance where SQL Apply is running.

Exceptions

Usage Notes

• Use the APPLY_UNSET procedure to restore the default settings of a parameter.

• See Oracle Data Guard Concepts and Administration for help with tuning SQL Apply and for information about setting appropriate values for different parameters.

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);

Syntax

DBMS_LOGSTDBY.APPLY_UNSET (
     inname          IN VARCHAR);

Parameters

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

Exceptions

Usage Notes

• Use the APPLY_SET procedure to specify a nondefault value for a parameter.

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');

Syntax

DBMS_LOGSTDBY.BUILD;

Usage Notes

• Supplemental log information includes extra information in the redo logs that uniquely identifies a modified row in the logical standby database, and also includes information that helps efficient application of changes to the logical standby database.

• LogMiner dictionary information allows SQL Apply to interpret data in the redo logs.

• DBMS_LOGSTDBY.BUILD should be run only once for each logical standby database you want to create. You do not need to use DBMS_LOGSTDBY.BUILD for each Oracle RAC instance.

• DBMS_LOGSTDBY.BUILD waits for all transactions (including distributed transactions) that are active at the time of the procedure invocation to complete before returning. See Oracle Database Administrator's Guide for information about how to handle in-doubt transactions.

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;

Syntax

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

Parameters

Exceptions

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 table will not be synchronized with the rest of the tables being maintained by SQL Apply and SQL Apply will not start to maintain it until SQL Apply encounters redo that occurred after the table was instantiated from the primary. The SCN at which the table was instantiated from the primary database is available in the DBA_LOGSTDBY_EVENTS view.

• The specified table must be a table that is supported by logical standby (that is, it does not appear in the DBA_LOGSTDBY_UNSUPPORTED_TABLES view on the primary database).

• If there are any skip rules that specifically name this table (without any wildcards), those skip rules will be dropped as part of INSTANTIATE_TABLE, so that the table will be properly maintained by SQL Apply in the future. If there are skip rules that indirectly reference this table (match a skip rule with a wildcard in the schema_name or table_name, and have a TABLE, DML, or SCHEMA_DDL statement type), INSTANTIATE_TABLE will fail with an ORA-16278 error. Any multiobject skip rules that pertain to the table must be dropped or changed before re-attempting the INSTANTIATE_TABLE call.

Examples

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

Syntax

DBMS_LOGSTDBY.IS_APPLY_SERVER
RETURN BOOLEAN;

Parameters

None

Syntax

DBMS_LOGSTDBY.MAP_PRIMARY_SCN(primary_scn NUMBER) RETURN NUMBER;

Exceptions

Usage Notes

Use this function to get a conservative SCN at the logical standby database that corresponds to an SCN at the primary database. This function is useful in the context of doing compensating flashback database operations at the logical standby following a flashback database or a point-in-time recovery operation done at the primary database.

Syntax

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

Parameters

Exceptions

Usage Notes

• This routine is intended only for logical standby systems.This routine will fail if the new primary database was formerly a logical standby database and the LogMiner dictionary build has not completed successfully.Log files displayed in the alert log will be referred to as terminal logs. Users should keep in mind that file paths are relative to the new primary database and may not resolve locally.Upon manual registration of the terminal logs, users should complete the process by calling either START LOGICAL STANDBY APPLY if the new primary database was formerly a physical standby database or START LOGICAL STANDBY APPLY NEW PRIMARY if the new primary database was formerly a logical standby database.See the alert log for more details regarding the reasons for any exception.

Examples

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

Syntax

DBMS_LOGSTDBY.PURGE_SESSION;

Exceptions

Usage Notes

• This procedure does not delete the archived redo log files. You must issue operating system commands to delete unneeded files.

• This procedure updates the DBA_LOGMNR_PURGED_LOG view that displays the archived redo log files that have been applied to the logical standby database.

• In Oracle Database 10g Release 2, metadata related to the archived redo log files (and the actual archived redo log files) are purged automatically based on the default setting of the LOG_AUTO_DELETE parameter described in the DBMS_LOGSTDBY.APPLY_SET procedure described.

Example

To identify and remove unnecessary files:

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

   SQL> EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
   

2. 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
   

3. Use operating system-specific commands to delete archived redo log files from the file system.

Syntax

DBMS_LOGSTDBY.REBUILD;

Usage Notes

• LogMiner dictionary information is logged in the redo log files.The standby redo log files (if present) are archived.

Examples

SQL> EXECUTE DBMS_LOGSTDBY.REBUILD;

Syntax

DBMS_LOGSTDBY.SET_TABLESPACE(
           NEW_TABLESPACE IN VARCHAR2)

Parameters

Exceptions

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');

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

Create or replace procedure sec_mgr.skip_drop_policy (
statement     in varchar2,
pkgown         in varchar2,
pkgname        in varchar2,
procnm         in varchar2,
cuser          in varchar2,
xidusn         in number,
xidslt         in number,
xidsqn         in number,
exstatus       in number,
skip_action    out number) Is
Begin
  If 0 = exstatus Then
    Insert Into sec_mgr.logit Values
      ('Success: '||pkgown||'.'||pkgname||'.'||procnm|| ' by '||cuser);
    
    If cuser != 'TESTSCHEMA' Then  
      skip_action := DBMS_LOGSTDBY.SKIP_ACTION_APPLY;
    Else
      skip_action := DBMS_LOGSTDBY.SKIP_ACTION_SKIP;
    End If;  
  End If;
End skip_drop_policy;

EXECUTE DBMS_LOGSTDBY.SKIP( -
     stmt => 'PL/SQL', -
     schema_name => 'SYS', -
     object_name => 'DBMS_RLS.DROP_POLICY', -
     proc_name => 'SEC_MGR.SKIP_DROP_POLICY' -
     use_like=> FALSE);

Usage Notes

• This procedure requires DBA privileges to execute.

• You cannot associate a stored procedure to be invoked in the context of a DML statement. For example, the following statement returns the ORA-16104: invalid Logical Standby option requested error:

  SQL> EXECUTE DBMS_LOGSTDBY.SKIP(-
       stmt => 'DML', -
       schema_name => 'HR', -
       object_name => 'EMPLOYEES', -
       proc_name => 'DML_HANDLER');
  

  Also, if an event matches multiple rules either because of the use of wildcards while specifying the rule or because of a specification of overlapping rules. For example, if you specify a rule for the SCHEMA_DDL event for the HR.EMPLOYEES table, and a rule for the ALTER TABLE event for the HR.EMPLOYEES table, only one of the matching procedures will be invoked (alphabetically, by procedure). In the following code example, consider the following rules:

  SQL> EXECUTE DBMS_LOGSTDBY.SKIP( -
       stmt => 'SCHEMA DDL', -
       schema_name => 'HR', -
       object_name => 'EMPLOYEES', -
       proc_name => 'SCHEMA_DDL_HANDLER');
  SQL> EXECUTE DBMS_LOGSTDBY.SKIP( -
       stmt => 'ALTER TABLE', -
       schema_name => 'HR', -
       object_name => 'EMPLOYEES', -
       proc_name => 'TABLE_ALTER_HANDLER');
  

  On encountering an ALTER TABLE statement, the schema_ddl_handler procedure will be invoked because its name will be at the top of an alphabetically sorted list of procedures that are relevant to the statement.Collisions on a rule set because of a specification containing wildcard entries are resolved in a similar fashion. For example, the rules in the following example will result in the empddl_handler procedure being invoked upon encountering the ALTER TABLE HR.EMPLOYEES ADD COLUMN RATING NUMBER statement:

  SQL> EXECUTE DBMS_LOGSTDBY.SKIP(-
       stmt => 'ALTER TABLE', -
       schema_name => 'HR', -
       object_name => 'EMP%', -
       proc_name => 'EMPDDL_HANDLER');
  SQL> EXECUTE DBMS_LOGSTDBY.SKIP( -
       stmt => 'ALTER TABLE', -
       schema_name => 'HR', -
       object_name => 'EMPLOYEES', -
       proc_name => 'EMPLOYEE_DDL_HANDLER');
  

• 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, SQL Apply stops running.

• Before calling the SKIP procedure, SQL Apply 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 IMMEDIATE statement to start SQL Apply using the new filter settings.

• See the UNSKIP procedure for information about reversing (undoing) the settings of the SKIP procedure.

• For USER statements, the SCHEMA_NAME parameter will be the user and specify '%' for the OBJECT_NAME parameter.

• If the PROC_NAME parameter is supplied, it must already exist in DBA_PROCEDURES and it must execute with DEFINER rights. If the procedure is declared with INVOKER rights, the ORA-1031: insufficient privileges message will be returned.

• If the procedure returns a REPLACEMENT statement, the REPLACEMENT statement will be executed using the SYSTEM and OBJECT privileges of the owner of the procedure.

• The PL/SQL block of a SKIP procedure cannot contain transaction control statements (for example, COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) unless the block is declared to be an autonomous transaction.

Skip Statement Options

Table 86-14 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. In addition, any of the SQL statements listed in the sys.audit_actions table (shown in the right column of Table 86-14) are also valid values. Note that keywords are generally defined by database object.

GRANT
REVOKE
ANALYZE TABLE
ANALYZE INDEX
ANALYZE CLUSTER

AUDIT CLUSTER
CREATE CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER

CREATE CONTEXT
DROP CONTEXT

CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP DATABASE LINK
DROP PUBLIC DATABASE LINK

ALTER DIMENSION
CREATE DIMENSION
DROP DIMENSION

CREATE DIRECTORY
DROP DIRECTORY

ALTER INDEX
CREATE INDEX
DROP INDEX

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

ALTER PROFILE
CREATE PROFILE
DROP PROFILE

ALTER ROLE
CREATE ROLE
DROP ROLE
SET ROLE

ALTER ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT

ALTER SEQUENCE
CREATE SEQUENCE
DROP SEQUENCE

CREATE PUBLIC SYNONYM
CREATE SYNONYM
DROP PUBLIC SYNONYM
DROP SYNONYM

AUDIT SQL_statements
NOAUDIT SQL_statements

CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE TABLE

CREATE TABLESPACE
DROP TABLESPACE
ALTER TABLESPACE

ALTER TRIGGER
CREATE TRIGGER
DISABLE ALL TRIGGERS
DISABLE TRIGGER
DROP TRIGGER
ENABLE ALL TRIGGERS
ENABLE TRIGGER

ALTER TYPE
ALTER TYPE BODY
CREATE TYPE
CREATE TYPE BODY
DROP TYPE
DROP TYPE BODY

ALTER USER
CREATE USER
DROP USER

CREATE VIEW
DROP VIEW

CREATE VIEW
DROP VIEW

^Footnote 1 All directory objects are owned by SYS, but for the purpose of filtering them with a skip directive the schema should be specified as '%'".

^Footnote 2  See Oracle Data Guard Concepts and Administration for information about supported packages.

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

Exceptions

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', -
     object_name => '%', -
     proc_name => null);
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DML', -
     schema_name => 'HR', -
     object_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');
   

Syntax

DBMS_LOGSTDBY.SKIP_ERROR (
     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 NULL,
     esc                       IN CHAR1    DEFAULT NULL);

Parameters

Usage Notes

• A stored procedure provided to the SKIP_ERROR procedure is called when SQL Apply encounters 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.

• This procedure requires DBA privileges to execute.

• For USER statements, the SCHEMA_NAME parameter will be the user and you should specify '%' for the OBJECT_NAME parameter.

• If the PROC_NAME parameter is specified, it must already exist in DBA_PROCEDURES and it must execute with DEFINERS rights. If the procedure is declared with INVOKERS rights, the ORA-1031: insufficient privileges message will be returned.

• The PL/SQL block of a SKIP_ERROR procedure cannot contain transaction control statements (for example: COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) unless the block is declared to be an autonomous transaction using the following syntax:

  PRAGMA AUTONOMOUS_TRANSACTION
  

Exceptions

Example 1

The following example shows how to specify rules so that SQL Apply will skip any error raised from any GRANT DDL command.

SQL> EXECUTE DBMS_LOGSTDBY.SKIP_ERROR('GRANT') 

Example 2

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_error OUT VARCHAR2 
   ) AS 
   BEGIN 
   -- Default to what we already have 
   new_error := error; 
   -- 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_error := 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');
   

Syntax

DBMS_LOGSTDBY.SKIP_TRANSACTION (
     xidusn          IN NUMBER,
     xidslt          IN NUMBER,
     xidsqn          IN NUMBER);

Parameters

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.

Exceptions

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);

Syntax

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

Parameters

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

Exceptions

Usage Notes

• This procedure requires DBA privileges to execute.

• Wildcards passed in the schema_name or the object_name parameter are not expanded. The wildcard character is matched at the character level. Thus, you can delete only one specified rule by invoking the UNSKIP procedure, and you will need a distinct UNSKIP procedure call to delete each rule that was previously specified.

  For example, assume you have specified the following two rules to skip applying DML statements to the HR.EMPLOYEE and HR.EMPTEMP tables:

  SQL> EXECUTE DBMS_LOGSTDBY.SKIP (STMT => 'DML',-
       SCHEMA_NAME => 'HR', -
       OBJECT_NAME => 'EMPLOYEE', -
       PROC_NAME => null);
  SQL> EXECUTE DBMS_LOGSTDBY.SKIP (STMT => 'DML',-
       SCHEMA_NAME => 'HR', -
       OBJECT_NAME => 'EMPTEMP', -
       PROC_NAME => null);
  

  In the following example, the wildcard in the TABLE_NAME parameter cannot be used to delete the rules that were specified:

  SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP (STMT => 'DML',-
       SCHEMA_NAME => 'HR', -
       OBJECT_NAME => 'EMP%');
  

  In fact, this UNSKIP procedure matches neither of the rules, because the wildcard character in the TABLE_NAME parameter is not expanded. Instead, the wildcard character will be used in an exact match to find the corresponding SKIP rule.

Syntax

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

Parameters

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

Exceptions

Usage Notes

• This procedure requires DBA privileges to execute.

• Wildcards passed in the schema_name or the object_name parameters are not expanded. Instead, the wildcard character is treated as any other character and an exact match is made. Thus, you can delete only one specified rule by invoking the UNSKIP_ERROR procedure, and you need a distinct UNSKIP_ERROR procedure call to delete each rule that you previously specified.

  For example, assume you have specified the following two rules to handle the HR.EMPLOYEE and HR.EMPTEMP tables:

  SQL> EXECUTE DBMS_LOGSTDBY.SKIP_ERROR (STMT => 'DML',-
       SCHEMA_NAME => 'HR', -
       OBJECT_NAME => 'EMPLOYEE', -
       PROC_NAME => 'hr_employee_handler');
  SQL> EXECUTE DBMS_LOGSTDBY.SKIP_ERROR (STMT => 'DML',-
       SCHEMA_NAME => 'HR', -
       OBJECT_NAME => 'EMPTEMP', -
       PROC_NAME => 'hr_tempemp_handler');
  

  In this case, the following UNSKIP procedure cannot be used to delete the rules that you have specified:

  SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP_ERROR (STMT => 'DML',-
       SCHEMA_NAME => 'HR', -
       OBJECT_NAME => 'EMP%');
  

  In fact, the UNSKIP procedure will match neither of the rules, because the wildcard character in the OBJECT_NAME parameter will not be expanded.

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);

Syntax

DBMS_LOGSTDBY.UNSKIP_TRANSACTION (
     xidusn_p         IN NUMBER,
     xidslt_p         IN NUMBER,
     xidsqn_p         IN NUMBER);

Parameters

Exceptions

Usage Notes

• This procedure requires DBA privileges to execute.

• Query the DBA_LOGSTDBY_SKIP_TRANSACTION view to list the transactions that are going to be skipped by SQL Apply.

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);