Skip to main content

Oracle DB

Availability
Available CoreAvailable StandardAvailable PlusAvailable ProAvailable Enterprise FlexAvailable Self-Managed EnterpriseAvailable PyAirbyte
Support Level
Marketplace
Connector Version
0.5.8 (last updated 4 months ago)
Sync Success Rate
Low
Usage Rate
High
Data workers used
0.5 per sync (Learn more)
Definition ID
b39a7370-74c3-45a6-ac3a-380d48520a83

Features​

FeatureSupportedNotes
Full Refresh SyncYes
Incremental - Append SyncYes
Replicate Incremental DeletesNo
Logical Replication (WAL)No
TLS SupportYes
SSH Tunnel ConnectionYes
LogMinerNo
FlashbackNo
NamespacesYesEnabled by default

The Oracle source does not alter the schema present in your database. Depending on the destination connected to this source, however, the schema may be altered. See the destination's documentation for more details.

Prerequisites​

  1. Oracle Database 11g or above (tested with Oracle 11g, 12c, 18c, 19c, and 21c)
  2. Allow connections from Airbyte to your Oracle database (if they exist in separate VPCs)
  3. Create a dedicated read-only Airbyte user with access to all tables needed for replication

Setup guide​

Step 1: Confirm your database is accessible from the machine running Airbyte​

This is dependent on your networking setup. The easiest way to verify if Airbyte is able to connect to your Oracle instance is via the check connection tool in the UI.

This step is optional but highly recommended to allow for better permission control and auditing. Alternatively, you can use Airbyte with an existing user in your database.

To create a dedicated database user, run the following commands against your database:

CREATE USER airbyte IDENTIFIED BY <your_password_here>;
GRANT CREATE SESSION TO airbyte;

Next, grant the user read-only access to the relevant tables. The simplest way is to grant read access to all tables in the schema as follows:

GRANT SELECT ANY TABLE TO airbyte;

Or you can be more granular:

GRANT SELECT ON "<schema_a>"."<table_1>" TO airbyte;
GRANT SELECT ON "<schema_b>"."<table_2>" TO airbyte;

Your database user should now be ready for use with Airbyte.

Step 3: Configure connection type and schemas​

Connection Type: Choose between Service Name and System ID (SID):

  • Service Name: Recommended for modern Oracle installations (Oracle 8i and later). A service name is a logical representation of a database and can be used for connection load balancing and failover.
  • System ID (SID): Used for legacy Oracle instances. A SID uniquely identifies a specific Oracle database instance on a server.

Schemas: Specify the schemas Airbyte should sync from. Case sensitive. Defaults to the upper-cased username if empty. If the user does not have access to the configured schemas, no tables will be discovered.

Step 4: Additional JDBC configuration (optional)​

You can provide additional JDBC URL parameters in the JDBC URL Params field to customize the connection behavior. Parameters should be formatted as key=value pairs separated by &. For example:

defaultRowPrefetch=20&oracle.net.CONNECT_TIMEOUT=10000&oracle.net.READ_TIMEOUT=30000

Common useful parameters:

  • defaultRowPrefetch: Number of rows to prefetch (default: 10)
  • oracle.net.CONNECT_TIMEOUT: Connection timeout in milliseconds
  • oracle.net.READ_TIMEOUT: Socket read timeout in milliseconds

Connection via SSH Tunnel​

Airbyte can connect to an Oracle instance through an SSH tunnel. This is useful when the database is not directly accessible from the public internet, for example because it does not have a public IP address or your security policy restricts direct connections.

When using an SSH tunnel, Airbyte connects to an intermediate server (a bastion server) that has direct access to the database. Airbyte connects to the bastion and then asks the bastion to connect directly to the database.

To configure an SSH tunnel:

  1. Configure all fields for the source as you normally would, except SSH Tunnel Method.
  2. SSH Tunnel Method defaults to No Tunnel (meaning a direct connection). To use an SSH tunnel, choose SSH Key Authentication or Password Authentication.
    1. Choose Key Authentication if you will use an RSA private key for the SSH tunnel (see below for more information on generating this key).
    2. Choose Password Authentication if you will use a password for the SSH tunnel.
  3. SSH Tunnel Jump Server Host is the hostname or IP address of the bastion server.
  4. SSH Connection Port is the port on the bastion server for the SSH connection. The default SSH port is 22.
  5. SSH Login Username is the username Airbyte uses to connect to the bastion server. This is not the Oracle database username.
  6. If you are using Password Authentication, set Password to the password for the bastion server OS user. If you are using SSH Key Authentication, leave this blank.
  7. If you are using SSH Key Authentication, set SSH Private Key to the RSA private key for the SSH connection. Provide the full contents of the key file, starting with -----BEGIN RSA PRIVATE KEY----- and ending with -----END RSA PRIVATE KEY-----.

Generating an SSH Key Pair​

The connector expects an RSA key in PEM format. To generate this key:

ssh-keygen -t rsa -m PEM -f myuser_rsa

This produces the private key in PEM format and the public key in the standard format used by the authorized_keys file on your bastion host. Add the public key to the bastion host for the user you want Airbyte to connect as. Copy and paste the private key into the Airbyte connector configuration.

Data Type Mapping​

Oracle data types are mapped to the following data types when synchronizing data.

Note: The connector automatically handles LONG and LONG RAW columns by setting the Oracle JDBC property oracle.jdbc.useFetchSizeWithLongColumn=true. Oracle recommends avoiding LONG and LONG RAW columns in favor of LOB types (CLOB, BLOB) for new applications.

Oracle TypeResulting TypeNotes
binary_doublenumber
binary_floatnumber
blobstring
charstring
char(3 char)string
clobstring
datestring
decimalnumber
floatnumber
float(5)number
integernumber
interval year to monthstring
long rawstring
numbernumber
number(6, 2)number
nvarchar(3)string
rawstring
timestampstring
timestamp with local time zonestring
timestamp with time zonestring
varchar2string
varchar2(256)string
xmltypestring

If you do not see a type in this list, assume that it is coerced into a string. We are happy to take feedback on preferred mappings.

Encryption options​

The connector supports three encryption methods. On Airbyte Cloud, encryption is required. You must choose either Native Network Encryption or TLS. On Self-Managed Airbyte, all three options are available.

  1. Unencrypted (Self-Managed only): The connection uses the TCP protocol without encryption. All data is transmitted in plain text. Only use this option in secure, trusted network environments.

  2. Native Network Encryption (NNE): Provides database connection encryption without the configuration overhead of TLS and without requiring different ports. The connector sets SQLNET.ENCRYPTION_CLIENT=REQUIRED, meaning only encrypted traffic is accepted. Choose from these encryption algorithms:

    • AES256: Advanced Encryption Standard with 256-bit keys (recommended)
    • RC4_56: RC4 stream cipher with 56-bit keys (legacy, less secure)
    • 3DES168: Triple DES with 168-bit keys (legacy, less secure)

  3. TLS Encrypted (verify certificate): Uses the TLS protocol with certificate verification for maximum security. Requires you to provide the server's SSL certificate in PEM format in the SSL PEM file field. The certificate must be valid and trusted.

IP allow list​

If you use Airbyte Cloud and your organization restricts access to specific IPs, add the Airbyte Cloud IP addresses to your allow list.

Reference​

Config fields reference

Field
Type
Property name
object
encryption
string
host
integer
port
string
username
object
connection_data
string
jdbc_url_params
string
password
array<string>
schemas
object
tunnel_method

Changelog​

Expand to review
VersionDatePull RequestSubject
0.5.82026-03-2575410Fold strict-encrypt variant into main connector; encryption is now required on Airbyte Cloud
0.5.72025-07-1563326Fix publishing flow.
0.5.62025-07-1062920Convert to new gradle build flow
0.5.52025-07-1062921Convert to new gradle build flow
0.5.32024-12-1849883Use a base image: airbyte/java-connector-base:1.0.0
0.5.22024-02-1335225Adopt CDK 0.20.4
0.5.12024-01-2434453bump CDK version
0.5.02023-12-1833485Remove LEGACY state
0.4.02023-06-2627737License Update: Elv2
0.3.252023-06-2027212Fix silent exception swallowing in StreamingJdbcDatabase
0.3.242023-03-2220760Removed redundant date-time datatypes formatting
0.3.232023-03-0623455For network isolation, source connector accepts a list of hosts it is allowed to connect to
0.3.222022-12-1420346Consolidate date/time values mapping for JDBC sources
2022-10-1315535Update incremental query to avoid data missing when new data is inserted at the same time as a sync starts under non-CDC incremental mode
0.3.212022-09-0116238Emit state messages more frequently
0.3.202022-08-1814356DB Sources: only show a table can sync incrementally if at least one column can be used as a cursor field
0.3.192022-08-0314953Use Service Name to connect to database
0.3.182022-07-1414574Removed additionalProperties:false from JDBC source connectors
0.3.172022-06-2414092Introduced a custom jdbc param field
0.3.162022-06-2213997Fixed tests
0.3.152022-04-2912480Query tables with adaptive fetch size to optimize JDBC memory consumption
0.3.142022-02-2110242Fixed cursor for old connectors that use non-microsecond format. Now connectors work with both formats
0.3.132022-02-1810242Updated timestamp transformation with microseconds
0.3.122022-02-1410256Add -XX:+ExitOnOutOfMemoryError JVM option
0.3.112021-12-248958Add support for JdbcType.ARRAY
0.3.102021-12-078582Update connector fields title/description
0.3.92021-12-018371Fixed incorrect handling "\n" in ssh key
0.3.82021-10-137125Fix incorrect handling of LONG RAW data type
0.3.72021-10-016616Added network encryption options
0.3.62021-09-306585Improved SSH Tunnel key generation steps
0.3.52021-09-226356Added option to connect to DB via SSH.
0.3.42021-09-016038Remove automatic filtering of system schemas.
0.3.32021-09-015779Ability to only discover certain schemas.
0.3.22021-08-134699Added json config validator.
Was this page helpful?