ORACLE DBA

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

CONFIGURE DISCOVERER wReferenced 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:

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

Pre-requisite checks

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.106Footref 2
libaio-devel-0.3.106Footref 2
libstdc++-4.1.0-28.4
libstdc++-devel-4.1.0-28.4
make-3.80-202.2
openmotif-2.2.3Footref 1
openmotif22-2.2.3Footref 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:

[root@sniperhost2 ~]# 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.

Then choose 'Oracle Fusion Middleware 11g Media Pack' for your system.

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.

2.1 Unzip downloaded software to newly created mountpoint

[root@sniperhost2 u01]# mkdir disco

[root@sniperhost2 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

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.

On the following screen, choose only Discoverer component.

New tablespaces will be created in your database for discoverer.

4. Weblogic 10.3.5 installation

Run weblogic installation package and follow configuration screens:

[oracle@sniperhost2 disco]# ./wls1035_oepe111172_linux32.bin

[oracle@sniperhost2 disco]# cd disco_11/Disk1/

[oracle@sniperhost2 Disk1]# ./runInstaller

5. Discoverer installation

Run installation script from the stage area:

[oracle@sniperhost2 Disk1]$ pwd

/u01/disco/soft/disco/11.1.1.2/Disk1

[oracle@sniperhost2 Disk1]$ ./runInstaller

Then follow configuration screens as in example below:

At this step you should set Middlewere home where 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:

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.

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

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:

[oracle@sniperhost2 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"

JRE_MEMORY_OPTIONS=" -ms128m -mx512m"

This worked and I proceeded with installation.

Choose only Oracle Discoverer.

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:

<PREFIX>_DISCOVERER_PS, <PREFIX>_DISCOVERER

Use following commands to set the passwords.

alter user <PREFIX>_DISCOVERER_PS identified by <new password>

alter user <PREFIX>_DISCOVERER identified by <same password as above>

I don't have Oracle Internet Directory implemented, so at the next step I skipped option to use it.

Final screen shows installation summary and information about configuration options. I advise you save configuration.

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.

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

Administrator Console: http://sniperhost2.sniperdomain:7001/console

I had to start managed server WLS_DISCO manually to make Discoverer URLs work:

http://sniperhost2.sniperdomain:8090/discoverer/viewer

EM Console: http://sniperhost2.sniperdomain:7001/em

EMAgent URL: http://sniperhost2.sniperdomain:5155/emd/main

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_<ias-instance> directory.

Save the dbc file in lowercase.

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:

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:

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_<ias-instance>/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/<password>@<db> \

-USER <EUL_User_Prefix>_US \

-PASSWORD <password> \

-DEFAULT_TABLESPACE <default tablespace> \

-TEMPORARY_TABLESPACE <temp tablespace> \

-EUL_LANGUAGE US \

-APPS_GRANT_DETAILS <FNDNAM>/<FNDNAM password>

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: