ADD SCHEMATRANDATA

Valid for Oracle. Use ADD SCHEMATRANDATA to enable schema-level supplemental logging for a table. ADD SCHEMATRANDATA acts on all of the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification.

ADD SCHEMATRANDATA is valid for both integrated and classic capture and does the following:

  • Enables Oracle supplemental logging for new tables created with a CREATE TABLE.

  • Updates supplemental logging for tables affected by an ALTER TABLE to add or drop columns.

  • Updates supplemental logging for tables that are renamed.

  • Updates supplemental logging for tables for which unique or primary keys are added or dropped.

By default, ADD SCHEMATRANDATA logs the key columns of a table in the following order of priority:

  1. Primary key

  2. In the absence of a primary key, all of the unique keys of the table, including those that are disabled, unusable or invisible. Unique keys that contain ADT member columns are also logged. Only unique keys on virtual columns (function-based indexes) are not logged.

  3. If none of the preceding exists, all scalar columns of the table are logged. (System-generated row-OIDs are always logged.)

ADD SCHEMATRANDATA also supports the conditional or unconditional logging requirements for using integrated Replicat.

Note:

Apply Oracle Patch 10423000 to the source database if the Oracle version is earlier than 11.2.0.2.

When to Use ADD SCHEMATRANDATA

ADD SCHEMATRANDATA must be used in the following cases:

  • For all tables that are part of an Extract group that is to be configured for integrated capture. ADD SCHEMATRANDATA ensures that the correct key is logged by logging all of the keys.

  • For all source tables that will be processed in an integrated Replicat group. Options are provided that enable the logging of the primary, unique, and foreign keys to support the computation of dependencies among relational tables being processed through different apply servers.

  • When DDL replication is active and DML is concurrent with DDL that creates new tables or alters key columns. It best handles scenarios where DML can be applied to objects very shortly after DDL is issued on them. ADD SCHEMATRANDATA causes the appropriate key values to be logged in the redo log atomically with each DDL operation, thus ensuring metadata continuity for the DML when it is captured from the log, despite any lag in Extract processing.

Database-level Logging Requirements for Using ADD SCHEMATRANDATA

Oracle strongly encourages putting the source database into forced logging mode and enabling minimal supplemental logging at the database level when using Oracle GoldenGate. This adds row chaining information, if any exists, to the redo log for update operations. See Installing and Configuring Oracle GoldenGate for Oracle Database for more information about configuring logging to support Oracle GoldenGate.

Additional Considerations for Using ADD SCHEMATRANDATA

  • Before using ADD SCHEMATRANDATA, issue the DBLOGIN command. The user who issues the command must be granted the Oracle Streams administrator privilege.

    SQL> exec dbms_streams_auth.grant_admin_privilege('user')
    
  • ADD SCHEMATRANDATA can be used instead of the ADD TRANDATA command when DDL replication is not enabled. Note, however, that if a table has no primary key but has multiple unique keys, ADD SCHEMATRANDATA causes the database to log all of the unique keys. In such cases, ADD SCHEMATRANDATA causes the database to log more redo data than does ADD TRANDATA. To avoid the extra logging, designate one of the unique keys as a primary key, if possible.

  • For tables with a primary key, with a single unique key, or without a key, ADD SCHEMATRANDATA adds no additional logging overhead, as compared to ADD TRANDATA. For more information, see "ADD TRANDATA".

  • If you must log additional, non-key columns of a specific table (or tables) for use by Oracle GoldenGate, such as those needed for FILTER statements and KEYCOLS clauses in the TABLE and MAP parameters, issue an ADD TRANDATA command for those columns. That command has a COLS option to issue table-level supplemental logging for the columns, and it can be used in conjunction with ADD SCHEMATRANDATA.

Syntax

ADD SCHEMATRANDATA schema {
[ALLOWNONVALIDATEDKEYS] [NOSCHEDULINGCOLS | ALLCOLS]}
schema

The schema for which you want the supplementary key information to be logged. Do not use a wildcard. To issue ADD SCHEMATRANDATA for schemas in more than one pluggable database of a multitenant container database, log in to each pluggable database separately with DBLOGIN and then issue ADD SCHEMATRANDATA. See DBLOGIN for more information.

ALLOWNONVALIDATEDKEYS

This option is valid for Oracle 11.2.0.4 and later 11g versions and Oracle 12.1.0.2 and later 12c versions. (Not valid for Oracle 11.2.0.3 or 12.1.0.1.) It includes NON VALIDATED and NOT VALID primary keys in the supplemental logging. These keys override the normal key selection criteria that is used by Oracle GoldenGate. If the GLOBALS parameter ALLOWNONVALIDATEDKEYS is being used, ADD SCHEMATRANDATA runs with ALLOWNONVALIDATEDKEYS whether or not it is specified. By default NON VALIDATED and NOT VALID primary keys are not logged. For more information, see the GLOBALS ALLOWNONVALIDATEDKEYS parameter.

NOSCHEDULINGCOLS | ALLCOLS

These options control supplemental logging for an Oracle target database. You can use these options together though the latter option is used. For example, with the ADD SCHEMATRANDATA oggadm_ext ALLCOL NOSCHEDULINGCOLS command the NOSCHEDULINGCOLS option would be used.

NOSCHEDULINGCOLS

Disables the logging of scheduling columns. By default, ADD SCHEMATRANDATA enables the unconditional logging of the primary key and the conditional supplemental logging of all unique key(s) and foreign key(s) of all current and future tables in the given schema. Unconditional logging forces the primary key values to the log whether or not the key was changed in the current operation. Conditional logging logs all of the column values of a foreign or unique key if at least one of them was changed in the current operation. The integrated Replicat primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies. For more information about integrated Replicat, see Installing and Configuring Oracle GoldenGate for Oracle Database.

ALLCOLS

Enables the unconditional supplemental logging of all supported key and non-key columns for all current and future tables in the given schema. This option enables the logging of the keys required to compute dependencies, plus columns that are required for filtering, conflict resolution, or other purposes. Columns like LOB, LONG, and ADT are not included.

Example

Example 1   

The following enables supplemental logging for the schema scott.

ADD SCHEMATRANDATA scott
Example 2   

The following example logs all supported key and non-key columns for all current and future tables in the schema named scott.

ADD SCHEMATRANDATA scott ALLCOLS