Skip to main content

Singer.io tap for extracting data from Oracle - extended for new Oracle versions

Project description

extended-tap-oracle

License: MIT

Singer tap that extracts data from a Oracle database and produces JSON-formatted data following the Singer spec.

How to use it

Run and configuration of this Singer Tap depends of the desired replication mode (INCREMENTAL or STREAMING)

Prerequisites : Create user on targeted PDB

Connect to CDB, then switch to targetted PDB

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SQL> alter session set container=wms17;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
WMS17

Tap-Oracle user need to be created with the following rights on DB :

  • CREATE_SESSION system privilege
grant CREATE SESSION to singer_user;
  • SELECT right on V_$DATABASE
grant select on V_$DATABASE to singer_user;

You can also grant select on table to singer, table by table or via SELECT ANY TABLE privilege (up to you)

  • SELECT ANY TABLE system privilege
grant SELECT ANY TABLE to singer_user;

Log based replication

Tap-Oracle Log-based replication requires some configuration changes in Oracle database:

  • Enable ARCHIVELOG mode

  • Set retention period a reasonable and long enough period, ie. 1 day, 3 days, etc.

  • Enable Supplemental logging

Setting up Log-based replication on a self hosted Oracle Database:

To verify the current archiving mode, if the result is ARCHIVELOG, archiving is enabled:

  SQL> SELECT LOG_MODE FROM V$DATABASE

To enable ARCHIVELOG mode (if not enabled yet):

  SQL> SHUTDOWN IMMEDIATE
  SQL> STARTUP MOUNT
  SQL> ALTER DATABASE ARCHIVELOG
  SQL> ALTER DATABASE OPEN

To set retention period, use RMAN:

  RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

To enable supplemental logging:

  SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

Install and Run

First, make sure Python 3 is installed on your system or follow these installation instructions for Mac or Ubuntu.

It's recommended to use a virtualenv:

  python3 -m venv venv
  pip install extended-tap-oracle

or

  python3 -m venv venv
  . venv/bin/activate
  pip install --upgrade pip
  pip install .

Configuration

Running the the tap requires a config.json file.

Example with the minimal settings:

  {
    "host": "foo.com",
    "port": 1521,
    "user": "my_user",
    "password": "password",
    "sid": "ORCL",
    "filter_schemas": "HR" # Lets get only the HR sample schema
  }

You can run a discover run using the previous config.json file to acquire all the tables definition

tap-oracle --config /tmp/config.json --discover >> /tmp/catalog.json

Then use the catalog.json to run a full export:

tap-oracle --config /tmp/config.json --catalog /tmp/catalog.json

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

extended-tap-oracle-1.0.7.tar.gz (13.3 kB view details)

Uploaded Source

File details

Details for the file extended-tap-oracle-1.0.7.tar.gz.

File metadata

  • Download URL: extended-tap-oracle-1.0.7.tar.gz
  • Upload date:
  • Size: 13.3 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.8.0 pkginfo/1.8.2 readme-renderer/32.0 requests/2.25.1 requests-toolbelt/0.9.1 urllib3/1.26.4 tqdm/4.59.0 importlib-metadata/4.11.1 keyring/23.5.0 rfc3986/2.0.0 colorama/0.4.4 CPython/3.8.5

File hashes

Hashes for extended-tap-oracle-1.0.7.tar.gz
Algorithm Hash digest
SHA256 83975e980fc1795fb6dc807665c8e5a2ad47febe61771062bb50f82fb01b5f28
MD5 52549b1d7ff3897366c6b029a17559b8
BLAKE2b-256 633a8be4f5c0c4fa902c442e3f32d75659f41dbf07bd911d0feae39b471e87fe

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page