1. Command Line Interface Reference for Oracle GoldenGate
  2. GGSCI Command Line Interface Commands
  3. ADD SCHEMATRANDATA

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.

To perform ADD SCHEMATRANDATA against a schema in the PDB of a multitenant database, you need to login to PDB to issue the command.

ADD SCHEMATRANDATA is valid for both integrated and classic Extract 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.

  • Enables a table for auto-capture. The command add schema-level PK, UI, FK, ALLKEYS supplemental logging data.

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.

Use ADD SCHEMATRANDATA 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.

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. 

    exec dbms_streams_auth.grant_admin_privilege('GG-AdminUser')

  • 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 adding SCHEMATRANDATA on a schema in a PDB, you need to be logged into that PDB in DBLOGIN. For example, for PDBEAST and schema HR, use the following command:

    ADD SCHEMATRANDATA pdbeast.hr

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

GGSCI Syntax

ADD SCHEMATRANDATA schema {
[ALLOWNONVALIDATEDKEYS] 
[NOSCHEDULINGCOLS | ALLCOLS]}
[NOVALIDATE]
[PREPARECSN  {WAIT | LOCK | NOWAIT | NONE}]
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. From the root conatiner, you may add schematrandata with the container prefix ADD SCHEMATRANDATA [pdb_name].schema

ALLOWNONVALIDATEDKEYS

This option is 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, 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 keys and foreign keys 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.

If you are enabling auto_capture, then do not use this option. This will allow tables in this schema to be auto captured unless the table is explicitly excluded/disabled for replication (such as through TABLEEXCLUDE, DELETE TRANDATA, or alter table disable logical replication DDL).

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.

NOVALIDATE

Valid for all databases supported by ADD SCHEMATRANDATA.

Suppresses additional information about the table being handled being processed by ADD SCHEMATRANDATA. By default, this option is enabled. The additional information processing creates a lapse time on command response so this option can be used to increase response time.

PREPARECSN {WAIT | LOCK | NOWAIT | NONE}

Valid for Oracle for both DML and DDL. Automatically prepares the tables at the source so the Oracle data pump Export dump file will includes Instantiation CSNs. Replicat uses the per table instantiation CSN set by the Oracle data pump (on import) to filter out trail records. On the target, the data pump import populates the system tables and views with instantiation SCNs using the DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter to enable table-level instantiation filtering.

WAIT

Wait for any in-flight transactions and prepare table instantiation.

LOCK

Put a lock on the table (to prepare for table instantiation).

NOWAIT

Default behavior, preparing for instantiation is done immediately.

NONE

No instantiation preparation occurs.

Example

The following enables supplemental logging for the schema hr. 

ADD SCHEMATRANDATA hr

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

ADD SCHEMATRANDATA hr ALLCOLS

The following example suppress additional table information processing.

ADD SCHEMATRANDATA hr NOVALIDATE