The steps taken and issues resolved when doing a first installation of the new Oracle BI Apps 11.1.1.7.1 release, including ETL set up to source data from an Oracle Siebel CRM database.

This blog post covers the steps taken and issues resolved when doing a first installation of the new Oracle BI Apps 11.1.1.7.1 release, including ETL set up to source data from an Oracle Siebel CRM database.

First Run: Four ETL Errors of Oracle BI Apps 11.1.1.7.1

After attending Oracle’s partner training for OBIA 11.1.1.7.1 I was warned by other early adopters that the installation and configuration may not go exactly to plan, as was to be expected of any new application. The actual installation and configuration of BIA went surprisingly smoothly following this document from Oracle (admittedly I was working with an entirely vanilla environment so was able to profit from the simplicity):

http://docs.oracle.com/cd/E38317_01/doc.11117/e35981/install.htm

We had also been warned that the first ETL would be the most arduous task since the transition from Informatica to ODI is not a 1:1 mapping. A great example is lookups in OBIA – in Informatica, lookup transformations were used that would return only one record for each lookup value ensuring that one row in was always one row out, lookups are not a feature of ODI and instead joins are used (inner, outer, etc.) which can return more than one record potentially causing duplicates in data and violating keys and constraints.

While that is something to bear in mind, in this situation I was working with an (almost) ‘empty’ Siebel database and therefore was not in danger of hitting data related issues in my ETL. The below errors are things I found and you may find helpful to know, from a purely configurative perspective.

Here’s my (original) setup: Oracle Linux 6 x64 VM with:

• ODI 11.1.1.7.0
• OBIEE 11.1.1.7.0 patched to 11.1.1.7.131017
• OBIA 11.1.1.7.1
• Oracle Database 11.2.0.1.0 (later upgraded to 11.2.0.3.0, see my first error)

Windows Server 2008 x86 VM with:

• Siebel 8.1.1.10
• Oracle Database 11.2.0.1.0 (later upgraded to 11.2.0.3.0)

Both VMs were connected over a wired internal network.

The ETL was run over a new vanilla Siebel database with just a few records inserted for testing so the ETL was a relatively quick process. I generated load plans using the BIA Configuration Manager (which by default if your environment is setup properly should be accessible at http://[ANALYTICS_HOST]:9704/biacm/) and set them off – below are the errors I encountered during this process:

1. No more data to read from socket

This error occurred intermittently across any load plan or session I tried to run in ODI, it didn’t appear to be related to any particular source or target. It turns out that OBIA 11.1.1.7.1 is not compatible with Oracle Database 11.2.0.1.0, upgrading to 11.2.0.3.0 resolves this error (Oracle Patch # 10404530).

1. ORA-0094: “FIND_AUDIT_TRAIL” Invalid Identifier

This error is caused by a missing stored procedure that comes packaged with apps, if you come across this error locate and execute the script FIND_AUDIT_VALUES.sql which can be found at $MIDDLEWARE_HOME /Oracle_BI1/biapps/etl/etl_stored_procs/oracle, execute the SQL as your DWH user [PREFIX]_DW as specified during the OBIA RCU.

1. ORA-14, TNS: Listener does not currently know of service requested in connect descriptor

During my database upgrade I changed the port number of the database and the JDBC connection to the data warehouse in ODI was connected to the wrong port. To resolve this, in the Topology Manager I navigated to the physical connection for BIAPPS_DW and updated the JDBC URL to include my new port number.

1. ORA-12516, TNS: Listener could not find available handler with matching protocol stack

I was unsure if this was somehow to do with the inelegant database upgrade carried out to resolve the socket error. Without the skills of a qualified DBA I was unable to fully understand the root cause of this, however thankfully somebody else had a better idea:

http://selvathiruppathi.blogspot.co.uk/2012/03/ora-12516-tnslistener-could-not-find_09.html

You may find this error is persistent and that restarting the database or listener does not resolve the issue - in my case I had situation whereby connection pools on my OLTP database were leaking and therefore the database reached maximum number of connections quickly and hung – as a workaround I increased the number of sessions and processes using the below SQL:

1. ALTER SYSTEM SET SESSIONS=300 SCOPE=SPFILE;
2. ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

This resolved my interim issue and I was able to complete a full ETL – but this will only work as a temporary fix since eventually if you don’t release those connection pools the number will eventually exceed 300, you should have a DBA look into the issue if this were to happen on a non-sandbox environment.

In the mean time you can monitor the number of sessions and processes being used by your database by executing this SQL.

SELECT (SELECT COUNT(*) FROM V$SESSION) SESSIONS, (SELECT COUNT(*) FROM V$PROCESS) PROCESSES FROM DUAL;

Upon completion of the ETL we have a sandbox environment with Siebel 8.1.1.10 (with Open UI) running on a server connected to a separate instance of OBIA 11.1.1.7.1, a great place to start to get to know the apps themselves a little better and to understand their relationship with Siebel!

If you would like to hear more about the latest Oracle BI Apps release and how it might help your business, please give us a call on +44 203 283 4315 or contact us here.