The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

DB Link with Oracle BI Applications

Authors: Simon Miller, Director OBIA, Oracle Customer Engineering and Advocacy Lab (CEAL team) and Patrick Block, Principal, OBIA Development

In an earlier blog post 3 Modes for Moving Data to the BI Applications DW from a Source Application Database the available options for setting up data source connections was provided in detail, along with screenshots.  It should be noted this information written for OBIA is still applicable for While not all customers will deploy the SDS (Source Dependent Store with GoldenGate), most customers should consider using the DB link option. The DB Link allows for direct Oracle database to Oracle database communication, and the data won’t need to flow through your ODI Agent/WLS.  The objective of this blog is to elaborate on the above mentioned post and add tips and troubleshooting guidance. 

Firstly, the naming conventions required can be a bit tricky, and while it looks odd, it does work. When your DBLink is setup, you can test it like this:

In the above example, 310 is the DSN number defined in the BIACM and EBS11510 is the DSN Name.
This DBLink was setup with the following syntax:
    CONNECT TO "SDS_EBS11510_FULL" IDENTIFIED BY VALUES '054339818D629B44BA4880F2536FBAB4C0223F5FAC15FE03350F952A3FB376523F'
(CONNECT_DATA= (SID=dev159a)))';

If you get an error when trying to create the db link then check to see if your GLOBAL_NAMES initialization parameter is set to TRUE. This requires the database link name to match the remote database's global name.  

When FALSE you can name the database link whatever you want (what you’ll require), see:
Oracle Database 12.1 documentation, Names for Database Links

You can find the <DATASERVER_NAME>  and <DSN_ID> from the BIACM or ODI Studio Client. In the BIACM the data server name is called ‘Oracle Data Integrator Server Name’ and DSN ID is called ‘Data Source Number’.

There are a couple of other gotchas you should be aware of to avoid other commonly seen errors.  We are ultimately looking for a DB Link whose name corresponds to the Remote Object Mask below where %SCHEMA corresponds to the 'Instance / dblink' property, %OBJECT is the hardcoded value 'WORLD' and %DSERVER is derived based on the datasource num id.  The screenshots below are for an EBS R12.1.3 source with DSN name EBSR1213_DEFAULT and DSN ID 335. Make sure the ‘Instance/dblink (Data Server)’ property matches the ‘Name’ property. The ‘Name’ corresponds to the ‘Oracle Data Integrator Data Server Name’ property as seen in the BIACM when editing your source.

While this should be set automatically when defining your data sources, you should verify the ‘default’ checkbox is checked in the physical schema (in ODI Studio Client) and the ‘Local Object Mask’ and ‘Remote Object Mask’ are configured correctly.

Finally, please continue to refer to the 3 Modes for Moving Data to the BI Applications DW from a Source Application Database post, which explains how to let the BIACM know to use the DBLink.

Join the discussion

Comments ( 2 )
  • Venkat Friday, January 23, 2015

    OBIA 11 Team,

    Nice to see a post after long time

    1. If I go with 'JDBC mode', instead of 'Database Link mode', as my DBA normally don't allow DB Links to be created,

    What are the high level steps to identify ELT bottlenecks?

    2. Does this ODI Agent work in asynchronous mode, with reader, transformation and writer threads working simultaneously each processing a batch of records, just like Inforamtica IS threads?

    3. In case ODI agent doesn't work like Infa IS threads, what would happen while reading data from huge source tables (ex: payroll from EBS/PSFT) via 'JDBC mode'? ODI agent wait until it has all source records before it writes to TEMP table?

    4. Also isn't I$_W_PAYROLL_FS an extra insert to do every time I try to load W_PAYROLL_FS? One more insert I$_W_PAYROLL_F for W_PAYROLL_F? Isn't this ELT approach lengthier than ETL approach?

    5. Based on the problems reported from customers, what are the most common performance issues in loading OBAW star schemas using ODI, particularly when customers are dealing with huge amount of data?

    Thanks in advance


  • Venkat Gali Tuesday, January 27, 2015

    Hello OBIA 11 Team,

    Are there any known issues and workarounds to make OBIA Rel8 work on Exadata? Especially around

    1.setting up ODI,BICM etc rpd on Exadata

    2. ETL Index drop and creation during batch loads

    3. OBIEE query performance due to Exadata index

    Planning to setup project analytics for PSFT


    Venkat Gali

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.