Oracle APPS DBA World

While Upgrading EBusiness Suite from 11i- Whether to go for 12.1 or 12.2 ?

2014-05-15T08:59:00.001-07:00

EBusiness Users/Customers now can upgrade from 11i or 12.0 to 12.1 or 12.2.

A Direct Upgrade is possible from 11.5.9 or higher to 12.1

A Direct Upgrade is possible from 11.5.10 CU2 or higher to 12.2

Oracle Lifetime Support LifeCycle for Oracle EBusiness Products-

R12.1 includes major architectural improvements to the Financial Products to support Global and Shared service operations. It also includes significant enhancements to other Product areas such as -
Procurement
Supply Chain Management
Asset LifeCycle
Service
Human Capital Management

It provides increased opportunity for process improvement and automation

For more on that- see 12.1 and 12.2 Release Content Documents(RCDs)

Upgrade from 11i to 12.1 involves significant changes in FINANCIALS Data Model and functionality, an 11i to R12 upgrade will require greater involvement from Business Process owners and functional analysts than was required prior. Upgrade from 11.5.9 to 11.5.10 was more tech focussed.

Upgrade from 11i to 12.2 includes-
Financial Architectural Changes
Some specific 12.2 technical changes-

Move to Online Patching
Uptake of Weblogic Server
Upgrade to 11gR2 Database
Required Switch from Oracle Single Sign-On to Oracle Access Manager

Customers at release levels below 11.5.9 should consider a reimplementation

When Should You Choose 12.2?

Consider targeting 12.2 if any of the following circumstances applies to your organization:

 You need the high availability provided by Online Patching.

 You want the functional enhancements that are in 12.2.

 You prefer to invest in a single project to upgrade directly to 12.2.

You want the functional enhancements that are in 12.2.

ERP

 Enhanced Labor Costing with Projects and Payroll

 Project Cost Breakdown Structure

 Contract Lifecycle Management (CLM) for Public Sector: Electronic Contract File, Concurrent Modifications, and more

 Channel Revenue Management: Simplified HTML Flows

2013-02-28T08:23:00.001-08:00

Oracle APPS- Purge Workflow email notifications from the Oracle Workflow queue so the Email is not sent

So we have a scenario typically when we create a cloned environment. The Workflow mailer has not been running. Which may have caused a large number of e-mail notifications to accumulate in the queue. How does one prevent these from being sent when the mailer is started.

1. Update the notifications you do not want sent, in the WF_NOTIFICATIONS table. Check the WF_NOTIFICATIONS table. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id, status, mail_status, begin_date 
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';

This should show which notifications are waiting to be e-mailed.

One
 can use the BEGIN_DATE column to help narrow down the ones not to send 
if one only wants to stop the e-mails from a specific date range. 

To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The 
mailer will think the e-mail has already been sent and it will not send it again.. (Users can 
still reply to the notification from the worklist page in the applications). 

Example:


update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL'; 
OR


update wf_notifications
set mail_status = 'SENT'
where end_date is not null
and status = 'CLOSED'
and MAIL_STATUS = 'MAIL';

 

 

This will update all notifications waiting to be sent by the mailer. 


  
2. Then run the script wfntfqup.sql to purge the 
WF_NOTIFICATION_OUT queue and rebuild it with data currently in the 
WF_NOTIFICATIONS table. This is what purges all notifications waiting in
 the queue to be sent.  It will then populate the queue with the current
 data in the wf_notifications table. 


  Since you have changed the mail_status = 'SENT" it will not enqueue
 these messages again.. Only the ones where mail_status = 'MAIL' and 
status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent
 by the mailer. 



sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr  

 

Example Syntax: 


sqlplus apps/apps@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys 


3. Now start the mailer.

2012-09-25T08:16:00.002-07:00

Concurrent Manager Status Shows 'System Hold Fix Manager before resetting counters'

User complained his program is not running and shows No Manager. You checked and Standard Manager seems to be down.

You ran adcmctl.sh and it show status could not be found.

What to do ??

Do the following-

1. Stop all middle tier services including the concurrent managers.
    Please make sure that no FNDLIBR, FNDSM, or any dead process is running.

2. Stop the database.

3. Start the database.

4. cd $FND_TOP/bin
$ adrelink.sh force=y "fnd FNDLIBR"
$ adrelink.sh force=y "fnd FNDFS"
$ adrelink.sh force=y "fnd FNDCRM"
$ adrelink.sh force=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit). Article- ID : 134007.1
Title: CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables

6. Execute the following SQL:
    select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where
    CONCURRENT_QUEUE_NAME like 'FNDSM%';
7. Start the middle tier services including your concurrent manager.

8. Retest the issue.

2012-09-21T11:21:00.006-07:00

Expire All FND_USER Passwords?

“Expire all FND_USER passwords”
This is available starting in RUP4. Requires Patch 4676589 ATG RUP 4.
The script to expire all passwords in the fnd_user table is $FND_TOP/sql/AFCPEXPIRE.sql. It can be executed from SQL*Plus or as a Concurrent Program:
sqlplus -s APPS/<pwd> @AFCPEXPIRE.sql
or
Submit concurrent request: CP SQL*Plus Expire FND_USER Passwords
This script sets the fnd_user.password_date to null for all users which causes all user passwords to expire. It can also be run as a SQL*Plus concurrent program. The user will need to create a new password upon the next login.

2012-09-21T11:18:00.005-07:00

FND_USER script

Purpose-

Can be used to create an FND_USER for any developer that joins the team

This simple script will set all the desired admin responsibilities and all common profile options for a given FND_USER.
Salient features of this simple script are
1. If a user is inactive, that FND_USER will be re-activated
2. If any of their Sysadmin or WF Admin or Application Developer or Functional Admin responsibilities are inactive, those responsibilities will be re-activated
3. Add commonly used responsibilities will be assigned to the user
3. Personalization, Diagnostic and Utilities profile options will be assigned to this user.
4. Password expiration will be removed from the FND_USER

In a nutshell, use this script to enable a user and to enable all their administrator responsibilities.
This script can be run as many times for any given user.
In case a user does not exist, then new user will be created with default password being oracle123

SCRIPT BELOW

################################################################################

Please find the script below

Save this as a SQL File, and it will prompt for parameter User Name

DECLARE
v_session_id INTEGER := userenv('sessionid');
v_user_name VARCHAR2(30) := upper('&Enter_User_Name');
result    BOOLEAN;
v_user_id INTEGER;

FUNCTION check_fu_name(p_user_name IN VARCHAR2) RETURN BOOLEAN IS
    CURSOR c_check IS
      SELECT 'x' FROM fnd_user WHERE user_name = p_user_name;
    p_check c_check%ROWTYPE;
BEGIN
    OPEN c_check;
    FETCH c_check
      INTO p_check;
    IF c_check%FOUND
    THEN
      /*Yes, it exists*/
      CLOSE c_check;
      RETURN TRUE;
    END IF;
    CLOSE c_check;
    RETURN FALSE;
END check_fu_name;

BEGIN
IF NOT (check_fu_name(p_user_name => v_user_name))
THEN
    fnd_user_pkg.createuser(x_user_name                  => v_user_name
                           ,x_owner                      => ''
                           ,x_unencrypted_password       => 'oracle123'
                           ,x_session_number             => v_session_id
                           ,x_start_date                 => SYSDATE - 10
                           ,x_end_date                   => SYSDATE + 100
                           ,x_last_logon_date            => SYSDATE - 10
                           ,x_description                => 'BNitin'
                           ,x_password_date              => SYSDATE - 10
                           ,x_password_accesses_left     => 10000
                           ,x_password_lifespan_accesses => 10000
                           ,x_password_lifespan_days     => 10000
                           ,x_employee_id                => NULL /*Change this id by running below SQL*/
                            /*
                                                             SELECT person_id
                                                                   ,full_name
                                                             FROM   per_all_people_f
                                                             WHERE upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%'
                                                             GROUP BY person_id
                                                                      ,full_name
                                                             */
                           ,x_email_address => ' abc@gmail.com'
                           ,x_fax           => ''
                           ,x_customer_id   => ''
                           ,x_supplier_id   => '');
   dbms_output.put_line ( 'FND_USER Created' ) ;
ELSE
    fnd_user_pkg.updateuser(x_user_name                  => v_user_name
                           ,x_owner                      => 'CUST'
                           ,x_end_date                   => fnd_user_pkg.null_date
                           ,x_password_date              => SYSDATE - 10
                           ,x_password_accesses_left     => 10000
                           ,x_password_lifespan_accesses => 10000
                           ,x_password_lifespan_days     => 10000);
   dbms_output.put_line ( 'End Date removed from FND_USER ' ) ;
END IF;
SELECT user_id
    INTO v_user_id
    FROM fnd_user
   WHERE user_name = v_user_name;
fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'FND_FUNC_ADMIN'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'SYSADMIN'
                      ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'FNDWF_ADMIN_WEB'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'APPLICATION_DEVELOPER'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);

    fnd_user_pkg.addresp(username       => v_user_name,
                       resp_app       => 'ICX',
                       resp_key       => 'PREFERENCES',
                       security_group => 'STANDARD',
                       description    => 'BNitin dbawalkspot.com',
                       start_date     => sysdate - 1,
                       end_date       => null);

result := fnd_profile.save(x_name        => 'APPS_SSO_LOCAL_LOGIN'
                            ,x_value       => 'BOTH'
                            ,x_level_name => 'USER'
                            ,x_level_value => v_user_id);

result := fnd_profile.save(x_name        => 'FND_CUSTOM_OA_DEFINTION'
                            ,x_value       => 'Y'
                            ,x_level_name => 'USER'
                            ,x_level_value => v_user_id);

result := fnd_profile.save(x_name        => 'FND_DIAGNOSTICS'
                            ,x_value       => 'Y'
                            ,x_level_name => 'USER'
                            ,x_level_value => v_user_id);

result := fnd_profile.save(x_name        => 'DIAGNOSTICS'
                            ,x_value       => 'Y'
                            ,x_level_name => 'USER'
                            ,x_level_value => v_user_id);

result := fnd_profile.save(x_name        => 'FND_HIDE_DIAGNOSTICS'
                            ,x_value       => 'N'
                            ,x_level_name => 'USER'
                            ,x_level_value => v_user_id);


COMMIT;
END;
/

################################################################################

2012-09-12T14:16:00.002-07:00

Using Lightweight MLS With Oracle E-Business Suite Release 12.1.3

There was a requirement where we had to store the item's descriptions in 5 languages(Countries of business for us). This was only required for reporting purposes. It was not required to have forms and self service pages in other languages.

Here the feature of Lightweight MLS came in very handy. Its introduced in 12.1.3

What it does- IT ENABLES ADDITIONAL LANGUAGES WITHOUT THE NEED TO APPLY THE NLS PATCH

Steps to enable MLS-

From OAM go to License Manager and activate the desired language
From AD Admin -Choose 'Maintain Application Database Entities' option and then the 'Maintain Multi-Lingual Tables' task
Run the message synchronization script-$FND_TOP/patch/115/sql/AFMSGSYNC.sql

$ sqlplus apps/apps @AFMSGSYNC.sql

NOTE: This script synchronizes the language data in FND_NEW_MESSAGES table
    with the base language data.

    Press RETURN to continue.

    Currently, following languages are installed:

    CODE NAME                     STATUS
    ---- ------------------------ ------------
    US   American English         Base
    D    German                   Install
    FRC  Canadian French          Install

    Please choose Oracle Language code from the installed languages.
    For example JA for Japanese. This language data will be synchronized
    with the base language data.

    Language Code: D

    You chose Spanish (D).
    Is this correct language to synchronize [Yes] ?

    Reading FND_NEW_MESSAGES table to see if language data exists.

    No Spanish language data exists in FND_NEW_MESSAGES table.

    Base language data will be copied to this language. Do you wish to proceed [No] ? Yes

    Synchronization is in progress...

    Synchronization has been done.
    Please do a 'commit' if changes are OK. Otherwise, do a 'rollback'.

    SQL> commit;

    Commit complete.

Bounce the application tier services and we have light weight MLS enabled

Upgrading to Oracle Identity Management (11.1.1.5.0)

2012-08-17T11:49:00.001-07:00

Upgrading to Oracle Identity Management (11.1.1.5.0)

Understanding the Oracle Identity Management Installation

Overview and Structure of Oracle Identity Management 11g Installation

Oracle Identity Management 11g includes two distinct suites
-Oracle Identity Management 11g Release 1 (11.1.1.5.0)

-Oracle Identity and Access Management 11g Release 1 (11.1.1.5.0)

Oracle Identity Management 11g Release 1 (11.1.1.5.0)

To install Oracle Identity Management 11g Release 1 (11.1.1.5.0) you must install
Oracle Identity Management 11g Release 1 (11.1.1.2.0) first.

To install Oracle Identity Management 11g Release 1 (11.1.1.2.0), use ofm_idm_win_
11.1.1.2.0_32_disk1_1of1.zip (for Windows) or ofm_idm_linux_
11.1.1.2.0_32_disk1_1of1.zip (for Linux) comprising the following products:
– Oracle Internet Directory (OID)
– Oracle Virtual Directory (OVD)
– Oracle Directory Services Manager (ODSM)
– Oracle Directory Integration Platform (ODIP)
– Oracle Identity Federation (OIF)
Then you must patch your Oracle Identity Management 11.1.1.2.0 to Oracle Identity
Management 11.1.1.5.0 using the ofm_idm_win_11.1.1.5.0_32_disk1_
1of1.zip (for Windows) or ofm_idm_linux_11.1.1.5.0_32_disk1_1of1.zip
(for Linux)

Oracle Identity and Access Management 11g Release 1 (11.1.1.5.0)

To install Oracle Identity and Access Management 11g Release 1 (11.1.1.5.0), use ofm_
iam_generic_11.1.1.5.0_disk1_1of1.zip comprising the following Oracle Identity and Access Management 11.1.1.5.0 products:

– Oracle Identity Manager (OIM)
– Oracle Access Manager (OAM)
– Oracle Identity Navigator (OIN)
– Oracle Adaptive Access Manager (OAAM)
– Oracle Entitlements Server (OES)

Structure of the Installation

You can install both of the Oracle Identity Management products under a common
Middleware Home directory. When you install these suites on the same machine, two
Oracle Home directories are created on the machine .

For example, the first one, IDM_Home can be the IDM_Home
directory for

Oracle Internet Directory
Oracle Virtual Directory
Oracle Directory Services Manager
Oracle Directory Integration Platform
Oracle Identity Federation

The second one, IAM_Home can be the IDM_Home directory for

Oracle Identity Manager
Oracle Access Manager
Oracle Adaptive Access Manager

Understanding Oracle Fusion Middle and Oracle Identity Management

2012-08-17T11:43:00.000-07:00

Oracle Fusion Middleware-

It is a collection of standards based software products that spans a range of tools and services.
It entails
JAVA EE and developer tools
Integration Services
Business Intelligence
Collaboration

Oracle Fusion Middleware offers complete support for
-Development
-Deployment
-Management

Oracle Enterprise Manager Fusion Middleware Control-

It is a web browser based GUI that you can use to monitor and administer Oracle Fusion Middleware components including Oracle Identity Management Components that are installed in Oracle Weblogic Server domains.

Oracle Identity Management-

It enables enterprises to manage the end-to-end lifecycle of user identities across all enterprise resources-both within and beyond the firewall.

Within OIM , you can

Deploy applications faster
Apply the most granular protection to enterprise resources
Automatically eliminate latent access privileges
and Much more..

Oracle Identity Management 11g Release 1(11.1.1.5.0) Components

Oracle Internet Directory                                   (OID)
Oracle Directory Integration Platform                (ODIP)
Oracle Virtual Directory                                     (OVD)
Oracle Directory Services Manager                   (ODSM)
Oracle Identity Federation                                 (OIF)

Oracle Identity and Access Management 11g Release 1 (11.1.1.5.0) Components

Oracle Identity Manager                                   (OIM)
Oracle Access Manager                                   (OAM)
Oracle Adaptive Access Manager                    (OAAM)
Oracle Identity Navigator                                 (OIN)
Oracle Entitlement Server    (OES)

Spl Instructions for Oracle Single Sign On (OSSO) and Oracle Delegated Admin Services Users

OSSO and Oracle Delegated Admin Services are reqd components for

Oracle Portal
Forms
Reports
Discoverer Release 10g and Release 11g

There are no (11.1.1) versions of OSSO and Oracle Delegated Admin Services
If you have OSSO 10g (10.1.2.3 or 10.1.4.3) installed on your topology, upgrade OSSO on 10g to Oracle Access Manager 11g.
NOTE- Installing OSSO 10g for use with OID 11g Rel1(11.1.1.5.0) is not supported
You can continue to use OSSO 10g or Oracle Delegated Admin Services Rel10g with OID Rel10g

Oracle Identity Manager Architecture

2012-08-14T09:35:00.002-07:00

Oracle Identity Manager is J2EE ( provisioning, request processing and job scheduling) and Web based (profile management and delegated administration) application
OIM is J2EE based application deployed on J2EE compliant application server (Weblogic, Tomcat, IBM websphere) and repository in relation database ( ORACLE , MYSQL)
For OIM certification matrix Click here

Oracle Identity Manager

2012-08-14T08:32:00.002-07:00

Oracle Identity Manager

Oracle Identity Manager is a world class leading Identity provisioning server which helps in

Creation of User
Managing Users
Revoking Access

History- Oracle purchased Thor technologies and bundled Identity Provisioning Server and Audit and Compliance Module as Oracle Identity Manager

Few Services in Identity Manager

Access Right Management - Granting and Revoking access rights for a resource(URL,site,services,server,database..)
Provisioning - Process that grants users , groups appropriate access rights
Deprovisioning
Attestation - confirming from authorized user that access right or other privileges are correct or not

Architecture of ORACLE IDENTITY MANAGER

OIM uses a 3 tier architecture

Presentation tier - consists of Admin console, Design console and custom client
Server tier - consists of Oracle Identity Manager server application with various other components and acts as a bridge between Presentation tier and Data and Enterprise Integration tier
Data and Enterprise Integration tier-Consists of Database server which contains OIM data

Components in Identity Manager-

Oracle Identity Manager server
Oracle Identity Manager Remote Manager
Oracle Identity Manager Design Console (Windows only)

Requirement for installing OIM-

A supported Application server. Following can be used--Oracle Application Server/JBOSS/Weblogic/Websphere..)
A supported JVM (use Sun JDK)
A supported Database ( Oracle DB, MS-SQL)

Forms Session Timeout Issues-

2012-08-07T13:38:00.003-07:00

Forms Session Timeout Issues-

Often when using forms in 11i, users may complain that their session times out and that they are presented with the following request to login even though they are busily working:

Decision


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


Your log on session is no longer valid. 


Would you like to log back in so you can 


continue working? If you do not log in, any 


outstanding data changes in your forms will 


not be saved. 


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

 [Yes] [No]

 When the user is idle for more minutes than specified by the system profile option "ICX:Session Timeout" or the number of milliseconds specified by the session.timeout parameter in zone.properties (these values should indicate the same time), then the user will be prompted to login again.

-note-171261.1

For situations such as this, there are (at least) two detectors that are useful.  They can be used independantly or together depending on the circumstance.   The first detector is a PL/SQL query that shows the user's status, while the second detector shows the user's interaction with forms and the forms interaction with the ICX_SESSIONS table


Detector 1 - Watch the User's Status in the ICX_SESSIONS Table

col timeout format a8 
col user_name format a10 
col mins_idle format 999.99 
select 
  disabled_flag, 
  to_char(first_connect,'MM/DD/YYYY HH:MI:SS') Start_Time, 
  to_char(sysdate,'HH:MI:SS') Current_Time, 
  USER_NAME, 
  session_id, 
  (SYSDATE-last_connect)*24*60 Mins_Idle, 
  fnd_profile.value_specific 
    ('ICX_SESSION_TIMEOUT', 
     a.user_id, 
     a.responsibility_id, 
     a.responsibility_application_id, 
     a.org_id, 
     NULL 
    ) TimeOut 
from 
  ICX_SESSIONS a, fnd_User b 
where 
  a.user_id=b.user_id 
  and last_connect > sysdate-1/24;

EXAMPLE OUTPUT
==============

D START_TIME          CURRENT  USER_NAME  SESSION_ID MINS_IDLE TIMEOUT 
- ------------------- -------- ---------- ---------- --------- -------- 
N 09/30/2006 05:28:50 05:29:00 SYSADMIN   568577602        .17 30 
N 09/30/2006 05:22:23 05:29:00 DCOLLIER   469704674       6.62 30


Understanding the Session Timeout Query (Detector 1)

The session timeout query is meant to be run again and again to confirm that a user's activity is being counted and can also be used for determining what activity is counted against the idle timeout and what is not. For example, when practicing with this you may note that when a user is within core forms the ICX_SESSIONS table is not updated with every single click. Instead, the update is made only if at least one minute has passed since the last update.  The session begins when the user logs into applications using the supported "AppsLogin" method, but there is no session created (and therefore never a timeout window) when logging in directly via the unsupported "/dev60cgi/f60cgi" URL. 

Column Specifics 

D - The disabled flag. If this is set to 'Y', then the session has been explicitly disabled, such as from a user logging out. If this is 'N', then the session is not disabled. 

START_TIME - The time of day the user first logged in. 

CURRENT - The current time of day; useful when running the query repeatedly as this timestamps each query run. 

USER_NAME - The login name of the user. 

SESSION_ID - The session ID of the user. This can be confirmed from forms using the following navigation from the "System Administrator" responsibility: 

- Help/Diagnostics/Examine 
- - Block:$PROFILES$ 
- - Field: ICX_SESSION_ID 
- - Value: Click this field for the value to appear 
(The value of my session_id=469704674 as seen in the above Detector1 query result) 

MINS_IDLE - The perceived number of minutes that the user has been idle. 

TIMEOUT - The value of "ICX:Session Timeout" for the specific user 

The query, as written, will dump a listing of all users that have connected within the last hour. If different users have different values for "ICX: Session Timeout", then this will be immediately apparent.  

Certainly, the where clause can be altered (where session_id=469704674) to just watch a specific session, or even "where user_name='SOMEUSER' ", for example, to show all sessions open for a particular user. Early versions of 11i had problems with "session confusion" in that the timeout window created a new session rather than validating an existing session (bug:3102240, bug:3305591) and therefore user activity was counted in one session while the timeout was watching another.  This led to an inability to revalidate an existing session and the repeated display of the timeout popup window.

Finally, the query, when evaluating the profile option value of "ICX:Session Timeout" only considers the site, application, responsibility, and user level. Typically these are the only levels that are allowed, but when troubleshooting it may be useful to see if an applications DBA has overridden these defaults and introduced a peculiar value. The following query will confirm that only these four levels need to be checked:

select 
  SITE_ENABLED_FLAG, 
  APP_ENABLED_FLAG, 
  RESP_ENABLED_FLAG, 
  USER_ENABLED_FLAG, 
  ORG_ENABLED_FLAG, 
  SERVER_ENABLED_FLAG, 
  SERVERRESP_ENABLED_FLAG 
from 
  fnd_profile_options 
  where 
upper(profile_option_name) = 'ICX_SESSION_TIMEOUT';

EXAMPLE OUTPUT
==============

S A R U O S S 
- - - - - - - 
Y Y Y Y N N N 

See the above query for the full names of the columns.  This query result just indicates the completely normal instance where the "ICX:Session Timeout" system profile option can only be set at the Site, Application, Responsibility, and User levels.


An Example Application of the Session Timeout Query

As described above, the session timeout query (Detector 1) can be rewritten as follows:

col timeout format a8 
col user_name format a10 
col mins_idle format 999.99 
select 
  disabled_flag, 
  to_char(first_connect,'MM/DD/YYYY HH:MI:SS') Start_Time, 
  to_char(sysdate,'HH:MI:SS') Current_Time, 
  USER_NAME, 
  session_id, 
  (SYSDATE-last_connect)*24*60 Mins_Idle, 
  fnd_profile.value_specific 
   ('ICX_SESSION_TIMEOUT', 
     a.user_id, 
     a.responsibility_id, 
     a.responsibility_application_id, 
     a.org_id, 
     NULL 
   ) TimeOut 
from 
  ICX_SESSIONS a, fnd_User b 
where 
  a.user_id=b.user_id 
  and user_name='&USER_NAME_IN_UPPER_CASE';


Things to look for:

1. Verify that the user has only one active session.
This rendition of the query will prompt you to enter the username of the user you are watching and will display all rows with that username. If the user has more than one active session, this will be indicated by having more than one row with 'N' under the "Disabled" column and a "Mins_Idle" less than the timeout value.

2. Confirm that the user's activity is being counted at allAs the user works, the "Mins_Idle" should change at least every minute and not accumulate more than a few minutes of time.  Depending on what form the user is using and what exactly the user is doing (long running query, etc.) the "Mins_Idle" can accumulate.  The key is finding conditions where the user is busily updating a form, but the "Mins_Idle" continues to accumulate in excess of several minutes.  Such forms, often custom, were not built correctly to make the check_session calls described below in the section on "Detector 2".  Detector 2 watches user activity from a different perspective.

3. Confirm that the proper ICX_SESSION is updated.The only session that should be updated by the user's activity within forms should be the one identified by the ICX_SESSION_ID profile field as seen by navigating to Help/Diagnostics/Examine as detailed above. 

It is somewhat normal to have several hundred rows returned because the ICX_SESSIONS table contains information on both active and inactive sessions for everyone who has logged in since the table was last cleaned. It is generally recommended to schedule the concurrent request "Delete Data From Temporary Table" to run at least weekly. This request runs the script $ICX_TOP/sql/ICXDLTMP.sql which deletes any information in ICX_SESSIONS (and its dependant tables) that is more than 4 hours old and alleviates the performance issues associated with letting these tables grow with obsolete data. This script can be run manually. 

Before watching any particular user, it is best to tidy up these tables by running the $ICX_TOP/sql/ICXDLTMP.sql script. This script is run by the apps user from sqlplus, requires no arguments, and is designed to clear out information on any session older than four hours (repeated for emphasis). Caution must be taken when running this in production since ACTIVE users who have been logged in for more than 4 hours will also lose their entry in ICX_SESSIONS. This will eventually cause them to be kicked out of applications with the error message "Your log on session has become invalid. Exiting Oracle Applications." which is slightly different than the timeout window in that no opportunity is offered to log back in and continue.  The kicked out users can immediately log back in from the beginning, but will find this annoying. Generally this script is only run at night or during a time when there are only a minimum number of active users.

Detector 2 - Track the User's Perceived Idle Status via Forms Runtime Diagnostic

The second detector is simply just a "tail -f" of an FRD (forms runtime diagnostic) with a grep for session tickling. Clearly, this detector is only valid for forms users whereas the first detector will also work with self-service users. When I run this test, on one PC I have the 11i forms session where I am logged in. On another PC, so that I can see behavior immediately, I have two telnet windows that serve as user activity detectors.  Having these two detectors displayed on a laptop while sitting next to a specific user that constantly complains of being timed out is instructive.  Quite often a user's trip to the water cooler is much longer than they admit to on a help ticket and therefore the session timeout on their unattended workstation is doing exactly what it should do.

Each of the two telnet windows is running one of the user activity detectors: 

Detector1 - the time checking query as above 
Detector2 - the command "tail -f username_frd.log | grep -n fnd_session.check_session"

With Detector2, the "username_frd.log" is an ongoing FRD trace as setup by Note:150168.1 section 'C'.  Essentially the FRD is activated by navigating via System Administrator to Profile/System and querying up the profile option "ICX: Forms Launcher" and specifying to display at the Site and user level for whatever user you will be tracing. You can copy the current Site level value to the user level value and then add the FRD arguments to trace only that specific user. For example:http://server.domain:8000/dev60cgi/f60cgi?record=all&log=username_frd.log  will create a "username_frd.log" file with tracing details of just one specific user.  Note that the log will be written on the forms server in the directory specified by the environment variable FORMS60_TRACE_PATH regardless of the location specifed for the log.

When testing, you should find that for every single time the user clicks in a field, Detector2 gives two more lines of the following where each pair of lines has their own, new line numbers indicating activity: 

     29441:In Argument 0 - Type: String Value: Entering fnd_session.check_session. 
     29482:In Argument 0 - Type: String Value: Completed fnd_session.check_session. 

The definitions for fnd_session.check_session and fnd_session.session_status are in $AU_TOP/resource/FNDSQF.pll and are they main driver for the session timeout determination within forms.  The function "check_session" calls "session_status" which calls FND_ICX_SEC.CHECK_SESSION in the database which uses a query similar to "Detector1", above, to determine timeout status.


A look at the code for session_status within FNDSQF.pll makes the difference between the two detectors clear: 

     PACKAGE BODY fnd_session IS 
     last_verify_time DATE := NULL; /* The last time session verified*/ 
     check_delta NUMBER := 1/(24*60); /* = 1 minute. Number of Days betw chks */ 

If "last_verify_time" (which is set to fnd_standard.system_date on exit) is greater than one minute, then session_status won't bother the database with a call to FND_ICX_SEC.CHECK_SESSION and will just return that the session is valid. If "last_verify_time" is greater than one minute, then there will be a call to FND_ICX_SEC.CHECK_SESSION and the session tickler will update the last_connect time which can be seen in the results of the Detector1 query.   Of course, the actual code within FNDSQF will vary with each version, so it is always useful to know what version of FNDSQF is in use by reading the header line (for example: strings -a $AU_TOP/resource/FNDSQF.pll | grep '$Header').

Detector 2 is useful in determining what activity within forms counts against the idle timeout clock. It is useful to demonstrate the effectiveness of this detector on a known good form such as the profile options query form from the System Administrator responsibility. Essentially any mouse click into any field results in two lines being added to the "tail -f|grep" output, while not clicking anything leaves the telnet window seemingly hung.  This is a clear, interactive demonstration that the session tickler is working.

Certain product teams forms and certain custom forms do not properly call fnd_session.check_session and therefore don't count user activity. If by using these methods you find an Oracle form behaving this way, get the shortname and version of that form (Help/About Oracle Applications) along with the FNDSQF.pll versoin.

Database sessions and more ..

2012-08-07T13:22:00.002-07:00

Kill Oracle Sessions

A user has fired a sql statement which is stuck and not moving along. As a DBA you need to kill the oracle session. This is what you can do

USING SQL PLUS - Find out the SID and SERIAL# of the SESSION

select username,sid,serial#,terminal from v$session;

Then use the following query to kill the session

alter system kill session ‘SID, SERIAL#’;

This statement does the following--

Terminates the session

Rolls back transactions

Releases all session locks

Frees all session resources

If the session is waiting for some activity that must be completed --for example waiting for a reply from a remote database or rolling back a transaction then Oracle waits for this activity to complete and then kills the session. If the wait lasts for 60 seconds then oracle marks this session to be killed and returns the message that session is marked for kill.

USING KILL COMMAND --Find out the SPID (Server Process ID) of the oracle session. then issue the kill command

kill -9 <SPID>

Look at the following queries

select username,sid,serial#,terminal from v$session;

—————————— ———- ————-

SCOTT 134 47747 BSR

SYS 147 46965 pts/0

150 2 UNKNOWN

151 4 UNKNOWN

154 1 UNKNOWN

167 1 UNKNOWN

168 1 UNKNOWN

169 1 UNKNOWN

170 1 UNKNOWN

18 rows selected.

alter system kill session ’134,47747′ immediate;

Using Unix KILL Command:

SELECT s.sid, p.spid, s.osuser, s.programFROM v$process p, v$session sWHERE p.addr = s.paddr;

SID SPID OSUSER PROGRAM

———- ———— ——————————

170 15771 oracle oracle@oraprod (PMON)

169 15773 oracle oracle@oraprod (PSP0)

168 15775 oracle oracle@oraprod (MMAN)

167 15777 oracle oracle@oraprod (DBW0)

166 15779 oracle oracle@oraprod (LGWR)

165 15781 oracle oracle@oraprod (CKPT)

164 15783 oracle oracle@oraprod (SMON)

163 15785 oracle oracle@oraprod (RECO)

162 15787 oracle oracle@oraprod (CJQ0)

161 15789 oracle oracle@oraprod (MMON)

160 15791 oracle oracle@oraprod (MMNL)

138 16425 oracle oracle@oraprod (J000)

154 15799 oracle oracle@oraprod (QMNC)

147 16259 oracle sqlplus@oraprod (TNS V1-V3)

150 15807 oracle oracle@oraprod (q000)

151 15809 oracle oracle@oraprod (q001)

137 16329 oracle sqlplus@oraprod (TNS V1-V3)

134 16380 BSR sqlplus.exe

18 rows selected.

$ ps -ef|grep 16380

oracle 16436 16236 0 17:30:37 pts/0 0:00 grep 16380
oracle 16380 1 0 17:28:32 ? 0:00 oracleORCL (LOCAL=NO)
$ kill -9 16380

Look at the following note-

Should Sessions be Killed in OS or Using Alter System Kill Session? [ID 161794.1]

Compiling Invalid Objects in Oracle Database

2012-08-03T09:08:00.000-07:00

After schema changes packages and package bodies go invalid

How to validate them in one go

Choice

1. You can go to $ORACLE_HOME/rdbms/admin
As SYS user run @utlrp.sql and check invalids again

NOTE- This might need to be run multiple times

2. Run the following query

invalid.sql

Set heading off;

set feedback off;

set echo off;

Set lines 999;

Spool run_invalid.sql

select

'ALTER ' || OBJECT_TYPE || ' ' ||

OWNER || '.' || OBJECT_NAME || ' COMPILE;'

from

dba_objects

where

status = 'INVALID'

and

object_type in ('PACKAGE','FUNCTION','PROCEDURE')

;

spool off;

set heading on;

set feedback on;

set echo on;

@run_invalid.sql

Oracle Applications Filesystem R12

2012-07-25T14:32:00.001-07:00

Depending on the needs of a business and how the apps are installed the apps product directories may be located on a single machine or on multiple machines. OS environment settings locate different file locations on the database tier and application tier.

The top level applications directory structure is as below

the db-->apps_st-->data (DATA_TOP) directory is located on the database node machine, and contains the system tablespaces, redo log file, data tablespaces, index tablespaces, and database files
the db--->tech_st--->10.2.0 directory is located on the database node machine and contains the Oracle home for Oracle 10g database
the apps--->apps_st-->appl(APPL_TOP) contains product directories and files for Oracle Apps
the apps-->apps_st-->comn (COMMON_TOP) contains directories and files used across products
the apps-->tech_st-->10.1.2 directory contains the Oracle Home used for the Applications Technology stack tools components
the apps-->tech_st-->10.1.3 directory contains the Oracle Home used for the Applications technology stack Java components

Oracle APPS Env

Oracle apps uses env settings to find executable programs and other files required for apps operations. These settings are defined when you install Oracle APPS. Many of the setting are defined by the information you provide when running Rapid Install while others have the same values in all installations.

Instance Home($INST_TOP)
Oracle R12 has introduced a top level directory called Instance Top. It provides the capability to share the application stack code and technology stack code across instances for instance test and development instance. It also provides the centralization of config log files making their management simpler.

The basic structure of Instance Home is -
<APPS_BASE>/inst/apps/<context>/<INST_TOP> where APPS_BASE is the top level of the Apps Installation and context is the highest level at which the application context exists
For instance
/d01/appmgr/inst/apps/dev1 where dev1 is the contextname

Instance Top is depicted as below

A key benefit of having an instance top is that Autoconfig no longer writes to the APPL_TOP or Oracle_Home directories so these can both be made read only file systems if required. In 11i the adpatch utility wrote to $APPL_TOP/admin on a patching/admin node. Under the new model $APPL_CONFIG_HOME/admin is used instead. $APPL_CONFIG_HOME will equate to a value such as /d01/applmgr/apps/apps_st/appl

Another advantage of instance home is that the log files can be stored centrally and therefore managed more easily. This is an important feature for security as log files could contain critical data.

Log Files

Introducing OBIEE- Oracle Business Intelligence Enterprise Edition

2012-07-24T11:31:00.000-07:00

Introducing OBIEE- Oracle Business Intelligence Enterprise Edition

OBIEE comes from Siebel Analytics which comes from an earlier acquisition of nQuire by Siebel in 2002.

By default, the Oracle Business Intelligence components are installed in a cluster configuration and are scalable.You cannot add Oracle Business Intelligence to an existing domain created by Oracle WebLogic Server or another Oracle Fusion Middleware product.

After running Oracle Business Intelligence 11g Installer, the installed products share the same Oracle Fusion Middleware infrastructure in a single WebLogic domain.

All Oracle Business Intelligence products intended for a WebLogic domain must be installed and configured at the same time. You cannot install some products now and then install others to the same WebLogic domain later.

For example, if you choose to install and configure Oracle Business Intelligence Enterprise Edition but not Oracle Real-Time Decisions, you cannot later add Oracle Real-Time Decisions to the WebLogic domain. Similarly, any WebLogic domains that contain Oracle Business Intelligence 11g products should not be extended later to include other Oracle Fusion Middleware products.

Installation

Type of installation

Type of installation	Purpose	Definition
Simple Install	Demonstration and Evaluation Single-User Development	Installation with the default settings on a single computer in the minimum number of steps
Enterprise Install	Enterprise Deployment for Hosted Development and Production	Enterprise Install type enables you to specify several more configuration settings than the simple
Software Only Install	Enterprise Deployment for Highest Levels of Availability and Security	Installation of the binary without configuration

To find the installers:
Downloads

For OBIEE Install Guide:
OBIEE INSTALL GUIDE

Installation on LINUX

Creation of the installer user

On Linux, running Oracle Business Intelligence 11g Installer as the root user is not supported.

Creation of the orinstall group

[root@oel11g ~]# groupadd oinstall

Creation of the OBI User

[root@oel11g ~]# /usr/sbin/useradd obi -g oinstall
[root@oel11g ~]# passwd obi
Changing password for user obi.
New UNIX password:
BAD PASSWORD: it is WAY too short
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

Creation of the Middleware Home directory

mkdir -p /MiddlewareHome
chown -R obi:oinstall /MiddlewareHome
chmod -R 775 /MiddlewareHome

where

Linux - chown: Change owner to the user obi
chmod 775: Everything is allowed for the owner and read and execute access are allowed for everyone

Package

The prerequis verification of the installer will check all package. With an Oracle Database 11g installed, you need only to install onepackage (compat-db-4.1.25-9) that you can get from yum of from the installation DVD.

On yum, I found the package compat-db-4.2.52-5.1.i386.rpm that I have copied and installed from the tmp directory.

[root@oel11g tmp]# rpm -Uvh compat-db-4.2*
Preparing...                ########################################### [100%]
   1:compat-db              ########################################### [100%]

Set the File Handle Limit parameter

Set the hard and soft limit to the OBI user by modifying the file limits.conf such as for a database installation:

obi                 soft    nofile  4096
obi                 hard    nofile  65536

The maximum File-descriptor must be at least 4096

To discover the file-descriptor settings, run the command ulimit -a
Or set file-descriptor using the command ulimit -n 4096

[root@oel11g dev]# ulimit -a|grep open
open files                      (-n) 4096

Environment Variable

You can add them in the .bash_profile shell startup script of the obi user.

# File Descriptor Limit
ulimit -n 10240

# Database Parameters
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin
export TNS_ADMIN
PATH=$ORACLE_HOME/bin:/opt/bin:$PATH
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH