Skip to content

Oracle

Oracle is an object-relational database management system created by the Oracle Corporation. Follow our setup guide to connect your Oracle database as a destination to ETLrobot.

Prerequisites

To connect your Oracle as a destination database in ETLrobot, you need:

  • Oracle version 12c or above.
  • Your database's IP address (e.g., 1.2.3.4) or domain (your.server.com).
  • Your database's port (usually 1521).
  • Your database service name or SID.
  • Database privileges that allow you to create users and grant privileges. This is required to create a database user for ETLrobot.
  • Set the parameter MAX_STRING_SIZE to extended. If using AWS, here instructions for setting the MAX_STRING_SIZE.
  • Oracle recommends using AL32UTF8 as the database character set.
  • It is recommended to set the database timezone to UTC.

Common supported implementations:

Setup instructions

  1. Database setup

    1. Connect to your Oracle instance as an admin user using your SQL client.

    2. Execute the following command to create an Oracle user and schema.
      Choose a memorable username (for example, etlrobot). Replace <password> with a strong password of your choice.

      CREATE USER <username> IDENTIFIED BY <password>;
GRANT CREATE SESSION TO <username>;
      • CREATE USER: Create a user and database schema. In Oracle, a user is a schema.
      • CREATE SESSION: Allows the user to connect to the database.

    3. Execute the following commands to grant the etlrobot user the following privileges.

      GRANT CREATE TABLE TO <username>;
GRANT UNLIMITED TABLESPACE TO <username>;
GRANT SELECT ON v$parameter TO <username>;
      • CREATE TABLE: Required to create tables in the schema.
      • UNLIMITED TABLESPACE: Allows the user to allocate as much space as needed to store the data.
      • SELECT ON v$parameter: Allows ETLrobot to validate the required parameter configuration settings.

  2. Choose your connection method

    Decide whether to connect to your Oracle database directly or via an SSH tunnel.

    Connect directly

    If your database is publicly accessible you may connect directly. You must whitelist ETLrobot's IPs by creating a rule in a security group that allows ETLrobot access to your database port and instance.

    Configure your firewall and/or other access control systems to allow:

    1. incoming connections to your host and port (usually 1521) from ETLrobot's IPs for your database's region
    2. outgoing connections from all ports (1024 to 65535) to ETLrobot's IPs
    Connect via an SSH tunnel

    If your database is not publicly accessible you will need to connect using an SSH tunnel.

    SSH requires a publicly accessible SSH server to act as an intermediary between ETLrobot and your private database. The SSH server will forward traffic from ETLrobot through a secured encrypted tunnel to the private database.

    To connect using SSH, do the following:

    1. Select the SSH option.
    2. Copy ETLrobot's public SSH key.
    3. Add the public key to the authorized_keys (usually $user/.ssh/authorized_keys) file of your SSH server. The key must be all on one line, so make sure that you don't introduce any line breaks when cutting and pasting.

    Click here detailed instructions on connecting via an SSH tunnel.

  3. Complete the destination setup

    Be sure to fill out all the required fields.

    ETLrobot tests and validates your Oracle destination connection and database setup. If successful, the sync to your database will automatically start.