> For the complete documentation index, see [llms.txt](https://docs.erathos.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.erathos.com/connectors/databases/oracle.md).

# Oracle

Find out how to connect Oracle to your data warehouse effortlessly.

The available connections between databases and the Erathos platform are through **Open Connection**, **Static IP** and **SSH tunnel**.

### Connecting Oracle:

1. Log in to your Erathos account
2. Select Oracle as connector
3. Name your connection
4. Fill in the following form with the required information:
   * **db\_host** — the hostname or IP address of your Oracle database
   * **db\_port** — the listener port (`1521` by default, `1522` on Oracle Autonomous Database)
   * **db\_name** — the Oracle **service name** (Erathos connects using a service name, not a SID). On Oracle Cloud this is usually the fully qualified name, including the PDB name and the VCN's DNS domain, e.g. `PDB.sub09061234567.myvcn.oraclevcn.com`
   * **db\_user** and **db\_password** — the database credentials Erathos should use

You can optionally fill in:

* **db\_schema** — restricts extraction to a single schema. When left empty, Erathos will discover tables, views and materialized views across every schema the credentials can see (excluding Oracle's built-in system schemas, such as `SYS`, `SYSTEM`, `XDB`, `CTXSYS`, etc.)
* **db\_role** — only required if the supplied credentials must connect with an elevated Oracle connect role, such as `SYSDBA` or `SYSOPER`. Leave this empty for a regular (non-privileged) connection.
* SSH tunnel fields — **ssh\_host**, **ssh\_user**, **ssh\_password** (or an **SSH private key**), **ssh\_port** and **ssh\_keepalive\_interval**, if you choose to connect through an SSH tunnel/bastion.

{% hint style="info" %}
Erathos always attempts to connect over an encrypted **TCPS** connection first and automatically falls back to plain **TCP** if the database doesn't have TCPS configured. No wallet file upload is required.
{% endhint %}

When using CDC, the following values may be used in cdc\_snapshot\_mode:

* *initial*: performs an initial snapshot, after it completes, streaming following changes from the redo/archive logs.
* *no\_data*: does not perform any snapshot, only streams changes that are currently available in the redo/archive logs.

If your database is a multitenant (CDB/PDB) database, also fill in the following CDC fields (see [CDC setup](#cdc-setup) below):

* **cdc\_db\_name** — the service name of the **container database (CDB)**, e.g. `ORCLCDB`. CDC reads changes from the CDB, even when your tables live in a PDB.
* **cdc\_pdb\_name** — the name of the **pluggable database (PDB)** that holds your tables, e.g. `ORCLPDB1`.
* **cdc\_db\_user** and **cdc\_db\_password** — the common user created for CDC (e.g. `c##erathos_cdc`). When left empty, **db\_user** and **db\_password** are used.

Don't forget to click on **Save and close** to advance to the next steps of the integration.

With this connection created you can seamlessly move your Oracle data to data-warehouses such as BigQuery, Redshift or Databricks.

### Finding your connection details in Oracle Cloud (OCI)

The steps to gather the host, port and service name depend on whether your database is an **Autonomous Database** or a **DB System** (VM, Bare Metal or Exadata).

#### Autonomous Database (ADB-S / ADW / ATP)

1. In the OCI Console, go to **Oracle Database** > **Autonomous Database** and select your database instance.
2. Click **DB Connection**, then open the **Connection Strings** tab.
3. Pick any of the listed connection strings (e.g. the `_high` or `_medium` profile) and use it to fill in:
   * **db\_host** and **db\_port** (`1522`) from the `host`/`port` shown in the connect descriptor.
   * **db\_name** with the full `service_name` shown in the same connect descriptor (it already includes the VCN's DNS domain, e.g. `PDB.sub09061234567.myvcn.oraclevcn.com`).
4. Under **Network**, configure an **Access Control List (ACL)** allowing connections either from Erathos' static IP or from any IP.
5. With an ACL configured, you can toggle **Mutual TLS (mTLS) authentication** to **off** on the same Network settings page. This lets Erathos connect over TLS without requiring a client wallet, matching how the connector authenticates.

#### DB Systems (Base Database / VM / Bare Metal / Exadata)

1. In the OCI Console, go to **Oracle Database** > **DB Systems**, select your DB system, then select the database.
2. Under **DB Connection**, copy the **Host Name/IP** into **db\_host** and the **Port** (`1521` by default) into **db\_port**.
3. For **db\_name**, use the fully qualified service name shown there — it's the PDB/service name followed by the subnet and VCN DNS domain (e.g. `PDB.sub09061234567.myvcn.oraclevcn.com`), not just the short service or database name. If it's not shown on that page, you can confirm it by running `SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') FROM DUAL;` on the database, or by checking the subnet's DNS domain name under **Networking > Virtual Cloud Networks > your VCN > Subnets**.
4. Make sure the DB system's **Virtual Cloud Network (VCN)** security list or network security group allows inbound traffic on the database port:
   * From Erathos' static IP, if you restrict allowed IPs to your database.
   * From any IP, if you have a database open to the internet.
   * If the DB system only has a private IP, use the **SSH tunnel** connection method through a bastion/jump host that Erathos can reach, filling in the ssh\_host, ssh\_user and ssh\_password (or SSH private key) fields.
5. DB Systems typically only expose a plain TCP listener (no TCPS) unless you've configured one yourself — this is fine, Erathos automatically falls back to TCP in that case.

### Database user and permissions

Erathos only needs read access. We recommend creating a dedicated, read-only database user:

```sql
CREATE USER erathos_reader IDENTIFIED BY "<strong-password>";

-- Required to establish a connection
GRANT CREATE SESSION TO erathos_reader;

-- Lets Erathos read table/column/primary-key metadata from the data
-- dictionary views (ALL_OBJECTS, ALL_TAB_COLUMNS, ALL_CONSTRAINTS, ALL_CONS_COLUMNS)
GRANT SELECT_CATALOG_ROLE TO erathos_reader;

-- Lets Erathos read the actual table/view data
GRANT SELECT ANY TABLE TO erathos_reader;
```

If you'd rather scope access to specific schemas or tables instead of granting `SELECT ANY TABLE`, grant `SELECT` explicitly on each object you want to sync, and set the **db\_schema** field described above so Erathos only introspects that schema:

```sql
GRANT SELECT ON schema_name.table_name TO erathos_reader;
```

{% hint style="warning" %}
Without `SELECT_CATALOG_ROLE` (or equivalent grants on the `ALL_*` dictionary views), Erathos will not be able to discover tables, columns or primary keys, even if `SELECT` has been granted on the underlying tables.
{% endhint %}

### CDC setup

In order to enable CDC in your Oracle connection you need to confirm the following settings in your database. CDC reads changes from the database's redo and archive logs using Oracle **LogMiner**, so the steps below require a user with `SYSDBA` privileges.

{% hint style="warning" %}
CDC requires control over archive logging, supplemental logging and access to LogMiner. Managed services that restrict these settings, such as Oracle Autonomous Database, may not support CDC. If you are unsure, reach out to our team at <support@erathos.com>.
{% endhint %}

#### Archive log mode

The database must run in `ARCHIVELOG` mode, so that redo logs are archived instead of overwritten before CDC reads them.

```sql
-- Should return ARCHIVELOG
SELECT LOG_MODE FROM V$DATABASE;

-- If necessary, enable it (connected as SYSDBA to the CDB root on multitenant databases):
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
```

{% hint style="warning" %}
Important: Enabling `ARCHIVELOG` mode requires restarting the database.
{% endhint %}

{% hint style="danger" %}
Important: If archive logs that CDC still needs to read are deleted (e.g. by an RMAN backup policy or because the recovery area filled up), the connection will need to perform a fresh initial snapshot to recover. Keep archive logs long enough for CDC to read them, and make sure `db_recovery_file_dest_size` leaves enough room for them.
{% endhint %}

#### Supplemental logging

By default, Oracle's redo logs don't carry enough information to reconstruct full rows. Minimal supplemental logging must be enabled for the database, and full (`ALL`) column logging for every table you want to stream.

```sql
-- Should return YES (run on the CDB root on multitenant databases)
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

-- If necessary, enable it with:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Enable full column logging for each captured table (run in the PDB on multitenant databases)
ALTER TABLE schema_name.table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- List tables with full column logging enabled
SELECT OWNER, TABLE_NAME
FROM ALL_LOG_GROUPS
WHERE LOG_GROUP_TYPE = 'ALL COLUMN LOGGING';
```

#### CDC user

CDC needs a user with LogMiner privileges, besides read access to your tables. On multitenant (CDB/PDB) databases this must be a **common user** (prefixed with `c##`) created in the CDB root, and its credentials go in the **cdc\_db\_user** and **cdc\_db\_password** fields.

CDC also creates a small helper table in the user's default tablespace, so the user needs a quota on it. On multitenant databases the tablespace must exist both in the CDB root and in the PDB.

```sql
-- Multitenant (CDB/PDB): run in the CDB root as SYSDBA
CREATE USER c##erathos_cdc IDENTIFIED BY "<strong-password>"
    DEFAULT TABLESPACE users QUOTA UNLIMITED ON users
    CONTAINER=ALL;

GRANT CREATE SESSION TO c##erathos_cdc CONTAINER=ALL;
GRANT SET CONTAINER TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE TO c##erathos_cdc CONTAINER=ALL;
GRANT FLASHBACK ANY TABLE TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO c##erathos_cdc CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##erathos_cdc CONTAINER=ALL;
GRANT LOGMINING TO c##erathos_cdc CONTAINER=ALL;
GRANT CREATE TABLE TO c##erathos_cdc CONTAINER=ALL;
GRANT LOCK ANY TABLE TO c##erathos_cdc CONTAINER=ALL;
GRANT CREATE SEQUENCE TO c##erathos_cdc CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR TO c##erathos_cdc CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$LOG TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$LOG_HISTORY TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_LOGS TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$TRANSACTION TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$MYSTAT TO c##erathos_cdc CONTAINER=ALL;
GRANT SELECT ON V_$STATNAME TO c##erathos_cdc CONTAINER=ALL;
```

On a non-multitenant database, run the same grants without `CONTAINER=ALL`, skip `SET CONTAINER`, and use a regular user name (e.g. `erathos_cdc`). You can then use it as the connection's **db\_user**, or keep **db\_user** as your read-only user and fill in **cdc\_db\_user** and **cdc\_db\_password**.

{% hint style="info" %}
On AWS RDS for Oracle, archive logging is enabled together with automated backups, and the settings above are changed through RDS procedures instead, e.g. `EXEC rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');` and `EXEC rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 24);`.
{% endhint %}

#### Additional CDC settings

The following optional fields can be used to fine-tune CDC:

* **cdc\_lob\_enabled** — set to `true` to capture `BLOB`/`CLOB` columns. By default, LOB columns are left out of CDC rows because capturing them is considerably slower.
* **cdc\_log\_mining\_strategy** — `online_catalog` (default) reads table structures from the current data dictionary and is the fastest option. If the structure of your captured tables changes frequently, use `redo_log_catalog` instead, which tracks table structure changes through the redo logs.