APPLY_SERVERS

Controls the number of APPLIER processes used to apply changes. The maximum number allowed is 1024, provided the MAX_SERVERS parameter is set to accommodate this.

EVENT_LOG_DEST

Controls where SQL Apply records the occurrence of an interesting event. It takes the following values:

• DEST_ALL - All events will be recorded in the DBA_LOGSTDBY_EVENTS view and in the alert log.

• DEST_EVENTS_TABLE - All events that contain information about user data will be recorded only in the DBA_LOGSTDBY_EVENTS view. This is the default value.

For example, if SQL Apply receives an ORA-1403 error, the whole event is recorded in theDBA_LOGSTDBY_EVENTS view. Whereas, the alert log records only that SQL Apply stopped because of ORA-1403. No information regarding the user table or offending statement is logged in the alert log. However, if you stop the SQL Apply engine, it gets recorded in both theDBA_LOGSTDBY_EVENTS view and in the alert log.

Note that this parameter affects the behavior of the following parameters:RECORD_APPLIED_DDL, RECORD_SKIP_DDL, RECORD_SKIP_ERRORS, andRECORD_UNSUPPORTED_OPERATIONS. For example, if RECORD_APPLIED_DDL is set to TRUE, butEVENT_LOG_DEST is set to DEST_EVENTS_TABLE, then the applied DDL string will only be recorded in the DBA_LOGSTDBY_EVENTS view.

LOG_AUTO_DEL_RETENTION_TARGET

This parameter setting is only meaningful if LOG_AUTO_DELETE has been set to TRUE. The value you supply for this parameter controls how long (in minutes) a remote archived log that is received from the primary database will be retained at the logical standby database once all redo records contained in the log have been applied at the logical standby database. The default value is 1440 minutes.

LOG_AUTO_DELETE

Automatically deletes foreign archived redo log files as soon as they have been applied on the logical standby database. By default, a foreign archived redo log file is not deleted until 24 hours (the default value of LOG_AUTO_DEL_RETENTION_TARGET parameter) after it has been applied at the logical standby database. Set to TRUE to enable automatic deletion of archived redo log files. Set to FALSE to disable automatic deletion. The default value is TRUE.

MAX_EVENTS_RECORDED

Number of recent events that will be visible through the DBA_LOGSTDBY_EVENTS view. To record all events encountered by SQL Apply, use the DBMS_LOGSTDBY.MAX_EVENTS constant as the number value. The default value is 10,000.

MAX_SERVERS

Number of processes that SQL Apply uses to read and apply redo. The default value is 9. The maximum number allowed is 2048.

MAX_SGA

Number of megabytes from shared pool in System Global Area (SGA) that SQL Apply will use. The default value is 30 megabytes or one quarter of the value set for SHARED_POOL_SIZE, whichever is lower. The maximum size allowed is 4095 megabytes.

PREPARE_SERVERS

Controls the number of PREPARER processes used to prepare changes. The maximum number allowed is 1024, provided the MAX_SERVERS parameter is set to accommodate this.

PRESERVE_COMMIT_ORDER

TRUE: Transactions are applied to the logical standby database in the exact order in which they were committed on the primary database. This is the default parameter setting.

FALSE: Transactions containing non-overlapping sets of rows may be committed in a different order than they were committed on the primary database.

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

You cannot modify this parameter while SQL Apply is running.

RECORD_APPLIED_DDL

Controls whether DDL statements that have been applied to the logical standby database are recorded in the location specified by the EVENT_LOG_DEST parameter. Specify one of the following values:

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

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

RECORD_SKIP_DDL

Controls whether skipped DDL statements are recorded in the location specified by theEVENT_LOG_DEST parameter. Specify one of the following values:

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

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

RECORD_SKIP_ERRORS

Controls whether skipped errors (as described by the SKIP_ERROR procedure) are recorded in the location specified by the EVENT_LOG_DEST parameter. Specify one of the following values:

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

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

RECORD_UNSUPPORTED_OPERATIONS

Captures information about transactions running on the primary database that will not be supported by a logical standby database. This procedure records its information as events in theDBA_LOGSTDBY_EVENTS table. Specify one of the following values:

TRUE: The information is captured and recorded as events in the DBA_LOGSTDBY_EVENTS table.

FALSE: The information is not captured. This is the default.

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16104

invalid Logical Standby option requested

ORA-16236

Logical Standby metadata operation in progress

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16104

invalid Logical Standby option requested

ORA-16236

Logical Standby metadata operation in progress

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, as well as the SELECT_CATALOG_ROLE on the primary database

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16236

Logical Standby metadata operation in progress

ORA-16276

Specified database link does not correspond to primary database

ORA-16277

Specified table is not supported by logical standby database

ORA-16278

Specified table has a multi-object skip rule defined

ORA-20001

Primary SCN is before mapped range

ORA-20002

SCN mapping requires PRESERVE_COMMIT_ORDER to be TRUE

FORMER_STANDBY_TYPE

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

DBLINK

The name of a database link to the new primary database

ORA-16104

Invalid Logical Standby option.

ORA-16109

Failed to apply log data from previous primary.

ORA-01309

Invalid session

NEW_TABLESPACE

Name of the new tablespace where metadata tables will reside.

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16236

Logical Standby metadata operation in progress

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 86-14 shows a list of keywords and the equivalent SQL statements, either of which is a valid value for this parameter.

The keyword PL/SQL is used for the execution of Oracle-supplied packages which are supported for replication. See  for information about supported packages.

schema_name

The name of one or more schemas (wildcards are permitted) associated with the SQL statements identified by the stmt parameter. If not applicable, this value must be set to NULL.

object_name

The name of one or more objects (wildcards are permitted) associated with the SQL statements identified by the stmt. If not applicable, this value must be set to NULL.

proc_name

Name of a stored procedure to call when SQL Apply determines that a particular statement matches the filter defined by the stmt, schema_name, and object_name parameters. Specify the procedure in the following format:

'schema.package.procedure'

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

For DDLs, SQL Apply calls the stored procedure with the following call signature:

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

• IN STATEMENT_TYPE VARCHAR2 -- The stmt of the filter

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

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

• IN XIDUSN NUMBER -- Transaction ID part 1

• IN XIDSLT NUMBER -- Transaction ID part 2

• IN XIDSQN NUMBER -- Transaction ID part 3

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

  SKIP_ACTION_APPLY -- Execute the statement

  SKIP_ACTION_SKIP -- Skip the statement

  SKIP_ACTION_ERROR -- Halt apply so the DBA can take appropriate steps (for example, take compensating action)

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

For PL/SQL, SQL Apply calls the stored procedure with the following call signature:

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

• IN PACKAGE_SCHEMA VARCHAR2 -- The schema of the package being skipped (for example, SYS or XDB)

• IN PACKAGE_NAME VARCHAR2 -- The name of the package being skipped (for example,DBMS_RLS)

• IN PROCEDURE_NAME VARCHAR2 – The name of the procedure being skipped (for example, ADD_POLICY)

• IN CURRENT_SCHEMA VARCHAR2 – The name of the current schema in which the PL/SQL was executed on the primary

• IN XIDUSN NUMBER – Transaction ID part 1

• IN XIDSLT NUMBER -- Transaction ID part 2

• IN XIDSQN NUMBER -- Transaction ID part 3

• IN EXIT_STATUS – 0 (Zero) if the PL/SQL succeeded on the primary, or 1

proc_name (cont.)

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

  SKIP_ACTION_APPLY -- Execute the statement

  SKIP_ACTION_APPLY -- Execute the statement

  SKIP_ACTION_ERROR – Raise an error which halts apply so that the DBA can take appropriate steps

Note 1: SKIP_ACTION_REPLACE is not supported for PL/SQL.

Note 2: SQL Apply calls the skip handler when the procedure's exit is processed.

Note 3: The use_like parameter must be set to FALSE for PL/SQL since wildcarding PL/SQL is not supported.

The following example shows how to have a conditional skip rule on DBMS_RLS.DROP_POLICY:

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

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 .

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 for more information about pattern matching.

There is no keyword for this group of SQL statements.

GRANT
REVOKE
ANALYZE TABLE
ANALYZE INDEX
ANALYZE CLUSTER

CLUSTER

AUDIT CLUSTER
CREATE CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER

CONTEXT

CREATE CONTEXT
DROP CONTEXT

DATABASE LINK

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

DIMENSION

ALTER DIMENSION
CREATE DIMENSION
DROP DIMENSION

DIRECTORYFoot 1 

CREATE DIRECTORY
DROP DIRECTORY

DML

Includes DML statements on a table (for example: INSERT, UPDATE, and DELETE)

INDEX

ALTER INDEX
CREATE INDEX
DROP INDEX

NON_SCHEMA_DDL

All DDL that does not pertain to a particular schema

Note: SCHEMA_NAME and OBJECT_NAME must be null

PL/SQLFoot 2 

Execute Oracle-supplied package.

PROCEDUREFoot 3 

ALTER FUNCTION
ALTER PACKAGE
ALTER PACKAGE BODY
ALTER PROCEDURE
CREATE FUNCTION
CREATE LIBRARY
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PROCEDURE
DROP FUNCTION
DROP LIBRARY
DROP PACKAGE
DROP PACKAGE BODY
DROP PROCEDURE

PROFILE

ALTER PROFILE
CREATE PROFILE
DROP PROFILE

ROLE

ALTER ROLE
CREATE ROLE
DROP ROLE
SET ROLE

ROLLBACK STATEMENT

ALTER ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT

SCHEMA_DDL

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

Note: SCHEMA_NAME and OBJECT_NAME must not be null

SEQUENCE

ALTER SEQUENCE
CREATE SEQUENCE
DROP SEQUENCE

SYNONYM

CREATE PUBLIC SYNONYM
CREATE SYNONYM
DROP PUBLIC SYNONYM
DROP SYNONYM

SYSTEM AUDIT

AUDIT SQL_statements NOAUDIT SQL_statements 

TABLE

CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE TABLE

TABLESPACE

CREATE TABLESPACE
DROP TABLESPACE
ALTER TABLESPACE

TRIGGER

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

TYPE

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

USER

ALTER USER
CREATE USER
DROP USER

VIEW

CREATE VIEW
DROP VIEW

VIEW

CREATE VIEW
DROP VIEW

ORA-01031

Insufficient privileges:

• Procedure used INVOKER rights

• Procedure needs DBA privileges

ORA-16103

Logical standby apply must be stopped to allow this operation.

ORA-16104

Invalid logical standby option requested.

ORA-16203

"Unable to interpret SKIP procedure return values."

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

ORA-16236

Logical standby metadata operation in progress.

stmt

Either a keyword that identifies a set of SQL statements or a specific SQL statement. The use of keywords simplifies configuration because keywords, generally defined by the database object, identify all SQL statements that operate on the specified object. Table 86-14 shows a list of keywords and the equivalent SQL statements, either of which is a valid value for this parameter.

schema_name

The name of one or more schemas (wildcards are permitted) associated with the SQL statements identified by the stmt parameter. If not applicable, this value must be set to NULL.

object_name

The name of one or more objects (wildcards are permitted) associated with the SQL statements identified by the stmt. If not applicable, this value must be set to NULL.

proc_name

Name of a stored procedure to call when SQL Apply encounters an error and determines a particular statement matches the filter defined by the stmt, schema_name, and object_nameparameters. Specify the procedure in the following format:

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

This procedure returns an error message that directs SQL Apply to perform one of the following actions:

• Silently skip the error and continue with SQL Apply

• Replace the error message that would have been created with a custom one, and stop SQL Apply

• Do nothing, causing SQL Apply to stop and the original error message to be logged

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

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

• IN STATEMENT_TYPE VARCHAR2 -- The stmt of the filter

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

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

• IN XIDUSN NUMBER -- Transaction ID part 1

• IN XIDSLT NUMBER -- Transaction ID part 2

• IN XIDSQN NUMBER -- Transaction ID part 3

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

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

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 for more information about pattern matching.

ORA-01031

Insufficient privileges:

• Procedure used INVOKER rights

• Procedure needs DBA privileges

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16104

invalid Logical Standby option requested

ORA-16236

Logical Standby metadata operation in progress

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

ORA-01031

Need DBA privileges

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16104

invalid Logical Standby option requested

ORA-01031

need DBA privileges to execute this procedure

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16104

invalid Logical Standby option requested

ORA-01031

Need DBA privileges

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16104

invalid Logical Standby option requested

XIDUSN

Transaction ID undo segment number of the transaction being skipped

XIDSLT

Transaction ID slot number of the transaction being skipped

XIDSQN

Transaction ID sequence number of the transaction being skipped

ORA-01031

need DBA privileges to execute this procedure

ORA-16103

Logical Standby apply must be stopped to allow this operation

ORA-16104

invalid Logical Standby option requested