Monday, July 27, 2009

Form servlet and socket mode in R12

Oracle Applications Release12 by default configures Forms 10g stack in servlet mode, as this is the preferred and recommended deployment model for forms on the web. In this mode a java servlet called the Forms Listener Servlet manages the communication between the Forms Java Client and the OracleAS Forms Services. The Forms Listener Servlet communicates through the HTTP server port and does not need extra ports to handle the communication between the client and the Oracle Applicaiton Server, Forms Services

Although Forms Servlet is the preferred deployment method, there may be circumstances where customers need to switch from Forms Servlet mode to Forms Socket mode, which allows desktop clients to access the Forms Server directly.

also to reduce network traffic. as said above the servlet mode uses http protocol on each transaction between a client and the Forms Server requiring the exchange of cookies and http headers which increases network traffic.

Listener executable name in 10g AS is frmsrv present in $ORACLE_HOME/bin.


How to check if form service is in servlet or socket mode:

To check if it is in servlet mode use from backend:
$ ./adformsctl.sh status

You are running adformsctl.sh version 120.12.12000000.3

Not starting the Forms OC4J instance as Forms connect mode is set to socket.

adformsctl.sh: exiting with status 1


To check it is servlet from front end do,
Log in to Oracle Applications and launch a Forms-based application.
Open the Sun Java Console (from Tools Menu in Internet Explorer).
Check whether the "mode" directive displayed in Sun Java Console when launching forms-based applications, is set to http,native
Direct Forms Servlet Launch is:
web_protocol>://.:/forms/frmservlet


TO check if it is socket mode from backend:
$ ./adformsrvctl.sh status

You are running adformsrvctl.sh version 120.9.12000000.7

Checking status of FORMS Server (Socket Mode) ...

Forms Server (Socket) is running as PID 8352


adformsrvctl.sh: exiting with status 0

adformsrvctl.sh: check the logfile $ADMIN_LOG_HOME/ora/10.1.2/forms/socket.log for more information ...


To check it is socket from front end do,
Log in to Oracle Applications and launch a Forms-based application.
Open the Sun Java Console (from Tools Menu in Internet Explorer).
Check whether the "mode" directive displayed in Sun Java Console when launching forms-based applications is set to socket.
The direct launch URL for Forms Socket Mode is:
://.:/OA_HTML/frmservlet



Manually Starting/stoping/status of Forms Server in socket mode:


$INST_TOP/admin/scripts/adformsrvctl.sh start
$INST_TOP/admin/scripts/adformsrvctl.sh stop
$INST_TOP/admin/scripts/adformsrvctl.sh status

Step by steps to convert to socket mode:

1.Source the environment on the application tier.

2.Stop all the application tier services:

$INST_TOP/admin/scripts/adstpall.sh

3.Run the following command to enable Forms Socket Mode:

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \
[-contextfile=] \
-mode=socket \
[-port=] \
-runautoconfig= \
-appspass=

Parameter Description
-contextfile Full path to application tier context file, using the syntax:
On UNIX:

$INST_TOP/appl/admin/.xml

-mode servlet - to enable Forms Servlet mode. This is the default value.

socket - to enable Forms Socket mode
-port Port number used to run Forms in socket mode. The default port number is 9095.

A port number is not needed if servlet mode is used.

-runautoconfig Specify whether AutoConfig should be run after changing the forms mode. Possible values are:

No - Do not run AutoConfig after enabling Forms servlet/socket mode. This is the recommended value.
Yes - Run AutoConfig after enabling Forms servlet/socket mode.

-appspass Password for the applications user. Required only if -runautoconfig=Yes.


For example, on UNIX:

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \
-contextfile=$INST_TOP/appl/admin/mycontext.xml \
-mode=socket \
-port=9095 \
-runautoconfig=No
The script prompts for the parameters if they are not passed as command line arguments.


one more eg:
$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode -contextfile=$INST_TOP/appl/admin/.xml -mode=socket -port=9001
-runautoconfig=Yes -appspass=


Note:If Autoconfig was not automatically executed as part of the preceding step, then run AutoConfig on the application tier:
$INST_TOP/admin/scripts/adautocfg.sh


4.Start all the application tier services:

$INST_TOP/admin/scripts/adstrtal.sh

5.Check whether Forms Server is running:
On UNIX:
$INST_TOP/admin/scripts/adformsrvctl.sh status

Log in to Oracle Applications and launch a Forms-based application.
Open the Sun Java Console (from Tools Menu in Internet Explorer).
Check whether the "mode" directive displayed in Sun Java Console when launching forms-based applications is set to socket.
The direct launch URL for Forms Socket Mode is:
://.:/OA_HTML/frmservlet




------------------------------------------------------------------------------------------------------------------

Step by steps to convert to servlet mode:


Carry out these steps to reenable Forms Servlet mode if Socket Mode is no longer required:

1.Source the environment on the application tier.

2.Stop all the application tier services:

$INST_TOP/admin/scripts/adstpall.sh

3. Run the following script to disable Forms Socket Mode and re-enable Forms Servlet Mode:

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \
[-contextfile=] \
[-mode=servlet] \
-runautoconfig= \
-appspass=
Parameter Description
-contextfile Full path to application tier context file, using the syntax:
On UNIX:

$INST_TOP/appl/admin/.xml

-mode servlet - to enable Forms Servlet mode. This is the default value.

socket - to enable Forms Socket mode
-port Port number used to run Forms in socket mode. The default port number is 9095.

A port number is not needed if servlet mode is used.

-runautoconfig Specify whether AutoConfig should be run after changing the forms mode. Possible values are:

No - Do not run AutoConfig after enabling Forms servlet/socket mode. This is the recommended value.

Yes - Run AutoConfig after enabling Forms servlet/socket mode.

-appspass Password for the applications user. Required only if -runautoconfig=Yes.


For example, on UNIX:

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \
-contextfile=$INST_TOP/appl/admin/mycontext.xml \
-mode=servlet \
-runautoconfig=No



If Autoconfig was not automatically executed as part of the preceding step, then run AutoConfig on the application tier:
On UNIX:

$INST_TOP/admin/scripts/adautocfg.sh


4.Start all the application tier services:

$INST_TOP/admin/scripts/adstrtal.sh

5.Log in to Oracle Applications and launch a Forms-based application.
Open the Sun Java Console (from Tools Menu in Internet Explorer).
Check whether the "mode" directive displayed in Sun Java Console when launching forms-based applications, is set to http,native

Direct Forms Servlet Launch is:
web_protocol>://.:/forms/frmservlet


Manually Starting Forms Server
On UNIX:

$INST_TOP/admin/scripts/adformsrvctl.sh start
Step 4.2: Manually Stopping Forms Server
On UNIX:

$INST_TOP/admin/scripts/adformsrvctl.sh stop
Step 4.3: Checking Status of Forms Server
On UNIX:

$INST_TOP/admin/scripts/adformsrvctl.sh status



Reference:384241.1

Thursday, July 23, 2009

All information on Gather Stats and more on performance issues

Introduction:

Prior to Release 11i, Oracle Applications have defaulted to use the Rule-Based Optimizer.
Oracle's Applications development team has reviewed SQL statements in 11i to tune them for use by the Cost-Based Optimizer (CBO).
CBO uses statistics to decide the execution of SQL statements.
FND_STATS is a vital part of collecting this data.

CBO stands for Cost Based Optimization. The R11i application was written to
use the CBO. It determines the most efficient way to execute a SQL statement
based on statistical data stored in the data dictionaries. These stored
statistics are used to compute the cost of executing the SQL. The execution
plan with the lowest cost is the one chosen by the optimizer.


References:
419728.1 How To Gather Statistics On Oracle Applications 11.5.10 - Concurrent Process,Temp Tables, Manually
232419.1 How to Troubleshoot Performance Issues
113653.1 OM-PERF: Performance Tuning - Setup
113573.1 OM-PERF: Performance Tuning - FAQ
744143.1 Tuning performance on eBusiness suite
156968.1 --> coe_stats.sql - Automates CBO Stats Gathering using FND_STATS and Table sizes
170647.1 Does Gather Schema Statistics collect statistics for indexes?
427878.1 Which Method To Gather Statistics When On DB 10g
Note 244040.1 - Oracle E-Business Suite Recommended Performance Patches
Note 122371.1 - How to gather statistics for Oracle Applications
Note 116571.1 - Cost Based Optimiser and Applications Release 11i
Note 117363.1 - Analysing 11i Tables for Performance
Note 102334.1 - How to automate ANALYZE TABLE when changes occur on tables
Note 114671.1 - Gathering Statistics for the Cost Based Optimizer
Note 116178.1 - Analyze Command - Compute Statistics vs. Estimate Statistics
Verifying Statistics
============
Note 163208.1 bde_last_analyzed.sql - Verifies CBO Statistics
Automatic Statistic Gathering
====================
Note 377152.1 Best Practices for automatic statistics collection on 10g
Note 465787.1 Managing CBO Stats during an upgrade to 10g or 11g
368252.1 EBPERF FAQ - Collecting Statistics with Oracle Apps 11i
394371.1 adpatch performance is slow
Note 370583.1 - Basic troubleshooting of JVM consuming cpu or too many JDBC connections in Apps 11i
Note 304099.1 - Using J2SE Version 5.0 with Oracle E-Business Suite 11i
Note 401561.1 - Using J2SE Version 6 with Oracle E-Business Suite 11i


The Rule Based Optimizer (RBO) uses a fixed ranking system to determine the
most efficient access method when executing a SQL statement. This method is
not as flexible as CBO; which takes into consideration the data distribution
characteristics of the database. CBO also offers a number of enhancements
which are not available with RBO.


RBO still exists. By default the 11i apps will use CBO. However, by
the use of hints, RBO can still be invoked.

Statistics must be gathered for Application Releases; 10.7, 11.0 and 11i.
Applications 11i must use FND_STATS. Applications 10.7 and 11.0 can use any method


ANALYZE Command - Compute Statistics vs. Estimate Statistics

COMPUTE STATISTICS

When computing statistics, an entire object is scanned to
gather data about the object. This data is used by Oracle
to compute exact statistics about the object. Slight variances
throughout the object are accounted for in these computed
statistics. Because an entire object is scanned to gather
information for computed statistics, the larger the size of
an object, the more work that is required to gather the necessary
information.

ESTIMATE STATISTICS

When estimating statistics, Oracle gathers representative
information from portions of an object. This subset of information
provides reasonable, estimated statistics about the object. The
accuracy of estimated statistics depends upon how representative
the sampling used by Oracle is. Only parts of an object are
scanned to gather information for estimated statistics, so an
object can be analyzed quickly. You can optionally specify the
number or percentage of rows that Oracle should use in making the
estimate.

Should I gather statistics for SYS schema?

- On 8i, never gather stats for the SYS schema, because it is not supported or recommended.
- On 9i, gathering stats for SYS schema is supported, but not necessary.
- On 10g, it is mandatory to collect stats for SYS schema.


How to collect statistics for SYS schema ?

You must have the SYSDBA (or both ANALYZE ANY DICTIONARY and ANALYZE ANY) system privilege to execute this procedure.
execute dbms_stats.gather_schema_stats(''SYS'', method_opt=>''for all columns size 1'', degree=>30,estimate_percent=>100,cascade=>true);

If you are using Database version 10G, you can use the following syntax instead
execute dbms_stats.gather_dictionary_stats ();
this procedure will gather statistics for all system schema's, including SYS and SYSTEM, and other optional schema's, such as CTXSYS and DRSYS.
There are no eBusiness Suite specific guidelines on how often these statistics need to be refreshed.
Normally, you would need to re-gather stats on those objects if there is a significant change in the environment (a lot of DB users created, a lot of new objects created or rebuilt, etc).

How do I collect statistics for fixed objects (V$ views) in 10G databases?

Run the following command during a typical workload
dbms_stats.gather_fixed_objects_stats;
Note: the script $APPL_TOP/admin/adstats.sql requires the Database to be in restricted mode and could be used only when running upgrades. For optimal performance, statistics on fixed objects should be gathered when there is a typical load in the system.
There are no eBusiness Suite specific guidelines on how often these statistics need to be refreshed
Normally, you would need to re-gather stats on fixed objects after upgrades or if the workload changes.
For additional guidelines, refer to
Note 465787.1 Managing CBO Stats during an upgrade to 10g or 11g





Checks:
1.Run the Verify Stats report to confirm that statistics are current.

This report is a utility provided with FND_STATS, and can be run as follows:

SQL> set server output on
SQL> set long 10000
SQL> exec FND_STATS.VERIFY_STATS(OM, OE_ORDER_HEADERS_ALL)

This procedure takes a schema name and/or a list of comma separated
table_names and checks the stats on all these objects and spools out a
report. Some important columns to look at are LAST_ANALYZED and NUM_ROWS.

2. Ensure that the database initialization parameters which affect CBO are set
up correctly.

Run the following script to verify the init.ora parameters are correct:

$FND_TOP/sql/AFCHKCBO.sql

The output of this report shows the customer's current settings vs. the
recommended settings for CBO.

3. When slow performance is encountered check that the profile
options OM: Debug is set to 'No' and OM: Debug Level is 'null'.
If these profile options are set to a value of 'yes' for
OM: Debug or other than NULL for OM: Debug Level, then system
performance maybe adversely affected.


What to do?
If you use Oracle Applications, it is recommended that you run the concurrent programs for gathering statistics

To run concurrent program Gather Schema Statistics:
1. Log on to Oracle Applications with
Responsibility = System Administrator
2. Submit Request Window
Navigate to: List > Request > Run.
3. Enter the appropriate parameters. This can be run for specific
schemas by specifying the schema name or entering 'ALL' to gather
statistics for every schema in the database.
4. Submit the gather schema statistics program.


The following concurrent requests are available in Oracle Applications for gathering statistics:
Gather All Column Statistics
Gather Column Statistics
Gather Schema Statistics
Gather Table Statistics
For Oracle Applications 11i it is recommended to use only the 'Gather Schema Statistics' or the 'Gather Table Statistics'. Rest are not supported, and results in sub-optimal plans.

When you gather statistics for a table or a whole schema, it cascades down and gathers also the statistics for all indexes on each table and all columns for that particular table or schema.

Gather Schema Statistics Concurrent Request calls Gather_Schema_Stats as part of FND_STATS package.

The package specification and package body for FND_STATS are located in
$FND_TOP/admin/sql/ AFSTATSS.pls and AFSTATSB.pls

AFSTATSB.pls:
--------------
FND_STATS.Gather_Schema_Stats calls
FND_STATS.GATHER_TABLE_STATS with cascade = TRUE for each schema calls
DBMS_STATS.GATHER_TABLE_STATS with cascade_true

Cascade option of GATHER_TABLE_STATS:
--------------------------------------
Running procedure DBMS_STATS.GATHER_TABLE_STATS with parameter cascade set to
the non-default value TRUE means that indexes for the table are analyzed.

Using the cascade option is equivalent to running the gather_index_stats
procedure on each of the table's indexes, however, Applications customers are
always recommended to use the Gather Schema Statistics concurrent request
rather than analyze Apps objects manually.


parameters:
· Schemaname
You may enter ALL to analyze every defined App schema.

· Estimate_percent
Percentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similiar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.

· Degree
Enter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers). Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make Full Table Scans look less attractive.

· Backup Flag
If the value is 'NOBACKUP' then it won't take a backup of the current statistics and should run quicker. If the value is 'BACKUP' then it does an export_table_stats prior to gathering the statistics.

· Restart Request Id
Enter the request id that should be used for recovering gather_schema_stats if this request should fail. You may leave
this parameter null.

· Gather Options
As of 11.5.10, FND_STATS.GATHER_SCHEMA_STATS introduced a new parameter called OPTIONS that, if set to GATHER AUTO, allows FND_STATS to automatically determine the tables for which statistics should be gathered based on the change threshold. The Modifications Threshold can be adjusted by the user by passing a value for modpercent, which by default is equal to 10. GATHER AUTO uses a database feature called Table Monitoring, which needs to be enabled for all the tables. A procedure called ENABLE_SCHEMA_MONITORING has been provided to enable monitoring on all tables for a given schema or all Applications schemas .


Manual Execution
=============
In R11i customers should be using the FND_STATS command.
Do not use the ANALYZE command or DBMS_STATS package directly,
as doing so may cause incomplete statistics to be generated.

Use the following command to gather schema statistics:
exec fnd_stats.gather_schema_statistics('ONT') < For a specific schema >
exec fnd_stats.gather_schema_statistics('ALL') < For all schemas >

Use the following command for gathering statistics on a temporary table
(ie: temporary tablename = TEMP_tmp in schema ABC):
exec fnd_stats.gather_table_stats('ABC','TEMP_tmp');

# sqlplus apps/
SQL> exec fnd_stats.gather_schema_statistics('MRP'); <- One schema
SQL> exec fnd_stats.gather_schema_statistics('ALL'); <- All schemas
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES'); <- One table



Troubleshooting tips:
1.Ensure the initialization parameters for eBusiness suite are set correctly.

It can be checked by using bde_chk_cbo.sq l .Then you verify the result with one of the following two notes :

Note 216205.1 Database Initialization Parameters for Oracle Applications 11i

Note 396009.1 Database Initialization Parameters for Oracle Applications Release 12

2.Make sure Gather Shema Stats is running on periodical basis. It can be checked with bde_last_analyzed.sql (Note 163208.1) which verifies stats by schema and index.


3.For 10g Database users, we recommend to enable ASMM(Automatic shared memory management). This puts Oracle in control of allocating memory within the SGA. The SGA_TARGET parameter sets the amount of memory available to the SGA. This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE parameter value. Provided the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0" Oracle will control the memory pools which would otherwise be controlled by the following parameters:

DB_CACHE_SIZE (default block size)
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE


4.If all the above settings are fine, and still you face slow performance, then either AWR or Statspack report is must checking.

-For10g Database users, refer to Note 276103.1 PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY .
-For 9i Database users, refer to Note 94224.1 Title: FAQ- Statspack Complete Reference

Best practices for tuning performance
Database tier

Refer to Recommended Performance Patches for the Oracle E-Business Suite.
There are several optimizer related patches that need to be applied on top of 10.2.0.(2/3). Refer to Note 244040.1 for an updated list.
Convert to the OATM Tablespace Model for the EBusiness Suite. See Note 404954.1 How to run OATM migration utility.
Applications tier

Deploy with socket mode for internal users: R12: Refer to Note 384241.1.
Enable Forms Dead Client Detection
Value specified in minutes : FORMS_TIMEOUT=10
• Terminates fwebmx processes for dead clients.
• Enable Forms Abnormal Termination Handle
Do not set FORMS_CATCHTERM
Disable Cancel Query
• Cancel Query increases middle-tier CPU as well as DB CPU
• Refer to MetaLink Note 138159.1 on how to enable and tune Cancel query related parameters
• To Disable Cancel Query : Set the Profile “FND: Enable Cancel Query” to ‘No’
For tuning JVM/OC4J Refer to Note 362851.1:Guidelines to setup the JVM in Apps 11i
Use one JVM per 2 CPUs
• No more than one JVM/CPU
• No more than 100 concurrent users per JVM



Tracing The Performance of a Process:
1. Navigate to SQLPLUS.
2. At the SQL prompt, type the following:

SQL> alter system set timed_statistics = true;

Whenever troubleshooting an application performance problem, it is a good idea
to collect timed_statistics to determine which piece of code is taking the
longest amount of time to execute. Before running a trace, make sure that
timed_statistics is turned on at the database level. This will in no way affect
performance for the users that are currently using the database.

1. Form Trace:
To turn
trace on at the form level, go to the toolbar and navigate to
HELP -> TOOLS -> TRACE. By clicking on the word trace, a checkmark will appear
next to it, indicating that trace has been turned on. Reproduce the problem.
Navigate to HELP -> TOOLS -> TRACE, and click on the word trace to uncheck
trace and turn it off.

2.SQL Trace
Some applications now have profiles that allow you to turn trace
on within the application. These profiles are usually updateable at
the user level and can be turned on just for the user who is trying
to trace a process. By turning trace on with the use of one of these
profile options, you are effectively doing the same thing as setting
SQL_TRACE=TRUE in the init.ora, but you DO NOT have to bounce the
database. See the below list of current Trace Profile Options:

AX: Trace Mode
Debug: Trace Level for Danish
INV: Debug Trace
MRP: Trace Mode
OE: Debug Trace
Utilities: SQL Trace

If you do not have the luxury of a profile option to turn SQL trace
on, you can also turn trace on by typing the following at the SQL
prompt:

SQL> alter session set sql_trace = true;

After setting SQL Trace to true, run the process and then turn off trace
when you are done:

SQL> alter session set sql_trace = false;

3.tkprof:

tkprof sys=no explain=apps/
sort='(prsela,exeela,fchela)'

The above mentioned command sorts the SQL by placing the worst performing
SQL statement at the top of the output file

Upgrade compatibility

To answer to questions while planning for upgrade such as

My current DB and Apps versions are so and so, I need to upgrade DB and Apps to so and so latest versions,
So is direct upgrade possible?
How should I split the upgrade process ?
I want only DB upgrade, will it be compatible with my Current apps version ?
I want only Apps upgrade, will it be compatible with my current DB version ?


Read below to get an answer:

oracle database upgrade guide at http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007718
–362203.1 Oracle Applications Release 11i with Oracle 10g Release 2
–216205.1 Database Initialization Parameters for Oracle Applications 11i


10G upgrade :

Base release of 10gR2 is 10.2.0.1 so first you have to Install 10.2.0.1 & then apply patchset 10.2.0.2 which is certified to work with Apps 11i (11.5.9 & 11.5.10)

–You can download 10.2.0.1 software from http://www.oracle.com/technology/software/products/database/oracle10g/index.html
You need companion CD as well so download companion CD as well from above location.
–10.2.0.2 is available as patch 4547817 which you can download from http://metalink.oracle.com or updates.oracle.com


You can upgrade directly to 10.2.0.1 from following database version
8.1.7.4 , 9.2.0.4 or higher, 10.1.0.2 or higher


Database upgrade is Two Step process:

1.upgrade software from 8i, 9i or 10.1 to 10.2
Install companion CD separately ( Ultra Search )
apply patcheset 10.2.0.2 check ReadMe of patch 4547817 via OUI


2.Upgrade database Metadata ( via executing sql’s or dbua )

Complete Checklist for Manual Upgrades to 10gR2 316889.1

Now apart from upgrading database there are some pre upgrade & post upgrade steps specific to oracle applications 11i & follow them as mentioned in metalink note # 362203.1

Upgrade to 9.2.0.8 from note: 216550.1



Apps upgrade to support 10G:

316365.1 Oracle Applications Release 11.5.10.2 Maintenance Pack Installation Instructions (Use this to upgrade from 11i i.e. 11.5.5,6,7,8,9 to 11.5.10.2)

Upgrade of Oracle Application to 11.5.10.2 from previous release of 11i (11.5.5 to 11.5.9) should be done by applying maintenance pack i.e. Patch # 3480000 and few other prereq. & post patch steps. Follow Metalink Note # 316365.1

Your application should be on AD minipack I version 2 i.e. 11.AD.I.2 which is available via patch # 4337683 so first apply patch 4337683. (If you are not sure if you already have AD.I.2 or not query AD_BUGS table)

If you are planning to merge prereq. patches, NLS patches and other functional patches with 11.5.10.2 (3480000), note that AD.I.2 4337683 should not be merged as it is not possible to merge AD patches.

If you are already on 11.5.10 and wish to upgrade to 11.5.10.2, simply apply patch u3480000.drv using adpatch utility (316366.1)


316366.1 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2) (Use this to upgrade from 11.5.10 to 11.5.10.2)


289788.1 Upgrading Oracle Applications (Use this doc to upgrade to 11.5.10.2 from 10.7 or 11.0.3)

Sunday, July 19, 2009

OraInventory Information:

The inventory is a very important part of the Oracle Universal Installer. This is where OUI keeps all information

regarding the products installed on a specific machine.

There are two inventories with the newer releases of OUI (2.x and higher):

The inventory in the ORACLE_HOME (Local Inventory)
The central inventory directory outside the ORACLE_HOME (Global Inventory)

At startup, the Oracle Universal Installer first looks for the key (oraInst.loc) that specifies where the global

inventory is located at


this key is platform dependant


/var/opt/oracle/oraInst.loc ( Solaris )

/etc/oraInst.loc ( AIX and Linux )

HKEY_LOCAL_MACHINE -> Software -> Oracle -> INST_LOC ( Windows )

If this key is found, the directory within it will be used as the global inventory location.

If the key is not found, the inventory path will default created as follows:

UNIX : $­ORACLE_BASEoraInventory
WINDOWS : c:program filesoracleInventory

If the ORACLE_BASE environment variable is not defined, the inventory is created at the same level as the first

Oracle home.
For example, if your first Oracle home is at /export/app/oracle/ORACLEHome1, then,
the inventory is at /export/app/oracle/oraInventory



The Oracle Universal Installer inventory is the location for the Oracle Universal Installer's bookkeeping.

The inventory stores information about:
All Oracle software products installed in all Oracle homes on a machine
Other non-ORACLE_HOME specific products, such as the Java Runtime Environment (JRE)

Starting with Oracle Universal Installer 2.1, the information in the Oracle Universal Installer inventory is stored

in Extensible Markup Language (XML) format.
The XML format allows for easier diagnosis of problems and faster loading of data.
Any secure information is not stored directly in the inventory. As a result, during deinstallation of some products,

you may be prompted for required secure information, such as passwords.


Local Inventory

There is one Local Inventory per ORACLE_HOME.
It is physically located inside the ORACLE_HOME at $­ORACLE_HOME/inventory
and contains the detail of the patch level for that ORACLE_HOME.

The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.

If the Local Inventory becomes corrupt or is lost, this is very difficult to recover, and may result in having to

reinstall the ORACLE_HOME and re-apply all patchsets and patches.


Global Inventory

The Global Inventory is the part of the XML inventory that contains the high level list of all oracle products

installed on a machine.
There should therefore be only one per machine.
Its location is defined by the content of oraInst.loc.
The Global Inventory records the physical location of Oracle products installed on the machine,
such as ORACLE_HOMES (RDBMS and IAS) or JRE.
It does not have any information about the detail of patches applied to each ORACLE_HOMEs.
The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through

OUI Installer, Rapid Install, or Rapid Clone.

Note: If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the

Global Inventory synchronized.

OUI 2.x (11.5.7 and later):

Global inventory becomes a list of pointers to each Local Inventory
Global and local inventories are in text based XML format
XML conversion is validated and performed if needed by Rapid Clone

Prior to 2.x ( 11.5.5 or aerlier )
Global inventory was in binary format
Global inventory contained all software install information
No local inventories in ORACLE_HOMEs



oraInst.loc file may be in different locations, depending on system platform:

/var/opt/oracle/oraInst.loc file
or
/etc/oraInst.loc

---file contents---
inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall
---file contents---


Required permissions for oraInst.loc

chmod 644 /var/opt/oracle/oraInst.loc



oraInst.loc contain path information to the Oracle central(also called global) Inventory directory and
group of the software owner which can be dba or oinstll as created on the system:
That is why group name should be same eg: dba for database user eg: oracle and application user eg: applmgr
as application side oracle_homes are alos registered in Global inventory.

Example:
inventory_loc=/app/oracle/product/10.2/oraInventory
inst_group=dba


Global Inventory holds information about Oracle Products on a Machine and local inventory withing the software home

hold information about that software.

To recreate lobal oraInventory content if corrrupted or missing can be done suing runInstaller or from perl script

OUIsetup.pl as shown below:

From runInstaller:

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -attachHome ORACLE_HOME="/u01/oracle/product/10.1.2" ORACLE_HOME_NAME="Ora10gHome"


Note: The information of ORACLE_HOME and its name to be attached in central inventory can be obtained from the

inventory.xml file present withing local inventory.


After this check if inventory information can be listed with below command:

$ORACLE_HOME/OPatch/opatch lsinventory -detail


Also can do these verifications,

View the file /ContentsXML/inventory.xml
The ORACLE_HOME_NAME will be mentioned against the location of ORACLE_HOME

For Example
HOME NAME="OraDB10g_Home" LOC="/u01/home/oracle/product/10.2/db_1" TYPE="O" IDX="1"

Here the ORACLE_HOME_NAME is OraDB10g_Home


Invoke the runInstaller inside $ORACLE_HOME/oui/bin
Select Installed Products

1. Contents
The products (ORACLE_HOME_NAME) installed will be displayed
Put a check mark on the ORACLE_HOME_NAME and its location will be displayed

2. Environment
The installed ORACLE_HOME_NAME along with Home Path will be displayed.


To create global invetory from perl script OUIsetup.pl:

. Download Patch 5035661 (OUI 2.2) (Or Later) from Metalink.

Note:
Do not follow the readme.txt file for this patch.
Just follow the instructions on this Note.

Recreate the Global oraInventory on ALL the ORACLE_HOMEs as follows :

1) iAS ORACLE_HOME :

a. Unzip the patch into the directory
ie: unzip -od p5035661_11i_LINUX.zip

b. Source the Apps environment file (/APPSORA.env).
If the environment file is not available, please make sure
the environment variable 'APPL_TOP' is set.

c. Change directory to the /appsoui/setup

d. Execute the perl script OUIsetup.pl:
perl OUIsetup.pl

This command, will create a new oraInventory (on the location where
the oraInst.loc is pointing to) and will add one entry on the file
oraInventory/ContentsXML/inventory.xml for the iAS ORACLE_HOME
just registered.

e. Give all the Permissions to the new oraInventory create in order to
prevent permission problems that might occur during the registration
of the the next ORACLE_HOME (ie: 8i or 9i).

For Example :
cd /
chmod -R 777 oraInventory

2) RDBMS ORACLE_HOME (8i or 9i)

a. Unzip the patch into the directory
ie: unzip -od p5035661_11i_LINUX.zip

b. Source the DB environment file (/_.env)
If the environment file is not available, please make sure
the environment variable 'ORACLE_SID' is set.

c. Change directory to the /appsoui/setup

d. Execute the perl script OUIsetup.pl:
perl OUIsetup.pl

This command, will use the same oraInventory created on Step 4.1, and
will add another entry on the file oraInventory/ContentsXML/inventory.xml
for the RDBMS (8i or 9i) ORACLE_HOME just registered.


Verify oraInventory registration :

- Set the DISPLAY correctly

- Go to the directory $ORACLE_HOME/appsoui/oui/install

- Execute the command : runInstaller.sh

- Click on the button 'Installed Products'

- If you are able to see all the ORACLE_HOMEs and the products installed
on each one, then the Registration was succesfull.



If the Local Inventory is missing or corrupted, then a valid workaround is to
Re-Install the Techstack by using 'rapidwiz -techstack'.
That procedure will create a a New $ORACLE_HOME (9i or iAS) containing a valid
Local Inventory.

For detailed steps on that procedure, refer to the following Metalink notes:
Note 289786.1 : 11.5.10 Oracle applications Installation Manual' (Chapter 5, Page 5-1) and
Note 146468.1 : Installing Oracle9i Application Server 1.0.2.2.2 with Oracle Applications

Re-install would be easier.
Take cold backup.
remove the inventory pointer file.
remove the /xxx/app/oracle/product/9.2.0/* files and directories (save
tnsnames.ora, sqlnet.ora, and listener.ora if they are in normal position and see $ORACLE_HOME/cpu/. for which cpu

pathces were applied and $ORACLE_HOME/.patch_storage for which opatches are applied).

setenv ORACLE_HOME, ORACLE _SID, etc. and install, then apply patchset and CPU's to bring it back to where it was.