> ## Documentation Index
> Fetch the complete documentation index at: https://docs.popsink.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Oracle Source

The Oracle Source Connector is an advanced tool designed to synchronize Oracle databases with Popsink efficiently and in real-time. It enables the real-time transfer of data, ensuring that the latest database changes are mirrored to the target systems. This capability is crucial for maintaining up-to-date business intelligence, enabling real-time analytics, and enhancing operational workflows.

## **Prerequisites**

Before deploying the Oracle Source Connector, certain prerequisites must be met to ensure a smooth integration process:

1. **Whitelisting Popsink's IP Address**: Ensure that Popsink's IP address is whitelisted to allow uninterrupted communication between Oracle databases and Popsink.
2. **Database Log Mode Configuration**: The Oracle database Archive Mode should be set to ArchiveLog.
3. **Supplemental Logging**: Supplemental Logs should be enable on the source you wish to replicate. This can me done at Database or Table Level :

   * At Database Level

   ```sql theme={null}
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   ```

   * At Table Level

   ```sql theme={null}
   ALTER TABLE **{db.name}** ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   ```

   > Note: If using AWS RDS, additional steps may be required. Please consult the relevant AWS RDS documentation and do feel free to reach out for help.

## **Required Permissions**

Although you *could* use a **`superuser`**, we do recommend setting up dedicated users for security reasons. The user account that the Oracle Source Connector utilizes must have sufficient privileges. The following SQL statements outline the necessary permissions:

```sql theme={null}
GRANT CREATE SESSION TO {username} CONTAINER=ALL;
GRANT SET CONTAINER TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to {username} CONTAINER=ALL;
GRANT FLASHBACK ANY TABLE TO {username} CONTAINER=ALL;
GRANT SELECT ANY TABLE TO {username} CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO {username} CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO {username} CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO {username} CONTAINER=ALL;
GRANT LOGMINING TO {username} CONTAINER=ALL;
GRANT CREATE TABLE TO {username} CONTAINER=ALL;
GRANT LOCK ANY TABLE TO {username} CONTAINER=ALL;
GRANT CREATE SEQUENCE TO {username} CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR TO {username} CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR_D TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOG TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOG_HISTORY TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_LOGS TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO {username} CONTAINER=ALL;
GRANT SELECT ON V_$TRANSACTION TO {username} CONTAINER=ALL;
```

Replace **`{username}`** with the actual username being used for the connection.

## Steps

Go to: **[Sources](https://app.popsink.com/sources) -**> [**Create New**](https://app.popsink.com/sources/add) then select the **Oracle** connector and **Continue**

In the *Credentials* sections fill in the required information.

### **`host`**

* **Description**: The hostname or IP address of the Oracle database server.
* **Type**: **`string`**
* **Example**: **`oracle-db-server.example.com`**

### **`port`**

* **Description**: The port number on which the Oracle database server is listening.
* **Type**: **`integer`**
* **Default**: **`1521`** (default port for Oracle database)
* **Example**: **`1521`**

### **`user`**

* **Description**: The username used to connect to the Oracle database.
* **Type**: **`string`**
* **Example**: **`popsink_user`**

### **`password`**

* **Description**: The password associated with the specified user for authentication.
* **Type**: **`string`**
* **Example**: **`p@ssw0rd123`**

### **`service_name`** or **`sid`**

Provide **exactly one** of the two:

* **`service_name`**: The Oracle service name to connect to.
  * **Type**: **`string`**
  * **Example**: **`ORCLPDB1`**
* **`sid`**: The Oracle System Identifier, for legacy databases that are not service-name addressable.
  * **Type**: **`string`**
  * **Example**: **`ORCL`**

### Advanced Options

| Field                  | Default | Description                                             |
| ---------------------- | ------- | ------------------------------------------------------- |
| **Poll Interval (ms)** | `2000`  | How often the connector polls for new redo log activity |
| **Initial Load**       | `true`  | Snapshot existing rows before streaming changes         |

## Connecting through an SSH Tunnel

If the database is only reachable through a bastion host, enable **SSH Tunnel** and provide:

| Field               | Required | Description                                     |
| ------------------- | -------- | ----------------------------------------------- |
| **SSH Host**        | Yes      | Hostname or IP of the bastion host              |
| **SSH Port**        | Yes      | SSH port (default `22`)                         |
| **SSH User**        | Yes      | User to authenticate as on the bastion          |
| **SSH Private Key** | Yes      | **Base64-encoded** private key for the SSH user |

### Table Selection

After a successful credential check, pick the tables to replicate from the discovered list. The selection is stored as a comma-separated whitelist in `SCHEMA.TABLE` format.

You can use **Check Credentials** at any moment to verify the validity of your inputs.

Once the validity check has passed, the next step is the standard *Informations* page where you may give the connector a name and select which of your teams should own this connector.

Create the connector and you are now done. The connector may take a few minutes to synchronize with the source database.

## **Conclusion**

The Oracle Source Connector is an essential component for creating a reliable data pipeline between Oracle databases and Popsink. By following the guidelines provided in this document, you can ensure a successful setup and enjoy a robust, real-time data integration experience.