Oracle DB
Output schema
By default, Oracle outputs each stream to a separate table. Each table contains three columns:
_AIRBYTE_AB_ID: a UUID that Airbyte assigns to each processed event. The Oracle column type isVARCHAR(64)._AIRBYTE_EMITTED_AT: a timestamp showing when the event was pulled from the data source. The Oracle column type isTIMESTAMP WITH TIME ZONE._AIRBYTE_DATA: a JSON blob containing the event data. The Oracle column type isNCLOB.
Enable normalisation to create strongly typed, normalised tables.
Supported sync modes
| Sync mode | Supported? |
|---|---|
| Full Refresh - Overwrite | Yes |
| Full Refresh - Append | Yes |
| Full Refresh - Overwrite + Deduped | No |
| Incremental Sync - Append | Yes |
| Incremental Sync - Append + Deduped | No |
Get started (Airbyte Cloud)
The Oracle connector is currently in Alpha on Airbyte Cloud. Airbyte Cloud only supports TLS-encrypted connections to your database. Otherwise, follow the open-source instructions below.
Get started (Airbyte Open Source)
Requirements
To use the Oracle destination, you’ll need:
- An Oracle server running version 21 or later
Network access
Make sure Airbyte can access your Oracle database. For databases within a VPC, allow access from the IP address exposing Airbyte.
Permissions
Airbyte namespaces let you store data in different schemas. Required permissions vary by scenario:
| Login user | Destination user | Required permissions | Comment |
|---|---|---|---|
| DBA User | Any user | - | |
| Regular user | Same user as login | Create, drop and write table, create session | |
| Regular user | Any existing user | Create, drop and write ANY table, create session | Grants can be provided on a system level by DBA or by target user directly |
| Regular user | Not existing user | Create, drop and write ANY table, create user, create session | Grants should be provided on a system level by DBA |
Create a dedicated Airbyte user for this purpose.
Set up the Oracle destination
You now have everything needed to configure Oracle as a destination in the UI. You’ll need the following information:
- Host
- Port
- Username
- Password
- Database
- Connection via SSH Tunnel
Airbyte can connect to an Oracle instance through an SSH tunnel. Use this when direct database access is unavailable or conflicts with security policy. For example, the database may not have a public IP address.
An SSH tunnel connects Airbyte through an intermediate server, also called a bastion server. This server has direct database access. Airbyte connects to the bastion, which then connects directly to the database server.
This feature requires additional configuration when you create the source. The following steps explain each configuration field.
- Configure every source field as usual, except
SSH Tunnel Method. SSH Tunnel Methoddefaults toNo Tunnel, which creates a direct connection. For an SSH tunnel, chooseSSH Key AuthenticationorPassword Authentication.- Choose
Key Authenticationwhen using an RSA private key to establish the SSH tunnel. See below for key-generation details. - Choose
Password Authenticationwhen using a password to establish the SSH tunnel.
- Choose
SSH Tunnel Jump Server Hostidentifies the intermediate bastion server that Airbyte connects to. Enter a hostname or IP address.SSH Connection Portis the bastion server port used for the SSH connection. The default SSH port is22. Keep this value unless you’ve explicitly changed the port.SSH Login Usernameis the username Airbyte uses to connect to the bastion server. It isn’t the Oracle username.- When using
Password Authentication, setSSH Login Usernameto the previous step’s user password. When usingSSH Key Authentication, leave this field blank. This isn’t the Oracle password. It belongs to the operating system user that Airbyte uses to run commands on the bastion. - When using
SSH Key Authentication, setSSH Private Keyto the RSA private key used for the SSH connection. Include the full key file contents, starting with-----BEGIN RSA PRIVATE KEY-----and ending with-----END RSA PRIVATE KEY-----.
Encryption options
Airbyte can connect to the Oracle source using three network connectivity options:
Unencrypteduses the TCP protocol. All network data is transmitted without encryption.Native network encryptionencrypts database connections without TCP / IP and SSL / TLS configuration overhead. You also don’t need to open and monitor different ports. By default, the SQLNET.ENCRYPTION_CLIENT option is always set to REQUIRED. The client or server only accepts encrypted traffic. You can choose anEncryption algorithmthat meets your security policies.TLS Encrypted(verify certificate) transfers data using TLS, including the handshake and certificate verification. To use this option, enter the server-issued certificate’s contents in theSSL PEM filefield.
Namespace support
This destination supports namespaces. The namespace maps to an Oracle schema.
Changelog
Expand to review
| Version | Date | Pull Request | Subject |
|---|---|---|---|
| 1.0.0 | 2024-04-11 | #36048 | Removes Normalization, updates to V2 Raw Table Format |
| 0.2.0 | 2023-06-27 | #27781 | License Update: Elv2 |
| 0.1.19 | 2022-07-26 | #10719 | Destination Oracle: added custom JDBC parameters support. |
| 0.1.18 | 2022-07-14 | #14618 | Removed additionalProperties: false from JDBC destination connectors |
| unpublished | 2022-05-17 | 12820 | Improved ‘check’ operation performance |
| 0.1.16 | 2022-04-06 | 11514 | Bump mina-sshd from 2.7.0 to 2.8.0 |
| 0.1.15 | 2022-02-25 | 10421 | Refactor JDBC parameters handling and remove DBT support |
| 0.1.14 | 2022-02-14 | 10256 | (unpublished) Add -XX:+ExitOnOutOfMemoryError JVM option |
| 0.1.13 | 2021-12-29 | #9177 | Update connector fields title/description |
| 0.1.12 | 2021-11-08 | #7719 | Improve handling of wide rows by buffering records based on their byte size rather than their count |
| 0.1.10 | 2021-10-08 | #6893 | 🎉 Destination Oracle: implemented connection encryption |
| 0.1.9 | 2021-10-06 | #6611 | 🐛 Destination Oracle: maxStringLength should be 128 |
| 0.1.8 | 2021-09-28 | #6370 | Add SSH Support for Oracle Destination |
| 0.1.7 | 2021-08-30 | #5746 | Use default column name for raw tables |
| 0.1.6 | 2021-08-23 | #5542 | Remove support for Oracle 11g to allow normalization |
| 0.1.5 | 2021-08-10 | #5307 | 🐛 Destination Oracle: Fix destination check for users without dba role |
| 0.1.4 | 2021-07-30 | #5125 | Enable additionalPropertities in spec.json |
| 0.1.3 | 2021-07-21 | #3555 | Partial Success in BufferedStreamConsumer |
| 0.1.2 | 2021-07-20 | #4874 | Require sid instead of database in connector specification |