Generic Oracle

Last updated on Nov 03, 2023
On This Page

Oracle database is a cross-platform Relational Database Management System (RDBMS) that can run on various hardware across operating systems including Windows Server, Unix, and various distributions of GNU/Linux. It is commonly referred to as Oracle database, OracleDB, or simply Oracle. The database software endorses transaction processing, business intelligence and different analytics applications, and used by both small and large enterprises to store and manage data.

Prerequisites

Perform the following steps to configure your Generic Oracle Source:

Set up Redo Logs for Replication

Note: You must have SYSDBA privileges to perform the following steps for setting up Redo logs.

Connect to your Oracle server in SQL Developer or any SQL client tool and run the following commands:

1. Enable ARCHIVELOG archival mode

Verify that the current archiving mode is ARCHIVELOG:

  SELECT LOG_MODE FROM V$DATABASE

If the archive mode is NOARCHIVELOG, enable it using the following commands:

  SHUTDOWN IMMEDIATE;
  STARTUP MOUNT;
  ALTER DATABASE ARCHIVELOG;
  ALTER DATABASE OPEN;

2. Configure Recovery Manager

Recovery Manager (RMAN) settings determine how long the database holds backlogs and archive logs.

  1. Connect to the RMAN by executing the following commands on the command prompt:

     RMAN

 CONNECT TARGET <username>` -- (to connect to your database)

   SHOW ALL; -- (to check the default settings)

  2. Configure the log retention policy to 7 days:

     CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

3. Enable supplemental logging

Supplemental logging ensures that columns are logged in Redo log files which are required by LogMiner to read the activity history for a database.

  • Enable supplemental logging at a database level:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  • Enable supplemental logging at a table level:

    ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

4. Set PGA_AGGREGATE_LIMIT (Optional)

Depending on your database workload, you can set the pga_aggregate_limit parameter to prevent out-of-memory errors in your Pipeline. To do this, enter the following command:

ALTER SYSTEM SET pga_aggregate_limit = <new value> SCOPE=BOTH;

Note:

  • Replace the placeholder value in the command above with your own. For example, <new value> with 1G.

  • You can set the above value in one of the following units of measurement:

    • K represents kilobytes.

    • M represents megabytes.

    • G represents gigabytes.

5. Grant permissions to run LogMiner

LogMiner is used to read the archived Redo logs.

Run the following commands to grant the required permissions to it:

 GRANT SELECT ON SYS.V_$DATABASE TO <username>;
 GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <username>;
 GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <username>;
 GRANT EXECUTE ON DBMS_LOGMNR TO <username>;
 GRANT EXECUTE ON DBMS_LOGMNR_D TO <username>;
 GRANT SELECT ANY TRANSACTION TO <username>;
 GRANT EXECUTE_CATALOG_ROLE TO <username>;

If you are using Oracle database version 12, grant the following additional permission:

  GRANT LOGMINING TO <username>

Create a Database User and Grant Privileges

1. Create a database user (optional)

In order to create a database user, you must have the CREATE privilege.

To create a user, open your Oracle server in SQL Developer or any SQL client tool and enter the following command:

CREATE USER hevo IDENTIFIED BY <password>;
GRANT CREATE SESSION TO hevo; --permission needed to connect to the database

2. Grant privileges to the User

The database user specified in the Hevo Pipeline must have the SELECT privileges.

To assign this privilege, open your Oracle server in SQL Developer or any SQL client tool and enter the following commands:

  1. Check current privileges assigned to the user:

    select * from DBA_TAB_PRIVS where grantee = 'username';

  2. Grant access to all_views including system tables:

    GRANT SELECT ON all_views to <username>;

  3. Grant access to schemas of selected tables:

    GRANT SELECT ON <schema_name>.<table_name> to <username>;

Retrieve the Service Name

Service name represents 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;

Connecting to a Local Database

Refer to this section for steps to connect to your local database. For detailed information and troubleshooting help, read Connecting to a Local Database.

Prerequisites

  • Oracle service is running on your local machine.

  • Data to be loaded to the Destination is available in your Oracle database.

  • You have an account on ngrok and an installed ngrok utility on your local machine. To run ngrok on your local machine, follow these one-time steps:

    1. Extract the ngrok utility:

      • On Linux or MacOS, unzip ngrok from a terminal:

        unzip /path/to/ngrok.zip

      • On Windows, double-click ngrok.zip to extract it.

    2. Authenticate ngrok in your local machine:

      ./ngrok authtoken <your_auth_token>

      You can get the auth token from your ngrok dashboard. For example, in the image below, the auth_token starts with 1t7uGaJshUvnh2Fg1MApl.

      Auth token

Connect to the local database

Perform the following steps to connect to the local database:

  1. Log in to your database server.

  2. Start a TCP tunnel forwarding to your database port.

    ./ngrok tcp <your_database_port>

    For example, the port address for Oracle is 3306. Therefore, the command would be:

    ./ngrok tcp 3306

  3. Copy the public IP address (hostname and port number) for your local database and port. For example, in the image below, 8.tcp.ngrok.io is the database hostname and 19789 is the port number.

    Getting hostname and port

  4. Paste the hostname and port number into the Database Host and Database Port fields respectively.

    MySQL settings

  5. Specify all other settings and click TEST & CONTINUE.

Specify Oracle Connection Settings

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

    Oracle Settings

    • Pipeline Name: A unique name for your Pipeline, not exceeding 255 characters.

    • 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 listens 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. You can expand this section by clicking SEE MORE to view the list of ingestion modes to choose from. Default value: RedoLog. The available ingestion modes are RedoLog, Table, and Custom SQL.

      Redo Log

      Depending on the ingestion mode you select, you must configure the objects to be replicated. Refer to section, Object and Query Mode Settings for the steps to do this.

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

      Note: For Custom SQL ingestion mode, all Events loaded to the Destination are billable.

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

    • 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 include these objects post-Pipeline creation to ingest data.

        You can change this setting later.

  2. Click TEST CONNECTION. This button is enabled once you specify all the mandatory fields. Hevo’s underlying connectivity checker validates the connection settings you provide.

  3. Click TEST & CONTINUE to proceed for setting up the Destination. This button is enabled once you specify all the mandatory fields.

Object and Query Mode Settings

Once you have specified the Source connection settings in Step 4 above, do one of the following:

  • For Pipelines with Table or RedoLog mode:

    1. In the Select Objects page, select the objects you want to replicate and click CONTINUE.

      Object Selection

      Note: Each object represents a table in your database.

    2. In the Configure Objects page, specify the query mode you want to use for each selected object.

      Query-Mode Selection

  • For Pipelines with Custom SQL mode:

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

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

      Custom SQL

Additional Information

Read the detailed Hevo documentation for the following related topics:

Limitations

  • Redo Log does not support user-defined data types. Therefore, fields with such data types are not captured in the log and are lost.


See Also

Revision History

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

Date Release Description of Change
Nov-03-2023 NA Added section, Object and Query Mode Settings.
Oct-09-2023 NA Added section, Set PGA_AGGREGATE_LIMIT.
Apr-21-2023 NA Updated section, Specify Oracle Connection Settings to add a note to inform users that all loaded Events are billable for Custom SQL mode-based Pipelines.
Mar-09-2023 2.09 Updated section, Specify Oracle Connection Settings to mention about SEE MORE in the Select an Ingestion Mode section.
Dec-19-2022 2.04 Updated section, Specify Oracle Connection Settings to add information that you must specify all fields to create a Pipeline.
Dec-07-2022 2.03 Updated section, Specify Oracle Connection Settings to mention about including skipped objects post-Pipeline creation.
Dec-07-2022 2.03 Updated section, Specify Oracle Connection Settings to mention about the connectivity checker.
Jun-16-2022 NA Added section, Connecting to a Local Database.
Dec-06-2021 1.77 Added a See Also link to the Pipeline failure due to Redo Log expiry page.
Nov-22-2021 NA Updated the Limitations section.
Feb-22-2021 1.57 Added sections:
- Create a User and Grant Privileges
- Retrieve the Service Name
Was this page helpful?

Tell us what went wrong