Oracle Discoverer 11.1.1.7 installation and integration with Oracle EBS R12.1.3


In this post I will cover steps required to install discoverer with existing EBS R12 instance and make them work together.

Referenced Oracle notes:
Using Discoverer 11.1.1 with Oracle E-Business Suite Release 12 (Doc ID 1074326.1)
Certification information:
Configuration:
Discoverer 11.1.1.7.0 will be installed into separate ORACLE_HOME on an existing application tier server node. Then it will be integrated with existing EBS 12.1.3 installation. Configuration can be presented in the following table:

Node name
Database tier
Apps tier
Discoverer
sniperhost2
Yes
Yes
Yes
  1. Pre-requisite checks
  1. Check that system has following packages installed:
binutils-2.16.91.0.5-23.4
compat-libstdc++-296-2.96-132.7.2
compat-db-4.1.25-9
control-center-2.8.0-12
gcc-4.1.0-28.4
gcc-c++-4.1.0-28.4
glibc-common-2.3.4-2.9
libaio-0.3.106
Footref 2
libaio-devel-0.3.106
Footref 2
libstdc++-4.1.0-28.4
libstdc++-devel-4.1.0-28.4
make-3.80-202.2
openmotif-2.2.3
Footref 1
openmotif22-2.2.3
Footref 1
setarch-1.6-1
sysstat-5.0.5-1
Check them with rpm -q command.
rpm -q binutils-2* compat-libstdc++-296-2* compat-db-4* control-center-2* gcc-4* gcc-c++-4* glibc-common-2* libaio-0.3* libaio-devel-0.3* libstdc++-4* libstdc++-devel-4* make-3* openmotif-2* openmotif22-2* setarch-2* sysstat-7*
b. Minimum memory and space requirements:
RAM  4 GB
SWAP 4 GB
Check it with following command:
[[email protected] ~]# cat /proc/meminfo|egrep -i 'MemTotal|SwapTotal'
MemTotal:        4151228 kB
SwapTotal:       4194300 kB
c. Network requirements:
On Linux x86 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
     2.Download required software
Following software should be downloaded.
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
Software can be obtained at site edelivery.oracle.com. Once you logged in, choose 'Oracle Fusion Middleware' as a Product Pack.
download1.png
Then choose 'Oracle Fusion Middleware 11g Media Pack' for your system.
download2.png
Here you can finally download software you need. Sad thing is that you may not find required version. Oracle removes software distributions from public access pretty quickly. Then the only option to get it is to create Oracle SR, which is not possible with free account. So if you suspect that you need it in the future, don't remove it from your HDD.
download3.png
2.1 Unzip downloaded software to newly created mountpoint
[[email protected] u01]# mkdir disco
[[email protected] u01]# chown oracle.oinstall disco/
3. Configuring a Database and Database Schemas
Oracle Portal and Oracle Discoverer require database schemas on 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.
cd RCU_HOME/bin
./rcu
Then follow configuration screens below
rcu1.png
rcu2.png
While checking SYS credentials installer gave me following error:
Invalid username/password
Please enter valid username/password
Solution is to create valid password file for database instance. Use MOS note ID 1209587.1 as reference.
rcu3.png
On the following screen, choose only Discoverer component.
rcu4.png
rcu5.png
New tablespaces will be created in your database for discoverer.
rcu6.png
rcu7.png
rcu8.png
rcu9.png
4. Weblogic 10.3.5 installation
Run weblogic installation package and follow configuration screens:
[[email protected] disco]# ./wls1035_oepe111172_linux32.bin
weblogic1.png
weblogic2.png
weblogic3.png
weblogic4.png
weblogic5.png
[[email protected] disco]# cd disco_11/Disk1/
[[email protected] Disk1]# ./runInstaller
5. Discoverer installation
Run installation script from the stage area:
[[email protected] Disk1]$ pwd
/u01/disco/soft/disco/11.1.1.2/Disk1
[[email protected] Disk1]$ ./runInstaller
Then follow configuration screens as in example below:
fmw1.png
fmw2.png
fmw3.png
fmw4.png

At this step you should set Middlewere home where weblogic server was installed. You can leave "Oracle Home Directory" value to default.
fmw5.png
fmr6.png
fmw7.png
At this step open terminal as root user and run oracleRoot.sh script:
disco9.png
Finally installation complete:
fmw8.png

6. Patching Oracle Portal, Forms, Reports and Discoverer
Before configuring discoverer installation we need to apply Oracle Portal, Forms, Reports and Discoverer 11g Patch Set 6 (11.1.1.7.0) for Linux x86 which is latest patchset as for now.
After unzipping the archive go to Disk1 directory and run runInstaller.sh script as oracle user.
patch1.png
patch2.png
patch3.png
patch4.png
patch5.png

At this step run script as root as requested in the dialog box.
patch6.png
Patching is complete now.
7. 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
ORACLE_HOME/bin/config.sh
At this step I got error:
[[email protected] bin]$ Error occurred during initialization of VM
Could not reserve enough space for object heap
Could not create the Java virtual machine.
Solution:
I performed following action plan per MOS note 1303781.1:
Modify the ORACLE_HOME/oui/oraparam.ini
change
JRE_MEMORY_OPTIONS=" -mx1024m -XX:MaxPermSize=1024m"
to
JRE_MEMORY_OPTIONS=" -ms128m -mx512m"
This worked and I proceeded with installation.
conf0.png
conf1.png
conf2.png
conf3.png
conf4.png
conf5.png
conf6.png
conf7.png
At this step I had an error:
INST-07120 : Unable to connect to the Database with the given credentials: Invalid user name/password
Again MOS note 959058.1 helped. This knowledge base is great.
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
conf8.png
I don't have Oracle Internet Directory implemented, so at the next step I skipped option to use it.
conf9.png
conf10.png

Final screen shows installation summary and information about configuration options. I advise you save configuration.
conf11.png
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/PROD_domain
                Domain Host Name: sniperhost2.sniperdomain
                Domain Port No: 7001
                User Name: weblogic
                Automatic Port Detection: true
                Administrator Console: http://sniperhost2.sniperdomain:7001/console
                EM Console: http://sniperhost2.sniperdomain:7001/em
                EMAgent URL: http://sniperhost2.sniperdomain:5155/emd/main
                Discoverer Viewer URL: http://sniperhost2.sniperdomain:8090/discoverer/viewer
                Discoverer Plus URL: http://sniperhost2.sniperdomain:8090/discoverer/plus
8. Verifying the installation
To verify the installation we may use opmnctl utility, starting it from instance home.
verify1.png
Also URLs should be checked. Those URLs could be found on the final screen in the previous step.
verify2.png
verify3.png

I had to start managed server WLS_DISCO manually to make Discoverer URLs work:
verify4.png
verify5.png

verify6.png
verify8.png
verify7.png
9. Configure Oracle Business Intelligence Discoverer 11g for Oracle E-Business Suite Integration
9.1 Copy Database Connection File
Discoverer needs access to the Database Connection (dbc) file for the database you wish to connect to.
Copy the dbc file from the $FND_SECURE directory of the E-Business Suite Release 12 instance you are setting up Discoverer with to the Discoverer 11.1.1 $ORACLE_INSTANCE/config/Discoverer/Discoverer_ directory.
Save the dbc file in lowercase.
conf1.png
9.2 Update tnsnames.ora File
On your  Discoverer  node, include the tnsnames entry to connect to your EBS R12 database in file $ORACLE_INSTANCE/config/tnsnames.ora. Use the same entry as exists in the tnsnames.ora file on your 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 entry, that I added to discoverer tnsnames.ora file:
conf2.png
9.3 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:
conf3.png
9.4 Create the Discoverer End-User Layer
Before running command line scripts, you must source the following environment script.
source $ORACLE_INSTANCE/Discoverer/Discoverer_/util/discenv.sh
conf4.png
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 /
conf5.png
9.5 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:
conf6.png
9.6 Set Applications Profile Options for Discoverer using AutoConfig
Login to Oracle Applications Manager and navigate to Site Map > AutoConfig > Edit Parameters.
conf11.png
conf12.png
s_disco_url        Set the value of this variable to the base URL where Discoverer is available.        
:// :
Check value of variable in context file:
conf10.png
Now run autoconfig on application tier.
/adautocfg.sh
conf13.png
After running autoconfig you should restart applications.
[[email protected] scripts]$ ./adstpall.sh apps/apps
[[email protected] scripts]$ ./adstrtal.sh apps/apps

9.7 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:
conf17.png

10 Test connection to discoverer

Now we can finally check results of our work.
Here is screen of login page:
conf14.png
conf15.png
Unfortunately no workbooks created so far, so worksheet list is empty.
conf16.png

11.1 Install Discoverer Administrator and Desktop

I'm absolute beginner in discoverer, so I had to google for some time to find how to perform this step. We need to install additional software at windows host that has connection to our database server. Download Oracle BI Discoverer Desktop and Discoverer Administrator 11.1.1.3.0  here.

As usual, 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.

admin0.png

admin1.png

admin2.png
admin3.png
admin4.png
admin5.png
admin6.png


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


Error:
dis51usr.exe - System Error

The program can't 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.

11.2 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.
eul1.png
eul2.png

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

eul5.png

Now you need to provide access to Business area to application user or responsibility. I kept is simple and choose SYSADMIN user.
eul6.png
eul7.png


11.3 Create workbook

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

conn1.png

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

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

Error:

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

work1.png

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.

work2.png
work3.png
work4.png

work5.png

work6.png
work7.png

After clicking Finish button results of the query are displayed.

work8.png
















Now save the workbook in the database.

work9.png

work10.png
work11.png

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

work12.png
work13.png

Big thanks to this blog author

Web Analytics