Latches

INTRODUCTION
Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.
A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).
Oracle latch is actually a spin mutexes on process level which usedin most cases to guard the SGA fixed and heap based data structures from being changed every time by more than just a single process

Latch present a contrast with enqueues which always protect structures which has some disk based equivalent such as database objects, data blocks or table rows.Another significant difference between latch and enqueue In latches there is no ordered queue of waiters like in enqueues. Latch waiters may either use timers to wakeup and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get.

A process acquires a latch when working with a structure in the SGA (System Global Area). It continues to hold the latch for the period of time it works with the structure. The latch is dropped when the process is finished with the structure. Each latch protects a different set of data, identified by the name of the latch. Oracle uses atomic instructions like "test and set" for operating on latches. Processes waiting to execute a part of code for which a latch has already been obtained by some other process will wait until the latch is released. Examples are redo allocation latches, copy latches, archive control latch etc. The basic idea is to block concurrent access to shared data structures. Since the instructions to
set and free latches are atomic, the OS guarantees that only one process gets it. Since it is only one instruction, it is quite fast. Latches are held for short periods of time and provide a mechanism for cleanup in case a holder dies abnormally while holding it. This cleaning is done using the services of PMON.



Latches request can be made in two modes: "willing-to-wait" or "no wait". Normally,
latches will be requested in "willing-to-wait" mode. A request in "willing-to-wait" mode will loop, wait, and request again until the latch is obtained. In "no wait" mode the process request the latch. If one is not available, instead of waiting, another one is requested. Only when all fail does the server process have to wait.
Examples of "willing-to-wait" latches are: shared pool and library cache latches
A example of "no wait" latches is the redo copy latch.

LATCH CONTENTION

If a required latch is busy, the process requesting it spins, tries again and if still not available, spins again. The loop is repeated up to a maximum number of times determined by the initialization parameter _SPIN_COUNT.
If after this entire loop, the latch is still not available, the process must yield the CPU and go to sleep. Initially is sleeps for one centisecond. This time is doubled in every subsequent sleep. This causes a slowdown to occur and results in additional CPU usage, until a latch is available. The CPU usage is a consequence of the "spinning" of the process. "Spinning" means that the process continues to look for the availability of the latch after certain intervals of time,
during which it sleeps.

More information on latches

V$LATCH
V$LATCHHOLDER
V$LATCHNAME
Each row in the V$LATCH table contains statistics for a different type
of latch. The columns of the table reflect activity for different types
of latch requests. The distinction between these types of requests is
whether the requesting process continues to request a latch if it
is unavailable:

V$LATCHNAME key information:
GETS Number of successful willing-to-wait requests for
a latch.
MISSES Number of times an initial willing-to-wait request
was unsuccessful.
SLEEPS Number of times a process waited a requested a latch
after an initial wiling-to-wait request.

IMMEDIATE_GETS Number of successful immediate requests for each latch.
IMMEDIATE_MISSES Number of unsuccessful immediate requests for each latch.


Calculating latch hit ratio

To get the Hit ratio for latches apply the following formula:
"willing-to-wait" Hit Ratio=(GETS-MISSES)/GETS
"no wait" Hit Ratio=(IMMEDIATE_GETS-IMMEDIATE_MISSES)/IMMEDIATE_GETS
This number should be close to 1. If not, tune according to the latch name

Useful SQL scripts to get latch information


-Display System-wide latch statistics.

column name format A32 truncate heading "LATCH NAME"
column pid heading "HOLDER PID"
select c.name,a.addr,a.gets,a.misses,a.sleeps,
a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+)
and a.latch# = c.latch#
order by a.latch#;

-Given a latch address, find out the latch name.

column name format a64 heading 'Name'
select name from v$latchname a, v$latch b
where b.addr = '&addr'
and b.latch#=a.latch#;

-Display latch statistics by latch name.

column name format a32 heading 'LATCH NAME'
column pid heading 'HOLDER PID'
select c.name,a.addr,a.gets,a.misses,a.sleeps,
a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+) and a.latch# = c.latch#
and c.name like '&latch_name%' order by a.latch#;




List of all the latches

Oracle versions might differ in the latch# assigned to the existing latches.
The following query will help you to identify all latches and the number assigned.

column name format a40 heading 'LATCH NAME'
select latch#, name from v$latchname;


SPIN_COUNT controls how many times the process will re-try to obtain the latch before backing off and going to sleep. This basically means the process is in a tight CPU loop continually trying to get the latch for SPIN_COUNT attempts. On a single CPU system if an Oracle process tries to acquire a latch but it is held by someone else the process will release the CPU and go to sleep for a short period before trying again. However, on a multi processor system (SMP) it is possible that the
process holding the latch is running on one of the other CPUs and so will potentially release the latch in the next few instructions (latches are usually held for only very short periods of time). Performance can be adjusted by changing the value of SPIN_COUNT. If a high value is used, the latch will be attained sooner than if
you use a low value. However, you may use more CPU time spinning to get the latch if you use a high value for SPIN_COUNT.

Enqueue

The oracle server uses diffrent type of locks to prevent concurrent access to the data and to prevent destructive interference between the uses

Following types of locks are available in Oracle Server
a. Data Manipulation Locks: its protect the data.Table locks protects the entire table while the row lock protect the selected row

b.Data Doctionary locks-Its protect the schema defination of the tables ,views ,procudure etc
c.Internal locks and latches:Its protect the internal database structure such as datafiles,tablespaces
d. PCM locks:These locks protect the data in Real Application Clusters

These all locks are implemented with the help of enqueue and latch service in Oracle server

Enqueue:

Enqueue are the locking mechanism used for managing access to shared resources like table,redo thread,transactions.These are local locks to seralize the access to the shared resource.

An enqueue can be requested in different levels/mode: null, row share, row exclusive, share, share row exclusive or exclusive. If a session holds an enqueue in share mode, other sessions can then also take the enqueue in share mode (for the same resource). If a session holds an enqueue in exclusive mode, other sessions that wants to get it - independently in which level - they have to wait.

When access is required by a session, a lock structure is obtained and a request is made to acquire access to the resource at a specific level (mode) is made. The lock structure is placed on one of three linked lists which hang off of the resource,called the OWNER (if the enqueue could be acquired), WAITER (if the session is waiting to acquiring the enqueue) and CONVERTER (the session is holding the enqueue in one level but wants to convert it to
another) lists.

An enqueue is uniquely defined by it's TYPE, ID1 and ID2 (which are columns in the V$LOCK view). For example can there only be one enqueue for user SCOTT's EMP table (identified by TYPE=TM, ID1=object ID, ID2=0).

Common types of enqueues

JQ - Job Queue. When a job (submitted by DBMS_JOB.SUBMIT) is running, it is protected by a JQ enqueue (which means that only one SNP-process
can run the job).

ST - Space management Transaction. The ST enqueue is need to be held every time the session is allocating/deallocating extents (which means
wants to change the UET$ and FET$ dictionary tables), like coalescing,
drop/truncate segments and disk -sorting. If the session gets a timeout
when requesting the ST enqueue, "ORA-1575 timeout waiting for space
management" is returned.

TM - DML (Table) enqueue Every time a session wants to lock a table, a TM enqueue is requested. If a session deletes a row in the parent-table
(DEPT) and a referential constraint (foreign key) is created without
an index on the child-table (EMP), or if the session is updating the
column(s) that the foreign key references to then a share lock (level 4)
is taken on the child table. If another session tries to do changes to
the child-table they have to wait (because they want the enqueue in row
exclusive mode, and that is not compatible with the share mode). If an
index is created on the child-table?s foreign key-column, then no share-
lock is required on the child-table.


TX - Transaction. As soon as a transaction is started a TX enqueue is needed. A transaction is uniquely defined by the rollback segment number,
the slot number in the rollback segment?s transaction table and the slot
number?s sequence number. A session can be waiting on a TX enqueue for
several reasons: 1) Another session is locking the requested row.
2) When two sessions tries to insert the same unique key into a table
(none of them has done a COMMIT), then the last session is waiting for
the first one to COMMIT or ROLLBACK. 3) There are no free ITL (Interested
Transaction List) in the block header (increase INI_TRANS och PCT_FREE
for the segment).


UL - User Lock. A session has taken a lock with the DBMS_LOCK.REQUEST function.


More info

- When a session is waiting on enqueue ,it can be found to be waiting on enqueue wait event in v$session_wait

SELECT * FROM v$session_wait WHERE event='enqueue';

-To see if the instance has had locking problems since startup, execute the
following SELECT:

SELECT *
FROM v$sysstat
WHERE class=4;

STATISTIC# NAME CLASS VALUE
22 enqueue timeouts 4 0
23 enqueue waits 4 2
24 enqueue deadlocks 4 0
25 enqueue requests 4 213
26 enqueue conversions 4 0
27 enqueue releases 4 204

-"enqueue timeouts" shows the total number of enqueue operations (get
and convert) that timed out before they could complete.

-"enqueue waits" shows how many times a session had to wait for an
enqueue. This column is updated first after the wait is finished.

- "enqueue deadlocks" is how many times a deadlock situation has
occured (every time the client receives an ORA-60 and a trace file
will be created). This value should be zero, else an investigation
should be made and the trace files should be checked.

- "enqueue requests" minus "enqueue releases" shows how many locks that
are held just now (which is equal to the number of rows in V$LOCK).

-"enqueue conversions" is how many times an session is holding a lock
in one mode and then wants to change the mode (for example, first the
session is doing an SELECT * FROM emp FOR UPDATE and then a UPDATE emp
SET sal=9876 WHERE empno=7839).

-"enqueue releases" shows how many times an enqueue was released (freed).

-v$lock contains a list of all the locks in the system

LMODE column :shows in which mode the session is holding the lock.
0=None, 1=NULL, 2=Row share (SS), 3=Row exclusive (SX), 4=Share (S),
5=Share row exclusive (SSX), 6=Exclusive (X)

REQUEST column: LOck mode that is requested if RRQUEST > 0 the some lock is requested

LMODE > 0 and REQUEST=0 then it is owner of the resource
LMODE=0 and Request >0 then it is waiting for the resource
If a session has values greater than 0 in both LMODE and REQUEST, then the
session is a Converter.

For the lock type TM (table lock), column ID1 is the object_id (or the
data_object_id) for that table (could be joined with DBA_OBJECTS). For a
lock of type TX (transaction lock), TRUNC( ID1 / 65536 ) shows which
rollback segment that is used for the transaction and ID1 -
( rollbacksegment# * 65536 ) shows which slot number in the transaction
table that this sessions is using (or want to used).

A lots of view have been created accross to take a locking issues.

Following could be used for that purpose

-To see waiters from dba_waiters view

set linesize 1000
column waiting_session heading 'WAITING|SESSION'
column holding_session heading 'HOLDING|SESSION'
column lock_type format a15
column mode_held format a15
column mode_requested format a15

select
waiting_session,
holding_session,
lock_type,
mode_held,
mode_requested,
lock_id1,
lock_id2
from
dba_waiters;




-This script gives information about the user sessions locking a particular object

set linesize 1000
column program format a15
column object format a15
select substr(username||'('|| se0.sid||')',1,5) "User Session",
substr(owner,1,5) "Object Owner",
substr(object,1,15) "Object",
se0.sid,
substr(serial#,1,6) "Serial#",
substr(program,1,15) "Program",
logon_time "Logon Time",
process "Unix Process"
from v$access ac, v$session se0
where ac.sid = se0.sid
and Object = '&PACKAGE'
order by logon_time,"Object Owner","Object"
/


To find locks in a RAC instance

SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request
;

Forms in R12

Features of Form

-forms are deployed as a one One OC4J instance of 10.1.3 Oracle home
-It uses the utlity from 10.1.2 Oracle home/bin
-Forms.EAR 10.1.2 is deployed to the OC4J container in Application Server 10.1.3
-Run time form executabale f60webmx has been replaced by frmweb
-Forms Servlet is default deployment / communication mode
-FORMS_ environment variables replace FORMS60_
-New environment variables e.g. FORMS_TRACE_DIR
-$ORACLE_HOME/bin/frmbld.sh replaces f60desm for design
-frmcmp.sh and frmcmp_batch for generation



Forms Servlet Architecture
The Forms Listener Servlet is responsible for managing the Forms Runtime processes, and routing all communication with the clients. All traffic between the Forms client applet and the Forms server runtime process is now routed via the Apache listener and the Forms Listener Servlet.

-The URL generated by the Forms Applet will run the Forms Listener Servlet. The Oracle http server (Apache) listener receives the request, recognizes it as a request to run a servlet and delegates it to mod_oc4j to execute.
-Mod_oc4j passes the request on to the Forms Listener Servlet(Forms OC4J instance). The Forms Listener Servlet forks a new forms runtime process (frmweb).
-The Forms message layer sends back a message containing the form meta data, and data required to display the UI. The message structure is the same as used by the Forms Listener Process, however this time it’s returned via the forms listener servlet and apache listener.
-The Forms Servlet wraps the Forms message data as http (using http tunneling) and sends it back to the client via the Apache listener.

All subsequent Forms client <-> Forms server communication follow the same path.

To migrate to socket mode architecture in forms on R12
execute: $FND_TOP/bin/txkrun.pl -script=ChangeFormsMode -mode=socket
For more details see Oracle MetaLink Note 384241.1


More post about R12
R12 Introduction
How to find R12 components version

INST_TOP decoded

With R12 ,Oracle has introduced a new Top call INST_TOP.

Features on this top

-All the logs files have been moved from APPL_TOP,ORACLE_HOME to this to top

- All configuration files have been moved to INST_TOP
like
DBc file
All the http related config files
appsweb.cfg
All the start or stop scripts
all the OC4J related config files

-we can now share the Apps and technology stack among multiple
nodes and even instances (Dev,Test, Production) as all the log and config are located in this top.We will have INST_TOP for each context.

-Configuration and log files are co-located

-Easier collection of instance state
-Minimize disk usage and maximize performance


Filesystem look
/inst/apps/ ($INST_TOP)
• /admin
• /scripts ($ADMIN_SCRIPTS_HOME) All the start and stop are here
• /appl ($APPL_CONFIG_HOME) All the apps env are here
• /fnd/12.0.0/secure ($FND_SECURE) dbc file lis located here
• /certs
• /logs ($LOG_HOME) all the logs are located
• /ora ($ORA_CONFIG_HOME)
/10.1.2
/10.1.3 ($ORACLE_CONFIG_HOME) all the techstack(oacore,forms) related config files
• /pids
• /portal
• /rgf


Some more changes to variable

OA_HTML =$COMMON_TOP/webapps/oacore/html
JAVA_TOP=$COMMON_TOP/java/classes
AF_JLIB=$COMMON_TOP/java/lib
JAVA_BASE=$COMMON_TOP/java/

How to find R12 components Version

Apache Version

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v



Perl Version

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built


Java Version

sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

Jre version
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

Forms Version

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Plsql Version

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication mode

cat $FORMS_WEB_CONFIG_FILE|grep serverURL=
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

Please take a look at following post for more information
R12 Introduction
INST_TOP Decoded

Introduction to R12

Technology Change
New Versions of Mid Tier Technology
• Application Server: 1.0 -> 10.1
• Servlet Container: JServ -> OC4J
• Apache: 1.3.19 -> 1.3.34
• Forms & Reports: 6 -> 10
• R12 File System Change:
• The two 11i middle tier Oracle homes are replaced with
newer versions
• Instance Home vs. Source Home

Components Version
11i
Java Home: 8.1.7-based iAS 1.0.2
C Home: 8.0.6-based Developer 6
Database Home: 10.2
Forms : Developer 6


R12
Java Home: 10.1.3-based iAS 1.0.2
C Home: 10.1.2-based Developer 10
Database Home: 10.2
Forms : Forms 10g (10.1.2)
New Instance Top

More information-Jserv has been replaced by Oc4J
-Forms uses servlet mode instead of socket mode in 11i
-mod-plsql is not used in R12


Please take a look at following post for more information on R12

How to find R12 components version
INST_TOP Decoded

Tuning Tools

Explain Plan
-Shows the execution path of a SQL statement
-indicates what tables it will read first and in what order
-indicates what indexes it will use, or if new indexes are needed
-Helps verify that Oracle will retrieve the data the way you expect it to

How to?
Must have full access to a PLAN_TABLE or create PLAN_TABLE with utlxplan.sql delivered by Oracle

Insert the following command at the top of your SQL statement

explain plan
set statement_id = 'x'
into plan_table
for
;


select lpad(' ',2*level) || operation operations,options,object_name
from plan_table
where statement_id = 'x'
connect by prior id = parent_id and statement_id = 'x'
start with id = 1 and statement_id = 'x'
order by id;



How to Read?
-Read innermost out, top/down
-Look for TABLE ACCESS… (FULL) …
-Costly if table is big
-Costly if performed many times
-Look for INDEX… (RANGE SCAN)…
-Expected for non-unique indexes
-Suspicious for unique indexes

Some info about joins
Nested Loops-Good for on-line screens and reports
-Read all rows from table 1
-Then access table 2 once for each row returned from table 1
-Fastest if:
rows returned from table 1 are small
access to table2 is inexpensive. This means either a UNIQUE lookup or a SMALL Range Scan.

Merge Join
-Better than nested loops for joining a large number of rows

Hash Join-Common in Parallel Queries
-Better than Merge Join
-Smallest table is passed over and a hashing algorithm is applied to each row to create a hash table and a bitmap.
Second table is passed over and a hashing algorithm applied and check for matchs (ie. joins)
The bitmap is used as a quick lookup and is especially useful when hash table is too large to fit in memory.

Sort Merge Join
-Rows are produced from table 1 and are then sorted
-Rows are produced from table 2 and sorted by the same sort key as table 1
-Table 1 and 2 are NOT accessed concurrently
-Sorted rows from both sides are then merged together(joined)


Autotrace
Similar to Explain Plan
Provides plan - without having to perform separate query from PLAN_TABLE
Provides statistics
Note: Does not support bind variables

Syntax
set autotrace { off | on | traceonly } [explain] [statistics]
Autotrace
SET AUTOTRACE OFF
default.
SET AUTOTRACE ON EXPLAIN
shows only the optimizer execution path
SET AUTOTRACE ON STATISTICS
shows only execution statistics
SET AUTOTRACE ON
includes both the plan and the statistics
SET AUTOTRACE TRACEONLY
same as ON but suppresses query output

Autotrace-Execution Plan produced by Autotrace contains the following:
Line number of each execution step
The relationship number between each step and its parent
Each step of the plan
shows any database links or parallel servers used
-TKPROF discussed next will show row counts whereas Autotrace does not!
Autotrace: Example


Trace Files and TKPROF

Regular Trace
Contains SQL, execution statistics, and execution plan

Advantages
Provides execution path
Provides row counts
Produces smallest flat file


Disadvantages
Cannot tell what values were supplied at run time to SQL
If statistics for query are low but run time is long cannot tell what events caused the long wait

Trace with Binds(level 4)
Regular Trace plus values supplied to the SQL statement via the local variables (i.e. binds)
Advantages
Provides execution path
Provides row counts
Can tell what values the statement was run for

Disadvantages
Again if run time is long and statistics are low it will be hard to tell why in this type of trace.
Produces a bigger flat file that the regular trace because bind variable information has to be stored.


Trace with Waits (level 8)
Regular Trace plus database operation timings that the SQL waited to have done in order to complete. For example: disk access timings.
Advantages
Provides execution path
Provides row counts
Can tell timings for all events associated with SQL.


Disadvantages
Trace file may easily max out due to all the information Oracle must write to the trace file and then only partial information is available in trace file.
Bind variable information is not available

Trace with Binds and Waits(level 12)
Regular trace with the both the wait and bind information. Contains the most complete information and will produce the largest trace file.

Advantages
Provides execution path
Provides row counts
Can tell timings for all events associated with SQL.
Can tell what values the SQL was run with


Disadvantages
Trace file may easily max out due to all the information Oracle must write to the trace file and then only partial information is available in trace file.






Formatting Your SQL Trace File with TKPROF
Trace Files are unformatted dumps of data
TKPROF is tool used to format trace files for reading
Syntax
tkprof {tracefile} {outputfile}
[explain={user/passwd} ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
TKPROF Usage “Best Practices”
The following is the recommended usage:
tkprof {trace file} {output file}explain={usernm/passwd} print=?sort=prsela,exeela,fchela
Use “explain=…” to get Plan in the report
Use “print=…” to only report on the first “?” statements
Use “sort=…” for sorting the statements with the longest elapsed times first (works with timed_statistics=true)


Some more command related to tracing
1. To trace any sid from outside
sys.dbms_system.set_ev(sid, serial#, , , '')
Examples: SQL> execute sys.dbms_system.set_ev(8, 219, 10046, 12, '');

2.Gathering stats for any object in APPS
exec apps.fnd_stats.GATHER_TABLE_STATS('APPLSYS','FND_CONCURRENT_REQUESTS',100,4);

3. Using oradebug
oradebug setospid
oradebug close_trace

oradebug setospid
oradebug event 10046 trace name context off;
oradebug event 10046 trace name context forever, level 4;

4.Using tkprof
This print 10 sql only
tkprof .trc elaps.prf sys=no explain=apps/ sort=(prsela,exeela,fchela) print=10
This print all the sql
tkprof .trc elaps.prf sys=no explain=apps/apps sort=prsela,exeela,fchela

Java Workflow Mailer

Workflow notification mailer
The Workflow Notification Mailer performs email send and response processing for the Oracle Workflow Notification System.
By deploying the Workflow Notification Mailer workflow users receive their notifications by email, as well as from the Worklist web pages.

Java based Workflow Notification Mailer - Features

Java Based Workflow Notification Mailer is indroduced in the OWF.G mini-pack with Following features.
1. Only one Workflow Mailer to run on all platforms
2. Based on standard Java Mail APIs
3. Sends outbound mail via SMTP
4. Receives inbound mail via IMAP
5. Configured using the Oracle Workflow Manager
6. One Workflow Mailer for detail and summary emails
7. Access Log Files via Browser
8. No flat files to configure
9. No directory permissions to worry about

Comparison between C based and Java based mailer


'C' based Workflow Mailer eMail Processing
1. Mail for the instance is delivered to the server mail queues
2. Workflow Mailer Process reads the mail queue files directly and processes them.
3. After processing the mail traffic, each mail is stored in a file according to the action taken by the Workflow Notification Mailer


Java based Workflow Mailer emailProcessing
1. Mail is delivered to the server mail queues
2. Workflow Notification Mailer connects to the IMAP service
3. Incoming E Mail is retrieved by the IMAP service
4. Workflow Notification Mailer processes the E Mail responses
Workflow Notification Mailer stores processed E Mail, via the IMAP service, in predefined E Mail Folders

Troubleshooting Java Mailer

Verify Outbound Processing
1 Verify Agent Listeners are running
2 Go to the "View Details" screen for the Mailer you are configuring
3 Verify that Mailer is running
4 Click on the "Test Email" button
5 Enter a role using the LOV
6. The test address will override that role’s email address
7. Click Send, write down the Notification Id just in case
8. Verify your email account receives this email
Verify Inbound Processing
1 Send an e-mail to the Workflow Mailer reply to address
2 Subject "Hello World"
3 Body "Hello World"
4. The mailer should process this email and send back an FYI "unsolicited email" message to your email account

Read followings link for more information regarding Application
Adpatch Part I
Adpatch Part II
Adpatch Part III
Ad Utilities Part I
Ad Utilities Part II
Ad Utilities Part II
troubleshoot Ad Utilities
Jserv
Jserv Architecture
Apache
Guest User password
Forms
Forms Architecture
Forms Fact
Concurrent Manager
Autoconfig
OA Framework Basics
Profile OA framework

Apps Useful script

Script to find out patches applied

select orig_bug_number,to_char(max(last_update_date),'DD-MON-YYYY hh24:mi:ss') updated from ad_patch_run_bugs where
orig_bug_number in
(
select distinct app_patch.patch_name
from (
select substr(ap.patch_name,1,10) patch_name
from ad_patch_runs pr, ad_patch_drivers dr,
ad_applied_patches ap
where pr.start_date between to_date('&&1','MM/DD/YYYY')
and to_date('&&2','MM/DD/YYYY')
and dr.patch_driver_id = pr.patch_driver_id
and ap.applied_patch_id = dr.applied_patch_id
and not exists
(select 'x' from ad_comprising_patches cp
where cp.patch_driver_id = dr.patch_driver_id)
UNION ALL
select substr(b.bug_number,1,10) patch_name
from ad_patch_runs pr, ad_patch_drivers dr,
ad_comprising_patches cp, ad_bugs b
where pr.start_date between to_date('&&1','MM/DD/YYYY')
and to_date('&&2','MM/DD/YYYY')
and dr.patch_driver_id = pr.patch_driver_id
and cp.patch_driver_id = pr.patch_driver_id
and cp.bug_id = b.bug_id) app_patch)
group by orig_bug_number;



Version information

select name, created from v$database;
select * from product_component_version;
select RELEASE_NAME from FND_PRODUCT_GROUPS;



Materlized view


select unusable, known_stale, invalid, REWRITE_ENABLED, mview_name from
sys.dba_mview_analysis where MVIEW_NAME like 'MSC%';


File version in database
set pages 52
set verify off
set term on
set feedback off
col "APP NAME" format a10
col filename format a15
col version format a15
col last_update_date format a15
set linesize 90
select af.app_short_name "APP NAME",
af.filename,
afv.version,
afv.LAST_UPDATE_DATE
from ad_files af,
ad_file_versions afv
where af.file_id = afv.file_id and
af.filename like '&1'
order by afv.LAST_UPDATE_DATE desc
/


Check Apps 11i package versions
select text
from user_source
where line = 2
and name = upper('&enter_package_name')
order by line
/

Check XML file in MDS repository
exec JDR_UTILS.printDocument('/oracle/apps/fnd/framework/navigate/webui/XXXX')
;


Check messages
select message_text from fnd_new_messages m, fnd_application a
where upper('&message_name') = m.message_name
and upper('&language_code') = m.language_code
and upper('&application_short_name') = a.application_short_name
and m.application_id = a.application_id
/


Check profile options
set linesize 140
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_VAL format 999999999
column VALUE format A60 wrap


select
p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
'UnDef') LEVEL_SET,
v.level_value LEVEL_VAL,
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
/

Some Useful Scripts

Script to find the sid of the session you are logged in as

select distinct(sid) from v$mystat;

************************************************************

Script to see all active session
select username,osuser,sid,serial#, program,sql_hash_value,module from v$session where username is not null
and status ='ACTIVE' and module is not null;

*****************************************************************

to see waiters

set linesize 1000
column waiting_session heading 'WAITING|SESSION'
column holding_session heading 'HOLDING|SESSION'
column lock_type format a15
column mode_held format a15
column mode_requested format a15

select
waiting_session,
holding_session,
lock_type,
mode_held,
mode_requested,
lock_id1,
lock_id2
from
dba_waiters
/

*********************************************************************

this is for v$transaction

REM This script joins V$SESSION,V$TRANSACTION & V$ROLLNAME
REM This query will show all users that have active transactions
REM and the rollback segment each user is writing to:
col RBS format a15 trunc
col SID format 9999
col USER format a15 trunc
col COMMAND format a60 trunc
col status format a8 trunc
select r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk, t.noundo,
substr(s.program, 1, 78) "COMMAND"
from v$session s, v$transaction t, v$rollname r
where t.addr = s.taddr
and t.xidusn = r.usn
order by t.cr_get, t.phy_io
/



***********************************************************************************************************************


long running queries

# This script can be used to monitor the
# PROGRESS of sqls which do FULL TABLE SCANS

set linesize 1000
select
OPNAME,
sid,SOFAR/TOTALWORK*100,
to_char(start_time,'dd-mon-yy hh:mi') started,
elapsed_seconds/60,time_remaining/60
from
v$session_longops
where
sid =&sid

*****************************************************************************************************************
to see all lock objects

set term on;
set lines 130;
column sid_ser format a12 heading 'session,|serial#';
column username format a12 heading 'os user/|db user';
column process format a9 heading 'os|process';
column spid format a7 heading 'trace|number';
column owner_object format a35 heading 'owner.object';
column locked_mode format a13 heading 'locked|mode';
column status format a8 heading 'status';
select
substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
l.process,
p.spid,
substr(o.owner||'.'||o.object_name,1,35) owner_object,
decode(l.locked_mode,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Excl',
6,'Exclusive',null) locked_mode,
substr(s.status,1,8) status
from
v$locked_object l,
all_objects o,
v$session s,
v$process p
where
l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
and s.status != 'KILLED'
/


**********************************************************************************************************************

to see waits events
set linesize 1000
column sid format 999
column username format a15 wrapped
column spid format a8
column event format a30 wrapped
column osuser format a12 wrapped
column machine format a25 wrapped
column program format a30 wrapped
select sw.sid sid
, p.spid spid
, s.username username
, s.osuser osuser
, sw.event event
, s.machine machine
, s.program program
from v$session_wait sw
, v$session s
, v$process p
where s.paddr = p.addr
and event not in ('pipe get','client message')
and sw.sid = s.sid
/
**********************************************************************************************************************
session waits


select sid,seq#,wait_time,event,seconds_in_wait,state from v$session_wait where sid in (&sid)


*********************************************************************************************************************

to see all user accesing that objects
column object format a30
column owner format a10
select * from v$access where object='&object_name'
/


******************************************************************************************************************


REM ******************************************************************************************
REM This script gives information about the user sessions locking a particular object
REM ******************************************************************************************
set linesize 1000
column program format a15
column object format a15
select substr(username||'('|| se0.sid||')',1,5) "User Session",
substr(owner,1,5) "Object Owner",
substr(object,1,15) "Object",
se0.sid,
substr(serial#,1,6) "Serial#",
substr(program,1,15) "Program",
logon_time "Logon Time",
process "Unix Process"
from v$access ac, v$session se0
where ac.sid = se0.sid
and Object = '&PACKAGE'
order by logon_time,"Object Owner","Object"
/
*******************************************************************************************************************************
to see sql plan
set linesize 9999
column QUERY format a999
set pages 250
set head off
set verify off
select id,lpad(' ',2*(depth-1)) || depth ||'.' || nvl(position,0) || ' '|| operation || ' '|| options || ' '|| object_name ||' '
||'cost= '|| to_char(cost)||' '|| optimizer "QUERY"
from v$sql_plan
where hash_value = &sql_hash_value
order by child_number,id
/

*************************************************************************************************************************
to find server location


select nvl(username,'ORACLE SHADOW PROCESS'),
machine from
v$session where username is null
and rownum < 2

MLS Installation in 11i

Introduction to NLS/MLS
NLS: National language support. Being able to run an application in 1 language. This language can be any supported language.
It basically means the ability to run an Applications instance in any one of the supported languages (other than American English), including certain region/territory specific numeric and date formats. Only the customer-facing components (i.e. the user-interfaces, lookup tables, online documentation etc.) are translated. The underlying logic stays the same as that for American English.
MLS: Multiple language support. Being able to run an application in more then 1 language at a time.
It means the Application can be successfully run in more than one language on the same Oracle Applications instance.

MLS in Release 11iIn Release 11i, the translatable fields moved from the base table to the translation (_TL) table.So all tables containing translatable strings/fields have a related translation table ( _TL table).
(Though it was started in Release 11.0, only FND, ICX and AK had this functionality. The other modules did not.)
For example, the FND_CONCURRENT_PROGRAMS table NO longer contained the following columns:
USER_CONCURRENT_PROGRAM_NAME NOT NULL VARCHAR2(240)
DESCRIPTION VARCHAR2(240)
instead, these columns were moved to the FND_CONCURRENT_PROGRAMS_TL table.

So the _TL table contains one record for each language i.e the translated string for each language.
Also “ _VL” view has been introduced for the “_TL” table, which allows to select the data based on the session language without running the risk of returning more rows then expected.
So view FND_CONCURRENT_PROGRAMS_VL will return rows as per the session language settings.

A number of profile options have been introduced, which force an “ALTER SESSION”, setting the specified language, numeric character and date format etc for the current user only.

When a user does signon, the profile options that will influence the NLS characteristics for his session are:
ICX_DATE_FORMAT_MASK
ICX_DATE_LANGUAGE
ICX_LANGUAGE
ICX_NLS_SORT
ICX_NUMERIC_CHARACTERS
ICX_TERRITORY

So what happens when you signon is that the profile options are being read and the corresponding NLS variable will be set using an 'alter session' command.
All translatable files reside in a subdirectory which has the language code incorporated in the name (e.g. $AP_TOP/reports/EL, $GL_TOP/forms/F etc.) .

- Forms, Reports, Messages & Seeded data are translated
- FND_LANGUAGES table shows which languages are installed, and which is the base language and which are non-base languages (Installed Languages)


Installation Introduction

The Translation Synchronization patch feature provides a quick way for you to synchronize your existing translations with the American English file versions on your Applications instance. By applying just one patch for each language, you will be able to bring your translations up to your current Applications patch level. You can also choose to get the latest translations to bring your translations up-to-date.
The basic concept involves preparing a single manifest file for your Applications instance. This manifest reflects the language software content for all the active languages on your current Applications system. The manifest will be used to generate customized patches for your system.
This feature reduces the installation time and complexity for synchronizing language installations. It also provides a simple way for you to update your existing translations.


Pre Installation Steps

Prerequisite Patches
a.If you are not on AD mini-pack I (11i.AD.I.2) or later, apply the latest
AD mini-pack from OracleMetaLink. AD patch level report is generated by the following command:
sqlplus /
b.Apply update 5837664. This update delivers the latest tool for generating manifest file as mentioned in section 3. The latest tool will generate a single manifest file with all the active languages in an instance. Download this update and review its readme for instructions to apply it.

Character Set Support
The database character set must support the New Language.If the database is not in a character set that supports the new Language, the database character set as well as the character set in the APPL_TOP must be converted.

Check Space in APPL_TOP
It requires approx 5 GB for each new lnguage. So, check the space available in $APPL_TOP
$ cd $APPL_TOP
$ df –k .



Installation steps

1. Change Language Configuration
Use the OAM License Manager to activate a new language(s).

2. Maintain Multilingual Tables

What it does: It will do the following:
• Create a record with SOURCE_LANG = 'US' for each of the languages that doesn't have a records in the _TL table.
• Update all records where SOURCE_LANG <> LANGUAGE, defaulting the translatable fields to the value found under the SOURCE_LANG
Steps : From the AD Administration main menu, choose the Maintain Applications Database Objects menu, and select the "Maintain the multi-lingual tables" task as described below:
1. Run adadmin
2. Select Option 2 (Maintain Applications Database Objects menu)
3. Select Option 6 (Maintain multi-lingual tables)


3.Maintain Snapshot Information

4.Generate and Upload the Manifest of Customer-specific Files
Log on to your Applications' administration node as the APPLMGR user and source the APPL_TOP environment file. Use the manifest generation tool to generate customer-specific manifest file by executing the following command:

perl $AD_TOP/bin/adgennls.pl

The manifest generated from the tool is in the format :

$APPL_TOP/admin/$TWO_TASK/out/adgennls.txt

The manifest contains all of your active languages. You do not need to run the tool for each individual language.
Once the manifest is generated, you can go to http://updates.oracle.com/TransSync (use your OracleMetaLink username and password).
Follow the instructions on the screen to upload the manifest file. A Translation synchronization patch will be generated specifically based on your manifest for each of your active languages. The patch will synchronize the language file versions and American English file versions in your Applications instance. If you select the option to get latest translations, the patch will also bring your translations up-to-date.

If your current translations are already up-to-date and in sync with your American English file versions, you will be notified by e-mail that there is no need to apply Translation Synchronization patch.

5.Applying Translation Synchronization Patch


Once the customer-specific Translation Synchronization patch is ready, you will receive an e-mail stating its availability. You can then download the patch from OracleMetaLink. Follow the instructions in the README file to apply the patch.
A Merged Translation patch may be generated if full translations are not available.
You can merge multiple language Translation Synchronization Patches and apply the single merged patch to your system. See Oracle Applications Patching Procedures guide for information about merging NLS patches.


Post Installation Steps

1. Run Autoconfig

2. Compile and Genarate from adadmin
• Compile the flexfield data
• Generate message files
• Generate forms and libraries
• Generate Reports

3.
Install font files on the HTTP Server (Conditional)
To enable the Oracle UIX Dynamic Image technology to successfully generate images, the required font files to support the active Applications languages must be available on the HTTP Server node. Oracle Applications provides Unicode font files in the /resource directory and should be installed on HTTP Server.
To satisfy all Applications supported language font requirements including specific East Asian language font requirements, there are actually five fonts supplied: Generic font without East Asian languages, Japanese font, Korean font, Simplified Chinese font and Traditional Chinese font. The fonts are called 'Albany' and they are proportional fonts in ttf format. Disk space required for the font files is approximately 75MBytes.
Steps:
• Copy the font files to the Java default font directory for the external JDK on the HTTP Server node.
$ cp /resource/ALBAN*.ttf /jre/lib/fonts
• Remove Image Files from Cache Directory
Remove all existing non-English language image files from the image cache directory.
$ cd $OA_HTML/cabo/images/cache
$ cd
$ rm *.gif *.imx

4. Restart the Apache service on the HTTP Server node for the updates to take effect.


Read followings links for more information regarding Application
Adpatch Part I
Adpatch Part II
Adpatch Part III
Ad Utilities Part I
Ad Utilities Part II
Ad Utilities Part II
troubleshoot Ad Utilities
Jserv
Jserv Architecture
Apache
Guest User password
Forms
Forms Architecture
Forms Fact
Concurrent Manager
Autoconfig