Oracle

Required Information

To configure Oracle in Alation, you will need the following information:

  • Hostname or IP address of your server

  • Port number - default is 1521

  • Service name

  • Kerberos information (if using Kerberos)

  • Service account

  • Query Log Ingestion setup:  Custom View

../../_images/DS_Oracle01.png

Synonym Extraction

Applies from release V R7 (5.12.x)

For Oracle sources, Alation can extract both private and public synonyms if synonyms extraction is enabled in Labs/Feature Configuration. All extracted public synonyms can be located under the schema called PUBLIC on the catalog page of your data source. This schema is used in Alation to only surface the synonym object pages to users - it is created by Alation and is not present in the database itself.

Private synonyms will be extracted together with their respective schemas if these schemas are extracted.

Preliminaries

Firewall Configuration

  • Open outbound TCP port 1521 to Oracle server

  • DNS Record (if using Kerberos authentication)

    • Oracle Kerberos authentication requires Alation server hostname to be resolvable. Add a DNS record for Alation server or edit /etc/hosts file on the Alation server.

Create Service Account

Sample SQL to create an account

CREATE USER alation IDENTIFIED BY [password];
GRANT CREATE SESSION TO alation;

Metadata Extraction

GRANT SELECT ON DBA_OBJECTS TO alation;
GRANT SELECT ON DBA_TAB_COLUMNS TO alation;
GRANT SELECT ON DBA_CONS_COLUMNS TO alation;
GRANT SELECT ON DBA_CONSTRAINTS TO alation;
GRANT SELECT ON DBA_VIEWS TO alation;
GRANT SELECT ON DBA_IND_COLUMNS TO alation;
GRANT SELECT ON DBA_INDEXES TO alation;
GRANT SELECT ON DBA_SYNONYMS TO alation;
GRANT SELECT ON DBA_COL_COMMENTS TO alation;
GRANT SELECT ON DBA_MVIEW_COMMENTS TO alation;
GRANT SELECT ON DBA_TAB_COMMENTS TO alation;
GRANT SELECT ON DBA_SEGMENTS TO alation;
GRANT SELECT ON DBA_LOBS TO alation;

Permission

Purpose

GRANT SELECT ON DBA_OBJECTS TO alation;

Required for table extraction.

GRANT SELECT ON DBA_TAB_COLUMNS TO alation;

Required for table and column extraction.

GRANT SELECT ON DBA_CONS_COLUMNS TO alation;

Required for column extraction.

GRANT SELECT ON DBA_CONSTRAINTS TO alation;

Required for primary key and foreign key extraction.

GRANT SELECT ON DBA_VIEWS TO alation;

Required for view extraction.

GRANT SELECT ON DBA_IND_COLUMNS TO alation;

Required for index extraction.

GRANT SELECT ON DBA_INDEXES TO alation;

Required for index extraction.

GRANT SELECT ON DBA_SYNONYMS TO alation;

Required for synonym extraction.

GRANT SELECT ON DBA_COL_COMMENTS TO alation;

Required for source comments extraction.

GRANT SELECT ON DBA_MVIEW_COMMENTS TO alation;

Required for view comments extraction.

GRANT SELECT ON DBA_TAB_COMMENTS TO alation;

Required for table comments extraction.

GRANT SELECT ON DBA_SEGMENTS TO alation;

Required for table iteration.

GRANT SELECT ON DBA_LOBS TO alation;

Required for table extraction.

Table Profiles

GRANT SELECT on [SCHEMA or TABLE]

Query History

Active Session History (ASH, sometimes called AWR  tables).

Active session history is recorded by default and is the most commonly used method for capturing query history for Alation. ASH only records some of the queries because it samples them on an interval (one every ten seconds). This means that the Lineage feature is not fully supported when using ASH. Some lineage may appear but many links will be missing. Popularity will not be as accurate with ASH compared to audit but over many samples it should still be a good approximation.Oracle Enterprise Edition and the Diagnostics and Tuning option are required for Active Session History.

ASH query ingestion requires access to a view on top of:

  • dba_hist_active_sess_history

  • dba_users

  • dba_hist_sqltext tables

See appendix A.4 Oracle Query Log View <Appendix_4> for the view definition.

Driver

Refer to Support Matrix for the certified driver version.

Enable the Default Schema Extraction

Applies from 2020.4

When configuring metadata extraction on the Metadata Extraction tab of the Settings page, enable the Enable default schema extraction checkbox to extract all the default schemas.

Important

Enable this checkbox only if required since extracting all the default schemas will make the metadata extraction process slower.

../../_images/Oracle_01.png