Supplemental Logging Options Per Use Case

https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/setup.htm#GIORA366

Logging option	GGSCI command	What it does	Use case
Forced logging mode	None; enable through the database.	Forces the logging of all transactions and loads.	Strongly recommended for all Oracle GoldenGate use cases.
Minimum database-level supplemental logging	None; enable through the database.	Enables minimal supplemental logging to add row-chaining information to the redo log.	Required for all Oracle GoldenGate use cases
Schema-level supplemental logging, default setting See Section 3.2.2, "Enabling Schema-level Supplemental Logging".	ADD SCHEMATRANDATA	Enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of all tables in a schema. All of these keys together are known as thescheduling columns.	Enables the logging for all current and future tables in the schema. If the primary key, unique key, and foreign key columns are not identical at both source and target, use ALLCOLS. Required when using DDL support.
Schema-level supplemental logging with unconditional logging for all supported columns. (SeeSection 3.2.2, "Enabling Schema-level Supplemental Logging" for non-supported column types.)	ADD SCHEMATRANDATA withALLCOLS option	Enables unconditional supplemental logging of all of the columns in a table, for all of the tables in a schema.	Use for integrated Replicat when parallelism is greater than 1 and the source and target have different scheduling columns.
Schema-level supplemental logging, minimal setting	ADD SCHEMATRANDATA withNOSCHEDULINGCOLS option	Enables unconditional supplemental logging of the primary key and all valid unique indexes of all tables in a schema.	Use only for nonintegrated Replicat. This is the minimum required schema-level logging.
Table-level supplemental logging with built-in support for integrated Replicat See Section 3.2.3, "Enabling Table-level Supplemental Logging"	ADD TRANDATA	Enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of a table. All of these keys together are known as the scheduling columns.	Required for all Oracle GoldenGate use cases unless schema-level supplemental logging is used. If the primary key, unique key, and foreign key columns are not identical at both source and target, use ALLCOLS.
Table-level supplemental logging with unconditional logging for all supported columns. (See Section 3.2.3, "Enabling Table-level Supplemental Logging" for non-supported column types.)	ADD TRANDATA with ALLCOLSoption	Enables unconditional supplemental logging of all of the columns of the table.	Use for integrated Replicat when parallelism is greater than 1 and the source and target have different scheduling columns.
Table-level supplemental logging, minimal setting	ADD TRANDATA withNOSCHEDULINGCOLS option	Enables unconditional supplemental logging of the primary key and all valid unique indexes of a table.	Use only for nonintegrated Replicat. This is the minimum required table-level logging.

Enabling Minimum Database-level Supplemental Logging
Oracle strongly recommends putting the Oracle source database into forced logging mode. Forced logging mode forces the logging of all transactions and loads, overriding any user or storage settings to the contrary. This ensures that no source data in the Extract configuration gets missed.
In addition, minimal supplemental logging, a database-level option, is required for an Oracle source database when using Oracle GoldenGate. This adds row chaining information, if any exists, to the redo log for update operations.

Note:

Database-level primary key (PK) and unique index (UI) logging is strongly discouraged because of the excessive additional overhead it creates on tables outside of replication. Unless those logging options are required for business purposes, you only need to enable minimal supplemental logging at the database level and force logging for Oracle GoldenGate.

Perform the following steps to verify and enable, if necessary, minimal supplemental logging and forced logging.

1. Log in to SQL*Plus as a user with ALTER SYSTEM privilege.
2. Issue the following command to determine whether the database is in supplemental logging mode and in forced logging mode. If the result is YES for both queries, the database meets the Oracle GoldenGate requirement.
   

   SELECT supplemental_log_data_min, force_logging FROM v$database;
   

3. If the result is NO for either or both properties, continue with these steps to enable them as needed:
   

   SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   SQL> ALTER DATABASE FORCE LOGGING;
   

4. Issue the following command to verify that these properties are now enabled.
   

   SELECT supplemental_log_data_min, force_logging FROM v$database;
   

   The output of the query must be YES for both properties.
5. Switch the log files.
   

   SQL> ALTER SYSTEM SWITCH LOGFILE;
   

3.2.2 Enabling Schema-level Supplemental Logging

Oracle GoldenGate supports schema-level supplemental logging. Schema-level logging is required for an Oracle source database when using the Oracle GoldenGate DDL replication feature. In all other use cases, it is optional, but then you must use table-level logging instead (see Section 3.2.3, "Enabling Table-level Supplemental Logging").
By default, schema-level logging automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of all tables in a schema. Options enable you to alter the logging as needed.

Note:

Oracle strongly recommends using schema-level logging rather than table-level logging, because it ensures that any new tables added to a schema are captured if they satisfy wildcard specifications.

Perform the following steps on the source system to enable schema-level supplemental logging.

1. Apply Oracle Patch 13794550 to the source Oracle database if the version is earlier than 11.2.0.2.
2. Run GGSCI on the source system.
3. Issue the DBLOGIN command with the alias of a user in the credential store who has privilege to enable schema-level supplemental logging.
   

   DBLOGIN USERIDALIAS alias
   

   See Reference for Oracle GoldenGate for Windows and UNIX for more information about DBLOGIN and additional options.
4. Issue the ADD SCHEMATRANDATA command for each schema for which you want to capture data changes with Oracle GoldenGate.
   

   ADD SCHEMATRANDATA schema [ALLCOLS | NOSCHEDULINGCOLS]
   

   Where:
   
   • Without options, ADD SCHEMATRANDATA schema enables the unconditional supplemental logging on the source system 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 default is optional to support nonintegrated Replicat but is required to support integrated Replicat because 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 Section 5.3, "Deciding Which Apply Method to Use".
   • ALLCOLS can be used to enable the unconditional supplemental logging of all of the columns of a table and applies to all current and future tables in the given schema. Use to support integrated Replicat when the source and target tables have different scheduling columns. (Scheduling columns are the primary key, the unique key, and the foreign key.)
   • NOSCHEDULINGCOLS logs only the values of the primary key and all valid unique indexes for existing tables in the schema and new tables added later. This is the minimal required level of schema-level logging and is valid only for Replicat in nonintegrated mode.
   In the following example, the command enables default supplemental logging for the finance schema.
   

   ADD SCHEMATRANDATA finance
   

   In the following example, the command enables the supplemental logging only for the primary key and valid unique indexes for the hr schema.
   

   ADD SCHEMATRANDATA hr NOSCHEDULINGCOLS
   

   See Reference for Oracle GoldenGate for Windows and UNIX for more information about ADD SCHEMATRANDATA.

3.2.3 Enabling Table-level Supplemental Logging

Enable table-level supplemental logging on the source system in the following cases:

• To enable the required level of logging when not using schema-level logging (see Section 3.2.2, "Enabling Schema-level Supplemental Logging"). Either schema-level or table-level logging must be used. By default, table-level logging automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of a table. Options enable you to alter the logging as needed.
• To prevent the logging of the primary key for any given table.
• To log non-key column values at the table level to support specific Oracle GoldenGate features such as filtering.

Perform the following steps on the source system to enable table-level supplemental logging or use the optional features of the command.

1. Run GGSCI on the source system.
2. Issue the DBLOGIN command using the alias of a user in the credential store who has privilege to enable table-level supplemental logging.
   

   DBLOGIN USERIDALIAS alias
   

   See Reference for Oracle GoldenGate for Windows and UNIX for more information about DBLOGIN and additional options.
3. Issue the ADD TRANDATA command.
   

   ADD TRANDATA [container.]schema.table [, COLS (columns)] [, NOKEY] [, ALLCOLS | NOSCHEDULINGCOLS]
   

   Where:
   
   • container is the name of the root container or pluggable database if the table is in a multitenant container database.
   • schema is the source schema that contains the table.
   • table is the name of the table. See Administering Oracle GoldenGate for Windows and UNIX for instructions for specifying object names.
   • ADD TRANDATA without other options automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of the table. 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 default is optional to support nonintegrated Replicat (see also NOSCHEDULINGCOLS) but is required to support integrated Replicat because 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 Section 5.3, "Deciding Which Apply Method to Use".
   • ALLCOLS enables the unconditional supplemental logging of all of the columns of the table. Use to support integrated Replicat when the source and target tables have different scheduling columns. (Scheduling columns are the primary key, the unique key, and the foreign key.)
   • NOSCHEDULINGCOLS is valid for Replicat in nonintegrated mode only. It issues an ALTER TABLE command with an ADD SUPPLEMENTAL LOG DATA ALWAYS clause that is appropriate for the type of unique constraint that is defined for the table, or all columns in the absence of a unique constraint. This command satisfies the basic table-level logging requirements of Oracle GoldenGate when schema-level logging will not be used. See Section 10.1, "Ensuring Row Uniqueness in Source and Target Tables" for how Oracle GoldenGate selects a key or index.
   • COLS columns logs non-key columns that are required for a KEYCOLS clause or for filtering and manipulation. The parentheses are required. These columns will be logged in addition to the primary key unless the NOKEY option is also present.
   • NOKEY prevents the logging of the primary key or unique key. Requires a KEYCOLS clause in the TABLE and MAP parameters and a COLS clause in the ADD TRANDATA command to log the alternate KEYCOLS columns.
4. If using ADD TRANDATA with the COLS option, create a unique index for those columns on the target to optimize row retrieval. If you are logging those columns as a substitute key for a KEYCOLS clause, make a note to add the KEYCOLS clause to the TABLE and MAP statements when you configure the Oracle GoldenGate processes.