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

# Oracle Source Connector: Setup and Configuration

> Configure Oracle as a source in Artie with archive logs, LogMiner, supplemental logging, and service account permissions for both single-tenant and multi-tenant architectures.

## Prerequisites

* Host and port
* Single-tenant architecture: Database name
* Multi-tenant architecture: Container database name (CDB) and pluggable database name (PDB)
* Service account credentials

## Oracle setup

### 1. Enable archive logs

```sql theme={null}
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
```

### 2. Enable LogMiner

```sql theme={null}
-- Enable database supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- For each table, enable column level supplemental logging:
ALTER TABLE SCHEMA_NAME.TABLE_NAME ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
```

### 3. Creating the database user

<CodeGroup>
  ```sql Multi-tenant (CDB) theme={null}
  ALTER SESSION SET CONTAINER=CDB$ROOT;
  CREATE USER C##ARTIE_USER IDENTIFIED BY YOUR_PASSWORD;

  ALTER SESSION SET CONTAINER={PDB};
  CREATE TABLESPACE ARTIE_LOGMINER_TBS DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
  ALTER USER C##ARTIE_USER DEFAULT TABLESPACE ARTIE_LOGMINER_TBS;
  ALTER USER C##ARTIE_USER QUOTA UNLIMITED ON ARTIE_LOGMINER_TBS;

  -- Grant permissions
  GRANT CREATE SESSION TO C##ARTIE_USER CONTAINER=ALL; 
  GRANT SET CONTAINER TO C##ARTIE_USER CONTAINER=ALL; 

  -- Allows the Connector to use LogMiner
  GRANT LOGMINING TO C##ARTIE_USER CONTAINER=ALL;

  -- Flashback queries used for performing initial snapshots of the data
  GRANT FLASHBACK ANY TABLE TO C##ARTIE_USER CONTAINER=ALL; 
  GRANT SELECT ANY TRANSACTION TO C##ARTIE_USER CONTAINER=ALL; 

  -- Required for schema history when performing initial snapshots
  GRANT SELECT_CATALOG_ROLE TO C##ARTIE_USER CONTAINER=ALL; 
  GRANT EXECUTE_CATALOG_ROLE TO C##ARTIE_USER CONTAINER=ALL; 

  -- Connector creates a table for explicitly managing the flushing of internal log buffers (LGWR)
  GRANT CREATE TABLE TO C##ARTIE_USER CONTAINER=ALL;

  GRANT CREATE SEQUENCE TO C##ARTIE_USER CONTAINER=ALL; 

  -- Grant the user permission to read each schema and table you wish to sync
  ALTER SESSION SET CONTAINER={PDB};
  GRANT SELECT ON SCHEMA.TABLE TO C##ARTIE_USER CONTAINER=ALL;
  -- Alternatively, you can grant access to all
  -- GRANT SELECT ANY TABLE TO C##ARTIE_USER CONTAINER=ALL;

  -- Grant the user access to the DBA_EXTENTS, DBA_TABLESPACES, DBA_SEGMENTS, and TRANSACTION system views. 
  GRANT SELECT ON DBA_EXTENTS TO C##ARTIE_USER;
  GRANT SELECT ON DBA_TABLESPACES TO C##ARTIE_USER;
  GRANT SELECT ON DBA_SEGMENTS TO C##ARTIE_USER;
  GRANT SELECT ANY TRANSACTION TO C##ARTIE_USER;

  -- Grant the user permission to run LogMiner
  ALTER SESSION SET CONTAINER=CDB$ROOT;
  GRANT SELECT ON SYS.V_$DATABASE TO C##ARTIE_USER;
  GRANT SELECT ON SYS.V_$PARAMETER TO C##ARTIE_USER;
  GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO C##ARTIE_USER;
  GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO C##ARTIE_USER;
  GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO C##ARTIE_USER;
  GRANT EXECUTE ON DBMS_LOGMNR TO C##ARTIE_USER;
  GRANT EXECUTE ON DBMS_LOGMNR_D TO C##ARTIE_USER;
  GRANT SELECT ANY TRANSACTION TO C##ARTIE_USER;
  GRANT EXECUTE_CATALOG_ROLE TO C##ARTIE_USER;
  ```

  ```sql Single-tenant theme={null}
  CREATE USER ARTIE_USER IDENTIFIED BY {password};

  CREATE TABLESPACE ARTIE_LOGMINER_TBS DATAFILE SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
  ALTER USER ARTIE_USER DEFAULT TABLESPACE ARTIE_LOGMINER_TBS;
  ALTER USER ARTIE_USER QUOTA UNLIMITED ON ARTIE_LOGMINER_TBS;

  -- Grant permissions
  GRANT CREATE SESSION TO ARTIE_USER; 
  GRANT SET CONTAINER TO ARTIE_USER; 

  -- Allows the Connector to use LogMiner
  GRANT LOGMINING TO ARTIE_USER; 

  -- Flashback queries used for performing initial snapshots of the data
  GRANT FLASHBACK ANY TABLE TO ARTIE_USER; 
  GRANT SELECT ANY TRANSACTION TO ARTIE_USER; 

  -- Required for schema history when performing initial snapshots
  GRANT SELECT_CATALOG_ROLE TO ARTIE_USER; 
  GRANT EXECUTE_CATALOG_ROLE TO ARTIE_USER; 

  -- Connector creates a table for explicitly managing the flushing of internal log buffers (LGWR)
  GRANT CREATE TABLE TO ARTIE_USER; 

  GRANT CREATE SEQUENCE TO ARTIE_USER;

  -- Grant the user permission to read each schema and table you wish to sync
  GRANT SELECT ON SCHEMA.TABLE TO ARTIE_USER;
  -- Alternatively, you can grant access to all
  -- GRANT SELECT ANY TABLE TO ARTIE_USER;

  -- Grant the user permission to run LogMiner
  GRANT SELECT ON SYS.V_$DATABASE TO ARTIE_USER;
  GRANT SELECT ON SYS.V_$PARAMETER TO ARTIE_USER;
  GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO ARTIE_USER;
  GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO ARTIE_USER;
  GRANT EXECUTE ON DBMS_LOGMNR TO ARTIE_USER;
  GRANT EXECUTE ON DBMS_LOGMNR_D TO ARTIE_USER;
  GRANT SELECT ANY TRANSACTION TO ARTIE_USER;
  GRANT EXECUTE_CATALOG_ROLE TO ARTIE_USER;
  ```
</CodeGroup>

## Overriding primary keys

If you want to replicate tables that do not have primary keys, you can specify the unique index that you want to use as the primary key.
Specify this under the "Advanced settings" tab in the table settings.