Oracle CDC Client

Oracle Standard databases

Use the following steps to determine the status of the database and to configure the database archiving mode:

1. In a SQL shell, log into the database as a user with DBA privileges.
2. Check the database logging mode.

   For example:

   select log_mode from v$database;

   If the command returns ARCHIVELOG, you can go to "Task 2. Enable Supplemental Logging".

   If the command returns NOARCHIVELOG, continue with the following steps.

3. Shut down the database.

   For example:

   shutdown immediate;

4. Start up and mount the database.

   For example:

   startup mount;

5. Enable archiving, open the database, and make it writable.

   For example:

   alter database archivelog;
   alter database open read write;

Oracle RAC and Exadata databases

Use the following steps to configure the database archiving mode and verify the status of the database:

1. In a Terminal session on one of the database nodes, stop and mount the database.

   For example:

   srvctl stop database -d <database name>
   srvctl start database -d <database name> -o mount

2. In a SQL shell, log into the database as a user with DBA privileges and configure the database.

   For example:

   alter database archivelog;

3. In the Terminal session, restart the database.

   For example:

   srvctl stop database -d <database name>
   srvctl start database -d <database name>

4. In the SQL shell, check if the database is in logging mode.

   For example:

   select log_mode from v$database;

1. Determine the logging that you want to enable.
2. Before enabling supplemental logging, check if it is enabled for the database.

   For example, you might run the following command on your CDB, then the pluggable database:

   select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all from v$database;

   If the command returns Yes or Implicit for all three columns, then minimal supplemental logging is enabled, as well as identification key and full supplemental logging. You can skip to "Task 3. Create a User Account."

   If the command returns Yes or Implicit for the first two columns, then supplemental and identification key logging are both enabled. If this is what you want, you can skip to "Task 3. Create a User Account."

3. Enable minimal supplemental logging.
   For example, you can run the following command from your CDB or from your non-CDB database:

   alter database add supplemental log data;

4. Optionally, enable identification key logging, if needed.

   When enabling identification key logging for a database, do so for the database that contains the schemas to be monitored.

   To enable identification key logging for a pluggable database, you must also enable it for the CDB.

   When enabling identification key logging for individual tables, you don’t need to enable it for the CDB. According to Oracle, best practice is to enable logging for individual tables, rather than the entire CDB. For more information, see the Oracle LogMiner documentation.

   For example, you can run the following command to apply the changes to just the container:

   alter session set container=<pdb>;

   Enable primary key or full supplemental identification key logging to retrieve data from redo logs. You do not need to enable both:

   To enable primary key logging

   You can enable primary key identification key logging for individual tables or all tables in the database:

   • For individual tables

     For example, you might run the following commands to enable minimal supplemental logging for a pluggable database, and then enable primary key logging for each table that you want to use:

     alter table <schema name>.<table name> add supplemental log data (primary key) columns;

   • For all tables

     For example, you might run the following commands to enable primary key logging for an entire pluggable database:

     alter database add supplemental log data (primary key) columns;
     alter database add supplemental log data (primary key);

   To enable full supplemental logging

   You can enable full supplemental identification key logging for individual tables or all tables in the database:

   • For individual tables

     For example, you might run the following commands to enable minimal supplemental logging for a pluggable database, and then enable full supplemental logging for each table that you want to use:

     alter table <schema name>.<table name> add supplemental log data (all) columns;

   • For all tables

     For example, you might run the following command to enable full supplemental logging for an entire pluggable database:

     alter database add supplemental log data (primary key) columns;
     alter database add supplemental log data (all) columns;

5. Submit the changes.

   For example:

   alter system archive log current;

12c, 18c, 19c, or 21c CDB databases

For Oracle, Oracle RAC, and Oracle Exadata 12c, 18c, 19c, and 21c CDB databases, create a common user account. Common user accounts are created in cdb$root and must use the convention: c##<name>.

1. In a SQL shell, log into the database as a user with DBA privileges.
2. Create the common user account.

   For example:

   alter session set container=cdb$root;
   
   create user <user name> identified by <password>;
   grant create session, alter session, set container, logmining, select any dictionary, select_catalog_role, execute_catalog_role to <user name> container=all;
   
   grant select on database_properties to <user name>;
   grant select on all_objects to <user name>;
   grant select on all_tables to <user name>;
   grant select on all_tab_columns to <user name>;
   grant select on all_constraints to <user name>;
   grant select on all_cons_columns to <user name>;
   
   grant select on v_$database to <user name>;
   grant select on gv_$database to <user name>;
   grant select on v_$instance to <user name>;
   grant select on gv_$instance to <user name>;
   grant select on v_$database_incarnation to <user name>;
   grant select on gv_$database_incarnation to <user name>;
   
   grant select on v_$log to <user name>;
   grant select on gv_$log to <user name>;
   grant select on v_$logfile to <user name>;
   grant select on gv_$logfile to <user name>;
   grant select on v_$archived_log to <user name>;
   grant select on gv_$archived_log to <user name>;
   grant select on v_$standby_log to <user name>;
   grant select on gv_$standby_log to <user name>;
   
   grant select on v_$logmnr_parameters to <user name>;
   grant select on gv_$logmnr_parameters to <user name>;
   grant select on v_$logmnr_logs to <user name>;
   grant select on gv_$logmnr_logs to <user name>;
   grant select on v_$logmnr_contents to <user name>;
   grant select on gv_$logmnr_contents to <user name>;
   
   alter session set container=<pdb>;
   
   grant select <db>.<table> to <user name>; 

   Repeat the final command for each table that you want to monitor.

When you configure the origin, use this user account for the JDBC credentials. Use the entire user name, including the c##, as the JDBC user name.

12c, 18c, 19c, or 21c non-CDB databases

For Oracle, Oracle RAC, and Oracle Exadata 12c, 18c, 19c, and 21c non-CDB databases, create a user account with the necessary privileges:

1. In a SQL shell, log into the database as a user with DBA privileges.
2. Create the user account.

   For example:

   create user <user name> identified by <password>;
   grant create session, alter session, set container, logmining, select any dictionary, select_catalog_role, execute_catalog_role to <user name>;
   
   grant select on database_properties to <user name>;
   grant select on all_objects to <user name>;
   grant select on all_tables to <user name>;
   grant select on all_tab_columns to <user name>;
   grant select on all_constraints to <user name>;
   grant select on all_cons_columns to <user name>;
   
   grant select on v_$database to <user name>;
   grant select on gv_$database to <user name>;
   grant select on v_$instance to <user name>;
   grant select on gv_$instance to <user name>;
   grant select on v_$database_incarnation to <user name>;
   grant select on gv_$database_incarnation to <user name>;
   
   grant select on v_$log to <user name>;
   grant select on gv_$log to <user name>;
   grant select on v_$logfile to <user name>;
   grant select on gv_$logfile to <user name>;
   grant select on v_$archived_log to <user name>;
   grant select on gv_$archived_log to <user name>;
   grant select on v_$standby_log to <user name>;
   grant select on gv_$standby_log to <user name>;
   
   grant select on v_$logmnr_parameters to <user name>;
   grant select on gv_$logmnr_parameters to <user name>;
   grant select on v_$logmnr_logs to <user name>;
   grant select on gv_$logmnr_logs to <user name>;
   grant select on v_$logmnr_contents to <user name>;
   grant select on gv_$logmnr_contents to <user name>;
   
   grant select <db>.<table> to <user name>;

   Repeat the final command for each table that you want to monitor.

When you configure the origin, use this user account for the JDBC credentials.

11g databases

For Oracle 11g databases, create a user account with the necessary privileges:

1. In a SQL shell, log into the database as a user with DBA privileges.
2. Create the user account.

   For example:

   create user <user name> identified by <password>;
   grant create session, alter session, execute_catalog_role, select any transaction, select any table to <user name>;
   grant select on gv_$database to <user name>;
   grant select on gv_$archived_log to <user name>;
   grant select on gv_$instance to <user name>;
   grant select on gv_$log to <user name>; 
   grant select on v_$database to <user name>;
   grant select on v_$database_incarnation to <user name>;
   grant select on v_$logmnr_contents to <user name>;
   grant select on v_$archived_log to <user name>;
   grant select on v_$instance to <user name>;
   grant select on v_$log to <user name>;
   grant select on v_$logfile to <user name>;
   grant select on v_$logmnr_logs to <user name>;
   grant select on <db>.<table> to <user name>;

   Repeat the final command for each table that you want to mine.

When you configure the origin, use this user account for the JDBC credentials.

alter session set container=cdb$root;
execute dbms_logmnr_d.build(options=> dbms_logmnr_d.store_in_redo_logs);
alter system archive log current;

execute dbms_logmnr_d.build(options=> dbms_logmnr_d.store_in_redo_logs);
alter system archive log current;

• Schema: sales
• Table Name Pattern: SALES%
• Exclusion Pattern: SALES.*-.

In memory

When buffering in memory, the origin buffers the LogMiner redo SQL statements returned by Oracle. It processes the data after receiving a commit for the statement.

Before buffering in memory, increase the Data Collector Java heap size to accommodate the information that you expect the pipeline to receive.

Use the following equation to estimate the Java heap size requirements:

Required Memory = (L * (30 + S) * T * 1.5) bytes

• L - Maximum number of statements LogMiner generates for each transaction.

  Each changed row generates one statement with all field names and values.

• S - Maximum length in characters of each redo SQL statement generated by LogMiner, including all field names and values.
  For example, the following SQL statement contains 92 characters, including spaces and punctuation.

  insert into "SYS"."Y"("ID","D") values ('690054',TO_TIMESTAMP('2017-07-18 02:00:00.389390'))

• T - Maximum number of transactions in flight for the tables at any given time.

30 represents the number of bytes in the statement ID, which is also stored.

To disk

When buffering to disk, the origin stores only the statement ID for each SQL query in memory. Then it saves the queries to disk.

By default the origin buffers data to the directory specified by the system property, java.io.tmpdir. You can configure the origin to buffer data to an existing directory on the Data Collector machine. The user who starts the pipeline must have read and write access to the directory.

Note that when buffering to disk, Data Collector clears the buffered data when the pipeline starts and stops. When you restart a pipeline, Data Collector uses the last-saved offset unless you reset the origin.

Before buffering to disk, ensure that enough space is available on the local disk. You might also increase the Data Collector Java heap size, but to a lesser degree than when buffering fully in memory.

Use the following calculation to determine the amount of disk space that the pipeline requires:

Required Disk Space = (L * S * T * 1.5) bytes

• L - Maximum number of statements LogMiner generates for each transaction.

  Each changed row generates one statement with all field names and values.

• S - Maximum length in characters of each redo SQL statement generated by LogMiner, including all field names and values.
  For example, the following SQL statement contains 92 characters, including spaces and punctuation.

  insert into "SYS"."Y"("ID","D") values ('690054',TO_TIMESTAMP('2017-07-18 02:00:00.389390'))

• T - Maximum number of transactions in flight for the tables at any given time.

Use the following equation to estimate the Java heap size required by the pipeline:

Required Memory = (L * T * 30 * 1.5) bytes

• L - Maximum number of statements LogMiner generates for each transaction.

  Each changed row generates one statement with all field names and values.

• T: Maximum number of transactions in flight for the tables at any given time.

• You can configure advanced properties to enable the origin to process Oracle Blob and Clob data. The origin converts Blob data to the Data Collector Byte Array data type, and Clob data to the Data Collector String data type.
• The origin treats Oracle Raw data as a Data Collector Byte Array data type.
• The origin converts Oracle Timestamp with Timezone data to the Data Collector Zoned Datetime data type. To maximize efficiency while providing more precision, the origin includes only the UTC offset with the data. It omits the time zone ID.

sdc.operation.type

The origin evaluates the Oplog operation type associated with each entry that it processes. When appropriate, it writes the operation type to the sdc.operation.type record header attribute.

The origin uses the following values in the attribute to represent the operation type:

• 1 for INSERT
• 2 for DELETE
• 3 for UPDATE and SELECT_FOR_UPDATE

If you use a CRUD-enabled destination in the pipeline such as JDBC Producer or Elasticsearch, the destination can use the operation type when writing to destination systems. When necessary, you can use an Expression Evaluator processor or any scripting processor to manipulate the value in the header attribute. For an overview of Data Collector changed data processing and a list of CRUD-enabled destinations, see Processing Changed Data.

When using CRUD-enabled destinations, the destination looks for the operation type in the sdc.operation.type attribute before checking the oracle.cdc.operation attribute.

oracle.cdc.operation

The origin also writes the CRUD operation type to an oracle.cdc.operation attribute. This attribute was implemented in an earlier release and is supported for backward compatibility.

The origin uses the following values in the attribute to represent the operation type:

• INSERT
• UPDATE
• SELECT_FOR_ UPDATE
• DELETE

CRUD-enabled destinations check this attribute for the operation type only if the sdc.operation.type attribute is not set.

• oracle.cdc.sequence.oracle - Includes sequence numbers that indicate the order in which statements were processed within the transaction. The sequence number is generated by Oracle. Use to keep statements in order within a single transaction.
  Note: When the origin uses local buffering, Oracle provides the default value of 1 for all records, so should not be used to order statements. Use the oracle.cdc.sequence.internal attribute when using local buffering.
• oracle.cdc.sequence.internal - Includes sequence numbers equivalent to those in the attribute above, but created by the origin. The sequence starts with 0 each time the pipeline starts.

  Like the attribute above, you can use these values to keep statements in order within a single transaction.

  Best practice is to use this attribute only when using local buffering, when the oracle.cdc.sequence.oracle attribute does not provide the expected data.

Primary keys

When a table contains a primary key, the origin includes the following record header attribute:

• jdbc.primaryKeySpecification - Provides a JSON-formatted string that lists the columns that form the primary key in the table and the metadata for those columns.
  For example, a table with a composite primary key contains the following attribute:

  jdbc.primaryKeySpecification = 
       {{"<primary key column 1 name>":
           {"type": <type>, 
            "datatype": "<data type>", 
            "size": <size>, 
            "precision": <precision>, 
            "scale": <scale>, 
            "signed": <Boolean>,  
            "currency": <Boolean> }},
            ...,
       {"<primary key column N name>":
           {"type": <type>, 
            "datatype": "<data type>", 
            "size": <size>, 
            "precision": <precision>, 
            "scale": <scale>, 
            "signed": <Boolean>,  
            "currency": <Boolean> } } }

  A table without a primary key contains the attribute with an empty value:

  jdbc.primaryKeySpecification = {} 

For an update operation on a table with a primary key, the origin includes the following record header attributes:

• jdbc.primaryKey.before.<primary key column name> - Provides the old value for the specified primary key column.
• jdbc.primaryKey.after.<primary key column name> - Provides the new value for the specified primary key column.

Note: The origin provides the new and old values of the primary key columns regardless of whether the value changes.

Decimal fields

When a record includes decimal fields, the origin includes the following record header attributes for each decimal field in the record:

• jdbc.<fieldname>.precision
• jdbc.<fieldname>.scale

• The Oracle Number data type is converted to the Data Collector Decimal data type, which does not store scale and precision.
• The Oracle Timestamp data type is converted to the Data Collector Datetime data type, which does not store nanoseconds.

• Active - Pipeline is running and actively processing changes.
• Paused - Pipeline is running but paused during a time change window. The state lists the remaining paused time.