Overview

The OLake Oracle Source connector supports two synchronization modes. It offers features like parallel chunking, checkpointing, and automatic resume for failed full loads. This connector can be used within the OLake UI or run locally via Docker for open-source workflows.

Sync Modes Supported

• Full Refresh
• Full Refresh + Incremental

Prerequisites

Version Prerequisites

Oracle Database 11g or above (tested with Oracle 19c, and 23ai)

Connection Prerequisites

• Following permissions need to be granted to the user which will be used to connect Oracle DB with OLake.

  1. CREATE SESSION

  Use: Fundamental system privilege that allows a user to log in (connect) to the Oracle database.
  Why is it required?: To connect to the database (DBeaver, SQL Developer, OLake, etc.).
  Grant Query:

      GRANT CREATE SESSION TO <username>;

  ---

  2. SELECT ON <table>

  Use: Grants read-only access to run SELECT queries on the table owned by the schema owner.
  Why is it required?: Required to access the table for discovery and chunk iteration.
  Grant Query:

      GRANT SELECT ON <table_owner>.<table_name> TO <username>;

  ---

After initial Prerequisites are fulfilled, the configurations for Oracle can be configured.

---

Configuration

Use Olake UI for Oracle

1. Navigate to the Source Configuration Page

1. Complete the OLake UI Setup Guide
2. After logging in to the OlakeUI, select the Sources tab from the left sidebar.
3. Click Create Source on the top right corner.
4. Select Oracle from the connector dropdown
5. Provide a name for this source.

2. Provide Configuration Details

• Enter Oracle DB credentials.
  

Field	Description	Example Value
Host required	Hostname or IP address of the Oracle database server.	oracle-host
Port required	TCP port on which the Oracle listener is accepting connections.	1521
Connection Type required	Method used to establish a connection between the Oracle database server. It can be either Service Name or SID	Service Name
Service Name^	Oracle service name that identifies the specific database service to connect to.	oracle-service-name
SID^	Oracle DB sid that uniquely identifies the database instance.	oracle-sid
Username required	Database user used to authenticate the connection.	oracle-user
Password	Password for the specified user.	oracle-password
Max Threads	Maximum number of worker threads the connector can spin up for parallel tasks.	10
Retry Count	Number of times the connector will retry a failed operation before giving up.	0
JDBC URL Parameters	Extra JDBC URL parameters for fine-tuning the connection.	{"TIMEOUT": "86400"}
SSL Mode	SSL configuration for the database connection. Contains details such as the SSL mode.	disable

Oracle Username Case Sensitivity

Oracle Database automatically converts unquoted lowercase usernames to uppercase. If a connection error occurs, try changing the username to all uppercase.

3. Test Connection

• Once the connection is validated, the Oracle source is created. Jobs can then be configured using this source.

• In case of connection failure, refer to the Troubleshooting section.

Use OLake CLI for Oracle

1. Create Configuration File

• Once the Olake CLI is setup, create a folder to store configuration files such as source.json and destination.json.

The source.json file for oracle must contain these mandatory fields.

2. Provide Configuration Details

An example source.json file will look like this:

source.json

{
 "host": "oracle-host",
 "username": "oracle-user",
 "password": "oracle-password",
 "service_name": "oracle-service-name",
 "sid": "oracle-sid",
 "port": 1521,
 "max_threads": 10,
 "backoff_retry_count": 3,
 "jdbc_url_params": {},
 "ssl": {
     "mode": "disable"
 }
}

Field	Description	Example Value	Type
host required	Hostname or IP address of the Oracle database server.	"oracle-host"	String
port required	TCP port on which the Oracle listener is accepting connections.	1521	Integer
service_name^	Oracle service name that identifies the specific database service to connect to.	"oracle-service-name"	String
sid^	Oracle DB sid that uniquely identifies the database instance.	"oracle-sid"	String
username required	Database user used to authenticate the connection.	"oracle-user"	String
password	Password for the specified user.	"oracle-password"	String
max_threads	Maximum number of worker threads the connector can spin up for parallel tasks.	10	Integer
backoff_retry_count	Number of times the retry will take place incase of timeout before failing the sync.	3	Integer
jdbc_url_params	Extra JDBC URL parameters for fine-tuning the connection.	{"TIMEOUT": "86400"}	Object
ssl	SSL configuration for the database connection. Contains details such as the SSL mode.	{"mode": "disable"}	Object

^ Only one among service_name or sid is required

Similarly, destination.json file can be created inside this folder. For more information, see destination documentation.

Oracle Username Case Sensitivity

Oracle Database automatically converts unquoted lowercase usernames to uppercase. If a connection error occurs, try changing the username to all uppercase.

3. Check Source Connection

To verify the database connection following command needs to be run:

check command

docker run --pull=always \
-v "[PATH_OF_CONFIG_FOLDER]:/mnt/config" \
olakego/source-oracle:latest \
check \
--config /mnt/config/source.json

• If OLake is able to connect with Oracle DB {"connectionStatus":{"status":"SUCCEEDED"},"type":"CONNECTION_STATUS"} response is returned.

• In case of connection failure, refer to the Troubleshooting section.

---

Data Type Mapping

Oracle Data Types	Destination Data Type
NUMBER(n,0) where n<=9	int
NUMBER(n,0) where n>9	bigint
NUMBER(n,m) where m!=0, BINARY_DOUBLE, FLOAT	double
BINARY_FLOAT	float
DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIMEZONE	timestamptz
VARCHAR2, NVARCHAR2, CLOB, NCLOB, LONG, RAW, LONG RAW, BLOB, BFILE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, CHAR(* BYTE)	string

timestamptz timezone

OLake always ingests timestamp data in UTC format, independent of the source timezone.

---

Date and Time Handling

During transfer, values in date, time, and timestamp columns are modified to ensure valid calendar ranges and destination compatibility.

• Case I (Year 0000):
  Source dates with year 0000 are not valid in most destinations, so we change them to the epoch start date.
  Example: 0000-05-10 → 1970-01-01
• Case II (Year > 9999):
  Extremely large years are capped at 9999. The month and date are not affected.
  Examples: 10000-03-12 → 9999-03-12
• Case III (Invalid month/day):
  When the month or day exceeds valid ranges (i.e. month > 12 or day > 31), or the combined date is invalid, the value is replaced with the epoch start date.
  Examples: 2024-13-15 → 1970-01-01, 2023-04-31 → 1970-01-01

Note

These rules apply to date, time, and timestamp columns during transfer.

---

Troubleshooting

1. Sync Failure Due to I/O Timeout

2025-06-30T14:12:18Z FATAL error occurred while reading records: error occurred while waiting for context groups: failed to get or split chunks: failed to get next row id: read tcp 192.xx.x.xxx:xxxxx->65.x.xxx.xxx:xxxx: i/o timeout

Cause: Oracle databases often enforce an idle timeout policy, typically between 10 to 15 minutes, after which inactive sessions are terminated. For large tables (e.g., with 10M+ rows), the chunk splitting and row-reading operations may take longer than this default threshold, leading to a connection timeout.

Solution: Set the TIMEOUT parameter (in seconds) to extend the idle timeout:

• In OLake UI, add it under the JDBC URL Parameters textbox while creating or editing the Oracle source.

• In OLake CLI, Add it under the jdbc_url_params field in your config.json.

"jdbc_url_params": {
    "TIMEOUT": "86400" /* Timeout in seconds; here, 24 hours (24 × 60 × 60) */
}

If the issue is not listed here, post the query on Slack to get it resolved within a few hours.

Changelog

Date of Release	Version	Description
27 August 2025	0.1.11	override default timeout in Discover