Oracle

Hevo supports the following variations of Oracle as a Source:

Click each variant above to know the prerequisites and pre-configuration setups specific to it. Once you complete the setups, follow the steps mentioned below to configure it as a Source in your Hevo Pipeline and start ingesting data.


Select the Source Type

To select Oracle as the Source:

  1. Click PIPELINES in the Asset Palette.

  2. Click + CREATE in the Pipelines List View.

  3. In the Select Source Type page, select the Oracle variant.


Specify Oracle Connection Settings

In the Configure your Oracle Source page, specify the following:

Oracle Settings

  • Pipeline Name: A unique name for your Pipeline.

  • Database Host: The Oracle database host’s IP address or DNS.

    The following table lists few examples of Oracle hosts:

    Variant Host
    Amazon RDS Oracle oracle-rds-1.xxxxx.rds.amazonaws.com
    Generic Oracle 192.168.2.5

    Note: For URL-based hostnames, exclude the http:// or https:// part. For example, if the hostname URL is https://oracle-rds-1.xxxxx.rds.amazonaws.com, enter oracle-rds-1.xxxxx.rds.amazonaws.com.

  • Database Port: The port on which your Oracle server is listening for connections. Default value: 1521.

  • Database User: The authenticated user who has the permissions to read tables in your database.

  • Database Password: The password for the database user.

  • Select an Ingestion Mode: The desired mode by which you want to ingest data from the Source. The available Ingestion Modes are Binlog, Table, and Custom SQL. Read Ingestion Modes.

    For Ingestion mode as RedoLog or Table, you can configure the objects to be replicated. Refer to section, Objects and Query mode.

    For Pipelines created after Release 1.96, Hevo supports the RedoLog ingestion mode for Oracle Database 19c and higher.

  • Service Name: An alias of the unique Oracle database to which Hevo connects. To retrieve the Service Name, open your Oracle server in any SQL client tool as a database user with SYSDBA privilege and enter the following command:

    select name from v$database;
    
  • Schema Settings

    • Owner: The name of the schema owner to identify the schemas for ingesting the data. Data of all the schemas defined by the specified owner are ingested for replication. This is required if Ingestion mode is Table or Custom SQL.

    • Load All Schema: Select this toggle option to load data for all the schemas. This is applicable when Ingestion mode is Redo Log.

  • Connection Settings

    • Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel, instead of directly connecting your Oracle database host to Hevo. This provides an additional level of security to your database by not exposing your Oracle setup to the public. Read Connecting Through SSH.

    If this option is disabled, you must whitelist Hevo’s IP addresses to allow Hevo to connect to your Oracle database host. Refer to the content for your Oracle variant for steps to do this.

  • Advanced Settings:

    • Load Historical Data: Applicable for Pipelines with RedoLog mode. If this option is enabled, the entire table data is fetched during the first run of the Pipeline. If disabled, Hevo loads only the data that was written in your database after the time of creation of the Pipeline.

    • Merge Tables: Applicable for Pipelines with RedoLog mode. If this option is enabled, Hevo merges tables with the same name from different databases while loading the data to the warehouse. Hevo loads the Database Name field with each record. If disabled, the database name is prefixed to each table name. Read How does the Merge Tables feature work?.

    • Include New Tables in the Pipeline: Applicable for all Ingestion modes except Custom SQL. If enabled, Hevo automatically ingests data from tables created after the Pipeline has been built. If disabled, the new tables are listed in the Pipeline Detailed View in Skipped state, and you can manually include the ones you want and load their historical data.

      You can change this setting later.

Click TEST & CONTINUE to proceed for setting up the Destination.


Objects and Query Mode

Do one of the following:

  • For Pipeline with Table or RedoLogs mode:

    1. Once your respective Source connection settings are specified in Step 2 above, select the objects to be replicated in the Select the Objects you want to replicate page, and then click CONTINUE.

      Object Selection

      Note: Each object represents a table in your database.

    2. In the CONFIGURE SOURCE OBJECTS page, specify the query mode to be used for each selected object.

  • For Pipeline with Custom SQL mode:

    1. In the Provide Query Settings page, enter the custom SQL query to fetch data from the Source.

      Custom SQL

    2. In the Query Mode drop-down, select the query mode.

Continue to complete the subsequent steps and configure the Destination system.


Limitations

None.



See Also



Revision History

Refer to the following table for the list of key updates made to this page:

Date Release Description of Change
Aug-24-2022 1.96 Updated section, Limitations to remove limitation regarding Hevo not supporting RedoLog Ingestion mode for Oracle Database 19c or higher.
Apr-21-2022 1.86 - Updated sections, Specify Oracle Connection Settings and Objects and Query Mode.
- Removed section, Select the Pipeline Mode.
Dec-06-2021 1.77 Added a See Also link to the Pipeline failure due to Redo Log expiry page.
Nov-26-2021 NA Added section, Limitations
Jul-26-2021 1.68 Added a note for the Database Host field.
Jul-12-2021 1.67 Added the field Include New Tables in the Pipeline under Source configuration settings.
Last updated on 24 Aug 2022

Tell us what went wrong