Oracle DB
Output schema
By default, each stream goes to its own Oracle table. Each table contains three columns:
_AIRBYTE_AB_ID: a UUID Airbyte assigns to each processed event. The Oracle column type isVARCHAR(64)._AIRBYTE_EMITTED_AT: a timestamp showing when Airbyte pulled the event 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.
Enabling normalisation also creates 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
You’ll need the following to use the Oracle destination:
- An Oracle server running version 21 or above
Network access
Make sure Airbyte can access your Oracle database. If your database is inside a VPC, you may need to allow access from the IP 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 |
Creating an Airbyte-specific user for this purpose is highly recommended.
Set up the Oracle destination in Airbyte
You now have everything required to configure Oracle as a destination in the UI. You’ll need these details:
- Host
- Port
- Username
- Password
- Database
- Connection via SSH Tunnel
Airbyte can connect to an Oracle instance through an SSH Tunnel. Use this approach when direct database access is impossible or breaches security policy, such as when it has no public IP address.
With an SSH tunnel, Airbyte connects through an intermediate server, also called a bastion server. This server must have direct database access. Airbyte connects to the bastion, which then connects directly to the database server.
This feature needs extra configuration when you create the source. The following guidance explains each setting.
- Configure every source field as usual, except
SSH Tunnel Method. SSH Tunnel Methoddefaults toNo Tunnel, which means 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 guidance on generating this key. - Choose
Password Authenticationwhen using a password to establish the SSH Tunnel.
- Choose
SSH Tunnel Jump Server Hostis the intermediate (bastion) server that Airbyte connects to. Enter its hostname or IP Address.SSH Connection Portis the bastion server port used for the SSH connection. The default SSH port is22. Keep this default unless you’ve explicitly changed the port.SSH Login Usernameis the username Airbyte uses to connect to the bastion server. This isn’t the Oracle username.- If you’re using
Password Authentication, setSSH Login Usernameto the previous step’s user password. If you’re usingSSH Key Authentication, leave this field blank. This isn’t the Oracle password. It’s the OS-user password Airbyte uses to run commands on the bastion. - If you’re using
SSH Key Authentication, setSSH Private Keyto the RSA Private Key used for the SSH connection. Include the key file’s full 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:
Unencrypted: The connection uses the TCP protocol. All data travels across the network without encryption.Native network encryption: This option encrypts database connections without TCP / IP and SSL / TLS configuration overhead. You also don’t need to open and listen on different ports. The SQLNET.ENCRYPTION_CLIENT option always defaults to REQUIRED. The client or server only accepts encrypted traffic. You can choose anEncryption algorithmthat meets your security policies.TLS Encrypted(verify certificate): This option transfers data using TLS, including the handshake procedure and certificate verification. To use it, insert the server-issued certificate’s content into 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 |