Script to kill Inactive forms sessions

Lots of time, due to user training issues, users leave their forms sessions open and do not end thier sessions. This can cause lots of issues as sometime even querying in a forms sessions places a lock (I know of Order Entry forms which used to behave like that) and as DBAs you will end up clearing blocking sessions in the database.

Oracle apps has timeout profiles (e.g. ICX Session Timeout) options and confirguration options (Apache timeouts etc) , but most of them do not end the database session. Following script checks the forms sessions in the database and kill the database and their corresponding forms (f60webmx) sessions, which were “INACTIVE” and idle (LAST_CALL_ET) for more than 1 hour.

This works for all types of configurations, whether single node or multi-node. For multi-node the requirement is to enable remsh (remote shell), otherwise change the script to use ssh (for more secure option). It also create log and appends to it and send e-mail if there are any sessions that needs to be killed.

Click here to download script

How to force hints using Outlines

Oracle introduced plan stability in form of Outlines in version 8i. However I have always been intrigued as to how to force a hint to a query using outline. This is particularly useful for Apps DBA, as sometimes it is not possible to change the query and put the hint in it, e.g.
If a query is being fired from some executable which cannnot be edited, like INCOIN for Item Import in Oracle Apps.

Also, often, changing sql will lead to invalidations and will require recompilation which means we may need downtime, e.g. if you want to force hint to a query which is being fired from package, you will have to change the package and recompile it and its dependencies.

If you want to force a hint to a query in a running production system and do not have downtime to do it, here is an example how you can achieve it:

Note: Always make sure you get the exact sql from the shared_pool. Outlines are very sensitive and even one blank space will invalidate the outlines.

1. Take the sql from the shared_pool. (Use hash_value in 9i or sql_id in 10g).
e.g. The following sql is in shared_pool:
select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2

2. This sql sometimes uses index AD_PARALLEL_UPDATE_UNITS_U1 and you have to force use of AD_PARALLEL_UPDATE_UNITS_N1 by using hints….

select /*+ index( ad_parallel_update_units AD_PARALLEL_UPDATE_UNITS_N1FIRST_ROWS) +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2

3. Create an outline on the original sql:
CREATE OR REPLACE OUTLINE ADPARALLEL_ORIG
select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2;

4. Create an outline for the hinted sql
CREATE OR REPLACE OUTLINE ADPARALLEL_HINT
select /*+ index( ad_parallel_update_units AD_PARALLEL_UPDATE_UNITS_N1) FIRST_ROWS) +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2;

5. Update the OUTLN.OL$HINTS table to replace the outline of orig sql with the “hinted” sql:
UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,’ADPARALLEL_HINT’,'ADPARALLEL_ORIG’,'ADPARALLEL_ORIG’,'ADPARALLEL_HINT’)
WHERE OL_NAME IN (‘ADPARALLEL_ORIG’,'ADPARALLEL_HINT’);
commit;

6. You can drop the “hinted” sql at this point:
DROP OUTLINE ADPARALLEL_HINT

7. At this point if the sql: select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2
is parsed again, it will use the outline and will be forced to use index AD_PARALLEL_UPDATE_UNITS_N1, no matter what. You have to make sure that the cursor for this sql is invalidated and query is parsed again, otherwise database will continue using the old plan, which was cached before until next hard parse of this sql. One way to ensure hard parse is to flush shared pool:
alter system flush shared_pool.
However you will have to be careful as this will invalidate all the cursors and will cause hard parsing for all the sqls that are being fired in the database.

8. You can also monitor if the query is using outline or not by running following sqls:

1. select name,used from dba_outlines where name=’ADPARALLEL_ORIG’
The column “USED” will show value of ‘USED’ or ‘UNUSED’ accordingly.

2. Select sql_text,outline_category from v$sql where hash_value=’Your hash value’ — For 9i
Select sql_text,outline_category from v$sql where sql_id=’Your sql id’ — For 10g

The column “OUTLINE_CATEFORY” will show some value e.g. DEFAULT in our case as we created outline in default category.
If the value is null then outlines are not being used. This is the most fool proof and quick way of checking whether the current sql in the sql area is using the outline or not.

3. Run event 10053 for the sql. Event 10053 traces the CBO Computations. (refer metalink note#225598.1)

1. alter session set events =’10053 trace name context forever, level 1;
2. then run explain for the sql e.g.
explain plan for
select /*+ FIRST_ROWS +*/ * from ad_parallel_update_units where update_id = :b1 and status = :b2

A trace will be generated in your udump directory and look for words like outline in it. In 10g you will see something like:
Outline Data:
/*+
BEGIN_OUTLINE_DATA…..

Note: There is a funny problem until 9i. In order to enable usage of outlines in database you have to alter session or system to enable “use_stored_outlines = {TRUE|category|FALSE}” e.g. to Enable usage of “Default outline category” system-wide you will have to :
ater system set use_stored_outlines = true;
However during next restart of database this value will be lost and you will stop using outline that you have created above. One solution is to create system level “on database startup” trigger and run alter system command in it. This problem is not seen in 10g.

References:Note:92202.1

Oracle Applications Upgrade – An Overview

For quite sometime now Oracle apps customers have been trying to upgrade their apps to latest version 11i (or Release 12 now). In this blog I will talk very basic about the upgrade process. How the upgrade process actually works. This is based on my experience with upgrade projects.

Some facts about the instance that was upgraded:
1. Database size before upgrade: 800 GB
2. Database version before upgrade: 8.1.7.4
3. Apps version before upgrade : 11.0.3
4. OS version used during upgrade: HP-UX PA Risc 11.23 (11i v2) with 32 CPUs.
5. Hardware used during upgrade: HP-UX PA Risc Superdome with 32 CPUs, 80 GB RAM
6. SGA size 12 GB (7.5 GB Buffer Cache, 2.7 GB Shared Pool)
7. Database size after upgrade: 1.3 TB
8. Database version after upgrade: 10.2.0.2
9. Apps version after upgrade: 11.5.10.2
10. This upgrade did not include migrating to OATM.

In general following are the technical tasks that need to be done to upgrade 11.5.10.2. (This post does not address upgrade to release 12). The complete details as to how to accomplish and upgrade can be found in the Oracle apps upgrade manual. Oracle Applications 11i (11.5.10.2) Docs

1. Download “The Upgrade Manual Script” TUMS from metalink. TUMS comes as a patch applied using adpatch. This provides a script adtums.sql and also load some objects in db that the script uses to examine your installation. The script reports the list of steps that do not apply to your instance.

Note: If you see the upgrade manual, you will find that Oracle has categorized the various steps involved in the upgrade starting from category 1 through 7, where category 1 and 2 are pre-upgrade steps, category 3 the upgrade and rest post upgrade steps.

3. Download or get the new 11i CDs(11.5.10.2 are the latest) and stage them. Make new mount points for 11i application tier in your application tier and for 9i ORACLE_HOME on database tier. If you have-or-is planning to use multi-node architecture, setup the new mount points on the machine which is your admin-tier. Rest of the tier can be cloned later on for sake of simplicity. Follow instructions in Chapter 4 of installation manual of Oracle apps Installation Guide
For an upgrade, Rapid Install installs an Oracle9i Release 2 RDBMS Oracle home without
a database. You will use this Oracle home to upgrade or migrate your existing database
to Oracle9i.

4. Under category-2 steps, run adpatch under newly laid application tier (11i) and install latest upgrade patches in pre-install mode. e.g. for this upgrade we applied 3993353.

5. Under category-3 steps, upgrade your database to 9i release2 and run autoupgrade (executable name is adaimgr from new 11i application tier). Autoupgrade (formaly know as autoinstall) is the ad utility that upgrades your database code and objects from 10.7 or 11 to base 11i (11.5.0). This is a time consuming step. At this point you have upgraded your instance from 10.7 or 11 to base 11i (11.5.0)

6. Under category-4 post upgrade step. Apply the latest AD minipack e.g. for this upgrade we applied:AD.I.4 (4712852) with adpatch options=nogenerateportion,hotpatch

7. At this point undet category-4 step only, apply 11.5.10.2 maintenane pack udriver which can be found under $AU_TOP/patch/115/driver with following options: adpatch options=nocopyportion,nogenerateportion.
This will bring your database code and objects from base 11i (11.5.0) level to 11.5.10.2 MP level.

8. At this point you have upgraded the database to 9i release 2 and application to 11.5.10.2. Rest of the steps like, installing online help patch, install additional patches for NLS, licensing new products etc… can be done now. After this you will have to again run Rapid-install from the stage area and run it to configure and start the application servers. Follow instructions in Chapter-4 paragraph “Conguring and Starting Server Processes” of the Applications Installation manual. Also as part of post upgrade steps, you can migrate your database to 10g release 2 and clone other nodes for your multi-tier setup.

The following diagram illustrate the theory behind the upgrade.

Note that you also cannot upgrade directly to 10g until the upgrade is over. This is because:
1. Your application tier may not be supported with 10g
2. Upgrade only works on 9i release 2 version.

DMZ configuration for iRecruitment (Oracle Apps 11i) – Part II

This post is in continuation to DMZ configuration for iRecruitment (Oracle Apps 11i) – Part I

1. Creation of the External WebServer 

Clone the internal Oracle E-Business suite middle tier to the machine that you identified to be the external web tier in the DMZ. Sharing file systems between the external web tiers and the internal middle tiers is not supported in any deployment option. However, sharing file systems such as APPL_TOP between multiple external web tiers or between multiple internal middle tiers supported.

2. Update Hierarchy Type  

The following profile options are used to construct various URLs in an E-Business Suite 11i environment:

•        Applications Web Agent

•        Applications Servlet Agent

•        Applications JSP Agent

•        Applications Framework Agent

•        ICX:Forms Launcher

•        ICX: Oracle Discoverer Launcher

•        ICX: Oracle Discoverer Viewer Launcher

•        Applications Help Web Agent

•        Applications Portal

•        BOM:Configurator URL of UI Manager

•        ASO : Configurator URL

•        QP: Pricing Engine URL

•        TCF:HOST

The default hierarchy type value for the above profile options is Security.

The Configuration of E-Biz Suite environment for DMZ requires these profile options hierarchy type to set to SERVRESP. To change the profile options hierarchy type values to SERVRESP, execute the following sql script.

    sqlplus apps/apps-passwd @$FND_TOP/patch/115/sql/txkChangeProfH.sql SERVRESP

hierarchy-type1.jpg

3. Update Node Trust Level

Oracle E-Business Suite 11i has the capability to restrict access to a predefined set of responsibilities based on the Web server from which the user logs in. This capability is provided by tagging web servers with a trust level. The server trust level indicates the level of trust associated with the web server. Currently, three trust levels are supported:

node-trust-level.jpg

v      Administrative

Servers marked as Administrative are typically those used exclusively by system administrators. These servers are considered secure and provide access to any and all E-Business Suite functions.

v     Normal
Servers marked as Normal are those used by employees within a company’s firewall. Users logging in from normal servers have access to only a limited set of responsibilities.

 v      External

Servers marked as External are those used by customers or employees outside of a company’s firewall. These servers have access to an even smaller set of responsibilities.

Node Trust Level (NODE_TRUST_LEVEL) is a server profile option. The default value for this profile option for all E-Business Suite middle tiers is set to Normal.

Identify the external web tier in your Oracle E-business Suite 11i environment and set the NODE_TRUST_LEVEL profile option value at the server level to External. To change the value of the Node Trust Level profile option value for a particular node, perform the following steps:

1. Login to Oracle E-Business Suite as sysadmin user using the internal URL
2. Select System Administrator Responsibility
3. Select Profile / System
4. From the ‘Find system profile option Values’ window, select the server that you want to make external
5. Query for %NODE%TRUST%. You will see a profile option named ‘Node Trust Level‘. The value for this profile option at site level will be Normal. Leave this setting as is
6. Set the value of this profile option to External at the server level (not site level). The site-level value should remain Normal.

4. Update List of Responsibilities

It’s possible (and recommended) to restrict the general set of Applications Responsibilities based on the application server that you’re using. For example, there should be no reason to allow external users to modify your company’s Chart of Accounts, so that responsibility can’t be used if the end-user is logging in from outside the corporate intranet.After updating the server-level profile value for Node Trust Level for the external web tier(s) to External, users can no longer see any responsibilities when they login to the external web tier. In order for a responsibility to be available from the external E-Business Suite web tier, set the Responsibility Trust Level profile option value for that responsibility to External at the responsibility level.To change the value of the Responsibility Trust Level profile option at the responsibility level for a particular responsibility, perform the following steps:

1)     Login to Oracle E-Business Suite as sysadmin user using the internal URL

2)     Select System Administrator Responsibility

3)     Select Profile / System

4)     From the ‘Find system profile option Values’ window, select the responsibility that you want to make external

5)     Query for %RESP%TRUST%. You will see a profile option named ‘Responsibility trust level‘. The value for this profile option at site level will be Normal. 6)     Set the value of this profile option for the below responsibilities to External at responsibility level (not site level). The site-level value should remain Normal.

responsibility.jpg

a)    iRecruitment External Candidate

5. Update Home Page Mode to Framework

The new Oracle E-Business Suite 11i Home page based on the Oracle Applications Framework architecture is required for the deployment of the Oracle E-Business Suite in a DMZ configuration. To enable this set the self-service personal home page mode to “Framework Only” as shown in the diagram below.

To change the value of the Home page mode, perform the following steps:

1. Login to Oracle E-Business Suite as sysadmin user using the internal URL
2. Select System Administrator Responsibility
3. Select Profile / System

From the ‘Find system profile option Values’ window, query for %HOME%MODE%. You will see a profile option named ‘Self Service Personal Home Page Mode‘ , set the value of this profile option to Framework Only.

homepage.jpg

6. Configuration Details for using Reverse Proxies in DMZ

6.1 Update Oracle E-Business Suite Application Context file

 

On the external Oracle E-Business Suite web node, run the AutoConfig. In the Context Detail screen, set the following configuration values:Ø       set the webentry point, s_webentryhost, to the reverse proxy server  (hostname). Ø       Set the webentry domain, s_webentrydomain, to the domain name of the reverse proxy server (domainname.com). Ø       set the active webport, s_active_webport, to the port where the reverse proxy server listen for client requests. For example port 80 for HTTP or 443 for HTTPS. Ø       Set the webentry protocol, s_webentryurlprotocol, to the protocol value the clients use to access the reverse proxy server (https). Ø       Set the login page, s_login_page, to <webentry protocol>://<webentry point>.<webentry domain>:<active webport>.Replace <webentry protocol>, <webentry point>, <webentry domain>, and <active webport>   with their respective values.

5.2 Enable Oracle E-Business Suite Application Server Security.

The Server Security feature supports authentication of application server machines and code modules in order to access the database. When Server Security is activated, Application Servers are required to supply server IDs (like passwords) and/or code IDs to access a database server. Server IDs identify the machine from which the connection is originating. Code IDs identify the module and patch level from which the connection is originating. Code IDs are included in applications code by development. The database server can be set to allow access only from specific machines and/or by code at a desired patch level.

The application server security feature is not activated by default for pre 11.5.10 E-Business Suite installations. 1.      Set the value of Application Server Security Authentication (s_appserverid_authentication) to SECURE 2.      Run AutoConfig on each Applications middle tier to complete the configuration. 3.      After AutoConfig completes successfully, restart the Oracle HTTP server  

5.3 Run AutoConfig and Restart Oracle HTTP Server.

Ø       Run AutoConfig on External Server.

¨       cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME

¨       adautocfg.sh

Start Oracle HTTP server on External Server.