Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Discoverer_EBS

VIEWS: 1,794 PAGES: 15

  • pg 1
									Discoverer in Oracle Applications
Posted in November 4th, 2006 by Atul in 11i, basics, discoverer One of my readers reminded me that I have not covered on discoverer for Apps DBA yet so thought of putting some bits and pieces for you on Discoverer and later on How to Integrate Discoverer 10g with Oracle Apps 11i. So lets start this discussion about overview & things you should know about Discoverer as Apps DBA. Whats is Discoverer Server ? Discoverer is and ad hoc query, reporting, analysis and publishing tool which help business users to get quick access to information from datawarehouse or OLTP systems. In Apps, Discoverer version 4i used to be installed & configured out of the box which is desupported now. You should configure Apps with Discoverer 10g (I am going to cover about how to configure discoverer 10g with E-Business Suite) What should I know about Discoverer as an Apps DBA ? Though each component in Apps like forms, reports, web server including Discoverer in itself are quite big and more you know about them better it is for you . Few Important things you should know about Discoverer in Apps is like how to start/stop & how to access it. In case of any issues you should be able to troubleshoot discoverer. What is EUL ? You will hear lot about EUL from business analyst which means End User Layer, which is the key to provide ease of use to underlying data in Oracle Apps Database. Technical things about discoverer in Apps .. –In Apps default discoverer version 4i, is installed under $ORACLE_HOME/discwb4 –Script to start discoverer server is addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME – This script in turn start discoverer processes which can be start by startall.sh under $ORACLE_HOME/discwb4/util (This script is different from adstrtal.sh) – startall.sh calls scripts like startgatekeeper.sh, startlocator.sh , startoad.sh, startosagent.sh to start gatekeeper, locator, oad & osagent resp. – In Apps two major way to access EUL is via Discoverer view & Discoverer Plus edition (You can use Discoverer Administration Edition as well)

Overview on How to Configure Discoverer 10g with Oracle Apps
Posted in November 6th, 2006 by Atul in discoverer By now you might already be aware of that Discoverer 4i (which is shipped with Oracle Apps 11i) is desupported so Should we start looking for New discoverer Version and answers is YES we should. Today I am going to cover overview fo 10g Discoverer & few important thing you should know before configuring 10g Discoverer with Oracle Applications 11i. What all Discoverer Versions available with Apps ? You can configure Discoverer 10g Release 1 i.e. version 9.0.4 ( Yes version 9.0.4 is also called as Discoverer 10g Rel 1) or latest certified is Discoverer 10g Release 2 i.e. Version 10.1.2. I suggest you go for Discoverer version 10.1.2 . Can I upgrade Discoverer 4i to 10g ? No Upgrade of 4i to 10g is not supported ( Please check what I mean by not supported in next line) , only option is to install new discoverer 10g software & Upgrade 4i EUL to 10g EUL (EUL Upgrade is supported ) If you want to know what is EUL or basics of Discoverer click Can I Install Discoverer 10g in Existing ORACLE_HOME with Oracle Apps ? No , Oracle Discoverer with 1og should be in its own oracle_home, It can’t share home with any other oracle_home like 8.0.6 or iAS . Though it can be on same machine where your Apps Middle tier or Can be on separate Machine. Limitation for Discoverer 9.0.4 & 10.1.2 Configuring Discoverer 9.0.4 or 10.1.2 is currently certified only with Apps 11.5.8 to 11.5.10

Discoverer 10g & Oracle 11i Configuration Steps
Posted in November 8th, 2006 by Atul in 11i, discoverer If you are reading this post directly then visit my previous posts on discoverer basics & overview with Apps at

Lets start today with broad level Steps to Install & configure 10g Discoverer with Apps

11i , in end you can see metalink note & other references.
Please Note that Steps mentioned here are specifically from version 10.1.2.0.2

1.Install 10g Oracle Application Server Release 2 i.e. 10.1.2.0.2 2.Copy DBC file from $FND_SECURE(E-Business Suite Tier) to $ORACLE_HOME/discoverer/secure(Discoverer Home Installed in Step1) 3. Update tnsnames.ora in DISCO ORACLE_HOME installed in Step1 to point to EBusiness Suite 11i Database 4. Migrate your EUL (End User Layer) from 4i to 10g EUL ( Please Note upgrade from EUL3i to direct 10g is not supported , you have to first upgrade eul to4i & then to 10g ) 5. Select JVM for Disco Plus (you can Oracle’s Jinitiator or Sun Java Plug-in) 6. Apply Necessary Apps Patch for compatibility Issues ( For list of patches check Metalink Note mentioned below) 7. Change Profile options to point to 10g Discoverer from 4i All these Steps above are compulsory steps , I have not mentioned Optional Steps like Single Sign On Server configuration and generating Business Layers. Also steps mentioned here are configurations related to Apps DBA’s perspective for functional steps refer to document mentioned below. Related Links
Metalink Note # 313418.1 Using Discoverer 10.1.2 with Oracle E-Business Suite 11i 1og Application Server Installation Overview

http://becomeappsdba.blogspot.com/2006/10/installing-10g-application-server.html
10g Discoverer Installation Guide http://download-uk.oracle.com/docs/cd/B14099_19/getstart.htm discoverer Documentation http://download-uk.oracle.com/docs/cd/B14099_19/bi.htm

Discoverer 10g with R12
Overview: Oracle Business Intelligence 10g is an integrated business intelligence solution supporting intuitive ad hoc query, reporting, analysis, and web publishing.Discoverer enables report builders and analysts to create, modify, and execute ad hoc queries and reports. Casual users can view and navigate through predefined reports and graphs through business views that hide the complexity of the underlying data structures being reported upon. Requirement : Installing Discoverer 10g as a standalone server for R12 Instance. In this section we will see how to setup a standalone discoverer 10g server and integrate it with R12 Instance. Hardware Requirement: - Seperate Node with Linux Operating System Software Requirement: - Download Oracle Business Intelligence Server 10g Release 2 (10.1.2.0.2) Setup Steps: 1. Create a User iadisco on the BI Box 2. Run Rapidwiz and Install Standalone Discoverer.

3. Make sure that the service is Up and Running http://r12bi.r12.com:1156 - Should be accessible [iadisco@r12bi bin]$ ./opmnctl status Processes in Instance: DiscoR12.r12bi.r12.com -------------------+--------------------+---------+--------ias-component | process-type | pid | status -------------------+--------------------+---------+--------DSA | DSA | N/A | Down LogLoader | logloaderd | N/A | Down dcm-daemon | dcm-daemon | 8875 | Alive WebCache | WebCache | 12422 | Alive WebCache | WebCacheAdmin | 12385 | Alive OC4J | home | 12386 | Alive OC4J | OC4J_BI_Forms | 12387 | Alive HTTP_Server | HTTP_Server | 12388 | Alive Discoverer | ServicesStatus | 12389 | Alive Discoverer | PreferenceServer | 12390 | Alive [iadisco@r12bi bin]$ 4. Apply Discoverer Plus and Viewer MLR Patch 5367472 (Ref 373634.1 ) 5. Restart the Application and Complete a Health Check http://r12bi.r12.com:7777/discoverer/viewer http://r12bi.r12.com:7777/discoverer/plus Configuration Steps: 1. Identify the DBC File Location on the BI Home. [iadisco@r12bi bin]$ cat $ORACLE_HOME/opmn/conf/opmn.xml | grep -i FND_SECURE

[iadisco@r12bi bin]$ 2. Create the directory if it is not present. mkdir -p /home1/iadisco/discoverer/secure 3. Copy the DBC File from the R12 Instance to the Directory Created above. DBC File at R12 will be at $FND_SECURE 4. Update the TNSNAMES.ora entry on the BI Home with the R12 Instance. Make sure the tnsping for the R12 Instance works. Summary: We have installed a standalone discoverer instance for the R12 Instance. Now the next steps will be Creating the End User Layer on the R12 Database. Must Read : 373634.1 Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12

Installing Discoverer Administration Edition on Windows PC
Requirement: A Windows PC and establishing connection to the EBusiness Suite Edition. Software : Download Discoverer 10.1.2 Administration Edition from the below site http://www.oracle.com/technology/software/products/ids/index.html Installation Steps: 1. Run the Setup and Install the Desktop Edition. 2. Apply the latest certified Discoverer Administration Edition Patch 3. Set Windows Registry Settings as per Note 373634.1 4. Create a Secure Directory in the Discoverer Home. mkdir %ORACLE_HOME%/secure ; eg:ORACLE_HOME=D:\Discoverer10g\ 5. Copy the DBC File from the EBusiness Suite Instance and place it in the secure directory. 6. Create a Windows System Variable FND_SECURE and with value D:\Discoverer10g\secure 7. Update %ORACLE_HOME%/network/admin/tnsnames.ora and include the EBusiness Suite Instance. The database name must match the two_task entry in the dbc file. 8. Make sure that the TNSPING Works.

Upgrading the existing Discoverer End User Layer to Discoverer 10.1.2
Applicable: The following instruction is applicable only to customers who are having Discoverer4i and looks for 10g migration. Summary: Existing End User Layer EUL4_US will be backed up using Operating System Export Command and Imported back to the same database as EUL5_US Schema.This will help in preserving the Old End User Layer and create a new Schema for the 10g Discoverer. Pre-Upgrade Step: 1. Backup the Existing End User Layer. exp eul4_us/xxxxxx file=pre_10g_upgrade_eul4_backup.dmp Move the file to $AU_TOP/discoverer 2. Create a new tablespace as DISCOVERE10G 3. Create a new user EUL5_US with the default tablespace as DISCOVERER10G 4. Grant connect , resource to eul5_us; & Grant select any table to eul5_us 5. Import the dump as EUL5_US. imp eul5_us/eul5_us file=pre_10g_upgrade_eul4_backup.dmp fromuser=eul4_us touser=eul5_us 6. Lock the EUL4_US User. Upgrade Steps: 1. Log in as Operating System Discoverer User on the Unix Box. 2. Make sure that the tnsping for the EBSO Instance works. 3. Run the command EULAPI to upgrade the End User Layer from EUL4 to EUL5. $ORACLE_HOME/bin/eulapi -CONNECT <EUL User>/<Password>@<db> AUTO_UPGRADE Eg:$ORACLE_HOME/bin/eulapi -CONNECT EUL5_US/EUL5_US@EBSO AUTO_UPGRADE Testing the Upgrade: Connect to 10g EM of Discoverer Instance and Create a Public Connection Connection Name : EUL_UPGRADE Connect To : Oracle Applications User Name SYSADMIN Connection Type Public Database EBSO End User Layer EUL5_US Locale English (United States) Responsibility System Administrator

Access the below url and confirm if you are able to see the EBSO Workbooks. http://r12bi.r12.com:7777/discoverer/viewer http://r12bi.r12.com:7777/discoverer/plus

Creating End User Layer for R12 and Importing all Business Areas
Summary: Discoverer 10g Import of all Base EEX Files are done when the existing End User Layer needs to be rebuild or corrupted. This action plan will help in building a fresh End User Layer from the existing EEX Files on the Middle Tier of the Server. The entire below action plan does not require any Windows Utility as 10g BI has EUL API's to handle the discoverer related activity. This enhancement help Discoverer Administrator to work more on less time with flexible EUL API utilities. The same activity could also be performed using the Discoverer Administrator Utilities on Windows. Below Action Plan helps in performing the same. Action Plan using EUL APIs Action Plan using Discoverer Admin Utility Execution Summary: EUL5_US Database Export is taken as a Rollback Action Plan. The End User Layer EUL5_US is completely dropped. Using the EUL API's or DIS51ADM the new empty EUL is created. Once the EUL is Created, Generate All Business Views is run from the Oracle Applications. Once Generate All Business View is completed, Invalid Objects in the Database is compiled and made sure that there are no new invalids. Mount EBSO Application Tier Server Node $AU_TOP/discover to Oracle10gAS Discoverer Instance. Import the Contents using adupdeul.sh using MKS Toolkit and Refresh all the Business Area. Caution: All Customers Customized Business Area and Workbooks will be completely removed when importing all the Base EEX Files. Services / Component Affected: All Discoverer related Services Downtime Estimation: This action plan depends on the Database Load and an average time will be around 8 Hours Action Plan using EUL APIs: 1. Take an EUL User Database Export ( User : EUL5_US ) Operations will take the export dump and move it to $AU_TOP/discover. 2. Run the following sql; select default_tablespace from dba_users where username='EUL5_US'; 3. Drop the EUL5_US User ( drop user eul5_us cascade; ) 4. Connect to the 10g Discoverer Instance

5. Make sure TNSPING Works towards the EBSO Instance from the 10g Discoverer Instance. 6. Create New Discoverer End User Layer $ORACLE_HOME/bin/eulapi -CREATE_EUL -APPS_MODE -CONNECT system/@ -USER EUL5_US -PASSWORD -DEFAULT_TABLESPACE -TEMPORARY_TABLESPACE -EUL_LANGUAGE US -APPS_GRANT_DETAILS APPS/ 7. Regenerate Business Views by running the "Generate All Business Views" Resp: Business View Setup responsibility. 8. After Regeneration of the Business Views has been completed, check the Business View Generator output file. It should not contain any errors. 9. Recompile all objects in the APPS schema using adadmin. 10. Ensure the BIS views exist and all BIS views are valid by issuing the following command in SQL*Plus: $ sqlplus apps/@ SQL> select object_name from user_objects where object_type = 'VIEW' and status = 'INVALID' and ( object_name like '%FV_%' or object_name like '%FG_%' or object_name like '%BV_%' or object_name like '%BG_%' ); If necessary, recompile those objects so that all views are valid. The Discoverer refresh process may stop if a select statement from a invalid BIS View causes the error. 11. Mount EBSO Application Tier Server Node $AU_TOP/discover to Oracle10gAS Discoverer Instance. 12. Grant End User Layer Administration Privileges to SYSADMIN $ORACLE_HOME/bin/eulapi -CONNECT /@ -GRANT_PRIVILEGE -USER SYSADMIN -PRIVILEGE administration -PRIVILEGE all_admin_privs -LOG 13. Make sure user SYSADMIN has full security access to all Business Areas $ORACLE_HOME/bin/eulapi -CONNECT /@ -GRANT_PRIVILEGE

-USER SYSADMIN -BUSINESS_AREA_ADMIN_ACCESS % -WILDCARD -LOG 14. Import Discoverer Content for Release 11i using adupdeul.sh mode=complete sh adupdeul.sh connect=sysadmin/sysadmin-password@< ;EBSO_DB> resp="System Administrator" gwyuid=APPLSYSPUB/PUB fndnam=APPS secgroup="Standard" topdir= language=US eulprefix=EUL5 eultype=OLTP mode=complete iashome= logfile=import_complete_eul_us.log 15. Refresh the Discoverer End User Layer Action Plan using Discoverer Admin Utility: 1. Take an EUL User Database Export ( User : EUL5_US ) Operations will take the export dump and move it to $AU_TOP/discover. 2. Run the following sql; select default_tablespace from dba_users where username='EUL5_US'; 3. Drop the EUL5_US User ( drop user eul5_us cascade; ) 4. Add the Instance TNS Entry to the File D:\ODisc10g\NET80\ADMIN\tnsnames.ora on Discoverer Admin Server 5. Open a Command Window and type "tnsping ORACLE_SID" and make sure you get a response. 6. Create New Discoverer End User Layer D:/ODisc10G/bin/dis51adm.exe /CREATE_EUL /APPS_MODE /CONNECT system/@$ORACLE_SID /USER EUL5_US /PASSWORD EUL_US /DEFAULT_TABLESPACE /TEMPORARY_TABLESPACE TEMP /EUL_LANGUAGE US /APPS_GRANT_DETAILS apps/ /SHOW_PROGRESS 7. Regenerate Business Views by running the "Generate All Business Views" Resp: Business View Setup responsibility.

8. After Regeneration of the Business Views has been completed, check the Business View Generator output file. It should not contain any errors. 9. Recompile all objects in the APPS schema using adadmin. 10. Ensure the BIS views exist and all BIS views are valid by issuing the following command in SQL*Plus: $ sqlplus apps/@ SQL> select object_name from user_objects where object_type = 'VIEW' and status = 'INVALID' and ( object_name like '%FV_%' or object_name like '%FG_%' or object_name like '%BV_%' or object_name like '%BG_%' ); If necessary, recompile those objects so that all views are valid. The Discoverer refresh process may stop if a select statement from a invalid BIS View causes the error. 11. Copy all the contents from discover directory from $AU_TOP directory to Local Machine where Discoverer Administrator is Installed ( Directory : D:EEX$ORACLE_SID ) 12. Connect as EUL5_US on Discoverer Administrator and grant access to User SYSADMIN and System Administrator Responsibility. Open Discoverer Administrator Edition 10g (D:Odisc10gbindis51adm.exe) Connect as EUL5_US with the Standard Password Make sure you De-Select "Oracle Application User" Select the Priviledge Dialog from Tools >> Privileges From the Privileges tab, select the Applications user 'SYSADMIN' from the pull down menu. In the Privilege window, click the checkboxes for: Administration, Format Business Area Create/Edit Business Area Create Summaries Set Privilege Manage Schedule Workbook Click Apply Button to Save Repeat the same grant for the Oracle Applications responsibility 'System Administrator'. Ensured that Discoverer Admin Edition is able to connect to the Instance as SYSADMIN and Resp System Administrator. Note : Make sure that you select "Oracle Application User" option. 13. Import Discoverer Content for Release 11i using adupdeul.sh mode=complete sh adupdeul.sh connect=sysadmin/@ resp="System Administrator" gwyuid=APPLSYSPUB/PUB fndnam=APPS secgroup="Standard" topdir=D:/EEX/XXXXX/discover

language=US eulprefix=EUL5 eultype=OLTP mode=complete iashome=D:/ODisc10g/bin logfile=import_complete_eul_us.log 14. Refresh the Discoverer End User Layer

Discoverer 10g Import of all Base EEX Files related to a Application Patch
Discoverer 10g Import of all Base EEX Files related to a Patch or a Set of Patch are done after applying patches. There are few patches, which bring new Business Area and Workbooks. These new information are found in the format of EEX Files inside the Patch. This action plan will help in in-corporating the new EEX Files into the End User Layer without doing a full import of all the Business Area. Execution Summary: EUL5_US Database Export is taken as an Rollback Action Plan. Generate All Business Views is run from the Oracle Applications. Once Generate All Business View is completed, Invalid Objects in the Database is compiled and made sure that there are no new invalids. All the contents from $AU_TOP/discover is copied to the Local Machine where Discoverer Administrator is Installed. Once the download is completed, Import the Patch Contents using adupdeul.sh using MKS Toolkit and Refresh all the Business Area. Caution: New Business Area must be refreshed after importing the EEX Files from the Patch Contents Services / Component Affected: All Discoverer related Services Downtime Estimation: This action plan depends on the Database Load. Importing will depend on the Patch Size. It is always recommeded to review the Patch Readme to perform the action plan regarding the Refresh of Business Area. If there are no specific Business Area specified on the Readme of the Patch then it is recommended to perform a full Refresh of Business Area. Overall Downtime Estimation will be around 8 Hours. Action Plan: 1. Take an EUL User Database Export ( User : EUL5_US ) Operations will take the export dump and move it to $AU_TOP/discover. 2. Connect to the 10g Discoverer Instance

3. Make sure TNSPING Works towards the EBSO Instance from the 10g Discoverer Instance. 4. Regenerate Business Views by running the "Generate All Business Views" Resp: Business View Setup responsibility. 5. After Regeneration of the Business Views has been completed, check the Business View Generator output file. It should not contain any errors. 6. Recompile all objects in the APPS schema using adadmin. 7. Ensure the BIS views exist and all BIS views are valid by issuing the following command in SQL*Plus: $ sqlplus apps/@ SQL> select object_name from user_objects where object_type = 'VIEW' and status = 'INVALID' and ( object_name like '%FV_%' or object_name like '%FG_%' or object_name like '%BV_%' or object_name like '%BG_%' ); If necessary, recompile those objects so that all views are valid. The Discoverer refresh process may stop if a select statement from a invalid BIS View causes the error. 8. Mount EBSO Application Tier Server Node $AU_TOP/discover to Oracle10gAS Discoverer Instance. 9. Import Discoverer Content for Release 11i using adupdeul.sh mode=driver sh adupdeul.sh connect=sysadmin/sysadmin-password@<;EBSO_DB> resp="System Administrator" gwyuid=APPLSYSPUB/PUB fndnam=APPS secgroup="Standard" topdir= language=US eulprefix=EUL5 eultype=OLTP mode=driver driver=u123456.drv iashome= logfile=import_complete_eul_us.log 10. Refresh the Discoverer 5i End User Layer Special Instruction: If there are a list of Patches available , then all the EEX related to a Patch can be imported using a single adupdeul.sh command. Say the Patch Numbers are 123456 , 123457 and 123458 , then the command will be; sh adupdeul.sh connect=sysadmin/sysadmin-password@<;EBSO_DB>

resp="System Administrator" gwyuid=APPLSYSPUB/PUB fndnam=APPS secgroup="Standard" topdir= language=US eulprefix=EUL5 eultype=OLTP mode=driver driver=u123456.drv,u123457.drv,u123458.drv iashome= logfile=import_complete_eul_us.log

Refreshing 10g Business Area using Discoverer Admin Edition and adrfseul.sh
Using Discoverer Admin Edition: 1. Connect to the Discoverer Administrator Server 2. Add the TNS Entry to D:\ODisc10g\network\admin\tnsnames.ora 3. Ensure that TNSPING works for the TNS entry made in step 2. 4. Open Discoverer Administrator Tool D:ODisc10g\bin\dis51adm.exe 5. Connect as SYSADMIN (Make sure you check the Box "Oracle Application User") 6. Open all the Business Area 7. Select all the Business Area 8. Select File >> Refresh , this process involves two steps. The first step validates , it gives a difference report at the end. If there are differences then the second step refresh continues. Using adrfseul.sh: 1. Mount EBSO Application Tier Server Node $AU_TOP/discover to Oracle10gAS Discoverer Instance. 2. Start the Refresh Process by Executing the below command sh adrfseul.sh connect=sysadmin/@ resp="System Administrator" gwyuid=APPLSYSPUB/PUB fndnam=APPS secgroup="Standard" eulschema=EUL5_US eulpassword= twotask= iashome=$ORACLE_BI_HOME logfile=refresh_eul_us_.log


								
To top