Concurrent Request on hold

Put all Concurrent requests on hold.

UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
and status_code in ('Q','I');

unhold all requests submitted/submitted by sysadmin --

UPDATE fnd_concurrent_requests
SET hold_flag = 'N'
WHERE phase_code = 'P'
and status_code in ('Q','I')
and REQUESTED_BY='0';

grant select to APPS object on different schema without causing invalids in database.

Query to grant select privs on all apps objects to a given schema XYZ without causing invalid objects


select 'exec ad_zd.grant_privs(''SELECT'||''''||','||''''||object_name||''''||','||''''||'XYZ'||''''||')'||';'
from dba_objects where object_type in ('TABLE','VIEW') and owner ='APPS';

find Indexes created on table

Query to find the list of all indexes created on a table.

SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_name like upper('&table_name')
ORDER BY index_name, column_position;

find number of users connected on each application node

Query to find the number of active users connected to each oracle ebusiness suite Server

Script to determine "active users" for OACoreGroup: 

REM 
REM SQL to count number of Application users 11i/R12 
REM Run as APPS user 
REM 
select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions 
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y' 
and icx.PSEUDO_FLAG = 'N' 
and icx.node_id=fnd.node_id 
and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0, 
icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects 
group by icx.node_id,fnd.node_name; 
REM 
REM END OF SQL 
REM 

check last email sent from oracle EBusiness Suite

Query to check the last email sent from oracle Ebusiness suite


select to_char(max(begin_date),'DD-MON-YY HH24:MI:SS')
 from apps.wf_notifications
 where mail_status = 'SENT';

ADOP  PATCHING CYCLE -  PHASES


source <ebs_root>/EBSapps.env run
To prepare instance for patching

$adop phase=prepare                    

Source the patch edition environment file
To prepare instance for patching, specify patches, request merge, and apply with 4 workers.
$adop phase=prepare, apply patches=12345, 67890 merge=yes workers=4

adop phase=apply patches=xxx


To prepare instance for patching and apply patches interactively
$adop phase=prepare, apply

Running all phases in single command:
$adop phase=prepare,apply,finalize,cutover,cleanup patches=patch1,patch2

To apply patches with list of patches in the input_file.
$adop phase=apply input_file=adopsession20120702.txt

To fully automated patching with all parameters taken from input_file:
$adop input_file=adopsessions20120702.txt

To apply patches in hotpatch mode
$adop phase=apply input_file=adopsession20120702.txt hotpatch=yes

adop phase=finalize

adop phase=cutover

adop phase=cutover cm_wait=3

adop phase=cutover cm_wait=3 mtrestart=no (not to start the application services)

source <ebs_root>/EBSapps.env run

adop phase=cleanup

if prepare phase is failing..

adop phase=prepare skipsyncerror=yes

ADOP - Checking if adop phase fails on prepare,fs_clone or cutover


export RUN_CONTEXT_FILE=$INST_TOP/appl/admin/<SID>_hostname.xml
 export PATCH_CONTEXT_FILE=PATCH_FILEYSTEM_INST_TOP/appl/admin/<SID>_hostname.xml

check if fs_clone fails
 perl $AD_TOP/patch/115/bin/txkADOPValidations.pl  -contextfile=$RUN_CONTEXT_FILE -patchctxfile=$PATCH_CONTEXT_FILE -phase=fs_clone -logloc=/tmp

check if prepare phase fails

 perl $AD_TOP/patch/115/bin/txkADOPValidations.pl  -contextfile=$RUN_CONTEXT_FILE -patchctxfile=$PATCH_CONTEXT_FILE -phase=prepare -logloc=/usr/tmp

check if Cutover phase fails:

 perl $AD_TOP/patch/115/bin/txkADOPValidations.pl  -contextfile=$RUN_CONTEXT_FILE -patchctxfile=$PATCH_CONTEXT_FILE -phase=cutover -logloc=/tmp