How To Create Linux LVM In 3 Minutes
Copyright © Walker 02 Jul 2007 23:22
What’s LVM? Why using Linux Logical Volume Manager or LVM?
These questions are not the scope here. But in brief, the most attractive feature of Logical Volume Manager is to make disk management easier in Linux! Basically, LVM allows users to dynamically extend or shrink Linux “partition” or file system in online mode! The LVM can resize volume groups (VG) online by adding new physical volumes (PV) or rejecting those existing PVs attached to VG.
A visualized concept diagram of the Linux Logical Volume Manager or LVM.
A visualized concept diagram of the Linux Logical Volume Manager or LVM
In this 3-minutes Linux LVM guide, let’s assume that
* The LVM is not currently configured or in used. Having say that, this is the LVM tutorial if you’re going to setup LVM from the ground up on a production Linux server with a new SATA / SCSI hard disk.
* Without a luxury server hardware, I tested this LVM tutorial on PC with the secondary hard disk dedicated for LVM setup. So, the Linux dev file of secondary IDE hard disk will be /dev/hdb (or /dev/sdb for SCSI hard disk).
* This guide is fully tested in Red Hat Enterprise Linux 4 with Logical Volume Manager 2 (LVM2) run-time environment (LVM version 2.00.31 2004-12-12, Library version 1.00.19-ioctl 2004-07-03, Driver version 4.1.0)!
How to setup Linux LVM in 3 minutes at command line?
1. Login with root user ID and try to avoid using sudo command for simplicity reason.
2. Using the whole secondary hard disk for LVM partition:
fdisk /dev/hdb
At the Linux fdisk command prompt,
1. press n to create a new disk partition,
2. press p to create a primary disk partition,
3. press 1 to denote it as 1st disk partition,
4. press ENTER twice to accept the default of 1st and last cylinder – to convert the whole secondary hard disk to a single disk partition,
5. press t (will automatically select the only partition – partition 1) to change the default Linux partition type (0×83) to LVM partition type (0x8e),
6. press L to list all the currently supported partition type,
7. press 8e (as per the L listing) to change partition 1 to 8e, i.e. Linux LVM partition type,
8. press p to display the secondary hard disk partition setup. Please take note that the first partition is denoted as /dev/hdb1 in Linux,
9. press w to write the partition table and exit fdisk upon completion.
3. Next, this LVM command will create a LVM physical volume (PV) on a regular hard disk or partition:
pvcreate /dev/hdb1
4. Now, another LVM command to create a LVM volume group (VG) called vg0 with a physical extent size (PE size) of 16MB:
vgcreate -s 16M vg0 /dev/hdb1
Be properly planning ahead of PE size before creating a volume group with vgcreate -s option!
5. Create a 400MB logical volume (LV) called lvol0 on volume group vg0:
lvcreate -L 400M -n lvol0 vg0
This lvcreate command will create a softlink /dev/vg0/lvol0 point to a correspondence block device file called /dev/mapper/vg0-lvol0.
6. The Linux LVM setup is almost done. Now is the time to format logical volume lvol0 to create a Red Hat Linux supported file system, i.e. EXT3 file system, with 1% reserved block count:
mkfs -t ext3 -m 1 -v /dev/vg0/lvol0
7. Create a mount point before mounting the new EXT3 file system:
mkdir /mnt/vfs
8. The last step of this LVM tutorial – mount the new EXT3 file system created on logical volume lvol0 of LVM to /mnt/vfs mount point:
mount -t ext3 /dev/vg0/lvol0 /mnt/vfs
To confirm the LVM setup has been completed successfully, the df -h command should display these similar message:
/dev/mapper/vg0-lvol0 388M 11M 374M 3% /mnt/vfs
Some of the useful LVM commands reference:
vgdisplay vg0
To check or display volume group setting, such as physical size (PE Size), volume group name (VG name), maximum logical volumes (Max LV), maximum physical volume (Max PV), etc.
pvscan
To check or list all physical volumes (PV) created for volume group (VG) in the current system.
vgextend
To dynamically adding more physical volume (PV), i.e. through new hard disk or disk partition, to an existing volume group (VG) in online mode. You’ll have to manually execute vgextend after pvcreate command that create LVM physical volume (PV).
Tuesday, June 22, 2010
Friday, June 4, 2010
Oracle Apps Stuff
Dedicated to All Oracle Apps DBA / Core DBA in the World !!!
Concurrent Managers
Concurrent processing is at the core of every oracle applications instance. Over the years Oracle Applications has come through a number of changes but concurrent processing has retained itself and in fact got new features added on to its self with very new version.
The request processing on Oracle Applications takes place through the implementation of concurrent Managers. These Managers allow for a concurrent processing of jobs and also scheduling and queuing of jobs.
The objective of the current post is to go through a overview of Concurrent Managers and concurrent processing in Oracle Applications.
The default installation of Oracle Applications comes with a number of pre defined concurrent managers however you can create your custom concurrent managers to spread out the load of your job processing.
Apart from taking care of the load of your jobs the concurrent managers can also be made to schedule periodic jobs and you could also have the concurrent managers run in specific workshifts thus allowing specific programs to be run with specific priority and specific times.
Concurrent managers also allows you to tweak the number of concurrent process that it can handle concurrent if the request exceed this prescribed limt they are automatically put on pending state. The processing of a request takes place based on the time of request submission and priority of the request submitted.
As mentioned there are different types of concurrent mangers in Oracle Applications among them the three important manager which are required by any oracle applications installation are stated below
* Internal Concurrent Manager (ICM) - The Internal concurrent manager or the ICM is the 'master' concurrent manager. Its primary responsibility is to take care of the starting and stopping of all other concurrent managers that have been defined in the system and activated. However once the other managers are up and running the ICM does not play much of a role. It is for the same reason that despite your ICM being down oracle applications continues to perform as expected most of the times. That is you would be able to continue submitting requests even with the ICM down. However there are cases where you can evolve your ICM to do larger roles like acting as a conflict resolution manager or in case you have generic service management enabled your ICM can take care of starting and stopping other application services also.
* Standard Concurrent Manager - This the core manger and the most hard working of the three. It takes care of managing all the concurrent requests in the system. If however specific programs have been defined to use specific concurrent manager they would be taken care by that manager in all other cases the standard manager will be the default concurrent manager and will process the request. That is by default all the concurrent programs are defined to be run by the standard manager and they should not be excluded unless you have included them to run by an other manager. Though we can change a few setting of the standard concurrent manager like the number of process it can handle, most of the other definition should remain unchanged.
* Conflict Resolution Manager (CRM) - The conflict resolution manager of the CRM is responsible of handling any conflicts that might occur within the concurrent programmes. This conflict could be as a result of various reasons for example there might be a business requirement that two instances of a particular report should not be executed at the same time or during a particular period. In case such a scenario occurs the CRM is responsible for taking care of such request. As in the case of the standard concurrent manager the definition for the CRM should be rarely changed.
Apart from these three concurrent manages there is another type of concurrent manager known as the Transaction Manager also exists. The transaction manager is responsible for taking the load off the concurrent request table for pooling the request submitted by the user.The transaction manager takes care of these requests and sends it to standard manager directly.In a RAC environment the Transaction manager is required to be activated on each node of the RAC environment.
From the front end you could view the status of your concurrent manager by logging with the System Administration responsibility and going to the Concurrent Manager administer screen.
The concurrent managers are like other process which run on the oracle applications executable FNDLIBR. The FNDLIBR executable is located at $FND_TOP/bin.
You could also grep the FNDLIBR executable to check if any concurrent manager process are running
$ ps -ef|grep FNDLIBR
The $FND_TOP/sql/afcmstat.sql script gives you a list of concurrent managers and their respective status.
The status of concurrent managers and the nodes on which they are configured can also be known from the Oracle Applications manager.
Posted by Oracle Apps DBA Labs at 10:50 PM 0 comments
Check Versions of Oracle Applications Components
Help-> About Oracle Applications from any of the forms sessions.
Or
Connect as apps user
select release_name from apps.fnd_product_Groups;
Version of Applications Forms (fmb,fmx) or reports (rdf)
To find the version of any oracle applications files
strings -a ICQTYED.fmx | grep Header
Alternatively you can also use the adident command
adident Header ICQTYED.fmx
Version of a Java class File
To find the version of a java class file
$ strings | grep '$Header'
Version of JDBC
In your middle tier, edit the jserv.properties file located in the iAS_ ORACLE_HOME/Apache/Jserv/etc directory
Locate the wrapper.classpath that is pointing to the jdbc zip file
/d01/oracle/viscomn/java/jdbc14.zip
Or
http://.:port/OA_HTML/jsp/fnd/aoljtest.jsp
Look for String ” JDBC driver version” under Connection String
Version of Apache
Go to the $iAS/Apache/Apache/bin directory and enter the following command:
$ httpd -version
Version Of The OA Framework
To find out the version of your Oracle appplication Framework
Option 1
http://.:port/OA_HTML/OAInfo.jsp
Option 2
adident Header %FND_TOP%/html/OA.jsp
adident Header %OA_HTML%/OA.jsp
Note: OA.jsp should be of the same version in both the places
Version of Application Product or Patch Set Level.
One of the most common things you will asked by your support engineer is the version or commonly known as the patch set level of your Oracle Applications product, while or after raising your service request. You can query this by logging on to your application database as the apps user.
select patch_level from fnd_product_installations where patch_level like '%AD%';
Version of Discoverer
cd $APPL_TOP/admin
grep -I s_disco_ver_comma **xml
Version of OJSP
Log in to the application server as the applmgr user
cd $OA_HTML
edit the jtflogin.jsp file to add the following line
OJSP Version: <%= application.getAttribute("oracle.jsp.versionNumber") %>
clear your cache and bounce your apache server
soruce the jtflogin.jsp from your browser
http://[your web server]:[your port]/OA_HTML/jtflogin.jsp
OJSP Version: 1.1.3.5.2
Version of JSP
Log on to your HTTP Server node, and change to the OA_HTML directory.
Using a text editor, create a file called test.jsp with only the following line:
<%= application.getAttribute("oracle.jsp.versionNumber") %>
Access this JSP from a web browser, using the URL:
http://[your web server]:[your port]/OA_HTML/test.jsp
Version of Portal
select fnd_oracle_schema.getouvalue(’PORTAL’) from dual;
FND_ORACLE_SCHEMA.GETOUVALUE(’PORTAL’)
——————————————————————————–
PORTAL30 <= to retrieve the portal user
select version from PORTAL30.wwc_version$;
VERSION
——————————————————————————–
3.0.9.8.1
or
begin
execute immediate 'select fnd_oracle_schema.getouvalue(''PORTAL'') from
dual' into portal_user;
begin
execute immediate 'select user_name from fnd_user where user_name like
upper('''||portal_user||''')'
into portal_user_name;
if portal_user is not null then
execute immediate 'select version from '||portal_user||'.wwc_version$'
into portal_ver;
check_message := ' [PASS] Your Applications database contains
Portal version: '||portal_ver;
dbms_output.put_line(check_message);
end if;
exception
when no_data_found then
null;
end;
exception
when others then
null ;
end;
Version of XML Parser
You can find out the version of your XML Parser using the following query
SQL> select WF_EVENT_XML.XMLVersion() XML_VERSION
2 from sys.dual;
XML_VERSION
--------------------------------------------------------------------------------
Oracle XDK Java 9.0.4.0.0 Production
Version of XML Publisher
To check weather XML publisher is installed or not you can query the FND_PRODUCT_INSTALLATIONS table or you can lookup the
reports in Oracle Applications Manager. You can find out the version for your XML publisher from the output of your report or from MetaInfo.class file.
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.
Version of WorkFlow
You can find out the version of your workflow using the following query
SQL> select TEXT Version from WF_RESOURCES
2 where TYPE = 'WFTKN' and NAME = 'WF_VERSION';
VERSION
--------------------------------------------------------------------------------
2.6.0
Version of Oracle Login Server
begin
execute immediate 'select fnd_oracle_schema.getouvalue(''LOGINSERVER'') from
dual' into sso_user;
begin
execute immediate 'select user_name from fnd_user where user_name like
upper('''||sso_user||''')' into
sso_user_name;
if sso_user is not null then
execute immediate 'select version from '||sso_user||'.wwc_version$' into
sso_ver;
check_message := ' [PASS] Your Applications database contains Login
Server version: '||sso_ver;
dbms_output.put_line(check_message);
end if;
exception
when no_data_found then
null;
end;
exception
when others then
null ;
end;
Posted by Oracle Apps DBA Labs at 10:38 PM 0 comments
Check Developer Forms Patch Set Level
1.Login though applmgr account
2.Create a file fpslevel.sh
cat > fpslevel.sh
copy the following contents in fpslevel.sh file.
#!/bin/sh
########################################
#fpslevel.sh - Used to find out the forms patchset level
########################################
clear
PatchLevel=0
f60gen > /dev/null 2>&1
if [ $? -eq 127 ]
then
echo "Environment is not Set"
else
PatchLevel=`f60gen | grep '(Form Compiler)' | grep -v Release | awk -F. '{print $5}' `
echo 'Forms Patchset Level :' ` expr $PatchLevel - 9 `
fi
echo 'For More Information '
echo 'Refer to Metalink Note:232313.1 for More Information'
3.Grant the execute permission to the script
chmod 777 fpslevel.sh
4.Execute the script.
./fpslevel.sh
Posted by Oracle Apps DBA Labs at 9:47 PM 0 comments
Check Database Performance by these queries
SELECT * FROM v$log;
SELECT COUNT(*) FROM v$process;
SELECT COUNT(*) FROM v$session WHERE status='ACTIVE'
SELECT COUNT(*) FROM dba_locks WHERE blocking_others='Blocking';
SELECT COUNT(*) FROM dba_objects WHERE status='INVALID' AND owner IN ('ABC','DEF') GROUP BY owner;
SELECT A.Tablespace_Name,TRUNC((SUM(A.Tots)/1024/1024),3) Tot_Size,
TRUNC(((SUM(A.Tots)/1024/1024)-(SUM(A.Sumb)/1024/1024)),3) Tot_Used
FROM (
SELECT Tablespace_Name,0 Tots,SUM(Bytes) Sumb,
MAX(Bytes) Largest,COUNT(*) Chunks
FROM Sys.Dba_Free_Space A
GROUP BY Tablespace_Name
UNION
SELECT Tablespace_Name,SUM(Bytes) Tots,0,0,0
FROM Sys.Dba_Data_Files
GROUP BY Tablespace_Name) A, V$instance B
GROUP BY A.Tablespace_Name
ORDER BY A.Tablespace_Name
SELECT (1-(SUM(DECODE(name,'physical reads', value, 0))/
(SUM(DECODE(name,'db block gets', value, 0)) +
SUM(DECODE(name,'consistent gets', value, 0))))) * 100 "Read Hit Ratio"
FROM v$sysstat
SELECT d.status "Status",
d.tablespace_name "Name",
d.CONTENTS "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %"
FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.CONTENTS "Type", d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %"
FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes
FROM v$temp_extent_pool GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY'
ORDER BY 7
Posted by Oracle Apps DBA Labs at 9:42 PM 0 comments
AutoConfig
What is Autoconfig?
Autoconfig is a tool for configuring application instance.
What are the programs and program name involved in Auto Config?
You can see perl,shell,cmd and java programs.
1.adconfig.pl- Its a main program which inturn call Java APIs for configuring appl instance.
args-appl context file location
2.adautocfg.sh/adautocfg.cmd-This program inturn calls adconfig.pl
args-apps password
3.adconfig.sh-This program again calls adconfig.pl
These three programs used for running Auto config.
4. adchgatname.pl - This program will validating the APPL_TOP name in context file .If the APPL_Top name is changed, then this utility will take care of storing the old APPL_TOP name in APPL_TOP/admin/ATName.txt.Adconfig.pl calls this program .
Posted by Oracle Apps DBA Labs at 9:38 PM 0 comments
Labels: AUTOCONFIG
Archiving Oracle Applications
Why Archive?
After an implementation of Oracle Application which has been around from some years, you would see a substantial growth in the database size.
As your Oracle Application continues to grow and upgrade the database also grows with it. A lot of data which resides in this database would no longer be needed to be updated and a significat amount of this data would rarely be used for any kind of Reporting. Nevertheless we cannot simply go ahead and purge or trash this data for obvious compliance and regulatory reasons. Another added advantage of archiving is that you can implement data masking in your non production environment while in the process of archiving, this reduces the risks from exposing your production data after subsequent clones.
There are many challenges associated with a large growing database like
* Increase in storage costs.
* Decrease in system responsiveness.
* Increase in times taken to complete cloning cycles.
* Increase in recovery times in cases of system failures.
What to archive ?
This is the most difficult when it came to implement an archive policy. Fortunately most of the archive solutions available in the market came with an out of box solution for Oracle Applications which made sure to preserve the referential integrity of the data. since data in an application is in its normalized form its important to ensure that the archived data is valid enough to be reported from when required. To ensure this a meta data repository is bundled along with the solution which defines the rules for archiving and the associated constraints.
Archiving Options
when it came to archiving options available with the ebusiness suite. these were quite a few of them available. Most of these solutions adopted a similar approach to archiving.
After going through the popular ones solutions available i can categorize them in the following groups.
* Proprietary File Type Based Solutions.
* Archive Database Based Solutions.
Proprietary File Type Based Solutions
Under these kinds of archiving solution the data is archived from Oracle Applications and stored in a flat file system which is generally in a proprietary format. The main and the biggest advantage of adopting such kind of the solution is that the need to maintain a large database is reduced. Also the archived data in the file system can be further compressed further to shrink the size. The major disadvantage of this approach is that you loose the capacity to do a seemless reporting from within your application. That is in order to be able to report from your archived data you would need to be depended on the proprietary reporting tool, rather than being able to do so from with oracle applications.
Archive Database Solutions
In the archive database based solutions, the archived data is stored in a separate database as opposed to a file system. This archive database could reside on a low cost second tire storage system. The main advantage in this structure is that you have the ability to a get to your archive data from within the application itself, in other words you do not need any third party tools to access your data.
One of the disadvantages of this approach is that, this would result in atleast one more database to maintain apart from the production system despite of the fact that this database would be on a lost cost second tier storage.
Limitations.
One of the main limitations that most of these archiving solutions had that, although most of them were able to archive the data from your application they failed to do a database reorg.
After the archiving cycle the database would need to be reorged manually to reclaim the space and see an increase in the system performance.
Also in the even of applications upgrade when the structure of your database tables changed the process of propagating the changes in the archives, though available was a complex process.
Posted by Oracle Apps DBA Labs at 9:33 PM 0 comments
AD Utilities
AD Utilities (Applications DBA Utilities) are set of Oracle supplied utilities that are used to administer Oracle Applications.
Some AD utilities are given below...
adadmin - Performs maintenance tasks for Oracle Applications.
adchkdig - Checks the integrity of Oracle Applications patches downloaded from OracleMetaLink.
adutconf.sql - Reports standard information about the installed configuration of Oracle Applications.
adctrl - Manages parallel workers in AD Administration and AutoPatch.
adident - Reports the version and translation level of an Oracle Applications file.
adncnv - Converts a file from one character set to another.
admrgpch - Merges multiple patches into a single merged patch.
adrelink.sh - Relinks Oracle Applications executable programs with the Oracle server product libraries.
adsplice - Adds off-cycle products.
adtimrpt.sql - Reports a summary of the timing for jobs run by parallel workers.
adpatch - Applies patches and other system updates.
admsi.pl - Generates customized installation instructions for a patch.
rapidwiz - Provides a wizard for entering parameters that are specific to a new installation or an upgrade of an Oracle Applications system.
Posted by Oracle Apps DBA Labs at 9:32 PM 0 comments
Change DOMAIN NAME on Oracle Apps (11.5.10.2)
I have changed the domain name ies.apps.com to ies.oracle.com as follows
Step 1
Enter in to “vi /etc/hosts” then change the host name as u want
e.g.: (ies.apps.com) to (ies.oracle.com)
Enter to “vi /etc/sysconfig/network” then change to the new host name
e.g.: (ies.apps.com) to (ies.oracle.com)
Then run $ service network restart
Then reboot the computer
NOTE: Ensure that backup must be taken of .xml file of both DB Tier and Apps Tier…
Step 2
Go to DB Tier…..
“$ vi /v01/oracle/visdb/appsutil/VIS_ies.xml”
Values of following parameters you have to change:
Change the new values to “ies.oracle.com” or as u want
1. global_database_name oa_var="s_global_database_name"
2. domain oa_var="s_domainname"
Step 3
Go to Apps Tier….
“$ vi /v01/oracle/visappl/admin/VIS_ies.xml”
Values of following parameters you have to change:
1. domain oa_var="s_dbdomain"
2. domain oa_var="s_admdomain"
3. domain oa_var="s_cpdomain"
4. domain oa_var="s_wfdomain"
5. domain oa_var="s_smtpdomainname"
6. domain oa_var="s_formsdomain"
7. chronosURL oa_var="s_chronosURL"
8. domain oa_var="s_metdomain"
9. domain oa_var="s_mwadomain"
10. discoinstancename oa_var="s_discoinstance"
11. disco_machine oa_var="s_disco_machine"
12. webentrydomain oa_var="s_webentrydomain"
13. proxybypassdomain oa_var="s_proxybypassdomain"
14. apps_portal_url oa_var="s_apps_portal_url"
15. sysadminmail oa_var="s_sysadmin_mail" osd="unix"
16. cookiedomain oa_var="s_cookie_domain"
17. login_page oa_var="s_login_page"
18. jdbc_url oa_var="s_apps_jdbc_connect_descriptor"
19. ohs_serveradmin oa_var="s_ohs_serveradmin"
20. domain oa_var="s_webcache_domain"
21. domain oa_var="s_domainname"
22. FORMS60_MAPPING oa_var="s_f60map"
Step 4
Then manually login to apps/apps@vis
Then manually you have to update the following table in database:
1. HOME_URL in ICX_PARAMETERS
Then login to /sys as sysdba
Then manually you have to update the following table in database:
2. GLOBAL_NAME in GLOBAL_NAME
Then exit from sqlplus….
Step 5
Then go to “/v01/oracle/visdb/9.2.0/network/admin”
Then change the all old host name (apps) to new host name (oracle) of the following scripts:
$ vi listner.ora
$ vi tnsname.ora
$ vi sqlnet.ora
Step 6
Run Auto Config on DB Tier…
“/v01/oracle/visdb/9.2.0/appsutil/scripts/VIS_ies/”
Run ./adautocfg.sh app/apps
Run Auto Config on Apps Tier…
“$ /v01/oracle/viscomm/admin/scripts/VIS_ies/”
Run $ ./adautocfg.sh app/apps
Posted by Oracle Apps DBA Labs at 9:24 PM 0 comments
Sunday, February 8, 2009
OPatch in Apps
In Apps we need to use Opatch and adpatch for patching oracle apps. Why we need to use two utility to patch oracle apps.opatch is used to patch all oracle homes (for R12..10.1.2,10.1.3 and 10.2.0.2 oracle homes). Apps patches we need to use to adpatch.
How to use Opatch?
Opatch is one of the easiest and safest for patching oracle apps because you can rollback opatch if you find any issues applying the patches
1. From the opatch read me make sure which oracle home you want to patch
2. Shutdown the instance related to particular oracle home.
3. Set the oracle home in the env .Set the opatch in the path.
4. Follow the readme whether opatch needs up gradation.
5. cd to the patch area and opatch apply
6. Proceed the post install steps
7. Run cpu_root.sh to give some permissions to the executables.
8. For rollback use opath rollback –id
9. “opatch lsinventory “ is used to list all the patches
10. “opatch lsinventory –details” is used to find the version belongs to particular oracle home.
$ ORACLE_HOME/OPatch/ (Unzip Patch number)
$ cd ..
$ ./opatch apply (Patch number)/
Then system will ask for respond……. Y | N….
Give Y
Posted by Oracle Apps DBA Labs at 11:53 PM 0 comments
Autoconfig in Oracle Apps 11i / R12 / 12i
In Oracle Applications 11i & R12 (12i) all the configuration parameters are captured in two context files:
Application context file called the .xml contains the configuration information for the application tier & is located in $APPl_TOP/admin for each node of application tier.
Database context file called the .xml contains the configuration information for the database tier & is located in $ORACLE_HOME/appsutil/.
Details stored in the context file contains host names,domain name , directory structure, port numbers used ……
The AutoConfig feature of Oracle application manager(OAM) is used to update & manage context files.
Updating Context file
Suppose we want to update the active port from to 80 , for this we will edit the application context file as follows:
Step 1 :Start all services
$ $COMMON_TOP/admin/scripts//adstrtal.sh APPS/
Step 2 :log on to OAM & click site map.
Step 3:Click AutoConfig from the list of System Configuration features in the Administration tab.
Step 4 : Click the Edit Parameter button for the application tier context file to access the Context File Parameters page & then click the system tab.
Step 5 : Click the Focus icon (blue triangle) for oa_web_server. This expands oa_web_server and shows the web server related parameters.
Step 6:Update the Web Port (s_active_webport) parameter from <> to 80.
Step 7 : Save it & answer the tab.Then logout of OAM
AutoConfig Script
The autoconfig script uses information from the context file to generate all applications configuration files & updates database profiles. It is located in
Application tier: $COMMON_TOP/admin/scripts/CONTEXT_NAME/adautoconfig.sh
Database tier: $ORACLE_HOME/appsutil/scripts/CONTEXT_NAME/adautoconfig.sh
Now we run the autoconfig script to reconfigure the oracle application system with the updated Applications context.
Step 1 : Stop all services
$ $COMMON_TOP/admin/scripts//adstpall.sh APPS/
Step 2:Run the autoconfig script, $adautocfg.sh & respond to the prompts.
Step 3:Check log files for any errors.log file for autoconfig is adconfig.log & is located in
/admin//log/MMDDhhmm/
Step 4: Restart all services :
$ $COMMON_TOP/admin/scripts//adstrtal.sh APPS/
Rolling back an autoconfig session
Each execution of AutoConfig creates a roll back script in case you want to revert back to the previous configuration settings. For this we perform the following steps:
Step 1 :Shut down all services:
$ cd $COMMON_TOP/admin/scripts/
$ adstpall.sh APPS/
Step 2:Restore AutoConfig session.
$ cd $APPL_TOP>/admin//out/MMDDhhmm
$ restore.sh
Step 3 : Restart all services:
$ $COMMON_TOP/admin/scripts//adstrtal.sh APPS/
Restoring the AutoConfig updated files and restarting the services allows you to log on to OAM.
Restoring the previous configuration
Now we run OAM AutoConfig to restore the Applications Context.
Step 1 :Log on to the Oracle Application Manager (OAM) & click site map.You will be able to log on but some services will be down.
Step 2: Click AutoConfig from the list of System Configuration features in the Administration tab.
Step 3 : Click the Show History button for the application tier context file to access the Context File History page.
Step 4 :Click the Show Differences from Current Configuration button to see the recently updated parameters & Select Restore Previous Value for the Web Port and Discoverer Port parameters.
Step 5 :Save & respond the tabs. Log out of OAM.
Step 6 :Shut down all services:
$ cd $COMMON_TOP/admin/scripts/
$ adstpall.sh APPS/
Step 7 :Run the AutoConfig script, adautocfg.sh & Respond to the prompts.
Step 8 : View the log file and check for any errors.
Step 9 : Restart all services:
$ $COMMON_TOP/admin/scripts//adstrtal.sh APPS/
Step 10 :Log on to the Oracle Application Manager (OAM)
If the status of some services appears to be down, click the Refresh icon to update the
status.
Related : Metalink Notes
218089.1 Autoconfig FAQ
165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i
387859.1 Using AutoConfig to Manage System Configurations in Oracle Applications Release 12
270519.1 Customizing an AutoConfig Environment
364927.1 How To Run Autoconfig On Database Tier (DB-Tier)
341322.1 How to change the hostname of an Applications Tier using AutoConfig
338003.1 How to change the hostname and/or port of the Database Tier using AutoConfig
315674.1 How To Verify if Autoconfig is Enabled on 11.5.x
391406.1 How to get a clean Autoconfig Environment
Posted by Oracle Apps DBA Labs at 11:37 PM 1 comments
Labels: AUTOCONFIG
Blocks , Extents, Segments in Oracle Database
I am going to cover basic concepts and Architecture of Oracle Database (9i, 10g, and 11g)
A. Data Blocks is smallest logical unit to store Oracle Data.
ii) One data block represent specific number o
f bytes on physical hard disk.
iii) Data Block in Oracle is also called as logical block
iv) Data Block size is usually multiple of operating system block size
v) You can have multiple block sizes with in single database (max. five)
vi) Block Size is specified by initialization parameter DB_BLOCK_SIZE
vii) Format of Data Block is
a) Header : contains generic information like block address and type of segment (index, data..)
b) Table Directory : contains information about table having rows in that block
c) Row Directory : contains information about actual row contained in that block
d) Free Space : available space in data block for additional row or update of row which require more space.
e) Row Data : contains table or index data.First three component of data block (Header, Table & Row directory) collectively known as Overhead
B. Extent is collection of contiguous data blocks.
ii) One or more extents make up a segment.
C. Segment is set of extents allocated for specific data structure (like table or index).
ii) Various kind of segments are table, index, cluster, rollback, temporary …
iii) Important views for segments are dba_segments, user_segments, all_segments
iv) In a Segment, first block of first extent contains segment header information
Things to note w.r.t. Segment, Extent & Datablocks
i) Segment and its associated extents are stored in one table space.
ii) Extents of a segment may not be contiguous on disk
iii) Segment can span multiple datafiles of a particular tablespace (Information on tablespace & datafiles coming soon) but extent can contain data from only one datafile.
Posted by Oracle Apps DBA Labs at 11:24 PM 0 comments
Labels: Oracle Core DBA
ADPATCH Options
You can execute adpatch by logging in as the applications OS user and sourcing the appropriate environment file.
# su - applmgr
$cd SAMAPPL
$. ./APPSORA.env
$adpatch
By default adpatch does not take any clause, but there are some clauses that you could use with adpatch
Running a patch in test mode
You can use the apply clause with adpatch to specify weather to run the patch in TEST mode or not, when you run the patch in test mode it does not do any changes but runs generates a log fiule with all the actions it would have performed.
$ adpatch apply=n|y
The default is apply=y
Pre-install Mode
You can also run a patch in pre install mode, this would be done normally during an upgrade or consolidated update. When a patch is applied in a preinstall mode the all the AD utilities are updated before the upgrade or update.
$ adpatch preinstall=y
The default is preinstall=n
Other Options with adpatch
You can use the options clause to specify some of the other options available with adpatch.
Autoconfig
You can use the options=noautoconfig top specify autopatch that you do not wish to run autoconfig as a part of the patch application. This can be useful when applying a large number of patches when they are not merged. By default autoconfig is run as a part of adpatch.
$ adpatch options=noautoconfig
Checkfile
The chekfile option of adpatch tells adpathc to check for already executed exec, SQL, and exectier commands.You can use options=nocheckfile skips this check, however this can cause performance overheds so should be used only when specified.
$ adpatch options=nocheckfile
Compile Database
By defaulty autopatch compiles the invalid objects after the patch application, in case you wish not to do so you can specify options=nocompiledb along with autopatch.
$ adpatch options=nocompiledb
Compile JSP
By defaulty autopatch compiles the java server pages (jsp) after the patch application, in case you wish not to do so you can specify options=nocompilejsp along with autopatch.
$ adpatch options=nocompilejsp
Copy Portion
If you wish adpatch not to execute the commands present in the copy driver portion of the patch you can use the options=nocopyportion.
$ adpatch options=nocopyportion
Database Portion
If you wish adpatch not to execute the commands present in the database driver portion of the patch you can use the options=nodatabaseportion.
$ adpatch options=nodatabaseportion
Generate Portion
If you wish adpatch not to execute the commands present in the generate driver portion of the patch you can use the options=nogenerateportion
$ adpatch options=nogenerateportion
Maintenance Mode
If you wish to apply a patch regardless of the system being in maintenance mode you can use options=hotpatch.
$adpatch options=hotpatch
Integrity Check
If you wish to check the integrity of the patch you can use the options=integrity. Since metalink patches are pre checked for their integrity it is generally not required to do an explicit check and the default value is nointegrity.
$ adpatch options=integrity
Maintain MRC
You can use the maintainmrc option to specify weather you wish adpatch to execute the Maintain MRC schema as a part of the patch application or not. By default maintain MRC is done for standard patches and is disbaled for tarnslation and documentation patches.
$ adpatch options=nomaintainmrc
Pre requisite Patch Check
If you wish adpatch not to check for pre requisite patches before application of the main patch you can use options=noprereq.By default pre requsite checking is enabled.
$ adpatch options=noprereq
Validate Schemas
If you wish adpatch to explicitly validate all the registed schems by making a connection you can use options=validate. By default this validation is not performed.
$ adpatch options=validate
Java Classes
If you wish adpatch not to copy new java classes from the patch you can use options=nojcopy.By default java classes are copied.
$ adpatch options=nojcopy
Force Copy
By default adpatch copies the files without check the version of the existing files already present on the system.If you do not wish the newer version of the file to be replaced by the older version contained in the patch use options=noforcecopy.
$ adpatch options=noforcecopy
Relinking
If you wish adpatch not do perform relinking you can use options=nolink.
$adpatch options=nolink
Generate Forms
If you wish adpatch not to generate the forms files you can specify options=nogenform.
$ adpatch options=nogenform
Generate Reports
If you wish adpatch not to generate the report files you can specify options=nogenrep.
$ adpatch options=nogenrep
You could specify multiple options at the command line using the , delimiter.
$ adpatch options=hotpatch,nojcopy
Posted by Oracle Apps DBA Labs at 11:12 PM 0 comments
10 ways to protect APPS Password
1. Stay current with our latest Security Best Practices
Regularly review the latest version of Best Practices for Securing Oracle E-Business Suite (Note 189367.1). This note is regularly updated and will give security advice covering many different aspects of Applications 11i. For Release 12, see Best Practices For Securing Oracle E-Business Suite Release 12 (Note 403537.1)
2. Regularly change your APPS password
This is an essential activity from a security perspective and needs to be part of your routine operating procedures. Same applies for other schema passwords and SYSADMIN user. As an aside, don't use predicable passwords, or have a system to create passwords, such as using "0ct0ber" for the password in October as this will make it easier to guess
3. Always change passwords as part of a clone process from PROD
It is recomended to change ALL schema passwords and ALL eBiz user passwords in a cloned instance. You can use Removing Credentials from a Cloned EBS Production Database (Note 419475.1) to achieve this. Similarly, you don't want to have any relation in the passwords used for PROD compared to any other instances. Data masking and obfuscation is a large topic in its own right, but is also something you may need to consider doing for the cloned instance to protect sensitive data generally. With Release 12, EM plugin provides some data scrambling facilities
4. Perform data masking on any files sent to outside parties from the PROD system
When you need to send any log files or configuration files, ensure that you scan for any sensitive data before packing the files to be sent. In this article we are concerned about the APPS password, but this applies equally well for other data as well. For example, a crude mechanism would be to use "ed" or "sed" on all files to globally change any occurrences of the APPS password before creating a tar archive to email or upload. You may be uploading files to Oracle Support, or just emailing them within your Organization. Whenever the files are going to someone who cannot access them directly you should always check the files before sending.
5. Create separate schemas with minimal access required for direct database access
If anyone requires direct access to the E-Business Suite database, ensure that you create a new unique schema with the specific permissions required for them to perform their job role. Except for a very few Apps DBAs, there should be no reason that anyone else needs the APPS user password. Sometimes pressures of work make it easier to just give someone APPS access, but this should be resisted and the time taken to provide only the minimum access absolutely required. Every person should also have their own unique login (but this is digressing into a separate area that I'll address in a later article). When considering permissions to allocate, don't be tempted to give read only access to everything, as being able to read sensitive information may be just as damaging as being able to change it.
6. Protect Apps 11i middle tier file system files
These days, there is little need to give anyone UNIX-level access to the servers, but it is still important to ensure the "applmgr" operating system user password is well protected. Also consider whether any of your own startup scripts or monitoring scripts have the APPS password hard coded in them, and protect these scripts with chmod 700 permissions, or remove them if no longer needed
7. Ensure no processes are running with APPS username/password in command line
Generally the APPS password is not listed in "ps" output, but there may be some manual scripts or other processes intermittently running with the APPS password in clear text or trivially encoded. Ensure these scripts are changed to hide the APPS password. In addition, ensure operating system access is restricted to only those who really need it
8. Protect OID access
If you have integrated the E-Business Suite with Oracle Application Server 10g, Single Sign-On, and Oracle Internet Directory, then the Apps user password is stored in the OID database, as it is required for Provisioning to function. The OID administrator and anyone with ldapsearch rights in the Provisioning Profiles will be able to extract the APPS password from OID. This in turn implies the "AppsDN" OID password should be protected in the same way as the APPS password itself. For assistance in security hardening OID, refer to the Oracle Internet Directory Administrator's Guide 10g (10.1.4.0.1) - Part III Directory Security
9. Encrypt SQLNET traffic from Middle Tier to RDBMS
In a previous article, Steven highlighted that ANO is certified with the E-Business Suite. Use encryption to protect the APPS password from network sniffers tracing SQLNET connection packets and deciphering the APPS password on the wire.
10. Allow only specific IP addresses to access RDBMS via SQLNET
Slightly off topic, but if someone has acquired the APPS password they still have to be able to gain access to a tool that can use it. Restricting the IP addresses that can access your Apps database will help minimise this risk. If you are still using "fat" clients (Discoverer or ADI for example) then you will have to weigh up the risks against the administrative overhead. Oracle recommends upgrading to server-based equivalent tools or shared desktop technologies such as Citrix so desktop clients no longer need direct access. This topic is discussed further in E-Business Suite Recommended Set Up for Client/Server Products (Note 277535.1)
Posted by Oracle Apps DBA Labs at 11:09 PM 0 comments
Newer Posts Older Posts Home
Subscribe to: Posts (Atom)
About Me
My Photo
Chandrasekhar Das
View my complete profile
World Wide
free counters
Blog Archive
* ▼ 2009 (19)
o ▼ February (13)
+ Concurrent Managers
+ Check Versions of Oracle Applications Components
+ Check Developer Forms Patch Set Level
+ Check Database Performance by these queries
+ AutoConfig
+ Archiving Oracle Applications
+ AD Utilities
+ Change DOMAIN NAME on Oracle Apps (11.5.10.2)
+ OPatch in Apps
+ Autoconfig in Oracle Apps 11i / R12 / 12i
+ Blocks , Extents, Segments in Oracle Database
+ ADPATCH Options
+ 10 ways to protect APPS Password
o ► January (6)
+ RMAN ---Backup and recovery Methods being followed...
+ MULTINODE INSTALLATION of Oracle 11i Apps
+ Autoconfig FAQ
+ Autoconfig Basics
+ Comparison between Oracle Applications and SAP
+ Concurrent Manager in Oracle Apps
Labels
* AUTOCONFIG (4)
* Backup/Recovery (RMAN) (1)
* Concurrent Manager (1)
* Oracle Core DBA (1)
Counter
StatCounter - Free Web Tracker and Counter
Followers
Concurrent Managers
Concurrent processing is at the core of every oracle applications instance. Over the years Oracle Applications has come through a number of changes but concurrent processing has retained itself and in fact got new features added on to its self with very new version.
The request processing on Oracle Applications takes place through the implementation of concurrent Managers. These Managers allow for a concurrent processing of jobs and also scheduling and queuing of jobs.
The objective of the current post is to go through a overview of Concurrent Managers and concurrent processing in Oracle Applications.
The default installation of Oracle Applications comes with a number of pre defined concurrent managers however you can create your custom concurrent managers to spread out the load of your job processing.
Apart from taking care of the load of your jobs the concurrent managers can also be made to schedule periodic jobs and you could also have the concurrent managers run in specific workshifts thus allowing specific programs to be run with specific priority and specific times.
Concurrent managers also allows you to tweak the number of concurrent process that it can handle concurrent if the request exceed this prescribed limt they are automatically put on pending state. The processing of a request takes place based on the time of request submission and priority of the request submitted.
As mentioned there are different types of concurrent mangers in Oracle Applications among them the three important manager which are required by any oracle applications installation are stated below
* Internal Concurrent Manager (ICM) - The Internal concurrent manager or the ICM is the 'master' concurrent manager. Its primary responsibility is to take care of the starting and stopping of all other concurrent managers that have been defined in the system and activated. However once the other managers are up and running the ICM does not play much of a role. It is for the same reason that despite your ICM being down oracle applications continues to perform as expected most of the times. That is you would be able to continue submitting requests even with the ICM down. However there are cases where you can evolve your ICM to do larger roles like acting as a conflict resolution manager or in case you have generic service management enabled your ICM can take care of starting and stopping other application services also.
* Standard Concurrent Manager - This the core manger and the most hard working of the three. It takes care of managing all the concurrent requests in the system. If however specific programs have been defined to use specific concurrent manager they would be taken care by that manager in all other cases the standard manager will be the default concurrent manager and will process the request. That is by default all the concurrent programs are defined to be run by the standard manager and they should not be excluded unless you have included them to run by an other manager. Though we can change a few setting of the standard concurrent manager like the number of process it can handle, most of the other definition should remain unchanged.
* Conflict Resolution Manager (CRM) - The conflict resolution manager of the CRM is responsible of handling any conflicts that might occur within the concurrent programmes. This conflict could be as a result of various reasons for example there might be a business requirement that two instances of a particular report should not be executed at the same time or during a particular period. In case such a scenario occurs the CRM is responsible for taking care of such request. As in the case of the standard concurrent manager the definition for the CRM should be rarely changed.
Apart from these three concurrent manages there is another type of concurrent manager known as the Transaction Manager also exists. The transaction manager is responsible for taking the load off the concurrent request table for pooling the request submitted by the user.The transaction manager takes care of these requests and sends it to standard manager directly.In a RAC environment the Transaction manager is required to be activated on each node of the RAC environment.
From the front end you could view the status of your concurrent manager by logging with the System Administration responsibility and going to the Concurrent Manager administer screen.
The concurrent managers are like other process which run on the oracle applications executable FNDLIBR. The FNDLIBR executable is located at $FND_TOP/bin.
You could also grep the FNDLIBR executable to check if any concurrent manager process are running
$ ps -ef|grep FNDLIBR
The $FND_TOP/sql/afcmstat.sql script gives you a list of concurrent managers and their respective status.
The status of concurrent managers and the nodes on which they are configured can also be known from the Oracle Applications manager.
Posted by Oracle Apps DBA Labs at 10:50 PM 0 comments
Check Versions of Oracle Applications Components
Help-> About Oracle Applications from any of the forms sessions.
Or
Connect as apps user
select release_name from apps.fnd_product_Groups;
Version of Applications Forms (fmb,fmx) or reports (rdf)
To find the version of any oracle applications files
strings -a ICQTYED.fmx | grep Header
Alternatively you can also use the adident command
adident Header ICQTYED.fmx
Version of a Java class File
To find the version of a java class file
$ strings | grep '$Header'
Version of JDBC
In your middle tier, edit the jserv.properties file located in the iAS_ ORACLE_HOME/Apache/Jserv/etc directory
Locate the wrapper.classpath that is pointing to the jdbc zip file
/d01/oracle/viscomn/java/jdbc14.zip
Or
http://.:port/OA_HTML/jsp/fnd/aoljtest.jsp
Look for String ” JDBC driver version” under Connection String
Version of Apache
Go to the $iAS/Apache/Apache/bin directory and enter the following command:
$ httpd -version
Version Of The OA Framework
To find out the version of your Oracle appplication Framework
Option 1
http://.:port/OA_HTML/OAInfo.jsp
Option 2
adident Header %FND_TOP%/html/OA.jsp
adident Header %OA_HTML%/OA.jsp
Note: OA.jsp should be of the same version in both the places
Version of Application Product or Patch Set Level.
One of the most common things you will asked by your support engineer is the version or commonly known as the patch set level of your Oracle Applications product, while or after raising your service request. You can query this by logging on to your application database as the apps user.
select patch_level from fnd_product_installations where patch_level like '%AD%';
Version of Discoverer
cd $APPL_TOP/admin
grep -I s_disco_ver_comma **xml
Version of OJSP
Log in to the application server as the applmgr user
cd $OA_HTML
edit the jtflogin.jsp file to add the following line
OJSP Version: <%= application.getAttribute("oracle.jsp.versionNumber") %>
clear your cache and bounce your apache server
soruce the jtflogin.jsp from your browser
http://[your web server]:[your port]/OA_HTML/jtflogin.jsp
OJSP Version: 1.1.3.5.2
Version of JSP
Log on to your HTTP Server node, and change to the OA_HTML directory.
Using a text editor, create a file called test.jsp with only the following line:
<%= application.getAttribute("oracle.jsp.versionNumber") %>
Access this JSP from a web browser, using the URL:
http://[your web server]:[your port]/OA_HTML/test.jsp
Version of Portal
select fnd_oracle_schema.getouvalue(’PORTAL’) from dual;
FND_ORACLE_SCHEMA.GETOUVALUE(’PORTAL’)
——————————————————————————–
PORTAL30 <= to retrieve the portal user
select version from PORTAL30.wwc_version$;
VERSION
——————————————————————————–
3.0.9.8.1
or
begin
execute immediate 'select fnd_oracle_schema.getouvalue(''PORTAL'') from
dual' into portal_user;
begin
execute immediate 'select user_name from fnd_user where user_name like
upper('''||portal_user||''')'
into portal_user_name;
if portal_user is not null then
execute immediate 'select version from '||portal_user||'.wwc_version$'
into portal_ver;
check_message := ' [PASS] Your Applications database contains
Portal version: '||portal_ver;
dbms_output.put_line(check_message);
end if;
exception
when no_data_found then
null;
end;
exception
when others then
null ;
end;
Version of XML Parser
You can find out the version of your XML Parser using the following query
SQL> select WF_EVENT_XML.XMLVersion() XML_VERSION
2 from sys.dual;
XML_VERSION
--------------------------------------------------------------------------------
Oracle XDK Java 9.0.4.0.0 Production
Version of XML Publisher
To check weather XML publisher is installed or not you can query the FND_PRODUCT_INSTALLATIONS table or you can lookup the
reports in Oracle Applications Manager. You can find out the version for your XML publisher from the output of your report or from MetaInfo.class file.
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.
Version of WorkFlow
You can find out the version of your workflow using the following query
SQL> select TEXT Version from WF_RESOURCES
2 where TYPE = 'WFTKN' and NAME = 'WF_VERSION';
VERSION
--------------------------------------------------------------------------------
2.6.0
Version of Oracle Login Server
begin
execute immediate 'select fnd_oracle_schema.getouvalue(''LOGINSERVER'') from
dual' into sso_user;
begin
execute immediate 'select user_name from fnd_user where user_name like
upper('''||sso_user||''')' into
sso_user_name;
if sso_user is not null then
execute immediate 'select version from '||sso_user||'.wwc_version$' into
sso_ver;
check_message := ' [PASS] Your Applications database contains Login
Server version: '||sso_ver;
dbms_output.put_line(check_message);
end if;
exception
when no_data_found then
null;
end;
exception
when others then
null ;
end;
Posted by Oracle Apps DBA Labs at 10:38 PM 0 comments
Check Developer Forms Patch Set Level
1.Login though applmgr account
2.Create a file fpslevel.sh
cat > fpslevel.sh
copy the following contents in fpslevel.sh file.
#!/bin/sh
########################################
#fpslevel.sh - Used to find out the forms patchset level
########################################
clear
PatchLevel=0
f60gen > /dev/null 2>&1
if [ $? -eq 127 ]
then
echo "Environment is not Set"
else
PatchLevel=`f60gen | grep '(Form Compiler)' | grep -v Release | awk -F. '{print $5}' `
echo 'Forms Patchset Level :' ` expr $PatchLevel - 9 `
fi
echo 'For More Information '
echo 'Refer to Metalink Note:232313.1 for More Information'
3.Grant the execute permission to the script
chmod 777 fpslevel.sh
4.Execute the script.
./fpslevel.sh
Posted by Oracle Apps DBA Labs at 9:47 PM 0 comments
Check Database Performance by these queries
SELECT * FROM v$log;
SELECT COUNT(*) FROM v$process;
SELECT COUNT(*) FROM v$session WHERE status='ACTIVE'
SELECT COUNT(*) FROM dba_locks WHERE blocking_others='Blocking';
SELECT COUNT(*) FROM dba_objects WHERE status='INVALID' AND owner IN ('ABC','DEF') GROUP BY owner;
SELECT A.Tablespace_Name,TRUNC((SUM(A.Tots)/1024/1024),3) Tot_Size,
TRUNC(((SUM(A.Tots)/1024/1024)-(SUM(A.Sumb)/1024/1024)),3) Tot_Used
FROM (
SELECT Tablespace_Name,0 Tots,SUM(Bytes) Sumb,
MAX(Bytes) Largest,COUNT(*) Chunks
FROM Sys.Dba_Free_Space A
GROUP BY Tablespace_Name
UNION
SELECT Tablespace_Name,SUM(Bytes) Tots,0,0,0
FROM Sys.Dba_Data_Files
GROUP BY Tablespace_Name) A, V$instance B
GROUP BY A.Tablespace_Name
ORDER BY A.Tablespace_Name
SELECT (1-(SUM(DECODE(name,'physical reads', value, 0))/
(SUM(DECODE(name,'db block gets', value, 0)) +
SUM(DECODE(name,'consistent gets', value, 0))))) * 100 "Read Hit Ratio"
FROM v$sysstat
SELECT d.status "Status",
d.tablespace_name "Name",
d.CONTENTS "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %"
FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.CONTENTS "Type", d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %"
FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes
FROM v$temp_extent_pool GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY'
ORDER BY 7
Posted by Oracle Apps DBA Labs at 9:42 PM 0 comments
AutoConfig
What is Autoconfig?
Autoconfig is a tool for configuring application instance.
What are the programs and program name involved in Auto Config?
You can see perl,shell,cmd and java programs.
1.adconfig.pl- Its a main program which inturn call Java APIs for configuring appl instance.
args-appl context file location
2.adautocfg.sh/adautocfg.cmd-This program inturn calls adconfig.pl
args-apps password
3.adconfig.sh-This program again calls adconfig.pl
These three programs used for running Auto config.
4. adchgatname.pl - This program will validating the APPL_TOP name in context file .If the APPL_Top name is changed, then this utility will take care of storing the old APPL_TOP name in APPL_TOP/admin/ATName.txt.Adconfig.pl calls this program .
Posted by Oracle Apps DBA Labs at 9:38 PM 0 comments
Labels: AUTOCONFIG
Archiving Oracle Applications
Why Archive?
After an implementation of Oracle Application which has been around from some years, you would see a substantial growth in the database size.
As your Oracle Application continues to grow and upgrade the database also grows with it. A lot of data which resides in this database would no longer be needed to be updated and a significat amount of this data would rarely be used for any kind of Reporting. Nevertheless we cannot simply go ahead and purge or trash this data for obvious compliance and regulatory reasons. Another added advantage of archiving is that you can implement data masking in your non production environment while in the process of archiving, this reduces the risks from exposing your production data after subsequent clones.
There are many challenges associated with a large growing database like
* Increase in storage costs.
* Decrease in system responsiveness.
* Increase in times taken to complete cloning cycles.
* Increase in recovery times in cases of system failures.
What to archive ?
This is the most difficult when it came to implement an archive policy. Fortunately most of the archive solutions available in the market came with an out of box solution for Oracle Applications which made sure to preserve the referential integrity of the data. since data in an application is in its normalized form its important to ensure that the archived data is valid enough to be reported from when required. To ensure this a meta data repository is bundled along with the solution which defines the rules for archiving and the associated constraints.
Archiving Options
when it came to archiving options available with the ebusiness suite. these were quite a few of them available. Most of these solutions adopted a similar approach to archiving.
After going through the popular ones solutions available i can categorize them in the following groups.
* Proprietary File Type Based Solutions.
* Archive Database Based Solutions.
Proprietary File Type Based Solutions
Under these kinds of archiving solution the data is archived from Oracle Applications and stored in a flat file system which is generally in a proprietary format. The main and the biggest advantage of adopting such kind of the solution is that the need to maintain a large database is reduced. Also the archived data in the file system can be further compressed further to shrink the size. The major disadvantage of this approach is that you loose the capacity to do a seemless reporting from within your application. That is in order to be able to report from your archived data you would need to be depended on the proprietary reporting tool, rather than being able to do so from with oracle applications.
Archive Database Solutions
In the archive database based solutions, the archived data is stored in a separate database as opposed to a file system. This archive database could reside on a low cost second tire storage system. The main advantage in this structure is that you have the ability to a get to your archive data from within the application itself, in other words you do not need any third party tools to access your data.
One of the disadvantages of this approach is that, this would result in atleast one more database to maintain apart from the production system despite of the fact that this database would be on a lost cost second tier storage.
Limitations.
One of the main limitations that most of these archiving solutions had that, although most of them were able to archive the data from your application they failed to do a database reorg.
After the archiving cycle the database would need to be reorged manually to reclaim the space and see an increase in the system performance.
Also in the even of applications upgrade when the structure of your database tables changed the process of propagating the changes in the archives, though available was a complex process.
Posted by Oracle Apps DBA Labs at 9:33 PM 0 comments
AD Utilities
AD Utilities (Applications DBA Utilities) are set of Oracle supplied utilities that are used to administer Oracle Applications.
Some AD utilities are given below...
adadmin - Performs maintenance tasks for Oracle Applications.
adchkdig - Checks the integrity of Oracle Applications patches downloaded from OracleMetaLink.
adutconf.sql - Reports standard information about the installed configuration of Oracle Applications.
adctrl - Manages parallel workers in AD Administration and AutoPatch.
adident - Reports the version and translation level of an Oracle Applications file.
adncnv - Converts a file from one character set to another.
admrgpch - Merges multiple patches into a single merged patch.
adrelink.sh - Relinks Oracle Applications executable programs with the Oracle server product libraries.
adsplice - Adds off-cycle products.
adtimrpt.sql - Reports a summary of the timing for jobs run by parallel workers.
adpatch - Applies patches and other system updates.
admsi.pl - Generates customized installation instructions for a patch.
rapidwiz - Provides a wizard for entering parameters that are specific to a new installation or an upgrade of an Oracle Applications system.
Posted by Oracle Apps DBA Labs at 9:32 PM 0 comments
Change DOMAIN NAME on Oracle Apps (11.5.10.2)
I have changed the domain name ies.apps.com to ies.oracle.com as follows
Step 1
Enter in to “vi /etc/hosts” then change the host name as u want
e.g.: (ies.apps.com) to (ies.oracle.com)
Enter to “vi /etc/sysconfig/network” then change to the new host name
e.g.: (ies.apps.com) to (ies.oracle.com)
Then run $ service network restart
Then reboot the computer
NOTE: Ensure that backup must be taken of .xml file of both DB Tier and Apps Tier…
Step 2
Go to DB Tier…..
“$ vi /v01/oracle/visdb/appsutil/VIS_ies.xml”
Values of following parameters you have to change:
Change the new values to “ies.oracle.com” or as u want
1. global_database_name oa_var="s_global_database_name"
2. domain oa_var="s_domainname"
Step 3
Go to Apps Tier….
“$ vi /v01/oracle/visappl/admin/VIS_ies.xml”
Values of following parameters you have to change:
1. domain oa_var="s_dbdomain"
2. domain oa_var="s_admdomain"
3. domain oa_var="s_cpdomain"
4. domain oa_var="s_wfdomain"
5. domain oa_var="s_smtpdomainname"
6. domain oa_var="s_formsdomain"
7. chronosURL oa_var="s_chronosURL"
8. domain oa_var="s_metdomain"
9. domain oa_var="s_mwadomain"
10. discoinstancename oa_var="s_discoinstance"
11. disco_machine oa_var="s_disco_machine"
12. webentrydomain oa_var="s_webentrydomain"
13. proxybypassdomain oa_var="s_proxybypassdomain"
14. apps_portal_url oa_var="s_apps_portal_url"
15. sysadminmail oa_var="s_sysadmin_mail" osd="unix"
16. cookiedomain oa_var="s_cookie_domain"
17. login_page oa_var="s_login_page"
18. jdbc_url oa_var="s_apps_jdbc_connect_descriptor"
19. ohs_serveradmin oa_var="s_ohs_serveradmin"
20. domain oa_var="s_webcache_domain"
21. domain oa_var="s_domainname"
22. FORMS60_MAPPING oa_var="s_f60map"
Step 4
Then manually login to apps/apps@vis
Then manually you have to update the following table in database:
1. HOME_URL in ICX_PARAMETERS
Then login to /sys as sysdba
Then manually you have to update the following table in database:
2. GLOBAL_NAME in GLOBAL_NAME
Then exit from sqlplus….
Step 5
Then go to “/v01/oracle/visdb/9.2.0/network/admin”
Then change the all old host name (apps) to new host name (oracle) of the following scripts:
$ vi listner.ora
$ vi tnsname.ora
$ vi sqlnet.ora
Step 6
Run Auto Config on DB Tier…
“/v01/oracle/visdb/9.2.0/appsutil/scripts/VIS_ies/”
Run ./adautocfg.sh app/apps
Run Auto Config on Apps Tier…
“$ /v01/oracle/viscomm/admin/scripts/VIS_ies/”
Run $ ./adautocfg.sh app/apps
Posted by Oracle Apps DBA Labs at 9:24 PM 0 comments
Sunday, February 8, 2009
OPatch in Apps
In Apps we need to use Opatch and adpatch for patching oracle apps. Why we need to use two utility to patch oracle apps.opatch is used to patch all oracle homes (for R12..10.1.2,10.1.3 and 10.2.0.2 oracle homes). Apps patches we need to use to adpatch.
How to use Opatch?
Opatch is one of the easiest and safest for patching oracle apps because you can rollback opatch if you find any issues applying the patches
1. From the opatch read me make sure which oracle home you want to patch
2. Shutdown the instance related to particular oracle home.
3. Set the oracle home in the env .Set the opatch in the path.
4. Follow the readme whether opatch needs up gradation.
5. cd to the patch area and opatch apply
6. Proceed the post install steps
7. Run cpu_root.sh to give some permissions to the executables.
8. For rollback use opath rollback –id
9. “opatch lsinventory “ is used to list all the patches
10. “opatch lsinventory –details” is used to find the version belongs to particular oracle home.
$ ORACLE_HOME/OPatch/ (Unzip Patch number)
$ cd ..
$ ./opatch apply (Patch number)/
Then system will ask for respond……. Y | N….
Give Y
Posted by Oracle Apps DBA Labs at 11:53 PM 0 comments
Autoconfig in Oracle Apps 11i / R12 / 12i
In Oracle Applications 11i & R12 (12i) all the configuration parameters are captured in two context files:
Application context file called the .xml contains the configuration information for the application tier & is located in $APPl_TOP/admin for each node of application tier.
Database context file called the .xml contains the configuration information for the database tier & is located in $ORACLE_HOME/appsutil/.
Details stored in the context file contains host names,domain name , directory structure, port numbers used ……
The AutoConfig feature of Oracle application manager(OAM) is used to update & manage context files.
Updating Context file
Suppose we want to update the active port from to 80 , for this we will edit the application context file as follows:
Step 1 :Start all services
$ $COMMON_TOP/admin/scripts//adstrtal.sh APPS/
Step 2 :log on to OAM & click site map.
Step 3:Click AutoConfig from the list of System Configuration features in the Administration tab.
Step 4 : Click the Edit Parameter button for the application tier context file to access the Context File Parameters page & then click the system tab.
Step 5 : Click the Focus icon (blue triangle) for oa_web_server. This expands oa_web_server and shows the web server related parameters.
Step 6:Update the Web Port (s_active_webport) parameter from <> to 80.
Step 7 : Save it & answer the tab.Then logout of OAM
AutoConfig Script
The autoconfig script uses information from the context file to generate all applications configuration files & updates database profiles. It is located in
Application tier: $COMMON_TOP/admin/scripts/CONTEXT_NAME/adautoconfig.sh
Database tier: $ORACLE_HOME/appsutil/scripts/CONTEXT_NAME/adautoconfig.sh
Now we run the autoconfig script to reconfigure the oracle application system with the updated Applications context.
Step 1 : Stop all services
$ $COMMON_TOP/admin/scripts//adstpall.sh APPS/
Step 2:Run the autoconfig script, $adautocfg.sh & respond to the prompts.
Step 3:Check log files for any errors.log file for autoconfig is adconfig.log & is located in
/admin//log/MMDDhhmm/
Step 4: Restart all services :
$ $COMMON_TOP/admin/scripts//adstrtal.sh APPS/
Rolling back an autoconfig session
Each execution of AutoConfig creates a roll back script in case you want to revert back to the previous configuration settings. For this we perform the following steps:
Step 1 :Shut down all services:
$ cd $COMMON_TOP/admin/scripts/
$ adstpall.sh APPS/
Step 2:Restore AutoConfig session.
$ cd $APPL_TOP>/admin//out/MMDDhhmm
$ restore.sh
Step 3 : Restart all services:
$ $COMMON_TOP/admin/scripts//adstrtal.sh APPS/
Restoring the AutoConfig updated files and restarting the services allows you to log on to OAM.
Restoring the previous configuration
Now we run OAM AutoConfig to restore the Applications Context.
Step 1 :Log on to the Oracle Application Manager (OAM) & click site map.You will be able to log on but some services will be down.
Step 2: Click AutoConfig from the list of System Configuration features in the Administration tab.
Step 3 : Click the Show History button for the application tier context file to access the Context File History page.
Step 4 :Click the Show Differences from Current Configuration button to see the recently updated parameters & Select Restore Previous Value for the Web Port and Discoverer Port parameters.
Step 5 :Save & respond the tabs. Log out of OAM.
Step 6 :Shut down all services:
$ cd $COMMON_TOP/admin/scripts/
$ adstpall.sh APPS/
Step 7 :Run the AutoConfig script, adautocfg.sh & Respond to the prompts.
Step 8 : View the log file and check for any errors.
Step 9 : Restart all services:
$ $COMMON_TOP/admin/scripts//adstrtal.sh APPS/
Step 10 :Log on to the Oracle Application Manager (OAM)
If the status of some services appears to be down, click the Refresh icon to update the
status.
Related : Metalink Notes
218089.1 Autoconfig FAQ
165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i
387859.1 Using AutoConfig to Manage System Configurations in Oracle Applications Release 12
270519.1 Customizing an AutoConfig Environment
364927.1 How To Run Autoconfig On Database Tier (DB-Tier)
341322.1 How to change the hostname of an Applications Tier using AutoConfig
338003.1 How to change the hostname and/or port of the Database Tier using AutoConfig
315674.1 How To Verify if Autoconfig is Enabled on 11.5.x
391406.1 How to get a clean Autoconfig Environment
Posted by Oracle Apps DBA Labs at 11:37 PM 1 comments
Labels: AUTOCONFIG
Blocks , Extents, Segments in Oracle Database
I am going to cover basic concepts and Architecture of Oracle Database (9i, 10g, and 11g)
A. Data Blocks is smallest logical unit to store Oracle Data.
ii) One data block represent specific number o
f bytes on physical hard disk.
iii) Data Block in Oracle is also called as logical block
iv) Data Block size is usually multiple of operating system block size
v) You can have multiple block sizes with in single database (max. five)
vi) Block Size is specified by initialization parameter DB_BLOCK_SIZE
vii) Format of Data Block is
a) Header : contains generic information like block address and type of segment (index, data..)
b) Table Directory : contains information about table having rows in that block
c) Row Directory : contains information about actual row contained in that block
d) Free Space : available space in data block for additional row or update of row which require more space.
e) Row Data : contains table or index data.First three component of data block (Header, Table & Row directory) collectively known as Overhead
B. Extent is collection of contiguous data blocks.
ii) One or more extents make up a segment.
C. Segment is set of extents allocated for specific data structure (like table or index).
ii) Various kind of segments are table, index, cluster, rollback, temporary …
iii) Important views for segments are dba_segments, user_segments, all_segments
iv) In a Segment, first block of first extent contains segment header information
Things to note w.r.t. Segment, Extent & Datablocks
i) Segment and its associated extents are stored in one table space.
ii) Extents of a segment may not be contiguous on disk
iii) Segment can span multiple datafiles of a particular tablespace (Information on tablespace & datafiles coming soon) but extent can contain data from only one datafile.
Posted by Oracle Apps DBA Labs at 11:24 PM 0 comments
Labels: Oracle Core DBA
ADPATCH Options
You can execute adpatch by logging in as the applications OS user and sourcing the appropriate environment file.
# su - applmgr
$cd SAMAPPL
$. ./APPSORA.env
$adpatch
By default adpatch does not take any clause, but there are some clauses that you could use with adpatch
Running a patch in test mode
You can use the apply clause with adpatch to specify weather to run the patch in TEST mode or not, when you run the patch in test mode it does not do any changes but runs generates a log fiule with all the actions it would have performed.
$ adpatch apply=n|y
The default is apply=y
Pre-install Mode
You can also run a patch in pre install mode, this would be done normally during an upgrade or consolidated update. When a patch is applied in a preinstall mode the all the AD utilities are updated before the upgrade or update.
$ adpatch preinstall=y
The default is preinstall=n
Other Options with adpatch
You can use the options clause to specify some of the other options available with adpatch.
Autoconfig
You can use the options=noautoconfig top specify autopatch that you do not wish to run autoconfig as a part of the patch application. This can be useful when applying a large number of patches when they are not merged. By default autoconfig is run as a part of adpatch.
$ adpatch options=noautoconfig
Checkfile
The chekfile option of adpatch tells adpathc to check for already executed exec, SQL, and exectier commands.You can use options=nocheckfile skips this check, however this can cause performance overheds so should be used only when specified.
$ adpatch options=nocheckfile
Compile Database
By defaulty autopatch compiles the invalid objects after the patch application, in case you wish not to do so you can specify options=nocompiledb along with autopatch.
$ adpatch options=nocompiledb
Compile JSP
By defaulty autopatch compiles the java server pages (jsp) after the patch application, in case you wish not to do so you can specify options=nocompilejsp along with autopatch.
$ adpatch options=nocompilejsp
Copy Portion
If you wish adpatch not to execute the commands present in the copy driver portion of the patch you can use the options=nocopyportion.
$ adpatch options=nocopyportion
Database Portion
If you wish adpatch not to execute the commands present in the database driver portion of the patch you can use the options=nodatabaseportion.
$ adpatch options=nodatabaseportion
Generate Portion
If you wish adpatch not to execute the commands present in the generate driver portion of the patch you can use the options=nogenerateportion
$ adpatch options=nogenerateportion
Maintenance Mode
If you wish to apply a patch regardless of the system being in maintenance mode you can use options=hotpatch.
$adpatch options=hotpatch
Integrity Check
If you wish to check the integrity of the patch you can use the options=integrity. Since metalink patches are pre checked for their integrity it is generally not required to do an explicit check and the default value is nointegrity.
$ adpatch options=integrity
Maintain MRC
You can use the maintainmrc option to specify weather you wish adpatch to execute the Maintain MRC schema as a part of the patch application or not. By default maintain MRC is done for standard patches and is disbaled for tarnslation and documentation patches.
$ adpatch options=nomaintainmrc
Pre requisite Patch Check
If you wish adpatch not to check for pre requisite patches before application of the main patch you can use options=noprereq.By default pre requsite checking is enabled.
$ adpatch options=noprereq
Validate Schemas
If you wish adpatch to explicitly validate all the registed schems by making a connection you can use options=validate. By default this validation is not performed.
$ adpatch options=validate
Java Classes
If you wish adpatch not to copy new java classes from the patch you can use options=nojcopy.By default java classes are copied.
$ adpatch options=nojcopy
Force Copy
By default adpatch copies the files without check the version of the existing files already present on the system.If you do not wish the newer version of the file to be replaced by the older version contained in the patch use options=noforcecopy.
$ adpatch options=noforcecopy
Relinking
If you wish adpatch not do perform relinking you can use options=nolink.
$adpatch options=nolink
Generate Forms
If you wish adpatch not to generate the forms files you can specify options=nogenform.
$ adpatch options=nogenform
Generate Reports
If you wish adpatch not to generate the report files you can specify options=nogenrep.
$ adpatch options=nogenrep
You could specify multiple options at the command line using the , delimiter.
$ adpatch options=hotpatch,nojcopy
Posted by Oracle Apps DBA Labs at 11:12 PM 0 comments
10 ways to protect APPS Password
1. Stay current with our latest Security Best Practices
Regularly review the latest version of Best Practices for Securing Oracle E-Business Suite (Note 189367.1). This note is regularly updated and will give security advice covering many different aspects of Applications 11i. For Release 12, see Best Practices For Securing Oracle E-Business Suite Release 12 (Note 403537.1)
2. Regularly change your APPS password
This is an essential activity from a security perspective and needs to be part of your routine operating procedures. Same applies for other schema passwords and SYSADMIN user. As an aside, don't use predicable passwords, or have a system to create passwords, such as using "0ct0ber" for the password in October as this will make it easier to guess
3. Always change passwords as part of a clone process from PROD
It is recomended to change ALL schema passwords and ALL eBiz user passwords in a cloned instance. You can use Removing Credentials from a Cloned EBS Production Database (Note 419475.1) to achieve this. Similarly, you don't want to have any relation in the passwords used for PROD compared to any other instances. Data masking and obfuscation is a large topic in its own right, but is also something you may need to consider doing for the cloned instance to protect sensitive data generally. With Release 12, EM plugin provides some data scrambling facilities
4. Perform data masking on any files sent to outside parties from the PROD system
When you need to send any log files or configuration files, ensure that you scan for any sensitive data before packing the files to be sent. In this article we are concerned about the APPS password, but this applies equally well for other data as well. For example, a crude mechanism would be to use "ed" or "sed" on all files to globally change any occurrences of the APPS password before creating a tar archive to email or upload. You may be uploading files to Oracle Support, or just emailing them within your Organization. Whenever the files are going to someone who cannot access them directly you should always check the files before sending.
5. Create separate schemas with minimal access required for direct database access
If anyone requires direct access to the E-Business Suite database, ensure that you create a new unique schema with the specific permissions required for them to perform their job role. Except for a very few Apps DBAs, there should be no reason that anyone else needs the APPS user password. Sometimes pressures of work make it easier to just give someone APPS access, but this should be resisted and the time taken to provide only the minimum access absolutely required. Every person should also have their own unique login (but this is digressing into a separate area that I'll address in a later article). When considering permissions to allocate, don't be tempted to give read only access to everything, as being able to read sensitive information may be just as damaging as being able to change it.
6. Protect Apps 11i middle tier file system files
These days, there is little need to give anyone UNIX-level access to the servers, but it is still important to ensure the "applmgr" operating system user password is well protected. Also consider whether any of your own startup scripts or monitoring scripts have the APPS password hard coded in them, and protect these scripts with chmod 700 permissions, or remove them if no longer needed
7. Ensure no processes are running with APPS username/password in command line
Generally the APPS password is not listed in "ps" output, but there may be some manual scripts or other processes intermittently running with the APPS password in clear text or trivially encoded. Ensure these scripts are changed to hide the APPS password. In addition, ensure operating system access is restricted to only those who really need it
8. Protect OID access
If you have integrated the E-Business Suite with Oracle Application Server 10g, Single Sign-On, and Oracle Internet Directory, then the Apps user password is stored in the OID database, as it is required for Provisioning to function. The OID administrator and anyone with ldapsearch rights in the Provisioning Profiles will be able to extract the APPS password from OID. This in turn implies the "AppsDN" OID password should be protected in the same way as the APPS password itself. For assistance in security hardening OID, refer to the Oracle Internet Directory Administrator's Guide 10g (10.1.4.0.1) - Part III Directory Security
9. Encrypt SQLNET traffic from Middle Tier to RDBMS
In a previous article, Steven highlighted that ANO is certified with the E-Business Suite. Use encryption to protect the APPS password from network sniffers tracing SQLNET connection packets and deciphering the APPS password on the wire.
10. Allow only specific IP addresses to access RDBMS via SQLNET
Slightly off topic, but if someone has acquired the APPS password they still have to be able to gain access to a tool that can use it. Restricting the IP addresses that can access your Apps database will help minimise this risk. If you are still using "fat" clients (Discoverer or ADI for example) then you will have to weigh up the risks against the administrative overhead. Oracle recommends upgrading to server-based equivalent tools or shared desktop technologies such as Citrix so desktop clients no longer need direct access. This topic is discussed further in E-Business Suite Recommended Set Up for Client/Server Products (Note 277535.1)
Posted by Oracle Apps DBA Labs at 11:09 PM 0 comments
Newer Posts Older Posts Home
Subscribe to: Posts (Atom)
About Me
My Photo
Chandrasekhar Das
View my complete profile
World Wide
free counters
Blog Archive
* ▼ 2009 (19)
o ▼ February (13)
+ Concurrent Managers
+ Check Versions of Oracle Applications Components
+ Check Developer Forms Patch Set Level
+ Check Database Performance by these queries
+ AutoConfig
+ Archiving Oracle Applications
+ AD Utilities
+ Change DOMAIN NAME on Oracle Apps (11.5.10.2)
+ OPatch in Apps
+ Autoconfig in Oracle Apps 11i / R12 / 12i
+ Blocks , Extents, Segments in Oracle Database
+ ADPATCH Options
+ 10 ways to protect APPS Password
o ► January (6)
+ RMAN ---Backup and recovery Methods being followed...
+ MULTINODE INSTALLATION of Oracle 11i Apps
+ Autoconfig FAQ
+ Autoconfig Basics
+ Comparison between Oracle Applications and SAP
+ Concurrent Manager in Oracle Apps
Labels
* AUTOCONFIG (4)
* Backup/Recovery (RMAN) (1)
* Concurrent Manager (1)
* Oracle Core DBA (1)
Counter
StatCounter - Free Web Tracker and Counter
Followers
Concurrent Manager Faqs
vd11 13703 13660 0 May 11 ? 0:01 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS/94A491A1000000000000000000
n1070161 24936 24927 0 Apr 29 ? 0:05 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24938 24927 0 Apr 29 ? 0:06 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24927 24922 0 Apr 29 ? 2:03 FNDLIBR FND CPMGR FNDCPMBR sysmgr
="" sleep=60 pmon=20 diag=N logfile=/u16/app
The last process, #24927, shows 'FNDLIBR FND CPMGR', this one is the
Internal Manager (ICM). Notice that it gives some of the parameters it
was started with, the other processes showing 'Concurrent_Processor'
are Standard manager processes. Notice that the ICM process is the
parent process of the Standard managers. (processes 24936 and 24938)
Other managers will have the name of the executable, like ARLIBR or
INVLIBR:
$ ps -ef grep ARLIBR
vd11 13683 13660 0 May 11 ? 0:20 ARLIBR APPS/82A2A4940000000000000
000000000000000000000000000000000000000 AR ART
The Conflict Resolution manager will look like:
$ ps -ef grep FNDCRM
n1070161 24941 24927 0 Apr 29 ? 1:17 FNDCRM APPS_APPDEMO/84BFBEB900000
0000000000000000000000000000000000000000000000
What is the syntax for controlling the concurrent manager using startmgr and
concsub in NT?
On NT, the concurrent manager is run as an NT service, you start and
stop the managers using the Services control panel.
See the Applications Installation manual for NT, Appendix A for
details. See pg. 5-9 of this manual for instructions on creating the
concurrent manager service.
Why am I seeing pinging entries like this in the ICM logfile?
PING (0.0.0.0): 56 data bytes
64 bytes from 192.75.91.2: icmp_seq=0 ttl=255 time=0.705 ms
64 bytes from 192.75.91.2: icmp_seq=1 ttl=255 time=1.120 ms
Process monitor session ended : 29-FEB-2000 10:38:43
64 bytes from 192.75.91.2: icmp_seq=2 ttl=255 time=0.985 ms
64 bytes from 192.75.91.2: icmp_seq=3 ttl=255 time=1.006 ms
Pinging other machines is used in Distributed Concurrent Processing.
This means you have DCP turned on, using the environment variable
APPLDCP. Set APPLDCP to OFF and restart the managers.
I hit the Restart button to start the Standard manager, but it still did not
start?
Telling a manager to restart just sets the status to Restart. The ICM
will start it the next process monitor session or the next time the
ICM starts. Use Activate to start a manager immediately.
When a manager is deactivated manually, the ICM will not restart
it, you will need to set it to Restart, or activate it manually.
How many rows are in FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES
tables?
When tables reach above 3000-4000 rows, the performance begins to
diminish. You may want to run Purge Concurrent Request on a regular basis,
dependant on the amount of requests being run.
The Purge Concurrent Requests job can be used to purge:
Requests, Mgr logs, and All requests depending on what is chosen.
Use the following options: Enter = All, Mode = AGE, Mode Value = 15
The std.mgr log continuously grows where it may good to
archive it regularly.
Any processes pending in Internal or Conflict Resolution Manager?
Best course of action before starting the Concurrent Managers is to cancel
any "Deactivate" or "Verify" jobs pending in the Internal Manager and place
any other pending jobs on hold.
How do I turn on transaction manager diagnostics?
Set the profile option 'Concurrent:Debug Flags' to 'TCTM1' at the site
level. This will cause transactions to make debug entries in the
FND_CONCURRENT_DEBUG_INFO table. Truncate this table before running a
tranasction, then select the entries from the table.
Starting the managers with diag=Y will also produce more information
in the transaction manager logfile.
How do transaction managers work?
Briefly:
(See the server documentation for details on the DBMS_PIPE package)
1) A tranasction manager is started on the concurrent processing
server, and periodically reads the pipe for incoming transactions.
2) A client program (usually a form) calls the
FND_TRANSACTION.SYNCHRONOUS function.
3) This function writes a message into the pipe containing the program
to be run and its parameters.
4) FND_TRANSACTION.SYNCHRONOUS begins reading a return pipe for the
return status.
5) The manager sees the message in the pipe, retrieves the program id
and parameters.
6) The manager runs the program with the specified parameters. The
program will be of type 'Immediate', so there will not be a
separate concurrent request run.
7) The program completes, and the manager packs its return status into
the return pipe.
8) FND_TRANSACTION.SYNCHRONOUS reads the return value and passes it
back to its caller.
Note that these events take place essentially simultaneously on the
client and server. This is a synchronous transaction because the
client waits for the server to return, or times out waiting for it.
Problem....
When you try to submit a request like Active users or
Active responsibilities, request gets submitted.
When we view the help requests, you find that it is
inactive / nomanager.
Within 12 to 15 seconds, you refresh-it gets completed.
Initially, you could find only inactive and we look at
the diagnostic- the concurrent manager assigned is not
picking up.
There is no specialization rules in any managers except
the include program this source.
Solution....
Most often when this occurs where a request goes
"inactive/no manager" and is then processed a short time
later, the solution is to either increase the cache size
for your Standard manger, or increase the actual number of
Standard manager processes.
Cache Size is set on the CONCURRENT/MANAGER/DEFINE form. Basically,
this regulates how many requests a manager will pick up for each
sleep cycle.
How do I process more concurrent requests concurrently?
The Concurrent Manager parameters, (Query the concurrent manager by
Login as Sysadmin, navigate -> Concurrent -> Manager -> Define and Query for
the relevant concurrent manager), should be modified to handle more
concurrent requests concurrently, this can be done in two steps:
(i) Increase the Number of Target processes for the manager
(ii) Change the cache size of the concurrent manager as this determines
how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.
MANAGE OLOGIN="APPS/94A491A1000000000000000000
n1070161 24936 24927 0 Apr 29 ? 0:05 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24938 24927 0 Apr 29 ? 0:06 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24927 24922 0 Apr 29 ? 2:03 FNDLIBR FND CPMGR FNDCPMBR sysmgr
="" sleep=60 pmon=20 diag=N logfile=/u16/app
The last process, #24927, shows 'FNDLIBR FND CPMGR', this one is the
Internal Manager (ICM). Notice that it gives some of the parameters it
was started with, the other processes showing 'Concurrent_Processor'
are Standard manager processes. Notice that the ICM process is the
parent process of the Standard managers. (processes 24936 and 24938)
Other managers will have the name of the executable, like ARLIBR or
INVLIBR:
$ ps -ef grep ARLIBR
vd11 13683 13660 0 May 11 ? 0:20 ARLIBR APPS/82A2A4940000000000000
000000000000000000000000000000000000000 AR ART
The Conflict Resolution manager will look like:
$ ps -ef grep FNDCRM
n1070161 24941 24927 0 Apr 29 ? 1:17 FNDCRM APPS_APPDEMO/84BFBEB900000
0000000000000000000000000000000000000000000000
What is the syntax for controlling the concurrent manager using startmgr and
concsub in NT?
On NT, the concurrent manager is run as an NT service, you start and
stop the managers using the Services control panel.
See the Applications Installation manual for NT, Appendix A for
details. See pg. 5-9 of this manual for instructions on creating the
concurrent manager service.
Why am I seeing pinging entries like this in the ICM logfile?
PING (0.0.0.0): 56 data bytes
64 bytes from 192.75.91.2: icmp_seq=0 ttl=255 time=0.705 ms
64 bytes from 192.75.91.2: icmp_seq=1 ttl=255 time=1.120 ms
Process monitor session ended : 29-FEB-2000 10:38:43
64 bytes from 192.75.91.2: icmp_seq=2 ttl=255 time=0.985 ms
64 bytes from 192.75.91.2: icmp_seq=3 ttl=255 time=1.006 ms
Pinging other machines is used in Distributed Concurrent Processing.
This means you have DCP turned on, using the environment variable
APPLDCP. Set APPLDCP to OFF and restart the managers.
I hit the Restart button to start the Standard manager, but it still did not
start?
Telling a manager to restart just sets the status to Restart. The ICM
will start it the next process monitor session or the next time the
ICM starts. Use Activate to start a manager immediately.
When a manager is deactivated manually, the ICM will not restart
it, you will need to set it to Restart, or activate it manually.
How many rows are in FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES
tables?
When tables reach above 3000-4000 rows, the performance begins to
diminish. You may want to run Purge Concurrent Request on a regular basis,
dependant on the amount of requests being run.
The Purge Concurrent Requests job can be used to purge:
Requests, Mgr logs, and All requests depending on what is chosen.
Use the following options: Enter = All, Mode = AGE, Mode Value = 15
The std.mgr log continuously grows where it may good to
archive it regularly.
Any processes pending in Internal or Conflict Resolution Manager?
Best course of action before starting the Concurrent Managers is to cancel
any "Deactivate" or "Verify" jobs pending in the Internal Manager and place
any other pending jobs on hold.
How do I turn on transaction manager diagnostics?
Set the profile option 'Concurrent:Debug Flags' to 'TCTM1' at the site
level. This will cause transactions to make debug entries in the
FND_CONCURRENT_DEBUG_INFO table. Truncate this table before running a
tranasction, then select the entries from the table.
Starting the managers with diag=Y will also produce more information
in the transaction manager logfile.
How do transaction managers work?
Briefly:
(See the server documentation for details on the DBMS_PIPE package)
1) A tranasction manager is started on the concurrent processing
server, and periodically reads the pipe for incoming transactions.
2) A client program (usually a form) calls the
FND_TRANSACTION.SYNCHRONOUS function.
3) This function writes a message into the pipe containing the program
to be run and its parameters.
4) FND_TRANSACTION.SYNCHRONOUS begins reading a return pipe for the
return status.
5) The manager sees the message in the pipe, retrieves the program id
and parameters.
6) The manager runs the program with the specified parameters. The
program will be of type 'Immediate', so there will not be a
separate concurrent request run.
7) The program completes, and the manager packs its return status into
the return pipe.
8) FND_TRANSACTION.SYNCHRONOUS reads the return value and passes it
back to its caller.
Note that these events take place essentially simultaneously on the
client and server. This is a synchronous transaction because the
client waits for the server to return, or times out waiting for it.
Problem....
When you try to submit a request like Active users or
Active responsibilities, request gets submitted.
When we view the help requests, you find that it is
inactive / nomanager.
Within 12 to 15 seconds, you refresh-it gets completed.
Initially, you could find only inactive and we look at
the diagnostic- the concurrent manager assigned is not
picking up.
There is no specialization rules in any managers except
the include program this source.
Solution....
Most often when this occurs where a request goes
"inactive/no manager" and is then processed a short time
later, the solution is to either increase the cache size
for your Standard manger, or increase the actual number of
Standard manager processes.
Cache Size is set on the CONCURRENT/MANAGER/DEFINE form. Basically,
this regulates how many requests a manager will pick up for each
sleep cycle.
How do I process more concurrent requests concurrently?
The Concurrent Manager parameters, (Query the concurrent manager by
Login as Sysadmin, navigate -> Concurrent -> Manager -> Define and Query for
the relevant concurrent manager), should be modified to handle more
concurrent requests concurrently, this can be done in two steps:
(i) Increase the Number of Target processes for the manager
(ii) Change the cache size of the concurrent manager as this determines
how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.
Concurrent Manager
Q: Which Concurrent Managers (CCMs) are supported by the Sysadmin team?
* Internal Concurrent Manager (ICM)
* Standard Manager
* Conflict Resolution Manager (CRM)
The setup of product-specific managers related issues should
be directed to the associated product group, i.e. the Inventory manager
Q: Where do the concurrent request output and log files reside?
A: $APPLCSF/out or $Product_Top/out for output files
$APPLCSF/log or $Product_Top/log for log files
Q: Where are temporary files located?
A: Temporary files reside in:
$APPLPTMP$APPLTMP
$REPORTS25_TMP (Release 11.0)
$REPORTS60_TMP (Release 11.5)
Q: How to change PMON method to LOCK.
To check the process monitor (PMON) method status:
Connect as database user “APPS”.
SELECT profile_option_value
FROM fnd_profile_option_values
WHERE level_id = 10001
AND level_value = 0
AND application_id = 0
AND profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = ‘CONC_PMON_METHOD’);
This should return one row with a value of ‘LOCK’
If the value is ‘RDBMS’ or ‘OS’ run the following script:
$FND_TOP/sql/AFIMPMON.SQL – this will set the PMON method to LOCK instead of RDBMS.
Bounce the database. If running on NT, restart the NT Server completely.
Within 11.5 Applications, the only PMON method is LOCK.
Q: How to start and shutdown the CCM on Unix as APPS
A: To Start:
STARTMGR sysmgr=APPS/passwd
To Shutdown:
concsub apps/ sysadmin ‘System Administrator’ SYSADMIN CONCURRENT FND ABORT
11.5 => 11.5.4
adcmctl.sh apps/passwd start/stop prod
11.5.5 =>
adcmctl.sh start/stop apps/passwd prod
Q: How to terminate a concurrent request that cannot be cancelled?
A: Identify the request number to terminate. Shut down the concurrent managers and issue the following sql command as applsys:
update fnd_concurrent_requests
set status_code = ‘E’, phase_code = ‘C’
where Request_id =; (reqnum = request number)
Q: A Manager is not activating.
A: Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each. If the count is greater, change to one only. This can be done from sqlplus as follows:
As user apps:
select *
from SYS.DUAL;
Any extra rows should be deleted.
delete rownum
from SYS.DUAL;
(rownum = the row number to delete)
Any extra rows for apps.fnd_dual must be removed by performing the following SQL command:
delete from fnd_dual
where rownum < (select max(rownum) from fnd_dual);
Bounce the Concurrent Managers.
Q: Internal Concurrent Manager.
A: If the ICM should go down, requests will continue to run normally, except for ‘queue control’ requests.
If the ICM should go down, you can restart it with ‘startmgr’. There is no need to shut down the other managers first.
Q: How to purge requests that are in Pending status.
A: The Purge Concurrent Requests program will only purge requests that are in Completed status. Set the status of the Pending requests to Completed
before purging them. The Internal Manager Must be DOWN!
Using sqlplus as APPLSYS perform the following:
UPDATE fnd_concurrent_requests
SET phase_code = ‘C’
WHERE phase_code = ‘P’.
* Internal Concurrent Manager (ICM)
* Standard Manager
* Conflict Resolution Manager (CRM)
The setup of product-specific managers related issues should
be directed to the associated product group, i.e. the Inventory manager
Q: Where do the concurrent request output and log files reside?
A: $APPLCSF/out or $Product_Top/out for output files
$APPLCSF/log or $Product_Top/log for log files
Q: Where are temporary files located?
A: Temporary files reside in:
$APPLPTMP$APPLTMP
$REPORTS25_TMP (Release 11.0)
$REPORTS60_TMP (Release 11.5)
Q: How to change PMON method to LOCK.
To check the process monitor (PMON) method status:
Connect as database user “APPS”.
SELECT profile_option_value
FROM fnd_profile_option_values
WHERE level_id = 10001
AND level_value = 0
AND application_id = 0
AND profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = ‘CONC_PMON_METHOD’);
This should return one row with a value of ‘LOCK’
If the value is ‘RDBMS’ or ‘OS’ run the following script:
$FND_TOP/sql/AFIMPMON.SQL – this will set the PMON method to LOCK instead of RDBMS.
Bounce the database. If running on NT, restart the NT Server completely.
Within 11.5 Applications, the only PMON method is LOCK.
Q: How to start and shutdown the CCM on Unix as APPS
A: To Start:
STARTMGR sysmgr=APPS/passwd
To Shutdown:
concsub apps/
11.5 => 11.5.4
adcmctl.sh apps/passwd start/stop prod
11.5.5 =>
adcmctl.sh start/stop apps/passwd prod
Q: How to terminate a concurrent request that cannot be cancelled?
A: Identify the request number to terminate. Shut down the concurrent managers and issue the following sql command as applsys:
update fnd_concurrent_requests
set status_code = ‘E’, phase_code = ‘C’
where Request_id =
Q: A Manager is not activating.
A: Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each. If the count is greater, change to one only. This can be done from sqlplus as follows:
As user apps:
select *
from SYS.DUAL;
Any extra rows should be deleted.
delete rownum
from SYS.DUAL;
(rownum = the row number to delete)
Any extra rows for apps.fnd_dual must be removed by performing the following SQL command:
delete from fnd_dual
where rownum < (select max(rownum) from fnd_dual);
Bounce the Concurrent Managers.
Q: Internal Concurrent Manager.
A: If the ICM should go down, requests will continue to run normally, except for ‘queue control’ requests.
If the ICM should go down, you can restart it with ‘startmgr’. There is no need to shut down the other managers first.
Q: How to purge requests that are in Pending status.
A: The Purge Concurrent Requests program will only purge requests that are in Completed status. Set the status of the Pending requests to Completed
before purging them. The Internal Manager Must be DOWN!
Using sqlplus as APPLSYS perform the following:
UPDATE fnd_concurrent_requests
SET phase_code = ‘C’
WHERE phase_code = ‘P’.
Oracle Apps FND and Concurrent Manager Key Tables
*
Oracle Apps FND and Concurrent Manager Key Tables
Module: Concurrent Manager key Tables
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE
Module: FND key Tables
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_PROFILE_OPTION_VALUES
FND_APPLICATIONS
Module: FND key Tables
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS
Get your free Complementary Copy of Oracle Magazine
You can also browse the Free Magazines and E-Books section to see the complete list of free magazines, e-books and Whitepapers.
This article was originally published in forum thread: Oracle Apps FND and Concurrent Manager Key Tables started by Sadik View original post
Categories:
1. Oracle Apps Technical
Oracle Apps FND and Concurrent Manager Key Tables
Module: Concurrent Manager key Tables
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE
Module: FND key Tables
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_PROFILE_OPTION_VALUES
FND_APPLICATIONS
Module: FND key Tables
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS
Get your free Complementary Copy of Oracle Magazine
You can also browse the Free Magazines and E-Books section to see the complete list of free magazines, e-books and Whitepapers.
This article was originally published in forum thread: Oracle Apps FND and Concurrent Manager Key Tables started by Sadik View original post
Categories:
1. Oracle Apps Technical
Thursday, June 3, 2010
Using Discoverer 10g (9.0.4) with Oracle Applications 11i
Using Discoverer 10g (9.0.4) with Oracle Applications 11i
April 2005
Availability
Oracle E-Business Suite Release 11i is certified for integration with Oracle Discoverer 10g (9.0.4). Discoverer 9.0.4 must be installed in a separate Oracle Application Server 10g ORACLE_HOME on an existing application tier server node or on a standalone server, accessing an Oracle E-Business Suite Release 11i database.
Althought this Metalink Note will continue to be published for reference purposes, Oracle strongly recommends that customers interested in using Discoverer with the E-Business Suite use the latest certified version: Discoverer 10.1.2.. See MetaLink Note 313418.1 titled, Using Discoverer 10.1.2 with Oracle E-Business Suite 11i.
Usage of additional Oracle Application Server 10g components Oracle Single Sign-On, Oracle Internet Directory and Oracle Portal for integration with Oracle E-Business Suite Release 11i is documented in MetaLink Note 233436.1 titled, Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i.
________________________________________
This document contains information for using OracleAS Discoverer 10g (9.0.4) from Oracle Application Server 10g with Oracle Applications 11i. You should read and understand all content described here before you begin using this feature. The most current version of these notes is 257798.1 on OracleMetaLink. There is a change log at the end of this document.
• Section 1: Overview
• Section 2: Supported Architectures
• Section 3: Release Versions
• Section 4: Preinstallation Tasks
• Section 5: Installation Tasks
• Section 6: Postinstallation Tasks
• Section 7: Available Documentation
• Section 8: Conventions and Important Directory Locations
• Section 9: Installation of Additional Discoverer End User Layer patches
• Section 10: Known Issues and Limitations
________________________________________
Section 1: Overview
OracleAS Discoverer 10g, a key component of Oracle Application Server 10g, is an integrated business intelligence solution supporting intuitive ad hoc query, reporting, analysis, and web publishing. Business users at all levels of the organization can use Discoverer 10g to gain immediate access to information from data marts, data warehouses, and online transaction processing (OLTP) systems. Discoverer 10g 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.
Discoverer 10g is tightly integrated with Oracle E-Business Suite Release 11i. Release 11i users can use Discoverer to analyze data from selected business areas in Financials, Operations, Human Resources, Purchasing, Process Manufacturing, Activity Based Management, and more.
This document describes how to install Discoverer 10g into an existing Oracle E-Business Suite Release 11i environment. Installation of Discoverer 10g into an Oracle E-Business Suite Release 11i environment consists of:
• Installation of Discoverer 10g with Oracle Application Server 10g on a standalone application tier server node
• Creation of the Discoverer 10g End User Layer (EUL) on a database tier server node
• Import of the Oracle E-Business Suite Release 11i Discoverer content into the Discoverer 10g End User Layer
• (Optional) Installation of Discoverer Administration Edition on a Windows-based PC that can be used to customize Discoverer
For current Discoverer users, it also describes how to upgrade an existing Discoverer End User Layer from Discoverer 3i or 4i to Discoverer 10g.
The procedures in this document have significant effects on Oracle E-Business Suite Release 11i environments and should be executed only by skilled Oracle E-Business Suite database or systems administrators. Users are strongly advised to make backups of their environments before executing any of the procedures noted, and to test their environments before executing these procedures in production environments. Users must log off the system while these changes are being applied.
Availability of Translations
Discoverer content for Oracle E-Business Suite Release 11i includes workbooks and the End User Layer for both regular Applications modules as well as Business Intelligence System (BIS) products such as Financials Intelligence and Purchasing Intelligence. This content is available only in US English, and there are no translations planned for other languages.
________________________________________
Section 2: Supported Architectures
Discoverer 10g must be installed in a separate ORACLE_HOME on an existing application tier server node or on a stand-alone server, accessing an Oracle E-Business Suite Release 11i database.
________________________________________
Section 3: Release Versions
The following components must be used on the stand-alone instance:
Component Name Version
Oracle Application Server Enterprise Edition 10g
The following components must be used on the Oracle E-Business Suite Release 11i instance:
Component Name Version
Oracle E-Business Suite Release 11i 11.5.8 to 11.5.10
________________________________________
Section 4: Preinstallation Tasks
1. Read Discoverer 10g Configuration Guide
Chapter 1 of the Oracle Application Server Discoverer Configuration Guide 10g (9.0.4) explains how to run, maintain and support OracleAS Discoverer. At minimum, review this chapter before proceeding further.
2. Preserve Discoverer 3i Customizations (optional)
Skip this step if you are upgrading an existing Discoverer 4i End User Layer or if you are installing a Discoverer End User Layer for the first time.
If you are upgrading from Discoverer 3i to Discoverer 10g, you may wish to preserve your existing custom workbooks and business areas. Custom business areas built in Discoverer 3.1 may be migrated to the new Discoverer 10g End User Layer if:
They do not reference any Oracle delivered content
They use only Display Names which do not conflict with the Oracle delivered content, for example, by using a custom prefix
Migration of Discoverer 3i content to Discoverer 10g is a two-step process:
2.1 Migrate Discoverer 3i End User Layer customizations to Discoverer 4i
Refer to Note 139516.1 on OracleMetaLink for migration of Discoverer 3i customizations to Discoverer 4i.
2.2 Migrate Discoverer 4i End User Layer to Discoverer 10g
Continue with migration of your existing Discoverer 4i End User Layer to Discoverer 10g as detailed below.
________________________________________
Section 5: Installation Tasks
This section outlines steps required to install Oracle Application Server 10g, Discoverer 10g, and the Discoverer 10g End User Layer. See Section 8: Conventions and Important Directory Locations for important information about entering commands listed in this section.
1. Install Oracle Application Server 10g Enterprise Edition
Oracle Application Server 10g Enterprise Edition includes Discoverer Server 10g, Discoverer Plus 10g, and Discoverer Viewer 10g. You must install OracleAS 10g Infrastructure and OracleAS 10g Business Intelligence and Forms middle tier in separate ORACLE_HOMES on an existing application tier server node, or on a standalone server accessing an Oracle E-Business Suite Release 11i database.
You must install Oracle10g Application Server Enterprise Edition by following the instructions in the Oracle Application Server 10g Installation Guide for your operating system platform. In particular for details on the OracleAS 10g topology and installation sequence please refer to section 11.3 in the Oracle Application Server 10g Installation Guide.
Note
If you already have an existing integrated AS Discoverer 10g (9.0.4) instance you can upgrade this instance to the latest certified patch level following Step 2 below.
2. Apply the latest certified Oracle Application Server 10g Discoverer patches
2.1 Apply Oracle Application Server 10g Patchset 9.0.4.1
Download patch 3784229 ORACLE AS 10G (9.0.4) PATCH SET (9.0.4.1.0) for your appropriate Discoverer Server node platform from Oracle Metalink.
Follow the installation instructions provided in the patch README to install the patch on your Discoverer Server node.
2.2 Apply MLR Patch on top of 9.0.4.1
Download the appropriate platform-specific one-off patch for your Discoverer Server node from Oracle Metalink.
Operating System Platform
Sun Solaris (SPARC) Microsoft
Windows HP HP/UX 11.0 IBM AIX Linux
3700386
3700386
3952149
4034160
3891698
Follow the installation instructions provided in the patch README to install the patch on your Discoverer Server node.
3. Install Internet Developer Suite 10g (Optional)
3.1. Install Internet Developer Suite 10g on a Windows PC
If you wish to use Discoverer Administration Edition to customize your Discoverer End User Layer, install Internet Developer Suite 10g containing Discoverer 10g Administration Edition on a Windows PC.
3.2. Apply the latest certified Discoverer Administration Edition Patchset
Download patch 3628736 DEVELOPER SUITE 10G (9.0.4) PATCH SET 1 (9.0.4.1.0) from Oracle Metalink and follow the installation instructions provided in the patch README to install the patch on your Discoverer Administration Edition Server.
3.3. Set Windows Registry Settings
If you are using Discoverer Administration Edition on Windows to refresh your End User Layer, please follow the steps below to set necessary registry settings.
Attention
This step contains information about modifying the Microsoft Windows registry. Before you modify the Windows registry, back it up and make sure that you understand how to restore it if a problem occurs. For information about how to back up, restore, and edit the registry, see the following article number in the Microsoft Knowledge Base :
Q256986 Description of the Microsoft Windows Registry
On the Windows PC running Discoverer 10g Administration Edition:
3.3.1 Back up your current Microsoft Windows Registry
3.3.2 Open the Windows Registry editor and create or update the following registry keys:
Registry Key DataType Value
HKEY_CURRENT_USER\Software\Oracle\Discoverer 9\
Database\EnableTriggers REG_DWORD 1
HKEY_CURRENT_USER\Software\Oracle\Discoverer 9\
Database\DefaultPreserveDisplayPropertyForRefresh REG_DWORD 1
4. Specify Connect Dialog for Oracle Applications Users
If you use the Discoverer 10g Connection Management pages for direct login using Oracle Applications User/Password authentication, you must set up the Connect dialog to display the Applications User checkbox.
4.1. Open the file/discoverer/util/pref.txt in a text editor
4.2. Set the DefaultUserTypeIsApps preference to "1"
4.3. Set the ShowUserTypeChoice preference to "1"
4.4. Save the updated pref.txt file.
4.5. On UNIX, run the script/discoverer/util/applypreferences.sh
4.6. Stop and restart the OracleDiscoverer10g service to refresh the environment with the updated preferences
For details about other options that may be set for direct login, please refer to Chapter 13 of the Oracle Application Server Discoverer Configuration Guide 10g (9.0.4)
Note
Users may enter their user ID and Applications responsibility in the Connect dialog using the format:
UserID:
If you append a colon : immediately after the user in the Discoverer Connect dialog without specifying a responsibility after the colon, Discoverer will always try to establish a connection as an Oracle Applications user and pop up a list of valid Applications responsibilities for this Applications user.
5. Migrate your existing Discoverer 4i End User Layers to Discoverer 10g.
If you do not have an existing Discoverer 4i End User Layer, skip this step and proceed with Step 6 .
Upgrade your existing Discoverer 4i End User Layer to Discoverer 10g by using the following command on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 .
$ORACLE_HOME/bin/eulapi -CONNECT/@ -AUTO_UPGRADE
The upgrade process creates a new Discoverer 10g End User Layer based the existing Discoverer 4i End User Layer. The existing Discoverer 4i End User Layer remains unchanged. A new schema containing the Discoverer 10g End User Layer will be created under the same Database User. After migration, the Database User contains both functional 4i and 10g euls. After verification of successful upgrade, you should drop the 4i eul.
6. Create new Discoverer 10g End User Layer
You must create a new Discoverer 10g End User Layer if you do not have an existing Discoverer 4i End User Layer.
We recommend that you create a new tablespace to store database objects for the Discoverer 10g End User Layer in your Oracle Applications database.
6.1. Execute this step on your database tier with your environment pointing to your database ORACLE_HOME. To set the environment correctly, source the file:
[DB_ORACLE_HOME]/.env
6.2. 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:
% sqlplus /NOLOG
SQL> connect sys/ as sysdba
SQL> create tablespace DISCOVERER datafile \
'[DB_ORACLE_HOME]/dbf/discoverer01.dbf' size 200M reuse \
extent management local uniform size 128K;
SQL> /
Statement Processed
6.3. Exit SQL*Plus:
SQL> quit
6.4. Create the new Discoverer 10g End User Layer by using the following command, executed on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 .
$ORACLE_HOME/bin/eulapi
-CREATE_EUL
-APPS_MODE
-CONNECT system/@
-USER_US
-PASSWORD
-DEFAULT_TABLESPACE
-TEMPORARY_TABLESPACE
-EUL_LANGUAGE US
-APPS_GRANT_DETAILS/
This command should be entered on a single command line -- it is shown above on separate lines with extra spaces for ease of reference only.
For example:
$ORACLE_HOME/bin/eulapi -CREATE_EUL -APPS_MODE \
-CONNECT system/manager@cus115 -USER EUL_US -PASSWORD EUL_US \
-DEFAULT_TABLESPACE discoverer -TEMPORARY_TABLESPACE temp \
-EUL_LANGUAGE US -APPS_GRANT_DETAILS apps/apps
Note for Windows
%ORACLE_HOME%\bin\eulapi is not available with AS 10g Discoverer Version 9.0.4 on Windows. Please use following syntax instead:
%ORACLE_HOME%\jdk\bin\java -jar %ORACLE_HOME%\discoverer\lib\eulbuilder.jar.
Alternatively you may also use the equivalent Discoverer Administration Edition command line on Windows. Use the following syntax to create the eul using Discoverer Administration Edition:
%ORACLE_HOME%/bin/dis51adm.exe /CREATE_EUL /APPS_MODE \
/CONNECT system/manager@cus115 /USER EUL_US /PASSWORD EUL_US \
/DEFAULT_TABLESPACE discoverer /TEMPORARY_TABLESPACE temp \
/EUL_LANGUAGE US /APPS_GRANT_DETAILS apps/apps /SHOW_PROGRESS
6.5 Check max_extents for default rollback segments
The import and the refresh processes for the entire Applications 11i End User Layer - the complete set of .eex files - are large database transactions. Check that the default rollback segment where the Discoverer End User Layer is to be installed is large enough to support rolling back a 70 MB transaction at minimum:
6.5.1. Log on to SQL*Plus:
% sqlplus system/@)
6.5.2. Issue the following SQL command:
SQL> select SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS \
from DBA_SEGMENTS where SEGMENT_TYPE = 'ROLLBACK';
6.5.3. If the rollback segment for the tablespace where you plan to install the Discoverer End User Layer is less than 70 MB, you must either alter it or create a new rollback segment. Procedures for accomplishing this will vary depending upon your database configuration; see the Administrator's Guide for your Oracle database for more details.
7. Verify Java Virtual Machine for Discoverer Plus
Chapter 4.12 of the Oracle Application Server Discoverer Configuration Guide 10g (9.0.4) explains how to run Discoverer Plus with different Java Virtual Machines. Please note that Discoverer 10g is not supported to run with JInitiator 1.1.8. You must use JInitiator 1.3.
For more information on JInitiator please refer to MetaLink Note 124606.1 titled "Upgrading Oracle JInitiator with Oracle Applications 11i".
________________________________________
Section 6: Postinstallation Tasks
This section describes steps for configuring Oracle E-Business Suite Release 11i to use Discoverer 10g, and for loading the Applications-specific content and workbooks into the Discoverer 10g End User Layer.
1. Apply necessary Oracle Applications interoperability patches using AutoPatch
Note
If your environment was created using Oracle E-Business Suite Release 11.5.10 Rapid Install you can skip this step and proceed with Step 2. Oracle Applications interoperability patches are installed by default in Oracle E-Business Suite Release 11.5.10 Rapid Install.
1.1 Apply AD patch 3440628
This patch contains utilities for Discoverer End User Layer maintainance.
Download patch 3440628 from Oracle Metalink.
Follow the instructions in the README to apply the patch
1.2 Apply ICX patch 3133021
This patch contains support to invoke Discoverer from E-Business Suite functions.
Download patch 3133021 from Oracle Metalink.
Follow the instructions in the README to apply the patch
1.3 Apply the latest AutoConfig patch
Follow MetaLink Note 165195.1 titled "Using AutoConfig to Manage System Configurations with Oracle Applications 11i" and apply the latest available AutoConfig Patch to your system.
2. Set Applications Profile Options for Discoverer using AutoConfig
By default, AutoConfig configures profile options for Oracle E-Business Suite Release 11i environments for Discoverer 4i. Proceed with the following steps to allow AutoConfig switch profile options for your remote, standalone OracleAS Discoverer 10g server:
Edit the AutoConfig context file.
If you are currently using E-Business Suite level 11.5.9 or above, or have applied FND.G patchset (or above), you will use Oracle Applications Manager to edit your context file. For all previous versions, you will use the AutoConfig Context Editor tool.
To edit the context file using Oracle Applications Manager, login to Oracle Applications Manager and navigate to Site Map > AutoConfig > Edit Parameters.
To edit the context file using the AutoConfig Context Editor tool, enter the following commands:
% cd [COMMON_TOP]/util/editcontext/
% ./editcontext
Specify variables as listed in the table below:
Title Variable Name Description Value
Configure standalone discoverer s_disco_standalone Set the value of this variable to 'true' if E-business suite 11i is configured to use a standalone discoverer server true
Discoverer machine name s_disco_machine Set the value of this variable to the machine configured to run discoverer services
for example: ap6162rt.us.oracle.com
Discoverer port s_disco_port Set the value of this parameter to the port on which the http listener is listening for discoverer requests
for example: 7778
Discoverer protocol s_disco_protocol Set the value of this variable to the appropriate protocol used for accessing discoverer. Acceptable values for this parameter are http or https http|https
Disco Version Comma s_disco_ver_comma Disco Version 10
Discovery EUL prefix s_disco_eul_prefix Discovery EUL prefix EUL
Note
Other Discoverer related AutoConfig parameters are not relevant for Discoverer 10g configuration because AutoConfig does not control configuration of the remote, standalone OracleAS Discoverer 10g server node.
The changes will be saved in your Applications Context File/admin/.xml.
Run AutoConfig to generate all configuration files for the APPL_TOP and the ORACLE_HOMEs in its supporting technology stack:
/admin/scripts//adautocfg.sh
For further information about AutoConfig please refer to MetaLink Note 165195.1 titled "Using AutoConfig to Manage System Configurations with Oracle Applications 11i".
3. Verify Applications profile options in Oracle Applications
• Log into Release 11i with the System Administrator responsibility
• Navigate to the Profile > System form
• Query the %Discoverer% profile options.
• Verify the following ICX profile options in Oracle Applications:
Profile Description Value
ICX_DISCOVERER_LAUNCHER URL that points to Discoverer Server Launch page http://:
/discoverer/plus
?Connect=[APPS_SECURE]
ICX_DISCOVERER_VIEWER_LAUNCHER URL that points to Discoverer Viewer Servlet http://:
/discoverer/viewer
?Connect=[APPS_SECURE]
ICX_DISCOVERER_RELEASE 1 digit number specifying the Discoverer release 10
ICX_DISCOVERER_USE_VIEWER Specify whether Discoverer Viewer should be launched instead of Discoverer Plus (default). Y | N
ICX_DEFAULT_EUL ICX: Discoverer End User Layer Schema Prefix
This is the EULprefix. The EULprefix in combination with the Language code make up the EUL owner at runtime. EUL
ICX_DISCOVERER_EUL_LANGUAGE ICX: Discoverer End User Layer Language Override
Since the End User Layer content is currently available in US English only, it is possible to override the user's general language preference for the Discoverer End User Layer using this profile option. The specified End User Layer language will be used regardless of the individual user's language preferences.
This profile does not affect the National Language Support parameters passed to Discoverer. The Discoverer User Interface will still appear according to the users ICX_LANGUAGE and ICX_TERRITORY profiles. The User Interface language will only change for valid combinations of ICX_LANGUAGE and ICX_TERRITORY. US
ICX_DISCOVERER_PARAMETERS ICX: Discoverer Parameters
This profile may be used to optionally pass additional URL parameters to Discoverer. Compare Note 186120.1 on Oracle MetaLink.
Note
You may switch the ICX_DEFAULT_EUL profile at the responsibility level if you are planning to implement multiple End User Layers. For example:
• EUL_US for Business Intelligence System content
• EULEDW_US for Embedded Data Warehouse content
Then you can set ICX_DEFAULT_EUL to EULEDW for Embedded Data Warehouse responsibilities like "Financials Intelligence - Enterprise Data Warehouse". Those responsibilities will then use the Embedded Data Warehouse End User Layer.
4. Verify Location of Database Connection file
Discoverer 10g supports Single Sign-On functionality with Oracle E-Business Suite Release 11i. To support Single Sign-On, Discoverer needs access to the Database Connection (dbc) file for the database you wish to connect to. A dbc file is a text file which stores all the information required to connect to a particular database.
In the 10g Discoverer/discoverer/discwb.sh file check the variable for FND_TOP. If this is set to $OH/discoverer then ensure the directory $OH/discoverer/secure exists. Next copy the dbc file from the $FND_TOP/secure directory of the 11i instance you are setting up 10g Discoverer with to the 10g Discoverer $OH/discoverer/secure directory.
Note
The naming convention for the .dbc file for Oracle E-Business Suite Release 11.5.1 to 11.5.9 is
_.dbc.
The name is derived from:
select lower(host_name) || '_' || lower(instance_name) from v$instance;
The naming convention for the .dbc file for Oracle E-Business Suite Release 11.5.10 is
.dbc.
Discoverer will attempt to find the dbc file in following order
1. $FND_TOP/secure/.dbc
2. $FND_TOP/secure/_.dbc
Make sure $FND_TOP is set correctly for the user who starts the Discoverer service.
On Solaris variable FND_TOP is set in the environment script/discoverer/discwb.sh.
On Windows NT, make sure the FND_TOP variable is set as a Windows System Variable.
For more information about dbc files see Appendix 'Setting up and maintaining Oracle Applications' of the Oracle Applications System Administrators Guide .
5. Update tnsnames.ora file
On your standalone Oracle Application Server 10g node, update file $ORACLE_HOME/network/admin/tnsnames.ora and include the tnsnames entry to connect to your Oracle E-Business Suite Release 11i database. Use the same entry as exists in the tnsnames.ora file on your Oracle E-Business Suite Release 11i mid tier server node. The database name must match the two_task entry in the dbc file. Verify you can establish a connection to your Oracle E-Business Suite Release 11i database using sqlplus.
________________________________________
The remaining steps include patching your End User Layer to the latest level and are required only if you received new eex files, for example if you migrate to Discoverer 10g as part of uptaking a E-Business Suite 11i Maintainance Pack release, or if you implement the Discoverer End User Layer for the first time.
If you have only migrated your existing 4i End User Layers to Discoverer 10g without patching new eex files, you can skip the remaining steps in this section and proceed with Section 7.
________________________________________
6. Regenerate your Business Views
Most of the Discoverer folders delivered as part of the Oracle E-Business Suite Release 11i Discoverer content are based on Business Views. Business Views hide the complexity of the underlying Release 11i data model from non-technical users. Before you import or refresh the Discoverer .eex files you must regenerate all Business Views. This will enable the Discoverer import and refresh process to pull in the translated prompts for Key and Descriptive Flex Fields of your Business Views into your End User Layer. Make sure that every flexfield structure has at least one valid segment, and that all flexfields are frozen and compiled.
6.1. Apply Business View Generator patch patch 2921686 using AutoPatch.
6.1.1. Download the Business View Generator patch 2921686 from Oracle Metalink
6.1.2. Follow the instructions in the README to apply the patch.
6.2. Regenerate your Business Views by running the "Generate Business Views by Application" concurrent program using the Business View Setup responsibility:
Logon to Oracle Applications as SYSADMIN
Choose the "Business Views Setup" responsibility
Navigate to Reports > Run > Pick Single Request > "Generate ALL Business Views"
For additional details and background, refer to the Oracle Applications Flexfields Guide .
6.3. After regeneration of the Business Views has been completed, check the Business View Generator output file. It should not contain any errors.
6.4. Recompile all objects in the APPS schema using adadmin.
6.5. Ensure that the Business Intelligence System views exist and all Business Intelligence System 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 Business Intelligence System View causes the following error:
ORA-04063: view "APPS."
Attention
All invalid objects and errors must be resolved before you can proceed with the next steps, including import and refresh of the Discoverer End User Layer. Discoverer Administration Edition will drop End User Layer objects with dependencies on invalid Applications objects during the refresh process.
7. Set up Applications User/Responsibility for EUL Administration: SYSADMIN/System Administrator
The Release 11i System Administrator account must be granted permission to update and alter Discoverer 10g content.
7.1. Grant End User Layer Administration Privileges
Grant the End User Layer Administration privileges for user SYSADMIN using the following command, executed on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 .
$ORACLE_HOME/bin/eulapi \
-CONNECT/@ \
-GRANT_PRIVILEGE \
-USER SYSADMIN \
-PRIVILEGE administration \
-PRIVILEGE all_admin_privs \
-LOG
For all further End User Layer Administration activities you must use the Oracle Applications User with the System Administrator responsibility (e.g. SYSADMIN). This Applications user must import and own the Workbooks and Business Areas of all future Discoverer patches and share them as applicable with other Applications users and responsibilities.
7.2. Make sure user SYSADMIN has full security access to all Business Areas
This step is not necessary for the first import into a new End User Layer because the End User Layer does not contain any Business Areas at this point. But for all subsequent future imports, ensure that the user SYSADMIN has full access to all Business Areas.
Execute the following command on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 .
$ORACLE_HOME/bin/eulapi \
-CONNECT/@ \
-GRANT_PRIVILEGE \
-USER SYSADMIN \
-BUSINESS_AREA_ADMIN_ACCESS % \
-WILDCARD \
-LOG
Note for Windows
%ORACLE_HOME%\bin\eulapi is not available with AS 10g Discoverer Version 9.0.4 on Windows. Please use following syntax instead:
%ORACLE_HOME%\jdk\bin\java -jar %ORACLE_HOME%\discoverer\lib\eulbuilder.jar.
8. Mount Application Tier Server Node $AU_TOP/discover to Oracle10gAS BI instance
Discoverer .eex files are patched to your application tier server node, directory $AU_TOP/discover . Those files must be accessible from the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed.
Please mount your applications tier server node, directory $AU_TOP/discover on your standalone application server where Oracle Application Server 10g and Discoverer 10g were installed.
If you are unable to mount this directory, you can also zip and ftp the entire $AU_TOP/discover directory. In this case, make sure that you include the directory structure (ie. use zip -r) when you create the zip, and preserve the directory structure when you unzip the file on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed. The directory structure will include the import shell scripts $AU_TOP/discover/adupdeul.sh, $AU_TOP/discover/adrfseul.sh and the language specific subdirectories containing the set of .eex files.
9. Import Discoverer Content for Release 11i using adupdeul.sh mode=complete
In this step, you will run AD utility adupdeul.sh on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed to import the Discoverer loader files (.eex files) for Oracle E-Business Suite Release 11i into the Discoverer End User Layer.
9.1. On the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed, move to the mounted or copied ... /discover/ directory where adupdeul.sh is located
9.2. Start the import process of the Discoverer loader files (.eex files) using the shell script adupdeul.sh.
Syntax for the adupdeul.sh script:
% sh adupdeul.sh
connect=/@
resp=
gwyuid=
fndnam=
secgroup=
topdir=
language=US
eulprefix=
[iashome=]
[exedir=]
[eultype=]
[mode=]
[driver=]
[filelist=]
[logfile=]
[parfile=]
Where:
Parameter Description
secgroup APPS responsibility security group
topdir Top level directory where Discoverer files are available
iashome Oracle Application Server 10g Business Intelligence node Oracle Home directory
Required on UNIX platforms
exedir Directory where Discoverer executables are located
Required on Windows platforms
eulprefix EUL schema prefix, for example use Use eulprefix=EUL for schema EUL_US
eultype OLTP - default, for standard Applications 11i Discoverer content
EDW - for Enterprise Data Warehouse Discoverer content
Use eultype=OLTP to import the Discoverer 10g content for Oracle E-Business Suite Release 11i
mode COMPLETE - import all Discoverer content.
DRIVER - default, import specific subset of files
FILELIST - import specific subset of files
For the initial full import of the Discoverer 10g baseline files delivered by patch 1834171 , use mode=complete. This mode will upload all .eex files (*o.eex) from the .../discover/ directory into the Discoverer End User Layer.
driver Specifies a comma-separated list of driver files to be loaded.
This parameter is required if mode=DRIVER
filelist Specifies the filename of a file containing a list of files to be loaded.
This parameter is required if mode=FILELIST
logfile Log file where the adupdeul.sh script logs the result of the import process.
Default filename is adupdeul.log
parfile Filename of a command-line parameter file containing entries of the form PARAMETER=VALUE. Parameters specified at the command line take precedence over those specified in the parameter file.
Notes:
All parameters must be specified on a single command line or in a parameter file. Name-value pairs must not contain spaces. For example:
PARAMETER=VALUE is acceptable
PARAMETER = VALUE will generate errors
Example:
Mount or copy the directory $AU_TOP/discover as /user1/au_top/discover
cd /user1/au_top/discover (where adupdeul.sh is located)
Start the full import of all .eex files in topdir/US, where topdir is specified as a parameter
sh adupdeul.sh \
connect=sysadmin/sysadmin-password@db \
resp="System Administrator" \
gwyuid=APPLSYSPUB/PUB \
fndnam=APPS \
secgroup="Standard" \
topdir=/user1/au_top/discover \
language=US \
eulprefix=EUL \
eultype=OLTP \
mode=complete \
iashome=/oracle/iAS/bi \
logfile=import_complete_eul_us_cust115.log
The above example is displayed on multiple lines for clarity; do not type the backslash.
9.3. Performance of the import process greatly depends on network latency between the Oracle10gAS node and the database, and on available resources on your Oracle10gAS node. Depending on these factors, the import process may take less than one hour or up to several hours to complete for the entire set of Discoverer baseline .eex files. To minimize overhead from network latency during the import process, it is strongly recommended that Oracle10gAS be installed on a machine that is close to the database server and has a fast network connection.
9.4. Check the logfile after import. This should be a clean import logfile. You should be able to see that the import utility exited with status 0 and the logfile contains the message Import completed successfully.
You can safely ignore the following messages in the import log:
\\.eex: The import process did not import or modify any data
This messages provides information that.eex is an empty obsolete file and does not modify any data during import.
\\.eex: The identifier for ... contains one or more of the characters ! ~ - * ( ) ' which will be de-supported for use in identifiers in future releases of Discoverer - see release notes for more details.
You can safely ignore this message using Discoverer 10g. See release notes for more details.
9.5. As Release 11i products continually update and improve support for Discoverer in subsequent releases, it will be necessary to install additional Discoverer loader files after this initial installation. Please refer to Section 9 for details on importing subsequent patches using mode=driver.
10. Refresh the Discoverer 10g End User Layer
After the Discoverer 10g content for Release 11i has been imported into the Discoverer End User Layer on the database tier, the End User Layer must be refreshed against the Release 11i data model. This ensures that all references between business areas, folders, workbooks, business views, flexfields, and Release 11i database objects are valid.
On Unix platforms you may run the refresh process on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed.
Alternatively on Windows platforms you may run the refresh process on the machine where Discoverer Administration Edition is installed.
10.1. Move to the ... /discover/ directory where adrfseul.sh is located.
10.2. Start the refresh process against the database using the shell script adrfseul.sh.
Syntax for the adrfseul.sh script:
% sh adrfseul.sh
connect=/@
resp=
gwyuid=
fndnam=
secgroup=
eulschema=
eulpassword=
twotask=
[iashome=]
[exedir=]
[logfile=]
[parfile=]
Where:
Parameter Description
eulschema Full EUL schema name, for example eulschema=EUL_US
eulpassword EUL schema password.
twotask Database name as specified in your tnsnames.ora file.
iashome Oracle Application Server 10g Business Intelligence node Oracle Home directory
Required on UNIX platforms
exedir Directory where Discoverer executables are located
Required on Windows platforms
logfile Log file where the adupdeul.sh script logs the result of the import process.
Default filename is adrfseul.log
parfile Filename of a command-line parameter file containing entries of the form PARAMETER=VALUE. Parameters specified at the command line take precedence over those specified in the parameter file.
Notes:
All parameters must be specified on a single command line or in a parameter file. Name-value pairs must not contain spaces. For example:
PARAMETER=VALUE is acceptable
PARAMETER = VALUE will generate errors
Example:
cd /user1/au_top/discover (where adrfseul.sh is located)
Start the refresh process of all existing eul folders against the database
sh adrfseul.sh \
connect=sysadmin/sysadmin-password@mydb \
resp="System Administrator" \
gwyuid=APPLSYSPUB/PUB \
fndnam=APPS \
secgroup="Standard" \
eulschema=EUL_US \
eulpassword=EUL123 \
twotask=mydb \
iashome=/oracle/iAS/bi \
logfile=refresh_eul_us_mydb.log
The above example is displayed on multiple lines for clarity; do not type the backslash.
Note
For the refresh process you must use the Applications User/Responsibility SYSADMIN:System Administrator to logon rather than the End User Layer database schema owner. The End User Layer database schema owner doesn't have the necessary grants for the APPS schema and the refresh process would drop Discoverer folders! Using adrfseul.sh for the refresh enforces using Applications User/Responsibility in the connect string.
For all Business Areas expect several hours for the refresh process to complete, depending on your flex structure. Your End User Layer is now refreshed. Flex columns have been pulled in to the folders using the flex prompts in the language of the End User Layer as Item Display Names.
11. Grant Access to Responsibilities, Users, and Workbooks (optional)
Individual Discoverer users may be granted access to specific Release 11i responsibilities, which in turn provide access to Discoverer business areas and workbooks. Either the Discoverer 10g eulapi UNIX command-line utility or Discoverer 10g Administration Edition can be used to associate business area access with Release 11i responsibilities, and to identify workbook access privileges for responsibilities and users.
The procedures described in this step may be used as guidelines for planning your Discoverer reporting security. Your Discoverer reporting strategy may be defined to reflect your corporate data security strategy, policies, and business requirements. Discoverer security is flexible and highly granular, and you may wish to provide broader access for specific individuals who need to be able to see data enterprise-wide, i.e. across organizational boundaries.
Example: You may wish some Financial analysts to have access to all of the Financials Intelligence business areas, such as the Accounts Payables and Accounts Receivables. To do this, grant access to all of the Financials Intelligence business areas to the Financials Intelligence responsibility. The Financials Intelligence responsibility may then be granted to the individual Financial analysts who should have access to those areas.
Note
If you have upgraded to the Oracle Applications 11.5.8 Maintenance Pack or later Maintenance Pack releases, you can skip this step.
The 11.5.8 Maintainance Pack and later Maintenance Pack releases already include all necessary security grants and workbook sharing for the pre-seeded Oracle Applications responsibilities. If you wish to grant security access for Business Areas to Oracle Application users and responsibilities in addition to the grants that are provided with the 11.5.8 Maintenance Pack and higher, you may optionally follow the procedures listed in this step.
11.1 Grant Privileges, Security and Workbook Access on Unix
For information about delivered Business Areas with Oracle E-Business Intelligence Suite please refer to Chapter 4 of the Oracle Business Intelligence System Implementation Guide . You must grant security access to the Oracle Applications responsibilities you would like to use for each Business Area.
You may use the Discoverer 10g eulapi command line utility on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 to grant privileges, business area security and workbook access.
For help on usage of the eulapi command-line utility, type:
$ORACLE_HOME/bin/eulapi -help grant_privilege
Example 1:
Grant Discoverer User privileges to responsibility "Financials Intelligence"
$ORACLE_HOME/bin/eulapi \
-CONNECT/@ \
-GRANT_PRIVILEGE \
-ROLE "Financials Intelligence" \
-PRIVILEGE all_user_privs
Example 2:
Grant security access for business area "General Ledger" to responsibility "Financials Intelligence"
$ORACLE_HOME/bin/eulapi
-CONNECT/@ \
-GRANT_PRIVILEGE \
-ROLE "Financials Intelligence" \
-BUSINESS_AREA_ACCESS "General Ledger"
11.2. Grant Security and Privileges using Discoverer Administration Edition on Windows
If you have already granted security and privileges using the Discoverer 10g eulapi command-line utility, you may skip this step.
Log in to the Discoverer Administration Edition 9.0.4 as the Applications User which was granted full Administration Privileges and Security:
\bin\dis5adm.exe /CONNECT "SYSADMIN:System Administrator/@" /EUL _
Use the Help -> About Discoverer... dialog to make sure you are connected to the correct End User Layer (_; compare the import options used above). You can change the EUL in the dialog Tools -> Options -> Default EUL.
Using Tools -> Privileges
Check "User Edition" privileges for all Applications Users and Responsibilities who should be allowed to open workbooks.
Using Tools -> Security
Grant Business Area access to Applications Users and Responsibilities who should be allowed to query the various Business Areas.
11.3 Share Workbooks using Discoverer Plus Edition
You must grant shared workbook access to the Oracle Applications responsibilities to be used for workbook analysis.
1. Log in to the Discoverer User Edition 9.0.4 as the Applications Administration user who imported the eex files in Section 6, Step 8 .
\bin\dis5usr.exe /APPS_USER /CONNECT "SYSADMIN:System Administrator/@" /EUL _
2. Use the Help -> About Discoverer... dialog to confirm that you are connected to the correct End User Layer (_; compare the import options used in above). You can change the End User Layer in the dialog Tools -> Options -> Default EUL.
3. Using the File -> Manage Workbooks... -> Sharing... dialog
Share the workbooks to the Applications Users and Responsibilities that should be allowed to open the various workbooks.
11.4. Test Discoverer Responsibilities and Workbooks
Log in to Oracle E-Business Suite Release 11i with a userid that has been granted one of the responsibilities defined above.
Open the Release 11 workbooks assigned to that responsibility and verify that they can retrieve and display data.
Repeat for each responsibility that has been defined.
Congratulations!
You have completed your Discoverer 10g implementation and your Discoverer content for Oracle E-Business Suite Release 11i products is ready for use.
________________________________________
Section 7: Available Documentation
For a summary of installation and configuration documentation for Oracle Application Server 10g and its associated components, see the following Note on Oracle Metalink :
Oracle Application Server (10gAS) with Oracle E-Business Suite Release 11i Documentation (Note 207159.1)
________________________________________
Section 8: Conventions and Important Directory Locations
Conventions
The following typographical conventions are used in this document:
Convention Meaning
\ In examples of commands you type online, a backward slash at the end of a line signifies that you must type the entire command on one line. Do not type the backslash.
Mono space text Represents command line text. Type this text exactly as shown.
<> or [] Text enclosed in angled or square brackets represents a variable. Substitute an appropriate value for the variable text. Do not type the brackets.
Directory Paths Directory paths in this document are relative to the top level installation directory for the Oracle E-Business Suite. e.g. if you installed the Oracle E-Business Suite under a directory named /my/appsinstall and are pointing to an Oracle E-Business Suite Release 11i database named mytestdb, thenora/iAS/Apache in this document will mean the fully qualified path: /my/appsinstall/mytestdbora/iAS/Apache
April 2005
Availability
Oracle E-Business Suite Release 11i is certified for integration with Oracle Discoverer 10g (9.0.4). Discoverer 9.0.4 must be installed in a separate Oracle Application Server 10g ORACLE_HOME on an existing application tier server node or on a standalone server, accessing an Oracle E-Business Suite Release 11i database.
Althought this Metalink Note will continue to be published for reference purposes, Oracle strongly recommends that customers interested in using Discoverer with the E-Business Suite use the latest certified version: Discoverer 10.1.2.. See MetaLink Note 313418.1 titled, Using Discoverer 10.1.2 with Oracle E-Business Suite 11i.
Usage of additional Oracle Application Server 10g components Oracle Single Sign-On, Oracle Internet Directory and Oracle Portal for integration with Oracle E-Business Suite Release 11i is documented in MetaLink Note 233436.1 titled, Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i.
________________________________________
This document contains information for using OracleAS Discoverer 10g (9.0.4) from Oracle Application Server 10g with Oracle Applications 11i. You should read and understand all content described here before you begin using this feature. The most current version of these notes is 257798.1 on OracleMetaLink. There is a change log at the end of this document.
• Section 1: Overview
• Section 2: Supported Architectures
• Section 3: Release Versions
• Section 4: Preinstallation Tasks
• Section 5: Installation Tasks
• Section 6: Postinstallation Tasks
• Section 7: Available Documentation
• Section 8: Conventions and Important Directory Locations
• Section 9: Installation of Additional Discoverer End User Layer patches
• Section 10: Known Issues and Limitations
________________________________________
Section 1: Overview
OracleAS Discoverer 10g, a key component of Oracle Application Server 10g, is an integrated business intelligence solution supporting intuitive ad hoc query, reporting, analysis, and web publishing. Business users at all levels of the organization can use Discoverer 10g to gain immediate access to information from data marts, data warehouses, and online transaction processing (OLTP) systems. Discoverer 10g 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.
Discoverer 10g is tightly integrated with Oracle E-Business Suite Release 11i. Release 11i users can use Discoverer to analyze data from selected business areas in Financials, Operations, Human Resources, Purchasing, Process Manufacturing, Activity Based Management, and more.
This document describes how to install Discoverer 10g into an existing Oracle E-Business Suite Release 11i environment. Installation of Discoverer 10g into an Oracle E-Business Suite Release 11i environment consists of:
• Installation of Discoverer 10g with Oracle Application Server 10g on a standalone application tier server node
• Creation of the Discoverer 10g End User Layer (EUL) on a database tier server node
• Import of the Oracle E-Business Suite Release 11i Discoverer content into the Discoverer 10g End User Layer
• (Optional) Installation of Discoverer Administration Edition on a Windows-based PC that can be used to customize Discoverer
For current Discoverer users, it also describes how to upgrade an existing Discoverer End User Layer from Discoverer 3i or 4i to Discoverer 10g.
The procedures in this document have significant effects on Oracle E-Business Suite Release 11i environments and should be executed only by skilled Oracle E-Business Suite database or systems administrators. Users are strongly advised to make backups of their environments before executing any of the procedures noted, and to test their environments before executing these procedures in production environments. Users must log off the system while these changes are being applied.
Availability of Translations
Discoverer content for Oracle E-Business Suite Release 11i includes workbooks and the End User Layer for both regular Applications modules as well as Business Intelligence System (BIS) products such as Financials Intelligence and Purchasing Intelligence. This content is available only in US English, and there are no translations planned for other languages.
________________________________________
Section 2: Supported Architectures
Discoverer 10g must be installed in a separate ORACLE_HOME on an existing application tier server node or on a stand-alone server, accessing an Oracle E-Business Suite Release 11i database.
________________________________________
Section 3: Release Versions
The following components must be used on the stand-alone instance:
Component Name Version
Oracle Application Server Enterprise Edition 10g
The following components must be used on the Oracle E-Business Suite Release 11i instance:
Component Name Version
Oracle E-Business Suite Release 11i 11.5.8 to 11.5.10
________________________________________
Section 4: Preinstallation Tasks
1. Read Discoverer 10g Configuration Guide
Chapter 1 of the Oracle Application Server Discoverer Configuration Guide 10g (9.0.4) explains how to run, maintain and support OracleAS Discoverer. At minimum, review this chapter before proceeding further.
2. Preserve Discoverer 3i Customizations (optional)
Skip this step if you are upgrading an existing Discoverer 4i End User Layer or if you are installing a Discoverer End User Layer for the first time.
If you are upgrading from Discoverer 3i to Discoverer 10g, you may wish to preserve your existing custom workbooks and business areas. Custom business areas built in Discoverer 3.1 may be migrated to the new Discoverer 10g End User Layer if:
They do not reference any Oracle delivered content
They use only Display Names which do not conflict with the Oracle delivered content, for example, by using a custom prefix
Migration of Discoverer 3i content to Discoverer 10g is a two-step process:
2.1 Migrate Discoverer 3i End User Layer customizations to Discoverer 4i
Refer to Note 139516.1 on OracleMetaLink for migration of Discoverer 3i customizations to Discoverer 4i.
2.2 Migrate Discoverer 4i End User Layer to Discoverer 10g
Continue with migration of your existing Discoverer 4i End User Layer to Discoverer 10g as detailed below.
________________________________________
Section 5: Installation Tasks
This section outlines steps required to install Oracle Application Server 10g, Discoverer 10g, and the Discoverer 10g End User Layer. See Section 8: Conventions and Important Directory Locations for important information about entering commands listed in this section.
1. Install Oracle Application Server 10g Enterprise Edition
Oracle Application Server 10g Enterprise Edition includes Discoverer Server 10g, Discoverer Plus 10g, and Discoverer Viewer 10g. You must install OracleAS 10g Infrastructure and OracleAS 10g Business Intelligence and Forms middle tier in separate ORACLE_HOMES on an existing application tier server node, or on a standalone server accessing an Oracle E-Business Suite Release 11i database.
You must install Oracle10g Application Server Enterprise Edition by following the instructions in the Oracle Application Server 10g Installation Guide for your operating system platform. In particular for details on the OracleAS 10g topology and installation sequence please refer to section 11.3 in the Oracle Application Server 10g Installation Guide.
Note
If you already have an existing integrated AS Discoverer 10g (9.0.4) instance you can upgrade this instance to the latest certified patch level following Step 2 below.
2. Apply the latest certified Oracle Application Server 10g Discoverer patches
2.1 Apply Oracle Application Server 10g Patchset 9.0.4.1
Download patch 3784229 ORACLE AS 10G (9.0.4) PATCH SET (9.0.4.1.0) for your appropriate Discoverer Server node platform from Oracle Metalink.
Follow the installation instructions provided in the patch README to install the patch on your Discoverer Server node.
2.2 Apply MLR Patch on top of 9.0.4.1
Download the appropriate platform-specific one-off patch for your Discoverer Server node from Oracle Metalink.
Operating System Platform
Sun Solaris (SPARC) Microsoft
Windows HP HP/UX 11.0 IBM AIX Linux
3700386
3700386
3952149
4034160
3891698
Follow the installation instructions provided in the patch README to install the patch on your Discoverer Server node.
3. Install Internet Developer Suite 10g (Optional)
3.1. Install Internet Developer Suite 10g on a Windows PC
If you wish to use Discoverer Administration Edition to customize your Discoverer End User Layer, install Internet Developer Suite 10g containing Discoverer 10g Administration Edition on a Windows PC.
3.2. Apply the latest certified Discoverer Administration Edition Patchset
Download patch 3628736 DEVELOPER SUITE 10G (9.0.4) PATCH SET 1 (9.0.4.1.0) from Oracle Metalink and follow the installation instructions provided in the patch README to install the patch on your Discoverer Administration Edition Server.
3.3. Set Windows Registry Settings
If you are using Discoverer Administration Edition on Windows to refresh your End User Layer, please follow the steps below to set necessary registry settings.
Attention
This step contains information about modifying the Microsoft Windows registry. Before you modify the Windows registry, back it up and make sure that you understand how to restore it if a problem occurs. For information about how to back up, restore, and edit the registry, see the following article number in the Microsoft Knowledge Base :
Q256986 Description of the Microsoft Windows Registry
On the Windows PC running Discoverer 10g Administration Edition:
3.3.1 Back up your current Microsoft Windows Registry
3.3.2 Open the Windows Registry editor and create or update the following registry keys:
Registry Key DataType Value
HKEY_CURRENT_USER\Software\Oracle\Discoverer 9\
Database\EnableTriggers REG_DWORD 1
HKEY_CURRENT_USER\Software\Oracle\Discoverer 9\
Database\DefaultPreserveDisplayPropertyForRefresh REG_DWORD 1
4. Specify Connect Dialog for Oracle Applications Users
If you use the Discoverer 10g Connection Management pages for direct login using Oracle Applications User/Password authentication, you must set up the Connect dialog to display the Applications User checkbox.
4.1. Open the file
4.2. Set the DefaultUserTypeIsApps preference to "1"
4.3. Set the ShowUserTypeChoice preference to "1"
4.4. Save the updated pref.txt file.
4.5. On UNIX, run the script
4.6. Stop and restart the OracleDiscoverer10g service to refresh the environment with the updated preferences
For details about other options that may be set for direct login, please refer to Chapter 13 of the Oracle Application Server Discoverer Configuration Guide 10g (9.0.4)
Note
Users may enter their user ID and Applications responsibility in the Connect dialog using the format:
UserID:
If you append a colon : immediately after the user in the Discoverer Connect dialog without specifying a responsibility after the colon, Discoverer will always try to establish a connection as an Oracle Applications user and pop up a list of valid Applications responsibilities for this Applications user.
5. Migrate your existing Discoverer 4i End User Layers to Discoverer 10g.
If you do not have an existing Discoverer 4i End User Layer, skip this step and proceed with Step 6 .
Upgrade your existing Discoverer 4i End User Layer to Discoverer 10g by using the following command on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 .
$ORACLE_HOME/bin/eulapi -CONNECT
The upgrade process creates a new Discoverer 10g End User Layer based the existing Discoverer 4i End User Layer. The existing Discoverer 4i End User Layer remains unchanged. A new schema containing the Discoverer 10g End User Layer will be created under the same Database User. After migration, the Database User contains both functional 4i and 10g euls. After verification of successful upgrade, you should drop the 4i eul.
6. Create new Discoverer 10g End User Layer
You must create a new Discoverer 10g End User Layer if you do not have an existing Discoverer 4i End User Layer.
We recommend that you create a new tablespace to store database objects for the Discoverer 10g End User Layer in your Oracle Applications database.
6.1. Execute this step on your database tier with your environment pointing to your database ORACLE_HOME. To set the environment correctly, source the file:
[DB_ORACLE_HOME]/
6.2. 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:
% sqlplus /NOLOG
SQL> connect sys/
SQL> create tablespace DISCOVERER datafile \
'[DB_ORACLE_HOME]/dbf/discoverer01.dbf' size 200M reuse \
extent management local uniform size 128K;
SQL> /
Statement Processed
6.3. Exit SQL*Plus:
SQL> quit
6.4. Create the new Discoverer 10g End User Layer by using the following command, executed on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 .
$ORACLE_HOME/bin/eulapi
-CREATE_EUL
-APPS_MODE
-CONNECT system/
-USER
-PASSWORD
-DEFAULT_TABLESPACE
-TEMPORARY_TABLESPACE
-EUL_LANGUAGE US
-APPS_GRANT_DETAILS
This command should be entered on a single command line -- it is shown above on separate lines with extra spaces for ease of reference only.
For example:
$ORACLE_HOME/bin/eulapi -CREATE_EUL -APPS_MODE \
-CONNECT system/manager@cus115 -USER EUL_US -PASSWORD EUL_US \
-DEFAULT_TABLESPACE discoverer -TEMPORARY_TABLESPACE temp \
-EUL_LANGUAGE US -APPS_GRANT_DETAILS apps/apps
Note for Windows
%ORACLE_HOME%\bin\eulapi is not available with AS 10g Discoverer Version 9.0.4 on Windows. Please use following syntax instead:
%ORACLE_HOME%\jdk\bin\java -jar %ORACLE_HOME%\discoverer\lib\eulbuilder.jar
Alternatively you may also use the equivalent Discoverer Administration Edition command line on Windows. Use the following syntax to create the eul using Discoverer Administration Edition:
%ORACLE_HOME%/bin/dis51adm.exe /CREATE_EUL /APPS_MODE \
/CONNECT system/manager@cus115 /USER EUL_US /PASSWORD EUL_US \
/DEFAULT_TABLESPACE discoverer /TEMPORARY_TABLESPACE temp \
/EUL_LANGUAGE US /APPS_GRANT_DETAILS apps/apps /SHOW_PROGRESS
6.5 Check max_extents for default rollback segments
The import and the refresh processes for the entire Applications 11i End User Layer - the complete set of .eex files - are large database transactions. Check that the default rollback segment where the Discoverer End User Layer is to be installed is large enough to support rolling back a 70 MB transaction at minimum:
6.5.1. Log on to SQL*Plus:
% sqlplus system/
6.5.2. Issue the following SQL command:
SQL> select SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS \
from DBA_SEGMENTS where SEGMENT_TYPE = 'ROLLBACK';
6.5.3. If the rollback segment for the tablespace where you plan to install the Discoverer End User Layer is less than 70 MB, you must either alter it or create a new rollback segment. Procedures for accomplishing this will vary depending upon your database configuration; see the Administrator's Guide for your Oracle database for more details.
7. Verify Java Virtual Machine for Discoverer Plus
Chapter 4.12 of the Oracle Application Server Discoverer Configuration Guide 10g (9.0.4) explains how to run Discoverer Plus with different Java Virtual Machines. Please note that Discoverer 10g is not supported to run with JInitiator 1.1.8. You must use JInitiator 1.3.
For more information on JInitiator please refer to MetaLink Note 124606.1 titled "Upgrading Oracle JInitiator with Oracle Applications 11i".
________________________________________
Section 6: Postinstallation Tasks
This section describes steps for configuring Oracle E-Business Suite Release 11i to use Discoverer 10g, and for loading the Applications-specific content and workbooks into the Discoverer 10g End User Layer.
1. Apply necessary Oracle Applications interoperability patches using AutoPatch
Note
If your environment was created using Oracle E-Business Suite Release 11.5.10 Rapid Install you can skip this step and proceed with Step 2. Oracle Applications interoperability patches are installed by default in Oracle E-Business Suite Release 11.5.10 Rapid Install.
1.1 Apply AD patch 3440628
This patch contains utilities for Discoverer End User Layer maintainance.
Download patch 3440628 from Oracle Metalink.
Follow the instructions in the README to apply the patch
1.2 Apply ICX patch 3133021
This patch contains support to invoke Discoverer from E-Business Suite functions.
Download patch 3133021 from Oracle Metalink.
Follow the instructions in the README to apply the patch
1.3 Apply the latest AutoConfig patch
Follow MetaLink Note 165195.1 titled "Using AutoConfig to Manage System Configurations with Oracle Applications 11i" and apply the latest available AutoConfig Patch to your system.
2. Set Applications Profile Options for Discoverer using AutoConfig
By default, AutoConfig configures profile options for Oracle E-Business Suite Release 11i environments for Discoverer 4i. Proceed with the following steps to allow AutoConfig switch profile options for your remote, standalone OracleAS Discoverer 10g server:
Edit the AutoConfig context file.
If you are currently using E-Business Suite level 11.5.9 or above, or have applied FND.G patchset (or above), you will use Oracle Applications Manager to edit your context file. For all previous versions, you will use the AutoConfig Context Editor tool.
To edit the context file using Oracle Applications Manager, login to Oracle Applications Manager and navigate to Site Map > AutoConfig > Edit Parameters.
To edit the context file using the AutoConfig Context Editor tool, enter the following commands:
% cd [COMMON_TOP]/util/editcontext/
% ./editcontext
Specify variables as listed in the table below:
Title Variable Name Description Value
Configure standalone discoverer s_disco_standalone Set the value of this variable to 'true' if E-business suite 11i is configured to use a standalone discoverer server true
Discoverer machine name s_disco_machine Set the value of this variable to the machine configured to run discoverer services
for example: ap6162rt.us.oracle.com
Discoverer port s_disco_port Set the value of this parameter to the port on which the http listener is listening for discoverer requests
for example: 7778
Discoverer protocol s_disco_protocol Set the value of this variable to the appropriate protocol used for accessing discoverer. Acceptable values for this parameter are http or https http|https
Disco Version Comma s_disco_ver_comma Disco Version 10
Discovery EUL prefix s_disco_eul_prefix Discovery EUL prefix EUL
Note
Other Discoverer related AutoConfig parameters are not relevant for Discoverer 10g configuration because AutoConfig does not control configuration of the remote, standalone OracleAS Discoverer 10g server node.
The changes will be saved in your Applications Context File
Run AutoConfig to generate all configuration files for the APPL_TOP and the ORACLE_HOMEs in its supporting technology stack:
For further information about AutoConfig please refer to MetaLink Note 165195.1 titled "Using AutoConfig to Manage System Configurations with Oracle Applications 11i".
3. Verify Applications profile options in Oracle Applications
• Log into Release 11i with the System Administrator responsibility
• Navigate to the Profile > System form
• Query the %Discoverer% profile options.
• Verify the following ICX profile options in Oracle Applications:
Profile Description Value
ICX_DISCOVERER_LAUNCHER URL that points to Discoverer Server Launch page http://
/discoverer/plus
?Connect=[APPS_SECURE]
ICX_DISCOVERER_VIEWER_LAUNCHER URL that points to Discoverer Viewer Servlet http://
/discoverer/viewer
?Connect=[APPS_SECURE]
ICX_DISCOVERER_RELEASE 1 digit number specifying the Discoverer release 10
ICX_DISCOVERER_USE_VIEWER Specify whether Discoverer Viewer should be launched instead of Discoverer Plus (default). Y | N
ICX_DEFAULT_EUL ICX: Discoverer End User Layer Schema Prefix
This is the EULprefix. The EULprefix in combination with the Language code make up the EUL owner at runtime. EUL
ICX_DISCOVERER_EUL_LANGUAGE ICX: Discoverer End User Layer Language Override
Since the End User Layer content is currently available in US English only, it is possible to override the user's general language preference for the Discoverer End User Layer using this profile option. The specified End User Layer language will be used regardless of the individual user's language preferences.
This profile does not affect the National Language Support parameters passed to Discoverer. The Discoverer User Interface will still appear according to the users ICX_LANGUAGE and ICX_TERRITORY profiles. The User Interface language will only change for valid combinations of ICX_LANGUAGE and ICX_TERRITORY. US
ICX_DISCOVERER_PARAMETERS ICX: Discoverer Parameters
This profile may be used to optionally pass additional URL parameters to Discoverer. Compare Note 186120.1 on Oracle MetaLink.
Note
You may switch the ICX_DEFAULT_EUL profile at the responsibility level if you are planning to implement multiple End User Layers. For example:
• EUL_US for Business Intelligence System content
• EULEDW_US for Embedded Data Warehouse content
Then you can set ICX_DEFAULT_EUL to EULEDW for Embedded Data Warehouse responsibilities like "Financials Intelligence - Enterprise Data Warehouse". Those responsibilities will then use the Embedded Data Warehouse End User Layer.
4. Verify Location of Database Connection file
Discoverer 10g supports Single Sign-On functionality with Oracle E-Business Suite Release 11i. To support Single Sign-On, Discoverer needs access to the Database Connection (dbc) file for the database you wish to connect to. A dbc file is a text file which stores all the information required to connect to a particular database.
In the 10g Discoverer
Note
The naming convention for the .dbc file for Oracle E-Business Suite Release 11.5.1 to 11.5.9 is
The name is derived from:
select lower(host_name) || '_' || lower(instance_name) from v$instance;
The naming convention for the .dbc file for Oracle E-Business Suite Release 11.5.10 is
Discoverer will attempt to find the dbc file in following order
1. $FND_TOP/secure/
2. $FND_TOP/secure/
Make sure $FND_TOP is set correctly for the user who starts the Discoverer service.
On Solaris variable FND_TOP is set in the environment script
On Windows NT, make sure the FND_TOP variable is set as a Windows System Variable.
For more information about dbc files see Appendix 'Setting up and maintaining Oracle Applications' of the Oracle Applications System Administrators Guide .
5. Update tnsnames.ora file
On your standalone Oracle Application Server 10g node, update file $ORACLE_HOME/network/admin/tnsnames.ora and include the tnsnames entry to connect to your Oracle E-Business Suite Release 11i database. Use the same entry as exists in the tnsnames.ora file on your Oracle E-Business Suite Release 11i mid tier server node. The database name must match the two_task entry in the dbc file. Verify you can establish a connection to your Oracle E-Business Suite Release 11i database using sqlplus.
________________________________________
The remaining steps include patching your End User Layer to the latest level and are required only if you received new eex files, for example if you migrate to Discoverer 10g as part of uptaking a E-Business Suite 11i Maintainance Pack release, or if you implement the Discoverer End User Layer for the first time.
If you have only migrated your existing 4i End User Layers to Discoverer 10g without patching new eex files, you can skip the remaining steps in this section and proceed with Section 7.
________________________________________
6. Regenerate your Business Views
Most of the Discoverer folders delivered as part of the Oracle E-Business Suite Release 11i Discoverer content are based on Business Views. Business Views hide the complexity of the underlying Release 11i data model from non-technical users. Before you import or refresh the Discoverer .eex files you must regenerate all Business Views. This will enable the Discoverer import and refresh process to pull in the translated prompts for Key and Descriptive Flex Fields of your Business Views into your End User Layer. Make sure that every flexfield structure has at least one valid segment, and that all flexfields are frozen and compiled.
6.1. Apply Business View Generator patch patch 2921686 using AutoPatch.
6.1.1. Download the Business View Generator patch 2921686 from Oracle Metalink
6.1.2. Follow the instructions in the README to apply the patch.
6.2. Regenerate your Business Views by running the "Generate Business Views by Application" concurrent program using the Business View Setup responsibility:
Logon to Oracle Applications as SYSADMIN
Choose the "Business Views Setup" responsibility
Navigate to Reports > Run > Pick Single Request > "Generate ALL Business Views"
For additional details and background, refer to the Oracle Applications Flexfields Guide .
6.3. After regeneration of the Business Views has been completed, check the Business View Generator output file. It should not contain any errors.
6.4. Recompile all objects in the APPS schema using adadmin.
6.5. Ensure that the Business Intelligence System views exist and all Business Intelligence System 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 Business Intelligence System View causes the following error:
ORA-04063: view "APPS.
Attention
All invalid objects and errors must be resolved before you can proceed with the next steps, including import and refresh of the Discoverer End User Layer. Discoverer Administration Edition will drop End User Layer objects with dependencies on invalid Applications objects during the refresh process.
7. Set up Applications User/Responsibility for EUL Administration: SYSADMIN/System Administrator
The Release 11i System Administrator account must be granted permission to update and alter Discoverer 10g content.
7.1. Grant End User Layer Administration Privileges
Grant the End User Layer Administration privileges for user SYSADMIN using the following command, executed on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 .
$ORACLE_HOME/bin/eulapi \
-CONNECT
-GRANT_PRIVILEGE \
-USER SYSADMIN \
-PRIVILEGE administration \
-PRIVILEGE all_admin_privs \
-LOG
For all further End User Layer Administration activities you must use the Oracle Applications User with the System Administrator responsibility (e.g. SYSADMIN). This Applications user must import and own the Workbooks and Business Areas of all future Discoverer patches and share them as applicable with other Applications users and responsibilities.
7.2. Make sure user SYSADMIN has full security access to all Business Areas
This step is not necessary for the first import into a new End User Layer because the End User Layer does not contain any Business Areas at this point. But for all subsequent future imports, ensure that the user SYSADMIN has full access to all Business Areas.
Execute the following command on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 .
$ORACLE_HOME/bin/eulapi \
-CONNECT
-GRANT_PRIVILEGE \
-USER SYSADMIN \
-BUSINESS_AREA_ADMIN_ACCESS % \
-WILDCARD \
-LOG
Note for Windows
%ORACLE_HOME%\bin\eulapi is not available with AS 10g Discoverer Version 9.0.4 on Windows. Please use following syntax instead:
%ORACLE_HOME%\jdk\bin\java -jar %ORACLE_HOME%\discoverer\lib\eulbuilder.jar
8. Mount Application Tier Server Node $AU_TOP/discover to Oracle10gAS BI instance
Discoverer .eex files are patched to your application tier server node, directory $AU_TOP/discover . Those files must be accessible from the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed.
Please mount your applications tier server node, directory $AU_TOP/discover on your standalone application server where Oracle Application Server 10g and Discoverer 10g were installed.
If you are unable to mount this directory, you can also zip and ftp the entire $AU_TOP/discover directory. In this case, make sure that you include the directory structure (ie. use zip -r) when you create the zip, and preserve the directory structure when you unzip the file on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed. The directory structure will include the import shell scripts $AU_TOP/discover/adupdeul.sh, $AU_TOP/discover/adrfseul.sh and the language specific subdirectories containing the set of .eex files.
9. Import Discoverer Content for Release 11i using adupdeul.sh mode=complete
In this step, you will run AD utility adupdeul.sh on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed to import the Discoverer loader files (.eex files) for Oracle E-Business Suite Release 11i into the Discoverer End User Layer.
9.1. On the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed, move to the mounted or copied ... /discover/ directory where adupdeul.sh is located
9.2. Start the import process of the Discoverer loader files (.eex files) using the shell script adupdeul.sh.
Syntax for the adupdeul.sh script:
% sh adupdeul.sh
connect=
resp=
gwyuid=
fndnam=
secgroup=
topdir=
language=US
eulprefix=
[iashome=
[exedir=
[eultype=
[mode=
[driver=
[filelist=
[logfile=
[parfile=
Where:
Parameter Description
secgroup APPS responsibility security group
topdir Top level directory where Discoverer files are available
iashome Oracle Application Server 10g Business Intelligence node Oracle Home directory
Required on UNIX platforms
exedir Directory where Discoverer executables are located
Required on Windows platforms
eulprefix EUL schema prefix, for example use Use eulprefix=EUL for schema EUL_US
eultype OLTP - default, for standard Applications 11i Discoverer content
EDW - for Enterprise Data Warehouse Discoverer content
Use eultype=OLTP to import the Discoverer 10g content for Oracle E-Business Suite Release 11i
mode COMPLETE - import all Discoverer content.
DRIVER - default, import specific subset of files
FILELIST - import specific subset of files
For the initial full import of the Discoverer 10g baseline files delivered by patch 1834171 , use mode=complete. This mode will upload all .eex files (*o.eex) from the .../discover/ directory into the Discoverer End User Layer.
driver Specifies a comma-separated list of driver files to be loaded.
This parameter is required if mode=DRIVER
filelist Specifies the filename of a file containing a list of files to be loaded.
This parameter is required if mode=FILELIST
logfile Log file where the adupdeul.sh script logs the result of the import process.
Default filename is adupdeul.log
parfile Filename of a command-line parameter file containing entries of the form PARAMETER=VALUE. Parameters specified at the command line take precedence over those specified in the parameter file.
Notes:
All parameters must be specified on a single command line or in a parameter file. Name-value pairs must not contain spaces. For example:
PARAMETER=VALUE is acceptable
PARAMETER = VALUE will generate errors
Example:
Mount or copy the directory $AU_TOP/discover as /user1/au_top/discover
cd /user1/au_top/discover (where adupdeul.sh is located)
Start the full import of all .eex files in topdir/US, where topdir is specified as a parameter
sh adupdeul.sh \
connect=sysadmin/sysadmin-password@db \
resp="System Administrator" \
gwyuid=APPLSYSPUB/PUB \
fndnam=APPS \
secgroup="Standard" \
topdir=/user1/au_top/discover \
language=US \
eulprefix=EUL \
eultype=OLTP \
mode=complete \
iashome=/oracle/iAS/bi \
logfile=import_complete_eul_us_cust115.log
The above example is displayed on multiple lines for clarity; do not type the backslash.
9.3. Performance of the import process greatly depends on network latency between the Oracle10gAS node and the database, and on available resources on your Oracle10gAS node. Depending on these factors, the import process may take less than one hour or up to several hours to complete for the entire set of Discoverer baseline .eex files. To minimize overhead from network latency during the import process, it is strongly recommended that Oracle10gAS be installed on a machine that is close to the database server and has a fast network connection.
9.4. Check the logfile after import. This should be a clean import logfile. You should be able to see that the import utility exited with status 0 and the logfile contains the message Import completed successfully.
You can safely ignore the following messages in the import log:
This messages provides information that
You can safely ignore this message using Discoverer 10g. See release notes for more details.
9.5. As Release 11i products continually update and improve support for Discoverer in subsequent releases, it will be necessary to install additional Discoverer loader files after this initial installation. Please refer to Section 9 for details on importing subsequent patches using mode=driver.
10. Refresh the Discoverer 10g End User Layer
After the Discoverer 10g content for Release 11i has been imported into the Discoverer End User Layer on the database tier, the End User Layer must be refreshed against the Release 11i data model. This ensures that all references between business areas, folders, workbooks, business views, flexfields, and Release 11i database objects are valid.
On Unix platforms you may run the refresh process on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed.
Alternatively on Windows platforms you may run the refresh process on the machine where Discoverer Administration Edition is installed.
10.1. Move to the ... /discover/ directory where adrfseul.sh is located.
10.2. Start the refresh process against the database using the shell script adrfseul.sh.
Syntax for the adrfseul.sh script:
% sh adrfseul.sh
connect=
resp=
gwyuid=
fndnam=
secgroup=
eulschema=
eulpassword=
twotask=
[iashome=
[exedir=
[logfile=
[parfile=
Where:
Parameter Description
eulschema Full EUL schema name, for example eulschema=EUL_US
eulpassword EUL schema password.
twotask Database name as specified in your tnsnames.ora file.
iashome Oracle Application Server 10g Business Intelligence node Oracle Home directory
Required on UNIX platforms
exedir Directory where Discoverer executables are located
Required on Windows platforms
logfile Log file where the adupdeul.sh script logs the result of the import process.
Default filename is adrfseul.log
parfile Filename of a command-line parameter file containing entries of the form PARAMETER=VALUE. Parameters specified at the command line take precedence over those specified in the parameter file.
Notes:
All parameters must be specified on a single command line or in a parameter file. Name-value pairs must not contain spaces. For example:
PARAMETER=VALUE is acceptable
PARAMETER = VALUE will generate errors
Example:
cd /user1/au_top/discover (where adrfseul.sh is located)
Start the refresh process of all existing eul folders against the database
sh adrfseul.sh \
connect=sysadmin/sysadmin-password@mydb \
resp="System Administrator" \
gwyuid=APPLSYSPUB/PUB \
fndnam=APPS \
secgroup="Standard" \
eulschema=EUL_US \
eulpassword=EUL123 \
twotask=mydb \
iashome=/oracle/iAS/bi \
logfile=refresh_eul_us_mydb.log
The above example is displayed on multiple lines for clarity; do not type the backslash.
Note
For the refresh process you must use the Applications User/Responsibility SYSADMIN:System Administrator to logon rather than the End User Layer database schema owner. The End User Layer database schema owner doesn't have the necessary grants for the APPS schema and the refresh process would drop Discoverer folders! Using adrfseul.sh for the refresh enforces using Applications User/Responsibility in the connect string.
For all Business Areas expect several hours for the refresh process to complete, depending on your flex structure. Your End User Layer is now refreshed. Flex columns have been pulled in to the folders using the flex prompts in the language of the End User Layer as Item Display Names.
11. Grant Access to Responsibilities, Users, and Workbooks (optional)
Individual Discoverer users may be granted access to specific Release 11i responsibilities, which in turn provide access to Discoverer business areas and workbooks. Either the Discoverer 10g eulapi UNIX command-line utility or Discoverer 10g Administration Edition can be used to associate business area access with Release 11i responsibilities, and to identify workbook access privileges for responsibilities and users.
The procedures described in this step may be used as guidelines for planning your Discoverer reporting security. Your Discoverer reporting strategy may be defined to reflect your corporate data security strategy, policies, and business requirements. Discoverer security is flexible and highly granular, and you may wish to provide broader access for specific individuals who need to be able to see data enterprise-wide, i.e. across organizational boundaries.
Example: You may wish some Financial analysts to have access to all of the Financials Intelligence business areas, such as the Accounts Payables and Accounts Receivables. To do this, grant access to all of the Financials Intelligence business areas to the Financials Intelligence responsibility. The Financials Intelligence responsibility may then be granted to the individual Financial analysts who should have access to those areas.
Note
If you have upgraded to the Oracle Applications 11.5.8 Maintenance Pack or later Maintenance Pack releases, you can skip this step.
The 11.5.8 Maintainance Pack and later Maintenance Pack releases already include all necessary security grants and workbook sharing for the pre-seeded Oracle Applications responsibilities. If you wish to grant security access for Business Areas to Oracle Application users and responsibilities in addition to the grants that are provided with the 11.5.8 Maintenance Pack and higher, you may optionally follow the procedures listed in this step.
11.1 Grant Privileges, Security and Workbook Access on Unix
For information about delivered Business Areas with Oracle E-Business Intelligence Suite please refer to Chapter 4 of the Oracle Business Intelligence System Implementation Guide . You must grant security access to the Oracle Applications responsibilities you would like to use for each Business Area.
You may use the Discoverer 10g eulapi command line utility on the standalone application server where Oracle Application Server 10g and Discoverer 10g were installed in Step 1 to grant privileges, business area security and workbook access.
For help on usage of the eulapi command-line utility, type:
$ORACLE_HOME/bin/eulapi -help grant_privilege
Example 1:
Grant Discoverer User privileges to responsibility "Financials Intelligence"
$ORACLE_HOME/bin/eulapi \
-CONNECT
-GRANT_PRIVILEGE \
-ROLE "Financials Intelligence" \
-PRIVILEGE all_user_privs
Example 2:
Grant security access for business area "General Ledger" to responsibility "Financials Intelligence"
$ORACLE_HOME/bin/eulapi
-CONNECT
-GRANT_PRIVILEGE \
-ROLE "Financials Intelligence" \
-BUSINESS_AREA_ACCESS "General Ledger"
11.2. Grant Security and Privileges using Discoverer Administration Edition on Windows
If you have already granted security and privileges using the Discoverer 10g eulapi command-line utility, you may skip this step.
Log in to the Discoverer Administration Edition 9.0.4 as the Applications User which was granted full Administration Privileges and Security:
Use the Help -> About Discoverer... dialog to make sure you are connected to the correct End User Layer (
Using Tools -> Privileges
Check "User Edition" privileges for all Applications Users and Responsibilities who should be allowed to open workbooks.
Using Tools -> Security
Grant Business Area access to Applications Users and Responsibilities who should be allowed to query the various Business Areas.
11.3 Share Workbooks using Discoverer Plus Edition
You must grant shared workbook access to the Oracle Applications responsibilities to be used for workbook analysis.
1. Log in to the Discoverer User Edition 9.0.4 as the Applications Administration user who imported the eex files in Section 6, Step 8 .
2. Use the Help -> About Discoverer... dialog to confirm that you are connected to the correct End User Layer (
3. Using the File -> Manage Workbooks... -> Sharing... dialog
Share the workbooks to the Applications Users and Responsibilities that should be allowed to open the various workbooks.
11.4. Test Discoverer Responsibilities and Workbooks
Log in to Oracle E-Business Suite Release 11i with a userid that has been granted one of the responsibilities defined above.
Open the Release 11 workbooks assigned to that responsibility and verify that they can retrieve and display data.
Repeat for each responsibility that has been defined.
Congratulations!
You have completed your Discoverer 10g implementation and your Discoverer content for Oracle E-Business Suite Release 11i products is ready for use.
________________________________________
Section 7: Available Documentation
For a summary of installation and configuration documentation for Oracle Application Server 10g and its associated components, see the following Note on Oracle Metalink :
Oracle Application Server (10gAS) with Oracle E-Business Suite Release 11i Documentation (Note 207159.1)
________________________________________
Section 8: Conventions and Important Directory Locations
Conventions
The following typographical conventions are used in this document:
Convention Meaning
\ In examples of commands you type online, a backward slash at the end of a line signifies that you must type the entire command on one line. Do not type the backslash.
Mono space text Represents command line text. Type this text exactly as shown.
<> or [] Text enclosed in angled or square brackets represents a variable. Substitute an appropriate value for the variable text. Do not type the brackets.
Directory Paths Directory paths in this document are relative to the top level installation directory for the Oracle E-Business Suite. e.g. if you installed the Oracle E-Business Suite under a directory named /my/appsinstall and are pointing to an Oracle E-Business Suite Release 11i database named mytestdb, then
Subscribe to:
Posts (Atom)