This is a quick note to share a SQL that will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.
The column named "module" will tell you the name of the Form Function or the Concurrent Program Short name which has aquired a lock onto that table.
SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';
Wednesday, June 11, 2008
Types of Apps Tables
There are following tables :
_ALL Conatains multi org data before using this table you need to set client_into to
specific org , Org_id is common column.
_V view created on base table
_VL view created on multi language tables.
always use the condition LANGUAGE = USERENV(’LANG’).
_TL Tables support multi language.
_B These Main base tables
_F these date tracked tables for only in HRMS and these table contain 2 common
columns effective_start_date and effective_end_date .
_S sequence related tables .
_AVN Audit view shadow tables contains data track information .
_ALL Conatains multi org data before using this table you need to set client_into to
specific org , Org_id is common column.
_V view created on base table
_VL view created on multi language tables.
always use the condition LANGUAGE = USERENV(’LANG’).
_TL Tables support multi language.
_B These Main base tables
_F these date tracked tables for only in HRMS and these table contain 2 common
columns effective_start_date and effective_end_date .
_S sequence related tables .
_AVN Audit view shadow tables contains data track information .
Tracing Jinitiator
Some times Jinitiator raises exception .You can figure it out by using Trace Jinitiator
Step 1: Windows select Start - Settings - Control Panel – Jinitiator
Step 2: Enter following in the "java Run Time parameters”
textfield:
-Djavaplugin.trace=true
-Djavaplugin.trace.option=basicnetsecurityextliveconnect.
Step 3: Restart Your Machine
You can see the trace file information under C:\Documents and Settings\jinitiator. Trace
Step 1: Windows select Start - Settings - Control Panel – Jinitiator
Step 2: Enter following in the "java Run Time parameters”
textfield:
-Djavaplugin.trace=true
-Djavaplugin.trace.option=basicnetsecurityextliveconnect.
Step 3: Restart Your Machine
You can see the trace file information under C:\Documents and Settings\jinitiator. Trace
How to get Version of Programs, Files, and Objects
1) ORACLE APPLICATIONS
To obtain version of form from any applications form, navigate to the form. Then in the Menu toolbar
select Help => About Oracle Applications.
Or
Run the following statement
select release_name from fnd_product_group;
2) DATABASE OBJECTS
select text from user_source where name='&package_name' and text like '%$Header%';
You can also obtain the pls version on the database by running:
select name, text
from dba_source
where text like '%.pls%'
and line
VIEWS
select VIEW_NAME, TEXT
from
USER_VIEWS
where VIEW_NAME = '&VIEW_NAME';
Oracle Workflow
$FND_TOP/sql/wfver.sql
OR
select TEXT from WF_RESOURCES where NAME='WF_VERSION';
UNIX : uname -a
WINDOWS : Start => Parameters => Control Panel => System
JAVA
java -version
sqlplus
start=>Run=>cmd
sqlplus
TNSPING
start=>Run=>cmd
TNSPING
D2k version
ORACLE_HOME\Orainst\nt.rgs
File versions
On Unix: strings -a "File name" | grep Header
On Windows : find "Header" File_name
To obtain version of form from any applications form, navigate to the form. Then in the Menu toolbar
select Help => About Oracle Applications.
Or
Run the following statement
select release_name from fnd_product_group;
2) DATABASE OBJECTS
select text from user_source where name='&package_name' and text like '%$Header%';
You can also obtain the pls version on the database by running:
select name, text
from dba_source
where text like '%.pls%'
and line
VIEWS
select VIEW_NAME, TEXT
from
USER_VIEWS
where VIEW_NAME = '&VIEW_NAME';
Oracle Workflow
$FND_TOP/sql/wfver.sql
OR
select TEXT from WF_RESOURCES where NAME='WF_VERSION';
UNIX : uname -a
WINDOWS : Start => Parameters => Control Panel => System
JAVA
java -version
sqlplus
start=>Run=>cmd
sqlplus
TNSPING
start=>Run=>cmd
TNSPING
D2k version
ORACLE_HOME\Orainst\nt.rgs
File versions
On Unix: strings -a "File name" | grep Header
On Windows : find "Header" File_name
Tracing TNS Entries Through Oracle Applications
When u login into Apps Page ..
Step 1: Click on About link Page.
Step 2: Click on Page Context Tab
You will Get following Information.
1) Data base Name (Hostname)
2) Port no#
3) SID Name
Step 4: Build Tns Entries With following method and Paste it in local machine
Try to connect with default Password apps/apps
"SID"=
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = "Host Name")
(PORT = "Port number")) )
(CONNECT_DATA =
(SERVICE_NAME ="SID" )
) )
Connect with Default username/password : APPLSYSPUB/PUB
This username/password is hard-coded in FND_TOP/secure/appsweb.cfg file.
Once you get the DataBase access Use following Select statement gets the front URL access.
select HOME_URL from icx_parameters
You will get Front-end access login Screen.
By useing APPS.FND_WEB_SEC package change the SYSADMIN or user password At SQL Prompt.
Example 1:
--Change Sysadmin Password from PL/SQL
set serverout on
declare
ret varchar2(200);
begin
ret := fnd_web_sec.change_password('SYSADMIN',
'Welcome1);
dbms_output.put_line('Result of Package'ret);
---Y Means Success Else Fail.
end;
Commit;
Step 4: Finally Click on Java System Properties Scroll Down the Page. Your able to see the Unix Login user name(Back End User Access)
Step 5:Try to connect unix server with Default Password.
Step 1: Click on About link Page.
Step 2: Click on Page Context Tab
You will Get following Information.
1) Data base Name (Hostname)
2) Port no#
3) SID Name
Step 4: Build Tns Entries With following method and Paste it in local machine
Try to connect with default Password apps/apps
"SID"=
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = "Host Name")
(PORT = "Port number")) )
(CONNECT_DATA =
(SERVICE_NAME ="SID" )
) )
Connect with Default username/password : APPLSYSPUB/PUB
This username/password is hard-coded in FND_TOP/secure/appsweb.cfg file.
Once you get the DataBase access Use following Select statement gets the front URL access.
select HOME_URL from icx_parameters
You will get Front-end access login Screen.
By useing APPS.FND_WEB_SEC package change the SYSADMIN or user password At SQL Prompt.
Example 1:
--Change Sysadmin Password from PL/SQL
set serverout on
declare
ret varchar2(200);
begin
ret := fnd_web_sec.change_password('SYSADMIN',
'Welcome1);
dbms_output.put_line('Result of Package'ret);
---Y Means Success Else Fail.
end;
Commit;
Step 4: Finally Click on Java System Properties Scroll Down the Page. Your able to see the Unix Login user name(Back End User Access)
Step 5:Try to connect unix server with Default Password.
Oracle APPS Password Security
Oracle applications 11i Implementations has a Security weakness. Oracle application 11i passwords are stored in the database. Apps password stores in2 tables: FND_USER and FND_ORACLE_USERID tables Stores internal oracle applications database accounts. The APPS and APPLSYS database accounts must always have the same password
The APPS.fnd_user table has all applications accounts and there Are 2 Password columns provide for ENCRYPTION.
ENCRYPTED_FOUNDATION_PASSWORD
ENCRYPTED_USER_PASSWORD
The above 2 columns any also contains 1 of the String Values
1.External: Means User Authentication is Delegated So password is not stored in the FND_USER Table
2.INVALID Some Default Oracle apps Accounts access is blocked by directly updating the FND_USER Table.
3.X Means like INVALID
4.ZG If the Encryption Algorithm fails the Error MSG is stord in password column
The Passwords in FND_ORACLE_USERID maintained independent of the database, So Any changes to the account passwords using ALTER USER statements not reflect in the table. If you want change the passwords in FND_ORACLE_USERID can done by utility called FNDCPASS or sysadmin “ORACLE Form”
The Oracle Application passwords can be 1 to 100 chars length and longer Passwords are truncated at 100 Chars.
Oracle Apps passwords Encryption done by APPS.FND_WEB_SEC Or JAVA Class Oracle.apps.fnd.security.websessionManagerProc
Oracle.apps.fnd.security.AolSecurity
Oracle.apps.fnd.security.AolSecurityPrivate
You can find this class information at $JAVA_TOP Directory.
You can download the class and Using Java class Converter You can see how the code is written. The Actual Encryption and Decryption calls are in the “oracle.apps.fnd.security.AolSecurityPrivate” Java class. The Guest account Password is stores in System Profile. You can get by using
Select APPS.FND_PROFILE.VALUE('GUEST_USER_PWD') from dual.
The APPS.fnd_user table has all applications accounts and there Are 2 Password columns provide for ENCRYPTION.
ENCRYPTED_FOUNDATION_PASSWORD
ENCRYPTED_USER_PASSWORD
The above 2 columns any also contains 1 of the String Values
1.External: Means User Authentication is Delegated So password is not stored in the FND_USER Table
2.INVALID Some Default Oracle apps Accounts access is blocked by directly updating the FND_USER Table.
3.X Means like INVALID
4.ZG If the Encryption Algorithm fails the Error MSG is stord in password column
The Passwords in FND_ORACLE_USERID maintained independent of the database, So Any changes to the account passwords using ALTER USER statements not reflect in the table. If you want change the passwords in FND_ORACLE_USERID can done by utility called FNDCPASS or sysadmin “ORACLE Form”
The Oracle Application passwords can be 1 to 100 chars length and longer Passwords are truncated at 100 Chars.
Oracle Apps passwords Encryption done by APPS.FND_WEB_SEC Or JAVA Class Oracle.apps.fnd.security.websessionManagerProc
Oracle.apps.fnd.security.AolSecurity
Oracle.apps.fnd.security.AolSecurityPrivate
You can find this class information at $JAVA_TOP Directory.
You can download the class and Using Java class Converter You can see how the code is written. The Actual Encryption and Decryption calls are in the “oracle.apps.fnd.security.AolSecurityPrivate” Java class. The Guest account Password is stores in System Profile. You can get by using
Select APPS.FND_PROFILE.VALUE('GUEST_USER_PWD') from dual.
Load Balancer in Oracle Apps 11i , E-Business Suite
What is Load Balancer in Oracle Applications 11i ?
As name suggest its used to balance load on oracle applications, so lets say you initially sized (How to size server configuration in your apps 11i Instance during Implementation coming soon..) your server for 100 concurrent users & now additional 90 users started using your system , you can configure one more Application Tier node & distribute load of 190 users across two application tier node by configuring load balancer (Currently I am discussing only about Application Tier & once you are comfortable with Middle Tier I'll discuss on database tier)
Why I need load balancer in Apps 11 ?
Is Increased load only reason for introducing load balancer in Apps 11i , No not at all . There are other reasons as well like (These are more advanced reasons & if you don't understand don't worry read it for understanding purpose at this minute)
- You want to Hide Server Name so instead of users accessing it via http://hostname:port you want users to access by http://somefancyname.com use load balancer (I know you can do this by various other ways as well like reverse proxy, via dns ..)
- You want on some node only web server & some both form server & web server
and there can lot of other reason why you need load balancer (May be you want to put it in your CV that you know how to configure load balancer, I know this is stupid reason :) )
What is Role of Load balancer in Oracle Application 11i ?
Usually load balancer act as mediator in passing your request to respective Middle Tiers (Usually web server ), so lets say you have configured load balancer in front of two middle tier which are part of your Applications 11i lets call them MT1 & MT2 . Load balancer will accept client's request and pass on to MT1 & MT2 ( round robin or the way you have configured load balancer, you here is Unix Administrators)
What are different kind of load balancer ?
Session Persistent - These kind of load balancers assign request from a client to same Application Tier. so lets say user1 tried connecting applications via load balancer and LB sent that request to MT1 then in future LB will send requests from user1 always to MT1 for time being specified in session persistence duration parameter specified in LB configuration or till user logs out from Applications. This property is called as Stickness or sticky Bit
Non Session Persistent - These kind assign request to MT in round robin way so lets assume user1 requested for login page , LB sends this request to MT1 then user1 again clicked on user responsibility and this time LB sent request from user1 to second middle tier i.e. MT2
Other types of LB, I am not discussing here as these are not required at this minute
What all different kind of load balancing options available in Oracle 11i ?
DNS Level Load Balancing ( Check image at top , this represent DNS level LB)
HTTP Layer Load balancing (You need hardware load balancer which accepts http requests)
Jserv Load balancing (This is example of software load balancer, via mod_oprocmgr Process Manager Module)
Forms level load balancing - Jserv load balancing via forms servlets (More coming in future) , forms metrics server adfmsctl.sh
This is HTTP Server level Hardware Load balancer.
Before configuring Load balancer in Oracle Apps , you should be familiar with following technologies
1. Cloning
Check my previous post to know about cloning
Cloning Basics
Cloning Part I
Cloning Part II
Metalink Note # 230672.1 Cloning Oracle Applications Release 11i with Rapid Clone
2. Load Balancer Basics
Check my previous post for Over of Load balancer
Load Balancer Overview
3. Autoconfig
Check About Autoconfig on My Site
Metalink Note 165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i
Broad Level steps for configuring Hardware Load balancer at HTTP Server layer
A. Configure Hardware Load Balancer for Session Persistence
B. Apply Prereq. Patch (If you are not on 11.5.10 or higher)
C. Add additional Web Node using Rapid Clone
D. Configure Load balancer on Node1
E. Test Load balancer via Node1
F. Configure Load balancer on Node2
G. Test Load balancer via Node2
H. Test Load balancer is sending requests to both nodes when both Web Nodes are Up
Yesterday I posted that you need extra attention while configuring Load balancer at HTTP layer is about Session Persistence - Hardware level Load Balancer should be configured for session persistence also called as stickness or sticky bit for Oracle Apps 11i . If this is not set & lets assume client1 first request went to Node1 after authentication if next request for client1 goes to Node2 who is not aware of client1 's previous request will throw authentication again. Don't worry about how to set it, ask vendor or Unix Administrator to set sticky bit or session persistence .
Now you have broad overview of configuring Load balancer at HTTP layer, I'll discuss in detail why you need to apply above patches & what all step by step configuration and parameters which needs to be changed and reasoning behind those changes . Till then check for Cloning, Autoconfig.
I am going to cover most common load balancer setup in Oracle Apps 11i i.e. Setup Two or more Middle tier with Web Server & Form Server with Hardware Load balancer in front of middle tier . This type of setup is also called as "HTTP Layer Hardware Load balancing" in Oracle Applications.
For overview & basics of Load balancing visit my previous post at
http://becomeappsdba.blogspot.com/2006/09/configure-load-balancer-in-oracle-apps.html
http://becomeappsdba.blogspot.com/2006/09/configure-hardware-load-balancer-at.html
Below are steps you need to follow to configure Multiple Middle Tier (Web & Forms Server ) with Hardware Load balancer in front.
1. Setup a Load balancer such that it forwards requests to first middle tier only.
2. Make sure sticky bit is set on load balancer for session persistence (Check Load balancer documentation), You can Network level switch as well to send requests to multiple middle tier
3. Apply Prereq. patches (3175852, 3077386, 3209878)
4. Edit following entry in XML or Context File directly ($APPL_TOP/admin/$CONTEXT_NAME.xml) or via Oracle Application Manager
-- 4.1 "Web entry point Host" to the HTTP load-balancer machine name
-- 4.2 "Web entry point Domain" to the HTTP load-balancer domain name
-- 4.3 "Web entry protocol" to the HTTP load-balancer protocol e.g. "http" or "https"
-- 4.4 "Active Web Port" to the value of the HTTP load-balancer's external port
-- 4.5 "Login Page" to include "Web entry protocol"://"Web Host entry point"."Web domain entry point":"Active Web Port"
-- 4.6 "disco_machine" to load balancer machine
5. Run Autoconfig
6. Start Services & check if you can login to applications via Load balancer
7. Now clone this middle tier to another node (where you want to add another node)
8. Make sure above parameter are pointing to load balancer in second node as well
9. Change load balancer to point to second node only
10. shutdown services on first node
11. try accessing application from second node (you should be able to access application without issues)
11.1 -- If forms are not opening properly , check "ICX Form Launcher .." Profile option should point to load balancer
12. Now start application on both middle tier
13. Point load balancer to both middle tier
14. You should be able to access applications
As name suggest its used to balance load on oracle applications, so lets say you initially sized (How to size server configuration in your apps 11i Instance during Implementation coming soon..) your server for 100 concurrent users & now additional 90 users started using your system , you can configure one more Application Tier node & distribute load of 190 users across two application tier node by configuring load balancer (Currently I am discussing only about Application Tier & once you are comfortable with Middle Tier I'll discuss on database tier)
Why I need load balancer in Apps 11 ?
Is Increased load only reason for introducing load balancer in Apps 11i , No not at all . There are other reasons as well like (These are more advanced reasons & if you don't understand don't worry read it for understanding purpose at this minute)
- You want to Hide Server Name so instead of users accessing it via http://hostname:port you want users to access by http://somefancyname.com use load balancer (I know you can do this by various other ways as well like reverse proxy, via dns ..)
- You want on some node only web server & some both form server & web server
and there can lot of other reason why you need load balancer (May be you want to put it in your CV that you know how to configure load balancer, I know this is stupid reason :) )
What is Role of Load balancer in Oracle Application 11i ?
Usually load balancer act as mediator in passing your request to respective Middle Tiers (Usually web server ), so lets say you have configured load balancer in front of two middle tier which are part of your Applications 11i lets call them MT1 & MT2 . Load balancer will accept client's request and pass on to MT1 & MT2 ( round robin or the way you have configured load balancer, you here is Unix Administrators)
What are different kind of load balancer ?
Session Persistent - These kind of load balancers assign request from a client to same Application Tier. so lets say user1 tried connecting applications via load balancer and LB sent that request to MT1 then in future LB will send requests from user1 always to MT1 for time being specified in session persistence duration parameter specified in LB configuration or till user logs out from Applications. This property is called as Stickness or sticky Bit
Non Session Persistent - These kind assign request to MT in round robin way so lets assume user1 requested for login page , LB sends this request to MT1 then user1 again clicked on user responsibility and this time LB sent request from user1 to second middle tier i.e. MT2
Other types of LB, I am not discussing here as these are not required at this minute
What all different kind of load balancing options available in Oracle 11i ?
DNS Level Load Balancing ( Check image at top , this represent DNS level LB)
HTTP Layer Load balancing (You need hardware load balancer which accepts http requests)
Jserv Load balancing (This is example of software load balancer, via mod_oprocmgr Process Manager Module)
Forms level load balancing - Jserv load balancing via forms servlets (More coming in future) , forms metrics server adfmsctl.sh
This is HTTP Server level Hardware Load balancer.
Before configuring Load balancer in Oracle Apps , you should be familiar with following technologies
1. Cloning
Check my previous post to know about cloning
Cloning Basics
Cloning Part I
Cloning Part II
Metalink Note # 230672.1 Cloning Oracle Applications Release 11i with Rapid Clone
2. Load Balancer Basics
Check my previous post for Over of Load balancer
Load Balancer Overview
3. Autoconfig
Check About Autoconfig on My Site
Metalink Note 165195.1 Using AutoConfig to Manage System Configurations with Oracle Applications 11i
Broad Level steps for configuring Hardware Load balancer at HTTP Server layer
A. Configure Hardware Load Balancer for Session Persistence
B. Apply Prereq. Patch (If you are not on 11.5.10 or higher)
C. Add additional Web Node using Rapid Clone
D. Configure Load balancer on Node1
E. Test Load balancer via Node1
F. Configure Load balancer on Node2
G. Test Load balancer via Node2
H. Test Load balancer is sending requests to both nodes when both Web Nodes are Up
Yesterday I posted that you need extra attention while configuring Load balancer at HTTP layer is about Session Persistence - Hardware level Load Balancer should be configured for session persistence also called as stickness or sticky bit for Oracle Apps 11i . If this is not set & lets assume client1 first request went to Node1 after authentication if next request for client1 goes to Node2 who is not aware of client1 's previous request will throw authentication again. Don't worry about how to set it, ask vendor or Unix Administrator to set sticky bit or session persistence .
Now you have broad overview of configuring Load balancer at HTTP layer, I'll discuss in detail why you need to apply above patches & what all step by step configuration and parameters which needs to be changed and reasoning behind those changes . Till then check for Cloning, Autoconfig.
I am going to cover most common load balancer setup in Oracle Apps 11i i.e. Setup Two or more Middle tier with Web Server & Form Server with Hardware Load balancer in front of middle tier . This type of setup is also called as "HTTP Layer Hardware Load balancing" in Oracle Applications.
For overview & basics of Load balancing visit my previous post at
http://becomeappsdba.blogspot.com/2006/09/configure-load-balancer-in-oracle-apps.html
http://becomeappsdba.blogspot.com/2006/09/configure-hardware-load-balancer-at.html
Below are steps you need to follow to configure Multiple Middle Tier (Web & Forms Server ) with Hardware Load balancer in front.
1. Setup a Load balancer such that it forwards requests to first middle tier only.
2. Make sure sticky bit is set on load balancer for session persistence (Check Load balancer documentation), You can Network level switch as well to send requests to multiple middle tier
3. Apply Prereq. patches (3175852, 3077386, 3209878)
4. Edit following entry in XML or Context File directly ($APPL_TOP/admin/$CONTEXT_NAME.xml) or via Oracle Application Manager
-- 4.1 "Web entry point Host" to the HTTP load-balancer machine name
-- 4.2 "Web entry point Domain" to the HTTP load-balancer domain name
-- 4.3 "Web entry protocol" to the HTTP load-balancer protocol e.g. "http" or "https"
-- 4.4 "Active Web Port" to the value of the HTTP load-balancer's external port
-- 4.5 "Login Page" to include "Web entry protocol"://"Web Host entry point"."Web domain entry point":"Active Web Port"
-- 4.6 "disco_machine" to load balancer machine
5. Run Autoconfig
6. Start Services & check if you can login to applications via Load balancer
7. Now clone this middle tier to another node (where you want to add another node)
8. Make sure above parameter are pointing to load balancer in second node as well
9. Change load balancer to point to second node only
10. shutdown services on first node
11. try accessing application from second node (you should be able to access application without issues)
11.1 -- If forms are not opening properly , check "ICX Form Launcher .." Profile option should point to load balancer
12. Now start application on both middle tier
13. Point load balancer to both middle tier
14. You should be able to access applications
Subscribe to:
Posts (Atom)
