OBIEE 11g – Error selecting ”Coreapplication” in Oracle Enterprise Manager (ADF_FACES-60097, ADF_FACES-60096)

When we logged into Oracle Enterprise Manager (EM) and select ‘Coreapplication’ in the left pane , we encouter the error; “ADF_FACES-60097 : For more information, please see the server’s error log for an entry beginning with: ADF_FACES-60096: Server Exception during PPR, #1”

This seems like a known bug so we followed the steps in Doc Id 1363602.1. Unfortunately that didn’t solve the problem.

Oracle Support advised us to increase the logging for the EM as shown in Doc ID 1464333.1. This resulted in Error Logging in one of the AdminServer-diagnostic Log Files. A Fatal Error occurred reading this file: ‘FMWhome/user_projects/domains/lnbisfoundation_domain/sysman/mds/partition1/ai/bi/fragments/mdssys/cust/user/weblogic/deployment.jspx.xml’

Investigating the structure of this file turned out that for some reason this file was empty (zero bytes). Removing the file and restarting the environment did the trick for us. The file got recreated and ‘Coreapplication’  could be selected again.

[Update 09/14/2015 – FMWhome/user_projects/domains//sysman/mds/partition1/ai/bi/mdssys/cust/user//instance.jspx.xml
can also be zero bytes. A delete and a restart can fix the EM.]

– Daan

Solving ORA-01722 – Invalid Number while navigating from Oracle BI 11g to Oracle eBS R12


In a previous blog post, I covered how to navigate from Oracle BI 11g to Oracle eBS R12. While setting this up, I had some challenges to overcome. One of them was the following. When I clicked the column with the Action Link, the following error showed up;

The Oracle eBS Server was available, so that couldn’t be the problem. Checking the logfile(s) showed that there was a ORA-01722 error involved.

The question at this point is; Which query is throwing a ‘Invalid Number’-error. Thanks to Robin I got the idea to Sql Trace the Connection Pool which is being used for the Action Link.

Via the generated Trace Files I was able to identify the problem – query. It turned out to be the query which constructs the Url to navigate to Oracle eBS.

select fnd_run_function.get_run_function_url
 ( cast
 ( fnd_function.get_function_id ( 'AP_APXINWKB_SUMMARY_VIEW' ) as number )
 , cast ( '200.00' as number )
 , cast ( '*****' as number )
 , cast ( '0.00' as number )
 , 'INVOICE_ID=*****'
 , null ) as action_link
 from DUAL

A quick check in SQL*Plus gave the same error. The problem is in the Cast functions and the NLS_NUMERIC_CHARACTERS-settings.

An easy;

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;

….. solved the problem in SQL*Plus.

In Oracle BI, I added the ‘Alter Session’-Statement in the Connection Pool which is being used for the Action Link.

Problem Solved.

– Daan

Navigating from Oracle BI 11g to Oracle eBS R12

One of the interesting features of Oracle BI 11g is the Action Framework. This feature makes it possible to e.g. navigate from Oracle BI 11g to Oracle eBS R12. When you have a look at the different Oracle BI(A) presentations from Oracle you will see a slide regarding Action Links ‘Insight to Action’.

It looks like this is standard Out-of-the-Box functionality. This is partly true. You should have at least the Oracle BI 11g & Oracle eBS R12 integration in place. Next to that there is some additional configuration / implementation required.

Oracle BI configuration

  • Enable Action Link to Oracle eBS (ActionFrameworkConfig.xml)

Navigate to the following location

ORACLE_MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/config/fmwconfig/biinstances/coreapplication

Edit the ActionFrameworkConfig.xml

After you have modified the ActionframeworkConfig.xml file, you have to restart the Managed Server in Weblogic that is hosting your Oracle BI EE environment.

  • Oracle BI Connection Pool

Edit / Add a Connection Pool entry in the Oracle BI Administration Pool. This Connection Pool will be used to connect to Oracle BI Applications

Note  

I – the Application Role must have privileges to execute direct database requests against the Oracle eBS Connection Pool.

II –  for the Application Role (responsibility) to successfully invoke a Navigate to E-Business Suite action, the target Oracle eBS function must be accessible from the user’s current Oracle eBS Context.

Identify Oracle eBS Form Parameter

If you want to navigate to a query the form you are navigating to, you should identify the Oracle eBS Form Parameter. This can be achieved by the following steps.

1. Log into Oracle eBS and select the Form you want to navigate to

2. Identify the name of the Form and the Function which calls this Form

Navigate to the Forms Personalization Screen via Help, Diagnostics, Custom Code, Personalize

  • Forms
  • Function

We will use the name of the Form to identify the possible parameters of this Form. In this case;  APXINWKB. The Function Code will be used later on when creating the actual Action Link.

You can download the related .fmb-file from the Oracle eBS Application Server in the following location; $AU_TOP/forms/NL. The ‘NL’-part refers to the language.

When you open the .fmb-file in Oracle Forms, you should be able to locate the parameters


Action Link

Now everything is in place to create the actual Action Link.

  • Create Action Link

Create the Action Link via New, Action, Navigate to E-Business Suite 

  • Edit Action Link

Now we must edit the specific Action Link details

This consists of;

  • The Function Code (AP_APXINWKB_SUMMARY_VIEW)
  • The Oracle BI Connection Pool (Oracle EBS OLTP Connection Pool – Action Framework)
  • Parameter(s) –> INVOICE_ID

With this Action Link it is possible to call this link directly end fill in the Invoice Id yourself.

Another option is to make the Action Link part of an Oracle BI Answers Request.

  • Edit Oracle BI Answers Request

Select and edit a column in an Oracle BI Answers Request. Navigate to the Interaction Tab. Here we can add / edit an (existing) Action Link.

I have chosen to use a Invoice Number with an Action Link which uses a hidden (Invoice Id) column to navigate to Oracle eBS. This way I do not have to show the Invoice Id in the Oracle BI Answers Request. Still I can use the Invoice Id as a parameter in the Oracle eBS Form.

It’s a little bit of work, but still it’s a nice feature.

– Daan

Patching in Oracle BI 11g 11.1.1.6

Last week the announcement came that there has been a Patch (13742915) released for Oracle BI EE 11.1.1.6.1. This Patch can be dowloaded via Oracle Support (Login required).

If you want to apply the Patch, you have to perform a few steps. These steps can differ, depending on which steps you may or may not already have executed.

Start by downloading the Patch first.

Download Patch 13742915
——————————–

1. Navigate to ‘Patches & Updates’
2. Search for Patch 13742915
3. Select the required OS (I installed on Windows 64-bit)
4. Dowload the Patch

When the Patch has been dowloaded, go to your Oracle BI environment for the installation of the Patch. The following steps have to be performed.

Patch Installation for server
——————————–
1. Stop all BI Services, including BI Server, BI Administration Tool, BI Javahost, BI Scheduler (Start->Programs->Oracle BI->Stop BI Services)
2. Backup ORACLE_HOME\bifoundation\server
3. Backup ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository
4. Apply Patch using Opatch (See below)
5. Start all BI Services
6. Test the Patch
7. If needed, rollback the Patch (See below)

Apply Patch using Opatch
——————————–

Note: Make sure all BI Services have stopped

Check Oracle Support; ‘OBIEE 11g: How to Apply Patches Using Opatch’ (Id – 1220799.1)

1. Verify whether you have (the latest) version of Opatch installed

a) If needed install a new version of the Opatch utility (See below)
Make sure ….

… the ORACLE_HOME variable exists –> set ORACLE_HOME=MIDDLEWARE_HOME\Oracle_BI1

—-
Side-note from Oracle: “You are setting ORACLE_HOME for this command-prompt session only.
— This is set to only facilitate patch application/rollback/inventory, etc.
— Do not make this a permanent environment variable as it will affect other Oracle Products running
— on your system which may need a permanent ORACLE_HOME environment variable.”
—-

… the OPATCH-directory is in your PATH –> set PATH=%PATH%;ORACLE_HOME\OPatch

2. Read through the README.txt
3. Download the Patch to the PATCH_TOP directory (in my case; Z:\Oracle\FMW\OracleBIAdmin\PATCH_TOP)
4. CD to Patch directory (in my case; Z:\Oracle\FMW\OracleBIAdmin\PATCH_TOP\13742915)
5. Apply Patch
a) run “opatch apply” (without quotes)
— If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit
b) run “patch apply -jre %ORACLE_HOME%/jdk/jre” (without quotes)
6. OPatch succeeded
7. Verify the succesful installation
a) run “opatch lsinventory” (without quotes)
— If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit
b) run “opatch lsinventory -jre %ORACLE_HOME%/jdk/jre” (without quotes)
8. OPatch succeeded

Note: All BI Services can be started

Rollback the Patch using Opatch
——————————–
1. CD to Patch directory (in my case; Z:\Oracle\FMW\OracleBIAdmin\PATCH_TOP\13742915)
2 Rollback Patch
a) run “opatch rollback -id 13742915” (without quotes)
— If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit
b) run “opatch rollback -id 13742915 -jre %ORACLE_HOME%/jdk/jre” (without quotes)

How to install the Opatch utility
—————————————

1. Navigate to ‘Patches & Updates’
2. Search for Patch 6880880
3. Select the required OS (I installed on Windows 64-bit)
4. Dowload the Patch
5. Extract the zip file directly under the ORACLE_HOME (MIDDLEWARE_HOME\Oracle_BI1).
6. Backup ORACLE_HOME\OPatch
7. Make sure no directory ORACLE_HOME\OPatch exist
8. Unzip the OPatch downloaded zip into the ORACLE_HOME directory
9. check the version of the OPatch utility
a) ORACLE_HOME\OPatch
b) run “opatch version” (without quotes)

Good Luck!

Upgrading from 10g to 11g (Update)

In preparation of an Oracle BI Applications (OBIA) upgrade to OBIA 7.9.6.3, I have been running the Oracle BI 11.1.1.6 Upgrade Assistant. Just to see whether we could expect (serious) issues. On the other hand I was curious how the Oracle BI 10g Security Groups would upgrade to the Oracle BI 11g Application Roles. Application Roles are managed via the the Enterprise Manager (Policy Store – system-jazn-data.xml). Actually Rittman Mead are running a 5-part series on Oracle BI 11g Security at the moment.

The Oracle BI Application Security is configured as follows in Oracle BI 10g. The responsibilities in Oracle eBS are created as groups in Oracle BI. After upgrading to 11g these groups are created as Applications Roles. If you check the in the Oracle BI 11g Weblogic Console, you would see that the responsibilities are also created as groups. These groups are created for Authentication (confirming the identity of a user) purposes. The Application roles are created for the Authorization (specifying access rights).

I have been blogging about the upgrade from 10g to 11g earlier. There doesn’t seem to be any major difference in the Upgrade Assistant in 11.1.1.6. You basically have to follow the same steps as in the previous versions.

After the upgrade I ran the Consistency Checker. A few ‘Warnings’ caught my eye. I haven’t seen those before so I guess they are introduced in Oracle BI 11g.

–> NQSError 39051 (Warnings) – Application Role “*****” is not defined in the Enterprise Manager.

There are 2 options to solve these errors:

Remove the Application Role from ….

  • …. the Policy Store via the Enterprise Manager
  • …. the Reporsitory (RPD) via the Identy Manager in the Oracle BI Administration Tool.

–> NQSError 39062 (Warnings) Initialization Block ‘*****’ uses Connection Pool ‘”*****”.”*****”‘ which is used for report queries. This may impact query performance.

It’s common practice to create an additional Initialization Block for Session variables. Check Nicolas’ blog for more details on the Oracle BI Connection Pools.

Cheers.

Oracle Business Intelligence Enterprise Edition (OBIEE) 11.1.1.6.0

Although Oracle Business Intelligence Enterprise Edition (OBIEE) 11.1.1.6.0 has already been released a few weeks ago, I thought I would be nice to write some sort of wrap-up post with some interesting links to documents and various websites.

The latest version of Oracle BI 11g is available on OTN as well as on eDelivery.

Of course you should start by reading the documentation. The 11.1.1.6.0 docs have been duplicated compared to 11.1.1.5.0. Maybe because there is an additional part reserved for Oracle Exalytics. As always there is a lot of buzz around the Oracle BI Blogosphere. Christian has a nice up-to-date Link Library with a list of interesting websites with regards to 11.1.1.6 documentation and functionality posts/docs/etc.

Some interesting documents

Oracle BI 11.1.1.6.0 opens the door for Oracle Exalytics. When you want to get more information about Oracle Exalytics, check the following sites;

Happy Upgrading!

OBIEE 11.1.1.5 – Sample Application (V107)

The new Oracle BI EE 11.1.1.5 Sample Application (V107) is available for download on OTN. Previous releases only provided install files. This time Oracle added an additional option via a VirtualBox-image.

I hope it was worth the wait.