K Knowledge Base
K Knowledge Base
Breadcrumbs

Oracle Database (via Collector method) - v2.0.0

About Collectors

Pre-requisites

  • Python 3.6 - 3.9

  • Access to Oracle (see section below)

‌

Oracle Access

Create an Oracle user with read access to following tables

  • dba_hist_active_sess_history

  • dba_hist_snapshot

  • dba_users

  • dba_hist_sqltext

  • dba_mviews

  • dba_views

  • dba_procedures

  • dba_constraints

  • dba_cons_columns

  • dba_tab_columns

  • dba_audit_trail (If you do not have Auditing configured, speak to KADA about it.)

  • dba_tab_privs

  • dba_role_privs

  • dba_roles

  • proxy_users_and_roles

  • dba_synonyms

Check the MAX_STRING_SIZE option in the database , if it is not of type EXTENDED and you are unable to change it, you will need to alter the DDL_SQL used by the extractor and change from 32767 to the maximum supported value in MAX_STRING_SIZE instead. If this is not aligned you will see an ORA-00910: specified length is too long for its datatype will be thrown.

You have the option to create a wallet if you are using Oracle Cloud for authentication, otherwise this integration will use a username and password.

If you are using TNSNAMES ensure the tnsnames.ora file is up to date with the correct entries to be referenced.

You can connect 3 ways.

  1. Host/servicename

  2. TNSNAME in the tnsnames.ora file

  3. A connection descriptor

‌

Step 1: Create the Source in K

Create a Oracle source in K

  • Go to Settings, Select Sources and click Add Source

  • Select "Load from File" option

  • Give the source a Name - e.g. Oracle Production

  • Add the Host name for the Oracle Server

  • Click Finish Setup

Step 2: Getting Access to the Source Landing Directory

Step 3: Install the Collector

It is recommended to use a python environment such as pyenv or pipenv if you are not intending to install this package at the system level.

Some python packages also have dependencies on the OS level packages, so you may be required to install additional OS packages if the below fails to install.

You can download the whl via Platform Settings → Sources → Download Collectors

Run the following command to install the collector

pip install kada_collectors_extractors_<version>-none-any.whl

You may require an ODBC package for the OS to be installed as well as an oracle client library package if do you not have one already, see https://www.oracle.com/au/database/technologies/instant-client.html

‌

Step 4: Configure the Collector

The collector requires a set of parameters to connect to and extract metadata from Oracle

FIELD

FIELD TYPE

DESCRIPTION

EXAMPLE

username

string

Username to log into Oracle

"myuser"

password

string

Password to log into Oracle


dsn

string

Datasource Name for Oracle, this can be one of the following forms <tnsname> <host/servicename>

"preprod"

oracle_client_path

string

Full path to the location of the Oracle Client libraries

"/tmp/drivers/lib/oracleinstantclient_11_9"

oracle_major_version

string

We currently support 11g and 12c, 11g has different SQL scripts so be very careful when setting this value

12c

database_name

string

The database name as onboarded in K, it is important that it matches so the objects are created correctly

"mykdatabase"

host_name

string

The host name as onboarded in K, it is important that it matches so the objects are created correctly

"mykhost"

wallet_path

string

If you use Oracle wallets, then this is the location of the wallet, ensure that the sqlora.net file references the wallet locaton correctly. If you do not use wallets, leave this blank.

"/tmp/drivers/oracle/wallet"

output_path

string

Absolute path to the output location where files are to be written

"/tmp/output"

mask

boolean

To enable masking or not

true