Skip to main content

Generate lineage for Oracle assets
App

You can use Oracle Miner to extract query history from Oracle AWR snapshots, write the extracted queries, and run the Generic Miner package to generate lineage. After you set up Oracle and crawl Oracle metadata, create an ingestion workflow in Atlan to generate lineage for existing Oracle assets.

Prerequisites​

Before you begin, make sure you have:

  • Access to the Oracle Miner package in the Atlan marketplace. If you don't have access, contact Atlan Support by submitting a request.
  • Set up Oracle by following Set up Oracle.
  • Crawled Oracle metadata by following Crawl Oracle.
  • Oracle 12c or later with Diagnostics Pack and AWR enabled.
  • AWR snapshot retention and interval aligned to your workflow schedule.
  • A user with access to the AWR views used by the workflow.
  • AWR snapshots available for the time range you plan to extract.

Before you begin​

Validate your Oracle environment so the workflow can extract query history from AWR snapshots.

  1. Check the Oracle version.

    SELECT VERSION FROM V$INSTANCE;

  2. Verify that AWR is enabled.

    SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'statistics_level';

    If statistics_level is BASIC, enable AWR.

    ALTER SYSTEM SET statistics_level=TYPICAL SCOPE=BOTH;

  3. Review the AWR snapshot retention and interval.

    SELECT * FROM DBA_HIST_WR_CONTROL WHERE SRC_DBNAME='<database-name>';

    Update the retention and interval if needed.

    BEGIN
        DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
            retention => 20160,
            interval  => 60
        );
    END;

  4. Grant access to the AWR views for the Oracle crawler user.

    GRANT SELECT ANY DICTIONARY TO your_user;
    GRANT SELECT ON DBA_HIST_SQLTEXT TO your_user;
    GRANT SELECT ON DBA_HIST_SQLSTAT TO your_user;
    GRANT SELECT ON DBA_HIST_DATABASE_INSTANCE TO your_user;

  5. Verify that AWR snapshots are generated.

    SELECT SNAP_ID, BEGIN_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT;

    If needed, create a snapshot manually.

    BEGIN
        DBMS_WORKLOAD_REPOSITORY.create_snapshot;
    END;

Create workflow​

Create an ingestion workflow to extract query history from Oracle and generate lineage between existing Oracle assets.

  1. In the top right of any screen in Atlan, click New and then click New workflow.

  2. From the list of packages, select Oracle Miner and then click Setup Workflow.

  3. In Extraction method, select Direct.

  4. In Start date, select the earliest date you want to extract query history from.

  5. In Oracle connection, select the Oracle connection you crawled earlier.

  6. In Output option, select Preview.

    Review the preview output to confirm the matches meet your expectations.

  7. Update Output option to Generate lineage and run the workflow again. This applies the matches as lineage relationships in Atlan.

  8. Verify the lineage in Atlan.

After ingestion completes, your existing Oracle assets are enriched with lineage generated from Oracle query history.

Need help​

If you need help configuring the workflow, reach out to Atlan Support by submitting a request.

See also​