Since we recently developed automated Discoverer report migration into Blitz Report and our development team needed test environment,  we had to integrate Discoverer with Oracle EBS. In this post we share our experience.

Preparation


Referenced Oracle notes:

Oracle Fusion Middleware Installation Guide for Oracle Portal, Forms, Reports and Discoverer
Using Discoverer 11.1.1 with Oracle E-Business Suite Release 12 (Doc ID 1074326.1)

Certification information:

http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html
http://www.oracle.com/technetwork/middleware/downloads/fmw-11gr1certmatrix.xls

Configuration:

According to following link “Discoverer 11g release 11.1.1.7.0 is the terminal release of Discoverer”:

http://www.oracle.com/technetwork/developer-tools/discoverer/overview/discoverer-sod-jan2009-132849.pdf

Discoverer 11.1.1.7.0 was installed on a separate node. Then it was integrated with existing EBS 12.1.3 installation on other server. Configuration is presented in the following tables:

Node name Repository DB Discoverer OS Version
discoverer-enginatics Yes Yes Oracle Linux 6.10
Node name DB EBS
r1213a.enginatics.com Yes Yes

Install Oracle Database for Discoverer repository


References:

Master Note of Linux OS Requirements for Database Server (Doc ID 851598.1)
Requirements for Installing Oracle 11gR2 RDBMS on RHEL6 or OL6 64-bit (x86-64) (Doc ID 1441282.1)
Certification Information for Oracle Database on Linux x86-64 (Doc ID 1304727.1)
Quick Installation Guide 11g Release 2 (11.2) for Linux x86-64:
http://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm#CEGHFFGG

Checking hardware prerequisites

Memory requirements

Recommended: 2 GB of RAM or more
Actual: 4 GB

Command used:

grep MemTotal /proc/meminfo

Swap space

Required: 1.5 times the size of the RAM (Note: Depends on the RAM available)
Actual: 4 GB

Shared memory

Required: at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET
Actual: 1.9G

To determine the amount of shared memory available, enter the following command:

df -h /dev/shm/

Disk space requirements for /tmp directory

Required: 1GB or more
Actual: 39 GB

Command used:

df -h /tmp

Disk space requirements for Oracle Database installation

Required: 6.4 GB (1.7 for data files, 4.7 for Oracle Home)
Actual: 39G

df -h /u01

Downloading and staging software

After getting Oracle Database 11.2.0.4 distribution, place archives in one folder and unzip them.

Checking software prerequisites

Oracle RDBMS Pre-install RPM

To simplify installation Oracle RDBMS Pre-install RPM was used. When it is installed, it does the following:

* Automatically installs any additional packages needed for installing Oracle Grid
Infrastructure and Oracle Database 11gR2 (11.2.0.4).

* Creates an oracle user, and creates the oraInventory (oinstall) and OSDBA (dba) groups for
that user. For security purposes, this user has no password by default and cannot login
remotely. To enable remote login, please set a password using the “passwd” tool.

* Sets and verifies sysctl.conf settings, system startup parameters, user limits, and driver
parameters to the minimum acceptable values based on recommendations from the Oracle Database Installation Guide and the Oracle Validated Configurations program.

[oracle@sniperhost1 11.2.0.2]$ yum install oracle-rdbms-server-11gR2-preinstall.x86_64

Kernel version

Required: On Oracle Linux 6 2.6.32-100.28.5.el6.x86_64 or later
Actual: 4.1.12-124.16.4.el6uek.x86_64

Command to check:

cat /proc/version

Package requirements

Pre-install rpm should have installed all the required packages, but it’s always good to check once more. Following command was used to do this:

rpm -q binutils-2* compat-libcap1-1.10-1 compat-libstdc++-33-3.2.3-69.el6 gcc-4* gcc-c++-4* glibc-2* glibc-devel-2* ksh libgcc-4* libstdc++-4* libstdc++-devel-4* libaio-0.3.107-10.el6 libaio-devel-0.3.107-10.el6 make-3.81-23.el6.x86_64 sysstat-9.0*

 

Creating OS users and groups

The following local operating system groups and users are required if you are installing Oracle Database:

The Oracle Inventory group (typically, oinstall)
The OSDBA group (typically, dba)
The Oracle software owner (typically, oracle)

Fortunately for us, pre-install rpm performed all the work. So we can just check the results:

Configuring kernel parameters

Pre-install RPM helped with this task also. Open /etc/sysctl.conf file and verify that there are added lines with comments, that pre-install rpm did all the work.

Configuring resource limits

Again just open file /etc/security/limits.conf and verify that all parameters are already set up.

Creating required directories

Create a directory for Oracle Database installation. The directory should have oracle user and oinstall group as owner. Frequently oinstall group is ignored and dba group used instead. It works fine, but technically speaking it’s not a correct approach.

mkdir -p /u01/db/
chown -R oracle:oinstall /u01/db/
chmod -R 775 /u01/db/

Installing Oracle Database

Go to the stage directory and launch runInstaller script as oracle user.

Following error was faced at this stage:

>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

To resolve it just execute following command as root and relaunch runInstaller:

xhost +

Installation screens are presented below:

 

After successful installation execution of root scripts is required:

After successful installation execution of root scripts is required

 

For convenience it’s better to create environment file:

[oracle@discoverer-enginatics ~]$ cat DB_DISCO.env
export ORACLE_HOME=/u01/db/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=DISCO
export PATH=$ORACLE_HOME/bin:$PATH

Apply environment file, connect to instance as sysdba and run query to perform system check:

Discoverer pre-requisite checks


Before installing discoverer software following checks are required.

OS checks

Check that system has following packages installed:

binutils-2.20.51.0.2-5.28.el6
compat-libcap1-1.10-1
compat-libstdc++-33-3.2.3-69.el6 for x86_64
compat-libstdc++-33-3.2.3-69.el6 for i686
gcc-4.4.4-13.el6
gcc-c++-4.4.4-13.el6
glibc-2.12-1.7.el6 for x86_64
glibc-2.12-1.7.el6 for i686
glibc-devel-2.12-1.7.el6 for i686
libaio-0.3.107-10.el6
libaio-devel-0.3.107-10.el6
libgcc-4.4.4-13.el6
libstdc++-4.4.4-13.el6 for x86_64
libstdc++-4.4.4-13.el6 for i686
libstdc++-devel-4.4.4-13.el6
libXext for i386
libXtst for i386
libXi for i386
openmotif-2.2.3 for x86_64Footref 7
openmotif22-2.2.3 for x86_64Footref 7
redhat-lsb-4.0-3.el6 for x86_64
sysstat-9.0.4-11.el6

Check them with ‘rpm -q’ command.

rpm -q binutils-2* compat-libcap1-1.10-1.x86_64 compat-libstdc++-33-3.2.3-69.el6.x86_64 compat-libstdc++-33-3.2.3-69.el6.i686 gcc-4* gcc-c++-4* glibc-2* glibc-devel-2.12-1.212.0.1.el6.i686 libaio-0.3* libaio-devel-0.3* libgcc-4.4.7-23.0.1.el6.x86_64 libstdc++-4* libstdc++-4.4.7-23.0.1.el6.i686 libstdc++-devel-4*  libXext-1.3.3-1.el6.i686 libXtst-1.2.3-1.el6.i686 libXi-1.7.8-1.el6.i686 openmotif-2* openmotif22-2* redhat-lsb-4.0-7.0.2.el6.x86_64 sysstat-9*

Minimum memory and space requirements:

RAM  4 GB
SWAP 4 GB

Check it with following command:

[root@discoverer-enginatics ~]# cat /proc/meminfo|egrep -i 'MemTotal|SwapTotal'
MemTotal:        3974228 kB
SwapTotal:       4112380 kB

Network requirements:

On Linux x86-64 operating systems, configure the host to resolve host names to the loopback IP address by modifying the /etc/hosts file to contain the following entries:

127.0.0.1 hostname.domainname hostname
127.0.0.1 localhost.localdomain localhost

After doing so, check that the hostname resolves to the loopback IP address by entering the following command:

/bin/ping hostname.domainname

Download required software

Following software should be downloaded.

Oracle Database 11.2.0.4 which is certified with Discoverer 11g and Weblogic Server 10.3.6 can be downloaded as patch 13390677 from support.oracle.com
Weblogic 10.3.5 (Oracle Fusion Middleware 11.1.1.7.0 is certified with WebLogic Server 10.3.5 and 10.3.6)
Oracle Portal, Forms, Reports and Discoverer 11g 11.1.1.2
Oracle Portal, Forms, Reports and Discoverer 11g Patch Set 6 (11.1.1.7.0) (patch id 16471668)
Repository Creation Utility for creating the database schemas

Discoverer, RCU and Weblogic software can be obtained at site edelivery.oracle.com. Once you logged in, search by ‘Oracle Discoverer’

Then choose both Oracle Discoverer 11.1.1.2.0 and 11.1.1.7.0

Search ‘Oracle Fusion Middleware Repository Creation Utility’. Choose Oracle Fusion Middleware Repository Creation Utility 11.1.1.9.0

Click on card, choose required platform, then click ‘Continue’

You can download files using browser. Alternatively, you can download files directly to Linux server, using Wget option. Click on WGET option hyperlink, download .sh script to server.
Adjust MOS password in script, add execute permission and run the script in the staging directory

Sad thing is that you may not find required version. Oracle removes software distributions from public access from time to time. Then the only option to get it is to create Oracle SR, which is not possible with free account.

Stage downloaded software to newly created mountpoint

[root@discoverer-enginatics u01]# mkdir -p /u01/stage
[root@discoverer-enginatics u01]# chown oracle.oinstall /u01/stage
 
Database:
-rw-rw-r--. 1 oracle oracle 1395582860 Aug 27  2013 p13390677_112040_Linux-x86-64_1of7.zip
-rw-rw-r--. 1 oracle oracle 1151304589 Aug 27  2013 p13390677_112040_Linux-x86-64_2of7.zip
-rw-rw-r--. 1 oracle oracle 1205251894 Aug 27  2013 p13390677_112040_Linux-x86-64_3of7.zip
-rw-rw-r--. 1 oracle oracle  656026876 Aug 27  2013 p13390677_112040_Linux-x86-64_4of7.zip
-rw-rw-r--. 1 oracle oracle  599170344 Aug 27  2013 p13390677_112040_Linux-x86-64_5of7.zip
-rw-rw-r--. 1 oracle oracle  488372844 Aug 27  2013 p13390677_112040_Linux-x86-64_6of7.zip
-rw-rw-r--. 1 oracle oracle  119521122 Aug 27  2013 p13390677_112040_Linux-x86-64_7of7.zip
 
Disco:
V18772-01_1of4.zip  V18772-01_2of4.zip  V18772-01_3of4.zip  V18772-01_4of4.zip
 
Patchset_disco:
V37430-01.zip
 
RCU:
V75907-01.zip

Run Repository Creation Utility


Oracle Portal and Oracle Discoverer require database schemas in an Oracle database. These schemas can be created by using the Repository Creation Utility (RCU).

After unzipping start RCU from the bin directory inside the RCU_HOME directory.

[oracle@discoverer-enginatics RCU]$ unzip V75907-01.zip
cd rcuHome/bin
./rcu

Then follow configuration screens below

While checking SYS credentials installer gave following error:

Invalid username/password
Please enter valid username/password

Solution is to create valid password file for database instance. Use MOS note ID 1029539.6 as reference.

read -s syspass
orapwd file=orapw${ORACLE_SID} password=$syspass ignorecase=n

 

On the following screen, choose only Discoverer component.

 

New tablespaces will be created in your database for discoverer.

Weblogic 10.3.6 installation


Create iasadmin user for discoverer installation

[root@localhost stage]# useradd iasadmin
[root@localhost stage]# usermod -g oinstall iasadmin
[root@localhost stage]# passwd iasadmin

Create directory for weblogic installation

[root@localhost u01]# mkdir -p /u01/disco/middleware
[root@localhost u01]# chown -R iasadmin.oinstall /u01/disco

Start new vnc session as iasadmin and connect using vnc viewer

[iasadmin@discoverer-enginatics ~]$ vncserver :1

Download latest jdk to run WLS installation package. Download jdk 7, as jdk 8 gives error during Discoverer configuration at later stage

[iasadmin@discoverer-enginatics weblogic]$ tar -xzvf jdk-7u80-linux-x64.tar.gz

Run weblogic installation package and follow configuration screens:

/u01/disco/middleware/jdk1.7_wls_dont_delete/bin/java -jar wls1036_generic.jar

Discoverer installation


11.1.1.2 version need to be installed first, then patched to higher releases.
Run installation script from the stage area:

[iasadmin@discoverer-enginatics Disk1]$ pwd
/u01/stage/disco/Disco/Disk1
[iasadmin@discoverer-enginatics Disk1]$ ./runInstaller

Then follow configuration screens as in the example below:

At this step you should set Middlewere home to a directory in which weblogic server was installed. You can leave “Oracle Home Directory” value to default.

At this step open terminal as root user and run oracleRoot.sh script:

Finally installation complete:

Patching Oracle Portal, Forms, Reports and Discoverer


Before configuring discoverer installation it’s required to apply Oracle Portal, Forms, Reports and Discoverer 11g Patch Set 6 (11.1.1.7.0) for Linux x86_64 which is latest patchset.
After unzipping the archive go to Disk1 directory and run runInstaller.sh script as iasadmin user.

At this step run script as root as requested in the dialog box.

Patching is complete now.

Configuring Oracle Portal, Forms, Reports and Discoverer


Run the Configuration Tool to create your WebLogic Domain and configure your components and follow the configuration screens

export ORACLE_HOME=/u01/disco/middleware/as_1
$ORACLE_HOME/bin/config.sh

Error 1:

Exception in thread "AWT-EventQueue-0" java.lang.OutOfMemoryError: PermGen space
at java.text.RuleBasedBreakIterator.getCurrentCodePointCount(RuleBasedBreakIterator.java:692)
at java.text.RuleBasedBreakIterator.getNext(RuleBasedBreakIterator.java:710)
at java.text.RuleBasedBreakIterator.handleNext(RuleBasedBreakIterator.java:940)
at java.text.RuleBasedBreakIterator.previous(RuleBasedBreakIterator.java:645)

Solution:

Fusion Middleware 11g Installation Fails with java.lang.OutOfMemoryError: PermGen space (Doc ID 953141.1)

Modify the $ORACLE_HOME/oui/oraparam.ini

change

JRE_MEMORY_OPTIONS=" -mx1024m -XX:MaxPermSize=1024m"

To

JRE_MEMORY_OPTIONS=" -mx1024m -XX:MaxPermSize=2048m"

Error 2:

*** [Deployer:149193]Operation 'start' on application 'discoverer [Version=11.1.1.2.0]' has failed on 'WLS_DISCO' ***
*** [Deployer:149034]An exception occurred for task [Deployer:149026]start application discoverer [Version=11.1.1.2.0] on WLS_DISCO.: [HTTP:101216]Servlet: "WSRPPortletManagementService" failed to preload on startup in Web application: "/discoverer".
java.lang.NoClassDefFoundError: sun/io/CharacterEncoding

Solution:
—-
Reinstall Weblogic using JDK 7. JDK 8 was used before facing this error.

At this screen choose only Oracle Discoverer and Enterprise Manager.

At this step error was faced:

INST-07120 : Unable to connect to the Database with the given credentials: Invalid user name/password

In this case MOS note 959058.1 helped.

Passwords for following users have to be the same:
_DISCOVERER_PS, _DISCOVERER

Use following commands to set the passwords.

alter user _DISCOVERER_PS identified by 
alter user _DISCOVERER identified by

Oracle Internet Directory was not implemented in our configuration, so at the next step option to use it was skipped.

Final screen shows installation summary and information about configuration options.

Type: Oracle Portal, Forms, Reports and Discoverer Installation
        Configuration Options
                Middleware Home Location: /u01/disco/middleware
                Oracle Home Location: /u01/disco/middleware/as_1
                Oracle Instance Location: /u01/disco/middleware/asinst_1
                Oracle Instance: PROD
                Domain Option: Create Domain
                Domain Name: PROD_domain
                Domain Home: /u01/disco/middleware/user_projects/domains/DiscoDomain
                Domain Host Name: discoverer-enginatics.localdomain
                Domain Port No: 7001
                User Name: weblogic
                Automatic Port Detection: true
                Administrator Console: http://discoverer-enginatics.localdomain:7001/console
                EM Console: http://discoverer-enginatics.localdomain:7001/em
                EMAgent URL: http://discoverer-enginatics.localdomain:5155/emd/main
                Discoverer Viewer URL: http://discoverer-enginatics.localdomain:8888/discoverer/viewer
                Discoverer Plus URL: http://discoverer-enginatics.localdomain:8888/discoverer/plus

Verifying the installation


To verify the installation opmnctl utility can be used, launching it from the instance home.

$ORACLE_INSTANCE/bin/opmnctl status

Also URLs should be checked. Those URLs could be found on the final installation screen in the previous step.

Administrator Console: http://discoverer-enginatics.localdomain:7001/console

Discoverer Viewer: http://discoverer-enginatics.localdomain:8888/discoverer/viewer

EM Console: http://discoverer-enginatics.localdomain:7001/em

EMAgent URL: http://discoverer-enginatics.localdomain:5155/emd/main

Configure Oracle Business Intelligence Discoverer 11g for Oracle E-Business Suite Integration


Copy Database Connection File

Discoverer needs access to the Database Connection (dbc) file to connect to a EBS database.

Copy the dbc file from the $FND_SECURE directory of the integrated E-Business Suite Release 12 instance to the Discoverer 11.1.1 $ORACLE_INSTANCE/config/Discoverer/Discoverer_ directory.

Save the dbc file in lowercase.

[applvis@r12ml secure]$ scp VIS.dbc iasadmin@discoverer.enginatics.com:/u01/disco/middleware/asinst_1/config/Discoverer/Discoverer_asinst_1/vis.dbc

Update tnsnames.ora File

On the Discoverer node, include the tnsnames entry to connect to the EBS R12 database in file $ORACLE_INSTANCE/config/tnsnames.ora. Use the same entry as exists in the tnsnames.ora file on the Oracle E-Business Suite Release 12 application tier server node. The database name must match the two_task entry in the dbc file.

On apps tier tnsnames.ora file can be found under $TNS_ADMIN directory. Here is an entry, that was added to discoverer tnsnames.ora file:

Add alias for EBS instance host to /etc/hosts if it’s not resolved

Create new Discoverer 11.1.1 End User Layer

Create the Discoverer Tablespace to store database objects for the Discoverer 11.1.1 End User Layer in your E-Business Suite Release 12 database.

Start SQL*Plus and create a tablespace named ‘DISCOVERER’ with an initial size of 200 megabytes, using the absolute path to the subdirectory where your datafiles are located:

Create the Discoverer End-User Layer

Before running command line scripts, you must source the following environment script.

source $ORACLE_INSTANCE/Discoverer/Discoverer_asinst_1/util/discenv.sh

Create the new Discoverer 11.1.1 End User Layer by using the following command syntax

$ORACLE_HOME/bin/eulapi \
-CREATE_EUL \
-APPS_MODE \
-CONNECT system/@ \
-USER _US \
-PASSWORD \
-DEFAULT_TABLESPACE \
-TEMPORARY_TABLESPACE \
-EUL_LANGUAGE US \
-APPS_GRANT_DETAILS /

In our case:

$ORACLE_HOME/bin/eulapi \
-CREATE_EUL \
-APPS_MODE \
-CONNECT system/$systempass@VIS \
-USER EUL_US \
-PASSWORD $eul_pass \
-DEFAULT_TABLESPACE DISCOVERER \
-TEMPORARY_TABLESPACE TEMP \
-EUL_LANGUAGE US \
-APPS_GRANT_DETAILS apps/$appspass

In our case EUL_US already existed. So, skipped this step.

Apply AD Patch containing adupdeul.sh and adrfseul.sh.

For 12.1 it’s Patch 9394002. I will not cover full patching procedure here. Assuming that APPS tier already stopped and patch is unzipped, adpatch is started as on screenshot below:

Set Applications Profile Options for Discoverer using AutoConfig

Login to Oracle Applications Manager and navigate to Site Map > AutoConfig > Edit Parameters.

s_disco_url

Set the value of this variable to the base URL where Discoverer is available.

[protocol]:// [machine.domain]:[port]

Check value of variable in context file:

Now run autoconfig on application tier.

/adautocfg.sh

After running autoconfig you should restart applications.

[applmgr@discoverer-enginatics scripts]$ ./adstpall.sh apps/apps
[applmgr@discoverer-enginatics scripts]$ ./adstrtal.sh apps/apps

Set Applications Profile Options in Oracle E-Business Suite

Log into E-Business Suite Release 12 with the System Administrator responsibility

Navigate to the Profile > System form

Query the %Discoverer% profile options.

Ensure the all required ICX profile options in Oracle E-Business Suite are set:

Test connection to discoverer

Add discoverer url to JRE exception list

Also, add discoverer url to exceptions for browser pop-up blocker

These two steps required only for discoverer plus. Discoverer viewer works using html interface.

Finally results of all above work can be checked.
Here is screen of login page:

http://discoverer-enginatics.localdomain:8090/discoverer/viewer

List of workbooks is displayed at next step:

Example of Report:

At this stage Oracle Discoverer 11.1.1.7 installation and integration with Oracle EBS R12.1.3 complete.

Create business area and workbooks


Install Discoverer Administrator and Desktop

Download Oracle BI Discoverer Desktop and Discoverer Administrator 11.1.1.3.0 here.

Unzip installer and run setup.exe under Disk1\install\win32
This is one that worked for my 64bit Windows Home basic. Then follow installation screens.

I got an error straight after installation, when I tried to launch the application:

Error:
dis51usr.exe - System Error
 
The program cant start because MSVCR71.dll is missing from your
computer. Try reinstalling the program to fix this problem.

Solution:
Simple solution can be found in MOS note 1255244.1. You just download MSVCR71.dll from dll-files.com and place it into C:\Windows\System32 directory, if you are using a 32-bit Windows or into C:\Windows\SysWOW64 if you using a 64-bit Windows machine.

Create business area

Now connect to BI Discoverer Administrator to create Business area.

To connect use EUL_US user, that was created earlier.
Also, I created PROD connect descriptor using netmgr utility.
It’s required to connect to the database.

I chose AR and APPS tables to load into business area for example.

Now you need to provide access to Business area to application user or responsibility. I kept is simple and chose SYSADMIN user.

Create workbook

Now run Discoverer Desktop program. Before connecting to the database open Tools->Options menu

Enable connection to applications EULs, because our installation is integrated with EBS instance.

At this step, trying to connect as SYSADMIN user I faced an error:

Error:
unable to connect to: user@sid Failed to connect to database - Unable to connect to Oracle Applications database: invalid username/password

Solution:

Solution can be found in MOS note 1574364.1

You just need to run following command as applications (EBS) owner.

java oracle.apps.fnd.security.AdminAppServer apps/ AUTHENTICATION OFF DBC=$FND_SECURE/.dbc

After choosing ‘Create a new workbook’ menu, choose parameters for your query. I choose almost everything by default, because my goal was just to test workbook creation.

After clicking Finish button results of the query are displayed.

Now save the workbook in the database.

Now after logging in to the discoverer viewer again as SYSADMIN we can see workbooks available for this user.