How to Configure a Manual Dictionary Mapping for an Oracle RAC Database

Oracle Real Application Clusters (RAC) allow for a multi-node setup of a single database. This means that every time an application connects to the database, it might be connected to a different node. IBM Automatic Data Lineage uses some of the node identification in so-called dictionary mappings to point, for example, an ETL or reporting tool database connection to the correct database instance. This article explains how to configure a manual dictionary mapping so that it works correctly no matter which node database Automatic Data Lineage is connected to.

Additional Information

Every time the database is scanned, Automatic Data Lineage generates a dictionary mapping in the form:

"Global database name";"Database instance name";"Host name";"Port";"Dictionary ID";"Connection ID";"Included schemas";"Excluded schemas"
"DWHP";"DWHP1";"pbr03db01";"1521";"DWHP";"";"DWH|ADS|VDS_.*;""

A second run against the same database may (due to RAC) result in slightly different information being captured.

"Global database name";"Database instance name";"Host name";"Port";"Dictionary ID";"Connection ID";"Included schemas";"Excluded schemas"
"DWHP";"DWHP2";"pbr03db02";"1521";"DWHP";"";"DWH|ADS|VDS_.*;""

Instructions

To set up a manual dictionary mapping for such cases, it is necessary to:

  1. Make sure that the JDBC connection string is using the service name, not the SID (otherwise, the generated dictionary mapping should be always the same as the connection that goes against a single node); for example:
    jdbc:oracle:thin:@prd03db.xs.cp.com:1521/X_DWHP

  2. The manual dictionary mapping typically only changes the value in the Connection ID column. For RAC, we need to change the value of Database instance name from the extracted value (which is the database SID) to the service name.

    "Global database name";"Database instance name";"Host name";"Port";"Dictionary ID";"Connection ID";"Included schemas";"Excluded schemas"
"DWHP";"X_DWHP";"pbr03db02";"1521";"DWHP";"";".*;""

    When the value in the Database instance name column matches the actual Oracle service name, the value of the Host name column is ignored. See Alias (Manual) Connection Mappings Explained for more details.

  3. Now, this manual dictionary mapping will automatically apply to both of the auto-generated dictionary mappings.