How to collect Discoverer Plus and Viewer statistics

Hi All .

Here the step by step How to collect Discoverer Plus and Viewer statistics.

1.Navigate to the 'util' directory and edit the pref.txt file:

-- For 4i, it is $ORACLE_HOME/discwb4/util
-- For 10g, it is $ORACLE_HOME/discoverer/util
-- For 11g, it is:  $ORACLE_HOME/discoverer/config/pref.txt
 
$ORACLE_HOME refers to the top level AS home (Linux/Unix Example - /do2/Oracle/Middleware/as_1).  Your actual ORACLE_HOME name may differ, but should follow similar directory/path.
2.Navigate to the [Application] section:
3. Set the parameters

QPPEnable = 1
QPPCreateNewStats = 1 

(Although QPPCreateNewStats is not available by default in 11g, it still applies to 11g as well)

4. Save the pref.txt file.
5. Now we need to update the preference registry file:

-- For 4i and 10g, Unix/Linux, run: ./applypreferences.sh
-- For 4i and 10g, Windows run: applypreferences.bat
-- For 11g, Unix/Linux, run: $ORACLE_INSTANCE/Discoverer/Discoverer_asinst_1/util/applypreferences.sh
-- For 11g, Windows, run: $ORACLE_INSTANCE\Discoverer\Discoverer_asinst_1\util\applypreferences.bat

$ORACLE_INSTANCE refers to the instance home in the WLS/Discoverer home (Example - /do2/Oracle/Middleware/asinst_1).  Your actual ORACLE_INSTANCE may differ, but should follow similar directory/path.
Your ORACLE_INSTANCE may also differ if your environment includes multiple instances as may occur with clustered environments.

These settings will take effect for new sessions launched, but not existing sessions.
You may optionally, restart the Discoverer to force/kill all existing sessions.

P.S If you are using the Oracle BI Discoverer Plus Version 11.1.1.6.0/11.1.1.4.0 , you need to apply patch 11784475 (DISCO 11G PLUS/VIEWER QUERY STATS NOT WORKING IN APPS 12I EUL/64 BIT OS)

Good Luck ...

Refreshing IQY file from discoverer 11g

Hi All ...

Here the new issue that I have found in Discoverer 11g during the refreshing IQY file.

Open discoverer plus report  -->  export

change format to IQY:

Next.. next…..next

Open file and  run from your computer:

1    Enter your password when required

2    Enter:

 Enter:

Get the error:

 
The application encounters an invalid state. . 
#NAME? 
- Java heap space 
Oracle BI Discoverer Viewer cannot find the data necessary to display the results of this event. Correct any errors, and try again. 

Reason:

It appears that the managed server WLS_DISCO is running out of heap space. 

Solution:

You need to increase the maximum heap space.
How to increase the maximum java heap memory allowed for the managed server WLS_DISCO ( Doc ID 1592078.1 ) 

 Good Luck ...

Discoverer 10g installation Guide - Step by Step

Hi All

Here the Step by Step guide for installation Discoverer 10g on Linux.

1. Create an Linux user for your discoverer installation

2. Edit .bash_profile by adding ORACLE_HOME,TNS_ADMIN and TMP parameters

*** Pay attention, the TMP parameter need to be set before the starting Discoverer installation.

3. Login with created discoverer user to Linux

4. Download and extract the Discoverer 10g installation on Linux server.

(as_linux_x86_bi_101202_disk1.cpio and as_linux_x86_bi_101202_disk2.cpio)

To extract *.cpio files you can use the following command: cpio -idmv < file.cpio

5. Go to Disk1 folder and run : ./runInstaller -ignoreSysPrereqs .

6. In Destination section give the Name and Path for ORACLE_HOME from the previous steps.

7. Next

8. Next (need to fix any prerequisite issues if exists ).

9. Add the relevant languages --> Next.

10. Choose the automatic Port Configuration --> Next.

11. If you wan't to configure mail server you can leave it blank --> Next.

12. Specify name and password for ias_admin.
*** Remember the password. It will be on use on Upgrade to 10.1.2.3 PATCH SET RELEASE.

13. Install

14. Run $ORACLE_HOME/root.sh with root user --> OK.

Upgrade the discoverer to 10.1.2.3 PATCH SET

Download and extract Patch 5983622

Go to Disk1 directory and run:

./runInstaller -ignoreSysPrereqs

1. Choose the existed Name from the previous installation --> Next.

2. Choose the Oracle Applicatio Server and Developer Site 10g Rel2 Software Upgrade section. --> Next.

3. Enter the ias_admin password from the previous installation. --> Next.

4. Install.

5. Run $ORACLE_HOME/root.sh with root user --> OK.

6. Exit.

Install the latest Discoverer CP(cumulative patch).

1. Download and extract the latest Discoverer CP (For example CP10 - Parch 11674847)

2. Go to 11674847 folder

3. Run the following command for patch installation:

$ORACLE_HOME/OPatch/opatch apply

4. Go to $ORACLE_HOME/opmn/bin directory

5. ./opmnctl stopall

6. ./opmnctl startall

7. Update the $TNS_ADMIN/tnsnames.ora with instance tns names.

8. Enter to Discoverer page (default: http:<hostname>:7777/)

Good Luck ...

Discoverer Plus 11g - dis51pr: error while loading shared libraries: libpthread.so.0: cannot open shared object file

Hi All

Here the new issue that I found during changing the pref.txt in Discoverer Plus/Viewer 11g on RH6.

I changed the pref.txt in $ORACLE_INSTANCE/config/PreferenceServer/Discoverer_asinst_1.
And when I tried to run $ORACLE_INSTANCE/Discoverer/Discoverer_asinst_1/util/applypreferences.sh
I got an error:
dis51pr: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory

I know this error from the previous Discoverer version (10g ) on RH5. The patch 6690831 fixing this issue by removing the LD_ASSUME_KERNEL from opmn.xml. 

I checked the $ORACLE_INSTANCE/config/OPMN/opmn/opmn.xml , there was no LD_ASSUME_KERNEL parameter there.

How to fix it? Here the solution:

1. Backup $ORACLE_INSTANCE/Discoverer/Discoverer_asinst_1/util/discenv.sh file
2. Look for :

#Bug 7491881 - For LNX AMD, set the LD_ASSUME_KERNEL variable
export LD_ASSUME_KERNEL=2.4.19


and change it to


#Bug 7491881 - For LNX AMD, set the LD_ASSUME_KERNEL variable
#export LD_ASSUME_KERNEL=2.4.19
3. Run $ORACLE_INSTANCE/Discoverer/Discoverer_asinst_1/util/applypreferences.sh

Good Luck ...

Discoverer Plus from EBS R12.1.3 - java.lang.NullPointerException

Hi All


Here the new issue that I found after upgrade to EBS R12.1.3.
We have a lot of functions that helps users to open the discoverer reports per organization via Discoverer Plus. After we finished upgrade and updated the ICX: Discoverer Launcher and ICX: Discoverer Viewer Launcher profiles I found very strange issue:
When User (that not an EUL owner and Discoverer Administrator) try to open the discoverer via EBS function , he get an error java.lang.NullPointerException.
After restarting the APPL Tier and clear the cache I started to get the FULL error:

Exception found : 
java.lang.NullPointerException

at oracle.apps.fnd.util.URLEncoder.encode(URLEncoder.java:60)
at _OracleOasis._jspService(_OracleOasis.java:662)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:395)
at _RF._jspService(_RF.java:225)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:379)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:619)

Cause
The e-BS system profile option "Java Look and Feel" (short name FND_LOOK_AND_FEEL) is null (i.e. it is not set).
Regarding the URL, any strings specified by the profile option will be added to the URL.
In both cases, it is also possible that the file appsweb.cfg changes the Java Look and Feel.
Discoverer has also been known (in 11i at least) to benefit if the profile option Self Service Accessibility Features is set to Standard Accessibility.


Solution
The fix for this issue is in 11782912 Patch . Users who have applied this patch should not experience the issue. 
Workaround for users who cannot apply the patch:
1. Ensure that the value of the Java Look and Feel profile option FND_LOOK_AND_FEEL is not null, i.e. set it to a valid value from the List of Values.
2. If the URL changes, ensure that the profile option does not include the string &LookAndFeelName=OracleLookAndFeel . If it does, it will be added from here into the URL. 
3. In either case, ensure that the e-BS configuration file appsweb.cfg does not update the profile option or include the string &LookAndFeelName=OracleLookAndFeel . 


Useful documentation:
Java Look and Feel settings cause java.lang.NullPointerExcerption or java.lang.NoSuchFieldError when trying to start Discoverer from e-Business Suite [ID 554817.1]

Good Luck ...

How to change Export Destination in Discoverer Plus

Hi All


Here the way to change Export Destination in Discoverer Plus.
The default value is C:\Documents and Settings\username\

                                     
Action Plan:
1. Open the pref.txt in $ORACLE_HOME/discoverer/util.
2. Change DefaultExportPath parameter to wanted value (for example: "C:/Discoverer_Reports") from "" (default).

3. Execute ./applypreferences.sh form $ORACLE_HOME/discoverer/util.

4. Re-Login to Discoverer Plus.

5. Retry the export again.

Good luck ...

How to disable Estimate Time in Discoverer Desktop 10g / 11g

Hi All


Here the way to disable the Estimation Time in Discoverer Desktop 10g / 11g.
Some times the Estimate Time takes more time than Report execution.


So here we go:
1. Create file Estimate.txt
2. Write the following to the file:


For Discoverer 10g

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\ORACLE\Discoverer 10\Database]
"QPPEnable"=dword:00000000


For Discoverer 11g

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\ORACLE\Discoverer 11\Database]
"QPPEnable"=dword:00000000


3. Save and change the file extension to reg (Estimate.reg).
4. Double click on file and It will write a new entry to your registry.

Thanks a lot for my Dear Friends Efrat and Ofer.
Good Luck and have a fast running reports ...

Discoverer Plus 10g Has Slow Performance When Moving A Field / Column To The Page Item Area.

Hi All.

I found this issue after Discoverer CP9 upgrade.

The problem is – That in Discoverer Plus I can't drag an item to the Page Items section.In Discoverer Plus this action takes a lot of time or stacks the report forever.
In Discoverer Desktop it is done just fine .

Cause:

The cause is that the Preference values for the 'MaxVirtualHeapMem'  parameter is not picked up and the default value (which is small) is used. This results in a large amount of Disk I/O which results low performance of the application interface. Or 'MaxVirtualHeapMem'  parameter is too small.

There is an Oracle document for this bug [ID 359124.1] for versions 10.1.2.48.18 or 10.1.2.50.05.

If you version is a higher and you stiil have this issue, you need to do the fiollowing:

1. Open the pref.txt in $ORACLE_HOME/discoverer/util.

2. Increase MaxVirtualHeapMem  parameter.

3. Execute ./applypreferences.sh form $ORACLE_HOME/discoverer/util.

4. Recheck the issue.

Good Luck ..

Oracle EBS 11i/R12 with DB 10g on RedHat 5.x issues.

Hi All.


Here the steps that you need to perform for installing/migrate Oracle EBS 11i/R12 and DB 10g on RedHat 5.x:


1. After the RH5 installation please check that all need rpm's were installed:
compat-libstdc++-egcs-1.1.2-1
compat-libstdc++-33-3.2.3-6
compat-libstdc++-296-2.96-138
compat-libcwait-2.1-1
compat-oracle-el5-1.0-5
openmotif21-2.1.30-11.EL5 
binutils-2.15


The following packages must be installed from the OEL 5 or RHEL 5 distribution media: 

libXp-1.0.0-8.1.el5 
compat-libgcc-296-2.96-138 
compat-libstdc++-33-3.2.3-61 
compat-db-4.2.52-5.1


2. Update OS parameters:



vi /etc/sysctl.conf
==============================
# Kernel Parameters for Oracle 10.2.0
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144


run command :
su - root
sysctl -p


vi /etc/security/limits.conf :
==============================


# To increase the shell limits for Oracle 10g
orimax soft nproc 2047
orimax hard nproc 16384
orimax soft nofile 1024
orimax hard nofile 65536


vi /etc/pam.d/system-auth :
==============================


session required pam_limits.so
session required pam_unix.so


vi /etc/profile :
==============================
if [ $USER = "<oracle user>" ]; then
        if [ $SHELL = "/bin/bash" ]; then
              ulimit -u 16384
              ulimit -n 63536
fi
        else
              ulimit -u 16384 -n 63536
        fi
fi



3. perl adcfgclone.pl dbTier:
==============================
export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'
need add to bash_profile
run: perl adcfgclone.pl dbTier


For clonning from RH4 to RH5
==============================
see the Note:747424.1 (afmkinit.sh exiting with status 127)
edit $ORACLE_HOME/appsutil/bin/adgetlnxver.sh :change LD_ASSUME_KERNEL with XD_ASSUME_KERNEL
re-run: perl adcfgclone.pl dbTier


4. perl adcfgclone.pl appsTier:
==============================
export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'
need add to bash_profile


ln -s /usr/bin/ld215 /usr/bin/ld
Install Patch 6078836
ln -s /usr/lib/libdb.so.2 /usr/lib/libdb.so.3
perl adcfgclone.pl appsTier


For clonning from RH4
==============================
install  patch 6365595 
or
edit $AD_TOP/adgetlnxver.sh :change LD_ASSUME_KERNEL with XD_ASSUME_KERNEL
re-run: perl adcfgclone.pl appsTier


5. DISCOVERER issue:
==============================

OPMN not started after discoverer installation on RH5


cd $ORACLE_HOME/opmn/conf
cp opmn.xml opmn.xml.ORG
cat opmn.xml.ORG | grep -v LD_ASSUME_KERNEL > opmn.xml
diff opmn.xml.ORG opmn.xml
$ORACLE_HOME/opmn/bin/opmnctl stopall
$ORACLE_HOME/opmn/bin/opmnctl start


Good Luck ..

Create Discoverer 11g EUL for Oracle Application users.

Hi All.
If you get the following message:
You do not have access to any End User Layers (EULs). You must create at least one EUL to access Discoverer.
so
Here the step by step for creating Discoverer 11g EUL for  Oracle Application users.

 1. Create EUL user

     a. Create Table Space for EUL user

        create tablespace EUL datafile '/d01/test/db/apps_st/data/eul01.dbf'

        size                                  200M

        autoextend on maxsize                200M

        extent management local uniform size  64K

  
     b. Create EUL user

   

        create user EUL_US identified by EUL_US default tablespace EUL temporary   tablespace   TEMP;

     grant connect, resource to  EUL_US; 
     grant all privilege to EUL_US;
(You can revoke no need privileges after the EUL creation).

2. Enter to Oracle Discoverer Administrator with EUL_US user

You will get the following message:

 Press "Yes".

Press "Create an EUL".

Check  "New EUL is for use Oracle Application users ONLY" and press Select

Find EUL user that was created in first step and press OK. Press NEXT.

Enter in Schema "APPS" and apps Password.Press FINISH
It will install all relevand db objects for new EUL user.
Aftre the installation finished successfully:
Go to Tools --> Privileges --> In Previleges tab press SELECT

 Enter the Oracle Application User that will be the discoverer administrator (in my case it's SYSADMIN).

Give full privileges to user  and save the changes.

 3. Now you can connect to Oracle Discoverer Administrator / Desktop with Oracle Application User.

Good Luck..

INST-07408:Unable to install or configure the product on a 32-bit JVM on a 64-bit machine.

Hi All

I got the following error during the Discoverer 11g installation on RH5 64-bit after Weblogic and RCU were installed successfully:


INST-07408:Unable to install or configure the product on a 32-bit JVM on a 64-bit machine.

Reason:

1. Weblogic was installed with java 32-bit

2. Weblogic with installed with default RH5 java - JDK1.4

Here the action Plan to fix the issue:

1. Download the latest JDK from http://www.oracle.com/technetwork/java/javase/downloads/jdk-6u26-download-400750.html. Pay attention, the JDK need to be for Linux 64-bit.

2. Copy the rpm to server under /usr/java and run command 

    $ ./jdk-6u26-linux-x64-rpm.bin

3. Enter to discoverer Linux user and add to .bash_profile (.profile) 

    export PATH=/usr/java/jdk1.6.0_26/bin:$PATH

4. Check if the java version was changed:

     $ which java

     /usr/java/jdk1.6.0_26/bin/java

     $ java -version

     java version "1.6.0_26"

    Java(TM) SE Runtime Environment (build 1.6.0_26-b03)

    Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02, mixed mode) 

5. Re-run the Weblogic installation:

 /usr/java/jdk1.6.0_26/jre/bin/java -jar -d64 wls1032_generic.jar

(For RH 64bit need to download generic weblogic installation).

6. Re-run the Discoverer installation:

$ ./runInstaller

Good Luck..