Oracle CDC LogMiner Connection Adapter

Contents

Introduction
Setting up Oracle LogMiner
Adapter Properties
Ports
Suspend and Resume Behavior
Related Topics

Introduction

The TIBCO StreamBase® Adapter For Oracle CDC LogMiner Connection works with the TIBCO StreamBase® Adapter For Oracle CDC LogMiner Parser adapter to allow a StreamBase application to monitor changes to an Oracle 12c and above databases using the Oracle LogMiner.

The adapters together monitor the database's LogMiner REDO log to detect when records are inserted, updated, or deleted in a database table. The Connection adapter defines the database connection. The Parser adapter is linked to this Connection adapter to define what tables should be monitored; you can link more than one Parser adapter to a Connection adapter to monitor multiple tables from a single connection.

The Connection adapter is configured through a collection of properties set in the adapter's Properties view within StreamBase Studio. Properties specify, among other things, the database host name or IP address and TCP port number, and the database username and password.

Setting up Oracle LogMiner

  1. Ensure your database is set up correctly and has supplemental logging enabled.

    1. Enable LogMiner:

      1. Log onto your database using a user with DBA privileges.

      2. Check the current log level by executing select log_mode from v$database; If the command returns LOG_MODE of ARCHIVELOG then logging is already enabled and you can skip the following steps. If the command returns NOARCHIVELOG then you must enable logging.

      3. To enable logging, first shut down the database using shutdown immediate;.

      4. Then start up and mount the database using startup mount;.

      5. Now configure the database to enable archive log using alter database archivelog; and alter database open;.

    2. Enabled Supplemental Logging using ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

    3. If you require primary key information, you can enable logging for each table with ALTER TABLE 'schema'.'table name' ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; or for the entire database ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    4. You can also enable full logging for each table with ALTER TABLE 'schema'.'table name' ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; or for the entire database ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    5. Submit the changes using ALTER SYSTEM SWITCH LOGFILE;

  2. This sample provides scripts to create a SAMPLE table and perform insert/update/delete tasks on the table. The adapters in the sample are set up for the SAMPLE table; the Oracle CDC LogMiner Connection adapter properties do need to be updated with the correct database information in order to connect.

    The following scripts are provided:

    1. CreateSampleTable.sql — This SQL script creates the SAMPLE table which the adapters are set up to capture CDC information.

    2. InsertSampleData.sql — This SQL script inserts 10 demo records into the SAMPLE table.

    3. UpdateSampleData.sql — This SQL script updates the 10 demo records in the SAMPLE table with new values.

    4. DeleteSampleData.sql — This SQL script deletes the 10 demo records from the SAMPLE table.

  3. Place the JAR file containing the JDBC driver for your database ojdbc8.jar on the StreamBase classpath. One way to accomplish this is to copy the JAR file to $STREAMBASE_HOME/lib/ext.

Adapter Properties

This section describes the properties you can set for this adapter, using the Properties view in StreamBase Studio.

General Tab

Name: Use this required field to specify or change the name of this instance of this component, which must be unique in the current EventFlow module. The name must contain only alphabetic characters, numbers, and underscores, and no hyphens or other special characters. The first character must be alphabetic or an underscore.

Operator: A read-only field that shows the formal name of the operator.

Class name: Shows the fully qualified class name that implements the functionality of this adapter. If you need to reference this class name elsewhere in your application, you can right-click this field and select Copy from the context menu to place the full class name in the system clipboard.

Start with application: If this field is set to Yes (default) or to a module parameter that evaluates to true, this instance of this adapter starts as part of the JVM engine that runs this EventFlow module. If this field is set to No or to a module parameter that evaluates to false, the adapter instance is loaded with the engine, but does not start until you send an sbadmin resume command, or until you start the component with StreamBase Manager.

Enable Error Output Port: Select this check box to add an Error Port to this component. In the EventFlow canvas, the Error Port shows as a red output port, always the last port for the component. See Using Error Ports to learn about Error Ports.

Description: Optionally enter text to briefly describe the component's purpose and function. In the EventFlow canvas, you can see the description by pressing Ctrl while the component's tooltip is displayed.

Operator Properties Tab

Property Description
Enable Control Port If enabled the system shows a control port which you can use to send commands into the operator for things like connecting and disconnecting.
Log Level Controls the level of verbosity the adapter uses to send notifications to the console. This setting can be higher than the containing application's log level. If set lower, the system log level is used. Available values, in increasing order of verbosity, are: OFF, ERROR, WARN, INFO, DEBUG, TRACE.

Connection Tab

Property Description
JDBC URL

Optionally the full JDBC connection URL that overrides the connection information.

If this field has a value, the host, port, and database name are ignored.

Example: jdbc:oracle:thin:{host}:{port}:{databaseName}
Host The host name or IP address of the machine on which the database is running.
Port The TCP port number on which the database is listening.
Database Name The name of the database.
Username The username to use in connecting to the database.
Password The password to use in connecting to the database.
Database PDB If required, the pluggable database name.
Fetch Size The fetch size is effectively the batch size of how many updates must occur before the operator outputs a tuple. A lower number means less latency but higher server CPU usage.
Connect On Startup If enabled, the system tries to connect to the database on startup. You must enable this option if the control port is disabled.

Concurrency Tab

Use the Concurrency tab to specify parallel regions for this instance of this component, or multiplicity options, or both. The Concurrency tab settings are described in Concurrency Options, and dispatch styles are described in Dispatch Styles.

Caution

Concurrency settings are not suitable for every application, and using these settings requires a thorough analysis of your application. For details, see Execution Order and Concurrency, which includes important guidelines for using the concurrency options.

Ports

This section describes the adapter's ports.

Control Port

The control port which can be used to control the adapters runtime behavior

  • Command (String) - Supported values are:

    • Start — Connect to the database and start monitoring tables.

    • Stop — Disconnect from the database and stop monitoring tables.

    StartSCN (Double) — The starting SCN value to monitor in the REDO log. If this value and StartDate are both null then monitoring starts at the current SCN.

    EndSCN (Double) — The end SCN value to monitor in the REDO log. If this value is specified, the monitor only outputs values up to this point and does not continue to monitor forward. To monitor continuously, leave both EndSCN and EndDate null with a Start command.

    StartDate (Timestamp) — The starting date to monitor in the REDO log. If this value and StartSCN are both null, then monitoring starts at the current SCN.

    EndDate (Timestamp) — The end date to monitor in the REDO log. If this value is specified, the monitor only outputs values up to this point and does not continue to monitor forward. To monitor continuously leave both EndSCN and EndDate null with a Start command.

Status Port

The status port outputs various status information tuples to provide give insight into the adapter's state.

  • Status (String) — The name of the status.

    Time (Timestamp) — The date and time the status information was produced.

    Info (List Tuple) — A list of Name-value pairs of details for the status, such as database name or error message.

All status messages that may be produced and the info list they provide:

  • Command Error — An invalid command was given on the control port. Info list contains:

    • Command — The command that was given.

    • Message — A human readable error message.

  • Connected — The adapter successfully connected to the database. Info list contains:

    • DatabaseURL - The database URL used to connect.

  • Ready — The connection was made to the database, monitoring has started for all attached parsers, and all adapters are ready to process CDC information. Info List contains no values

  • Stopped — The connection is stopped and all monitoring is complete.

  • Connect Error — An error occurred while trying to connect to the database. Info list contains:

    • DatabaseURL — The database URL used to connect.

    • Error — The actual error message.

  • Start CDC Error — An error occurred while trying to start the LogMiner. Info list contains:

    • Start Query — The query used to try and start the LogMiner.

    • Data Select Query — The query used to select data from the LogMiner.

    • Error — The actual error message.

  • PDB Switch Error — An error occurred while trying to switch container databases, Info list contains:

    • PDB — The PDB that was tried.

    • Error — The actual error message.

  • Validate Table Warning — A warning while validating that the tables required exist. Info list contains:

    • Table — The schema.table that failed.

    • Error — The actual error message.

  • Query Error — An error occurred while querying for LogMinor data, Info list contains:

    • Error — The actual error message.

  • Query Max Error — The maximum numbers of errors occurred while querying for LogMinor data. This means the query operation will stop until the Start command is issued again and a new connection to the database is made. The error count is reset to zero when a successful query is complete. Info list contains:

    • ErrorCount — The number of errors that occurred.

  • Query Timeout — A timeout occurred while querying for LogMinor data. Info list contains:

    • Error — The actual error message.

  • Stop Error — An error occurred while trying disconnect from the database and stop the LogMiner. Info list contains:

    • Error — The actual error message.

Suspend and Resume Behavior

When suspended, the adapter maintains its connection to the database.

When resumed, nothing changes as the connection is maintained during suspend of the adapter.

Related Topics