EBS UPGRADE from 11i(11.5.10.2) to 12.2.5 or 12.2.6 and DB from 11.2.0.4 to 12c

Below Pre-upgrade steps on 12.2 servers can be done well in advance before the actual upgrade downtime.

Pre-upgrade Steps on 12.2
==========================
1. Install R12.2 File system
   a) Run startCD/Disk1/rapidwiz/bin/buildStage.sh
      to Build the stage area of the software from the zip files that are downloaded from edelivery.
   b) Run rapidwiz and choose options as below on the relevant screen
       -- Upgrade to Oracle EBS R12.2.0
       -- Create Upgrade Filesytem
2. Run ETCC on MT
        checkMTpatch.sh
3. Apply The patches recommended by ETCC to
        -- Forms Home
        -- WebTier Home
        -- Oracle Common Home
        -- Weblogic Server
4. Install 12c DB Oracle Home.
5. Run ETCC on DB
        checkDBpatch.sh
6. Apply the recommended patches to 12c DB home

Pre-Upgrade Steps on 11i
=========================
1. One way to start-off, is to take a full backup of Prod and perform pre-upgrade steps directly on Prod.

Otherwise

2. Take a fresh clone of 11i Prod instance.(Recommended process compared to above)

3. Build a DR from the new clone to the R12.2 DB server where R12 instance will be built.
For 11g Home, Use a different location other than the 12c Home installed above.

The concept of DR is nothing related to upgrade but since,
rsync or copy of DB(datafiles) from 11i to R12 will delay the upgrade process. This can be avoided using the DR.

Do Not Activate the Standby database yet.

==== Upgrade Window starts here ============
=====Make a note of all the timings for each step from here on.=========

4. Apply the Minimum Baseline patches on 11i required for the upgrade.
 Merge and apply all the patches to save time.(except AD patches- don't merge AD with others )

5. Handover the instance to Technical and functional teams for their pre-upgrade steps.
 SLA Pre Upgrade program to be completed by them at this point.

6. Other Major pre-upgrade steps include
a) Disable AOL Audit Trail - Run this program
b) Run Gather Schema Statistics for ALL schemas
c) Collect information of DBA DIRECTORIES and DB LINKS.
d) Disable Custom triggers,indexes,constraints.
e) Add datafiles to APPS and SYSTEM tablespaces.


Once the Pre-upgrade steps on 11i are completed by Tech/Func teams, we can ACTIVATE the DR.

DB Upgrade to 12c
==================
1. Ensure DR is in sync with the primary and Activate the Standby database using 11g Home.
2. Perform the pre-DB upgrade steps on 11g home.
a)Copy the below sql's from 12c Home to 11g Home and execute them.

Set ENV to 11g Home and run
SQL> @preupgrd.sql
SQL> @utluppkg.sql 

Preupgrd.sql will create below files.

preupgrade.log : The results of all the checks performed.Check and perform any manual steps that are advised.
preupgrade_fixups.sql : A fixup script that should be run before the upgrade.
postupgrade_fixups.sql : A fixup script that should be run after the upgrade.

b) Run preupgrade_fixups.sql and any manual steps recommended.

3. Perform the DB upgrade using DBUA.

Set ENV to 12c Oracle home.
$ cd $ORACLE_HOME/bin
$ ./dbua.sh

Choose options as below where applicable on the DBUA screens
    -- Upgrade Oracle database
    -- Check and Provide Source Database Home location
    -- Makre sure Pre-req checks are successful
    -- Choose -- Recompile Invalids during post-upgrade and
    -- Choose -- Upgrade timezone Data
    -- Choose -- I have my own backup and restore strategy
   

4. Run postupgrade_fixups.sql
5. Run utlu121s.sql to confirm upgrade went fine.
6. Run adgrants.sql and adstats.sql(Copy from $APPL_TOP/admin)
7. Run Datapatch to update ETCC patches inside Database

cd $ORACLE_HOME/OPatch
./datapatch

8. Delete Source Node information from Database
As APPS user
SQL> exec fnd_conc_clone.setup_clean;
9. Run Autoconfig

12.2.0 Upgrade
===============
1. Update any concurrent requests that are pending scheduled to Hold status.
2. Disable Archive log and set SGA,PGA,SESSIONS and PROCESSES as required for upgrade.
3. Run adgncons.sql from patch 13435302 and perform steps recommended.
4.
a) Merge and Apply the pre-install patches
R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] (Doc ID 1448102.2)

b) Apply the Consolidated Upgrade Patch (CUP) for Release 12.2.0 (Patch 22742071:12.2.0)
in pre-installation mode on the Run Edition File System.

b) Merge and Apply AD CUP7 patches or latest CUP available.
 Apply the AD Upgrade Patch for Release 12.2 (Patch 10117518)
 by merging it with the latest Consolidated Upgrade Patch (CUP) for AD (Patch 22742061:R12.AD.C).

Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1) -- For latest CUP7 and AD CUP7 patches

5. Merge preinstall patches with Upgrade Driver
cd $AU_TOP/patch/115/driver
admrgpch -d . -preinstall -master u10124646.drv

6. Apply 12.2 upgrade patch driver

cd $AU_TOP/patch/115/driver

time adpatch options=nocopyportion,nogenerateportion flags=nologging driver=u_merged.drv logfile=u_r122merged_`echo  $TWO_TASK`_`date '+%d%B%Y_%H_%M_%S'`.log workers=16 
(Provide batch size =30000)

Workers and batchsize depending on the number of CPU's on DB node.

7. Configure Domains to enable Managed Servers
 Run rapidwiz and choose options as below wherever applicable
 -- Upgrade to EBS R12.2.0
 -- Configure Upgraded R12.2.0 and provide the full path to Context File location

8.
a) Start the application services.
b) Wait and make sure all upgrade related concurrent requests are completed.
c) Stop the application services.

Online Patching Enablement
===========================
1. Apply latest Online Patching Enablement Readiness Report Patch 22071026.
2. Run Online Patching Readines report - $AD_TOP/sql/ADZDPSUM.sql.Review the generated Report and fix any issues reported.
3. Run below scripts located at $AD_TOP/sql
ADZDPMAN.sql
ADZDDBCC.sql
ADZDEXRPT.sql
4. Add tablespaces as needed
perl $AD_TOP/bin/adzdreport.pl apps
Choose     3.  Other generic reports
and then  choose   3.  Free space in important tablespaces
5. Make sure AD_ZD objects are all valid.
select owner,object_name,object_type from dba_objects where object_name like 'AD_ZD_%'  and  status='INVALID';
6. Apply the Online Patching Enablement patch 13543062 in hotpatch mode on Run FS.

When AutoPatch prompts with the following question, answer "Y"
This Patch seems to have been applied already.
Would you like to continue anyway  [N] ?   Y

7.
Run Online Patching Status Report,ADZDEXRPT.sql after Online Enablement Patch
Run the Readiness Scripts again
Run the Online Patching Database Compliance Checker report,ADZDDBCC.sql to check for coding standards violations.

12.2.5 Upgrade
===============
1. Copy adgrants.sql from patch 22123818 to DB HOME and run as sysdba.
2. Merge and Apply Bundle fixes and AD.DELTA.7 patches. Apply them in Downtime mode.
R12.AD.C.DELTA.7 PATCH - 20745242
BUNDLE FIXES FOR R12.AD.C.DELTA.7 - 21841288
BUNDLE FIXES II FOR R12.AD.C.DELTA.7 - 22123818
adop phase=apply patches=20745242,21841288,22123818 apply_mode=downtime merge=yes
3. Apply Patch 20784380:R12.TXK.C.Delta.7 merged with Bundle fixes

BUNDLE FIXES FOR R12.TXK.C.DELTA.7 - 21846184
BUNDLE FIXES II FOR R12.TXK.C.DELTA.7 - 22363475
TXK CONSOLIDATED PATCH FOR STARTCD 12.2.0.51 - 22495069

4. Generate and copy appsutil.zip to DB home and unzip using -o option.
5. Run Autoconfig on DB.
6. Apply 12.2.5 Upgrade patch in downtime mode.
adop phase=apply patches=19676458 apply_mode=downtime
7. Apply Online help patch 19676460 in downtime.
8. Apply Latest Apps CPU Patch
9. Run Patch Wizard and Apply recommended patches
10. Startup application services and wait for completion of any upgrade requests and shutdown services.

In case, you want to go with 12.2.6 upgrade instead of 12.2.5 below are the patches required.

For 12.2.6 Upgrade
===================

AD.8 and TXK.8 are the pre-requisites for 12.2.6.

R12.AD.C.Delta.8 (Patch 21841299) and
R12.TXK.C.Delta.8 (Patch 21830810 )
21900901 -  ORACLE E-BUSINESS SUITE 12.2.6 RELEASE UPDATE PACK


Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.8 and R12.TXK.C.Delta.8 (Doc ID 2159750.1)
2114016.1 - 'Oracle E-Business Suite Release 12.2.6 Readme'.

However,
The latest RUPs are R12.AD.C.Delta.9 (Patch 25178222) and R12.TXK.C.Delta.9 (Patch 25180736).

Upgrade JRE if required
=========================

1. unzip p22961063_18077_WINNT.zip
2. mv jre-8u77-windows-i586.exe j2se18077.exe
3. cp j2se18077.exe $COMMON_TOP/webapps/oacore/util/javaplugin
4. Provide 644 permissions to j2se18077.exe                                                                                                    
5. $FND_TOP/bin/txkSetPlugin.sh 18077 ( This will run Autoconfig and will set the new Jre in the Env)

Register any CUSTOM Products
============================
1. Copy the CUSTOM TOP directory from 11i to 12.2 APPL_TOP.
2. Rename the 11.5.0 directory under CUSTOM_TOP to 12.0.0
3. cd 3636980/izu/admin
mv izuprod.txt customprod.txt
mv izuterr.txt customterr.txt
cp newprods.txt $APPL_TOP/admin
cp customterr.txt $APPL_TOP/admin
cp customprod.txt $APPL_TOP/admin
cd $APPL_TOP/admin
vi customprod.txt
Replace all ocurrence of izu with custom  ,IZU with CUSTOM and 278 with 20081

vi customterr.txt
Replace all ocurrence of izu with custom  ,IZU with CUSTOM and 278 with 20081 and  Oracle_Support_Diagnostic_Tools with "CUSTOM".

vi newprods.txt and make
product=custom
oracle_schema=custom

20081 - Product ID should work most of the times,
else check the existing used ones and provide an unused product ID.

select ORACLE_ID,ORACLE_USERNAME from fnd_oracle_userid where ORACLE_ID > 20000;

3. Run adsplice
This runs autoconfig and sets the Env with New product Top (CUSTOM_TOP)

4. Add  entries for CUSTOM_TOP in Adop_sync.drv file  under APPL_TOP_NE
rsync -zr %s_current_base%/EBSapps/appl/custom %s_other_base%/EBSapps/appl
rsync -zr %s_current_base%/EBSapps/comn/java/classes/custom  %s_other_base%/EBSapps/comn/java/classes

5. Upload the New context_file to DB

Source the RUN filesystem                                                                                                                                       
$ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=Enter Full Patch Context XML File Location logfile=/tmp/patchctxupload.log

Run the following query and it should return one entry for the recently uploaded context file.

select distinct(PATH) from FND_OAM_CONTEXT_FILES where NAME not in ('TEMPLATE','METADATA','config.txt') and CTX_TYPE='A' and (status is null or upper(status) in ('S','F')) and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') = 'patch';


Configure Central Inventory
============================
The concept of central inventory will be very useful in cases where there are multiple instances on the same server.
 Normally, inventory is placed in a location which is commonly shared by all the instances on that server. Such configuration poses a lot of problems  during clones.But enabling central inventory moves the inventory to a location specific to each instance, normally, under APPS BASE location for that instance.         
Below process moves the inventory files from existing location to a instance specific location.                                                                                                                                               1. 1. Edit the context file and set the value of the context variables_ebs_central_inventory to 'true'.
2. Run AutoConfig.
3. Run the following command:
$ perl $FND_TOP/patch/115/bin/txkMigrateInventory.pl -contextfile=$CONTEXT_FILE

Add or Delete Managed servers as required
==========================================
Add MS
======
$ perl /patch/115/bin/adProvisionEBS.pl \
ebs-create-managedserver -contextfile= \
-managedsrvname=oacore_server2 -servicetype=oacore \
-managedsrvport=7203 -logfile=/TXK/addMS_oacoreserver2.log

Start newly added Managed server
admanagedsrvctl.sh start oacore_server2

Delete MS
==========
Stop the server
admanagedsrvctl.sh stop oacore_server2

$ perl /patch/115/bin/adProvisionEBS.pl \
ebs-delete-managedserver \
-contextfile= -managedsrvname=oacore_server2 \
-servicetype=oacore -logfile=/TXK/delMS_oacoreserver2.log

 $ perl /patch/115/bin/txkSetAppsConf.pl -contextfile= \
-configoption=removeMS -oacore=testserver.example.com:7205

Post-Upgrade steps
==================
Keep Concurrent programs on hold as required
Disable alerts if not desired,
Enable triggers that are disabled before upgrade
Create DB LINKS
Ensure the softlinks are updated with right locations
Ensure DBA_DIRECTORIES point to correct directories
Enable audit trail on R122
Configure WorkFlow
Compile Invalids and schedule periodically
Bring up Application Services
Run and Schedule weeky Gather Schema statistics for ALL schemas with estimate percent 40.
Schedule other Purge and workflow background processes requests
Startup application services and Clear Cache
Run fs_clone
Run adpreclone.pl on DB and Apps

SLA Update
===========
If SLA data is not upgraded completely during upgrade window, this can be done after upgrade is completed as follows:

1. Update profile option as required or in steps of 6 months -- SLA: Initial Date for Historical Upgrade
2. Run xla5584908.drv
     adop phase=apply options=nocopyportion patchtop=$XLA_TOP/patch/115  patches=driver:xla5584908.drv hotpatch=yes

Query to list concurrent program details with its parameter

1. query to list all the responsibilities attached to a user

select fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('lang')


2.to find from which responsibility a concurrent program can be run

select distinct
  a.user_concurrent_program_name,
  a.description,
  request_group_name,
  e.responsibility_name
from
  fnd_concurrent_programs_tl a,
  fnd_request_groups b,
  fnd_request_group_units c,
  fnd_responsibility d,
  fnd_responsibility_tl e
where
  a.concurrent_program_id = c.request_unit_id   
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id

3.provide concurrent program name to the following query.

it lists all the request sets which are created with the concurrent program given.

select distinct user_request_set_name
  from fnd_request_sets_tl
 where request_set_id in
          (select request_set_id
             from fnd_request_set_programs
            where concurrent_program_id =
                     (select concurrent_program_id
                        from fnd_concurrent_programs_tl
                       where upper(user_concurrent_program_name) = upper( '&enter_prog_name')));

4. provide the request set name to the following query.
it lists all concurrent programs of this request set.

select user_concurrent_program_name
  from fnd_concurrent_programs_tl
 where concurrent_program_id in
          (select concurrent_program_id
             from fnd_request_set_programs
            where request_set_id =
                     (select request_set_id
                        from fnd_request_sets_tl
                       where upper(user_request_set_name) = upper('&request_set_name')));

5. query to list concurrent program details with its parameter, values set and default value/type:

  select fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.application_short_name
      , fav.application_name
      , fav.application_id
      , fdfcuv.end_user_column_name
      , fdfcuv.form_left_prompt prompt
      , fdfcuv.enabled_flag
      , fdfcuv.required_flag
      , fdfcuv.display_flag
      , fdfcuv.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , fdfcuv.default_value  
 from   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 where  fcp.concurrent_program_id = fcpl.concurrent_program_id
 and    fcpl.user_concurrent_program_name = :conc_prg_name
 and    fcpl.language = 'us'
 and    fav.application_id=fcp.application_id
 and    fdfcuv.descriptive_flexfield_name = '$srs$.' || fcp.concurrent_program_name
 and    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 and    flv.lookup_type(+) = 'flex_default_type'
 and    flv.lookup_code(+) = fdfcuv.default_type
 and    flv.language(+) = userenv ('lang');


 6. query to find out concurrent program details and its parameters

select fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.concurrent_program_id
     , fav.application_short_name
     , fav.application_name
     , fav.application_id
     , fdfcuv.end_user_column_name
     , fdfcuv.form_left_prompt prompt
     , fdfcuv.enabled_flag
     , fdfcuv.required_flag
     , fdfcuv.display_flag
from   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl fdfcuv
     , fnd_application_vl fav
where  fcp.concurrent_program_id = fcpl.concurrent_program_id
and    fcpl.user_concurrent_program_name = :conc_prg_name
and    fav.application_id=fcp.application_id
and    fcpl.language = 'US'
and    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;


7 :for checking the locks in concurrent jobs

select decode(request,0,'holder: ','waiter: ')||sid sess,inst_id,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;

8 :for checking the concurrent programs running currently with details of processed time-- and start date


 select distinct c.user_concurrent_program_name,round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
 a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
 (a.actual_start_date-a.request_date)*24*60*60 as lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from   apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_concurrent_programs_tl c,apps.fnd_user d
where  a.concurrent_program_id=b.concurrent_program_id and b.concurrent_program_id=c.concurrent_program_id and
a.requested_by=d.user_id and status_code='r' order by process_time desc;
  
9 :for checking last run of a concurrent program along with processed time

-- useful to find the details of concurrent programs which run daily and comparison purpose

select distinct c.user_concurrent_program_name,
            round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) as process_time,
            a.request_id,a.parent_request_id,to_char(a.request_date,'dd-mon-yy hh24:mi:ss'),to_char(a.actual_start_date,'dd-mon-yy hh24:mi:ss'),
  to_char(a.actual_completion_date,'dd-mon-yy hh24:mi:ss'), (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
            (a.actual_start_date-a.request_date)*24*60*60 as lag_time,
            d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from   apps.fnd_concurrent_requests a,
            apps.fnd_concurrent_programs b ,
            apps.fnd_concurrent_programs_tl c,
            apps.fnd_user d
where       a.concurrent_program_id= b.concurrent_program_id and
            b.concurrent_program_id=c.concurrent_program_id and
            a.requested_by =d.user_id and
--          trunc(a.actual_completion_date) = '24-aug-2005'
c.user_concurrent_program_name='incentive compensation analytics - odi' --  and argument_text like  '%, , , , ,%';
--          and status_code!='c'

10 :for checking the last run of concurrent program.

- use below query to check all the concurrent request running which may refer given package

-- this is very useful check before compiling any package on given instance.

-- the query can be modified as per requirement.

-- remove fnd_concurrent_requests table and joins to check all program dependent on given package.


select
 fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');

11 :the following query will display the time taken to execute the concurrent programs

--for a particular user with the latest concurrent programs sorted in least time taken

-- to complete the request.


 select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' hours ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' minutes ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' secs ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      and pt.language = userenv('lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc;


12 : by using the below query we can get sid,serial#,spid of the concurrent request..

    select a.request_id, d.sid, d.serial# , c.spid
    from apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    where a.controlling_manager = b.concurrent_process_id
    and c.pid = b.oracle_process_id
    and b.session_id=d.audsid
    and a.request_id = &request_id
    and a.phase_code = 'r';


13 : by using below concurrent manager and program rules...

--gives detail of the concurrent_queue_name and user_concurrent_program_name

select b.concurrent_queue_name, c.user_concurrent_program_name
from fnd_concurrent_queue_content a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
where a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(include_flag, 'i', 1, 2), type_code;
  

14 : gives details of running concurrent jobs


select distinct c.user_concurrent_program_name,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) as process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 as end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 as lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
from     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.fnd_concurrent_programs_tl c,
    apps.fnd_user d
where   a.concurrent_program_id=b.concurrent_program_id and
    b.concurrent_program_id=c.concurrent_program_id and
    a.requested_by=d.user_id and
    status_code='r' order by process_time desc;
  

15 : gives detail of concurrent job completed and pending


select
 fcr.request_id
,fcpv.user_concurrent_program_name
,fcpv.concurrent_program_name
,fcpv.concurrent_program_id
,fcr.status_code
,fcr.phase_code
from fnd_concurrent_programs_vl fcpv
,fnd_executables fe
,sys.dba_dependencies dd
,fnd_concurrent_requests fcr
where fcpv.executable_id = fe.executable_id
and fe.execution_method_code = 'i'
and substr(fe.execution_file_name,1,instr(fe.execution_file_name, '.', 1, 1) - 1) = upper(dd.name)
and dd.referenced_type in ('view', 'table', 'trigger', 'package') -- add as required
--and referenced_owner = 'xxcus'
and dd.referenced_name = upper('&package_name')
and fcr.concurrent_program_id = fcpv.concurrent_program_id
and fcr.phase_code not in ( 'c','p');

16 :gives detail of running and completed concurrent jobs with start date and end date

-- latest one at the top

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' hours ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' minutes ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' secs ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'r','running','c','complete',f.phase_code) phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      and pt.language = userenv('lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc;

17 : wait events details related with concurrent programs


select s.saddr, s.sid, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
upper (s.program) program, s.type, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
from v$session s
where ( (s.username is not null)
and (nvl (s.osuser, 'x') <> 'system')
and (s.type <> 'background') and status='active'
)
order by "program";

18 : to find the pid of the concurrent job and kill it.


select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_id ='31689665'
and a.inst_id = b.inst_id and c.os_process_id = a.process;

19 :to find the database sid of the concurrent job

-- we need our concurrent request id as an input.

-- c.spid= is the operating system process id

-- d.sid= is the oracle process id


sql> column process heading "fndlibr pid"

select a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.spid
from apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
where a.controlling_manager = b.concurrent_process_id
and c.pid = b.oracle_process_id
and b.session_id=d.audsid
and a.request_id = &request_id
and a.phase_code = 'r';