Oracle connector is used to connect to the Oracle database which lets the users perform read, write, and load functions based on their privileges. It allows for the seamless transfer of data and metadata between Oracle databases and other databases, data warehouses, and applications. The Oracle connector can also help streamline data management and enable efficient data analytics by providing access to data stored in Oracle databases.
OvalEdge uses a JDBC driver to connect to the data source, which allows the user to crawl and profile database objects (Tables, Table Columns, etc.) and build lineage.
Connector Capabilities
The connector capabilities are shown below:
Crawling
Feature |
Supported Objects |
Remarks |
---|---|---|
Crawling |
|
|
Profiling
Feature |
Supported Objects |
Remarks |
---|---|---|
Table Profiling |
Row Count, Columns Count, View Sample data |
Not Supported Data Types: "blob", "clob", "nclob","lob", "img", "binary", "bfile", "xmltype" |
View Profiling |
Row Count, Columns Count, View sample data |
View is treated as a table for profiling purposes. |
Column Profiling |
Min, Max, Null Count, Distinct, Top 50 values |
- |
Full Profiling |
Supported |
- |
Sample Profiling |
Supported |
- |
Lineage Building
Lineage entities |
Details |
---|---|
Table Lineage |
Supported |
Column Lineage |
Supported |
Lineage Sources |
Stored Procedures, Functions, Triggers, Views, SQL queries (from Query Sheet) |
Querying
Operation |
Details |
---|---|
Select |
Supported |
Insert |
Not supported, by default |
Update |
Not supported, by default |
Delete |
Not supported, by default |
Joins within database |
Supported |
Joins outside database |
Not supported |
Aggregations |
Supported |
Group By |
Supported |
Order By |
Supported |
Union |
Supported |
By default, the service account provided for the connector is used for any query operations. If the service account has write privileges, then Insert / Update / Delete queries can be executed.
Prerequisites
The following are prerequisites for connecting to the Oracle:
Drivers
The APIs/drivers used by the connector are given below:
Sl.No |
Driver / API |
Version |
Details |
---|---|---|---|
1 |
JDBC Driver |
19.3.0.0 |
https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 Note: The latest version is 23.3.x |
Configuring Environment Variables
Configuring environment names enables you to select the appropriate environment from the drop-down list when adding a connector. This allows for consistent crawling of schemas across different environments, such as production (PROD), staging (STG), or temporary environments. It also facilitates schema comparisons and assists in application upgrades by providing a temporary environment that can be later deleted if needed.
Before establishing a connection, it is important to configure the environment names for the specific connector. If your environments have been configured, skip this step.
Steps to Configure the Environment
- Log into the OvalEdge application.
- Navigate to Administration > System Settings.
- Select the Connector tab.
- Find the key name “connector.environment”.
- Enter the desired environment values (PROD, STG) in the Value column.
- Click ✔ to Save.
Service Account Permissions
The following are the privileges required for a service account to crawl and profile the data:
Operation |
Access Permission |
---|---|
Connection Validation |
Select All Tables |
Crawl Schemas |
Select All Tables |
Crawl Tables |
Select All Tables |
Profile Schemas, Tables |
Select All Tables |
Procedures, Functions, and Triggers |
Execute Procedure |
Establish a Connection
To connect to the Oracle using the OvalEdge application, complete the following steps:
- Log in to the OvalEdge application.
- Navigate to Administration > Connectors.
- Click on the + (New Connector) icon.
- Add Connector pop-up window is displayed where you can search for the Oracle connector.
- The Add Connector with Connector Type specific details pop-up window is displayed. Enter the relevant information to configure the Oracle connection.
Note: The asterisk (*) denotes mandatory fields required for establishing a connection.
Field Name
Description Connector Type
It allows you to select the connector from the drop-down list. By default, 'Oracle' is displayed as the selected connector type.
Connector Settings
Credential Manager*
Select the option from the drop-down list to indicate where you want to save your credentials:
OE Credential Manager: Oracle connection is configured with the basic Username and Password of the service account in real-time when OvalEdge establishes a connection to the Oracle database. Users need to add the credentials manually if the OE Credential Manager option is selected.
HashiCorp: The credentials are stored in the HashiCorp database server and fetched from HashiCorp to OvalEdge.
AWS Secrets Manager: The credentials are stored in the AWS Secrets Manager database server; OvalEdge fetches the credentials from the AWS Secrets Manager.
Azure Key Vault: Azure Key Vault allows for secure storage and strict access mechanisms of sensitive information such as tokens, passwords, certificates, API keys, and other confidential data.
For more information on Azure Key Vault, click here.
For more information on Credential Manager, click here.
License Add Ons
All the connectors will have a Base Connector License by default that allows you to crawl and profile to obtain the metadata and statistical information from a data source.
OvalEdge supports various License Add-Ons based on the connector’s functionality requirements.
- Auto Lineage: Select the Auto Lineage Add-On license that enables the automatic construction of the Lineage of data objects for a connector with the Lineage feature.
- Data Quality: Select the Data Quality Add-On license to identify, report, and resolve the data quality issues for a connector whose data supports data quality (DQ), using DQ rules/functions, anomaly detection, Reports, and more.
Connector Name*
Provide a connector name for the Oracle database in OvalEdge. This name will serve as a reference for identifying the Oracle database connection.
Example: "Oracle_Connection_test"
Connector Environment
The Connector Environment drop-down list allows you to select the environment configured for the connector from the drop-down list.
For example, PROD, or STG (based on the configured items in the OvalEdge configuration for the connector.environment).
The purpose of the environment field is to help you identify which connector is connecting what type of system environment (Production, STG, or QA).
Note: The steps to set up environment variables are explained in the Configuring Environment Variables section.
Server*
Specify the name of the Oracle database instance server IP/URL, which is accessible by the OvalEdge application.
Example:
IP: 190.x1.x3.xx90
Server: ovaloraclerdbms.com
Port*
By default, port number 1521, related to the Oracle database, is displayed. If needed a new port number can be provided.
Database*
Enter the source database name for crawling.
Database Type
Select the database type as SID or SERVICE_NAME.
Oracle SID is the unique name that uniquely identifies the instance/database and a service name is an alias for the Oracle server.
Driver*
A JDBC driver is a Java library file with the extension .jar that connects to a database. The driver details associated with the Oracle database will be auto-populated by default.
Username*
Enter the Service Account Username of the Oracle Server.
Password*
Enter the password of the Oracle server.
Connection String
Set the Connection string toggle button to automatic, to get the details automatically from the credentials provided. Alternatively, you can manually enter the string.
Format: jdbc:oracle:thin:@{server}:1521:{sid}
Example: jdbc:oracle:thin:@oe.com:1521:ovaledgedb
Plugin Server
Enter the server name if you are running this as a plugin.
Plugin Port
Enter the port number on which the plugin is running.
Default Governance Roles
Steward*
Select the Steward from the drop-down list options.
Custodian*
Select the Custodian from the drop-down list options.
Owner*
Select the Owner from the drop-down list options.
Governance Roles 4, 5, 6*
Select the respective user from the drop-down options.
Note: The drop-down list displays all the configurable roles (single user or a team) as per the configurations made in the OvalEdge Security > Governance Roles section.
Admin Roles
Integration Admins*
To add Integration Admin Roles, search for or select one or more roles from the Integration Admin options, and then click on the Apply button.
The responsibility of the Integration Admin includes configuring crawling and profiling settings for the connector, as well as deleting connectors, schemas, or data objects.Security and Governance Admins*
To add Security and Governance Admin roles, search for or select one or more roles from the list, and then click on the Apply button.
The security and Governance Admin is responsible for:- Configure role permissions for the connector and its associated data objects.
- Add admins to set permissions for roles on the connector and its associated data objects.
- Update governance roles.
- Create custom fields.
- Develop Service Request templates for the connector.
- Create Approval workflows for the templates.
No. of Archive Objects*
The number of archive objects indicates the number of recent metadata modifications made to a dataset at a remote/source location. By default, the archive objects feature is deactivated. However, users may enable it by clicking the Archive toggle button and specifying the number of objects they wish to archive.
Select Bridge*
With the OvalEdge Bridge component, any cloud-hosted server can connect with any on-premise or public cloud data sources without modifying firewall rules. A bridge provides real-time control that makes it easy to manage data movement between any source and destination. For more information, refer to Bridge Overview.
For more information, refer to Bridge Overview
- After entering all the connection details, select the appropriate button based on your preferences.
- Validate: Click on the Validate button to verify the connection details. This ensures that the provided information is accurate and enables successful connection establishment.
- Save: Click on the Save button to store the connection details. Once saved, the connection will be added to the Connectors home page for easy access.
- Save & Configure: For certain Connectors that require additional configuration settings. Click on the Save & Configure button. This will open the Connection Settings pop-up window, allowing you to configure the necessary settings before saving the connection.
- Once the connection is validated and saved, it will be displayed on the Connectors home page.
Note: You can either save the connection details first, or you can validate the connection first and then save it.
Connection Validation Details
S.No |
Error Message(s) |
Description |
1 |
Failed to establish a connection, please check the credentials. |
In case of an invalid username and password. |
Note: If you have any issues creating a connection, please contact your assigned OvalEdge Customer Success Management (CSM) team.
Connector Settings
Once the connection is established successfully, various settings are provided to fetch and analyze the information from the data source.
The connection settings include Crawler, Profiler, Query Policies, Access Instruction, Business Glossary Settings, Anomaly Detection Settings, and Others.
To view the Connector Settings page,
- Go to the Connectors page.
- From the 9- dots select the Settings option.
- The connector Settings page is displayed, where you can view all the connector setting options.
- Click on Save Changes. All the settings will be applied to the metadata.
The following is a list of connection settings along with their corresponding descriptions:
Connection Settings |
Description |
Crawler |
Crawler settings are configured to connect to a data source and collect and catalog all the data elements in the form of metadata. |
Profiler |
The process of gathering statistics and informative summaries about the connected data source(s). Statistics can help assess the quality of data sources before using them for analysis. Profiling is always optional; crawling can be run without profiling. |
Query Policies |
It restricts the use of the selected query types based on your role. |
Access Instruction |
Access Instruction allows the data owner to instruct others on using the objects in the application. |
Business Glossary Settings |
The Business Glossary Setting provides flexibility and control over how they view and manage term association within the context of a business glossary at the connector level. |
Anomaly Detection Settings |
Anomaly Detection Settings enable users to configure anomaly detection preferences at the connector level. By default, the configuration aligns with the global settings in System Settings and cannot be modified. In custom settings, users can activate or deactivate anomaly detection for a specific connector. They can also switch between the default Deviation or IQR algorithm and adjust associated parameters. |
Others |
The Enable/Disable Metadata Change Notifications option is used to set the change notification about the metadata changes of the data objects.
|
Note: For more information, refer to the Connector Settings.
Additional Crawl
The standard crawling involves using a "Database Metadata Utility" to retrieve metadata from a database. Metadata includes information about database objects, such as tables, columns, and relationships. However, in some cases, the metadata obtained from the standard crawl might be incomplete or insufficient for specific use cases.
In such scenarios, an additional crawl can be triggered to extract more detailed metadata from specific database tables. This process ensures that the necessary metadata, often missed in standard crawls, is captured to provide a complete database view.
For example, in an Oracle database, additional crawls might retrieve metadata from the following tables:
- ALL_TAB_COMMENTS: This table provides comments or descriptive information related to regular tables, offering insights into each table's purpose or structure.
- DBA_TAB_COMMENTS: Similar to ALL_TAB_COMMENTS, but at a more privileged level. It provides comments on tables across the entire database, accessible to users with higher-level privileges.
- ALL_MVIEWS: Retrieves metadata about materialized views, which are database objects that store query results for efficiency.
- DBA_COL_COMMENTS: Contains comments for individual columns within tables, offering detailed descriptions of column-level data.
To access these tables for an additional crawl, certain privileges are required for the service account:
- DBA_TAB_COMMENTS and DBA_COL_COMMENTS: Access to these tables requires the SELECT ANY DICTIONARY privilege, which allows the user to query data dictionary views across the entire database.
GRANT SELECT ANY DICTIONARY TO <user>; - ALL_TAB_COMMENTS and ALL_MVIEWS: No special privileges are needed. The user can query these tables for objects they can access in the database.
Crawling of Schema(s)
A Crawl/Profile option allows you to select the specific schemas for the following operations: Crawl, Crawl & Profile, Profile, or Profile Unprofiled. For any scheduled crawlers and profilers, the defined run date and time are displayed to set.
- Navigate to the Connectors page, and click on the Crawl/Profile button.
Select Important Schema For Crawling and Profiling pop-up window is displayed. - Select the required Schema(s)
- The below list of actions is displayed in the Action section.
- Crawl: It allows the crawling of the metadata of the selected schemas.
- Crawl & Profile: It allows crawling the metadata of the selected schemas and profiles the sample data.
- Profile: It allows the collection of table column statistics.
- Profile Unprofiled: It allows the profiling of data that has not been profiled.
- Schedule: Connectors can also be scheduled for crawling and/or profiling in advance to run at prescribed times and selected intervals.
Note: For more information on Scheduling, refer to Scheduling Connector.
- Click on the Run button that gathers all metadata from the connected source into the OvalEdge Data Catalog.
Copyright © 2024, OvalEdge LLC, Peachtree Corners GA USA