session information query

I had a recent go live of one of the instances i delivered.

my manager was curious to know the session information on the instance.

following query helped me a lot in determining them.

set linesize 2000
column logon_time format a17
column username format a16
column unix_pid format a8
column machine format a20
column client_Program format a20
set pagesize 200
select
s.username,
s.sid,
s.serial#,
to_char(p.spid) unix_pid,s.status,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,
-- idle time
-- days added to hours
--( trunc(LAST_CALL_ET/86400) * 24 ) ':'
-- days separately
substr('0'trunc(LAST_CALL_ET/86400),-2,2) ':'
-- hours
substr('0'trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) ':'
-- minutes
substr('0'trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) ':'
--seconds
substr('0'mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2) idle_time,
substr(s.program,1,20) client_program,
s.machine,
s.process client_process,
s.osuser,
substr(p.program,1,20) server_program
from v$session s, v$process p
where s.username is not null
and p.addr = s.paddr
order by username, 2
/

e-business suite -- architecture and technology

Long ago, I was part of a conference describing apps architecture. this described very clearly several things that were not clear to me. following are the details -- and all thanks to the contributor.

What is Oracle Applications

Oracle Applications Release 11i is an 100-Percent internet enabled ERP(Enterprise Resource Planning) + CRM (Customer Relationship Management) +BIS(Business Intelligent system) software Package.
Oracle Applications is comprised of
- A Set of Files
Forms
Reports
Concurrent Programs
‘C’ object files & library files
JSP,HTML-JAVASCRIPT files
- Database
Tables
PL/SQL stored Procedures,packages,functions, java classes and Triggers
Indexes, Sequences, Views, Grants and Synonyms

Internet Computing Architecture

Internet computing Architecture is a framework for three-tired,distributed computing that supports Oracle Applications products .The three tiers are the
l Database Tier : manages the Oracle 8i database
l Application Tier : manages the Oracle Applications and other tools .
l Desktop Tier : provides the user interface display .

ICA Deployment

• Oracle Applications 11i has two different ICA deployment approach
• Form Based Products (ERP and CRM products)
• Non Form Based (HTML Based) products (Self-service, Workflow, BIS etc)

Form Based Architecture

The forms server mediates between the Forms client , a Java Applet running on
the desktop and the Oracle 8i database server at the backend.

The HTTP server helps start a a client session over the internal or external web.
The HTTP server for Release 11i is Apache .

For multiple Forms server Oracle Forms provides a CGI script that distributes the processing load among the servers

Self-service and Web Architecture

Oracle Self service Web Applications and Oracle Workflow are designed in HTML,XML and JavaScript.They operate by direct connection to Apache HTTP server.
Self service applications provide a fast and cost effective way to get information to and from people within an organization and business
Oracle Workflow is used to automatically enforce business rules and policies and to provide a common notification system .


Product Families

• Oracle Apps is a collection of products that are grouped into Product Families.
• ERP
• Financials : AP, AR, GL, CASH, ASSETS
• Manufacturing : Discrete (BOM, Capacity, Cost, ENGG, WIP, MS/MRP, Quality, INV) , Flow, Process, Project manufacturing
• Distributions : Configurator, Order Management ,Internet Procurement, Supply Chain Planning
• HRMS and Oracle Projects
• CRM
• Oracle Service, Oracle Sales , Oracle Marketing ,Internet Business Suite,
• Call Center

Shared products
• Different Modules within Oracle Apps are tightly integrated.
• Each module depends on components from other products for full functionality.
• For example, Oracle Payables uses components in Oracle General Ledger, Oracle Purchasing and Oracle Personnel. If you install Oracle Payables without installing Oracle Purchasing, it automatically gets installed in the shared mode.
• When you install a product, you install the database components of all Oracle Apps products (or rather, it gets installed).
• This only applies to the database portion of Oracle Apps. Only some files that are required get installed for products that you do not install.

Database Structure
• For each product there is a corresponding ORACLE schema that stores that product’s data (few exceptions).
• The default oracle schema name and password are usually the same as the product abbreviation.
– For example, by default:
– Oracle General Ledger data is in schema GL.
– Oracle Purchasing data is in schema PO.
• Some Human Resources products (PER, PAY, DT, FF) share the same schema, HR.
• All Business Productivity products (AOL, ALR, AD) use the same schema, APPLSYS.
• Additionally, the APPLSYSPUB schema has limited access to views and synonyms used in the sign-on process.

Oracle schema
• Oracle Apps Release uses an APPS schema.
• Products were split into two schemas:
– Tables, Indexes and Sequences (data objects) reside in the base product schemas.
– Views, triggers and packages (code objects) reside in the APPS schema.
• The APPS schema has access to all Oracle Apps products.
• Oracle Applications run from the APPS schema
– Note: In Release 10.6 and thereafter, business productivity products (AOL, ALR, and AD) run from the APPLSYS schema.

Apps Schema
• Why use an APPS schema?
• To ensure all these procedures work correctly, proper grants and synonyms had to be set up across all the different schemas.
– If a procedure didn't have the proper access to other procedures or funtions, it would error out.
• Maintaining all the grants and synonyms was time consuming and error prone.
• With the APPS schema, you no longer have to worry about setting up grants and synonyms between base product schemas.
– Procedures in the APPS schema have access to all the data in the base product schemas.
– Procedures in the APPS schema have access to all other procedures in the APPS schema.


Database schemas ...

• Schema Names are not the same as applications usernames
• When you sign on to Oracle Applications and you are connected to the APPLSYSPUB schema, you enter an application username and password.
• This is not the same as the underlying ORACLE schema name and passwords.
• After you enter your application username and password, you choose a responsibility.
• You then connect to the APPS schema automatically.
– You sign on as JDOE/WELCOME.
– You choose the ''JE Clerk'' responsibility.
– AOL connects you to the APPS schema.
– You use forms from the APPS schema which access the GL schema where the GL data reside.

APPL_TOP Directory

• The APPL_TOP directory is the top-level directory for your Oracle Applications files
• The environment variable $APPL_TOP points to it.

APPL_TOP ...

• The APPL_TOP directory contains:
– Your Oracle Applications environment file, for example, APPLSYS.env
– A top level directory for each product (a product can be installed on a disk other than APPL_TOP's disk.) This top level directory is stored in the _TOP environment variable.
– For compatibility with earlier releases, Rapid Install creates another directory,named for the version number, within the product’s short name directory.
– Each Applications top directory is associated with a single Oracle Applications database instance on the Oracle8i Server.
– The APPL_TOP directory has directories for products that are not licensed
• The product directory name uses the standard abbreviation.
– For example, ''gl'' for Oracle General Ledger
– For example, ''fnd'' (not ''aol'') for Application Object Library AutoInstall or Adadmin creates the environment file for you

Typical Product Directory

• Subdirectories group related files
• Each product has a standard set of subdirectories:

The Language files

When you install Oracle Applications in a language other than American English, each product tree includes directories that use the NLS language code. These directories hold translated data, form, html, message, and report files


The Database files
The DATA Directory
• The DATA file system contains the .dbf files of the Oracle Applications Oracle8i database.
• Rapid Install installs all the system, data, and index files in up to four different disks on the database server.

The Technology Stack
The ORA Directory
• Oracle Applications supports running with data in a database of one version, while linking Oracle Applications programs using the tools from a second or third version of the database server. This is known as multiple Oracle Homes.
• This model allows Oracle to support features in later database server versions and still maintain compatibility with an earlier release.

The ORA Directory ...

• The database home contains the files for creating and maintaining the Oracle8i database on the database server.
• The technology stack home contains library and object files the AD Relink Utility uses to link Oracle Applications programs with Forms-based tools on the Forms server and Report server.
• The HTTP home contains the object and library files used to link with the HTTP server.
• The Release 11i database home and HTTP home contain Oracle8i object and library files.
• The Release 11i technology stack home uses libraries from the Oracle8 server technology stack, which includes Oracle Forms, Oracle Reports, Pro*C, PL/SQL, and SQL*Plus.

The COMN Directory
The COMN directory contains files that are used by several different Oracle Applications products (or all Oracle Applications products), or that are used with third-party products

Oracle Applications Environment Files
• The environment file is a shell script that sets up your environment for running Oracle Applications.
• When you install or upgrade Oracle Applications, the AutoInstall program creates this script in the APPL_TOP directory.
– The environment you need depends on what products you installed, what language you installed, etc.
• The filename depends on the ORACLE schema name for AOL and on the platform.
– On Unix, the default filename is APPLSYS.env (case sensitive)
– On VMS, the default filename is APPLSYS.COM
• The file contains different statements on different platforms.
• You can recreate the environment file by using AD Administration (adadmin) utility.

How can Licensed products be de-licensed

How can Licensed products be de-licensed? What if HR needs to be de-licensed?

Licensed products can not be de-licenced oger the OAM interface.

They need to be updated at the table level. The switch on the FND_PRODUCT_INSTALLATIONS table to 'S'. This should de-license

If HR should really be shared rather than fully installed it may be possible to change it as long as no data has been populated in the database. Run this script to determine that HR is now installed fully:

select status from fnd_product_installations where application_id = 800

(I = Installed s = Shared)

Next determine whether hrglobal.drv was run using this script:

select application_short_name, Legislation_code, status, action, pi_steps_exist, LAST_UPDATE_DATE from hr_legislation_installations where application_short_name in ('PER','PAY')


The hrglobal.drv file should not be run in a shared installation as it installs legislations. If hrglobal.drv has not been run, change the switch on the FND_PRODUCT_INSTALLATIONS table to 'S'.

If the legislations have been installed the only way to go back to a shared install is to start over and reinstall the application.