Use ADD SCHEMATRANDATA to enable
schema-level supplemental logging for Oracle tables. 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 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.
ADD SCHEMATRANDATA logs the key columns of a table in the following order of priority:
● Primary key
● 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.
● If none of the preceding exists, all scalar columns
ADD SCHEMATRANDATA 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.
ADD SCHEMATRANDATA logs the key columns of a table in the following order of priority:
● Primary key
● 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.
● If none of the preceding exists, all scalar columns
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 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.
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.
·
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.
·
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
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.
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 available for Oracle 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 support integrated Replicat for an Oracle target database.
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 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.
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
