Monday, May 12, 2008

Count Total Number of Users Connected to ORACLE Applications


Want to determine how many users are connected to Oracle apps 11i,V$session does not give the true picture as mostly there are more than 1 oracle session for the same forms connection depending on how many forms the user has opened up.


1:- Can use this SQL statement to count concurrent_users in Oracle apps:

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

2:- In the Oracle Applications Manager, go to the site map, and select the "Applications Usage" option in the Activity region. There you will be able to report on many usage questions, such as:

Products Installed
Application Users Per Module Summary
Applications Usage Reports
Purchase Line Items Processed - Internet Supplier Portal
Purchase Line Items Processed - Purchasing Intelligence
Order Entry Lines Processed - Ordering Application
Purchase Line Items Processed - iProcurement
Expense Reports Processed - Internet Expense
Invoice Line Items Processed - Accounts Receivables

Note the Application Users per module summary is based on the number of users that have Active responsibilities for the Application module. I am not aware of there being any check on last usage date for the responsibility.

3:- Also if you are using responsibilities connected to a custom-application, which is standard Consulting practice in some countries, those users will be counted as users of the custom-application and not users of the actual Application-module for the screens they are accessing.

4:- Run the following queries:-
    This will give the number of users on the system in the past 1 hour.
     select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1/24 and user_id != '-1';

    This will give the number of users on the system in the past 1 day.
    select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1 and user_id != '-1';

    This will show the activity in the last 15 minutes.
    select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where  last_connect > sysdate - 1/96;

5:-  Check the Note:233871.1 which will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.

Surely this will affect the performance but how it affects is based on the number of users logged in and accessing the forms.

Factoring in System Overhead

In planning your organizations Sign On Audit implementation, you should consider the additional system overhead required to precisely User and Data Auditing monitor and audit your users as they access Oracle Applications. The more users you audit and the higher the level of auditing, the greater the likelihood of incurring additional system overhead.


How to setup Password Security?


Signon Password Failure Limit
The Signon Password Failure Limit profile option determines the maximum number of
login attempts before the user's account is disabled.
Users cannot see or update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT.

Signon Password Hard to Guess
The Signon Password Hard to Guess profile option sets rules for choosing passwords
to ensure that they will be "hard to guess." A password is considered hard-to-guess
if it follows these rules:
. The password contains at least one letter and at least one number.
. The password does not contain the username.
. The password does not contain repeating characters.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS.

Signon Password Length
Signon Password Length sets the minimum length of an Applications signon password.
If no value is entered the minimum length defaults to 5.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_LENGTH.

Signon Password No Reuse
This profile option specifies the number of days that a user must wait before being
allowed to reuse a password.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE.

Signon Password Case
This profile option is not available from the beginning.
With 11i.ATG_PF.H RUP3  comes the system profile 'Password Case Option'
After 11i.ATG_PF.H.RUP4  this system profile option was renamed to  'Signon Password Case'.
There are two settings: 'Sensitive' and 'Insensitive'.
The default is 'Insensitive'.
Setting this profile option to 'Sensitive' will make the password case sensitive.
'Mixed' is no longer supported.

These profiles should only be set at Site level.
They can be set at other levels, such as User or Responsibility.

However, when logging in there is no User context, so if a User is prompted to
change their password at login, the profiles are only evaluated at site level.

Once logged in and resetting passwords using Preferences->Change Password,
or the Security ->User->Define form these other levels will have effect and will confuse the issue.

So these profiles should only be set at Site level, for consistent enforcement.

Saturday, May 10, 2008

How to compile invalid objects in an APPS Environment



Applying Patches can create invalid objects. To get a quick count of the number of existing invalids (if any), use the following select statement :


For a more detailed query, use the following script :


To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type :

alter package <package_name> compile; (package specification)
alter package <package_name> compile body; (package body)
alter view <view_name> compile; (view)

If the object compiles with warnings, use either of the following to see the errors that caused the warnings :

show errors
select * from user_errors where name = '<OBJECT_NAME>';

Another way to correct invalid objects is to run the adadmin utility as follows:


1. Log in as APPS User : <applmgr username>/<applmgr password>

2. Start the adadmin-Utility from the Unix prompt with this command :


The utility will then ask you a series of questions.

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

This task spawns parallel workers to compile invalid database objects in your APPS schema(s). It uses the same parallel phases as AutoInstall.

Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )

Within Applications, there is a script to compile INVALID objects - called ADCOMPSC.pls

Arguments for ADCOMPSC.pls :

1 - Schema to run in
2 - Password for schema
3 - Check errors for objects starting with #3

NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema's. In case of an ORA-1555 error while running adcompsc.pls, restart the script.

The script can be run as followed :

cd $AD_TOP/sql
sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example : SQL> @adcompsc.pls apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing.

If there are any objects still left INVALID, verify them by using the script 'aderrchk.sql' to record the remaining INVALID objects. 'Aderrchk.sql' uses the same syntax as 'adcompsc.pls'. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool <file> command in sqlplus.

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects which will not compile, try the following :

select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';

This script will provide the script that creates the packages/recreates the packages.
If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package :

select text
from user_errors
where name = '<PACKAGENAME>';